PostgreSQL給模糊搜索加索引

PostgrSQL有個模塊叫pg_trgm，可以對字符串來進行比較相似度，并通過加GIST或者GIN索引來達到提速的效果。在一般的RDBMS中這種需求都會進行全表掃描的，但是PG如果加了這個模塊，在一定場景下就可以使用索引來提速了。

一、背景
我們有一個需求根據人員的拼音碼(或者藥品的拼音碼)進行搜索，因為拼音碼不一定是全的，故通常給的方案是模糊搜索，在拼音碼的首尾兩端各加一個百分號，但是效率通常很慢，一般情況下也不建議這么做。

二、環境
OS：CentOS 6.5
DB：PostgreSQL 9.3

三、步驟
1.因為DB是通過源碼編譯的，所以創建很簡單，只要添加一個擴展

his=# create extension pg_trgm;
CREATE EXTENSION

his=# select count(1) from tbl_user;

  count  
1008215
(1 row)
his=# explain analyze select 1 from tbl_user where user_spell like '%CYL%';
                                                 QUERY PLAN                                      --------------------------------------------------------------------------------------------
 Seq Scan on tbl_user  (cost=0.00..35156.69 rows=82 width=0) (actual time=0.357..693.233 rows=1021 loops=1)
   Filter: ((user_spell)::text ~~ '%CYL%'::text)
   Rows Removed by Filter: 1007194
 Total runtime: 1193.699 ms
(4 rows)</pre>--加了索引后的查詢，提高了近10倍 
his=# create index idx_user_spell on tBL_user using gist (user gist_trgm_ops);
CREATE INDEX
his=# explain analyze  select 1 from tbl_user where user_spell like '%CYL%';
                                                          QUERY PLAN                             ---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_user  (cost=4.92..319.11 rows=82 width=0) (actual time=117.652..120.849 rows=1021 loops=1)
   Recheck Cond: ((user_spell)::text ~~ '%CYL%'::text)
   Rows Removed by Index Recheck: 2
   ->  Bitmap Index Scan on idx_user_spell  (cost=0.00..4.90 rows=82 width=0) (actual time=117.291..117.291 rows=1023 loops=1)
         Index Cond: ((user_spell)::text ~~ '%CYL%'::text)
 Total runtime: 121.098 ms
(6 rows)
四、說明 

可以看出來模糊搜索也走了索引，速度有了很大提升，COST也減小很多。這個模塊在官網上可以看到有幾個自帶的函數，主要示例如下： 

1.similarity(text,text) 

這個函數是用來比較兩個字符串的相近程度的，取值范圍在0-1之間，完全相同為1，完全不同則為0 
his=# select similarity('123','789');

 similarity 

      0

(1 row)
his=# select similarity('123','123');
 similarity 

      1

(1 row)
his=# select similarity('123','12345');
 similarity 

    0.428571

(1 row)
--和相似度相反的是他的操作符<->，這個操作符表示的是兩組字符串的一個距離，如果是一樣的，則是重合的，距離為0，如果完全不同，則為1，算法實際就是1減去上面這個相似值，比如以下例子：
his=# select '123'<->'123','123'<->'12345','123'<->'678';
 ?column? | ?column? | ?column? 
----------+----------+----------
        0 | 0.571429 |        1
(1 row)</pre>2.show_trgm(text) 這個函數返回的一串字符數組，有點類似于全文檢索的分詞，可以用這個函數來做一些Debug 
his=# select show_trgm('123'),show_trgm('1234');
        show_trgm        |          show_trgm

-------------------------+-----------------------------
 {"  1"," 12",123,"23 "} | {"  1"," 12",123,234,"34 "}
(1 row)

--上面的相似度就是用的這個分詞分出來的，比如123和1234，相同的值有3個，總的不同值有6個，所以相似度是3/6=0.5
--這個函數可以看出對字符數字能有些進行切割，但是對漢字暫時還無能為力，有一定的限制
his=# select show_trgm('中國人民'),show_trgm('中國人民12');
 show_trgm |      show_trgm

-----------+---------------------
 {}        | {"  1"," 12","12 "}
(1 row)
his=# select similarity('中國人','日本'),similarity('中國人','中國人');
 similarity | similarity 
------------+------------
          0 |          0
(1 row)</pre>五、優點與不足 

1.使用這個模塊可以對需要使用模糊檢索字符串的數據進行加索引提速 

2.對字母或數字的相似度比較較為滿意，對漢字還不支持 

3.如果模糊檢索的數據結果集較大，運行速度可能比較慢，比如只搜索一個字母匹配的 %C% 



六、參考 

1.http://www.postgresql.org/docs/9.4/static/pgtrgm.html 2.http://blog.163.com/digoal@126/blog/static/163877040201191882553803/