歡迎光臨
每天分享高質量文章

計算非重覆結果數目,使用子查詢快了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/

赞(0)

分享創造快樂