MySQL 全文索引

前言

最近项目中需要对特殊字段进行全文索引,方便检索查询,因此整理了一下关于MySQl全文索引的一些内容

MySQL 8.x 全文索引

内置了 ngram 全文解析器,以及一个可以安装的日文 mecab 全文解析器插件

配置开启全文索引

需要在 MySQL 配置文件中 [mysqld] 配置项下增加三个参数:

1
2
3
4
5
innodb_ft_min_token_size = 1
ft_min_word_len = 1
// 在使用中文检索分词插件 ngram 之前,先得在 MySQL 配置文件里面设置分词大小
ngram_token_size = 1

增加配置之后需要重启 MySQL & 重建数据表中的 FULLTEXT 索引。

查看修改配置是否生效:

1
SHOW VARIABLES LIKE '%ft%';

官网文档:

  1. innodb_ft_min_token_size
  2. ft_min_word_len

创建 & 使用全文索引

创建表的同时创建全文索引

1
2
3
4
5
6
CREATE TABLE goods (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (100),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB;

通过 ALTER TABLE 方式添加

1
ALTER TABLE goods ADD FULLTEXT INDEX ft_index (title, body) WITH PARSER ngram;

通过 CREATE INDEX 方式添加

1
CREATE FULLTEXT INDEX ft_index ON goods (title, body) WITH PARSER ngram;

使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// 指定检索模式
SELECT * FROM goods
WHERE MATCH (title, body)
AGAINST ('敬业 爱国' IN NATURAL LANGUAGE MODE);
// 不指定检索模式,默认使用自然语言模式
SELECT * FROM goods
WHERE MATCH (title, body)
AGAINST ('敬业 爱国');
// 使用 BOOLEAN 模式执行高级查询,必须包含“爱国”
SELECT * FROM goods
WHERE MATCH (title, body)
AGAINST ('+爱国' IN BOOLEAN MODE);
// 使用 BOOLEAN 模式执行高级查询,必须包含"爱国",但是不能包含"你懂的"
SELECT * FROM goods
WHERE MATCH (title, body)
AGAINST ('+爱国 -你懂的' IN BOOLEAN MODE);
// 获取匹配结果记录数
SELECT COUNT(*) FROM goods
WHERE MATCH (title, body)
AGAINST ('敬业 爱国' IN NATURAL LANGUAGE MODE);
// 照相关性排序,相关性高的在前面。相关性的值是一个非负浮点数,0表示无相关性。
SELECT id,title,
MATCH (title,body) AGAINST ('手机' IN NATURAL LANGUAGE MODE) AS score
FROM goods ORDER BY score DESC;

注意

  1. 全文索引只支持 InnoDB 和 MyISAM 引擎
  2. 只能在类型为 CHAR,VARCHAR 或者 TEXT 的字段上创建全文索引
  3. MATCH (columnName) AGAINST (‘keywords’) MATCH() 函数使用的字段名,必须要与创建全文索引时指定的字段名一致。如上面的示例,MATCH (title,body)使用的字段名与全文索引 ft_index(title,body) 定义的字段名一致。如果要对 title 或者 body 字段分别进行查询,就需要在 title 和 body 字段上分别创建全文索引。
  4. 导入大量数据时,先导入数据再在创建全文索引比先创建全文索引再导入数据快很多,全文索引很影响 TPS。

扩展阅读

禁用词

  1. MyISAM搜索索引的禁用词

全文检索模式

  1. 自然语言检索模式(fulltext-natural-language)
  2. 布尔检索模式(fulltext-boolean)
  3. 查询扩展检索模式(fulltext-query-expansion)
----本文结束 感谢您的阅读----