使用局部索引來提升 PostgreSQL 的效能

使用局部索引來提升 PostgreSQL 的效能

大家可能還不知道 PostgreSQL 支援對錶資料進行局部索引吧?  它的好處是既能加快這部分索引過的資料的讀取速度, 又不會增加額外開銷.  對於那些反覆根據給定的 WHERE 子句讀出來的資料, 最好的辦法就是對這部分資料索引. 這對某些需要預先進行聚集計算的特定分析工作流程來說, 很合適. 本帖中, 我將舉一個例子說明如何通過部分索引最佳化資料查詢.

假設有這樣一個事件表, 結構如下:

每個事件關聯一個使用者, 有一個 ID, 一個時間戳記, 和一個描述事件的 JSON. JSON 的內容包含頁面的路徑, 事件的類別 (如: 單擊, 網頁瀏覽, 表單提交), 以及其他跟事件相關的屬性。

我們使用這個表格儲存體各種事件記錄. 假設我們手上有個事件自動跟蹤器 , 能自動記錄使用者的每一個點擊, 每一次頁面瀏覽, 每一次表單提交, 以便我們以後做分析. 再假設我們想做個內部用的報表(internal dashboard)顯示一些有價值的資料(high-value metrics), 如:每周的註冊數量, 每天應收帳款. 那麼, 問題就來了. 跟這個報表相關的事件, 只佔該事件表資料的一小部分 -- 網站的點擊量雖然很高, 但是只有很小一部分最終成交! 而這一小部分成交資料跟其他資料混雜放在一起, 也就是說, 它的信噪比很低. 

我們現在想提高報表查詢的速度.[1]  先說註冊事件吧, 我們把它定義為:註冊頁面(/signup/)的一次表單提交. 要獲得九月份第一周的註冊數量, 可以理解成:

對一個包含1千萬條記錄, 其中只有 3000 條是註冊記錄, 並且沒有做過索引的資料集, 執行這樣的查詢需要 45 秒.

對單列做全索引(Full Indexes) : 大雜燴

提高查詢速度, 比較傻的辦法是: 給事件相關的各種屬性建立單列索引(single-column index):(data->>'type'),(data->>'path'), 和 time. 通過 bitmap,  我們可以把這三個索引掃描結果合并起來.  如果我們只是有選擇地查詢其中一部分資料, 而且相關索引依然存在記憶體中, 查詢的速度會變得很快.  剛開始查詢大概用 200 毫秒, 後面會降到 20 毫秒 — 比起要花 45 秒查詢的順序掃描, 確實有明顯的提高.

這種索引方式有幾個弊端:

• 資料寫入的開銷. 這種方式在每次 INSERT/UPDATE/DELETE 操作的時候, 需要修改這三個索引的資料.[2]  導致像本例這樣頻需要繁寫入資料的更新資料操作代價太高.

• 資料查詢的限制. 這種方式同時也限制了我們自訂有價值(high-value)事件類型的能力. 比方說, 我們無法在 JSON 欄位上做比範圍查詢更複雜的查詢. 具體如:通過Regex搜尋, 或者尋找路徑是/signup/ 開頭的頁面.

• 磁碟空間的使用. 本例中的提到的表占 6660 mb 磁碟空間, 三個索引和起來有 1026 mb, 隨著時間的推移, 這些數字還會不斷的暴漲.[3]

局部索引（Partial Indexes）

我們分析用的註冊事件，只佔了表中全部資料的 0.03%。而全索引是對全部資料進行索引， 顯然不合適。要提高查詢速度， 最好的辦法是用局部索引。

以我們對註冊事件的定義為過濾條件，建立一個無關列(unrelated column)索引，通過該索引，PostgreSQL 很容易找到註冊事件所在的行，查詢速度自然要比在相關欄位的3個全索引快的多。 尤其是對時間欄位進行局部索引。具體用法如下:

CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

這個索引的查詢速度，會從剛開始的 200 毫秒, 降到 2 毫秒。只要多執行查詢語句，速度自然就會加快。更重要的是，局部索引解決了前面提到的全索引的幾個缺點。

