MySQL词相似度表索引查询性能异常及优化方案问询

咱们来一步步拆解你的问题，都是非常实际的数据库性能优化场景，我帮你理清楚背后的原因和解决方案：

得先搞清楚InnoDB中聚簇索引和二级索引的核心区别：

• 你的主键PRIMARY (word1_id, word2_id)是聚簇索引，它的叶子节点包含了表的所有列数据（包括value）。当执行SELECT value, word2_id FROM cooccurrence WHERE word1_id = 436 ORDER BY value DESC时，MySQL可以直接通过聚簇索引快速定位到所有word1_id=436的行，直接读取所需数据，不需要额外的回表操作；排序也只是在内存中对这些行的value进行处理（如果行数不多的话），所以速度很快。
• 而你的Word2 (word2_id, word1_id)是二级索引，它的叶子节点只存储索引列和主键值。当执行SELECT value, word1_id FROM cooccurrence WHERE word2_id = 436 ORDER BY value DESC时，MySQL首先通过Word2索引找到所有匹配的主键，然后需要逐行回到聚簇索引中读取value列——这会产生大量随机磁盘IO（因为word2_id=436的行在聚簇索引里是分散存储的），之后还要对读取到的value进行排序，这两个步骤直接拉高了耗时。
• 另外也有可能是统计信息不准确，导致MySQL没选Word2索引反而走了全表扫描，但从耗时差距来看，回表开销是主要原因。

当前表结构不太适配你的核心查询需求：

• 你需要频繁查询某个词ID出现在word1_id或word2_id的记录并按value降序，但当前结构只有聚簇索引能高效支持word1_id的查询，word2_id的查询依赖二级索引回表，性能差距明显。
• 不过好在数据是完全静态的，这给优化提供了很大空间，不需要考虑数据更新带来的索引维护成本。

针对你的静态数据场景，推荐几个实用的优化方向：

• 构建覆盖索引：
  • 针对word2_id的查询，创建覆盖索引(word2_id, value DESC, word1_id)。这样查询SELECT value, word1_id FROM cooccurrence WHERE word2_id = 436 ORDER BY value DESC可以直接从这个索引中获取所有需要的数据：索引按word2_id分组，value是降序排列的，既不需要回表，也不需要额外排序，性能会和word1_id的查询持平。
  • 同理，如果你想进一步优化word1_id的查询，可以创建(word1_id, value DESC, word2_id)的覆盖索引，避免聚簇索引扫描后的排序操作。
• 数据冗余存储：
  • 因为数据是静态的，可以复制一份数据创建反向表cooccurrence_reverse，表结构为word2_id INT(11) PRIMARY KEY, word1_id INT(11) PRIMARY KEY, value INT(11)，主键设为(word2_id, word1_id)。这样查询word2_id时直接查反向表，利用聚簇索引的优势，完全不需要回表。这种方案适合查询频率极高的场景，用存储空间换极致性能。
• 预生成排序结果：
  • 提前为每个词ID生成按value降序排列的关联记录列表，存储到辅助表word_similarity_rank（包含word_id, related_word_id, value），并按word_id建索引。查询时直接从辅助表取结果，完全避免实时排序和索引扫描的开销，非常适合静态数据的高频查询。

当你把value改为FLOAT（其实FLOAT和INT都是4字节，存储空间不会增加）、新增Year列（INT，4字节），数据量暴涨到几百GB时，会对性能产生显著影响，主要体现在：

1. 内存缓存命中率下降：InnoDB缓冲池无法容纳全部数据和索引，更多查询需要读取磁盘，延迟会明显升高。
2. 索引开销增大：二级索引的大小会随数据量膨胀，扫描索引和回表的IO成本都会上升。
3. 排序成本飙升：如果查询需要排序的数据量超过内存限制，MySQL会使用磁盘临时文件排序，速度大幅变慢。
4. 磁盘IO压力剧增：几百GB的数据意味着更多磁盘读写，普通机械硬盘可能无法支撑，建议换成SSD缓解。

可以通过以下措施降低影响：

• 增大InnoDB缓冲池大小（尽量设置为服务器内存的50%-70%），提高缓存命中率。
• 使用SSD存储，降低磁盘IO延迟。
• 按Year列做分区，查询时如果指定Year条件，可以只扫描对应分区的数据，减少扫描范围。
• 坚持使用覆盖索引，避免回表操作，减少磁盘IO次数。

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