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

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

需求

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

【资料图】

explain一下看看执行计划

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

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

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

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

解决方案：

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

全文索引介绍

1、发展历史

旧版的MySQL的全文索引只能用在MyISAM存储引擎的char、varchar和text的字段上。

MySQL5.6.24上InnoDB引擎也加入了全文索引。

2、全文索引

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

3、创建全文索引

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

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

create table 表名(字段名1,字段名2,字段名3,字段名4,FULLTEXT full_index_name (字段名))ENGINE=InnoDB;
登录后复制

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

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

eg:

create table t_word(    id        int unsigned auto_increment comment "自增id" primary key,    uid       char(32)     not null comment "32位唯一id",    word      varchar(256) null comment "英文单词",    translate varchar(256) null);create fulltext index full_idx_translate    on t_word (translate);create fulltext index full_idx_word    on t_word (word);INSERT INTO t_word (id, uid, word, translate) VALUES (1, "9d592499c65648b0a9519206688ef3f9", "lion", "狮子");INSERT INTO t_word (id, uid, word, translate) VALUES (2, "ce26ac4239514bc6af481bcb1d9b67df", "panda", "熊猫");INSERT INTO t_word (id, uid, word, translate) VALUES (3, "a7d6042853c44904b68275daafb44702", "tiger", "老虎");INSERT INTO t_word (id, uid, word, translate) VALUES (4, "f13bd0a8ecea44fc9ade1625eeb4cc3c", "goat", "山羊");INSERT INTO t_word (id, uid, word, translate) VALUES (5, "27d5cbfc93a046388d712085e567474f", "sheep", "绵羊");INSERT INTO t_word (id, uid, word, translate) VALUES (6, "ed35df138cf348aa937781be8ee21cbf", "lamb", "羊羔");INSERT INTO t_word (id, uid, word, translate) VALUES (7, "fba5861d9527440990276e999f47ef8f", "buffalo", "水牛");INSERT INTO t_word (id, uid, word, translate) VALUES (8, "3a72e76f210841b1939fff0d3d721375", "bull", "公牛");INSERT INTO t_word (id, uid, word, translate) VALUES (9, "272e0b28ea7a48248a86f17533bf9943", "cow", "母牛");INSERT INTO t_word (id, uid, word, translate) VALUES (10, "47127adface54e418e4c1b9980af6d16", "calf", "小牛");INSERT INTO t_word (id, uid, word, translate) VALUES (11, "10592499c65648b0a9519206688ef3f9", "little lion", "小狮子");INSERT INTO t_word (id, uid, word, translate) VALUES (12, "1bf095110b634a01bee5b31c5ee7ee0c", "little cow", "母牛");INSERT INTO t_word (id, uid, word, translate) VALUES (13, "4813e588cde54c30bd65bfdbb243ad1f", "little calf", "小小牛");INSERT INTO t_word (id, uid, word, translate) VALUES (14, "5e377e281ad344048b6938a638b78ccb", "little bull", "小公牛");INSERT INTO t_word (id, uid, word, translate) VALUES (15, "2855ad0da2964c7682c178eb8271f13d", "little buffalo", "小水牛");INSERT INTO t_word (id, uid, word, translate) VALUES (16, "72f24c9a77644d57a36f3bdf2b8116b0", "little lamb", "小羊羔");INSERT INTO t_word (id, uid, word, translate) VALUES (17, "2d592499c65648b0a9519206688ef3f9", "I""m a big lion", "我是一只大狮子");
登录后复制

3、删除全文索引

alter table 表名 drop index 索引名;

4、全文索引使用

语法

MATCH(col1,col2,...) AGAINST(expr[search_modifier])search_modifier:{    IN NATURAL LANGUAGE MODE    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION    | IN BOOLEAN MODE    | WITH QUERY EXPANSION}
登录后复制

4.1 IN NATURAL LANGUAGE MODE

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

// 默认是使用 in natural language modeselect * from t_word where match(word) against ("lion");// 或者 显示写select * from t_word where match(word) against ("lion" in natural language mode);
登录后复制登录后复制

结果如下：

4.2 IN BOOLEAN MODE

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

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

示例：

// 默认是使用 in natural language modeselect * from t_word where match(word) against ("lion");// 或者 显示写select * from t_word where match(word) against ("lion" in natural language mode);
登录后复制登录后复制

