MySQL的全文索引（Full-text search）：从MyISAM到InnoDB的实现差异，以及如何利用`MATCH AGAINST`的高级搜索模式？

MySQL 全文索引：MyISAM 与 InnoDB 的差异及 MATCH AGAINST 高级搜索模式

各位，今天我们来深入探讨 MySQL 的全文索引，重点关注MyISAM和InnoDB引擎在全文索引实现上的差异，以及如何利用MATCH AGAINST语句进行更高级、更灵活的全文搜索。全文索引是提升文本数据搜索效率的关键技术，掌握它对于开发搜索功能、内容分析等应用至关重要。

1. 全文索引的必要性

在没有全文索引的情况下，我们通常使用LIKE操作符进行文本搜索。然而，LIKE操作符的效率非常低，特别是当数据量很大时，它需要扫描整个表，并且不能利用任何索引。

例如：

SELECT * FROM articles WHERE content LIKE '%keyword%';

这种查询方式对于大型数据集来说是不可接受的。 全文索引的出现就是为了解决这个问题，它通过对文本数据进行预处理，建立倒排索引，从而实现快速的文本搜索。

2. MyISAM 和 InnoDB 的全文索引差异

在 MySQL 5.6 之前，全文索引只能在 MyISAM 引擎上使用。从 MySQL 5.6 开始，InnoDB 也开始支持全文索引，但两者在实现和功能上存在一些差异。

特性	MyISAM	InnoDB
支持版本	MySQL 5.5 及更早版本	MySQL 5.6 及更高版本
索引类型	仅支持 FULLTEXT 索引	支持 FULLTEXT 索引
分词器	内置分词器 (不适合所有语言)	内置分词器 (可配置，支持插件式分词器)
停止词	停止词列表文件 (可配置)	停止词表 (可配置)
最小索引长度	默认 4 个字符 (可配置)	默认 3 个字符 (可配置)
锁定粒度	表级锁定	行级锁定
事务支持	不支持	支持
数据恢复	崩溃后数据恢复较慢	崩溃后数据恢复较快
并发性能	并发性能较差	并发性能较好
索引存储位置	单独的文件存储	与数据存储在一起
支持的字符集	有限的字符集支持	支持多种字符集

2.1 分词器

分词器是将文本分解成单个词（token）的组件。 MyISAM 使用内置的分词器，它主要基于空格和标点符号进行分词，对于英文文本效果尚可，但对于中文等语言，分词效果较差，因为中文词语之间没有明显的空格分隔。

InnoDB 同样使用内置的分词器，但它允许配置自定义分词器，甚至可以通过插件的方式使用第三方分词器，例如 MeCab (适用于日文) 或 ICU (International Components for Unicode，支持多种语言)。 这使得 InnoDB 在处理多语言文本时更加灵活。

2.2 停止词

停止词是指在文本搜索中被忽略的常用词，例如 "the"、"a"、"is" 等。 这些词出现频率很高，但对于搜索结果的区分度贡献很小。

MyISAM 和 InnoDB 都支持停止词，并且允许配置停止词列表。 MyISAM 使用一个停止词文件来存储停止词，而 InnoDB 使用一个系统表来存储停止词。

2.3 最小索引长度

最小索引长度是指被索引的词的最小长度。 如果一个词的长度小于最小索引长度，则不会被索引。

MyISAM 默认的最小索引长度为 4 个字符，而 InnoDB 默认为 3 个字符。 可以通过配置参数 ft_min_word_len 来修改最小索引长度。

2.4 锁定机制和事务支持

MyISAM 使用表级锁定，这意味着在执行全文索引操作时，整个表会被锁定，影响并发性能。

InnoDB 使用行级锁定，这意味着只有被修改的行会被锁定，并发性能更好。 此外，InnoDB 支持事务，可以保证全文索引操作的原子性、一致性、隔离性和持久性 (ACID)。

3. 创建全文索引

可以使用 CREATE TABLE 语句或 ALTER TABLE 语句来创建全文索引。

3.1 使用 CREATE TABLE 语句

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX title_content_idx (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 使用 ALTER TABLE 语句

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

注意:

• 必须指定 ENGINE=InnoDB 或 ENGINE=MyISAM 来选择存储引擎。
• FULLTEXT INDEX 可以包含一个或多个列。
• 建议使用 utf8mb4 字符集，以支持更多的字符。

4. MATCH AGAINST 语句

MATCH AGAINST 语句用于执行全文搜索。 它的基本语法如下：

SELECT * FROM table_name
WHERE MATCH (column1, column2, ...) AGAINST ('search_term' [search_modifier]);

• MATCH (column1, column2, ...) 指定要搜索的列。
• AGAINST ('search_term' [search_modifier]) 指定搜索词和搜索模式。
• search_modifier 是可选的，用于指定搜索模式，例如 IN NATURAL LANGUAGE MODE、IN BOOLEAN MODE 和 WITH QUERY EXPANSION。

4.1 IN NATURAL LANGUAGE MODE

这是默认的搜索模式。 它将搜索词解释为自然语言文本，并根据词的出现频率和在文档中的重要性来计算相关性。 搜索结果按照相关性降序排列。

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL全文索引') AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL全文索引')
ORDER BY relevance DESC;

4.2 IN BOOLEAN MODE

IN BOOLEAN MODE 允许使用布尔运算符来组合搜索词。 常用的布尔运算符包括：

• +：必须包含该词。
• -：必须不包含该词。
• >：提高该词的相关性。
• <：降低该词的相关性。
• *：词干搜索。
• "：精确匹配。

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

这个查询会搜索包含 "MySQL" 但不包含 "InnoDB" 的文章。

