MySQL的全文索引（Full-text search）：从MyiSAM到InnoDB的实现差异与高级搜索

MySQL 全文索引：从 MyISAM 到 InnoDB 的实现差异与高级搜索

大家好，今天我们来深入探讨 MySQL 的全文索引技术。全文索引允许我们在文本数据中执行高效的搜索，而无需使用 LIKE 运算符和复杂的正则表达式。我们将重点关注 MyISAM 和 InnoDB 这两个存储引擎在全文索引方面的差异，并探索一些高级搜索技术。

1. 全文索引的基本概念

全文索引是一种特殊类型的索引，用于加速对文本数据的搜索。与传统的 B 树索引不同，全文索引会分析文本内容，将文本分解为单词（或词组），并构建一个倒排索引，将每个单词映射到包含该单词的文档。

2. MyISAM 存储引擎的全文索引

在 MySQL 5.6 之前，MyISAM 是唯一支持全文索引的存储引擎。MyISAM 的全文索引实现相对简单，但有一些限制。

2.1 MyISAM 全文索引的创建

可以使用 CREATE FULLTEXT INDEX 语句在 MyISAM 表上创建全文索引。

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

在这个例子中，我们在 articles 表的 title 和 content 列上创建了一个全文索引。可以指定单个列，也可以指定多个列，MySQL 会将这些列的内容合并到一个索引中。

2.2 MyISAM 全文索引的查询

使用 MATCH ... AGAINST 语法进行全文搜索。

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

• MATCH (title, content) 指定要搜索的列。
• AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) 指定搜索的关键词和搜索模式。

MyISAM 支持以下几种搜索模式：

• NATURAL LANGUAGE MODE (默认): MySQL 将查询字符串解释为自然语言短语。它会查找包含查询字符串中所有单词的文档，并根据相关性对结果进行排序。相关性基于单词出现的频率和文档长度。停用词（如 "the", "a", "is"）会被忽略。

• BOOLEAN MODE: 允许使用布尔运算符（如 +, -, *, >）来更精确地控制搜索。

• WITH QUERY EXPANSION: MySQL 会进行查询扩展，通过查找与查询字符串相关的其他单词来扩大搜索范围。

2.3 MyISAM 全文索引的限制

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

• 不支持事务： MyISAM 不支持事务，这意味着全文索引操作不能回滚。

• 停用词列表： MyISAM 使用一个全局的停用词列表，无法针对单个表进行定制。

• 最小索引长度： 默认情况下，MyISAM 要求单词的最小长度为 4 个字符。

3. InnoDB 存储引擎的全文索引

从 MySQL 5.6 开始，InnoDB 也支持全文索引。InnoDB 的全文索引实现更加复杂和强大，克服了 MyISAM 的一些限制。

3.1 InnoDB 全文索引的创建

与 MyISAM 类似，可以使用 CREATE FULLTEXT INDEX 语句创建 InnoDB 全文索引。

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

InnoDB 的全文索引创建过程会创建一个辅助表（FTS index table），用于存储索引数据。

3.2 InnoDB 全文索引的查询

与 MyISAM 相同，使用 MATCH ... AGAINST 语法进行全文搜索。

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

InnoDB 支持与 MyISAM 相同的搜索模式 (NATURAL LANGUAGE MODE, BOOLEAN MODE, WITH QUERY EXPANSION)。

3.3 InnoDB 全文索引的优势

• 行级锁定： InnoDB 使用行级锁定，这意味着多个全文索引操作可以并发执行，提高了并发性能。

• 支持事务： InnoDB 支持事务，这意味着全文索引操作可以回滚，保证数据的一致性。

• 可定制的停用词列表： InnoDB 允许针对单个表定制停用词列表。

• 更灵活的配置： InnoDB 提供了更多的配置选项，可以更精细地控制全文索引的行为。

3.4 InnoDB 全文索引的表和参数

InnoDB 全文索引使用多个内部表来存储索引数据和元数据。这些表都存储在特定的数据库目录下，并且不能直接访问。

一些重要的参数包括：

• innodb_ft_min_token_size: 控制单词的最小长度。默认值为 3。
• innodb_ft_max_token_size: 控制单词的最大长度。默认值为 84。
• innodb_ft_enable_stopword: 启用或禁用停用词列表。
• innodb_ft_server_stopword_table: 指定服务器级别的停用词表。
• innodb_ft_user_stopword_table: 指定用户自定义的停用词表。

3.5 InnoDB 全文索引的停用词

InnoDB 默认使用一个内置的停用词列表。可以通过修改 innodb_ft_server_stopword_table 和 innodb_ft_user_stopword_table 参数来定制停用词列表。

首先，创建一个包含停用词的表：

CREATE TABLE my_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO my_stopwords (value) VALUES ('the'), ('a'), ('is'), ('are');

