PostgreSQL 十億級模糊查詢最佳實踐

postgresql , 模糊查詢 , 正則查詢 , pg_trgm , bytea , gin , 函數索引

前模糊(有字首的模糊)，後模糊(有字尾的模糊)，前後模糊(無前字尾的模糊)，正則比對都屬于文本搜尋領域常見的需求。

postgresql擁有很強的文本搜尋能力，除了支援全文檢索，還支援模糊查詢、正則查詢。内置的pg_trgm插件是一般資料庫沒有的，可能很多人沒有聽說過。同時還内置了表達式索引、gin索引的功能。

不同的模糊查詢需求，有不同的優化方法。

對于前模糊和後模糊，postgresql則與其他資料庫一樣，可以使用btree來加速。後模糊可以使用反轉函數的函數索引來加速。

對于前後模糊和正則比對，一種方法是使用pg_trgm插件，利用gin索引加速模糊和正則查詢（輸入3個或3個以上字元的模糊查詢效果很好）。另一種方法是自定義gin表達式索引的方法，适合于定制的模糊查詢。

1. 前模糊(有字首的模糊)優化方法

使用b-tree可以支援前模糊的查詢。

1.1 當使用類型預設的index ops class時，僅适合于collate="c"的查詢（當資料庫預設的lc_collate<>c時，索引和查詢都需要明确指定collate "c"）。

索引、查詢條件的collate必須一緻才能使用索引。

例子

1.2 當資料庫預設的lc_collate<>c時，還有一種方法讓b-tree索引支援模糊查詢。使用對應類型的pattern ops，使用pattern ops将使用字元的查詢方式而非binary的搜尋方式。

文檔中有如下解釋

<a href="https://www.postgresql.org/docs/9.6/static/indexes-opclass.html">https://www.postgresql.org/docs/9.6/static/indexes-opclass.html</a>

使用類型對應的pattern ops，索引搜尋不僅支援like的寫法，還支援規則表達式的寫法，如下：

2. 後模糊(有字尾的模糊)的優化方法

使用反轉函數(reverse)索引，可以支援後模糊的查詢。

2.1 當使用類型預設的index ops class時，僅适合于collate="c"的查詢（當資料庫預設的lc_collate&lt;&gt;c時，索引和查詢都需要明确指定collate "c"）。

2.2 當資料庫預設的lc_collate&lt;&gt;c時，還有一種方法讓b-tree索引支援模糊查詢。使用對應類型的pattern ops，使用pattern ops将使用字元的查詢方式而非binary的搜尋方式。

使用類型對應的pattern ops，索引搜尋不僅支援like的寫法，還支援規則表達式的寫法。

3. 前、後模糊的合體優化方法

使用pg_trgm索引，可以支援前、後模糊的查詢。

注意：

(有字首的模糊)至少輸入1個字元，(有字尾的模糊)至少輸入2個字元，才有好的索引過濾效果。

如果要高效支援多位元組字元(例如中文)，資料庫lc_ctype不能為"c"，隻有token分割正确效果才是ok的。(因為lc_ctype決定了多位元組字元中什麼是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

生成随機中文字元串的函數

生成随機資料

模糊查詢

使用pg_trgm插件，支援前後模糊的查詢。

如果要讓pg_trgm高效支援多位元組字元(例如中文)，資料庫lc_ctype不能為"c"，隻有token分割正确效果才是ok的。(因為lc_ctype決定了多位元組字元中什麼是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

建議輸入3個或3個以上字元，否則效果不佳（後面會分析原因）。

postgresql 正則比對的文法為 <code>字元串 ~ 'pattern'</code> 或 <code>字元串 ~* 'pattern'</code>

<a href="https://www.postgresql.org/docs/9.6/static/functions-matching.html">https://www.postgresql.org/docs/9.6/static/functions-matching.html</a>

正則比對索引原理參考contrib/pg_trgm/trgm_regexp.c

首先，pg_trgm将字元串的前端添加2個空格，末尾添加1個空格。

然後，每連續的3個字元為一個token，拆開。

最後，對token建立gin反向索引。

檢視字元串的token，可以使用如下方法。

使用pg_trgm時，如果要獲得最好的效果，最好滿足這些條件。

1. 有字首的模糊查詢，例如a%，至少需要提供1個字元。( 搜尋的是token=' a' )

2. 有字尾的模糊查詢，例如%ab，至少需要提供2個字元。( 搜尋的是token='ab ' )

3. 前後模糊查詢，例如%abcd%，至少需要提供3個字元。( 這個使用數組搜尋，搜尋的是token(s) 包含 {" a"," ab",abc,bcd,"cd "} )

原因是什麼呢？

因為pg_trgm生成的token是三個字元，隻有在以上三個條件下，才能比對到對應的token。

當需要前後模糊搜尋1個或者2個字元時，pg_trgm無法滿足需求，但是我們可以使用表達式gin索引。

使用表達式，将字元串拆成1個單字，兩個連續的字元的數組，對數組建立gin索引即可。

模糊查詢和正則比對都是找出完全符合條件的記錄，還有一種需求是相似查詢。

例如postgresql字元串，輸入 p0stgresgl 也能根據相似度比對到。

這裡同樣用到了pg_trgm插件，如果要支援中文，同樣有這樣的要求：

如果需要讓pg_trgm支援中文相似查詢，資料庫lc_ctype不能為"c"，隻有token分割正确效果才是ok的。(因為lc_ctype決定了多位元組字元中什麼是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

1. 如果隻有前模糊查詢需求(字元串 like 'xx%')，使用collate "c"的b-tree索引；當collate不為"c"時，可以使用類型對應的pattern ops(例如text_pattern_ops)建立b-tree索引。

2. 如果隻有後模糊的查詢需求(字元串 like '%xx' 等價于 reverse(字元串) like 'xx%')，使用collate "c"的reverse()表達式的b-tree索引；當collate不為"c"時，可以使用類型對應的pattern ops(例如text_pattern_ops)建立b-tree索引。

3. 如果有前後模糊查詢需求，并且包含中文，請使用lc_ctype &lt;&gt; "c"的資料庫，同時使用pg_trgm插件的gin索引。(隻有token分割正确效果才是ok的。(因為lc_ctype決定了多位元組字元中什麼是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。)

4. 如果有前後模糊查詢需求，并且不包含中文，請使用pg_trgm插件的gin索引。

5. 如果有正規表達式查詢需求，請使用pg_trgm插件的gin索引。

6. 如果有輸入條件少于3個字元的模糊查詢需求，可以使用gin表達式索引，通過數組包含的方式進行搜尋，性能一樣非常好。

1億條記錄，每條記錄15個随機中文。測試前後模糊查詢性能。

1. 生成測試資料

2. 建立索引

表和索引大小

3. 模糊查詢性能測試

3.1 前模糊

響應時間：9毫秒

傳回4701行

3.2 後模糊

響應時間：0.25毫秒

傳回2行

3.3 前後模糊

響應時間：0.2毫秒

傳回1行