聊聊MySQL全文索引怎么解决like模糊匹配查询慢

聊聊MySQL全文索引怎么解决like模糊匹配查询慢

2024-11-10 01:12

模糊查询，如查询姓名包含”晓“的用户，常见的写法为 like "%晓%"，MySQL里面他会全表扫描，数据量少还好，全表扫描也很快，随着数据增加会变慢，上ES又很重。本篇文章就来给大家介绍like模糊匹配查询慢解决之道——MySQL全文索引。

需要模糊匹配查询一个单词

select * from t_phrase where LOCATE('昌',phrase) = 0;

select * from t_chinese_phrase where instr(phrase,'昌') > 0;

select * from t_chinese_phrase where phrase like '%昌%'

explain一下看看执行计划

由explain的结果可知，虽然我们给phrase建了索引，但是查询的时候，索引是失效的。

原因： mysql的索引是B+树结构，InnoDB在模糊查询数据时使用 "%xx" 会导致索引失效（此处就不展开讲了）

从查询时长上来看，花费时间：90ms

目前数据量：93230（9.3W）已经需要90ms，这个时间不太能接受，假如数据量增加，这个时间会不断增长。

解决方案：

数据量不大的情况下，使用mysql的全文索引；数据量比较大或者mysql的全文索引不达预期的情况下，可以考虑使用ES

下面主要是MySQL的全文索引相关.

旧版的MySQL的全文索引只能用在MyISAM存储引擎的char、varchar和text的字段上。
MySQL5.6.24上InnoDB引擎也加入了全文索引。

全文检索（Full-Text Search） 是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、词等信息，也可以进行各种统计和分析

若需对大量数据设置全文索引，建议先添加数据再创建索引。

1、创建表时创建全文索引

2、为已有表添加全文索引

create fulltext index 索引名称 on 表名(字段名)；

eg:

3、删除全文索引

alter table 表名 drop index 索引名;

语法

4.1 IN NATURAL LANGUAGE MODE

自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。

结果如下：

4.2 IN BOOLEAN MODE

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

为空默认，包含该词 + 包括，这个词必须存在。 - 排除，词不得出现。 >(大于号) 包括，并提高排名值,查询的结果会靠前包括，并降低排名值，查询的结果会靠后 () 将单词分组为子表达式（允许将它们作为一组包括在内，排除在外，排名等等）。〜否定单词的排名值。 * 通配符在这个词的结尾。 “” 定义短语（与单个单词列表相对，整个短语匹配以包含或排除）。

示例：

预期值：把包含lion的都查询出来实际结果：啥都没有。

全匹配查询的时候能查询出来

只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据

奇怪了，这咋没出来呢？

全文索引默认是只按照空格进行分词的，所以当我完整的单个单词去查询的时候是能查出来的。但是使用部分单词去查询或者使用部分中文去查询时，是查询不出来数据的,像中文需要使用中文分词器进行分词。

InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：N-gram parser。

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“齿轮传动”来进行分词，得到的结果如下：

参数解析:

innodb_ft_min_token_size 默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小 innodb_ft_max_token_size默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小ngram_token_size默认2，表示2个字符作为内置分词解析器的一个关键词,合法取值范围是1-10，如对“abcd”建立全文索引，关键词为’ab’，‘bc’，‘cd’ 当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效

方式1：在my.cnf中修改/添加参数

方式2：修改启动参数

参数均不可动态修改，修改后需重启MySQL服务，并重新建立全文索引

这里只提供部分测试数据，我下面sql使用全量数据，数据对不上

mysql 全文索引使用倒排索引为 full inverted index 结构：{单词，(单词所在文档的ID，单词在具体文件中的位置)}

添加索引：

建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：

查询结果如下：