全文检索函数

导学

当数据量增大时，LIKE '%关键词%' 的模糊查询会触发全表扫描，性能急剧下降。MySQL 的全文检索（Full-Text Search）通过建立全文索引，配合 MATCH ... AGAINST 语法，实现对大文本字段的高效关键词搜索——支持自然语言模式、布尔模式，甚至查询扩展。

定义

全文检索：基于全文索引（FULLTEXT INDEX），使用 MATCH(列名) AGAINST('关键词') 语法进行文本搜索的查询方式。仅适用于 CHAR、VARCHAR、TEXT 类型字段。

核心语法

创建全文索引

-- 建表时创建
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_idx (title, content)
) ENGINE=InnoDB;

-- 给已有表添加
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, content);

全文检索查询

-- 自然语言模式（默认）
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('关键词');

-- 布尔模式
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 查询扩展模式
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);

演示数据准备

DROP TABLE IF EXISTS articles;

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

INSERT INTO articles (title, content) VALUES
('MySQL 索引优化指南', '本文介绍 MySQL 中 B+Tree 索引的原理，以及如何通过 EXPLAIN 分析查询性能。'),
('MySQL 事务与锁机制', '深入讲解 InnoDB 的 MVCC 多版本并发控制，以及行锁、间隙锁的实现原理。'),
('Oracle 数据库迁移到 MySQL', '从 Oracle 迁移到 MySQL 的注意事项，包括数据类型映射、SQL 语法差异等。'),
('MySQL 主从复制配置', '手把手教你配置 MySQL 5.7 的异步复制，包括 binlog 格式选择和故障切换。');

当前 articles 表中的完整数据如下：

SQL 示例

场景一：自然语言模式全文检索

当前数据状态：见上文 articles 表完整数据。

执行语句：

SELECT id, title,
       MATCH(title, content) AGAINST('MySQL 优化') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 优化');

操作后结果：

结果解读：

• MATCH(...) AGAINST(...) 在 WHERE 子句中用于过滤匹配行
• 在 SELECT 子句中返回相关性评分（relevance），值越大越相关
• 结果自动按相关性降序排列（MySQL 内部优化）
• 第 3 条 "Oracle 数据库迁移到 MySQL" 也包含 "MySQL"，但相关性较低，未进入前三

注意：自然语言模式有一个"50% 阈值"陷阱——如果某个词出现在超过 50% 的行中，它会被忽略。例如搜索 "MySQL" 单独一个词时，由于 4 行中有 3 行包含 "MySQL"（75% > 50%），可能返回空结果！

场景二：布尔模式（精确控制匹配逻辑）

当前数据状态：见上文 articles 表完整数据。

执行语句：

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

-- 必须包含 MySQL，包含 优化 或 索引 之一
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL (优化 索引)' IN BOOLEAN MODE);

-- 前缀匹配：匹配 "配置"、"配置方法"、"配置文件" 等
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('配置*' IN BOOLEAN MODE);

操作后结果：

+MySQL -Oracle：

+MySQL (优化 索引)：

结果解读：

• + 前缀：词必须出现（AND）
• - 前缀：词必须不出现（NOT）
• 无前缀：词可选出现（OR）
• () 分组：控制优先级
• * 后缀：前缀匹配（如 配置* 匹配 "配置" 开头的词）
• 布尔模式没有 50% 阈值，适合搜索高频词

场景三：短语匹配（精确短语搜索）

当前数据状态：见上文 articles 表完整数据。

执行语句：

-- 精确匹配短语 "MySQL 5.7"
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('"MySQL 5.7"' IN BOOLEAN MODE);

操作后结果：

结果解读："MySQL 5.7" 用双引号包裹，表示精确短语匹配。只有第 4 条包含完整的 "MySQL 5.7"，其他条虽然包含 "MySQL" 和 "5.7" 但不连续，不匹配。

场景四：查询扩展模式（解决短查询召回不足）

当前数据状态：见上文 articles 表完整数据。

执行语句：

-- 普通搜索 "InnoDB"
SELECT id, title FROM articles WHERE MATCH(title, content) AGAINST('InnoDB');