然后，设置 innodb_ft_user_stopword_table 参数：

SET GLOBAL innodb_ft_user_stopword_table = 'your_database_name/my_stopwords';

需要重启 MySQL 服务器才能使更改生效。

4. MyISAM vs InnoDB 全文索引的对比

特性	MyISAM	InnoDB
锁定级别	表级锁定	行级锁定
事务支持	不支持	支持
停用词列表	全局停用词列表	可定制的停用词列表
数据一致性	较低	较高
并发性能	较低	较高
可配置性	较低	较高

5. 高级全文搜索技术

5.1 BOOLEAN MODE

BOOLEAN MODE 允许使用布尔运算符来精确控制搜索。

• +: 表示单词必须存在。
• -: 表示单词必须不存在。
• >: 增加单词的相关性。
• <: 降低单词的相关性。
• *: 通配符，匹配以指定单词开头的单词。
• "": 将多个单词作为一个短语进行搜索。

SELECT id, title
FROM articles
WHERE MATCH (title, content) AGAINST ('+MySQL -InnoDB' IN BOOLEAN MODE);  -- 必须包含 MySQL，不能包含 InnoDB

SELECT id, title
FROM articles
WHERE MATCH (title, content) AGAINST ('"Full-text search"' IN BOOLEAN MODE); -- 搜索短语 "Full-text search"

SELECT id, title
FROM articles
WHERE MATCH (title, content) AGAINST ('>MySQL <InnoDB' IN BOOLEAN MODE); -- MySQL 的相关性更高，InnoDB 的相关性更低

SELECT id, title
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQ*' IN BOOLEAN MODE); -- 搜索以 MySQ 开头的单词

5.2 WITH QUERY EXPANSION

WITH QUERY EXPANSION 允许 MySQL 扩展查询，通过查找与查询字符串相关的其他单词来扩大搜索范围。

SELECT id, title
FROM articles
WHERE MATCH (title, content) AGAINST ('database' WITH QUERY EXPANSION);

这种模式适用于用户不太确定要搜索什么，或者希望发现与查询字符串相关的其他信息的情况。但是，它也可能返回一些不相关的结果，因此需要谨慎使用。通常，WITH QUERY EXPANSION 会进行两次搜索。第一次搜索使用原始查询字符串，然后 MySQL 会查找与第一次搜索结果相关的其他单词，并使用这些单词进行第二次搜索。

5.3 查询优化

• 使用正确的搜索模式： 根据实际需求选择合适的搜索模式。NATURAL LANGUAGE MODE 适用于一般的搜索，BOOLEAN MODE 适用于需要精确控制搜索的情况，WITH QUERY EXPANSION 适用于需要扩大搜索范围的情况。

• 避免使用通配符： 通配符搜索可能会降低性能，应尽量避免使用。

• 优化停用词列表： 根据实际需求定制停用词列表，可以提高搜索的准确性。

• 定期维护索引： 定期使用 OPTIMIZE TABLE 语句来优化全文索引。

6. 实际案例

假设我们有一个电商网站，需要实现商品搜索功能。我们可以使用全文索引来加速搜索。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    description TEXT,
    price DECIMAL(10, 2),
    FULLTEXT (name, description)
) ENGINE=InnoDB;

-- 插入一些示例数据
INSERT INTO products (name, description, price) VALUES
('Laptop', 'High-performance laptop with 16GB RAM and 512GB SSD', 1200.00),
('Mouse', 'Wireless mouse with ergonomic design', 25.00),
('Keyboard', 'Mechanical keyboard with RGB lighting', 100.00),
('Gaming PC', 'Powerful gaming PC with RTX 3080 graphics card', 2500.00);

-- 搜索包含 "gaming" 的商品
SELECT id, name, description
FROM products
WHERE MATCH (name, description) AGAINST ('gaming' IN NATURAL LANGUAGE MODE);

-- 搜索包含 "laptop" 且不包含 "wireless" 的商品
SELECT id, name, description
FROM products
WHERE MATCH (name, description) AGAINST ('+laptop -wireless' IN BOOLEAN MODE);

-- 搜索与 "computer" 相关的商品
SELECT id, name, description
FROM products
WHERE MATCH (name, description) AGAINST ('computer' WITH QUERY EXPANSION);

7. 注意事项

• 全文索引会占用额外的存储空间。
• 全文索引的创建和维护需要一定的计算资源。
• 全文索引只适用于文本数据。
• 对于包含大量文本数据的表，建议使用全文索引来提高搜索性能。

8. 总结要点

MyISAM 和 InnoDB 在全文索引实现上存在显著差异，InnoDB 在并发、事务和可定制性方面具有优势。 理解不同搜索模式的用途，并结合实际需求进行选择是关键。 通过优化查询、定制停用词列表以及定期维护索引，可以进一步提升全文搜索的性能和准确性。