MySQL 5.6查询优化器针对带主键与复合索引表的查询行为异常

这是个典型的查询优化器索引选择误判问题，结合你的表结构、数据分布和查询案例，咱们一步步拆解背后的逻辑：

先明确两个索引的核心区别 #

你的表有两个关键索引：

• PRIMARY索引（聚簇索引）：InnoDB的聚簇索引直接存储整行数据，并且按id的字典序排序。走这个索引的话，查询会按id顺序扫描行，直到找到满足条件的数据，但如果满足条件的行在索引中位置靠后，就会扫描大量无关数据。
• some_other_id_col_1_col_2_idx（联合非聚簇索引）：只包含some_other_id、col_1、col_2三个字段，叶子节点存储对应的主键id。走这个索引时，能快速过滤出some_other_id匹配的行，再通过主键回表获取完整数据，但如果需要排序，得先收集所有匹配的id再排序。

逐个分析你的查询案例 #

快的查询（1、3、6）：为什么选联合索引？ #

• 查询1/3：some_other_id='VAL_1' + LIMIT 2/3 + ORDER BY id。优化器估算：通过联合索引快速定位到VAL_1的所有行，取前2/3个主键id回表，即使需要对这几行排序，成本也极低，远低于走主键索引扫描大量不满足条件的行。
• 查询6：some_other_id='VAL_2' + LIMIT 2 + 无ORDER BY。没有排序需求时，优化器直接走联合索引过滤出VAL_2的行，取前2个回表，完全不需要扫描无关数据，自然快。

慢的查询（2、4、5）：为什么选错了主键索引？ #

这都是优化器基于统计信息的成本估算偏差导致的：

• 查询2：VAL_1 + LIMIT 1 + ORDER BY id。优化器的逻辑是：“既然要按id排序取第一个匹配项，那走主键索引按顺序扫，找到第一个满足条件的就停，成本应该最低”。但实际数据分布是：满足some_other_id='VAL_1'且status='activated'的行在主键索引里的位置极靠后，导致优化器扫了几乎整个表才找到目标行，耗时15分钟。
• 查询4/5：VAL_2 + LIMIT 2 + ORDER BY id。优化器可能认为VAL_2对应的匹配行数很多，走联合索引的话，需要收集所有匹配的id再排序，成本很高；而走主键索引本身就是按id排序的，扫到2个满足条件的就行。但实际情况是，满足条件的行在主键索引里分散在极靠后的位置，导致扫描了大量无关数据才凑够2条。

解决方案建议 #

1. 更新表统计信息：执行ANALYZE TABLE tests;，让MySQL优化器拿到更准确的数据分布，修正成本估算偏差。这是最基础的操作，很多索引选择问题都是统计信息过时导致的。
2. 创建针对性联合索引：推荐创建(some_other_id, status, id)这个联合索引——它能直接过滤some_other_id和status，同时按id排序，完美匹配你的查询的WHERE和ORDER BY需求，不需要回表后再排序，性能会大幅提升。如果想进一步覆盖查询字段，可以把需要返回的列也加进去（但要注意索引不要太宽）。
3. 临时强制索引（应急用）：如果优化器还是顽固选错索引，可以在查询中用FORCE INDEX (some_other_id_col_1_col_2_idx)强制走联合索引，但这是临时方案，长期来看还是优化索引更靠谱。
4. 检查数据分布：因为id和some_other_id都是时间戳加随机字符生成，主键索引是按id字典序排序的，可能some_other_id相同的行在主键索引里分布得非常分散，这也是导致主键索引扫描低效的原因之一。

内容的提问来源于stack exchange，提问作者Tej Pal Sharma