提升MySQL InnoDB全文检索性能的优化方案咨询

背景 #

• 数据库：MySQL 8.0，InnoDB引擎
• 表规模：约200万行数据，总大小2GB
• 全文索引列：sentence（TEXT类型）
• 参考文档：MySQL 8.0官方全文检索文档

旧查询（使用SQL LIKE） #

SELECT * FROM books WHERE sentence LIKE '%This is a sample search input string%' and author_id = 5 and publisher_id = 23;

新查询（使用MySQL FULL_TEXT检索） #

SELECT * FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;

问题 #

切换到FULL_TEXT检索后性能未达预期：

• 输入字符串少于10个词时，全文检索比LIKE快；
• 输入约25个词时，全文检索耗时超3秒，与LIKE相当；
• 字符串越长，全文检索性能越差，耗时甚至超过15秒。

查询分析 #

通过SHOW PROFILE分析发现，90%的时间消耗在“FULLTEXT initialization”阶段（参考文档：MySQL 8.0官方SHOW PROFILE文档）。

已尝试但无效的优化 #

• 改写查询尝试联合使用其他索引与全文索引：

select * from books as b1 join books b2 on b1.author_id = b2.author_id and b1.publisher_id = b2.publisher_id WHERE b2.author_id = 5 and b2.publisher = 23 and MATCH (b1.source) AGAINST ('Sample input string') LIMIT 1;

• 仅查询document_id而非整行记录：

SELECT id FROM books WHERE MATCH (sentence) AGAINST ('This is a sample search input string') and author_id = 5 and publisher_id = 23 LIMIT 1;

可行优化方案 #

1. 改用布尔模式执行全文检索 #

默认自然语言模式会对查询字符串做复杂权重计算与分词优化，长字符串下初始化开销极大。改用布尔模式可跳过冗余计算，直接匹配词项，大幅降低初始化耗时：

SELECT * FROM books 
WHERE MATCH (sentence) AGAINST ('+This +is +a +sample +search +input +string' IN BOOLEAN MODE)
AND author_id = 5 AND publisher_id = 23 
LIMIT 1;

注：+表示必须匹配该词，可根据需求调整布尔操作符。

2. 优化停用词配置 #

若查询字符串包含大量MySQL默认停用词（如"is"、"a"这类高频词），初始化阶段会额外消耗资源过滤这些词。可：

• 自定义停用词表，移除查询中频繁出现的词；
• 临时禁用停用词检查（修改ft_stopword_file参数为空），测试性能变化。

3. 调整查询执行顺序，先过滤小数据集 #

创建(author_id, publisher_id)普通索引，让MySQL先通过该索引筛选出符合条件的小范围数据，再对这些数据执行全文检索，减少初始化阶段处理的数据量：

SELECT * FROM books 
WHERE author_id = 5 AND publisher_id = 23
AND MATCH (sentence) AGAINST('长查询字符串' IN BOOLEAN MODE)
LIMIT 1;

MySQL优化器会优先利用author_id+publisher_id索引缩小数据集，再对结果集做全文检索，避免全表级别的全文初始化操作。

4. 检查并调整全文索引参数 #

• 确认ft_min_word_len参数：若查询中的词长度小于默认值（通常为4），这些词不会被索引，初始化阶段会做额外无效判断，可根据业务需求调整参数并重建全文索引；
• 执行OPTIMIZE TABLE books;：整理全文索引碎片，减少IO开销，对长期写入的表有一定优化效果。

5. 拆分查询逻辑 #

先通过普通索引筛选出符合author_id和publisher_id的记录ID，再对这些ID对应的记录执行全文检索：

SELECT b.* FROM books b
JOIN (SELECT id FROM books WHERE author_id=5 AND publisher_id=23) filtered
ON b.id = filtered.id
WHERE MATCH(b.sentence) AGAINST('长查询字符串' IN BOOLEAN MODE)
LIMIT 1;

这种方式强制MySQL先执行范围过滤，再做全文检索，避免优化器选择低效执行计划。

内容的提问来源于stack exchange，提问作者123mig