MySQL统计表优化

Question

我需要在MySQL 5.5版本中创建一个表。

这个表将有以下信息：

• 用户浏览器（例如Firefox或Chrome）
• 浏览器版本（例如8.0或10）
• 用户IP地址
• 日期和时间（用户访问网站的时间）
• 引荐人（URL或为空）

这是我的想法：

create table statistics (
 browser varchar(255) not null,
 version float not null,
 ip varchar(40) not null,
 dateandtime datetime,
 referrer varchar(255)
);

我在mysql.com上看到需要使用索引来使查询变快，但现在我的问题是应该创建哪个索引才能让表查询更快？

我需要查询所有字段，例如：

• 我想知道过去7天有多少浏览器访问了我们的网站
• 我想知道今天有多少用户
• 我想知道过去1小时我们得到了哪些URL（来源）

谢谢

- apollo

Answer 1

我会推荐这样做：

使用整数而不是字符/变长字符。这样你可以更快地进行索引（除了referrer）。此外，我建议获取摘要表格。虽然它不是真正的规范化，但查询将立即执行 - 特别是如果你有一个大的组织和大量的流量。

所以这就是表格:

create table statistics (
 browser tinyint(3) UNSIGNED not null default 0,
 version float(4,2) not null default 0,
 ip INT(10) UNSIGNED not null default 0,
 createdon datetime,
 referrer varchar(5000),
 key browserdate (browser, createdon),
 key ipdate (ip, createdon),
 // etc..
);

浏览器 0 = 未知, 1 = 火狐等。这可以在您的代码中完成（因此您加载相同的代码以进行插入和选择）。我在这里不使用枚举，因为如果您需要修改表格并且有数百万条记录，则可能会很痛苦。新的浏览器 = 代码中的新数字，这种方式更快速地进行更改。

如果出现问题，此表可用于重新汇总所有其他表。因此，您需要为内联摘要表（例如浏览器）创建索引。

现在是摘要表：

create table statistics_browser_2011_11 (
 browser tinyint(3) UNSIGNED not null default 0,
 version float(4,2) not null default 0,
 number bigint(20) not null default 0,
 createdon datetime,
 unique key browserinfo (createdon, browser, version)
); // browsers stats for november 2011

这种方式是：当您插入数据（获取用户访问站点的日期并创建与表名匹配的字符串）到这个表中时，您只需要使用on duplicate key number = number +1。这样，当您检索浏览器统计信息时速度非常快。

现在，您将需要创建一个合并表，因为如果您是本月的第二天并且想查询过去7天的数据，则需要使用当前月份和上个月份的表。这里有更多信息：http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html

您需要针对其他信息（例如IP地址、引荐来源等）重复此过程。

为了维护这些表，您需要创建一个cronjob，用于为下个月创建表格。简单的PHP脚本获取当前年/月，然后为下个月创建表格（如果不存在），然后将它们合并。

这可能需要一些工作，但这就是我在工作中处理类似数据时的方法（拥有12TB的数据和5000名员工访问数据库）。每次查询的平均负载时间约为0.60秒。

- Gabriel

---

我从未想过这一点，我认为为了标准化，我们必须遵循1个表查找等等...我可以看到这是快速的。我将有很多工作要做，谢谢。 - apollo

没错，这需要很多工作，但此类数据增长非常迅速，经过几年，你会看到巨大的差别。 - Gabriel

Answer 2

我认为你的架构可以改进

create table statistics
(
  browser enum('Firefox','IE','Opera','Chrome','Safari','Others') not null 
    default 'Others',
   // major browser family only
   // instead of using free-form of varchar

  user_agent text,
   // to store the complete user agents
   // mainly for reference purpose only

  version float not null,
  ip varchar(40) not null,

  dateandtime datetime not null,

  referer varchar(2000)
  // 255 is no sufficient for referer
);

索引键

1. 在浏览器和日期时间上建立索引
2. 使用枚举将使浏览器的 GROUP BY 更快
3. 如果您需要版本信息，则为浏览器、版本、日期时间
4. 复合键为 日期时间，浏览器

查询1

select browser, count(*) from statistics
where dateandtime between ? and ?
group by browser;

查询2

 select count(*) from statistics
 where dateandtime between ? and ?;

查询 3

 select referer from statistics
 where dateandtime between ? and ?;

- ajreal

---

使用主键几乎总是比其他索引更有效。你可能希望让该表具有一个主键，以替代你提出的索引。 - Romain

