1.背景简介
项目开发过程有时候会遇到全文检索的需求，但是数据量有时候比较小，不属于高并发高吞吐场景，这种场景搭建ES服务有点浪费资源，也把工程设计复杂了，所以需要采用更简单更廉价的方案。一般业务系统都会用到Mysql或者PostgreSQL服务，无论是Mysql还是PostgreSQL都对full-text做了兼容，下面以最常见的mysql数据库服务为例，讲述mysql服务接入full-text索引的过程。
2.Mysql全文索引简介
mysql的全文索引主要用于全文字段的检索场景，支持char、varchar、text几个字段加全文索引，仅支持InNoDB与MyISAM引擎。
mysql内置了ngram解析器来支持中文、日文、韩文等语言的文本，全文索引支持通过建表语句来创建或者建表后新增。
mysql全文索引支持三种模式：
● 布尔模式(IN BOOLEAN MODE)
● 自然语言模式(NATURAL LANGUAGE MODE)
● 查询拓展(QUERY EXPANSION)
参考：
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html《Full-Text Search Functions》
3.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》
4.数据库配置
先看mysql版本
ngram解析器是mysql5.7版本后，内置的全文索引解析器，所以要求mysql版本要5.7及以上。
查看mysql版本：
select version()
配置ngram
ngram可以作为启动字符串的一部分或者在配置文件中设置
启动字符串：
mysqld --ngram_token_size=2
配置文件(my.ini)：
以windos系统为例，首先找到my.ini文件(默认安装路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini)，编辑该文件，在文件后加上如下配置：
ngram_token_size=2
配置完成后重启服务
5.创建全文索引
通过建表语句创建
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;
6.使用全文索引
首先写入部分测试全文数据：
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存储引擎)
● <>(增加或者减少相关性)
● ~(负相关性)
● *(通配符)
● “”(短语)
下面分别介绍这几种操作符在布尔模式下的用法：
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 ('武汉 <雨伞' 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
整个用下来，熟悉ES服务的同学可能已经发现了，这个跟ES操作还是很类似的。
参考：
https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html《Boolean Full-Text Searches》
自然语言模式
自然语言模式是默认全文检索模式，简单地说就是把检索关键词当做自然语言来处理，自然语言模式也等价于布尔模式中的无操作符模式，下面三种查询，结果是一样的：
-- 自然语言模式
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)
拓展查询很有可能查询出很多意想不到的结果，有点盲查的意思。
参考：
https://dev.mysql.com/doc/refman/5.7/en/fulltext-query-expansion.html《Full-Text Searches with Query Expansion》
https://www.begtut.com/mysql/using-mysql-query-expansion.html《MySQL查询扩展》
7.相关性排序
InnoDB的全文搜索基于 Sphinx 搜索引擎，相关性排序算法采用的是 BM25 and TF-IDF ，熟悉ES的同学应该对TF-IDT不陌生，TF-IDT简单的来说就是：被检索关键词出现越多的doc相关性越高，包含高辨识度(整体上在doc里面出现的比较少)关键词的doc相关性越高，相关性越高，会被排在结果的前面。
SELECT
*,
MATCH ( content ) against ( '武汉 杭州' IN Boolean MODE ) AS score
FROM
news
ORDER BY
score DESC;
上面检索了’武汉’、‘杭州’两个关键词，从结果中可以看出：
● 出现’杭州’(因为’杭州’整体上出现的较少，所以辨识度较高 IDF)的记录(doc)得分较高(相关性交大)，排在前面
● 武汉出现较多的记录得分也比较高(TF)文章来源：https://www.toymoban.com/news/detail-450006.html
参考：
http://t.zoukankan.com/hoohack-p-5408933.html《Sphinx的介绍和原理探索》
https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html《Boolean Full-Text Searches》
https://blog.csdn.net/zc1995730/article/details/122498012《TF-IDF算法原理和公式》文章来源地址https://www.toymoban.com/news/detail-450006.html
到了这里，关于Mysql全文索引的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
