使用子查詢可提升COUNTDISTINCT速度50倍_MySQL

　　很有用，但太慢

　　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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com