最近在处理一个用户端搜索速度慢的问题，表数据100w+, 需要搜索产品名称和主要信息栏位, 之前的业务中直接使用了LIKE，导致查询时间有时能达到10s+。那最简单的处理手段就是使用mysql自带的全文索引来实现基础的查询（如果要使用ES等花费代价太大）。

0x00 概念

全文索引（FULL TEXT INDEX）与其他索引类似， 只是它会拆分指定列内容里面的词语， 并分析出现的频率。

0x01 版本支持

开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况:

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

0x02 全文索引的配置

执行SHOW VARIABLES LIKE '%ft_%':

mysql 全索引扫描 mysql全文索引查询_python

上图中下你是了与全文索引有关的2个分词长度(最大/小搜索长度)设置:

MyISAM: ft_max_word_len, ft_min_word_len

InnoDB: inodb_ft_max_token_size, innodb_ft_min_token_size

有关min的配置, 是分词最小长度. 对于英文来说, 是直接按空格来拆分单词, 中文分词就需要用到组件ngram. 对于拆分出来的词语长度不小于最小配置的词才进行索引(统计)。与分词有关的"停止词"(stopword)请自行度娘。

innodb引擎默认只对单词长度 >= 3的进行索引，我们先设置为1试试。

在mysql配置文件的mysqld节点下添加设置:

[mysqld]
innodb_ft_min_token_size = 1

然后重启服务。

0x03 全文索引操作

-- 建表
CREATE TABLE `test` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `content` text NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

-- 建索引
ALTER TABLE `test` ADD FULLTEXT INDEX `FIX_content`(`content`) WITH PARSER ngram; /*这里必须指定中文分词器*/

-- 插入数据
INSERT INTO `test` (`content`) VALUES ('我'),('你'),('他')
,('我们'),('你们'),('他们')
,('我们的'),('你们的'),('他们的')
,('我们的爱'),('你们的猫'),('他们的狗')
,('我们的爱永久'),('你们的猫很可爱'),('他们的狗很凶猛')
,('我们的爱永久不变'),('你们的猫很可爱的样子'),('他们的狗很凶猛的样子');

如果修改了单词长度限制参数, 则建议重建索引, 先执行 ALTER TABLE test DROP INDEX FIX_content然后再add即可。

和普通索引一样, 全文索引页支持对多列操作:

ALTER TABLE tbname ADD FULLTEXT INDEX ix_name(col1, col2, ...)

0x04 全文索引的查询

语法是:

MATCH(col [,col...]) AGAINST('keyword')

那我们来查询看看:

SELECT *, MATCH(content) AGAINST('我们') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们');

执行结果:

mysql 全索引扫描 mysql全文索引查询_java_02

这个查询结果比较正常，是我们所期望的。

再看一下mysql自动分词的查询:

SELECT *, MATCH(content) AGAINST('我们的爱') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们的爱');

执行结果:

mysql 全索引扫描 mysql全文索引查询_mysql_03

默认是按相关度进行排序的， 但是后面那几个数据，相关度太低, 就不是我们想要的了。这个是否与我们前面设置的最小分词数等于1有关呢？

我们现在去设置 innodb_ft_min_token_size = 2:

[mysqld]]
innodb_ft_min_token_size = 2

重启mysql, 重建全文索引:

ALTER TABLE `test` DROP INDEX `FIX_content`;
ALTER TABLE `test` ADD FULLTEXT INDEX `FIX_content`(`content`) WITH PARSER ngram; /*这里必须指定中文分词器*/

mysql 全索引扫描 mysql全文索引查询_java_04

通过上图的参数查询结果能看到, 设置已经生效. 然后重新执行查询:

SELECT *, MATCH(content) AGAINST('我们的爱') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们的爱');

mysql 全索引扫描 mysql全文索引查询_mysql_05

结果还是那样, oh my god! 从测试来看，类似“的”这种助词，mysql8.0 默认的全文索引查询仍然没有处理好。

我们换个关键词:

SELECT *, MATCH(content) AGAINST('我们不变') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们不变');

mysql 全索引扫描 mysql全文索引查询_mysql_06

上面我们查询"我们不变", 系统拆分并自动匹配了"我们" 和"不变"。

