mysql 全文索引使用以及限制

项目场景：

最近在做性能优化，有使用到全文索引，做下记录。

---

问题描述

我们在做列表查询的时候，大多数情况都会遇到 like '%%'的查询，而这种查询会让索引失效
在数据量较大的时候，会让查询变得很慢，当然我们可以使用es去做优化，es的引入也会让代码变得复杂相对不使用来说，因此我们在没必要的时候就直接使用mysql就好了。

---

---

解决方案：

一、mysql 全文索引的限制，需要mysql版本在5.7及以上版本才支持，不知道版本的使用一下指令查看版本号。

select version();
1

二、建立索引sql如下

CREATE FULLTEXT INDEX idx_fullname ON t_course(name) WITH PARSER ngram;
CREATE FULLTEXT INDEX idx_fullname ON t_course_type(name) WITH PARSER ngram;
1
2

这里需要使用WITH PARSER ngram 适用于中文分词，如果要搜索的内容为英文，可以不加后面这段。
三、修改mysql配置如下，
在你的mysql安装目录下，找到该配置文件，D:\javaTools\mysql-8.0.12-winx64\my.ini，如果没有就自己新建一个。添加如下配置

innodb_ft_min_token_size = 1 #配置分词最小单位
ft_min_word_len = 1 #配置分词最小单位
1
2

修改完后需要重启才生效。
加这两个配置原因是要把粒度分到最小，如果不是，则有可能你搜，“你好”，内容含有 这两个字，你搜索不到，这两个值默认是4。所以要修改下。
重启完后使用指令查看配置是否生效

show variables like '%ft%';
1

四、使用全文索引搜索。

select
	tc.id,
	tc.name
from
	t_course tc 
where
match(name) against ('测试') limit 5
1
2
3
4
5
6
7

可以看到测试相关的内容就查出来了，explain一下看看是否走索引。

可以看到是走了刚刚的全文索引。
五、多字段全文索引

CREATE FULLTEXT INDEX idx_fullname ON t_course(name,字段名) WITH PARSER ngram;
1

match(name,字段名) against ('测试')
1

像上面这样类似建立一个组合索引即可。
六、join查询两个表全文索引。
有一种情况是join查询，两个表都有字段要全文索引，这种情况经过测试，是不生效的，类似还有一个coursetype表name字段也全文索引。

explain select
	tc.id,
	tc.name,
	tct.name,
	tct.id 
from
	t_course tc left join t_course_type tct  on tc.course_type =tct.id 
where
1=1 
and (
match(tc.name) against ('测试') 
or match(tct.name) against ('测试') 
)
1
2
3
4
5
6
7
8
9
10
11
12
13

这样子是不会走索引的，同时模糊搜索课程的名字和类型名字。这种情况只能另外想办法解决了，可以考虑 使用
(a left join b where match(a.name) ) union (a right join b math(b.name))
这种方式去实现，这样sql复杂度提升了不少，其实不建议这样子用，还不如直接放es算了。

总结

介绍了下如何使用全文索引，要注意的点已经上面都有写了，替大家走了坑总结实践出来的使用方案，大家如果有需要可以参考下。