-- 查询扩展：先用 InnoDB 搜一次，再用结果中的高频词扩展搜索
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('InnoDB' WITH QUERY EXPANSION);

操作后结果：

普通搜索：

查询扩展：

结果解读：

• 普通搜索只找到直接包含 "InnoDB" 的第 2 条
• 查询扩展模式先搜 "InnoDB"，发现第 2 条还包含 "MySQL"、"MVCC"、"锁" 等词，然后用这些高频词再次搜索
• 结果召回更多相关文档（第 1 条讲 MySQL 索引，第 4 条讲 MySQL 复制），即使它们不直接包含 "InnoDB"
• 适合用户输入很短（如一个词）时扩展召回

场景五：全文检索 vs LIKE 的性能对比

当前数据状态：基于 articles 表。

执行语句：

-- LIKE 模糊查询（全表扫描）
EXPLAIN SELECT * FROM articles WHERE content LIKE '%MySQL%';

-- 全文检索（使用全文索引）
EXPLAIN SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL');

操作后结果：

LIKE '%MySQL%' 的 EXPLAIN：

MATCH ... AGAINST('MySQL') 的 EXPLAIN：

结果解读：

• LIKE '%关键词%'：type = ALL，全表扫描，无法使用普通索引
• MATCH ... AGAINST：type = fulltext，使用全文索引 ft_idx
• 数据量越大，性能差距越明显。全文检索在百万级文本数据中仍能保持毫秒级响应

常见误区

面试考点

Q：全文检索和 LIKE '%关键词%' 的区别？

1. 全文检索使用倒排索引，LIKE '%...%' 无法使用索引，全表扫描。2. 全文检索支持相关性排序、布尔逻辑、查询扩展；LIKE 只有简单匹配。3. 全文检索有最小词长和 50% 阈值限制；LIKE 无限制。4. 全文检索只适用于文本字段；LIKE 适用于任何字符串字段。

Q：什么是 50% 阈值？如何解决？

自然语言模式下，出现在超过 50% 行中的词被视为"太常见"而被忽略。解决：1. 改用布尔模式（IN BOOLEAN MODE），无 50% 阈值；2. 使用子查询缩小数据集后再全文检索；3. 使用查询扩展模式。

Q：InnoDB 和 MyISAM 的全文索引有什么区别？

MyISAM 从早期就支持全文索引，功能成熟；InnoDB 从 5.6.4 开始支持，5.7 功能完善。主要区别：InnoDB 支持事务（全文索引随事务提交/回滚），MyISAM 不支持；InnoDB 使用 innodb_ft_min_token_size（默认 3），MyISAM 使用 ft_min_word_len（默认 4）；InnoDB 支持多全文索引，MyISAM 早期限制一个。

Q：中文全文检索怎么做？

MySQL 默认按空格分词，中文无空格，直接全文检索效果差。解决方案：1. MySQL 5.7.6+ 使用 ngram 解析器（WITH PARSER ngram），按字符 n-gram 分词；2. 使用第三方中文分词插件（如 jieba、 SCWS）；3. 程序层分词后存倒排表。

Q：MATCH 在 SELECT 和 WHERE 中的区别？

WHERE MATCH(...) AGAINST(...) 用于过滤匹配行；SELECT MATCH(...) AGAINST(...) 返回相关性评分（浮点数），可用于排序或展示。两者可以同时在一条 SQL 中使用。

小结

• 全文检索通过 FULLTEXT INDEX + MATCH ... AGAINST 实现高效文本搜索
• 三种模式：自然语言（默认，有 50% 阈值）、布尔模式（精确逻辑控制）、查询扩展（短查询召回增强）
• 性能远超 LIKE '%...%'（全表扫描 vs 倒排索引）
• 中文需要额外配置（ngram 解析器或第三方分词）
• 适合文章标题/内容搜索、日志检索、商品描述搜索等文本密集型场景

下一章引子：全文检索解决了大文本搜索的效率问题，但日常运维中，我们还需要频繁查看数据库的状态信息——表结构、索引列表、引擎状态等。SHOW 语句是 MySQL 运维的"瑞士军刀"。