MySQL系列(33):全文索引与ngram分词应用实践
MySQL的全文索引在实践应用中有一些细节是需要注意的。
例如,中文分词、中文全词精准匹配、联合全文索引的使用。
分词器
创建全文索引
示例:存放文章的表,文章语言文字是中文,要搜索内容,给内容字段创建全文索引:
1 | CREATE TABLE articles ( |
在 MySQL 里,不同存储引擎的全文索引默认分词器有所不同,下面介绍常用存储引擎 MyISAM 和 InnoDB 的情况:
MyISAM 存储引擎
MyISAM 存储引擎默认的全文索引分词器基于空格和标点符号进行分词。它会把文本按空格、大部分标点符号分割成独立的词项,像逗号、句号、问号等。此外,它还会忽略一些常见的短词(停用词),例如 the
、and
、is
等。
InnoDB 存储引擎
从 MySQL 5.6 开始,InnoDB 支持全文索引,其默认分词器和 MyISAM 类似,也是基于空格和标点符号分词,同时会忽略停用词。
示例说明,假设有如下文本:
1 | Hello, world! This is a test. |
默认分词器会将其分割成 Hello
、world
、This
、test
这些词项,而 is
、a
会因属于停用词被忽略,逗号、感叹号、句号等标点符号也会被去除。
自定义分词器
若默认分词器无法满足需求,MySQL 支持自定义分词器,可借助插件实现,例如使用 ngram
插件支持中文等非空格分隔语言的分词。
MySQL 默认没有集成 ngram
插件。ngram
插件是一个可选插件,用于支持对非空格分隔语言(如中文、日文、韩文等)进行全文索引分词。
检查是否安装
通过以下 SQL 语句检查 ngram
插件是否已经安装:
MySQL 8.0 及以上版本:
在 MySQL 8.0 及以上版本,推荐使用
INFORMATION_SCHEMA.PLUGINS
表来查看插件信息,示例如下:1
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'ngram'
若查询结果不为空,则表明 ngram
插件已安装;若为空,则未安装。
MySQL 5.x 版本:
使用
SHOW PLUGINS
查看所有插件,然后在结果里查找ngram
插件,示例如下:1
2
3SHOW PLUGINS;
SHOW PLUGINS LIKE 'ngram';
执行该语句后,会返回一个包含所有已安装插件信息的结果集,可以手动查找 ngram
插件。
安装ngram插件
下面是安装和使用 ngram
插件的示例:
Linux系统安装
ngram
插件1
INSTALL PLUGIN ngram SONAME 'ngram.so';
在 Windows 系统中,
ngram.so
需替换为ngram.dll
,即:1
INSTALL PLUGIN ngram SONAME 'ngram.dll';
注意事项:
权限要求:执行
INSTALL PLUGIN
语句需要SUPER
权限或者INSTALL PLUGIN
权限。持久化安装:上述安装方式是临时的,MySQL 重启后插件会失效。若要永久安装,需在 MySQL 配置文件(如
my.cnf
或my.ini
)中添加如下配置:1
2[mysqld]
plugin-load-add=ngram.so在 Windows 系统中,配置为:
1
2[mysqld]
plugin-load-add=ngram.dll
指定 ngram
分词器
下面为提供创建表、插入数据、查看分词结果模拟的示例。
创建表并在 content
列上创建全文索引,指定使用 ngram
分词器。
1 | -- 创建表并使用 ngram 分词器 |
分词长度
最小词长:MySQL 默认忽略小于 4 个字符的词,可通过修改
ft_min_word_len
系统变量调整,但修改后需重启 MySQL 服务。停用词:MySQL 有停用词列表,列表中的词在全文搜索时会被忽略。若需自定义停用词列表,可修改配置文件。
性能影响:创建和维护联合全文索引会占用一定系统资源,插入、更新和删除数据时可能影响性能。
1 | -- 查看当前最小词长 |
ngram分词器
分词长度
ngram
解析器会按照固定长度对文本进行分词。
ngram_token_size
的取值范围是 1 到 10,默认值为 2。- 若修改了
ngram_token_size
,已经创建的全文索引不会自动更新,你需要先删除原索引,再重新创建。
如果想调整分词长度,可以修改 ngram_token_size
系统变量,不过在 MySQL 5.7.3 里,修改该变量需要重启服务才能生效。
分词长度设置
查看当前
ngram_token_size
1
SHOW VARIABLES LIKE 'ngram_token_size';
当前会话临时设置
ngram_token_size
1
2-- 设置当前会话的 ngram_token_size 为 2
SET SESSION ngram_token_size = 2;此设置仅在当前会话有效,会话结束后设置会失效。
全局设置,对所有新会话生效,可使用全局级别的
SET
语句1
2-- 设置全局的 ngram_token_size 为 3
SET GLOBAL ngram_token_size = 3;此设置不会影响已经存在的会话,并且 MySQL 重启后设置会失效。
配置文件设置(永久生效)
若要让
ngram_token_size
设置在 MySQL 重启后依然有效,可在 MySQL 配置文件(如my.cnf
或my.ini
)中添加或修改以下配置:1
2[mysqld]
ngram_token_size = 4修改完成后,重启 MySQL 服务,新的
ngram_token_size
设置就会生效。创建表时使用设置
1
2
3
4
5
6-- 创建表并使用 ngram 分词器,使用当前设置的 ngram_token_size
CREATE TABLE fulltext_ngram_table (
id INT AUTO_INCREMENT PRIMARY KEY,
content TEXT,
FULLTEXT(content) WITH PARSER ngram
) ENGINE=InnoDB;重建全文索引
若修改了
ngram_token_size
,已经创建的全文索引不会自动更新,你需要先删除原索引,再重新创建。示例如下:1
2
3
4
5-- 删除全文索引
ALTER TABLE fulltext_ngram_table DROP INDEX content;
-- 重新创建全文索引
ALTER TABLE fulltext_ngram_table ADD FULLTEXT(content) WITH PARSER ngram;
模拟 ngram 分词结果
分别是使用存储过程和通过递归 CTE(Common Table Expression,公共表表达式)来模拟 ngram
分词结果。
方案一:使用存储过程
创建一个存储过程,在存储过程里使用 WHILE 循环进行分词。
1 | -- 创建存储过程 |
方案二:使用递归 CTE
递归 CTE 可在普通 SQL 里实现循环逻辑,下面是使用递归 CTE 模拟 ngram 分词结果的示例。
1 | -- 设置要分词的文本和分词长度 |
方案对比
- 存储过程:逻辑清晰,易于理解和维护,适合复杂的业务逻辑。不过需要创建和调用存储过程,相对麻烦。
- 递归 CTE:可直接在 SQL 里使用,无需创建额外的存储程序。但对于长文本,性能可能不如存储过程。
你可根据实际需求选择合适的方案。
是否使用ngrm的区别
使用 ngram 解析器的全文索引与没使用 ngrm 的区别
在 MySQL 中,使用 ngram
解析器的全文索引与不使用 ngram 解析器的全文索引在分词规则、对中文支持、搜索匹配度和性能方面存在显著区别,下面详细介绍。
分词规则
- 不使用 ngram 解析器:默认采用基于空格、标点等分隔符的分词规则。在处理英文等有明确分隔符的文本时,能按单词进行分词;但处理中文时,由于中文词与词之间无明显分隔符,会将整个中文文本当作一个词或按标点简单分割。
- 使用 ngram 解析器:按照固定长度(由 ngram_token_size 控制,默认值为 2)对文本进行分词。它会将文本拆分成连续的字符组合,不依赖空格和标点,能更好地处理中文文本。
对中文支持
- 不使用 ngram 解析器:对中文支持较差,在进行全文搜索时,很难准确匹配到中文词汇。例如,查询 “苹果” 时,可能无法从包含 “苹果手机” 的文本中搜索到结果。
- 使用 ngram 解析器:能有效提升对中文的支持。以 ngram_token_size 为 2 为例,“苹果手机” 会被分词为 “苹果”、“果手”、“手机”,搜索 “苹果” 时就能匹配到包含该词的文本。
搜索匹配度
- 不使用 ngram 解析器:对于中文搜索,精确匹配难度大,模糊匹配结果不准确,容易遗漏相关记录。
- 使用 ngram 解析器:由于对中文进行了分词处理,搜索匹配度更高,能更准确地返回相关记录。不过,可能会因为分词过细导致一些无关结果,例如搜索 “苹果” 时,包含 “果盘” 的文本也可能被部分匹配。
索引大小和性能
- 不使用 ngram 解析器:索引大小相对较小,因为分词数量较少。插入、更新和删除操作的性能较好,因为维护索引的开销较小。但搜索性能可能较差,尤其是在处理中文搜索时。
- 使用 ngram 解析器:索引大小通常较大,因为会生成更多的分词。插入、更新和删除操作的性能会受到一定影响,因为需要维护更多的索引项。不过,搜索性能在中文场景下通常会有显著提升。
示例对比
不使用ngram解析器
1 | -- 创建表 |
使用ngram解析器
1 | -- 创建表 |
综上所述,若数据库中包含大量中文文本且需要进行全文搜索,使用 ngram
解析器的全文索引能提供更好的搜索效果;若主要处理英文等有明确分隔符的文本,可不使用 ngram
解析器。
全词精准匹配
在 MySQL 里,全文索引可以实现全词精准匹配,下面分别介绍自然语言模式
、布尔模式
和查询扩展模式
下实现全词精准匹配的方法。
1 | CREATE TABLE articles ( |
自然语言模式
自然语言模式是全文索引默认的搜索模式,一般用于查找包含搜索词的文档。若要实现全词精准匹配,可将搜索词用双引号包裹。
1 | SELECT * FROM articles |
布尔模式
布尔模式允许使用各种操作符构建复杂的搜索条件,用双引号包裹搜索词也能实现全词精准匹配。
1 | SELECT * FROM articles |
这里 +
操作符表示该词必须出现在匹配的文档中,双引号确保全词精准匹配。
查询扩展模式
查询扩展模式会在初始搜索结果基础上,查找与这些结果相关的其他文档。同样,用双引号包裹搜索词可实现全词精准匹配。
1 | SELECT * FROM articles |
注意事项
最小词长:MySQL 有最小词长限制,默认小于 4 个字符的词会被忽略。可通过修改 ft_min_word_len
系统变量调整,但修改后需重启 MySQL 服务。
1 | -- 查看当前最小词长 |
联合全文索引
在 MySQL 里,全文索引可以使用多个字段,这被称为联合全文索引。联合全文索引允许在多个 CHAR
、VARCHAR
或 TEXT
类型的字段上同时创建索引,方便在这些字段组合的数据里进行全文搜索。
前提条件
- 存储引擎:MyISAM 和 InnoDB 存储引擎支持全文索引,推荐使用 InnoDB,因为它支持事务。
- 字段类型:仅 CHAR、VARCHAR 或 TEXT 类型的字段能创建全文索引。
创建联合全文索引
创建表时创建联合全文索引
1
2
3
4
5
6
7CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
content TEXT,
-- 在 title 和 content 字段上创建联合全文索引
FULLTEXT(title, content)
) ENGINE=InnoDB;在已有表上添加联合全文索引
1
2
3
4
5
6
7
8
9-- 创建表
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
content TEXT
) ENGINE=InnoDB;
-- 添加联合全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);
联合全文索引查询
可以使用 MATCH AGAINST 语法在多个字段上进行全文搜索。
自然语言模式
1
2
3SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('example keyword' IN NATURAL LANGUAGE MODE);此查询会在
title
和content
字段里查找包含 “example keyword” 的记录。布尔模式
1
2
3SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('+example -keyword' IN BOOLEAN MODE);此查询表示查找
title
或content
字段包含 “example” 但不包含 “keyword” 的记录。+
表示必须包含,-
表示必须排除。查询扩展模式
1
2
3SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('example keyword' WITH QUERY EXPANSION);查询扩展模式会先执行初始查询,再基于初始结果查找相关文档。
MySQL系列(33):全文索引与ngram分词应用实践
http://blog.gxitsky.com/2025/06/14/MySQL-33-InnoDB-Full-Text-Indexes-Action/