2025-06-14发表2025-06-14更新MySQL系列26 分钟读完 (大约3909个字)

MySQL系列(33)：全文索引与ngram分词应用实践

MySQL的全文索引在实践应用中有一些细节是需要注意的。

例如，中文分词、中文全词精准匹配、联合全文索引的使用。

分词器

创建全文索引

示例：存放文章的表，文章语言文字是中文，要搜索内容，给内容字段创建全文索引：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(content)
);

在 MySQL 里，不同存储引擎的全文索引默认分词器有所不同，下面介绍常用存储引擎 MyISAM 和 InnoDB 的情况：

MyISAM 存储引擎

MyISAM 存储引擎默认的全文索引分词器基于空格和标点符号进行分词。它会把文本按空格、大部分标点符号分割成独立的词项，像逗号、句号、问号等。此外，它还会忽略一些常见的短词（停用词），例如 the、and、is 等。

InnoDB 存储引擎

从 MySQL 5.6 开始，InnoDB 支持全文索引，其默认分词器和 MyISAM 类似，也是基于空格和标点符号分词，同时会忽略停用词。

示例说明，假设有如下文本：

text

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
3
SHOW 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 分词器。

-- 创建表并使用 ngram 分词器
CREATE TABLE fulltext_example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    FULLTEXT(content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO chinese_text (content) VALUES ('这是一个 MySQL 全文索引测试示例');

分词长度

最小词长：MySQL 默认忽略小于 4 个字符的词，可通过修改 ft_min_word_len 系统变量调整，但修改后需重启 MySQL 服务。
停用词：MySQL 有停用词列表，列表中的词在全文搜索时会被忽略。若需自定义停用词列表，可修改配置文件。
性能影响：创建和维护联合全文索引会占用一定系统资源，插入、更新和删除数据时可能影响性能。

-- 查看当前最小词长
SHOW VARIABLES LIKE 'ft_min_word_len';
-- 修改最小词长为 3
SET GLOBAL ft_min_word_len = 3;

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 设置就会生效。

创建表时使用设置

-- 创建表并使用 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，已经创建的全文索引不会自动更新，你需要先删除原索引，再重新创建。示例如下：

-- 删除全文索引
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 循环进行分词。

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE simulate_ngram_tokenization(IN input_text TEXT, IN token_size INT)
BEGIN
    -- 声明变量
    DECLARE text_length INT;
    DECLARE i INT;
    
    -- 获取文本长度
    SET text_length = CHAR_LENGTH(input_text);
    SET i = 1;

    -- 创建临时表存储分词结果
    DROP TEMPORARY TABLE IF EXISTS temp_ngram_tokens;
    CREATE TEMPORARY TABLE temp_ngram_tokens (token VARCHAR(255));

    -- 循环截取分词
    WHILE i <= text_length - token_size + 1 DO
        INSERT INTO temp_ngram_tokens (token) VALUES (SUBSTRING(input_text, i, token_size));
        SET i = i + 1;
    END WHILE;

    -- 查询分词结果
    SELECT token FROM temp_ngram_tokens;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_ngram_tokens;
END //

DELIMITER ;

-- 调用存储过程
CALL simulate_ngram_tokenization('这是一个 MySQL 全文索引测试示例', 2);

方案二：使用递归 CTE

递归 CTE 可在普通 SQL 里实现循环逻辑，下面是使用递归 CTE 模拟 ngram 分词结果的示例。

-- 设置要分词的文本和分词长度
SET @input_text = '这是一个 MySQL 全文索引测试示例';
SET @token_size = 2;

-- 使用递归 CTE 进行分词
WITH RECURSIVE ngram_tokens AS (
    -- 初始查询
    SELECT 
        1 AS pos,
        SUBSTRING(@input_text, 1, @token_size) AS token
    UNION ALL
    -- 递归查询
    SELECT 
        pos + 1,
        SUBSTRING(@input_text, pos + 1, @token_size)
    FROM 
        ngram_tokens
    WHERE 
        pos + @token_size - 1 < CHAR_LENGTH(@input_text)
)
SELECT token FROM ngram_tokens;

方案对比

存储过程：逻辑清晰，易于理解和维护，适合复杂的业务逻辑。不过需要创建和调用存储过程，相对麻烦。
递归 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解析器

-- 创建表
CREATE TABLE articles_no_ngram (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    FULLTEXT(content)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO articles_no_ngram (content) VALUES ('苹果手机是一款优秀的产品');

-- 搜索
SELECT * FROM articles_no_ngram 
WHERE MATCH(content) AGAINST('苹果' IN NATURAL LANGUAGE MODE);
-- 可能无法搜索到结果

使用ngram解析器

-- 创建表
CREATE TABLE articles_ngram (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    FULLTEXT(content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO articles_ngram (content) VALUES ('苹果手机是一款优秀的产品');

-- 搜索
SELECT * FROM articles_ngram 
WHERE MATCH(content) AGAINST('苹果' IN NATURAL LANGUAGE MODE);
-- 大概率能搜索到结果

综上所述，若数据库中包含大量中文文本且需要进行全文搜索，使用 ngram 解析器的全文索引能提供更好的搜索效果；若主要处理英文等有明确分隔符的文本，可不使用 ngram 解析器。

全词精准匹配

在 MySQL 里，全文索引可以实现全词精准匹配，下面分别介绍自然语言模式、布尔模式和查询扩展模式下实现全词精准匹配的方法。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);

INSERT INTO articles (title, content) VALUES
('MySQL Full - Text Search', 'This article is about MySQL full - text search.'),
('Full - Text Search Example', 'An example of full - text search in MySQL.');

自然语言模式

自然语言模式是全文索引默认的搜索模式，一般用于查找包含搜索词的文档。若要实现全词精准匹配，可将搜索词用双引号包裹。

1 2	SELECT * FROM articles WHERE MATCH(title, content) AGAINST('"MySQL"' IN NATURAL LANGUAGE MODE);

布尔模式

布尔模式允许使用各种操作符构建复杂的搜索条件，用双引号包裹搜索词也能实现全词精准匹配。

1 2	SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+"MySQL"' IN BOOLEAN MODE);

这里 + 操作符表示该词必须出现在匹配的文档中，双引号确保全词精准匹配。

查询扩展模式

查询扩展模式会在初始搜索结果基础上，查找与这些结果相关的其他文档。同样，用双引号包裹搜索词可实现全词精准匹配。

1 2	SELECT * FROM articles WHERE MATCH(title, content) AGAINST('"MySQL"' WITH QUERY EXPANSION);

注意事项

最小词长：MySQL 有最小词长限制，默认小于 4 个字符的词会被忽略。可通过修改 ft_min_word_len 系统变量调整，但修改后需重启 MySQL 服务。

-- 查看当前最小词长
SHOW VARIABLES LIKE 'ft_min_word_len';
-- 修改最小词长为 3
SET GLOBAL ft_min_word_len = 3;

联合全文索引

在 MySQL 里，全文索引可以使用多个字段，这被称为联合全文索引。联合全文索引允许在多个 CHAR、VARCHAR 或 TEXT 类型的字段上同时创建索引，方便在这些字段组合的数据里进行全文搜索。

前提条件

存储引擎：MyISAM 和 InnoDB 存储引擎支持全文索引，推荐使用 InnoDB，因为它支持事务。
字段类型：仅 CHAR、VARCHAR 或 TEXT 类型的字段能创建全文索引。

创建联合全文索引

创建表时创建联合全文索引

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    -- 在 title 和 content 字段上创建联合全文索引
    FULLTEXT(title, content)
) ENGINE=InnoDB;

在已有表上添加联合全文索引

-- 创建表
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
3

SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('example keyword' IN NATURAL LANGUAGE MODE);

此查询会在 title 和 content 字段里查找包含 “example keyword” 的记录。

布尔模式
1
2
3
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('+example -keyword' IN BOOLEAN MODE);
此查询表示查找 title 或 content 字段包含 “example” 但不包含 “keyword” 的记录。+ 表示必须包含，- 表示必须排除。

查询扩展模式

1
2
3

SELECT *
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/

作者

光星

发布于

2025-06-14

更新于

2025-06-14

许可协议

#全文索引全文搜索

MySQL系列(33)：全文索引与ngram分词应用实践

分词器

创建全文索引

MyISAM 存储引擎

InnoDB 存储引擎

自定义分词器

检查是否安装

安装ngram插件

指定 ngram 分词器

分词长度

ngram分词器

分词长度

分词长度设置

模拟 ngram 分词结果

方案一：使用存储过程

方案二：使用递归 CTE

方案对比

是否使用ngrm的区别

分词规则

对中文支持

搜索匹配度

索引大小和性能

示例对比

不使用ngram解析器

使用ngram解析器

全词精准匹配

自然语言模式

布尔模式

查询扩展模式

注意事项

联合全文索引

前提条件

创建联合全文索引

联合全文索引查询

作者

发布于

更新于

许可协议

喜欢这篇文章？打赏一下作者吧

评论

目录

指定 `ngram` 分词器