前言

一直以來，對于搜索時模糊匹配的優(yōu)化一直是個讓人頭疼的問題，好在強大pgsql提供了優(yōu)化方案，下面就來簡單談一談如何通過索引來優(yōu)化模糊匹配

案例

我們有一張千萬級數(shù)據(jù)的檢查報告表，需要通過檢查報告來模糊搜索某個條件，我們先創(chuàng)建如下索引：

CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);

然后搜個簡單的模糊匹配條件如 LIKE "血常規(guī)%"，可以發(fā)現(xiàn)查詢計劃生成如下，索引并沒有被使用上，這是因為傳統(tǒng)的btree索引并不支持模糊匹配

查閱文檔后發(fā)現(xiàn)，pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops，它們分別對應字段類型text、varchar和char，官方解釋為“它們與默認操作符類的區(qū)別是值的比較是嚴格按照字符進行而不是根據(jù)區(qū)域相關的排序規(guī)則。這使得這些操作符類適合于當一個數(shù)據(jù)庫沒有使用標準“C”區(qū)域時被使用在涉及模式匹配表達式（LIKE或POSIX正則表達式）的查詢中。”， 有些抽象，我們先試試看。創(chuàng)建如下索引并查詢剛才的條件 LIKE"血常規(guī)%"：（參考pgsql的文檔https://www.postgresql.org/docs/10/indexes-opclass.html）

CREATE INDEX lab_report_report_name_index ON lab.lab_report (report_name varchar_pattern_ops);

發(fā)現(xiàn)確實可以走索引掃描 ，執(zhí)行時間也從213ms優(yōu)化到125ms，但是，如果搜索LIKE "%血常規(guī)%"就又會走全表掃描了！ 這里我們引入本篇博客的主角"pg_trgm"和"pg_bigm"。

創(chuàng)建這兩個索引前分別需要引入如下兩個擴展包 ：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_bigm;

這兩個索引的區(qū)別是：“pg_tigm”為pgsql官方提供的索引，"pg_tigm"為日本開發(fā)者提供。下面是詳細的對比：（參考pg_bigm的文檔http://pgbigm.osdn.jp/pg_bigm_en-1-2.html）

Comparison with pg_trgm

Thepg_trgmcontrib module which provides full text search capability using 3-gram (trigram) model is included in PostgreSQL. The pg_bigm was developed based on the pg_trgm. They have the following differences:

(*1) You can use full text search for non-alphabetic language by commenting out KEEPONLYALNUM macro variable in contrib/pg_trgm/pg_trgm.h and rebuilding pg_trgm module. But pg_bigm provides faster non-alphabetic search than such a modified pg_trgm.

(*2) Because, in this search, only sequential scan or index full scan (not normal index scan) can run.

pg_bigm 1.1 or later can coexist with pg_trgm in the same database, but pg_bigm 1.0 cannot.

如無特殊要求推薦使用"pg_bigm"，我們測試一下效果：

可以使用位圖索引掃描，對于本次案例，使用pg_trgm效果同pg_bigm。

以上

本文只是簡單的介紹許多細節(jié)并未做深入的分析，歡迎留言指教或者討論

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。

最新評論