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

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

大家好，今天我们来深入探讨 MySQL 中的全文索引技术。全文索引允许我们在文本数据中进行高效的搜索，而无需像 LIKE 操作符那样进行全表扫描。我们将重点比较 MyISAM 和 InnoDB 存储引擎在全文索引方面的实现差异，并介绍一些高级的搜索模式。

1. 全文索引的基本概念

全文索引是一种特殊的索引类型，它为文本字段建立了索引，允许 MySQL 在文本数据中执行基于词语的搜索。与传统的 B-Tree 索引不同，全文索引能够识别单词之间的分隔符（例如空格、标点符号），并将文本分解成独立的词语。

应用场景：

• 博客文章搜索
• 新闻文章搜索
• 产品描述搜索
• 论坛帖子搜索
• 文档内容搜索

2. MyISAM 引擎的全文索引

在 MySQL 5.6 之前，MyISAM 是唯一支持全文索引的存储引擎。

2.1 创建全文索引

在 MyISAM 表中创建全文索引的语法如下：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body) -- 创建全文索引
) ENGINE=MyISAM;

或者，可以先创建表，然后添加全文索引：

ALTER TABLE articles ADD FULLTEXT INDEX article_fulltext (title, body);

2.2 全文搜索语法

MyISAM 引擎使用 MATCH() ... AGAINST() 语法进行全文搜索。

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

• MATCH (title, body)：指定要搜索的字段。
• AGAINST ('MySQL' IN NATURAL LANGUAGE MODE)：指定搜索词语和搜索模式。

2.3 搜索模式

MyISAM 提供了几种搜索模式：

• NATURAL LANGUAGE MODE (默认模式)：按照自然语言的习惯进行搜索。MySQL 会自动去除停用词（例如 "the", "a", "is" 等），并根据词语的出现频率进行排序。
• BOOLEAN MODE：允许使用布尔运算符（例如 +, -, *, > 等）来组合搜索词语。
• WITH QUERY EXPANSION：MySQL 会根据搜索词语自动扩展搜索范围，找到与搜索词语相关的其他词语。

2.4 示例

-- NATURAL LANGUAGE MODE
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('MySQL database' IN NATURAL LANGUAGE MODE);

-- BOOLEAN MODE (搜索包含 "MySQL" 但不包含 "database" 的文章)
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE);

-- WITH QUERY EXPANSION
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('optimization' WITH QUERY EXPANSION);

2.5 MyISAM 全文索引的限制

• 表锁： MyISAM 使用表锁，并发性能较差。
• 不支持事务： MyISAM 不支持事务，数据一致性难以保证。
• 崩溃恢复： MyISAM 的崩溃恢复机制相对较弱，容易导致数据损坏。
• 停用词列表： MyISAM 的停用词列表是全局的，无法为每个表单独配置。
• 最小索引长度: 默认情况下，MyISAM 的全文索引要求索引的词语长度至少为 4 个字符。可以通过修改 ft_min_word_len 系统变量来调整。

3. InnoDB 引擎的全文索引

从 MySQL 5.6 开始，InnoDB 也支持全文索引，这极大地改善了 MySQL 的全文搜索功能。

3.1 创建全文索引

在 InnoDB 表中创建全文索引的语法与 MyISAM 类似：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX article_fulltext (title, body)
) ENGINE=InnoDB;

或者：

ALTER TABLE articles ADD FULLTEXT INDEX article_fulltext (title, body);

3.2 全文搜索语法

InnoDB 引擎同样使用 MATCH() ... AGAINST() 语法进行全文搜索。

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

3.3 搜索模式

InnoDB 支持与 MyISAM 相同的搜索模式：

• NATURAL LANGUAGE MODE
• BOOLEAN MODE
• WITH QUERY EXPANSION

3.4 示例

-- NATURAL LANGUAGE MODE
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('MySQL performance' IN NATURAL LANGUAGE MODE);

