大家可能還不知道 PostgreSQL 支持對(duì)表數(shù)據(jù)進(jìn)行局部索引吧? 它的好處是既能加快這部分索引過(guò)的數(shù)據(jù)的讀取速度, 又不會(huì)增加額外開(kāi)銷. 對(duì)于那些反復(fù)根據(jù)給定的 WHERE 子句讀出來(lái)的數(shù)據(jù), 最好的辦法就是對(duì)這部分?jǐn)?shù)據(jù)索引. 這對(duì)某些需要預(yù)先進(jìn)行聚集計(jì)算的特定分析工作流來(lái)說(shuō), 很合適. 本帖中, 我將舉一個(gè)例子說(shuō)明如何通過(guò)部分索引優(yōu)化數(shù)據(jù)查詢.

假設(shè)有這樣一個(gè)事件表, 結(jié)構(gòu)如下:

每個(gè)事件關(guān)聯(lián)一個(gè)用戶, 有一個(gè) ID, 一個(gè)時(shí)間戳, 和一個(gè)描述事件的 JSON. JSON 的內(nèi)容包含頁(yè)面的路徑, 事件的類別 (如: 單擊, 網(wǎng)頁(yè)瀏覽, 表單提交), 以及其他跟事件相關(guān)的屬性。

我們使用這個(gè)表存儲(chǔ)各種事件日志. 假設(shè)我們手上有個(gè)事件自動(dòng)跟蹤器 , 能自動(dòng)記錄用戶的每一個(gè)點(diǎn)擊, 每一次頁(yè)面瀏覽, 每一次表單提交, 以便我們以后做分析. 再假設(shè)我們想做個(gè)內(nèi)部用的報(bào)表(internal dashboard)顯示一些有價(jià)值的數(shù)據(jù)(high-value metrics), 如:每周的注冊(cè)數(shù)量, 每天應(yīng)收帳款. 那么, 問(wèn)題就來(lái)了. 跟這個(gè)報(bào)表相關(guān)的事件, 只占該事件表數(shù)據(jù)的一小部分 -- 網(wǎng)站的點(diǎn)擊量雖然很高, 但是只有很小一部分最終成交! 而這一小部分成交數(shù)據(jù)跟其他數(shù)據(jù)混雜放在一起, 也就是說(shuō), 它的信噪比很低.

我們現(xiàn)在想提高報(bào)表查詢的速度。先說(shuō)注冊(cè)事件吧，我們把它定義為：注冊(cè)頁(yè)面(/signup/)的一次表單提交。要獲得九月份第一周的注冊(cè)數(shù)量，可以理解成：

對(duì)一個(gè)包含1千萬(wàn)條記錄, 其中只有 3000 條是注冊(cè)記錄, 并且沒(méi)有做過(guò)索引的數(shù)據(jù)集, 執(zhí)行這樣的查詢需要 45 秒.

提高查詢速度, 比較傻的辦法是: 給事件相關(guān)的各種屬性創(chuàng)建單列索引(single-column index):(data->>'type'),(data->>'path'), 和 time. 通過(guò) bitmap, 我們可以把這三個(gè)索引掃描結(jié)果合并起來(lái). 如果我們只是有選擇地查詢其中一部分?jǐn)?shù)據(jù), 而且相關(guān)索引依然存在內(nèi)存中, 查詢的速度會(huì)變得很快. 剛開(kāi)始查詢大概用 200 毫秒, 后面會(huì)降到 20 毫秒 — 比起要花 45 秒查詢的順序掃描, 確實(shí)有明顯的提高.

這種索引方式有幾個(gè)弊端:

數(shù)據(jù)寫(xiě)入的開(kāi)銷. 這種方式在每次 INSERT/UPDATE/DELETE 操作的時(shí)候, 需要修改這三個(gè)索引的數(shù)據(jù). 導(dǎo)致像本例這樣頻需要繁寫(xiě)入數(shù)據(jù)的更新數(shù)據(jù)操作代價(jià)太高.

數(shù)據(jù)查詢的限制. 這種方式同時(shí)也限制了我們自定義有價(jià)值(high-value)事件類型的能力. 比方說(shuō), 我們無(wú)法在 JSON 字段上做比范圍查詢更復(fù)雜的查詢. 具體如:通過(guò)正則表達(dá)式搜索, 或者查找路徑是/signup/ 開(kāi)頭的頁(yè)面.

