MySQL的`FULLTEXT`索引：如何进行自然语言与布尔模式搜索？

MySQL FULLTEXT 索引：自然语言与布尔模式搜索

大家好，今天我们来深入探讨 MySQL 中 FULLTEXT 索引的强大功能，特别是如何在自然语言和布尔模式下进行搜索。FULLTEXT 索引是 MySQL 提供的一种特殊类型的索引，专为高效地在文本数据中进行搜索而设计。它允许我们超越简单的 LIKE 谓词，实现更复杂、更智能的文本搜索。

FULLTEXT 索引的基础

FULLTEXT 索引可以应用于 CHAR, VARCHAR, 和 TEXT 列。创建 FULLTEXT 索引的语法很简单：

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

-- 或者在已有表上添加索引
ALTER TABLE articles ADD FULLTEXT index_article (title, content);

上面的例子中，我们在 articles 表的 title 和 content 列上创建了一个名为 index_article 的 FULLTEXT 索引。 这意味着我们可以利用这个索引来搜索文章的标题和内容。

重要提示： 默认情况下，FULLTEXT 索引会忽略一些常见的词，被称为“停用词”（stop words）。 这些词如 "the", "a", "is" 等，因为它们在文本中出现频率很高，但通常没有实际的搜索价值。 MySQL 有一个默认的停用词列表，我们也可以自定义这个列表。

自然语言搜索

自然语言搜索是 FULLTEXT 索引最基本的使用方式。 它试图理解用户的搜索意图，并返回与搜索词最相关的结果。 使用 MATCH...AGAINST 语法进行自然语言搜索：

SELECT id, title, content
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL tutorial' IN NATURAL LANGUAGE MODE);

这个查询会返回 title 或 content 中包含 "MySQL" 和 "tutorial" 的文章。 IN NATURAL LANGUAGE MODE 是可选的，因为它是默认模式。 MySQL 会根据 title 和 content 中搜索词的出现频率和位置，计算出一个相关性评分。 我们可以使用这个评分来对结果进行排序：

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL tutorial' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL tutorial' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

relevance 列显示了每篇文章与搜索词的相关性评分。评分越高，文章与搜索词越相关。

自然语言搜索的特点：

• 相关性排序： 结果根据相关性评分排序，最相关的结果排在前面。
• 停用词： 默认忽略停用词。
• 最小词长： 忽略短于 ft_min_word_len (默认 4) 个字符的词。
• 噪音词： 忽略出现在超过 50% 的文档中的词。这个阈值可以通过配置 ft_max_word_len 调整。

布尔模式搜索

布尔模式搜索提供了更精细的控制，允许我们使用布尔运算符来定义搜索条件。 使用 IN BOOLEAN MODE 关键字启用布尔模式：

SELECT id, title, content
FROM articles
WHERE MATCH (title, content) AGAINST ('+MySQL -tutorial' IN BOOLEAN MODE);

这个查询会返回包含 "MySQL" 但不包含 "tutorial" 的文章。

布尔模式的运算符：

运算符	描述
+	必须包含该词。
-	必须不包含该词。
>	提高该词的相关性评分。
<	降低该词的相关性评分。
~	取消该词的相关性。 相当于 -，但影响评分的方式不同。
*	通配符，匹配以该词开头的词。
"	短语搜索。 只有包含完全匹配的短语的文档才会被返回。
()	分组。 允许我们组合多个运算符。

布尔模式的例子：

• "+MySQL +database": 必须包含 "MySQL" 和 "database"。
• "+MySQL -database": 必须包含 "MySQL"，但不能包含 "database"。
• "MySQL database": 必须包含短语 "MySQL database"。
• *`"MySQL dat"`:** 必须包含以 "dat" 开头的词，如 "database", "data", "datatype"。
• "+MySQL +(database tutorial)": 必须包含 "MySQL"，并且包含 "database" 或 "tutorial"。
• "+MySQL >database": 必须包含 "MySQL"，并且 "database" 的相关性评分更高。
• "MySQL <database": 必须包含 "MySQL"，并且 "database" 的相关性评分更低。

一个更复杂的例子：

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL +(>database <performance) -InnoDB' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('+MySQL +(>database <performance) -InnoDB' IN BOOLEAN MODE)
ORDER BY relevance DESC;

这个查询会返回：

• 必须包含 "MySQL"。
• 必须包含 "database" 或 "performance"。
• "database" 的相关性评分更高。
• "performance" 的相关性评分更低。
• 不能包含 "InnoDB"。

布尔模式的特点：

