如何使用子查询提升 COUNT DISTINCT 速度 ？

作者:课课家教育更新于： 2017-05-16 10:45:12

　　在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。上层的查询块曾为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中，以便确定最后的输出。

　　SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。以层层嵌套的方式来构造程序正是SQL中"结构化"的含义所在。

　　子查询是本质上就是一个完整的SELECT语句，它可以使一个SELECT、SELECT...INTO语句、INSERT...INTO语句、DELETE语句、或UPDATE语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

　　使用子查询的规则：

　　1）子查询必须“自身就是一个完整的查询”。即，它必须至少包括一个SELECT子句和FROM子句。

　　2）子查询SELECT语句不能包括在ORDERBY子句中。因为ORDERBY字句只能对最终查询结果排序，如果显示的输出需要按照特定顺序显示，那么ORDERBY子句应该作为外部查询的最后一个子句列出。

　　3）子查询“必须包括在一组括号中”，以便将它与外部查询分开。

　　4）如果将子查询放在外部查询的WHERE或HAVING子句中，那么该子查询只能位于比较运算符的“右边”。

　　看完子查询的相关介绍，那么如何使用子查询提升COUNTDISTINCT速度呢？下面给大家举个例子：

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

　　很有用，但太慢

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

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

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

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

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

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

　　先聚合，然后Join

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

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

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

　　首先，缩小数据集

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

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

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

　　呵呵，大发现：这样只需要0.7秒！这比上面的查询快28倍，比原来的快了68倍。

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

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

　　小编结语：

　　更多内容尽在课课家教育！