本周收到哨兵报警，以下SQL查询超时。select*fromorder_infowhereuid=5837661orderbyidasclimit1执行showcreatetableorder_info发现这个表其实是有索引的CREATETABLE`order_info`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`uid`int(11)ULsigned,`order_status`DEnyint(3),...省略其他字段和索引PRIMARYKEY(`id`),KEY`idx_uid_stat`(`uid`,`order_status`),)ENGINE=InnoDBDEFAULTCHARSET=utf8理论上执行上面的SQL会命中idx_uid_stat索引，但实际上执行explain看explainselect*fromorder_infowhereuid=5837661orderbyidasclimit1，可以看到它的possible_keys（这条SQL可能涉及到的索引）是idx_uid_stat，但实际上（key）使用的是全表扫描。我们知道MySQL是根据cost来选择基于全表扫描的，还是会选择一个索引来执行最终的执行计划，所以看起来全表扫描的cost要小于基于idx_uid_stat索引执行的cost，但是我的第一感觉很奇怪，虽然这条SQL是回表的，但是它的限制是1，也就是说只选择一条满足uid=5837661的语句，即使表被返回。这个成本几乎可以忽略不计。优化器如何选择全表扫描？为了看清MySQL优化器为什么选择全表扫描，我打开optimizer_trace找出画外音：在MySQL5.6及之后的版本中，我们可以通过优化器trace功能查看优化器生成执行计划的全过程，使用optimizer_trace具体过程如下SEToptimizer_trace="enabled=on";//开启optimizer_traceSELECT*FROMorder_infowhereuid=5837661orderbyidasclimit1SELECT*FROMinformation_schema.OPTIMIZER_TRACE;//查看执行计划表SEToptimizer_trace="enabled=off";//关闭optimizer_traceMySQL全表扫描首先会计算然后选择SQL中可能涉及的所有索引并计算索引的代价，然后选择代价最小的执行。我们来看看优化器trace给出的关键信息{"rows_estimation":[{"table":"`rebate_order_info`","range_analysis":{"table_scan":{"rows":21155996,"cost":4.45e6//全表扫描成本}},..."analyzing_range_alternatives":{"range_scan_alternatives":[{"index":"idx_uid_stat","ranges":["5837661<=uid<=5837661"],"index_dives_for_eq_ranges":true,"rowid_ordered":false,"using_mrr":false,"我ndex_only":false,"rows":255918,"cost":307103,//使用idx_uid_stat索引的成本"chosen":true}],"chosen_range_access_summary":{//比较以上成本后选择的最终结果"range_access_plan":{"type":"range_scan","index":"idx_uid_stat",//可以看到最终选择了索引idx_uid_stat执行"rows":255918,"ranges":["58376617<=uid<=58376617"]},"rows_for_plan":255918,"cost_for_plan":307103,"chosen":true}}...可以看到全表扫描的代价是4.45e6，选择索引的代价idx_uid_stat为307103，远小于全表扫描的开销，而且从最终的选择结果（chosen_range_access_summary）来看确实选择了索引idx_uid_stat，但是为什么从explain看选择的是执行PRIMARY，也就是全表扫描?这个执行计划是不是错了，仔细看了下执行计划，发现有猫腻，执行计划里面有个reconsidering_access_paths_for_index_ordering选项这引起了我的注意ndex_provides_order":true,"order_direction":"asc","index":"PRIMARY",//可以看到选择了主键索引"plan_changed":true,"access_type":"index_scan"}}}是因为之所以排序是为了再次优化索引选择，由于我们的SQL使用了id排序（orderbyidasclimit1），所以优化器最终选择了PRIMARY，也就是全表扫描来执行，也就是说这个选择会忽略前面的基于索引成本的选择，为什么会有这样的选择，主要原因如下：简短的解释是优化器认为——或者应该说希望——扫描整个表（已经排序id字段)会很快找到有限的行，这样会使排序操作无效。使用orderbyidasc的基于id的排序方法，优化器认为排序是一个昂贵的操作，因此为了避免排序，它认为如果limitn的n很小，即使满表也能很快执行scan，所以选择了全表扫描，避免了id的排序（全表扫描其实就是根据id主键扫描聚簇索引，本身是根据id排序的）。如果这个选择是正确的，那就完了，但实际上这个优化是有bug的！实际选择idx_uid_stat会执行得更快（仅28ms）！网上很多人都反映过这个问题，而这个问题基本上只和SQL中orderbyidasclimitn的写法有关。如果n比较小，很大概率会进行全表扫描。如果n比较大，就会被选中。选择正确索引的bug最早可以追溯到2014年，很多人都呼吁官方及时修复这个bug。它可能很难实施。直到MySQL5.7和8.0才解决，所以在官方修复前尽量避免这种情况如果一定要用这种写法，怎么办？forceindex强制使用指定索引主要有两种方式，如下：select*fromorder_infoforceindex(idx_uid_stat)whereuid=5837661orderbyidasclimit1这种写法虽然可以，但是不够优雅。如果不推荐使用该索引怎么办？所以还有第二种更优雅的方案使用orderby(id+0)方案，如下select*fromorder_infowhereuid=5837661orderby(id+0)asclimit1这种方案也可以让优化器选择正确的索引，比较推荐！为什么这个trick行得通，因为虽然SQL是按照id排序的，但是加法等耗时操作是对id进行的（虽然只是个无用的0，但是已经足够骗过优化器了），优化器认为这时候基于全表扫描会消耗更多的性能，所以我会选择基于成本大小的方法来选择索引巨人之肩mysql优化器bughttp://4zsw5.cn/L1zEi
