Mysql 全文搜索对模糊查询的性能提升测试

从测试成绩来看，使用mysql自带的全文搜索索引类型 FULLTEXT，20w数据，对比*like ‘%xxxx%’*双向模糊查询，查询效率提升了54.75倍，还是相当不错的；

场景：
最近工作中实现了一个通过字段名，提供映射工具给到客户，使客户得以通过可视化的字段规则自行拼装sql，实现数据筛选分析功能；我们这张表的字段数非常多，足有上百个；分两个表的业务，一张表日表20w左右，另一张表日表200万左右；因为sql规则是用户自定义的，一直没有做什么优化；但是最近因为用户拼装出了超长sql，包含大量like查询以及and、or，最终导致查询跟不上，不得已考虑对他的优化。

本文讲述主要问题，大量的双模糊 like ‘%xxx%’优化

mysql 中的全文索引介绍

MySQL 5.6开始支持全文索引，可以在变长的字符串类型上创建全文索引，来加速模糊匹配业务场景的DML操作。它是一个inverted index（反向索引），创建fulltext index时会自动创建6个auxiliary index tables（辅助索引表），同时支持索引并行创建，并行度可以通过参数innodb_ft_sort_pll_degree设置，对于大表可以适当增加该参数值。

在MySQL5.6之前的版本中，只有 MyISAM 存储引擎支持全文索引，而且对中文搜索支持不是太好，需要自己进行分词后将段落预处理拆分成单词在入库。

MySQL5.7 开始才增加了对Inodb存储引擎的支持，并且有了内置的分词器 ngram。ngram 支持设置设置分词的长度，可以将中文按长度拆分为不同的单词（虽然不太智能，但满足大部分场景）。

-- 查询mysql版本
select version();
-- 8.0.23
1
2
3

测试部分

1. 创建无测试字段索引的测试表，并导入20w数据，进行无索引状态下模糊查询耗时计算

-- 无索引状态下耗时
select * from yd_alarminfo_all_20220825 where alarmTitle like "%端口故障%"
-- 耗时：0.438秒
1
2
3

2. 使用ngram分词，创建全文索引

alter table yd_alarminfo_all_20220825 add fulltext index idx_full_title(alarmTitle) with parser ngram;
1

3. 再次查询

-- 无索引状态下耗时
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('端口故障' IN BOOLEAN MODE)
--耗时：0.008秒

1
2
3
4

使用过程中的其他问题：

1）关于参数微调

我在使用的时候并没有对配置参数做调整，根据官方文档的介绍。全文搜索并没有提供很多的可供调整的参数，而且默认行为不管是对中文的分词都是满足的，大多数场景属于开箱即用，无需调整。更多参考官方文档： 12.10.6 Fine-Tuning MySQL Full-Text Search

2）Natural Language 模式下，查询结果不太一样，匹配字符串“端口故障”被进行了再次分词；该模式为默认的查询模式，需要注意一下

MySQL全文检索模式主要有两种：

一、自然语言模式(NATURAL LANGUAGE MODE) 自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
二、BOOLEAN模式(BOOLEAN MODE) BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

3）在MATCH … AGAINST(…) 中有自己的 AND OR 语法，如果使用传统的AND、OR拼装，效率拉胯

-- 错误示例
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('网卡端口故障' IN BOOLEAN MODE) OR MATCH (alarmTitle) against('AAA' IN BOOLEAN MODE)

-- 正确示例
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('网卡端口故障 -AAA-' IN BOOLEAN MODE)
1
2
3
4
5

4）补充BOOLEAN MODE下的语法示例：

MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)

expr 语法示例：
	'apple banana'
	查找至少包含两个单词之一的行。
	
	'+apple +juice'
	查找包含这两个单词的行。
	
	'+apple macintosh'
	查找包含“apple”一词的行，但如果它们也包含“macintosh”，则排名更高。
	
	'+apple -macintosh'
	查找包含“apple”一词但不包含“macintosh”一词的行。
	
	'+apple ~macintosh'
	查找包含“apple”一词的行，但如果该行也包含“macintosh”一词，则将其评分低于行不包含。这比搜索'+apple -macintosh'“软”，因为“macintosh”的存在导致该行根本不返回。
	
	'+apple +(>turnover