我们现在配置的最小搜索长度是2, 那我们搜索一个字行不行呢?

mysql 全索引扫描 mysql全文索引查询_mysql_07

sorry, 搜索词长度 < 全文索引设置的最小搜索长度 是没有结果的。

所以这个最小搜索长度, 无法限制mysql查询时分词的长度.

0x05 两种全文索引

自然语言的全文索引 (默认)

默认情况下，或者使用 IN NATURAL LANGUAGE MODE 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

这种模式下, 会进行关键词的拆分, 比如"洗衣机", 会匹配"洗衣机","洗衣","洗","衣","机"等。

自然语言搜索模式的特点：

1.忽略停词（stopword），英语中频繁出现的and/or/to等词被认为是没有实际搜索的意义，搜索这些不会获得任何结果。
2.如果某个词在数据集中频繁出现的几率超过了50%，也会被认为是停词，所以如果数据库中只有一行数据，不管你怎么全文搜索都不能获得结果。
3.搜索结果都具有一个相关度的数据，返回结果自动按相关度由高到低排列。
4.只针对独立的单词进行检索，而不考虑单词的局部匹配，如搜索box时，就不会将boxing作为检索目标。

布尔全文索引

使用IN BOOLEAN MODE。这种查找方式的特点是没有自然查找模式中的50%规则，即便有词语在数据集中频繁出现的几率超过50%，也会被作为搜索目标进行检索并返回结果，而且检索时单词的局部匹配也会被作为目标进行检索。

但是当关键词长度为1且出现频率很高时，却无法匹配。例如：

