前言
一直以來(lái)，對(duì)于搜索時(shí)模糊匹配的優(yōu)化一直是個(gè)讓人頭疼的問(wèn)題，好在強(qiáng)大pgsql提供了優(yōu)化方案，下面就來(lái)簡(jiǎn)單談一談如何通過(guò)索引來(lái)優(yōu)化模糊匹配
案例
我們有一張千萬(wàn)級(jí)數(shù)據(jù)的檢查報(bào)告表，需要通過(guò)檢查報(bào)告來(lái)模糊搜索某個(gè)條件，我們先創(chuàng)建如下索引：
CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);
然后搜個(gè)簡(jiǎn)單的模糊匹配條件如 LIKE "血常規(guī)%"，可以發(fā)現(xiàn)查詢(xún)計(jì)劃生成如下，索引并沒(méi)有被使用上，這是因?yàn)閭鹘y(tǒng)的btree索引并不支持模糊匹配
查閱文檔后發(fā)現(xiàn)，pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops，它們分別對(duì)應(yīng)字段類(lèi)型text、varchar和char，官方解釋為“它們與默認(rèn)操作符類(lèi)的區(qū)別是值的比較是嚴(yán)格按照字符進(jìn)行而不是根據(jù)區(qū)域相關(guān)的排序規(guī)則。這使得這些操作符類(lèi)適合于當(dāng)一個(gè)數(shù)據(jù)庫(kù)沒(méi)有使用標(biāo)準(zhǔn)“C”區(qū)域時(shí)被使用在涉及模式匹配表達(dá)式（LIKE或POSIX正則表達(dá)式）的查詢(xún)中?！?， 有些抽象，我們先試試看。創(chuàng)建如下索引并查詢(xún)剛才的條件 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)確實(shí)可以走索引掃描 ，執(zhí)行時(shí)間也從213ms優(yōu)化到125ms，但是，如果搜索LIKE "%血常規(guī)%"就又會(huì)走全表掃描了！ 這里我們引入本篇博客的主角"pg_trgm"和"pg_bigm"。
創(chuàng)建這兩個(gè)索引前分別需要引入如下兩個(gè)擴(kuò)展包 ：
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_bigm;
這兩個(gè)索引的區(qū)別是：“pg_tigm”為pgsql官方提供的索引，"pg_tigm"為日本開(kāi)發(fā)者提供。下面是詳細(xì)的對(duì)比：（參考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:
| Functionalities and Features |
pg_trgm |
pg_bigm |
| Phrase matching method for full text search |
3-gram |
2-gram |
| Available index |
GIN and GiST |
GIN only |
| Available text search operators |
LIKE (~~), ILIKE (~~*), ~, ~* |
LIKE only |
Full text search for non-alphabetic language
(e.g., Japanese) |
Not supported (*1) |
Supported |
| Full text search with 1-2 characters keyword |
Slow (*2) |
Fast |
| Similarity search |
Supported |
Supported (version 1.1 or later) |
| Maximum indexed column size |
238,609,291 Bytes (~228MB) |
107,374,180 Bytes (~102MB) |
(*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.
如無(wú)特殊要求推薦使用"pg_bigm"，我們測(cè)試一下效果：
可以使用位圖索引掃描，對(duì)于本次案例，使用pg_trgm效果同pg_bigm。
以上
本文只是簡(jiǎn)單的介紹許多細(xì)節(jié)并未做深入的分析，歡迎留言指教或者討論
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- Postgresql 存儲(chǔ)過(guò)程(plpgsql)兩層for循環(huán)的操作
- pgsql之create user與create role的區(qū)別介紹
- pgsql之pg_stat_replication的使用詳解
- pgsql 如何刪除仍有活動(dòng)鏈接的數(shù)據(jù)庫(kù)
- pgsql的UUID生成函數(shù)實(shí)例
- pgsql 如何手動(dòng)觸發(fā)歸檔
- pgsql 實(shí)現(xiàn)分頁(yè)查詢(xún)方式
