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天哪个浏览器来了我们的网站，有多少
• 我今天想知道我有多少用户
• 我想知道从上个小时起我们得到了什么urls (参考者)

谢谢

Answer 1

我建议这样做：

使用中间格而不是字符/varchars。这样，您的索引速度更快(除了引用者)。此外，我可以推荐获得摘要表。虽然它并没有真正标准化，但是查询将立即执行--特别是如果您有一个拥有大量流量的大型组织。

这是桌子：

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= firefox等这可以在您的代码中完成(因此您加载了用于插入和选择的相同代码)。我这里不使用枚举，因为如果您需要更改表，并且您有数百万条记录，这可能是痛苦的。new browser =代码中的新编号，更改速度要快得多。

如果发生什么事情，此表可用于重新总结所有其他表。因此，您可以为内联汇总表(示例浏览器)创建索引。

现在汇总表：

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

这样，当您将(当用户访问站点并创建一个与表名匹配的$string )插入到这个表中时，只需使用on duplicate key number = number +1即可。这样，当您检索浏览器统计数据时，速度非常快。

现在，您必须创建一个合并表，因为如果您是这个月的第二个，并且希望查询最后7天，那么您将需要当前的月份和最后一个月的表。以下是更多信息：http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html

你对其他信息重复这个过程: ip，推荐等等.

为了维护这些表，您必须创建一个为下个月创建表的cron作业。获取当前年份/月份的简单PHP脚本，如果下个月不存在，则创建下一个月的表，然后合并它们)

这可能有点工作，但这是我在工作中所做的(使用类似的数据)，有12 the的数据和5,000名获取数据库的员工。每次查询的平均加载时间约为0.60秒。

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. 使用enum将使浏览器组速度更快。
3. 如果您需要版本信息，那么它将是浏览器、版本、日期时间。
4. datetime, browser上的复合密钥

查询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 ?;

Answer 3

MYSQL中用户代理字符串的浏览器和OS统计

假设您有一个名为‘loginhistory’的MySQL表，其中包含‘userid’和‘useragent’。为了计算某些OSes在用户代理字符串中发生的次数，我使用了以下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，当用户代理字符串被搜索到某些元素并转换成友好的OS名称时。然后，外部查询对这些新创建的OS名称进行分组，并计算每个OS的频率，输出如下所示：

+---------------+------+
| 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子句，例如，只显示过去六个月中使用的OSes和浏览器的统计信息。