MySQL全文索引如何解决like模糊匹配查询慢的问题
这篇文章主要讲解了“MySQL全文索引如何解决like模糊匹配查询慢的问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL全文索引如何解决like模糊匹配查询慢的问题”吧！
需求需要模糊匹配查询一个单词
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的全文索引相关.
全文索引介绍1、发展历史旧版的MySQL的全文索引只能用在MyISAM存储引擎的char、varchar和text的字段上。
MySQL5.6.24上InnoDB引擎也加入了全文索引。
2、全文索引全文检索（Full-Text Search） 是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、词等信息，也可以进行各种统计和分析
3、创建全文索引若需对大量数据设置全文索引，建议先添加数据再创建索引。
1、创建表时创建全文索引
createtable表名(字段名1,字段名2,字段名3,字段名4,FULLTEXTfull_index_name(字段名))ENGINE=InnoDB; 2、为已有表添加全文索引
create fulltext index 索引名称 on 表名(字段名)；
eg:
createtablet_word(idintunsignedauto_incrementcomment'自增id'primarykey,uidchar(32)notnullcomment'32位唯一id',wordvarchar(256)nullcomment'英文单词',translatevarchar(256)null);createfulltextindexfull_idx_translateont_word(translate);createfulltextindexfull_idx_wordont_word(word);INSERTINTOt_word(id,uid,word,translate)VALUES(1,'9d592499c65648b0a9519206688ef3f9','lion','狮子');INSERTINTOt_word(id,uid,word,translate)VALUES(2,'ce26ac4239514bc6af481bcb1d9b67df','panda','熊猫');INSERTINTOt_word(id,uid,word,translate)VALUES(3,'a7d6042853c44904b68275daafb44702','tiger','老虎');INSERTINTOt_word(id,uid,word,translate)VALUES(4,'f13bd0a8ecea44fc9ade1625eeb4cc3c','goat','山羊');INSERTINTOt_word(id,uid,word,translate)VALUES(5,'27d5cbfc93a046388d712085e567474f','sheep','绵羊');INSERTINTOt_word(id,uid,word,translate)VALUES(6,'ed35df138cf348aa937781be8ee21cbf','lamb','羊羔');INSERTINTOt_word(id,uid,word,translate)VALUES(7,'fba5861d9527440990276e999f47ef8f','buffalo','水牛');INSERTINTOt_word(id,uid,word,translate)VALUES(8,'3a72e76f210841b1939fff0d3d721375','bull','公牛');INSERTINTOt_word(id,uid,word,translate)VALUES(9,'272e0b28ea7a48248a86f17533bf9943','cow','母牛');INSERTINTOt_word(id,uid,word,translate)VALUES(10,'47127adface54e418e4c1b9980af6d16','calf','小牛');INSERTINTOt_word(id,uid,word,translate)VALUES(11,'10592499c65648b0a9519206688ef3f9','littlelion','小狮子');INSERTINTOt_word(id,uid,word,translate)VALUES(12,'1bf095110b634a01bee5b31c5ee7ee0c','littlecow','母牛');INSERTINTOt_word(id,uid,word,translate)VALUES(13,'4813e588cde54c30bd65bfdbb243ad1f','littlecalf','小小牛');INSERTINTOt_word(id,uid,word,translate)VALUES(14,'5e377e281ad344048b6938a638b78ccb','littlebull','小公牛');INSERTINTOt_word(id,uid,word,translate)VALUES(15,'2855ad0da2964c7682c178eb8271f13d','littlebuffalo','小水牛');INSERTINTOt_word(id,uid,word,translate)VALUES(16,'72f24c9a77644d57a36f3bdf2b8116b0','littlelamb','小羊羔');INSERTINTOt_word(id,uid,word,translate)VALUES(17,'2d592499c65648b0a9519206688ef3f9','I''mabiglion','我是一只大狮子');
3、删除全文索引
alter table 表名 drop index 索引名;
语法
MATCH(col1,col2,...)AGAINST(expr[search_modifier])search_modifier:{INNATURALLANGUAGEMODE|INNATURALLANGUAGEMODEWITHQUERYEXPANSION|INBOOLEANMODE|WITHQUERYEXPANSION}
4.1 IN NATURAL LANGUAGE MODE
自然语言模式是MySQL 默认 的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
//默认是使用innaturallanguagemodeselect*fromt_wordwherematch(word)against('lion');//或者显示写select*fromt_wordwherematch(word)against('lion'innaturallanguagemode);
结果如下：
4.2 IN BOOLEAN MODE
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。推荐使用boolean模式
示例：
//默认是使用innaturallanguagemodeselect*fromt_wordwherematch(word)against('lion');//或者显示写select*fromt_wordwherematch(word)against('lion'innaturallanguagemode);
//排除包含lion记录、查询出包含cow或者little的记录，提升包含calf单词的排名，降低包含cow记录的排名，查询出以go开头的记录select*fromt_wordwherematch(word)against('-lioncowlittle>calf<cowgo*'inbooleanmode);
好像问题都解决了， 但是问题才刚开始
回到最开始的需求，我想模糊搜索
select*fromt_wordwherematch(word)against('lio'inbooleanmode);
预期值：把包含lion的都查询出来实际结果：啥都没有。
全匹配查询的时候能查询出来
select*fromt_wordwherematch(translate)against('小水牛'inbooleanmode);
只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据
select*fromt_wordwherematch(translate)against('小水'inbooleanmode);
奇怪了，这咋没出来呢？
中文分词与全文索引全文索引默认是只按照
空格进行分词的，所以当我完整的单个单词去查询的时候是能查出来的。但是使用部分单词去查询或者使用部分中文去查询时，是查询不出来数据的,像中文需要使用中文分词器进行分词。
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:'齿轮传动';
showvariableslike'%token%';
参数解析:
修改方式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中修改/添加参数
[mysqld]ngram_token_size=1
方式2： 修改启动参数
mysqld--ngram_token_size=1复制代码
实际使用初始化测试数据参数均不可动态修改，修改后需重启MySQL服务，并重新建立全文索引
这里只提供部分测试数据，我下面sql使用全量数据，数据对不上
createtablet_chinese_phrase(idintunsignedauto_incrementcomment'id'primarykey,phrasevarchar(32)notnullcomment'词组')collate=utf8mb4_general_ci;INSERTINTOt_chinese_phrase(id,phrase)VALUES(278911,'阿昌族');INSERTINTOt_chinese_phrase(id,phrase)VALUES(279253,'八一南昌起义');INSERTINTOt_chinese_phrase(id,phrase)VALUES(282316,'昌明');INSERTINTOt_chinese_phrase(id,phrase)VALUES(282317,'昌盛');INSERTINTOt_chinese_phrase(id,phrase)VALUES(282318,'昌言');INSERTINTOt_chinese_phrase(id,phrase)VALUES(286534,'东昌纸');INSERTINTOt_chinese_phrase(id,phrase)VALUES(291525,'海昌蓝');INSERTINTOtest.t_chinese_phrase(id,phrase)VALUES(346682,'繁荣昌盛');INSERTINTOtest.t_chinese_phrase(id,phrase)VALUES(282317,'昌盛');INSERTINTOtest.t_chinese_phrase(id,phrase)VALUES(287738,'繁盛');INSERTINTOtest.t_chinese_phrase(id,phrase)VALUES(287736,'繁荣');
mysql 全文索引使用倒排索引为 full inverted index
结构：{单词，(单词所在文档的ID，单词在具体文件中的位置)}
添加索引：
altertablet_chinese_phraseaddfulltextful_phrase(phrase)withparserngram;
建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子：
#test:库名t_chinese_phrase：表名字SETGLOBALinnodb_ft_aux_table="test/t_chinese_phrase";#查询分词情况SELECT*FROMINFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;#查询分词情况select*frominformation_schema.innodb_ft_index_table;
查询结果如下：
因为我们上面设置了分词数是1，所以，可以看到都是按照一个词进行分词的。
查询1、使用自然语言模式 NATURAL LANGUAGE MODE 查询字段解析：
FIRST_DOC_ID ：word第一次出现的文档ID
LAST_DOC_ID : word最后一次出现的文档ID
DOC_COUNT ：含有word的文档个数
DOC_ID ：当前文档ID
POSITION : word 当在前文档ID的位置
在自然语言模式（NATURAL LANGUAGE MODE)下，文本的查询被转换为n-gram分词查询的
并集。
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘繁 荣 昌 盛’)。下面一个例子：
SELECT*FROMt_chinese_phraseWHEREMATCH(phrase)AGAINST('繁荣昌盛'innaturallanguagemode);
布尔模式（BOOLEAN MODE）文本查询被转化为n-gram分词的
短语查询
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘”繁荣昌盛“’)。下面一个例子：
SELECT*FROMt_chinese_phraseWHEREMATCH(phrase)AGAINST('繁荣昌盛'inbooleanmode);
回到我们最开始的查询需求，看看实际的效果
查询包含了“昌”的数据
SELECT*FROMt_chinese_phraseWHEREMATCH(phrase)AGAINST('昌'INbooleanMODE);SELECT*FROMt_chinese_phraseWHEREMATCH(phrase)AGAINST('昌')orderbyidasc;
可以看到结果：目前“昌”在任意位置都能被查询到。
查询执行计划如下：
耗时31ms（不走索引是90ms），耗时差不多是之前的1/3。
1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；
2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；
3、在mysql的stopword中的单词检索不出结果。可通过
SELECT*FROMINFORMATION_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全文索引如何解决like模糊匹配查询慢的问题”的内容了，经过本文的学习后，相信大家对MySQL全文索引如何解决like模糊匹配查询慢的问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
