摘要:很有用,但是却很慢计算非重复的数目是分析的一个灾难,显然,我们要在第一篇博文上讨论。在来看一下,图解可以清楚无误的表明原因。也许你下次计算非重复结果需要花费一天的时间,试着用子查询的方法减轻它的负载。
本文说的这个技术是通用的,但为了解释说明,我们选用了 PostgreSQL。感谢 pgAdminIII 提供的解释性插图,这些插图有很大帮助。
很有用,但是却很慢计算非重复的数目是SQL分析的一个灾难,显然,我们要在第一篇博文上讨论。
首先一点:我们如果有一个很大的数据集而且可以容忍它不精确。一个像 HyperLogLog 的概率统计器可能是你的首选(我们在以后的博客中会讲到HyperLogLog ),但是要追求快速精准的结果,子查询的方法会节省你很多时间。
让我们从一个简单的查询语句开始吧:哪一个dashboard用户访问的最频繁。
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上都有高效的索引,并且日志行数要比user_id 和 dashboard_id多很多。
仅仅一千万行数据,查询语句就花费了48秒的时间。知道为什么吗?让我们看一下明了的图解吧。
慢的原因是数据库要遍历dashboards表和logs表的所有记录,然后JOIN操作,然后排序,之后才进行实际需要的分组和聚集操作。
先聚集,然后联合数据表分组和聚集之后,一切数据库操作的代价都变小了,因为数据的数量变小了。在分组和聚集的时候,因为我们不需要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
语句运行了24秒,获得了2.4倍的性能提高。在来看一下,图解可以清楚无误的表明原因。
像我们预期地那样,join操作之前先进行了group-and-aggregate操作。快上加快,我们还可以在time_on_site_logs 表上加上索引。
第一步,让你的数据变小我们还可以做的更好,我们对日志表做group-and-aggregate操作时,我们处理了一些无关的数据,其实没有必要。我们可以对每个分组上创建一个哈希集合,这样,在每个哈希桶中让每个dashboard_id 挑出那些需要被看到处理的数据。
不用做那么多工作,只用一个哈希集合,我们就可以先去除那些重复的值。然后我们在这个结果上做聚集操作。
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
我们让去重和分组聚集一步一步进行,分成两个阶段。首先在(dashboard_id, user_id)对上去重,然后在这基础上做简单快速的分组计算工作,JOIN操作还是放在最后。
让我们来揭晓最终效果:总共花费了0.7秒,是上一次的28倍,最初的68倍。
一般来说,数据大小和数据位置是很重要的,表中的属性字段的可能取值个数相对很少,所以才有那么明显的效果,与数据总量相比较,(user_id, dashboard_id) 对的不同值很少。越多的不同的值,各行的数据越分散,所以分组和计算它们花费越长的时间,果然天下没有白吃的午餐。
也许你下次计算非重复结果需要花费一天的时间,试着用子查询的方法减轻它的负载。
要问一下,你们是何许人也?我们做了 Periscope,一个可以使SQL数据分析更快的工具。我们在这里分享一下我们的工具蕴含的算法和技术。你可以到我们的主页上注册,从而作为我们的新客户,我们可以通知你相关事宜。
原文:Use Subqueries to Count Distinct 50X Faster
转载于:伯乐在线 - sunbiaobiao
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/38931.html
摘要:很有用,但是却很慢计算非重复的数目是分析的一个灾难,显然,我们要在第一篇博文上讨论。在来看一下,图解可以清楚无误的表明原因。也许你下次计算非重复结果需要花费一天的时间,试着用子查询的方法减轻它的负载。 本文说的这个技术是通用的,但为了解释说明,我们选用了 PostgreSQL。感谢 pgAdminIII 提供的解释性插图,这些插图有很大帮助。 很有用,但是却很慢 计算非重复的数目是...
阅读 1865·2021-11-15 11:39
阅读 1073·2020-12-03 17:06
阅读 729·2019-12-27 11:42
阅读 3267·2019-08-30 13:59
阅读 1452·2019-08-26 13:22
阅读 3281·2019-08-26 12:15
阅读 2471·2019-08-26 10:22
阅读 1558·2019-08-23 18:40