開始

安裝

首先是安裝 PgSQL，這里我使用的是 PgSQL 9.6，PgSQL 10 也剛發(fā)布了，有興趣的可以嘗下鮮。

PgSQL 的安裝可以說(shuō)非常復(fù)雜了，除了要安裝 Server 和 Client 外，還需要安裝 devel 包。為了實(shí)現(xiàn)空間索引功能，我們還要安裝最重要的 PostGIS 插件，此插件需要很多依賴，自己手動(dòng)安裝非常復(fù)雜而且很可能出錯(cuò)。

推薦自動(dòng)化方式安裝，Yum 一定要配合 epel 這樣的 Yum 源，保障能將依賴一網(wǎng)打盡。當(dāng)然最好的還是使用 docker 來(lái)運(yùn)行，找個(gè)鏡像就行了。

插件

由于 PgSQL 的很多功能都由插件實(shí)現(xiàn)，所以還要安裝一些常用的插件，如:

postgis_topology（管理面、邊、點(diǎn)等拓?fù)鋵?duì)象）

pgrouting（路徑規(guī)劃）

postgis_sfcgal（實(shí)現(xiàn)3D相關(guān)算法）

fuzzystrmatch（字符串相似度計(jì)算）

address_standardizer/address_standardizer_data_us（地址標(biāo)準(zhǔn)化）

pg_trgm（分詞索引）

這些插件在安裝目錄 /path/extensions 下編譯完畢后，在數(shù)據(jù)庫(kù)中使用前要先使用 create extension xxx 啟用。

啟動(dòng)

1.切換到非 root 用戶。（PgSQL 在安裝完畢后會(huì)創(chuàng)建一個(gè)名為 postgres 的超級(jí)用戶，我們可以使用這個(gè)超級(jí)用戶來(lái)操作 PgSQL，后期建議重新創(chuàng)建一個(gè)普通用戶用來(lái)管理數(shù)據(jù)）；

2.切換到 /installPath/bin/ 目錄下，PgSQL 在此目錄下提供了很多命令，如 createdb、createuser、dropdb、pg_dump 等；

3.使用 createdb 命令初始化一個(gè)文件夾 dir_db (此目錄不能已存在)存放數(shù)據(jù)庫(kù)物理數(shù)據(jù)，使用 -E UTF8 參數(shù)指定數(shù)據(jù)庫(kù)字符集為 utf-8；

4.使用 pg_ctl -D dir_db 指定數(shù)據(jù)庫(kù)啟動(dòng)后臺(tái)服務(wù)；

5.使用 psql -d db 在命令行登陸 PgSQL;

配置

安裝完畢后還要配置一些比較基本的參數(shù)才能正常使用。

Host權(quán)限

PgSQL需要在 pg_hba.conf 文件中配置數(shù)據(jù)庫(kù) Host 權(quán)限，才能被其他機(jī)器訪問(wèn)。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     trust

host    all             all             127.0.0.1/32            md5

host    all             all             172.16.0.1/16            md5

文件中注釋部分對(duì)這幾個(gè)字段介紹得比較詳細(xì)， 我們很可能需要添加 host(IP) 訪問(wèn)項(xiàng)， ADDRESS 是普通的網(wǎng)段表示法，METHOD 推薦使用 md5，表示使用 md5 加密傳輸密碼。

服務(wù)器配置

服務(wù)器配置在 postgresql.conf中，修改配置后需要 使用 pg_ctl restart -D dir_db 命令重啟數(shù)據(jù)庫(kù)；

此外，我們也可以在登陸數(shù)據(jù)庫(kù)后修改配置項(xiàng)：使用 SELECT * FROM pg_settings WHERE name = 'config'; 查詢當(dāng)前配置項(xiàng)，再使用 UPDATE 語(yǔ)句更新配置。但有些配置如內(nèi)存分配策略是只在當(dāng)前 session 生效的，全局生效需要在配置文件中修改，再重啟服務(wù)器。

