为何指定索引的MySQL新查询执行计划更差却生产性能更优

执行计划与实际性能矛盾的核心原因 #

你对执行计划的两处常规解读存在偏差，结合查询实际执行逻辑，矛盾点的本质如下：

一、对执行计划列的误读 #

• type=index不代表性能一定优于type=ALL：执行计划的type列优先级是基于索引扫描效率设定的，但如果走索引需要大量回表随机IO，其成本远高于全表顺序扫描。
• rows是优化器估算的满足WHERE条件的总行数，不是实际执行时需要遍历的行数，估算值和实际值可以有数量级的偏差。

二、原查询（耗时10秒）的实际执行逻辑 #

原查询自动选择外键对应的bill_id二级索引，执行逻辑为：

1. 按bill_id升序遍历二级索引条目，刚好匹配ORDER BY bill_id的排序要求，无需额外排序。
2. 每遍历到一个bill_id对应的主键值，就回表到聚簇索引查询整行数据，判断4050 between low and high是否成立。
   优化器估算满足WHERE条件的行仅1行，因此误以为只需扫1_{2行索引、回表1}2次即可返回，对应执行计划显示rows=1、type=index。
   但实际生产中，满足WHERE条件的行在bill_id排序中位置极靠后：比如全表共10万行，匹配行的bill_id是最大值，就需要从最小bill_id开始逐行遍历，完成10万次随机回表IO，直到最后一行才命中，因此耗时久。

三、新查询（耗时5秒）的实际执行逻辑 #

你指定使用bill_range_low_high_index，但根据表定义，low/high是varchar(255)类型，传入的过滤值4050是数字，触发隐式类型转换：MySQL会将varchar字段值转成浮点数再比较，导致low字段的索引有序性失效，无法用索引做范围过滤，因此优化器放弃该索引，走全表扫（对应执行计划type=ALL、key=NULL）。
但全表扫是顺序读取聚簇索引的数据页，顺序IO速度是随机IO的10~100倍：哪怕扫完全部10万行数据，也仅需连续读取数据页，无需反复做随机回表操作，成本远低于原查询的10万次随机IO，因此实际性能更优。

四、后续优化方向 #

• 将low/high的字段类型从varchar改为int，消除隐式类型转换，让bill_range_low_high_index可直接用于过滤。
• 调整索引为(low, high, bill_id)：过滤完满足4050 between low and high的行后，bill_id直接存储在索引中，无需回表即可取最小bill_id，耗时可降至毫秒级。
• 定期执行analyze table bill_range更新统计信息，避免优化器因分布估算错误选错索引。

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