MySQL索引失效排查：同WHERE条件下SELECT*与SELECT id索引使用差异

问题解答 #

1、当前问题的根本原因 #

你创建的idxTimestampStatus2是(status, timestamp)的二级联合索引，InnoDB二级索引的叶子节点仅存储索引列和主键id的值。

• 第二条select id查询需要的status、timestamp、id三个字段都存在于该索引中，属于覆盖索引，不需要回表查询聚簇索引，成本极低，因此优化器选择走该索引。
• 第一条select *查询需要获取表中所有字段，使用idxTimestampStatus2索引查询到主键id后，还需要回表到聚簇索引查询body、handle_after等剩余字段。优化器评估回表产生的随机IO总成本高于全表扫描的顺序IO成本，因此放弃使用索引，直接走全表扫描。
  另外idxTimestampStatus索引的顺序是(timestamp, status)，不符合status等值匹配在前、timestamp范围匹配在后的查询条件最左前缀匹配规则，本身查询效率就很低，也不会被优化器优先选择。

2、让SELECT *查询正常使用索引的方案 #

有两种可行方案：

• 首先执行ANALYZE TABLE test;更新表的统计信息，让优化器正确评估符合条件的行数（从执行计划看符合条件的只有4行，回表成本实际远低于全表扫），统计信息更新后优化器大概率会自动选择走idxTimestampStatus2索引。
• 如果统计信息更新后仍然不走索引，可以在查询中强制指定索引：select * from test force index(idxTimestampStatus2) where status = 'in_queue' and timestamp > 1625721850;，但注意不要滥用强制索引，避免后续数据分布变化后索引效率降低。
  注意：如果需要查询longtext类型的body字段，无法将其完整加入普通索引实现覆盖索引，因此select *查询不可避免需要回表，只有当查询过滤性足够高（符合条件的行占比低于10%）时，优化器才会优先选择走索引。

3、查询返回列数影响索引使用的原因 #

InnoDB的成本模型中，随机IO的成本远高于顺序IO：

• 如果查询返回的列都包含在所选二级索引中，就是覆盖索引，只需要顺序遍历二级索引即可拿到所有需要的数据，没有额外IO成本。
• 如果查询返回的列有字段不在二级索引中，每查到一条数据都需要额外执行一次随机IO回表到聚簇索引取数，返回的列越多，需要回表的概率越高、总IO成本越高。
  当优化器计算走二级索引+回表的总成本高于全表扫描的顺序IO成本时，就会放弃使用索引，因此返回列的数量会直接影响索引的选择。

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