一文给你讲清楚MySql全文索引实战和原理

语言: CN / TW / HK

theme: channing-cyan

1:什么是全文索引

在当我们进行模糊查询的时候,一般都是使用 like 关键字来实现,在数据量小的情况下效率确实可以。有的人会说了,数据量大的时候我加索引啊。确实可以加索引,而且 like name% 是可以使用到索引的,但是当你要 like %name% 这样使用就会导致索引无效。在某些情况下就造成了局限性。

关于索引失效的原理可以移步这里:https://juejin.cn/post/7044793268780924935

在数据量大的情况下,全文索引的效率会比like高,在MySql5.6版本以前,只有MyISAM索引支持全文索引,在这之后Innodb也支持。

2:全文索引的创建和使用

```js //创建全文索引 CREATE FULLTEXT INDEX on tableName(字段名)

ALTER TABLE tableName ADD FULLTEXTindex_name;

CREATE TABLE tableName([....],FULLTEXT KEYindex_name) ```

和常用的like不同,全文索引有自己的格式,使用matchagainst关键字,如下:

js select * from user where match(name) against('aaa');

3:全文索引实战

创建一张用户表people,主键ID和用户名name

``js //插入四条记录 insert into people (name) VALUES ('a'); insert into people (name) VALUES ('aa'); insert into people (name) VALUES ('aaa'); insert into people (name`) VALUES ('aaaa');

//在字段name上建立全文索引 create fulltext index name_index_ft on people(name);

//开始查询 // 分别执行下面这4条SQL select * from people where match(name) against('a'); // 没记录 select * from people where match(name) against('aa'); // 没记录 select * from people where match(name) against('aaa'); // 1条记录 select * from people where match(name) against('aaaa'); // 1条记录 ```

很多人可能会问,为什么我数据库有 name = a 和 name = aa的记录,为什么会找不到,而且全文索引不是跟like一样可以模糊查询的吗??当我执行 select * from people where match(name) against('aaa'); 应该有二条记录啊,为什么只有一条??,接下来就要去了解全文的原理了

4:全文索引原理

在MySql客户端执行 show variables like '%ft%'; 查看全文索引的信息

image.png

为什么在查询 a 和 aa的时候会没有记录

先解释下其中几个参数

  • ft_min_word_len:针对MyISAM引擎的,也就是在你创建的全文索引的字段的内容最大长度

  • ft_max_word_len:针对MyISAM引擎的,也就是在你创建的全文索引的字段的内容最小长度 也就是说,只有在内容长度为 4 ~ 84(图中数值)的时候,你的全文索引才会有效,但是我们使用的Innodb存储引擎,所以你要看的是这二项

  • innodb_ft_min_token_size:针对Innodb引擎的,也就是在你创建的全文索引的字段的内容最小长度

  • innodb_ft_max_token_size:针对Innodb引擎的,也就是在你创建的全文索引的字段的内容最大长度

也就是说你的全文索引字段的内容长度必须在 3 ~ 84之间才会有效,现在能明白为什么在查 a 和 aa的时候找不到记录,但是在查 aaa 的时候就会有记录了吧,因为 aaa 长度刚好等于 3

为什么在查询 aaa的时候。name = aaaa的记录找不到

在全文索引底层是有一个切词的概念的,比如 祝中国越来越强大 全文索引按照一个规则切词,有可能会被切成 中国越来越强大。那么切词的依据是什么呢?全文索引又是怎么切词的呢??

image.png

在这里有个很重要的参数:ft_boolean_syntax,就是第一项,后面有很多内容,全文索引就是按照这些来切词的。现在表的记录如下

image.png

将记录更新如下:

image.png

```js 现在我们再来执行 select * from people where match(name) against('aaa');

```

image.png

现在就能把所有的记录都找出来了吧,但是细心的你会发现,id = 6 的这条记录还是没找出来,这是为什么??

全文索引为什么默认不支持模糊查询

为什么是默认呢,因为至少到现在我们发现全文索引都是 等值查询 的,这是因为全文索引默认支持的就是等值查询,如果要支持模糊查询需要改写SQL如下:

js select * from people where match(name) against('aaa*' in boolean mode);

image.png

现在就可以把所有符合的数据都查出来了,* 表示一个或多个字符