兄弟，你在说什么啊？是我太天真了还是怎么回事？... :( - ajreal

我相信你知道可以设置带有主键的表（这是表中“主唯一索引”）。这是MySQL中InnoDB/MyISAM表上最快的可能索引。 - Romain

（浏览器，日期时间，版本，用户代理，IP，引荐者），这将防止具有完全相同内容的两行（但这真的可能发生吗？），并将使得根据“浏览器，日期时间”进行访问数据更快。 - Romain

以上所有内容都可以重复。 - ajreal

让我们在聊天中继续这个讨论。 - Romain

Answer 3

从MySQL中的用户代理字符串获取浏览器和操作系统统计信息
--------------------------------------------------------------
假设您有一个名为“loginhistory”的MySQL表，其中包含“userid”和“useragent”。为了计算在用户代理字符串中出现某些操作系统的次数，我使用了以下MySQL查询：

SELECT OS, COUNT(OS) AS freq FROM 
(SELECT 
 CASE
   WHEN useragent LIKE '%iPad%' THEN 'iPad'
   WHEN useragent LIKE '%iPhone%' THEN 'iPhone'
   WHEN useragent LIKE '%Android%' THEN 'Android'
   WHEN useragent LIKE '%Mac OS X%' THEN 'OS X'
   WHEN useragent LIKE '%X11%' THEN 'Linux'
   WHEN useragent LIKE '%Windows NT 6.3%' THEN 'Windows 8.1'
   WHEN useragent LIKE '%Windows NT 6.2%' THEN 'Windows 8'
   WHEN useragent LIKE '%Windows NT 6.1%' THEN 'Windows 7'
   WHEN useragent LIKE '%Windows NT 6.0%' THEN 'Windows Vista'
   WHEN useragent LIKE '%Windows NT 5.2%' THEN 'Windows Server 2003; Windows XP x64 Edition'
   WHEN useragent LIKE '%Windows NT 5.1%' THEN 'Windows XP'
   WHEN useragent LIKE '%Windows NT 5.0%' THEN 'Windows 2000'
   WHEN useragent LIKE '%Windows NT 4.0%' THEN 'Microsoft Windows NT 4.0'
   WHEN useragent LIKE '%Windows 9' THEN 'Windows 95/98/Millenium'
   WHEN useragent LIKE '%Windows CE' THEN 'Windows CE'
   ELSE 'Other'
 END OS
 FROM loginhistory) AS osses 
GROUP BY OS 
ORDER BY freq DESC

通过使用CASE、WHEN和THEN，搜索用户代理字符串中的某些元素并将其转换为友好的操作系统名称。然后，外部查询对这些新创建的操作系统名称进行分组，并计算每个操作系统的频率，输出类似于以下内容的结果：

+---------------+------+
| OS            | freq |
+---------------+------+
| Windows 7     | 173  |
| Windows 8.1   | 152  |
| iPad          | 63   |
| Windows Vista | 13   |
| OS X          | 10   |
| iPhone        | 8    |
| Android       | 7    |
+---------------+------+
7 rows in set (0.00 sec)

可以使用相同的方法来计算所有用户代理字符串中浏览器的频率。

SELECT browser, COUNT(browser) AS freq FROM 
(SELECT 
CASE
   WHEN useragent LIKE '%Chrome%' THEN 'Chrome'
   WHEN useragent LIKE '%Safari%' THEN 'Safari'
   WHEN useragent LIKE '%Firefox%' THEN 'Firefox'
   WHEN useragent LIKE '%MSIE 7%' THEN 'IE7'
   WHEN useragent LIKE '%MSIE 8%' THEN 'IE8'
   WHEN useragent LIKE '%MSIE 9%' THEN 'IE9'
   WHEN useragent LIKE '%MSIE 10%' THEN 'IE10'
   WHEN useragent LIKE '%rv:11%' THEN 'IE11'
   ELSE 'Other'
  END browser
  FROM loginhistory) AS browsers 
GROUP BY browser 
ORDER BY freq DESC

这将输出类似于以下内容：

+---------+------+
| browser | freq |
+---------+------+
| IE7     | 128  |
| IE11    | 119  |
| Chrome  | 83   |
| Safari  | 38   |
| Firefox | 7    |
| IE10    | 4    |
+---------+------+
6 rows in set (0.00 sec)

这些数据可以直接转储到像Chart.js这样的库中，该库将自动从频率数据生成饼图。或者您可以根据所有频率的总和自己计算百分比。

如果您还有一个日期或时间戳列与每个用户代理字符串相邻，您可以添加一个WHERE子句，例如，仅显示过去六个月中使用的操作系统和浏览器的统计信息。

- Muhammad Tahir