我正试图从一个每天约有2M新行的表中获得聚合结果(总的唯一IP)。
表格:
CREATE TABLE `clicks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`type` enum('popunder','gallery','exit','direct') COLLATE utf8_unicode_ci NOT NULL,
`impression_time` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`source_user_id` int(11) NOT NULL,
`destination_user_id` int(11) NOT NULL,
`destination_campaign_id` int(11) NOT NULL,
`destination_campaign_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`destination_campaign_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`ip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`referrer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country_code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`isp` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`category_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`bid` float(8,2) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `ip` (`ip`),
KEY `source_user_id` (`source_user_id`),
KEY `destination_user_id` (`destination_user_id`),
KEY `destination_campaign_id` (`destination_campaign_id`),
KEY `clicks_hash_index` (`hash`),
KEY `clicks_created_at_index` (`created_at`),
KEY `campaign_date` (`destination_campaign_id`,`created_at`),
KEY `source_user_date` (`source_user_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=301539660 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
我的查询:
SELECT SUM(ips_by_date.count) as count, ips_by_date.date as date
FROM (SELECT count(DISTINCT ip) as count, DATE(created_at) as date
FROM clicks as clicks
WHERE created_at BETWEEN '2016-05-22 00:00:00' AND '2016-05-23 23:59:59'
GROUP BY DATE(created_at)) as ips_by_date
GROUP BY date;
现在,这个查询只运行了一天就花了93秒,我觉得我错过了什么。
我有什么优化可以加快这个简单计数的性能吗?
谢谢。
首先,我不明白为什么子查询是必要的。内部查询每个日期有一行。没有必要再次聚集。其次,您的查询持续了两天,但我对性能有所了解。
所以,让我们从开始
SELECT count(DISTINCT ip) as count, DATE(created_at) as date
FROM clicks
WHERE created_at BETWEEN '2016-05-22 00:00:00' AND '2016-05-23 23:59:59'
GROUP BY DATE(created_at);
对于此查询,您需要clicks(created_at, ip)
上的索引。还要注意,我会把它写为:
SELECT count(DISTINCT ip) as count, DATE(created_at) as date
FROM clicks
WHERE created_at >= '2016-05-22' AND created_at < '2016-05-24'
GROUP BY DATE(created_at);
这应该显示出一些改进,但我不认为它会更好,因为文件排序对于外部聚合仍然是必要的。
这里的性能可以归结为索引的效率,因为代码中没有太大的更改空间(请参阅戈登代码以获得更干净的代码版本)。
(created_at)
或(created_at, ip)
上的索引在不进行进一步排序的情况下不会直接为您提供distinct ip
(因为您不按created_at
分组),但后者至少不需要直接访问表。因此,下一次优化将需要(date(created_at), ip)
上的索引,尽管这意味着数据会有一些重复。
从mysql5.7.6开始,您可以使用生成的列来创建列dtas date(created_at)
,在5.7.6之前,只需创建列dt
并手动更新即可(如果您更改了create_at
-值,则必须添加一个触发器来相应地更新该列)。您的初始更新可能需要一段时间,所以可以批量更新,或者考虑在将来的查询中使用它。
添加索引(dt, ip)
现在应该可以通过一次索引/范围扫描获得结果,无需文件排序,也无需从datetime:计算date()
select count(distinct ip) as count, dt
from clicks
where dt >= '2016-05-22' and dt < '2016-05-24'
group by dt;
如果一切正常,即使是几百万行,这也只需要几秒钟的时间。
有些事情仍然可能给您带来麻烦:由于90秒对于200万行来说仍然是一个相对较大的数字,这可能表明您的缓冲区大小/ram/hdd有问题。如果你花了80秒的时间来重新筛选并将索引加载到内存中,那么在那之后索引就无能为力了。一个简单的测试:运行查询两次。如果第二次(真的)明显更快(比如<<1/10),那么您可能需要考虑调整系统设置、架构或分区。话虽如此,你不应该为了这样的查询而调整你的系统(有时甚至不添加另一个索引或日期列),也不应该放慢其他更重要的事情的速度——为了获得每日统计数据,你可以在午夜轻松地运行一项任务,获取你能想到的所有统计数据,并将结果保存起来,以便你早上查看,如果运行查询需要几个小时,那也没关系。
首先添加前面提到的复合索引。那么,真正的性能问题将是读取无数行来计算COUNT(DISTINCT...)
。该操作需要收集所有值,排序并执行GROUP BY
,或者尝试将所有不同的值保留在RAM中。
摘要表非常适合在数据仓库应用程序中加速SUM
、COUNT
甚至AVG
。但是COUNT(DISTINCT...)
(也称为"计数唯一用户")不适用于汇总表。如果你愿意接受一个小错误,有办法。查看我的博客。
您可能没有意识到,但VARCHAR
中全面使用255有时会导致不必要的性能问题。在这种情况下,ip
在任何tmp表中占用765个字节,可能在有问题的查询中。将其更改为VARCHAR(39) CHARACTER SET ascii
将使其减少20倍!(很难预测这会在多大程度上加快你的查询速度。你可以通过一个简单的存储函数将其降到BINARY(16)
。