INSERT INTO `test` (`content`) VALUES('1.天娇美奴女士背包双肩背包双肩包女帆布小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('2.天娇美奴女士背包双肩背包双肩包女尼龙小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('3.天娇美奴女士背包双肩背包双肩包女尼龙小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('4.天娇美奴女士背包双肩背包双肩包女帆布小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('5.天娇美奴女士背包双肩背包双肩包女尼龙小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('6.天娇美奴女士背包双肩背包双肩包女尼龙小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('7.天娇美奴女士背包双肩背包双肩包女尼龙小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('8.天娇美奴女士背包双肩背包双肩包女帆布小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('9.天娇美奴女士背包双肩背包双肩包女小包书包女士真皮双肩包女韩版女背包双肩包百搭旅行背包女'),
('10.天娇美奴女士背包双肩背包双肩包女小包书包女士真皮双肩包女韩版女背包双肩包百搭旅行背包女'),
('11.天娇美奴牛皮女士背包双肩背包双肩包女小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女'),
('12.天娇美奴牛皮女士背包双肩背包双肩包女小包书包女士双肩包女韩版女背包双肩包百搭旅行背包女');

执行查询"包","背包":

SELECT * FROM test WHERE MATCH(content) AGAINST('包'); -- 无结果
SELECT * FROM test WHERE MATCH(content) AGAINST('包' IN BOOLEAN MODE);-- 无结果
SELECT * FROM test WHERE MATCH(content) AGAINST('背包'); -- 有结果
SELECT * FROM test WHERE MATCH(content) AGAINST('背包' IN BOOLEAN MODE); -- 有结果

搜索一个字时无法得到结果。但是我们可以使用修饰符来解决:

SELECT * FROM test WHERE MATCH(content) AGAINST('包*' IN BOOLEAN MODE);

上面的标识: 寻找包含"包"或者以"包"开头的记录。

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

  • + 必须包含该词
  • - 必须不包含该词
  • > 提高该词的相关性，查询的结果靠前
  • < 降低该词的相关性，查询的结果靠后
  • (*) 星号 通配符，只能接在词后面
  • " 精确匹配输入的单词

我们再插入些数据:

INSERT INTO `test` (`content`) VALUES ('可爱的猫'),('猫的主人是我们');

目前中文版的布尔查询有些问题:

SELECT *, MATCH(content) AGAINST('-(我们)' IN BOOLEAN MODE) AS rel FROM `test` WHERE MATCH(content) AGAINST('-(我们)' IN BOOLEAN MODE); -- 查询不到结果
SELECT *, MATCH(content) AGAINST('+(我们)' IN BOOLEAN MODE) AS rel FROM `test` WHERE MATCH(content) AGAINST('+(我们)' IN BOOLEAN MODE);-- 正常

如果要精确查找, 而不让mysql自动分词, 可以使用双引号(")包含关键词, 比如:

我们再插入几笔数据:

INSERT INTO `test` (`content`)VALUES('日用口罩 '),('求购医用拐杖'),('医用外科口罩 防病毒肺炎传染');

执行3种查询

SELECT * FROM test WHERE MATCH(content) AGAINST('医用口罩');
SELECT * FROM test WHERE MATCH(content) AGAINST('医用口罩' IN BOOLEAN MODE);
SELECT * FROM test WHERE MATCH(content) AGAINST('"正规公司"' IN BOOLEAN MODE);

mysql 全索引扫描 mysql全文索引查询_java_08

在 InnoDB引擎"BOOLEAN MODE"下查询不到数据.

那我们再来试下MyISAM引擎:

-- 建表  用MyISAM引擎
CREATE TABLE `test2` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `content` text NOT NULL,
    PRIMARY KEY(id)
) ENGINE=MyISAM;

-- 建索引
ALTER TABLE `test2` ADD FULLTEXT INDEX `FIX_content`(`content`) WITH PARSER ngram; /*这里必须指定中文分词器*/

-- 插入数据
INSERT INTO `test2` (`content`)VALUES('日用口罩 '),('求购医用拐杖'),('医用外科口罩 防病毒肺炎传染');

那我们现在执行查询并比较结果:

SELECT COUNT(*) AS count, 'InnoDB: 医用口罩' AS 'cat' FROM test WHERE MATCH(content) AGAINST('医用口罩') -- InnoDB
UNION ALL
SELECT COUNT(*) AS count, 'MyISAM: 医用口罩' AS 'cat' FROM test2 WHERE MATCH(content) AGAINST('医用口罩') -- MyISAM
UNION ALL
SELECT COUNT(*) AS count, 'InnoDB: 医用口罩 IN BOOLEAN MODE' AS 'cat' FROM test WHERE MATCH(content) AGAINST('医用口罩' IN BOOLEAN MODE) -- InnoDB
UNION ALL
SELECT COUNT(*) AS count, 'MyISAM: 医用口罩 IN BOOLEAN MODE' AS 'cat' FROM test2 WHERE MATCH(content) AGAINST('医用口罩' IN BOOLEAN MODE) -- MyISAM
UNION ALL
SELECT COUNT(*) AS count, 'InnoDB: "医用口罩" IN BOOLEAN MODE' AS 'cat' FROM test WHERE MATCH(content) AGAINST('"医用口罩"' IN BOOLEAN MODE) -- InnoDB
UNION ALL
SELECT COUNT(*) AS count, 'MyISAM: "医用口罩" IN BOOLEAN MODE' AS 'cat' FROM test2 WHERE MATCH(content) AGAINST('"医用口罩"' IN BOOLEAN MODE) -- MyISAM
;

mysql 全索引扫描 mysql全文索引查询_mysql 全索引扫描_09

在MyISAM引擎下, 只有最后一种双引号的才是正确的结果.

英文相关的资料参考 https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

注: InnoDB的停止词:

SELECT group_concat(value) FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

a,about,an,are,as,at,be,by,com,de,en,for,from,how,i,in,is,it,la,of,on,or,that,the,this,to,was,what,when,where,who,will,with,und,the,www

就那么几个英文单词, 这maybe就是导致前面"的"问题出现的原因.

今天不做深入研究.

注意

  1. 全文索引查询比LIKE模糊匹配速度快几十倍, 但是精度问题不好控制(需要区分InnoDB与MyISAM).
  2. 带索引的大表, 在恢复时建议先删除或停止索引, 待数据恢复后在恢复或重建索引, 以提高数据恢复速度。
  3. 默认全文搜索不区分大小写。若要区分大小写，可以更改成对应字符集的二进制排序方式。如Latin1编码下则选择latin1_bin作为二进制排序方式

补充

在Mysql5.7版本+表数据100w的情况下，如果只是查询指定的全文索引的栏位, 速度确实很快, 但是如果再加上其他栏位的查询, 与LIKE相比速度提升也很有限, 即使这些栏位已经添加了普通索引。