Mysql全文索引

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=湖北省武汉市
1
2

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

湖北 北省 省武 武汉 汉市
1

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()
1

配置ngram

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

mysqld --ngram_token_size=2
1

配置文件(my.ini)：
以windos系统为例，首先找到my.ini文件(默认安装路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini)，编辑该文件，在文件后加上如下配置：

ngram_token_size=2
1

配置完成后重启服务

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;
1
2
3
4
5
6
7

通过CREATE FULLTEXT INDEX语句创建

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

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, '武汉光谷是武汉市高新技术公司聚集地，大量来自全球各地的优秀人才聚集在此', '武汉光谷');
1
2
3
4
5

布尔模式

布尔模式的全文检索支持下面几种操作符：
● +(必须出现)
● -(必须不出现)
● 无操作符(出现了，相关性会更高)
● @distance(需要满足一定的编辑距离 仅仅支持InnoDB存储引擎)
● <>(增加或者减少相关性)
● ~(负相关性)
● *(通配符)
● “”(短语)

下面分别介绍这几种操作符在布尔模式下的用法：

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

查询结果：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

校验ngram

第三节讲到了ngram分词，下面校验一下ngram分词：

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

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

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

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

说明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 ('技术 武汉 光谷');
1
2
3
4
5
6

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

拓展查询

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

先查询关键词’西湖’：

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

上面通过WITH QUERY EXPANSION查询’西湖’就返回一条与’西湖’相关的数据。

下面再插入一条数据：

再来拓展查询’西湖’:

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

再看结果：

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

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

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

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

查询结果：

查询结果包括刚刚新增的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)
1
2
3
4

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

参考：
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;
1
2
3
4
5
6
7

上面检索了’武汉’、‘杭州’两个关键词，从结果中可以看出：
● 出现’杭州’(因为’杭州’整体上出现的较少，所以辨识度较高 IDF)的记录(doc)得分较高(相关性交大)，排在前面
● 武汉出现较多的记录得分也比较高(TF)

参考：
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算法原理和公式》