MySql 全文索引

1.MySql 全文索引介绍

Mysql 的全文索引主要用于全文字段的检索场景，支持 char、varchar、text 几个字段加全文索引，仅支持 InNoDB 与 MyISAM 引擎。

MySql 内置了 ngram 解析器来支持中文、日文、韩文等语言的文本，全文索引支持通过建表来创建或建表后新增。

全文索引支持三种模式：

• 布尔模式(Boolean Full-Text Searches)
• 自然语言模式(Natural Language Full-Text Searches)
• 查询拓展(Full-Text Searches with Query Expansion)

2.ngram 简介

ngram 一种基于统计语言模型的算法，简单来说，就是通过一个大小为 n 的滑动窗口，将一段文本分成多个由 n 个连续单元组成的 term。例：

n = 2
text = 湖北省武汉市

经过 ngram 解析器解析后，得到如下分词：

湖北 北省 省武 武汉 汉市

ngram 全文解析器是 MySql 服务内置的插件，与其他插件一样，在 MySql 服务启动的时候会自动加载。

参考：
https://zhuanlan.zhihu.com/p/32829048《自然语言处理中N-Gram模型介绍》
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html《ngram Full-Text Parser》

3.数据库配置

• 先看 MySql 版本
  ngram 解析器是 mysql5.7 版本后，内置的全文搜索解析器，所以要求 mysql 版本要在5.7及以上
  查看 mysql 版本：

  select version()
  

• 配置 ngram
  ngram 可以作为启动字符串的一部分或者在配置文件中设置
  启动字符串：

  -- 中文分词长度位2，每个字都可以查出来
  mysqld --ngram_token_size=2
  

  配置文件(my.ini)：
  以 windows 系统为例，首先找到 my.ini 文件(默认安装路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini)，编辑该文件，在文件后加上：ngram_token_size=2，配置完成后重启服务。

4.创建全文索引

