計算非重覆結果數目，使用子查詢快了50倍

本文說的這個技術是通用的，但為瞭解釋說明，我們選用了 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資料分析更快的工具。我們在這裡分享一下我們的工具蘊含的演演算法和技術。你可以到我們的主頁上註冊，從而作為我們的新客戶，我們可以通知你相關事宜。

本文由 伯樂線上 – sunbiaobiao 翻譯。
英文出處：Periscope

譯文連結：http://blog.jobbole.com/59177/