使用子查询可提升 COUNT DISTINCT 速度 50 倍

以你之姓@ 2022-04-16 03:23 202阅读 0赞

**数据库技术学习 [https://www.itkc8.com][https_www.itkc8.com]**

<table> 
 <tbody> 
 <tr> 
 <td> 注：这些技术是通用的，只不过我们选择使用Postgres的语法。使用独特的<a href="http://www.pgadmin.org/" rel="nofollow">pgAdminIII</a>生成解释图形。 <h3><a rel="nofollow"></a>很有用，但太慢</h3> Count distinct是SQL分析时的祸根，因此它是我第一篇博客的不二选择。 首先：如果你有一个大的且能够容忍不精确的数据集，那像<a href="http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf" rel="nofollow">HyperLogLog</a>这样的概率计数器应该是你最好的选择。 </td> 
 <td style="vertical-align:top;">&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> 让我们以我们一直使用的一个简单查询开始：哪个图表的用户访问量最大？ <a href="http://www.oschina.net/translate/use-subqueries-to-count-distinct-50x-faster#" rel="nofollow">?</a> 
 <table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>select</code>&nbsp; <code>&nbsp;&nbsp;</code><code>dashboards.</code><code>name</code><code>,&nbsp;</code> <code>&nbsp;&nbsp;</code><code>count</code><code>(</code><code>distinct</code>&nbsp;<code>time_on_site_logs.user_id)</code> <code>from</code>&nbsp;<code>time_on_site_logs&nbsp;</code> <code>join</code>&nbsp;<code>dashboards&nbsp;</code><code>on</code>&nbsp;<code>time_on_site_logs.dashboard_id&nbsp;=&nbsp;dashboards.id</code> <code>group</code>&nbsp;<code>by</code>&nbsp;<code>name</code>&nbsp; <code>order</code>&nbsp;<code>by</code>&nbsp;<code>count</code>&nbsp;<code>desc</code> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 首先，我们假设user_id和dashboard_id上已经设置了索引，且有比图表和用户数多得多的日志条目。 一千万行数据时，查询需要48秒。要知道原因让我们看一下SQL解析： <a href="https://s3.amazonaws.com/periscope-blog-assets/explain%20slow.png" rel="nofollow"><img alt="Explain Slow" src="http://static.oschina.net/uploads/img/201401/25075015_4ouo.png"></a> 它慢是因为数据库遍历了所有日志以及所有的图表，然后join它们，再将它们排序，这些都在真正的group和分组和聚合工作之前。 </td> 
 <td style="vertical-align:top;"><a href="http://my.oschina.net/Garfielt" rel="nofollow"><img alt="Garfielt" src="http://static.oschina.net/uploads/user/73/147631_50.jpg?t=1370742483000"></a> <a href="http://my.oschina.net/Garfielt" rel="nofollow">Garfielt</a> 翻译于 2年前 3人顶 顶&nbsp;翻译的不错哦! </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> <h3><a rel="nofollow"></a>先聚合，然后Join</h3> group-聚合后的任何工作代价都要低，因为数据量会更小。group-聚合时我们不需使用dashboards.name，我们也可以先在数据库上做聚集，在join之前： <a href="http://www.oschina.net/translate/use-subqueries-to-count-distinct-50x-faster#" rel="nofollow">?</a> 
 <table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>select</code> <code>&nbsp;&nbsp;</code><code>dashboards.</code><code>name</code><code>,</code> <code>&nbsp;&nbsp;</code><code>log_counts.ct</code> <code>from</code>&nbsp;<code>dashboards</code> <code>join</code>&nbsp;<code>(</code> <code>&nbsp;&nbsp;</code><code>select</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>dashboard_id,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>count</code><code>(</code><code>distinct</code>&nbsp;<code>user_id)&nbsp;</code><code>as</code>&nbsp;<code>ct</code> <code>&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>time_on_site_logs&nbsp;</code> <code>&nbsp;&nbsp;</code><code>group</code>&nbsp;<code>by</code>&nbsp;<code>dashboard_id</code> <code>)&nbsp;</code><code>as</code>&nbsp;<code>log_counts&nbsp;</code> <code>on</code>&nbsp;<code>log_counts.dashboard_id&nbsp;=&nbsp;dashboards.id</code> <code>order</code>&nbsp;<code>by</code>&nbsp;<code>log_counts.ct&nbsp;</code><code>desc</code> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 现在查询运行了20秒，提升了2.4倍。再次通过解析来看一下原因： <a href="https://s3.amazonaws.com/periscope-blog-assets/explain%20faster.png" rel="nofollow"><img alt="Explain Faster" src="http://static.oschina.net/uploads/img/201401/25075027_Bx6W.png"></a> 正如设计的，group-聚合在join之前。而且，额外的我们可以利用time_on_site_logs表里的索引。 </td> 
 <td style="vertical-align:top;"><a href="http://my.oschina.net/Garfielt" rel="nofollow"><img alt="Garfielt" src="http://static.oschina.net/uploads/user/73/147631_50.jpg?t=1370742483000"></a> <a href="http://my.oschina.net/Garfielt" rel="nofollow">Garfielt</a> 翻译于 2年前 2人顶 顶&nbsp;翻译的不错哦! </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> <h3><a rel="nofollow"></a>首先，缩小数据集</h3> 我们可以做的更好。通过在整个日志表上group-聚合，我们处理了数据库中很多不必要的数据。Count distinct为每个group生成一个哈希——在本次环境中为每个dashboard_id——来跟踪哪些bucket中的哪些值已经检查过。 我们可以预先计算差异，而不是处理全部数据，这样只需要一个哈希集合。然后我们在此基础上做一个简单的聚集即可。 <a href="http://www.oschina.net/translate/use-subqueries-to-count-distinct-50x-faster#" rel="nofollow">?</a> 
 <table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 </td> 
 <td> <code>select</code> <code>&nbsp;&nbsp;</code><code>dashboards.</code><code>name</code><code>,</code> <code>&nbsp;&nbsp;</code><code>log_counts.ct</code> <code>from</code>&nbsp;<code>dashboards&nbsp;</code> <code>join</code>&nbsp;<code>(</code> <code>&nbsp;&nbsp;</code><code>select</code>&nbsp;<code>distinct_logs.dashboard_id,&nbsp;</code> <code>&nbsp;&nbsp;</code><code>count</code><code>(1)&nbsp;</code><code>as</code>&nbsp;<code>ct</code> <code>&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>(</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>select</code>&nbsp;<code>distinct</code>&nbsp;<code>dashboard_id,&nbsp;user_id</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>time_on_site_logs</code> <code>&nbsp;&nbsp;</code><code>)&nbsp;</code><code>as</code>&nbsp;<code>distinct_logs</code> <code>&nbsp;&nbsp;</code><code>group</code>&nbsp;<code>by</code>&nbsp;<code>distinct_logs.dashboard_id</code> <code>)&nbsp;</code><code>as</code>&nbsp;<code>log_counts&nbsp;</code> <code>on</code>&nbsp;<code>log_counts.dashboard_id&nbsp;=&nbsp;dashboards.id</code> <code>order</code>&nbsp;<code>by</code>&nbsp;<code>log_counts.ct&nbsp;</code><code>desc</code> </td> 
 </tr> 
 </tbody> 
 </table>&nbsp; 我们采取内部的count-distinct-group，然后将数据拆成两部分分成两块。第一块计算distinct (dashboard_id, user_id)&nbsp;。第二块在它们基础上运行一个简单group-count。跟上面一样，最后再join。 <a href="https://s3.amazonaws.com/periscope-blog-assets/explain%20fastest.png" rel="nofollow"><img alt="Explain Fastest" src="http://static.oschina.net/uploads/img/201401/25075033_PVf5.png"></a> </td> 
 <td style="vertical-align:top;"><a href="http://my.oschina.net/Garfielt" rel="nofollow"><img alt="Garfielt" src="http://static.oschina.net/uploads/user/73/147631_50.jpg?t=1370742483000"></a> <a href="http://my.oschina.net/Garfielt" rel="nofollow">Garfielt</a> 翻译于 2年前 2人顶 顶&nbsp;翻译的不错哦! </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> 呵呵，大发现：这样只需要0.7秒！这比上面的查询快28倍，比原来的快了68倍。 通常，数据大小和类型很重要。上面的例子受益于基数中没多少换算。distinct (user_id, dashboard_id)相对于数据总量来说数量也很少。不同的对数越多，用来group和计数的唯一数据就越多——代价便会越来越大。 下一遇到长时间运行的count distinct时，尝试一些子查询来减负吧。 </td> 
 </tr> 
 </tbody> 
</table>

**数据库技术学习 [https://www.itkc8.com][https_www.itkc8.com]**

[https_www.itkc8.com]: https://www.itkc8.com