活動要寫在最前面,因為本日的干貨文章實在太長了!：)提醒超過20000字了.只好刪減一些,詳細閱讀請看云棲社區頭條.

云棲社區也有”小金人“了!送給3月云棲社區2016年第2-10期在線培訓的8位CTO大神.目前,3月4日10：00-10:40,第2期《游族網絡：如何運維千臺以上游戲云服務器》正在火熱報名.轉發”小金人“到朋友圈,并在直播期間積極留言,我們將挑選優秀留言提問者贈送技術冊本哦!

正文來了!感謝德哥的分享.重要的事情重復三遍：技術干貨,技術干貨,技術干貨!

作者：阿里云數據庫專家 德哥

來源：云棲社區

鏈接：https://yq.aliyun.com/articles/7444

轉載分享請帶上述版權聲明!

大數據正在向我們奔來.盡管業務場景不會完全相同,但在其中一個最典型場景——模糊檢索中,技術需求卻出奇的一致.

好比說：

物聯網,往往會產生大量的數據,除了數字數據,還有字符串類的數據,例如條形碼,車牌,手機號,郵箱,姓名等.假設用戶需要在大量的傳感數據中進行模糊檢索,甚至規則表達式匹配,有什么高效的辦法呢?

醫藥,市面上發現了一批藥品可能有問題,需要對藥品條碼進行規則表達式查找,找出復合條件的藥品流向.但怎么才能在如此復雜的系統中,用高效辦法來實現?

公安,偵查行動時,有可能需要線索的檢索.如用戶提供的殘缺的電話號碼,郵箱,車牌,IP地址,QQ號碼,微信號碼等進行交叉搜索,根據這些信息加上時間的疊加,模糊匹配和關聯,最終找出罪犯.但這個流程,可有高效辦法?

相同的需求還有很多.幾乎每一個模糊匹配的場景下,都必要正則表達式匹配,這和人臉拼圖有點類似,我們已經看到強烈的需求已經產生.但技術方面,要怎么做更好?

在我看來：正則匹配和模糊匹配通常是搜索引擎的特長,但是如果你使用的是PostgreSQL數據庫照樣能實現,而且性能不賴,加上分布式方案 (譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl, greenplum),處理百億以上數據量的正則匹配和模糊匹配效果杠杠的,同時還不失數據庫固有的功能,絕對是一舉多得.

首先對應用場景進行一下分類,以及現有技術下能使用的優化手段.

.1. 帶前綴的模糊查詢,例如 like 'ABC%',在PG中也可以寫成 ~ '^ABC'

可以使用btree索引優化,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

.2. 帶后綴的模糊查詢,例如 like '%ABC',在PG中也可以寫成 ~ 'ABC$'

可以使用reverse函數btree索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

.3. 不帶前綴和后綴的模糊查詢,例如 like '%AB_C%',在PG中也可以寫成 ~ 'AB.C'

可以使用pg_trgm的gin索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

.4. 正則表達式查詢,例如 ~ '[\d]+def1.?[a|b|0|8]{1,3}'

可以使用pg_trgm的gin索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

PostgreSQL pg_trgm插件自從9.1開始支持模糊查詢使用索引,從9.3開始支持規則表達式查詢使用索引,大大提高了PostgreSQL在刑偵方面的才能.

代碼見 https://github.com/postgrespro/pg_trgm_pro

pg_trgm插件的原理,將字符串前加2個空格,后加1個空格,組成一個新的字符串,并將這個新的字符串依照每3個相鄰的字符拆分成多個token.

當使用規則表達式或者模糊查詢進行匹配時,會檢索出他們的近似度,再進行filter.

GIN索引的圖例：

從btree檢索到匹配的token時,指向對應的list, 從list中存儲的ctid找到對應的記錄.

因為一個字符串會拆成很多個token,所以沒插入一條記錄,會更新多條索引,這也是GIN索引必要fastupdate的原因.

正則匹配是怎么做到的呢?

