我会推荐这样做:
使用整数而不是字符/变长字符。这样你可以更快地进行索引(除了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秒。