书籍 / MySQL 性能优化完全指南
第 39 章

全文检索

MySQL 全文检索

MySQL InnoDB 引擎从 5.6 版本开始支持全文索引（FULLTEXT Index）。与 LIKE '%keyword%' 全表扫描不同，全文检索使用倒排索引（Inverted Index）实现高效的文本搜索，并支持相关度排序。MySQL 8.0 还引入了 ngram 分词器，为中文搜索提供了原生支持。

全文索引基础

-- 创建全文索引
CREATE TABLE articles (
    id      INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    title   VARCHAR(200)    NOT NULL,
    content TEXT,
    author  VARCHAR(100),
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FULLTEXT INDEX ft_title_content (title, content)  -- 多列全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 在现有表上添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
CREATE FULLTEXT INDEX ft_title ON articles (title);

-- 全文索引要求:
-- 列类型必须是 CHAR, VARCHAR, TEXT
-- 只支持 InnoDB 和 MyISAM 引擎
-- 字符集必须是 utf8 系列（中文需要 ngram）

InnoDB 全文索引的底层结构

-- 全文索引存储在 6 个辅助表中
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE LIMIT 5;
-- WORD: 词语
-- FIRST_DOC_ID, LAST_DOC_ID: 文档 ID 范围
-- DOC_COUNT: 包含该词的文档数
-- DOC_ID: 具体文档 ID
-- POSITION: 在文档中的位置

-- 查看全文索引缓存（插入时先进缓存，达到阈值后合并）
SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE LIMIT 5;

-- 手动刷新全文索引缓存到磁盘（查询时自动触发，一般不需要手动）
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE articles;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

自然语言模式（默认）

自然语言模式（Natural Language Mode）是默认的全文搜索模式，返回相关度得分，自动过滤停用词和过于常见的词（出现在超过 50% 文档中的词）。

-- 基础自然语言搜索
SELECT id, title,
       MATCH(title, content) AGAINST ('MySQL performance tuning') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST ('MySQL performance tuning')
ORDER BY relevance DESC
LIMIT 10;

-- 注意：MATCH...AGAINST 必须同时出现在 WHERE 和 SELECT 中
-- WHERE 中的 MATCH 触发索引扫描，SELECT 中的 MATCH 获取相关度分数

-- 不写 ORDER BY relevance 时，MySQL 通常也会按相关度排序
-- 但显式写出更安全（某些情况下不保证顺序）

-- 限制：
-- 1. 默认最短词长 innodb_ft_min_token_size = 3（少于3字符的词被忽略）
-- 2. 过于常见的词（出现在50%以上文档中）被视为停用词
-- 3. 默认停用词列表包含: a, an, the, is, are... 等英文词

配置参数

-- 修改最小词长（需要重建索引后生效）
SET GLOBAL innodb_ft_min_token_size = 2;

-- 查看当前停用词
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

-- 自定义停用词表
CREATE TABLE my_stopwords (value VARCHAR(30));
INSERT INTO my_stopwords VALUES ('and'), ('or'), ('the');
SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';

-- 禁用停用词（中文环境通常需要）
SET GLOBAL innodb_ft_enable_stopword = OFF;

布尔模式

布尔模式（Boolean Mode）提供精确的搜索控制，支持 + - * "" ~ 等操作符，不计算相关度得分（除非显式计算）。

-- 布尔模式操作符
-- +word  : 必须包含
-- -word  : 必须不包含
-- word*  : 通配符（以 word 开头）
-- "phrase" : 精确短语
-- ~word  : 降低包含该词的相关度
-- (expr) : 分组

-- 必须包含 MySQL，可以包含 performance，不能包含 deprecated
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST
    ('+MySQL performance -deprecated' IN BOOLEAN MODE)
ORDER BY MATCH(title, content) AGAINST
    ('+MySQL performance -deprecated' IN BOOLEAN MODE) DESC;

-- 精确短语搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST
    ('"query optimization"' IN BOOLEAN MODE);

-- 通配符搜索（搜索所有以 optim 开头的词）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST ('optim*' IN BOOLEAN MODE);
-- 匹配: optimize, optimization, optimizer...

-- 复合布尔查询
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST
    ('+(MySQL PostgreSQL) +("performance" "tuning") -("NoSQL")' IN BOOLEAN MODE);
-- 必须包含 MySQL 或 PostgreSQL，
-- 必须包含 performance 或 tuning 短语，
-- 不能包含 NoSQL 短语

查询扩展

查询扩展（Query Expansion）分两步：先找相关文档，再从这些文档提取高频词，用这些词再次搜索，找到更多相关结果（用于搜索词较短时扩大召回）。

-- 自动查询扩展
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST ('database' WITH QUERY EXPANSION)
LIMIT 20;
-- 第一步搜索 "database" 找到相关文档
-- 从结果中提取高频词（如 MySQL、SQL、index...）
-- 第二步用扩展词重新搜索，返回更多结果

-- 注意：查询扩展会增加执行时间，不适合实时搜索
-- 适合：搜索词很短、需要提高召回率的场景

中文全文检索（ngram 分词器）

英文通过空格分词，而中文没有自然的词界。MySQL 8.0 内置的 ngram 分词器将文本按 N 个字符滑动切割（默认 N=2），实现中文搜索。

-- 查看 ngram 配置
SHOW VARIABLES LIKE 'ngram_token_size';
-- ngram_token_size = 2 (默认，二字分词)

-- 创建使用 ngram 的全文索引
CREATE TABLE articles_cn (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title   VARCHAR(200) NOT NULL,
    content TEXT,
    PRIMARY KEY (id),
    FULLTEXT INDEX ft_content_ngram (title, content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "高性能 MySQL 数据库优化" 被 ngram(2) 分词为:
-- 高性 性能 能M My yS SQ QL L数 数据 据库 库优 优化

-- 搜索（ngram 模式不区分分词，直接匹配子串）
SELECT id, title,
       MATCH(title, content) AGAINST ('数据库优化' IN NATURAL LANGUAGE MODE) AS score
FROM articles_cn
WHERE MATCH(title, content) AGAINST ('数据库优化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- 布尔模式下的中文搜索
SELECT * FROM articles_cn
WHERE MATCH(title, content) AGAINST ('+数据库 +优化 -降级' IN BOOLEAN MODE);

-- ngram 搜索注意事项:
-- 搜索词少于 ngram_token_size 个字符的词会被忽略
-- 解决方案：修改 ngram_token_size=1（单字搜索，索引更大）

-- my.cnf 配置
[mysqld]
ngram_token_size = 2      -- 对中文通常是 2
innodb_ft_min_token_size = 2

中文搜索示例：电商商品搜索

CREATE TABLE products (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    tags        VARCHAR(500),
    PRIMARY KEY (id),
    FULLTEXT INDEX ft_product (name, description, tags) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 搜索"防晒 夏季"（布尔模式，两个词都要有）
SELECT id, name,
       MATCH(name,description,tags) AGAINST ('+防晒 +夏季' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(name,description,tags) AGAINST ('+防晒 +夏季' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

-- 搜索"防晒霜"（精确短语）
SELECT * FROM products
WHERE MATCH(name,description,tags) AGAINST ('"防晒霜"' IN BOOLEAN MODE);

相关度排序优化

-- 综合相关度：标题权重 > 描述权重
SELECT
    id,
    name,
    -- 标题匹配权重 × 3 + 描述匹配权重
    MATCH(name) AGAINST ('MySQL优化' IN NATURAL LANGUAGE MODE) * 3
    + MATCH(description) AGAINST ('MySQL优化' IN NATURAL LANGUAGE MODE) AS weighted_score
FROM products
WHERE MATCH(name, description) AGAINST ('MySQL优化' IN NATURAL LANGUAGE MODE)
ORDER BY weighted_score DESC
LIMIT 20;

-- 混合相关度：全文相关度 + 时间衰减
SELECT
    id, title, created_at,
    MATCH(title,content) AGAINST (?) AS ft_score,
    -- 时间衰减：越新的文章得分更高
    EXP(-0.01 * DATEDIFF(NOW(), created_at)) AS time_decay,
    MATCH(title,content) AGAINST (?) * EXP(-0.01 * DATEDIFF(NOW(), created_at)) AS final_score
FROM articles
WHERE MATCH(title,content) AGAINST (?)
ORDER BY final_score DESC
LIMIT 20;

优化与限制

限制 说明 解决方案
不支持中文分词 ngram 是滑动窗口，非真正中文分词 使用 Elasticsearch + IK 分词器
高频词（停用词）被忽略 "的 了 是"等词被过滤 关闭 innodb_ft_enable_stopword
短词被忽略 少于 min_token_size 的词 减小 innodb_ft_min_token_size
不支持模糊匹配（拼音/错别字） 输入错误无法匹配 Elasticsearch 的 fuzzy 查询
不支持同义词搜索 "手机"搜不到"手持设备" Elasticsearch 同义词词库
大文本表性能 全文索引文件可能很大 分区、单独全文索引表 
-- 全文索引性能调优
-- 1. 增大全文索引缓存（减少磁盘 I/O）
SET GLOBAL innodb_ft_cache_size = 16000000;       -- 单索引 16MB
SET GLOBAL innodb_ft_total_cache_size = 128000000; -- 总缓存 128MB

-- 2. 批量插入时先禁用全文索引，插入完再重建（快 5-10 倍）
ALTER TABLE articles DISABLE KEYS;
-- ... 大批量 INSERT ...
ALTER TABLE articles ENABLE KEYS;

-- 3. 查询时带其他条件先过滤再全文搜索
SELECT * FROM articles
WHERE created_at > '2024-01-01'           -- 先用普通索引过滤
  AND MATCH(title,content) AGAINST (?);   -- 再全文搜索

MySQL 全文检索 vs Elasticsearch

维度 MySQL 全文索引 Elasticsearch
中文分词 ngram（滑动窗口） IK 分词器（智能分词）
模糊匹配 不支持 支持（fuzzy query）
同义词 不支持 支持（同义词词库）
搜索性能（大规模） 千万级文档慢 亿级文档仍高效
数据一致性 实时一致（ACID） 近实时（1秒延迟）
运维成本 低（已有 MySQL） 高（独立集群）
与业务数据结合 原生 JOIN 需要 CDC 同步
高亮/摘要提取 不支持 支持
适用规模 <1000 万文档 亿级以上

选型建议：数据量 <500 万、搜索需求简单（关键词匹配即可）、团队不想引入 Elasticsearch 运维成本 → 使用 MySQL 全文索引。数据量 >1000 万、需要中文智能分词/模糊搜索/搜索建议/高亮显示 → Elasticsearch 是更好的选择，通过 Canal/Debezium CDC 实时同步 MySQL 数据到 ES。

本章评分
4.8  / 5  (3 评分)

💬 留言讨论