// 排除包含lion记录、查询出包含cow或者little的记录，提升包含calf单词的排名，降低包含cow记录的排名，查询出以go开头的记录select * from t_word where match(word) against ("-lion cow little >calf 登录后复制好像问题都解决了， 但是问题才刚开始
回到最开始的需求，我想模糊搜索
select * from t_word where  match(word) against("lio" in boolean mode);
登录后复制预期值：把包含lion的都查询出来实际结果：啥都没有。
全匹配查询的时候能查询出来
select * from t_word where  match(translate) against("小水牛" in boolean mode);
登录后复制只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据
select * from t_word where  match(translate) against("小水" in boolean mode);
登录后复制奇怪了，这咋没出来呢？
中文分词与全文索引
InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：N-gram parser。
什么是N-gram？
在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“齿轮传动”来进行分词，得到的结果如下：
N=1 : "齿", "轮", "传", "动";N=2 : "齿轮", "轮传", "传动";N=3 : "齿轮传", "轮传动";N=4 : "齿轮传动";
登录后复制这个上面这个N是怎么去配置的？
查一下目前的值
show variables like "%token%";
登录后复制参数解析:
修改方式
方式1： 在my.cnf中修改/添加参数
[mysqld]ngram_token_size = 1
登录后复制方式2： 修改启动参数
mysqld --ngram_token_size=1复制代码
登录后复制实际使用
初始化测试数据
这里只提供部分测试数据，我下面sql使用全量数据，数据对不上
create table t_chinese_phrase(    id     int unsigned auto_increment comment "id"        primary key,    phrase varchar(32) not null comment "词组")    collate = utf8mb4_general_ci;INSERT INTO t_chinese_phrase (id, phrase) VALUES (278911, "阿昌族");INSERT INTO t_chinese_phrase (id, phrase) VALUES (279253, "八一南昌起义");INSERT INTO t_chinese_phrase (id, phrase) VALUES (282316, "昌明");INSERT INTO t_chinese_phrase (id, phrase) VALUES (282317, "昌盛");INSERT INTO t_chinese_phrase (id, phrase) VALUES (282318, "昌言");INSERT INTO t_chinese_phrase (id, phrase) VALUES (286534, "东昌纸");INSERT INTO t_chinese_phrase (id, phrase) VALUES (291525, "海昌蓝");INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (346682, "繁荣昌盛");INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (282317, "昌盛");INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287738, "繁盛");INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287736, "繁荣");
登录后复制添加索引
添加索引：
alter  table t_chinese_phrase add fulltext ful_phrase (phrase) with parser ngram;
登录后复制建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：
# test: 库名  t_chinese_phrase： 表名字SET GLOBAL innodb_ft_aux_table="test/t_chinese_phrase";# 查询分词情况SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;# 查询分词情况select * from information_schema.innodb_ft_index_table;
登录后复制查询结果如下：
查询
1、使用自然语言模式 NATURAL LANGUAGE MODE 查询
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘繁 荣 昌 盛’)。下面一个例子：
SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ("繁荣昌盛" in natural language mode) ;
登录后复制2、使用布尔模式（BOOLEAN MODE）查询
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘”繁荣昌盛“’)。下面一个例子：
SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ("繁荣昌盛" in boolean  mode) ;
登录后复制实际使用
回到我们最开始的查询需求，看看实际的效果
查询包含了“昌”的数据
SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ("昌" IN boolean  MODE) ;SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ("昌" ) order by id asc;
登录后复制可以看到结果：目前“昌”在任意位置都能被查询到。
查询执行计划如下：
耗时31ms（不走索引是90ms），耗时差不多是之前的1/3。
注意点
1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；
2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；
3、在mysql的stopword中的单词检索不出结果。可通过
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
登录后复制查询所有的stopword。遇到这种情况，有两种解决办法：
（1）stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法；
（2）使用布尔索引查询。
4、小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。
myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len
innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size
5、MySQL5.7.6之前的版本不支持中文，需使用第三方插件
6、全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。
【相关推荐：mysql视频教程】
以上就是聊聊MySQL全文索引怎么解决like模糊匹配查询慢的详细内容，更多请关注php中文网其它相关文章！ 
 
关键词：         
       全文索引 
              
       繁荣昌盛 
              
       自然语言