为何给MySQL表新增列会改变FULLTEXT搜索结果？

核心背景 #

原有带FULLTEXT索引的InnoDB表，在新增自增主键列后，相同的MATCH() AGAINST()查询返回结果（排序或相关性分数）发生变化。

1. 为何新增“无关列”会影响FULLTEXT搜索结果？ #

你新增的并非完全无关列——自增主键id是InnoDB表的聚簇索引，添加主键会触发整个表的重建流程，连带所有索引（包括FULLTEXT索引）都会被重新构建。FULLTEXT搜索的相关性分数依赖索引中存储的词频、逆文档频率等统计数据，重建索引时这些统计值会被重新计算，最终导致relevance分数变化，进而改变结果排序。

2. ALTER TABLE导致的表/FULLTEXT索引重建是否会引发此问题？ #

是的，这是核心原因：

• InnoDB中添加主键属于会触发表重建的DDL操作（即使是Online DDL，底层仍需重建表结构和所有索引）。
• 重建FULLTEXT索引时，MySQL会重新扫描所有数据，计算每个词在文档中的出现频率、在整个表中的分布等统计信息，这些是计算MATCH() AGAINST()相关性分数的核心依据。
• 原表的FULLTEXT索引可能存在统计信息过时的情况（比如数据更新后未及时更新统计），重建后统计信息被刷新，分数计算逻辑不变，但输入的统计数据变了，最终结果就会不同。

3. MySQL的哪些特性可解释该行为？ #

• InnoDB聚簇索引特性：主键是InnoDB表的聚簇索引，添加主键会强制重建整个表，所有二级索引（包括FULLTEXT）都会基于新的聚簇索引重新构建。
• FULLTEXT相关性计算逻辑：MATCH() AGAINST()的分数由以下因素决定：
  • 词频(TF)：词在当前文档中的出现次数
  • 逆文档频率(IDF)：词在整个表中的出现频率（出现越少，权重越高）
  • 文档长度：文档越短，相同词的权重越高
    这些值都存储在FULLTEXT索引中，重建索引时会重新计算。
• DDL操作的索引重建机制：ALTER TABLE涉及主键变更时，MySQL会重新生成所有索引，而非在原有索引上修改，这意味着FULLTEXT索引的统计信息会被完全重置。

进一步排查建议 #

• 对比相关性分数：执行查询时单独输出relevance字段，确认是分数变化导致排序不同，而非结果集的行缺失/新增。
• 验证结果集一致性：去掉ORDER BY relevance DESC，查看两次查询返回的行是否完全一致，仅排序不同。
• 查看FULLTEXT索引统计：查询INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE，对比重建前后的词频、文档数等数据差异。
• 模拟统计信息更新：在原表上执行ANALYZE TABLE reviews;，看执行后查询结果是否和添加主键后的一致（ANALYZE会更新表统计信息，包括FULLTEXT相关的统计）。
• 手动重建FULLTEXT索引：在原表上删除并重新创建FULLTEXT索引，执行DROP INDEX Producer ON reviews; CREATE FULLTEXT INDEX Producer ON reviews(Producer);（同理处理Name、Year索引），查看查询结果是否变化。
• 检查配置参数：确认ft_min_word_len、ft_stopword_file、innodb_ft_min_token_size等FULLTEXT相关参数在操作前后未发生变化。

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