SELECT id, title, content, MATCH (title, content) AGAINST ('"全文索引"' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('"全文索引"' IN BOOLEAN MODE)
ORDER BY relevance DESC;

这个查询会精确匹配包含 "全文索引" 的文章。

4.3 WITH QUERY EXPANSION

WITH QUERY EXPANSION 会自动扩展搜索词，以包含与原始搜索词相关的词。 它会执行两次搜索：第一次使用原始搜索词，第二次使用原始搜索词和第一次搜索结果中最相关的词。

SELECT id, title, content, MATCH (title, content) AGAINST ('全文索引' WITH QUERY EXPANSION) AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('全文索引' WITH QUERY EXPANSION)
ORDER BY relevance DESC;

WITH QUERY EXPANSION 可以提高搜索结果的召回率，但可能会降低搜索结果的精度。

5. 配置全文索引

可以通过修改 MySQL 的配置文件 (my.cnf 或 my.ini) 来配置全文索引。 一些常用的配置参数包括：

• ft_min_word_len：最小索引长度。
• ft_max_word_len：最大索引长度。
• innodb_ft_enable_stopword：是否启用停止词。
• innodb_ft_server_stopword_table：停止词表。
• innodb_ft_user_stopword_table：用户自定义停止词表。
• ngram_token_size：ngram 分词器的大小 (适用于中文等语言)。

修改配置文件后，需要重启 MySQL 服务器才能生效。

6. 全文索引的优化

• 选择合适的存储引擎： 如果需要事务支持和高并发性能，建议使用 InnoDB。
• 选择合适的字符集： 建议使用 utf8mb4 字符集，以支持更多的字符。
• 调整最小索引长度： 根据实际情况调整 ft_min_word_len 参数，以平衡索引大小和搜索精度。
• 配置停止词： 配置停止词列表，以提高搜索效率。
• 使用 EXPLAIN 分析查询： 使用 EXPLAIN 语句分析查询，以确定是否使用了全文索引。
• 定期优化表： 使用 OPTIMIZE TABLE 语句优化表，以提高全文索引的性能。

7. 实例演示

假设我们有一个名为 products 的表，包含以下字段：

• id：产品 ID (INT, PRIMARY KEY)
• name：产品名称 (VARCHAR(255))
• description：产品描述 (TEXT)

我们希望创建一个全文索引来搜索产品名称和描述。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    description TEXT,
    FULLTEXT INDEX name_description_idx (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO products (name, description) VALUES
('Apple iPhone 13', 'The latest iPhone with A15 Bionic chip.'),
('Samsung Galaxy S21', 'A powerful Android phone with Snapdragon 888 processor.'),
('Google Pixel 6', 'A smart phone with Google Tensor chip and advanced camera features.'),
('Apple iPad Pro', 'A high-performance tablet with M1 chip.'),
('Samsung Galaxy Tab S8', 'A versatile Android tablet with Snapdragon 8 Gen 1 processor.');

SELECT id, name, description, MATCH (name, description) AGAINST ('phone') AS relevance
FROM products
WHERE MATCH (name, description) AGAINST ('phone')
ORDER BY relevance DESC;

SELECT id, name, description, MATCH (name, description) AGAINST ('+Apple -tablet' IN BOOLEAN MODE) AS relevance
FROM products
WHERE MATCH (name, description) AGAINST ('+Apple -tablet' IN BOOLEAN MODE)
ORDER BY relevance DESC;

SELECT id, name, description, MATCH (name, description) AGAINST ('smart phone' WITH QUERY EXPANSION) AS relevance
FROM products
WHERE MATCH (name, description) AGAINST ('smart phone' WITH QUERY EXPANSION)
ORDER BY relevance DESC;

这个例子演示了如何创建全文索引，以及如何使用 MATCH AGAINST 语句进行不同模式的全文搜索。

8. InnoDB 全文索引的限制和注意事项

• InnoDB 全文索引不支持前缀索引。
• InnoDB 全文索引的性能可能不如 MyISAM，特别是在大数据集上。
• InnoDB 全文索引的维护成本较高，例如，插入、更新和删除操作会导致索引的重建。
• 在创建或修改全文索引后，可能需要一段时间才能生效。

9. 选择合适的全文索引方案

选择合适的全文索引方案取决于具体的应用场景和需求。 如果需要事务支持、高并发性能和可靠的数据恢复，建议使用 InnoDB。 如果对性能要求较高，并且可以接受表级锁定和不支持事务，可以考虑使用 MyISAM (但不推荐)。

10. 总结

今天我们深入探讨了 MySQL 全文索引，重点关注了 MyISAM 和 InnoDB 引擎在实现上的差异，以及如何利用 MATCH AGAINST 语句进行更高级、更灵活的全文搜索。 掌握全文索引对于提升文本数据搜索效率至关重要，希望今天的分享能帮助大家更好地理解和应用这项技术。

MyISAM 和 InnoDB 各有优劣，根据实际需求选择
MyISAM 在旧版本中是唯一选择，但事务支持和并发能力弱。InnoDB 提供了更好的事务和并发支持，但在某些特定场景下性能可能略逊。

MATCH AGAINST 提供了多种搜索模式，灵活满足搜索需求
IN NATURAL LANGUAGE MODE, IN BOOLEAN MODE, 和 WITH QUERY EXPANSION 提供了不同级别的搜索精度和召回率，可以根据实际应用场景进行选择。

理解配置选项和优化技巧，提升全文索引效率
通过调整最小索引长度、配置停止词、使用 EXPLAIN 分析查询等手段，可以有效地提升全文索引的性能。