我們可以修改配置并用客戶端驗(yàn)證 SQL 語(yǔ)句的優(yōu)化，使用 \timing on 開啟查詢計(jì)時(shí)，使用 EXPLAIN ANALYSE 語(yǔ)句 分析查詢語(yǔ)句效率。 下面介紹兩個(gè)已實(shí)踐過(guò)的配置參數(shù)：

• shared_buffers：用于指定共享內(nèi)存緩沖區(qū)所占用的內(nèi)存量。它應(yīng)該足夠大來(lái)存儲(chǔ)常使用的查詢結(jié)果，以減少物理I/O。但它也不能太大，以避免系統(tǒng) 內(nèi)存swap 的發(fā)生， 一般設(shè)置為系統(tǒng)內(nèi)存的 20%。
• work_mem：一個(gè)連接的工作內(nèi)存，在查詢結(jié)果數(shù)據(jù)量較大時(shí)，此值如果較小的話，會(huì)導(dǎo)致大量系統(tǒng) I/O，導(dǎo)致查詢速度急劇下降，如果你的 explain 語(yǔ)句內(nèi) buffer 部分 read數(shù)值過(guò)大，則表示工作內(nèi)存不足，需要調(diào)整加此參數(shù)。但此值也不能太大，需要保證 work_mem * max_connections + shared_buffers + 系統(tǒng)內(nèi)存 RAM，不然同樣可能會(huì)導(dǎo)致系統(tǒng) 內(nèi)存swap。

這樣，PgSQL 就能作為一個(gè)正常的關(guān)系型數(shù)據(jù)使用了。

分詞

全文索引的實(shí)現(xiàn)要靠 PgSQL 的 gin 索引。分詞功能 PgSQL 內(nèi)置了英文、西班牙文等，但中文分詞需要借助開源插件 zhparser；

SCWS

要使用 zhparser，我們首先要安裝 SCWS 分詞庫(kù)，SCWS 是 Simple Chinese Word Segmentation 的首字母縮寫（即：簡(jiǎn)易中文分詞系統(tǒng)），其 GitHub 項(xiàng)目地址為 hightman-scws，我們下載之后可以直接安裝。

安裝完后，就可以在命令行中使用 scws 命令進(jìn)行測(cè)試分詞了， 其參數(shù)主要有：

• -c utf8 指定字符集
• -d dict 指定字典 可以是 xdb 或 txt 格式
• -M 復(fù)合分詞的級(jí)別， 1~15，按位異或的 1|2|4|8 依次表示 短詞|二元|主要字|全部字，默認(rèn)不復(fù)合分詞，這個(gè)參數(shù)可以幫助調(diào)整到最想要的分詞效果。

zhpaser

1.下載 zhparser 源碼 git clone https:github.com/amutu/zhparser.git；

2.安裝前需要先配置環(huán)境變量：export PATH=$PATH:/path/to/pgsql；

3.make make install編譯 zhparser；

4.登陸 PgSQL 使用 CREATE EXTENSION zhparser; 啟用插件；

5.添加分詞配置

CREATE TEXT SEARCH CONFIGURATION parser_name (PARSER = zhparser); // 添加配置
ALTER TEXT SEARCH CONFIGURATION parser_name ADD MAPPING FOR n,v,a,i,e,l,j WITH simple; // 設(shè)置分詞規(guī)則 （n 名詞 v 動(dòng)詞等，詳情閱讀下面的文檔）

6.給某一列的分詞結(jié)果添加 gin 索引 create index idx_name on table using gin(to_tsvector('parser_name', field));

7.在命令行中使用上一節(jié)中介紹的 scws 命令測(cè)試分詞配置，如我認(rèn)為復(fù)合等級(jí)為 7 時(shí)分詞結(jié)果最好，則我在 postgresql.conf添加配置

zhparser.multi_short = true #短詞復(fù)合: 1

zhparser.multi_duality = true  #散字二元復(fù)合: 2

zhparser.multi_zmain = true  #重要單字復(fù)合: 4

zhparser.multi_zall = false  #全部單字復(fù)合: 8