詳見 https://raw.githubusercontent.com/postgrespro/pg_trgm_pro/master/trgm_regexp.c

實際上它是將正則表達式轉換成了NFA格式,然后掃描多個TOKEN,進行bit and|or匹配.

正則組合如果轉換出來的的bit and|or很多的話,就必要大量的recheck,性能也不能好到哪里去.

下面針對以上四種場景,實例講解如何優化.

1. 帶前綴的模糊查詢,例如 like 'ABC%',在PG中也可以寫成 ~ '^ABC'

   可以使用btree索引優化,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

   例子,1000萬隨機發生的MD5數據的前8個字符.

帶前綴的模糊查詢,不使用索引必要5483毫秒.

帶前綴的模糊查詢,使用索引只必要0.5毫秒.

.2. 帶后綴的模糊查詢,例如 like '%ABC',在PG中也可以寫成 ~ 'ABC$'

可以使用reverse函數btree索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

帶后綴的模糊查詢,使用索引只必要0.5毫秒.

.3. 不帶前綴和后綴的模糊查詢,例如 like '%AB_C%',在PG中也可以寫成 ~ 'AB.C'

可以使用pg_trgm的gin索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

前后模糊查詢,使用索引只必要3.8毫秒.

.4. 正則表達式查詢,例如 ~ '[\d]+def1.?[a|b|0|8]{1,3}'

可以使用pg_trgm的gin索引,或者拆列用多列索引疊加bit and或bit or進行優化(只適合固定長度的端字符串,例如char(8)).

前后模糊查詢,使用索引只必要108毫秒.

時間主要花費在排他上面.

檢索了14794行,remove了14793行.大量的時間花費在無用功上,但是比全表掃還是好很多.

優化：

使用gin索引后,必要考慮性能問題,因為info字段被打散成了多個char(3)的token,從而涉及到非常多的索引條目,如果有非常高并發的插入,最好把gin_pending_list_limit設大,來提高插入效率,降低實時合并索引帶來的RT升高.

使用了fastupdate后,會在每次vacuum表時,自動將pengding的信息合并到GIN索引中.

還有一點,查詢不會有合并的動作,對于沒有合并的GIN信息是使用遍歷的方式搜索的.

壓測高并發的性能：

修改配置,讓數據庫的autovacuum快速迭代合并gin.

創建一個測試函數,用來產生隨機的測試數據.依照這個速度,一天能支持超過40億數據入庫.

接下來對比一下字符串分離的例子,這個例子適用于字符串長度固定,并且很小的場景,如果字符串長度不固定,這種辦法沒用.

適用splict的辦法,測試數據不盡人意,所以還是用pg_trgm比較靠譜.(省略一圖)

大數據量性能測試：

模擬分區表,每小時一個分區,每小時數據量5000萬,一天12億,一個月360億.

生成插入SQL

性能指標, 范圍掃描, 落到單表5000萬的數據量內, 毫秒級返回(詳細看圖).

單表144億的正則和模糊查詢性能測試：

測試數據后續放出,分表后做到秒級是沒有問題的.信心從何而來呢?

因為瓶頸不在IO上,主要在數據的recheck, 把144億數據拆分成29個5億的表,并行執行,秒出是有可能的.

來看一個單表5億的測試結果,秒出：

全表掃描必要,

性能對比圖表：

1000萬數據對比

5億數據對比

1000萬數據btree bit or|and與gin對比

144億分區表對比

大數據量的優化辦法,例如百億級別以上的數據量,如何能做到秒級的模糊查詢響應.

對于單機,可以使用分區,同時使用并行查詢,充分使用CPU的功能.或者使用MPP, SHARDING架構,利用多機的資源.

原則,減少recheck,盡量掃描搜索到最終必要的結果(大量掃描,大量remove checked false row, 全表和索引都存在這種現象).

如果你能看到這行字,闡明是PG的真愛!云棲社區已經組建了 PG大牛群,2016學習PG技術,從社區開始,歡迎告訴我們你的需求!