• 精确控制： 使用布尔运算符可以精确地定义搜索条件。
• 不自动排序： 默认情况下，结果不按相关性排序。 需要手动使用 ORDER BY 和 MATCH...AGAINST 计算相关性评分。
• 停用词： 布尔模式默认不忽略停用词。 可以通过手动过滤来处理停用词。
• 最小词长： 默认忽略短于 ft_min_word_len 的词，与自然语言模式相同。
• 噪音词： 布尔模式默认不忽略噪音词。

FULLTEXT 索引的配置

MySQL 提供了几个配置选项来调整 FULLTEXT 索引的行为。

• ft_min_word_len: 指定索引的最小词长。 默认值为 4。
• ft_max_word_len: 指定索引的最大词长。
• ft_stopword_file: 指定停用词文件的路径。 默认情况下，MySQL 使用内置的停用词列表。

要修改这些选项，可以在 MySQL 的配置文件 (例如 my.cnf 或 my.ini) 中设置它们，然后重启 MySQL 服务。

[mysqld]
ft_min_word_len = 3
ft_stopword_file = /path/to/my_stopwords.txt

修改后，需要重建 FULLTEXT 索引才能使更改生效：

ALTER TABLE articles DROP INDEX index_article;
ALTER TABLE articles ADD FULLTEXT index_article (title, content);

自定义停用词列表：

可以创建一个包含自定义停用词的文本文件，每行一个词。 例如，创建一个名为 my_stopwords.txt 的文件，内容如下：

the
a
is
are
was
were
...
custom_stopword1
custom_stopword2

然后在配置文件中指定 ft_stopword_file 指向这个文件，并重建索引。

选择合适的搜索模式

如何选择自然语言模式和布尔模式？ 这取决于具体的需求。

特性	自然语言模式	布尔模式
目的	查找与搜索词最相关的结果。	精确控制搜索条件，使用布尔运算符。
排序	自动按相关性排序。	默认不排序，需要手动计算相关性评分并排序。
停用词	默认忽略停用词。	默认不忽略停用词。
复杂性	简单易用。	更复杂，需要了解布尔运算符。
适用场景	用户不熟悉搜索语法，需要智能推荐相关结果。	需要精确匹配特定条件的场景，例如高级搜索、过滤等。
性能	在大量数据下，性能可能不如布尔模式，因为需要计算相关性评分。	在精确匹配条件下，性能可能更好，特别是当使用索引覆盖查询时（select id）。

总结：

• 如果需要根据相关性排序的结果，并且用户不太熟悉搜索语法，则使用自然语言模式。
• 如果需要精确控制搜索条件，使用布尔运算符，例如必须包含某些词，排除某些词，或者进行短语搜索，则使用布尔模式。

FULLTEXT 索引的限制和注意事项

• 存储引擎： FULLTEXT 索引仅适用于 MyISAM 和 InnoDB 存储引擎。 在 MySQL 5.6 之前，仅 MyISAM 支持 FULLTEXT 索引。 InnoDB 从 MySQL 5.6 开始支持 FULLTEXT 索引。
• 最小词长： FULLTEXT 索引会忽略短于 ft_min_word_len 个字符的词。
• 噪音词： FULLTEXT 索引会忽略出现在超过 50% 的文档中的词 (噪音词)。
• 性能： 在大型数据集上，FULLTEXT 索引的创建和维护可能会消耗大量的资源。
• 更新： 对包含 FULLTEXT 索引的表进行大量更新操作 (例如 INSERT, UPDATE, DELETE) 可能会影响性能。 可以考虑先禁用索引，进行更新操作，然后再重新启用索引。
• 索引大小： FULLTEXT 索引可能会占用大量的磁盘空间，特别是对于包含大量文本的表。
• 中文支持： FULLTEXT 索引默认不支持中文等分词语言。需要安装和配置相应的分词插件，例如 ngram。

FULLTEXT 索引与中文分词

MySQL 内置的 FULLTEXT 索引对中文支持有限，因为它基于空格分隔单词。 中文文本没有空格，需要进行分词处理。

ngram 分词器：

MySQL 5.7.6 引入了 ngram 分词器，可以用于支持中文等分词语言的 FULLTEXT 索引。 ngram 分词器将文本分割成 N 个字符的片段。

使用 ngram 分词器：

1. 安装 ngram 分词器 (如果尚未安装): 通常情况下，ngram 分词器已经包含在 MySQL 的安装包中，无需额外安装。

2. 创建 FULLTEXT 索引时指定 ngram 分词器:

   CREATE TABLE chinese_articles (
       id INT PRIMARY KEY AUTO_INCREMENT,
       title VARCHAR(255),
       content TEXT,
       FULLTEXT INDEX index_chinese_article (title, content) WITH PARSER ngram
   );