• 通过建表语句创建

  CREATE TABLE `news`  (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '内容',`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标题',PRIMARY KEY (`id`) USING BTREE,FULLTEXT INDEX `idx_full_text`(`content`) WITH PARSER `ngram`
  ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  

• 通过 CREATE FULLTEXT INDEX 语句创建

  CREATE FULLTEXT INDEX idx_full_text ON news (content) WITH PARSER ngram;
  

5.使用全文索引

首先写入部分测试数据：

INSERT INTO `news` VALUES (1, '武汉市今天有小雨，大家出门记得带雨伞', '武汉天气');
INSERT INTO `news` VALUES (2, '湖北省武汉市是中部最大的城市，华中地区第一大都市', '湖北省武汉市');
INSERT INTO `news` VALUES (3, '武汉大学今年不开放樱花观赏，请各位游客不必前往', '武大樱花');
INSERT INTO `news` VALUES (4, '湖北省是千湖之省，风景秀丽，欢迎全国各地游客', '湖北省旅游');
INSERT INTO `news` VALUES (5, '武汉光谷是武汉市高新技术公司聚集地，大量来自全球各地的优秀人才聚集在此', '武汉光谷');

布尔模式

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

操作符	含义
无操作符	出现了，相关性会更高
+	必须包含
-	必须不包含
@distance	需要满足一定的编辑距离 仅仅支持InnoDB存储引擎
<	降低相关性
>	增加相关性
~	负相关性，如果包含，相关性会比不包含更低
*	与 like 类似，放在字段前或后
“”	将引号内的内容当整体检索

下面分别介绍几种操作符的用法：

select * from news where MATCH (content) against ('武汉 雨伞' in Boolean MODE); 

无操作符则表示出现’武汉’或者’雨伞’的数据会有更高的相关性。

select * from news where MATCH (content) against ('+武汉' in Boolean MODE);

+武汉表示必须出现’武汉’这个单词数据才能被检索到，从结果可以看出，'武汉’这个单词出现次数最多的排在最前面(参考第7章节)。

select * from news where MATCH (content) against ('+武汉 -雨伞' in Boolean MODE); 

+武汉表示被检索到的数据必须包含’武汉’这个分词，-雨伞表示被检索到的数据必须不能包含’雨伞这个分词’。

select * from news where MATCH (content) against ('+武汉 <雨伞' in Boolean MODE); 

'武汉 <雨伞’表示需要检索到包含武汉的数据，但是当出现’雨伞’这个分词时，需要降低相关性，所以带有’雨伞’的数据会排在最后。

select * from news where MATCH (content) against ('"全球 优秀"@5' in Boolean MODE);

这个@distance语法不知道为啥一直没有查出结果(待查明原因)

select * from news where MATCH (content) against ('武汉  ~雨伞' in Boolean MODE);

'武汉 ~雨伞’表示需要检索到包含武汉的数据，但是当出现’雨伞’这个分词时，相关性为负，所以带有’雨伞’的数据会排在最后(这个效果与<操作符有类似效果)。

select * from news where MATCH (content) against ('全球*' in Boolean MODE);

*操作符的作用其实与like的通配符类似。

select * from news where MATCH (content) against ('"武汉光谷"' in Boolean MODE);

双引号表示’武汉光谷’以短语的方式被检索到。

校验 ngram

前面讲到了 ngram 分词，下面校验一下 ngram 分词：

以数据表的第一条文本为例：

select * from news where MATCH (content) against ('天有' in Boolean MODE);

检索’天有’这个分词能够检索出’武汉市今天有小雨，大家出门记得带雨伞’这条文本，说明这条文本的分词情况如下：

武汉 汉市 市今 今天 天有 有小 小雨 。。。

说明 ngram(n=2) 解析器将文本分成了以2为滑动窗口，将一段文本分成多个由2个连续单元组成的 term。

自然语言模式

自然语言模式是默认全文检索模式，简单来说就是把检索关键词当作自然语言来处理，自然语言模式也等价于布尔模式中的无操作符，下面三种查询，结果是一样的：

-- 自然语言模式
select * from news where MATCH (content) against ('技术 武汉 光谷' IN NATURAL LANGUAGE MODE);
-- 布尔模式 无操作符
select * from news where MATCH (content) against ('技术 武汉 光谷' in Boolean MODE);
-- 默认模式
select * from news where MATCH (content) against ('技术 武汉 光谷');

这三种查询结果和排序都是一样的

拓展查询

拓展查询模式会进行两次查询，第一次查询命中关键词，第二次查询会根据第一次查询到的结果作为输入，再进行一次查询。
首先，我们 doc 列表如下：

先查询关键词 ‘西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

上面通过 WITH QUERY EXPANSION 查询 ‘西湖’ 就返回一条与其相关的数据
下面再插入一条数据：

再来拓展查询 ‘西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

再看结果，发现多了一条与 ‘西湖’ 无关的记录(就是刚刚新增的数据)。

那么为什么会出现这样的结果呢？
原因是拓展查询会根据第一次查询到的结果再进行一次查询，第一次查询到的结果是关于 ‘西湖’ 的结果，结果理包含了 ‘杭州’、 ‘美丽’、 ‘地方’ 等关键词，第二次查询会检索 ‘杭州’、 ‘美丽’、 ‘地方’ 等关键词，所以自然会查询到上面两条数据，下面再新增几条数据来验证:

上面新增数据11和12。再次拓展查询’西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

查询结果包括刚刚新增的11、12两条数据(因为这两条数据包括’西湖’关键字的第一次查询结果里面的关键词)。
上面的拓展查询结果相当于下面这两次查询：

-- 首先根据'西湖'关键词 查询出 '杭州'、'美丽'、'地方' 等结果
select content from news where MATCH (content) against ('西湖' IN NATURAL LANGUAGE MODE)
-- 再根据 '杭州'、'美丽'、'地方' 等结果二次查询
select * from news where MATCH (content) against ('杭州 美丽 地方' IN NATURAL LANGUAGE MODE)

拓展查询很有可能查询出很多意想不到的结果，有点盲查的意思。

6.相关性排序

InNoDB 的全文搜索基于 Sphinx 搜索引擎，相关性排序算法采用的是 BM25 and TF-IDT，熟悉 ES 的同学应该对 TF-IDT 不陌生，TF-IDT 简单来说就是：被检索关键词出现越多的 doc 相关性越高，包含高辨识度(整体上在 doc 里面出现的比较少)关键词的 doc 相关性越高，相关性越高，就越会被排在结果的前面。

SELECT*,MATCH ( content ) against ( '武汉 杭州' IN Boolean MODE ) AS score 
FROMnews 
ORDER BYscore DESC;

上面检索了’武汉’、‘杭州’两个关键词，从结果中可以看出：

• 出现’杭州’(因为’杭州’整体上出现的较少，所以辨识度较高 IDF)的记录(doc)得分较高(相关性交大)，排在前面
• 武汉出现较多的记录得分也比较高(TF)

7.注意事项

• 只能在类型为 char、varchar 或者 text 的字段上创建全文索引
• 全文索引只支持 InnoDB 和 MyISAM 引擎
• MATCH (columnName) AGAINST (‘keywords’)。MATCH() 函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH ( title,body) 使用的字段名与全文索引 ft_articles(title,body) 定义的字段名一致。如果要对 title 或者 body 字段分别进行查询，就需要在 title 和 body 字段上分别创建新的全文索引
• MATCH() 函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索
• 如果不是英文单词，是字符串，用串中子字符串查不到（同分词长度有一定关系）；但如果串中是用空格、‘,’,‘，’,';'等符号隔开，则可以查询
• 如果一个字段中英文混合，用英文模式建，默认单词长度位4。查中文要求：查询内容3个字或以上，且是单独句型，前后用语法分隔符分隔的；用中文模式建，英文查找慢，且结果不正确，匹配项大量增加
• 多字段共建成一个全文索引，则存储少，但查询速度慢
• 多字段共建成一个全文索引，如果中英文混合，按英文模式建，中文（查询内容3个字或以上，且是单独句型，前后用语法分隔符分隔的）；按中文模式建，中文查找模式准确，也快，但英文查找慢且匹配项大量增加
• 表设计建议：1 一个表最好只有一个全文索引字段、2 字段内容最好是全英文或全中文

---

好事定律：每件事最后都会是好事，如果不是好事，说明还没到最后。