开源中国

我们不支持 IE 10 及以下版本浏览器

It appears you’re using an unsupported browser

为了获得更好的浏览体验,我们强烈建议您使用较新版本的 Chrome、 Firefox、 Safari 等,或者升级到最新版本的IE浏览器。 如果您使用的是 IE 11 或以上版本,请关闭“兼容性视图”。
使用子查询可提升 COUNT DISTINCT 速度 50 倍 - 技术翻译 - 开源中国社区

使用子查询可提升 COUNT DISTINCT 速度 50 倍 【已翻译100%】

标签: <无>
oschina 推荐于 4年前 (共 5 段, 翻译完成于 01-26) 评论 13
收藏  
225
推荐标签: 待读

注:这些技术是通用的,只不过我们选择使用Postgres的语法。使用独特的pgAdminIII生成解释图形。

很有用,但太慢

Count distinct是SQL分析时的祸根,因此它是我第一篇博客的不二选择。

首先:如果你有一个大的且能够容忍不精确的数据集,那像HyperLogLog这样的概率计数器应该是你最好的选择。(我们会在以后的博客中谈到HyperLogLog。)但对于需要快速、精准答案的查询,一些简单的子查询可以节省你很多时间。

Garfielt
 翻译得不错哦!

让我们以我们一直使用的一个简单查询开始:哪个图表的用户访问量最大?

select 
  dashboards.name, 
  count(distinct time_on_site_logs.user_id)
from time_on_site_logs 
join dashboards on time_on_site_logs.dashboard_id = dashboards.id
group by name 
order by count desc

首先,我们假设user_id和dashboard_id上已经设置了索引,且有比图表和用户数多得多的日志条目。

一千万行数据时,查询需要48秒。要知道原因让我们看一下SQL解析:

Explain Slow

它慢是因为数据库遍历了所有日志以及所有的图表,然后join它们,再将它们排序,这些都在真正的group和分组和聚合工作之前。

Garfielt
 翻译得不错哦!

先聚合,然后Join

group-聚合后的任何工作代价都要低,因为数据量会更小。group-聚合时我们不需使用dashboards.name,我们也可以先在数据库上做聚集,在join之前:

select
  dashboards.name,
  log_counts.ct
from dashboards
join (
  select
    dashboard_id,
    count(distinct user_id) as ct
  from time_on_site_logs 
  group by dashboard_id
) as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

现在查询运行了20秒,提升了2.4倍。再次通过解析来看一下原因:

Explain Faster

正如设计的,group-聚合在join之前。而且,额外的我们可以利用time_on_site_logs表里的索引。

Garfielt
 翻译得不错哦!

首先,缩小数据集

我们可以做的更好。通过在整个日志表上group-聚合,我们处理了数据库中很多不必要的数据。Count distinct为每个group生成一个哈希——在本次环境中为每个dashboard_id——来跟踪哪些bucket中的哪些值已经检查过。

我们可以预先计算差异,而不是处理全部数据,这样只需要一个哈希集合。然后我们在此基础上做一个简单的聚集即可。

select
  dashboards.name,
  log_counts.ct
from dashboards 
join (
  select distinct_logs.dashboard_id, 
  count(1) as ct
  from (
    select distinct dashboard_id, user_id
    from time_on_site_logs
  ) as distinct_logs
  group by distinct_logs.dashboard_id
) as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

我们采取内部的count-distinct-group,然后将数据拆成两部分分成两块。第一块计算distinct (dashboard_id, user_id) 。第二块在它们基础上运行一个简单group-count。跟上面一样,最后再join。

Explain Fastest

Garfielt
 翻译得不错哦!

呵呵,大发现:这样只需要0.7秒!这比上面的查询快28倍,比原来的快了68倍

通常,数据大小和类型很重要。上面的例子受益于基数中没多少换算。distinct (user_id, dashboard_id)相对于数据总量来说数量也很少。不同的对数越多,用来group和计数的唯一数据就越多——代价便会越来越大。

下一遇到长时间运行的count distinct时,尝试一些子查询来减负吧。

Garfielt
 翻译得不错哦!
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们
评论(13)
Ctrl/CMD+Enter

思想还不错,不过第三种写得有点复杂,不知道CTE能否满足
嗯。思路 不错
SQL SERVER 也一样吗?
顶贴不评论~
分组处理,本来就要把针对数据量最大的部分单独拿出来运算,然后再考虑对这个结果进行连接把相关信息提取出来。如果数据库在sql中无法感知这一点,就只能用子查询等方式告诉数据库要这样做。
真想不到这样的写法。
mark
我早已经这么写了哈哈哈
感觉写SQL会自然而然的用到这些
呵呵,正常都是将每个子查询部分的数据集尽量搞到最小再连接,sql复杂些,但是性能最高。
真的有这么神奇? 我的印象中. COUNT DISTINCT 效率是很高的.尤其是group by 有索引的情况下,效率高的离谱.
学习了
IT共享导航来访http://itdh.boqingbokeji.com
顶部