mysql的全文索引使用及探索

 由于公司积累的数据达到上千万，经常需要模糊查询，一般索引无效，最好使用全文索引。有两种方式：1、使用专门的搜索引擎，例如elasticsearch；2、使用mysql内置的全文索引。这里主要记录mysql中的全文索引的使用方法。

MySQL 5.6版本以前只有MyISAM存储引擎支持全文引擎。在5.6版本中，InnoDB支持对全文索引的。5.7.6版本MySQL内置了ngram全文解析器，同时支持中文（也支持韩文和日文）全文的分词，但默认的分词解析器是以空格作为分隔来解析分词的，并且是不支持中文的。mysql8.0开始，默认分词也做了优化，开始支持中文分词了，但仍是以空格分隔的，所以若在包含中文的字段上创建全文索引，则需要使用ngram解析器。

1.全文索引参数配置

# ngram分词解析器的分词大小
ngram_token_size=2

# innodb默认分词解析器的分词大小
innodb_ft_min_token_size=2

# 最小匹配字数
ft_min_word_len=2
# 全文索引查询结果缓存最大限制，默认为2000000000，最大为42 9496 7295。
# 所以若查询结果的数据量比较大，或者统计返回结果的数据量可能会报异常：FTS query exceeds result cache limit
innodb_ft_result_cache_limit=2000000000

2.创建全文索引

可以在新建数据表时添加全文索引，也可以在已有数据表中添加全文索引。

/*关闭慢查询日志，以提升创建索引的性能。*/
SET GLOBAL slow_query_log = 0;
/*添加索引，此方法比alter table customer add fulltext index 方法性能更好*/
CREATE  fulltext index ft_customer_name ON  customer(name) WITH PARSER ngram;
/*开启慢查询日志*/
SET GLOBAL slow_query_log = 1;

由于数据表过大，所以原始数据表是采用横向分表（分区表）的的结构存储的。升级到mysql8.0后发现，是不能在分区表上建立全局索引的，只有单独建立一个数据表，把需要模糊查询的几个字段放到新的数据表里。建立全文索引时，必须带后缀with parser ngram。建立数据表如下：

CREATE TABLE `cust_full_text` (
	`id` INT(10) NOT NULL COMMENT '原始表主键ID',
	`name` VARCHAR(128) NOT NULL COMMENT '名称' COLLATE 'utf8mb4_unicode_ci',
	`address` VARCHAR(255) NULL DEFAULT NULL COMMENT '地址' COLLATE 'utf8mb4_unicode_ci',
	`update_time` DATETIME NOT NULL COMMENT '数据更新时间',
	PRIMARY KEY (`id`) USING BTREE,
	FULLTEXT INDEX `idf_cust_full_text` (`name`, `address`) with parser ngram,
	FULLTEXT INDEX `idf_cust_full_text_name` (`name`) with parser ngram
)
COMMENT='全文索引表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

3.数据同步触发

新建立一个表，若原始表更新时，此表也要一同更新，所以需要建立同步的触发器，代码如下：

/* 插入触发器 */
DELIMITER //
create trigger trigger_insert_cust
after insert on cust
FOR EACH ROW
begin
    insert into cust_full_text(id,name,address,update_time) values(new.id,new.name,new.address,new.update_time);
end;

/* 更新触发器 */
DELIMITER //
create trigger trigger_update_cust
after update on cust
FOR EACH ROW
begin
	update cust_full_text 
	set name = new.name,address = new.address,update_time = new.update_time
	where id = new.id;
end;

/* 删除触发器 */
DELIMITER //
create trigger trigger_delete_cust
after delete on cust
FOR EACH ROW
begin
	delete from cust_full_text where id = old.id;
end;

4.全文匹配查询

常用的全文检索模式有两种：全文检索模式和布尔模式。

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

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

select *
from cust_full_text cft 
where match(name) against('+科技' in boolean mode)
limit 10

下表说明了全文检索布尔运算符及其含义：

操作者	描述
+	包括，这个词必须存在。
-	排除，词不得出现。
>	包括，并提高排名值。
<	包括，并降低排名值。
()	将单词分组为子表达式（允许将它们作为一组包括在内，排除在外，排名等等）。
〜	否定单词的排名值。
*	通配符在这个词的结尾。
“”	定义短语（与单个单词列表相对，整个短语匹配以包含或排除，类似like %key%）。

5.全文索引的局限

1. 在InnoDB引擎上建立的数据表，全文索引在数据达到10万、20万，查询性能还是可以接受的，但是一旦达到100万以上，查询就慢的不可接受了。好像有一种说法，所谓的50%的门坎限制。即若超过50%的数据满足查询条件时，性能明显变慢。但在MyISAM引擎上的数据表上建议全文索引，性能会比较理想。可能两个原因：1MyISAM的索引是存储在单个文件中的，查询效率更高；2）MyISAM更好地利用内存。
2. 所以若查询结果的数据量比较大，或者统计返回结果的数据量可能会报异常：FTS query exceeds result。
3. 使用全文索引语法查询，结果按查询关键字的相关性排序。且尽量不要显式指定排序规则，否则可能会报异常：FTS query exceeds result。

理想选择，要么使用MyISAM建立数据表，并在指定列建立全局索引，要么转向Elasticsearch等企业级搜索引擎。（但好像Elasticsearch需要占用较多的内存，尽量保证不新增服务器或云服务器扩容的前提下实现全文搜索）

6.参考：

1. MySQL :: MySQL 8.0 Reference Manual :: 12.9.8 ngram Full-Text Parser
2. 全文搜索