• 索引只佔 96 kb 磁碟空間, 是全索引的 1026 mb 的 1/10000。

• 只有新增的行符合註冊事件的過濾條件， 才更新索引。由於合格事件只有 0.03%，資料寫入的效能得到很大的提高: 基本上，建立和更新這樣的索引沒有太大的開銷。

• 這樣的局部合并(partial join) 允許我們使用 PostgreSQL 提供的各種運算式作為過濾條件。索引中用到的 WHERE 子句，跟在查詢語句中的用法沒什麼兩樣， 所以我們可以寫出很複雜的過濾條件。 如：Regex， 函數返回結果，前面提到的首碼匹配。

不要索引結果是布爾值的斷言

我見過有人直接索引布林運算式：

(data->>'type') = 'submit' AND (data->>'path') = '/signup/'

，然後把時間欄位放在第二項. 如：

CREATE INDEX event_signup_time ON event
(((data->>'type') = 'submit' AND (data->>'path') = '/signup/'), time)

這樣做的後果，比上面兩種方法還要嚴重，因為 PostgreSQL 的查詢規劃器（query planner）不會將這個布林運算式當作過濾條件。也就是說，規劃器不會把它當作 WHERE 語句：

WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

所以，我們索引的欄位：

((data->>'type') = 'submit' AND (data->>'path') = '/signup/')

的值始終為 true。 當我們用這個索引當作條件過濾事件的時候，不管運算式的結果是 true 還是 false，都會先把事件數目據讀出來，載入完後，再過濾。[4]

這麼一來, 索引的時候會從磁碟中讀取許多不必要的資料, 此外也要檢查每一行資料的有效性. 拿我們例子中的資料集來說, 這樣的查詢第一次要 25 秒, 之後會降到 8 秒.  這樣的結果比索引整個時間欄位還要差一些.

局部索引能在很大程度上, 提高那些通過斷言過濾出表中一部分資料的查詢的速度. 對於以流量論英雄(Judging by traffic )的 #postgresql IRC 來說, 局部索引顯得有些資源利用不足. 對比全索引, 局部索引有適用範圍更廣的斷言(greater range of predicates), 配合高選擇性過濾條件(highly selective filters), 寫操作和磁碟空間會變得更少. 要是你經常查詢某個表中的一小部分資料, 應當優先考慮局部索引.

是不是開始愛上 PostgreSQL 了?  要瞭解它的各種功能和特點, 請移步到這裡 @danlovesproofs.

想不想將強大的技術變得更便於使用? 有興趣就給我們發郵件 jobs@heapanalytics.com.

[1] 這種問題可以通過對錶分區來解決. 把表中有價資料(high-value events)和其他資料分開放在不同的子表中. 不過, 如果有價值資料的種類比較多, 這種方法就不適用, 因為, 每次添加一個新的有價值資料的種類, 都要重新對錶分區.
[2] 通過 heap-only tuples 最佳化, 可以大大的降低更新操作的開銷, 但是, 每次 INSERT 或 DELETE 操作依然需要更新3個索引. 
[3] 我們可以通過建一個 '多列索引' 對 3 個欄位同時索引. 如: on((data->>'type'), (data->>'path'), time). 這個索引占 755 mb 磁碟空間, 比建 3 個索引用的磁碟空間也就少了 26%, 而且其他問題依然存在. 此外, 這樣的索引可能對同樣資料的其他查詢, 沒什麼用處. 所以, 如果我們有幾種不同的類型的有價值資料, 節省磁碟空間這點優勢也就不存在了. 
[4] 相關的查詢規劃(The relevant query plan):

------------------------------------華麗麗的分割線------------------------------------

CentOS 6.3環境下yum安裝PostgreSQL 9.3

PostgreSQL緩衝詳述

Windows平台編譯 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)環境的配置與安裝

Ubuntu上的phppgAdmin安裝及配置

CentOS平台下安裝PostgreSQL9.3

PostgreSQL配置Streaming Replication叢集

如何在CentOS 7/6.5/6.4 下安裝PostgreSQL 9.3 與 phpPgAdmin 

------------------------------------華麗麗的分割線------------------------------------

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡

本文永久更新連結地址：