MySQL 5.6带索引查询性能劣于无索引的原因咨询

遇到这种“选了索引反而更慢”的情况，其实是分区表特性、索引设计和MySQL 5.6优化器逻辑共同作用的结果，咱们一步步拆解：

1. 以分区键为首列的复合索引，为啥被优化器放弃？ #

你的表是按network_date（DATE类型）做日分区，每个分区约40万行，而原来的复合索引首列就是network_date——这相当于索引的前缀和分区键完全重复了。

MySQL分区表的核心逻辑是用分区键做数据的物理隔离，当你的查询涉及network_date过滤时（从EXPLAIN的rows=447万来看，应该是覆盖了11个左右的分区），优化器会先做分区裁剪，只扫描符合条件的分区。这时候，分区已经帮你完成了network_date维度的数据筛选，再用以network_date为首列的复合索引，额外的索引前缀过滤几乎没有收益，反而要承担“索引扫描+回表”的双重IO开销。

优化器会对比“分区裁剪后的全表扫描”和“索引扫描+回表”的成本：全表扫描是顺序IO，MySQL对顺序读的优化非常好（比如预读机制）；而索引回表是随机IO，每一条符合条件的索引条目都要去磁盘上找对应的数据行，当数据量较大时，随机IO的效率远低于顺序IO。所以优化器最终选择了成本更低的全表扫描。

2. 去掉network_date后的索引，为啥被选中但更慢？ #

当你把索引里的network_date去掉后，这个索引失去了和分区键的关联，优化器无法再通过分区键来预判索引的过滤范围，只能认为这个索引能过滤掉一部分数据（从filtered=50%来看，优化器预估有一半的行符合条件），所以选择了走索引。

但这里的问题在于：

• 没有network_date前缀的索引，无法利用分区裁剪的优势，可能需要扫描全局所有分区的索引条目，索引扫描的范围反而变大了；
• 50%的filtered意味着，扫描完索引后，有200多万行需要回表取数据，这200多万次随机IO的开销，远大于全表扫描447万行的顺序IO开销——毕竟顺序读可以一次性加载大量数据到内存，而随机读只能一次次零散地找数据，耗时自然就上去了。

3. MySQL 5.6优化器的局限性 #

MySQL 5.6的优化器在处理分区表与复合索引的结合时，成本估算逻辑不如新版本（比如8.0）智能。它可能没有准确计算出“索引回表的随机IO总成本”，只是单纯看到索引能过滤数据，就做出了选择，却忽略了分区表场景下顺序IO的优势。

给你的优化建议 #

• 检查查询语句的过滤条件：如果network_date的范围过大（比如查了十几天的数据），分区裁剪后的数据量还是很大，那全表扫描确实可能更高效；如果是小范围日期，那应该调整索引，让索引的前缀包含network_date+高频过滤列，这样在分区内可以快速定位到目标行，减少回表；
• 考虑使用覆盖索引：如果查询需要的字段都包含在索引里，就可以避免回表，这时候索引的效率会大幅提升；
• 升级MySQL版本：新版本的优化器对分区表和索引的成本计算更精准，能做出更合理的选择。

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