磁盤(pán)空間的使用. 本例中的提到的表占 6660 mb 磁盤(pán)空間, 三個(gè)索引和起來(lái)有 1026 mb, 隨著時(shí)間的推移, 這些數(shù)字還會(huì)不斷的暴漲.

我們分析用的注冊(cè)事件，只占了表中全部數(shù)據(jù)的 0.03%。而全索引是對(duì)全部數(shù)據(jù)進(jìn)行索引， 顯然不合適。要提高查詢速度， 最好的辦法是用局部索引。

以我們對(duì)注冊(cè)事件的定義為過(guò)濾條件，創(chuàng)建一個(gè)無(wú)關(guān)列(unrelated column)索引，通過(guò)該索引，PostgreSQL 很容易找到注冊(cè)事件所在的行，查詢速度自然要比在相關(guān)字段的3個(gè)全索引快的多。 尤其是對(duì)時(shí)間字段進(jìn)行局部索引。具體用法如下:

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

這個(gè)索引的查詢速度，會(huì)從剛開(kāi)始的 200 毫秒, 降到 2 毫秒。只要多運(yùn)行查詢語(yǔ)句，速度自然就會(huì)加快。更重要的是，局部索引解決了前面提到的全索引的幾個(gè)缺點(diǎn)。

索引只占 96 kb 磁盤(pán)空間, 是全索引的 1026 mb 的 1/10000。

只有新增的行符合注冊(cè)事件的過(guò)濾條件， 才更新索引。由于符合條件的事件只有 0.03%，數(shù)據(jù)寫(xiě)入的性能得到很大的提高: 基本上，創(chuàng)建和更新這樣的索引沒(méi)有太大的開(kāi)銷。

這樣的局部合并(partial join) 允許我們使用 PostgreSQL 提供的各種表達(dá)式作為過(guò)濾條件。索引中用到的 WHERE 子句，跟在查詢語(yǔ)句中的用法沒(méi)什么兩樣， 所以我們可以寫(xiě)出很復(fù)雜的過(guò)濾條件。 如：正則表達(dá)式， 函數(shù)返回結(jié)果，前面提到的前綴匹配。

我見(jiàn)過(guò)有人直接索引布爾表達(dá)式：

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

，然后把時(shí)間字段放在第二項(xiàng). 如：

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

這樣做的后果，比上面兩種方法還要嚴(yán)重，因?yàn)?PostgreSQL 的查詢規(guī)劃器（query planner）不會(huì)將這個(gè)布爾表達(dá)式當(dāng)作過(guò)濾條件。也就是說(shuō)，規(guī)劃器不會(huì)把它當(dāng)作 WHERE 語(yǔ)句：

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

所以，我們索引的字段：

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

的值始終為 true。 當(dāng)我們用這個(gè)索引當(dāng)作條件過(guò)濾事件的時(shí)候，不管表達(dá)式的結(jié)果是 true 還是 false，都會(huì)先把事件數(shù)據(jù)讀出來(lái)，加載完后，再過(guò)濾。

這么一來(lái), 索引的時(shí)候會(huì)從磁盤(pán)中讀取許多不必要的數(shù)據(jù), 此外也要檢查每一行數(shù)據(jù)的有效性. 拿我們例子中的數(shù)據(jù)集來(lái)說(shuō), 這樣的查詢第一次要 25 秒, 之后會(huì)降到 8 秒. 這樣的結(jié)果比索引整個(gè)時(shí)間字段還要差一些.

局部索引能在很大程度上, 提高那些通過(guò)斷言過(guò)濾出表中一部分?jǐn)?shù)據(jù)的查詢的速度. 對(duì)于以流量論英雄(Judging by traffic )的 #postgresql IRC 來(lái)說(shuō), 局部索引顯得有些資源利用不足. 對(duì)比全索引, 局部索引有適用范圍更廣的斷言(greater range of predicates), 配合高選擇性過(guò)濾條件(highly selective filters), 寫(xiě)操作和磁盤(pán)空間會(huì)變得更少. 要是你經(jīng)常查詢某個(gè)表中的一小部分?jǐn)?shù)據(jù), 應(yīng)當(dāng)優(yōu)先考慮局部索引