图片来自抱途网本周收到哨兵报警，以下SQL查询超时。select*fromorder_infowhereuid=5837661orderbyidasclimit1执行showcreatetableorder_info发现这张表其实已经被索引了：CREATETABLE`order_info`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`uid`int(11)unsigned,`order_status)`tinyint(3DEFAULTNULL,...省略其他字段和索引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是基于成本来选择是基于全表扫描还是基于索引来执行最终的执行计划，所以看起来全表扫描的成本要小于基于idx_uid_stat索引执行的成本。但我的第一感觉很奇怪。这条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=offim";计算全表扫描的代价，然后选择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,"index_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最终被选择执行“行”：255918，“范围”：[“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看到的option是执行PRIMARY，也就是全表扫描。这个执行计划错了吗？仔细看了执行计划，发现有猫腻。执行计划中有一个reconsidering_access_paths_for_index_ordering选项引起了我的注意。{"reconsidering_access_paths_for_index_ordering":{"clause":"ORDERBY","index_order_summary":{"table":"`rebate_order_info`","index_provides_order":true,"order_direction":"asc","index":"PRIMARY",//可以看到选择了主键索引"plan_changed":true,"access_type":"index_scan"}}}，也就是说由于排序又进行了一次索引选择优化，因为我们的SQL使用了id排序(orderbyidasclimit1)，优化器最终选择PRIMARY，也就是全表扫描来执行。也就是说，这个选择会忽略之前基于索引成本的选择。为什么会有这样的选择？主要原因如下：简短的解释是优化器认为——或者我应该说希望——扫描整个表（已经按id字段排序）将足够快地找到有限的行，这将避免排序操作。因此，通过尝试避免排序，优化器最终会浪费时间扫描表。从这个解释中我们可以看出，主要原因是因为我们使用了orderbyidasc这种基于id的排序方式，优化器认为排序是一个开销很大的操作。所以为了避免排序，它认为如果limitn的n小，即使全表扫描也能快速执行。所以它选择了全表扫描，这样就避免了ids的排序（全表扫描其实就是根据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，但是已经足够骗过优化器了），优化器认为这时基于全表扫描会消耗更多的性能，所以我们会根据开销的大小来选择索引。作者：坤哥，前独角兽技术专家，现创业，持续分享个人成长与收获。编辑：陶家龙来源：转载自公众号码海（ID：seaofcode）