3. 配置 ngram 分词器的参数:

   ngram 分词器有几个参数可以配置，例如 ngram_token_size，用于指定 N 的大小。 默认值为 2。 可以在 MySQL 的配置文件中设置这些参数：

   [mysqld]
   ngram_token_size=2

   然后重启 MySQL 服务并重建索引。

4. 进行搜索:

   SELECT id, title, content
   FROM chinese_articles
   WHERE MATCH (title, content) AGAINST ('中文搜索' IN NATURAL LANGUAGE MODE);

注意事项：

• ngram 分词器并不完美，可能会产生一些不准确的分词结果。
• ngram_token_size 的值会影响搜索结果。 较小的值可能会产生更多的匹配，但精度较低。 较大的值精度较高，但可能会错过一些匹配。 需要根据实际情况进行调整。
• 除了 ngram，还有其他的中文分词插件可供选择，例如 jieba。 这些插件通常提供更准确的分词结果，但需要额外安装和配置。

性能优化建议

• 只索引需要的列： 不要在不需要搜索的列上创建 FULLTEXT 索引。
• 批量插入数据： 如果需要插入大量数据，先禁用 FULLTEXT 索引，插入完成后再启用索引。
• 定期优化表： 使用 OPTIMIZE TABLE 命令可以优化表，提高 FULLTEXT 索引的性能。
• 调整 ft_min_word_len 和 ft_max_word_len： 根据实际情况调整这两个参数，可以提高搜索精度和性能。
• 使用缓存： 使用 MySQL 的查询缓存或外部缓存系统 (例如 Redis) 可以缓存搜索结果，提高响应速度。
• 分区表： 对于大型表，可以考虑使用分区表来提高性能。
• 索引覆盖查询： 在某些情况下，可以使用索引覆盖查询来避免读取数据行，从而提高性能。 例如，如果只需要返回 id 列，可以这样查询： SELECT id FROM articles WHERE MATCH (title, content) AGAINST ('keyword' IN BOOLEAN MODE); 并且索引包含id, title, content 列。

代码示例：完整的演示

以下是一个完整的演示，展示了如何使用 FULLTEXT 索引进行自然语言和布尔模式搜索。

-- 创建表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX index_article (title, content)
);

-- 插入数据
INSERT INTO articles (title, content) VALUES
('MySQL Tutorial for Beginners', 'This tutorial will teach you the basics of MySQL database.'),
('Advanced MySQL Techniques', 'Learn advanced techniques for optimizing MySQL performance.'),
('Database Design Principles', 'Understand the principles of designing efficient and scalable databases.'),
('Introduction to SQL', 'Learn the basics of SQL, the language used to communicate with databases.'),
('NoSQL Databases Explained', 'Explore the world of NoSQL databases and their advantages.');

-- 自然语言搜索
SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL tutorial' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL tutorial' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- 布尔模式搜索
SELECT id, title, content
FROM articles
WHERE MATCH (title, content) AGAINST ('+MySQL -performance' IN BOOLEAN MODE);

-- 布尔模式搜索，使用短语
SELECT id, title, content
FROM articles
WHERE MATCH (title, content) AGAINST ('"database design"' IN BOOLEAN MODE);

-- 布尔模式搜索，使用通配符
SELECT id, title, content
FROM articles
WHERE MATCH (title, content) AGAINST ('dat*' IN BOOLEAN MODE);

-- 修改 ft_min_word_len (需要修改 my.cnf 并重启 MySQL 服务)
-- 假设修改为 3
-- 重建索引
ALTER TABLE articles DROP INDEX index_article;
ALTER TABLE articles ADD FULLTEXT index_article (title, content);

-- 演示中文分词 (需要 MySQL 5.7.6+ 和 ngram 分词器)
CREATE TABLE chinese_articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX index_chinese_article (title, content) WITH PARSER ngram
);

INSERT INTO chinese_articles (title, content) VALUES
('MySQL中文教程', '本教程将介绍MySQL数据库的基础知识。'),
('高级MySQL技术', '学习优化MySQL性能的高级技术。'),
('数据库设计原则', '理解设计高效且可扩展的数据库的原则。'),
('SQL简介', '学习SQL的基础知识，SQL是用于与数据库通信的语言。'),
('NoSQL数据库详解', '探索NoSQL数据库的世界及其优势。');

SELECT id, title, content
FROM chinese_articles
WHERE MATCH (title, content) AGAINST ('MySQL 中文' IN NATURAL LANGUAGE MODE);

总结概括全文

FULLTEXT 索引是 MySQL 中进行高效文本搜索的关键工具。通过理解自然语言和布尔模式搜索的差异和特性，我们可以根据实际需求选择合适的搜索方式，并利用配置选项和优化技巧来提高搜索性能。 掌握 FULLTEXT 索引的使用，可以显著提升应用程序的搜索功能和用户体验。