mysql及pgsql全文索引

一、英文全文索引根据空格等符号分词，不需要特别的分词器

二、mysql 5.7.6开始支持中文全文索引

• 分词组件ngram

• 配置中文单词分词个数

ngram_token_size=2

• 创建全文索引

alter table `表名` add fulltext index idx_ft_ngram_name( `column1`, `column2` ) with parser ngram;

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB;

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

• 全文检索模式

  常用的全文检索模式有两种：

  1、自然语言模式(NATURAL LANGUAGE MODE)

  自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。

  2、BOOLEAN模式(BOOLEAN MODE)

  BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

• 注意

1. 只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
2. 全文索引只支持InnoDB和MyISAM引擎。
3. MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
4. MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
5. 如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。

三、pgsql中文全文索引

1、两个数据类型tsvector，tsquery

2、索引建立

• 为tsv函数建索引

    create index post_tsvcontent_idx on posts using gin(to_tsvector('english', title || content));

• 新增一个tsv列，为列建立索引（推荐）

alter table posts add_column tsv_content tsvector;
update posts set tsv_content = to_tsvector('english', coalesce(title,'') || coalesce(content,''));
create index post_tsvcontent2_idx on posts using gin(tsv_content);

3、中文全文搜索（zhparser）

zhparser是PostgreSQL的中文分词插件，安装后可以使PostgreSQL支持中文的全文检索（Full Text Search）。

启用中文分词

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
--可选的参数设定
alter role all set zhparser.multi_short=on;
--简单测试
SELECT * FROM ts_parse('zhparser', 'hello world! 2010年保障房建设在全国范围内获全面启动，从中央到地方纷纷加大 了 保 障 房 的 建 设 和 投 入 力 度 。2011年，保障房进入了更大规模的建设阶段。住房城乡建设部党组书记、部长姜伟新去年底在全国住房城乡建设工作会议上表示，要继续推进保障性安居工程建设。');
SELECT to_tsvector('testzhcfg','“今年保障房新开工数量虽然有所下调，但实际的年度在建规模以及竣工规模会超以往年份，相对应的对资金的需求也会创历史纪录。”陈国强说。在他看来，与2011年相比，2012年的保障房建设在资金配套上的压力将更为严峻。');
SELECT to_tsquery('testzhcfg', '保障房资金压力');

利用分词进行全文索引的方法如下：

--为T1表的name字段创建全文索引
create index idx_t1 on t1 using gin (to_tsvector('zhcfg',upper(name) ));
--使用全文索引
 select * from t1 where to_tsvector('zhcfg',upper(t1.name)) @@ to_tsquery('zhcfg','(防火)') ;

自定义中文分词词典

-- 确实的分词结果
SELECT to_tsquery('testzhcfg', '保障房资金压力');
-- 往自定义分词词典里面插入新的分词
insert into pg_ts_custom_word values ('保障房资');
-- 使新的分词生效
select zhprs_sync_dict_xdb();
-- 退出此连接
\c
-- 重新查询，可以得到新的分词结果
SELECT to_tsquery('testzhcfg', '保障房资金压力');

使用自定义分词的注意事项如下：

• 最多支持一百万条自定义分词，超出部分不做处理，必须保证分词数量在这个范围之内。自定义分词与缺省的分词词典将共同产生作用。
• 每个词的最大长度为128字节，超出部分将会截取。
• 增删改分词之后必须执行select zhprs_sync_dict_xdb();并且重新建立连接才会生效。

四、索引数据结构

B+树
Hash
索引策略gist
    gin
    全文索引（常用倒排索引）
    

参考

MySQL 5.7 中文全文检索使用教程