SQL

查詢中我們可以使用最簡(jiǎn)單的 SELECT * FROM table WHERE to_tsvector('parser_name', field) @@ 'word' 來(lái)查詢 field 字段分詞中帶有 word 一詞的數(shù)據(jù)；

使用 to_tsquery() 方法將句子解析成各個(gè)詞的組合向量，如 國(guó)家大劇院 的返回結(jié)果為 '國(guó)家' '大劇院' '大劇' '劇院' ，當(dāng)然我們也可以使用 | 符號(hào)拼接自己需要的向量；在查詢 長(zhǎng)句 時(shí)，可以使用 SELECT * FROM table WHERE to_tsvector('parser_name', field) @@ to_tsquery('parser_name','words')；

有時(shí)候我們想像 MySQL 的 SQL_CALC_FOUND_ROWS 語(yǔ)句一樣同步返回結(jié)果條數(shù)，則可以使用 SELECT COUNT(*) OVER() AS score FROM table WHERE ...，PgSQL 會(huì)在每一行數(shù)據(jù)添加 score 字段存儲(chǔ)查詢到的總結(jié)果條數(shù)；

到這里，普通的全文檢索需求已經(jīng)實(shí)現(xiàn)了。

優(yōu)化

我們接著對(duì)分詞效果和效率進(jìn)行優(yōu)化：

存儲(chǔ)分詞結(jié)果

我們可以使用一個(gè)字段來(lái)存儲(chǔ)分詞向量，并在此字段上創(chuàng)建索引來(lái)更優(yōu)地使用分詞索引：

ALTER TABLE table ADD COLUMN tsv_column tsvector;           // 添加一個(gè)分詞字段
UPDATE table SET tsv_column = to_tsvector('parser_name', coalesce(field,''));   // 將字段的分詞向量更新到新字段中
CREATE INDEX idx_gin_zhcn ON table USING GIN(tsv_column);   // 在新字段上創(chuàng)建索引
CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE  ON table FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv_column, 'parser_name', field); // 創(chuàng)建一個(gè)更新分詞觸發(fā)器

這樣，再進(jìn)行查詢時(shí)就可以直接使用 SELECT * FROM table WHERE tsv_column @@ 'keyword' 了。

這里需要注意，這時(shí)候在往表內(nèi)插入數(shù)據(jù)的時(shí)候，可能會(huì)報(bào)錯(cuò)，提示指定 parser_name 的 schema， 這時(shí)候可以使用 \dF 命令查看所有 text search configuration 的參數(shù)：

               List of text search configurations

   Schema   |    Name    |              Description

------------+------------+---------------------------------------

 pg_catalog | english    | configuration for english language

 public     | myparser   |

注意 schema 參數(shù)，在創(chuàng)建 trigger 時(shí)需要指定 schema， 如上面，就需要使用 public.myparser。

添加自定義詞典

我們可以在網(wǎng)上下載 xdb 格式的詞庫(kù)來(lái)替代默認(rèn)詞典，詞庫(kù)放在 share/tsearch_data/ 文件夾下才能被 PgSQL 讀取到，默認(rèn)使用的詞庫(kù)是 dict.utf8.xdb。要使用自定義詞庫(kù)，可以將詞庫(kù)放在詞庫(kù)文件夾后，在 postgresql.conf 配置 zhparser.extra_dict="mydict.xdb" 參數(shù)；

當(dāng)我們只有 txt 的詞庫(kù)，想把這個(gè)詞庫(kù)作為默認(rèn)詞庫(kù)該怎么辦呢？使用 scws 帶的scwe-gen-dict 工具或網(wǎng)上找的腳本生成 xdb 后放入詞庫(kù)文件夾后，在 PgSQL 中分詞一直報(bào)錯(cuò)，讀取詞庫(kù)文件失敗。我經(jīng)過(guò)多次實(shí)驗(yàn)，總結(jié)出了一套制作一個(gè)詞典文件的方法：

