利用子查询可提拔 COUNT DISTINCT 速率 50 倍

我们只需要把binlog文件反向执行，每个操作都执行逆操作即可。当然也不是所有的event都反转。Table_mapevent必须还是在Rows_log_event每个操作之前。很有效，但太慢

Countdistinct是SQL剖析时的祸端，因而它是我第一篇博客的不贰选择。

起首：假如你有一个年夜的且可以容忍不准确的数据集，那像HyperLogLog如许的几率计数器应当是你最好的选择。（我们会在今后的博客中谈到HyperLogLog。）但关于必要疾速、精准谜底的查询，一些复杂的子查询能够节俭你良多工夫。

让我们以我们一向利用的一个复杂查询入手下手：哪一个图表的用户会见量最年夜？
select
dashboards.name,
count(distincttime_on_site_logs.user_id)
fromtime_on_site_logs
joindashboardsontime_on_site_logs.dashboard_id=dashboards.id
groupbyname
orderbycountdesc

起首，我们假定user_id和dashboard_id上已设置了索引，且有比图表和用户数多很多的日记条目。

一万万行数据时，查询必要48秒。要晓得缘故原由让我们看一下SQL剖析：

登录/注册后可看大图

点击图片可检察全图

它慢是由于数据库遍历了一切日记和一切的图表，然后join它们，再将它们排序，这些都在真实的group和分组和聚合事情之前。

先聚合，然后Join

group-聚合后的任何事情价值都要低，由于数据量会更小。group-聚应时我们不需利用dashboards.name，我们也能够先在数据库上做会萃，在join之前：
select
dashboards.name,
log_counts.ct
fromdashboards
join(
select
dashboard_id,
count(distinctuser_id)asct
fromtime_on_site_logs
groupbydashboard_id)aslog_counts
onlog_counts.dashboard_id=dashboards.id
orderbylog_counts.ctdesc

如今查询运转了20秒，提拔了2.4倍。再次经由过程剖析来看一下缘故原由：

登录/注册后可看大图

点击图片可检察全图

正如计划的，group-聚合在join之前。并且，分外的我们能够使用time_on_site_logs内外的索引。

起首，减少数据集

我们能够做的更好。经由过程在全部日记表上group-聚合，我们处置了数据库中良多不用要的数据。Countdistinct为每一个group天生一个哈希——在本次情况中为每一个dashboard_id——来跟踪哪些bucket中的哪些值已反省过。

我们能够事后盘算差别，而不是处置全体数据，如许只必要一个哈希汇合。然后我们在此基本上做一个复杂的会萃便可。
select
dashboards.name,
log_counts.ct
fromdashboards
join(
selectdistinct_logs.dashboard_id,
count(1)asct
from(
selectdistinctdashboard_id,user_id
fromtime_on_site_logs
)asdistinct_logs
groupbydistinct_logs.dashboard_id
)aslog_counts
onlog_counts.dashboard_id=dashboards.id
orderbylog_counts.ctdesc

我们接纳外部的count-distinct-group，然后将数据拆成两部分分红两块。第一块盘算distinct(dashboard_id,user_id)。第二块在它们基本上运转一个复杂group-count。跟下面一样，最初再join。

登录/注册后可看大图

点击图片可检察全图

呵呵，年夜发明：如许只必要0.7秒！这比下面的查询快28倍，比本来的快了68倍。

一般，数据巨细和范例很主要。下面的例子受害于基数中没几换算。distinct(user_id,dashboard_id)相对数据总量来讲数目也很少。分歧的对数越多，用来group和计数的独一数据就越多——价值便会愈来愈年夜。

下一次碰到长工夫运转的countdistinct时，实验一些子查询来减负吧。
优化的SQL查询算法，有效地提高查询速度