-- BOOLEAN MODE (搜索包含 "MySQL" 并且包含 "performance" 的文章)
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('+MySQL +performance' IN BOOLEAN MODE);

-- WITH QUERY EXPANSION
SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('scalability' WITH QUERY EXPANSION);

3.5 InnoDB 全文索引的优势

• 行锁： InnoDB 使用行锁，并发性能更好。
• 支持事务： InnoDB 支持事务，可以保证数据一致性。
• 崩溃恢复： InnoDB 的崩溃恢复机制更强大，可以更好地保护数据。
• 停用词列表： InnoDB 允许为每个表单独配置停用词列表。
• 最小索引长度: 默认情况下，InnoDB 的全文索引要求索引的词语长度至少为 3 个字符。可以通过修改 innodb_ft_min_token_size 系统变量来调整。
• 插件式分词器： InnoDB 支持插件式的分词器，可以根据需要选择不同的分词算法。

3.6 InnoDB 全文索引的实现原理

InnoDB 的全文索引使用倒排索引（Inverted Index）来实现。倒排索引是一种将词语映射到包含该词语的文档的索引结构。

1. 文档解析： InnoDB 会将文本数据分解成独立的词语，并去除停用词。
2. 创建倒排索引： InnoDB 会为每个词语创建一个索引条目，包含该词语以及包含该词语的文档 ID。
3. 存储索引数据： InnoDB 会将倒排索引数据存储在特殊的辅助表中，这些表以 FTS_ 开头。

3.7 InnoDB 全文索引相关系统变量

系统变量	描述
innodb_ft_min_token_size	最小索引词语长度（默认值：3）
innodb_ft_max_token_size	最大索引词语长度（默认值：84）
innodb_ft_enable_stopword	是否启用停用词列表（默认值：ON）
innodb_ft_server_stopword_table	全局停用词表（默认值：information_schema.innodb_ft_default_stopword）
innodb_ft_user_stopword_table	用户自定义停用词表
innodb_ft_cache_size	全文索引缓存大小（默认值：32MB）
innodb_ft_total_cache_size	所有全文索引缓存的总大小
innodb_ft_result_cache_limit	全文搜索结果缓存大小（默认值：2GB）

3.8 自定义停用词列表

可以创建自定义的停用词表，并将其配置为 InnoDB 的停用词列表。

-- 创建停用词表
CREATE TABLE my_stopwords (
    value VARCHAR(30) NOT NULL,
    PRIMARY KEY (value)
) ENGINE=InnoDB;

-- 插入停用词
INSERT INTO my_stopwords (value) VALUES ('the'), ('a'), ('is');

-- 配置停用词表
SET GLOBAL innodb_ft_user_stopword_table = 'your_database.my_stopwords';

-- 重建全文索引 (必须重建索引才能使新的停用词列表生效)
ALTER TABLE articles DROP INDEX article_fulltext;
ALTER TABLE articles ADD FULLTEXT INDEX article_fulltext (title, body);

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

特性	MyISAM	InnoDB
锁机制	表锁	行锁
事务支持	不支持	支持
崩溃恢复	较弱	强大
停用词列表	全局	表级别
最小索引长度	4 (可配置)	3 (可配置)
分词器	内置	插件式
并发性能	较差	更好
数据一致性	难以保证	更好

结论：

InnoDB 在事务支持、并发性能和数据一致性方面优于 MyISAM，因此通常建议在需要全文索引的场景中使用 InnoDB 存储引擎。

5. 高级全文搜索模式

除了基本的 MATCH() ... AGAINST() 语法，MySQL 还提供了一些高级的全文搜索模式，可以满足更复杂的需求。

5.1 权重（Weight）

可以为不同的字段设置权重，以便在搜索结果中优先显示包含重要词语的文档。