1.準(zhǔn)備詞庫(kù)源文件 mydict.txt：詞庫(kù)文件的內(nèi)容每一行的格式為詞 TF IDF 詞性，詞是必須的，而 TF 詞頻(Term Frequency)、IDF 反文檔頻率(Inverse Document Frequency) 和 詞性 都是可選的，除非確定自己的詞典資料是對(duì)的且符合 scws 的配置，不然最好還是留空，讓 scws 自已確定；

2.在 postgresql.conf 中設(shè)置 zhparser.extra_dicts = "mydict.txt" 同時(shí)設(shè)置 zhparser.dict_in_memory = true；

3.命令行進(jìn)入 PgSQL，執(zhí)行一條分詞語(yǔ)句 select to_tsquery('parser', '隨便一個(gè)詞') ，分詞會(huì)極慢，請(qǐng)耐心(請(qǐng)保證此時(shí)只有一個(gè)分詞語(yǔ)句在執(zhí)行)；

4.分詞成功后，在/tmp/目錄下找到生成的 scws-xxxx.xdb 替換掉 share/tsearch_data/dict.utf8.xdb；

5.刪除剛加入的 extra_dicts dict_in_memory 配置，重啟服務(wù)器。

擴(kuò)展

由于查詢的是 POI 的名稱，一般較短，且很多詞并無(wú)語(yǔ)義，又考慮到用戶的輸入習(xí)慣，一般會(huì)輸入 POI 名稱的前幾個(gè)字符，而且 scws 的分詞準(zhǔn)確率也不能達(dá)到100%，于是我添加了名稱的前綴查詢來(lái)提高查詢的準(zhǔn)確率，即使用 B樹索引 實(shí)現(xiàn) LIKE '關(guān)鍵詞%' 的查詢。這里需

這里要注意的是，創(chuàng)建索引時(shí)要根據(jù)字段類型配置 操作符類，不然索引可能會(huì)不生效，如在 字段類型為 varchar 的字段上創(chuàng)建索引需要使用語(yǔ)句CREATE INDEX idx_name ON table(COLUMN varchar_pattern_ops)，這里的 varcharpatternops 就是操作符類。

自此，一個(gè)良好的全文檢索系統(tǒng)就完成了。

總結(jié)

簡(jiǎn)單的數(shù)據(jù)遷移并不是終點(diǎn)，后續(xù)要做的還有很多，如整個(gè)系統(tǒng)的數(shù)據(jù)同步、查詢效率優(yōu)化、查詢功能優(yōu)化（添加拼音搜索、模糊搜索）等。特別是查詢效率，不知道是不是我配置有問(wèn)題，完全達(dá)不到那種 E級(jí)毫秒 的速度，1kw 的數(shù)據(jù)效率在進(jìn)行大結(jié)果返回時(shí)就大幅下降（200ms），只好老老實(shí)實(shí)地提前進(jìn)行了分表，目前百萬(wàn)級(jí)查詢速度在 20ms 以內(nèi)，優(yōu)化還有一段路要走。

不過(guò)這次倒是對(duì) 技術(shù)的“生態(tài)”有了個(gè)更深的體會(huì)，這方面 PgSQL 確實(shí)和 MySQL 差遠(yuǎn)了，使用 MySQL 時(shí)再奇葩的問(wèn)題都能在網(wǎng)上快速找到答案，而 PgSQL 就尷尬了，入門級(jí)的問(wèn)題搜索 stackoverflow 來(lái)來(lái)回回就那么幾個(gè)對(duì)不上的回答。雖然也有阿里的“德哥”一樣的大神在辛苦布道，但用戶的數(shù)量才是根本。不過(guò)，隨著 PgSQL 越來(lái)越完善，使用它的人一定會(huì)越來(lái)越多的，我這篇文章也算是為 PgSQL 加溫了吧，哈哈~希望能幫到后來(lái)的使用者。

以上就是如何使用PostgreSQL進(jìn)行中文全文檢索的詳細(xì)內(nèi)容，更多關(guān)于使用PostgreSQL進(jìn)行中文全文檢索的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！