MySQL 全文索引

课程 : 《MySQL 基础与优化教程》

返回课程

Mysql全文索引介绍

在搜索数据库中的大量数据时，全文搜索确实非常强大，可以大幅度提高搜索效率。

1. MySQL中的全文索引是FultLeX类型的索引。

2. 全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建。

3. 在MySQL 5.7.6中，MySQL提供了支持中文、日文和韩文（CJK）的内置全文ngram解析器，以及用于日文的可安装MeCab全文解析器插件

4. 当创建表时，可以在CREATE TABLE语句中给出FULLTEXT索引定义，或者稍后使用ALTER TABLE或CREATE INDEX添加该定义。

5. 对于大型数据集，将数据加载到没有FULLTEXT索引的表中然后创建索引要比将数据加载到具有现有FULLTEXT索引的表中快得多。

创建索引

语法 :

FULLTEXT INDEX `索引名称`(`字段`, `字段`, ...) WITH PARSER `ngram`

示例 :

# 创建时
CREATE TABLE `articles`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '文章ID',
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '文章标题',
  `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '文章内容',
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `article_fulltext`(`title`, `content`) WITH PARSER `ngram`
);

# 修改已有数据表
ALTER TABLE articles ADD FULLTEXT INDEX article_fulltext (title,content) with parser ngram;

使用全文搜索查询

准备工作

查询并修改最小索引长度

show variables like '%ft%';

-- 输出 --
# MyISAM 引擎
ft_max_word_len	84
ft_min_word_len	4
# innodb 引擎
innodb_ft_max_token_size	84
innodb_ft_min_token_size	3

可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引。

配置最小搜索长度

需要修改mysql的配置文件来修改。

windows 平台下 :

mysql 的配置文件位置通过我的电脑 > 右键 > 服务 > mysql > 右键 > 属性查看 ( 没有此文件请创建 )。

linux 平台下 mysql 的配置文件是在 /etc/my.cnf 中 ( 没有此文件请创建 )。

在配置追加以下内容：

[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2

重启 mysql

windows : 右键我的电脑服务 mysql 右键重新启动；

如笔者测试环境配置文件位置 : C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

linux ：命令 systemctl restart mysqld

全文索引查询模式

自然语言检索模式 Natural Language

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


EXPLAIN 
SELECT * FROM articles 
WHERE MATCH(title,content) AGAINST('语言') 
# 默认使用自然语言模式，上面的命令等同于 :
EXPLAIN 
SELECT * FROM articles 
WHERE MATCH(title,content) AGAINST('语言' IN NATURAL LANGUAGE MODE);

BOOLEAN 模式 BOOLEAN MODE

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

MySQL布尔全文检索运算符

下表说明了全文检索布尔运算符及其含义：

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

以下示例说明如何在搜索查询中使用布尔全文运算符：

示例	描述
'mysql tutorial'	要搜索包含两个单词中至少一个的行：mysql或tutorial
'+mysql +tutorial'	要搜索包含两个单词的行：mysql和tutorial
'+mysql tutorial'	要搜索包含单词“mysql”的行，但为包含“tutorial”的行添加更高的排名
'+mysql -tutorial'	要搜索包含单词“mysql”但不包含“tutorial”的行
'+mysql~tutorial'	要搜索包含单词“mysql”的行，如果它包含单词“tutorial”，则将行排名更低。
'+mysql +(> tutorial <training)'	
要以任何顺序搜索包含单词“mysql”和“tutorial”，或“mysql”和“training”的行，
但将包含“mysql tutorial”的行放在“mysql training”之上。
'my*' 要查找包含以“my”开头的单词的行，例如“mysql”，“myyahoo”等

示例 :

SELECT * FROM articles 
WHERE MATCH(title,content) AGAINST('+非常 +世界' IN BOOLEAN MODE);

注意事项

使用 show variables like '%ft%'; 查询最小分词长度，然后修改 MySql 配置 :
ft_min_word_len : 2
innodb_ft_min_token_size : 2

建议使用命令创建索引；
ALTER TABLE `articles` ADD FULLTEXT INDEX 全文索引 (title, content) with parser ngram;

创建索引后建议修复表；