mysql 全文搜索

介绍

MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。自MySQL 5.7.6，内置了ngram全文解析器，用来支持中文、日文、韩文分词，本文使用的MySQL 版本是8.0.26，InnoDB数据库引擎。

mysql全文索引的介绍

总结：倒排索引(inverted index)来实现。倒排索引同B+树索引一样,也是一种索引结构。它在辅助表(auxiliary table)中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现,其拥有两种表现形式:

inverted file index,其表现形式为{单词,单词所在文档的ID}
full inverted index,其表现形式为{单词,(单词所在文档,在具体文档中的位置)；
Auxiliary Table(辅助表)
倒排索引需要将word存放到一张表中,这个表称为Auxiliary Table(辅助表)。在 InnoDB存储引擎中,为了提高全文检索的并行性能,共有6张 Auxiliary Table,目前每张表根据word的 Latin编码进行分区。
InnoDB存储引擎的全文索引中,还有另外一个重要的概念 FTS Index Cache(全文检索索引缓存),其用来提高全文检索的性能。
原理：分词操作不是每次都将数据存入辅助表中，而是将数据放入index cache 缓存中，等到查询时，将index cache的数据批量合并到辅助表中，然后再进行查询。

配置

在 mysql 配置文件中添加分词以及最小词语长度

# ft_min_word_len 最小字符长度默认为 4，在英文条件下确实比较合理中文情况下需要修改；
# innodb_ft_token_size 
# ngram_token_size 它的取值范围是1到10，默认值是 2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为 1

# 配置如下，具体根据真实场景进行配置
ft_min_word_len = 1
innodb_ft_token_size  = 1
ngram_token_size  = 1

#比如 ngram_token_size  
ngram_token_size  =1: '你', '好', '世', '界' 
ngram_token_size  =2: '你好', '好世', '世界' 
ngram_token_size  =3: '你好世', '好世界' 
ngram_token_size  =4: '你好世界'

修改完配置文件，重启mysql服务，并查看配置是否生效。

SHOW VARIABLES LIKE 'ft_min_word_len';

如图：ngram_token_size 已经配置成功

建表，入数据

- 诗词数据表
CREATE TABLE `t_poem` (
  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `c_poem_name` varchar(50) DEFAULT NULL COMMENT '诗名',
	`c_author` varchar(50) DEFAULT NULL COMMENT '作者',
  `c_content` longtext NOT NULL COMMENT '诗句内容',
  PRIMARY KEY (`c_id`),
  FULLTEXT KEY `full_text_index` (`c_poem_name`,`c_content`) WITH PARSER `ngram`
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-创建全文索引的其他方式：
ALTER TABLE t_poem ADD FULLTEXT INDEX content_index (c_content) WITH PARSER ngram;

CREATE FULLTEXT INDEX content_index ON t_poem (c_content) WITH PARSER ngram;

- 先创建数据
- 创建全文索引设置如下参数
SET GLOBAL innodb_ft_aux_table='steward/t_poem';

- 查询分词的对应信息
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;

灌入数据

INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (7, '咏柳', '贺知章', '碧玉妆成一树高，\r\n\r\n万条垂下绿丝绦。\r\n\r\n不知细叶谁裁出，\r\n\r\n二月春风似剪刀。');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (11, '凉州词', '王之涣', '黄河远上白云间，\r\n\r\n一片孤城万仞山。\r\n\r\n羌笛何须怨杨柳，\r\n\r\n春风不度玉门关。');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (12, '登鹳雀楼', '王之涣', '白日依山尽，\r\n\r\n黄河入海流。\r\n\r\n欲穷千里目，\r\n\r\n更上一层楼。');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (13, '春晓', '孟浩然', '春眠不觉晓，\r\n\r\n处处闻啼鸟。\r\n\r\n夜来风雨声，\r\n\r\n花落知多少。');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (14, '绝句', '杜甫', '两个黄鹂鸣翠柳，\r\n\r\n一行白鹭上青天。\r\n\r\n窗含西岭千秋雪，\r\n\r\n门泊东吴万里船。');

INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (15, '如果', NULL, '今生今世 永不再将你想起 \r\n除了\r\n除了在有些个\r\n因落泪而湿润的夜里 如果\r\n如果你愿意');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (16, '爱情', NULL, '有一天路标迁了希望你能从容\r\n有一天桥墩断了希望你能渡越\r\n有一天栋梁倒了希望你能坚强\r\n有一天期待蔫了希望你能理解');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (17, '远和近', NULL, '你 一会看我\r\n一会看云\r\n我觉得\r\n你看我时很远\r\n你看云时很近');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (18, '断章', NULL, '你站在桥上看风景，\r\n看风景人在楼上看你。\r\n明月装饰了你的窗子，\r\n你装饰了别人的梦。');
INSERT INTO `t_poem` (`c_id`, `c_poem_name`, `c_author`, `c_content`) VALUES (19, '独语', NULL, '我向你倾吐思念\r\n你如石像\r\n沉默不应\r\n如果沉默是你的悲抑\r\n你知道这悲抑\r\n最伤我心');

搜索模式

• 自然语言模式(NATURAL LANGUAGE MODE)
  自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
• 布隆模式(BOOLEAN MODE)
  （IN BOOLEAN MODE）剔除一半匹配行以上都有的词，例如，每行都有this这个词的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；布尔检索模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

 	 +   一定要有(不含有该关键词的数据条均被忽略)。 
     -   不可以有(排除指定关键词，含有该关键词的均被忽略)。 
     >   提高该条匹配数据的权重值。 
     <   降低该条匹配数据的权重值。
     ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像-将之排除)，只是排在较后面权重值降低。 
     *   万用字，不像其他语法放在前面，这个要接在字符串后面。 
     " " 用双引号将一段句子包起来表示要完全相符，不可拆字。

• 查询扩展(QUERY EXPANSION)
  查询的结果不仅匹配出结果同时可以联想出其他你需要的结果。（类似关联查询，但是官网推荐仅支持短语查询 否则会出现很多脏数据）

- 使用该模式，操作符会失效
SELECT c_id, c_poem_name, MATCH ( c_content, c_poem_name ) against ( '风景' IN NATURAL LANGUAGE MODE ) AS score  FROM t_poem;
- 包含风不包含景
SELECT c_id, c_poem_name FROM t_poem WHERE MATCH ( c_content, c_poem_name ) against ( '+风 -景' IN BOOLEAN MODE );
- 必须包含剪刀，包含风与否均可
SELECT c_id, c_poem_name FROM t_poem WHERE MATCH ( c_content, c_poem_name ) against ( "~风 +剪刀" IN BOOLEAN MODE ) ;

-查看执行计划
EXPLAIN SELECT c_id, c_poem_name
FROM t_poem
WHERE MATCH(c_content, c_poem_name) against('~风 +剪刀' IN BOOLEAN MODE);

总结

1、全文索引比 like + % 快 N 倍，但是可能存在精度问题；
2、如果需要全文索引的是大量数据，建议先添加数据，再创建索引；
3、对于中文，可以使用 MySQL 5.7.6 之后的版本，或者 Sphinx、Lucene 等第三方的插件；
4、MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致，且只能是同一个表的字段不能跨表；