InnoDB下MySQL全文索引性能劣于无索引问题排查

咱们先拆解下为啥你加了FULLTEXT索引反而变慢：

• 你用的是MySQL 5.6之前的版本，InnoDB的FULLTEXT索引功能还很受限：默认最小检索词长是4个字符（改参数要重启），而且布尔模式下的前缀匹配（+word1*）效率并不理想。更关键的是，你给两个字段单独建索引，查询时MySQL得分别对两个索引做检索，再把结果集做交集合并，这个额外的合并开销加上两次全文检索的成本，反而超过了全表扫描的开销。
• 你的全表扫描耗时稳定在0.9秒左右，说明InnoDB缓冲池已经把大部分数据缓存起来了，全表扫的IO开销极低，这时候全文索引的额外计算成本就凸显出来了。

下面给你几个适配当前场景的优化方案：

1. 改用联合FULLTEXT索引并调整查询语句 #

别给两个字段单独建索引，建一个包含name和surname的联合FULLTEXT索引：

CREATE FULLTEXT INDEX idx_name_surname ON `table`(name, surname);

然后修改查询语句，用一次MATCH AGAINST同时匹配两个字段，这样只需要一次全文检索，避免两次索引检索后的结果合并开销：

SELECT * FROM `table` 
WHERE MATCH(name, surname) AGAINST ('+word1* +word2*' IN BOOLEAN MODE);

注意：这种方式是在两个字段中同时匹配word1和word2，如果你的业务要求严格的name包含word1且surname包含word2，这个方法就不适用，但如果允许放宽匹配逻辑（比如任意字段含任意关键词），这是最省事的优化。

2. 调整FULLTEXT索引的核心参数（需重启MySQL） #

如果你的检索关键词长度小于4（比如中文词汇或短英文词），默认的ft_min_word_len=4会导致这些词不被索引，相当于全文索引没起作用。可以修改my.cnf（或my.ini）：

ft_min_word_len = 2  # 根据你的实际关键词长度调整，比如中文常用2字

修改后重启MySQL，然后重建FULLTEXT索引：

ALTER TABLE `table` DROP INDEX idx_name, ADD FULLTEXT INDEX idx_name(name);
ALTER TABLE `table` DROP INDEX idx_surname, ADD FULLTEXT INDEX idx_surname(surname);

这样短关键词也能被索引到，检索效率会有明显提升。

3. 引入第三方全文检索工具（比如Sphinx） #

MySQL 5.6之前的InnoDB全文索引确实能力有限，如果你需要严格的字段级模糊匹配，又不能转MyISAM，Sphinx是个靠谱的选择。它可以直接从MySQL同步数据，建立专门的全文索引，支持高效的多字段组合检索，性能比MySQL原生FULLTEXT好很多。你只需要把这类模糊查询请求转到Sphinx，查到主键后再回MySQL取完整数据即可。

4. 前缀/反向索引辅助优化（针对特定模糊场景） #

如果你的查询关键词大多出现在字段开头或结尾，可以尝试：

• 前缀索引：给name建name(90)的前缀索引，仅对LIKE 'word%'这类前缀匹配有效；
• 反向索引：新增name_reverse字段存储反转后的name值，建索引后，LIKE '%word'可以转化为LIKE 'drow%'用索引查询。
  不过这个方法对全模糊匹配（%word%）的提升有限，只能作为辅助手段。

5. 数据分区优化 #

如果你的表有合适的分区维度（比如创建时间），可以把表分成多个分区，查询时只扫描部分分区，减少扫描的数据量。但这个方法依赖表的结构，对全表模糊查询的提升是间接的。

最后提一句：你当前的全表扫描耗时0.9秒，对于62万数据来说其实不算特别慢，如果业务能接受这个延迟，也可以暂时保留全表扫描，等升级到MySQL 5.6+之后，InnoDB的全文索引性能会大幅提升，到时换原生联合全文索引会更省心。

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