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

　　注：這些技術是通用的，只不過我們選擇使用Postgres的語法。使用獨特的pgAdminIII生成解釋圖形。

　　很有用，但太慢

　　Count distinct是SQL分析時的禍根，因此它是我第一篇部落格的不二選擇。

　　首先：如果你有一個大的且能夠容忍不精確的資料集，那像HyperLogLog這樣的概率計數器應該是你最好的選擇。（我們會在以後的部落格中談到HyperLogLog。）但對於需要快速、精準答案的查詢，一些簡單的子查詢可以節省你很多時間。

　　讓我們以我們一直使用的一個簡單查詢開始：哪個圖表的使用者訪問量最大？

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解析：

　　它慢是因為資料庫遍歷了所有日誌以及所有的圖表，然後join它們，再將它們排序，這些都在真正的group和分組和聚合工作之前。

　　先聚合，然後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倍。再次通過解析來看一下原因：

　　正如設計的，group-聚合在join之前。而且，額外的我們可以利用time_on_site_logs表裡的索引。

　　首先，縮小資料集

　　我們可以做的更好。通過在整個日誌表上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。

　　呵呵，大發現：這樣只需要0.7秒！這比上面的查詢快28倍，比原來的快了68倍。

　　通常，資料大小和型別很重要。上面的例子受益於基數中沒多少換算。distinct (user_id, dashboard_id)相對於資料總量來說數量也很少。不同的對數越多，用來group和計數的唯一資料就越多——代價便會越來越大。

　　下一遇到長時間執行的count distinct時，嘗試一些子查詢來減負吧。

　　原文地址：https://periscope.io/blog/use-subqueries-to-count-distinct-50x-faster.html