SELECT *, MATCH (title) AGAINST ('MySQL') * 2 + MATCH (body) AGAINST ('MySQL') AS relevance
FROM articles
WHERE MATCH (title, body) AGAINST ('MySQL')
ORDER BY relevance DESC;

在这个例子中，title 字段的权重是 body 字段的两倍。

5.2 相似度（Proximity Search）

MySQL 不直接支持相似度搜索，但可以通过一些技巧来实现类似的功能。

一种方法是使用 BOOLEAN MODE 和通配符。

SELECT * FROM articles
WHERE MATCH (body) AGAINST ('"MySQL database"*' IN BOOLEAN MODE);

这种方法可以找到包含 "MySQL" 和 "database" 两个词语，并且这两个词语在一定距离内的文档。

5.3 前缀搜索（Prefix Search）

可以使用 BOOLEAN MODE 和 * 通配符进行前缀搜索。

SELECT * FROM articles
WHERE MATCH (title) AGAINST ('MySQ*' IN BOOLEAN MODE);

这种方法可以找到 title 字段以 "MySQ" 开头的文档。

5.4 使用 UDF (User Defined Function) 扩展全文搜索功能

可以编写自定义的 UDF 函数来扩展 MySQL 的全文搜索功能。例如，可以编写一个 UDF 函数来实现更复杂的相似度计算、拼写检查或词干提取。

6. 全文索引的优化

• 选择合适的存储引擎： 优先选择 InnoDB 存储引擎。
• 调整系统变量： 根据实际需求调整 innodb_ft_min_token_size、innodb_ft_max_token_size 等系统变量。
• 优化停用词列表： 根据业务需求，创建和维护自定义的停用词列表。
• 定期重建索引： 在数据量发生较大变化时，建议定期重建全文索引。
• 使用 EXPLAIN 分析查询： 使用 EXPLAIN 命令分析全文搜索查询，确保 MySQL 能够有效地使用全文索引。
• 避免在 WHERE 子句中使用 OR： OR 可能会导致 MySQL 无法使用全文索引。
• 考虑使用专门的搜索引擎： 对于复杂的搜索需求，可以考虑使用专门的搜索引擎，例如 Elasticsearch 或 Solr。

7. 代码示例：创建一个简单的博客系统

以下是一个简单的博客系统的示例，演示了如何在 MySQL 中使用全文索引。

-- 创建文章表
CREATE TABLE blog_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX post_fulltext (title, content)
) ENGINE=InnoDB;

-- 插入一些示例数据
INSERT INTO blog_posts (title, content, author) VALUES
('MySQL Full-Text Search Tutorial', 'This tutorial explains how to use MySQL full-text search...', 'John Doe'),
('InnoDB vs MyISAM Full-Text Indexing', 'A comparison of InnoDB and MyISAM full-text indexing...', 'Jane Smith'),
('Optimizing MySQL Queries for Performance', 'Tips and tricks for optimizing MySQL queries...', 'Peter Jones');

-- 全文搜索示例
SELECT * FROM blog_posts
WHERE MATCH (title, content) AGAINST ('MySQL full-text' IN NATURAL LANGUAGE MODE);

-- 全文搜索，使用 BOOLEAN MODE
SELECT * FROM blog_posts
WHERE MATCH (title, content) AGAINST ('+MySQL +performance' IN BOOLEAN MODE);

-- 全文搜索，使用 WITH QUERY EXPANSION
SELECT * FROM blog_posts
WHERE MATCH (title, content) AGAINST ('optimization' WITH QUERY EXPANSION);

InnoDB成为首选

InnoDB 存储引擎在全文索引方面提供了更强大的功能和更好的性能，通常是全文索引的首选。

高级搜索技巧

通过权重、相似度搜索和前缀搜索等高级技巧，可以实现更复杂的全文搜索需求。

优化至关重要

优化全文索引对于提高搜索性能至关重要，需要综合考虑存储引擎选择、系统变量调整、停用词列表管理和查询语句优化等因素。