MySQL 5.6按主键ORDER BY查询极慢、按非主键排序正常问题咨询

问题核心原因 #

这个现象并不是ORDER BY执行顺序早于WHERE，本质是MySQL 5.6优化器的索引选择策略出现了预估偏差，导致选中了更低效的执行路径：

• 对于第一条ORDER BY id DESC LIMIT 50的查询：
  MySQL优化器做决策时会优先考虑避免排序的成本：主键索引本身就是按id有序存储的，优化器预估「按主键倒序遍历，每一行判断是否符合author_id = 14 and publication_date < '2021-08-01'的条件，凑够50条就终止遍历」的成本，远低于「先按索引过滤出所有符合条件的行，再按id倒序排序取前50条」的成本。
  但实际场景里符合WHERE条件的行占比极低、或者在主键索引上分布非常分散，导致MySQL需要遍历数万甚至数十万行主键数据，才能凑够50条符合过滤条件的结果，最终出现几十秒的慢查询。EXPLAIN里的Using where也印证了这个逻辑：存储引擎返回每一行后，都要在Server层做WHERE条件判断。
• 对于第二条ORDER BY publication_date DESC LIMIT 50的查询：
  你应该是已经建立了(author_id, publication_date)的联合索引（或者至少包含这两个字段的前缀联合索引），这个索引的结构是先按author_id排序，同author_id下再按publication_date排序，完全匹配查询的过滤条件+排序逻辑：优化器可以直接定位到author_id=14的位置，再从小于2021-08-01的最大publication_date开始倒序取50条，所有取出的行天然符合WHERE条件，不需要额外过滤和排序，所以执行效率极高。Using index condition就是ICP（索引条件下推）特性生效的标识，直接在存储引擎层就用索引完成了条件过滤，不需要返回Server层再判断，性能更好。

常见修复方案 #

如果要修复第一条慢查询，可以选择以下任意一种方案：

• 手动给第一条查询加索引提示，强制走(author_id, publication_date)联合索引：

SELECT * from books FORCE INDEX(你的联合索引名)
where author_id = 14 
and publication_date < '2021-08-01' 
order by id desc limit 50

• 如果你需要保留ORDER BY id DESC的排序逻辑，也可以新建(author_id, publication_date, id)的联合索引，索引本身就可以满足过滤+按id排序的需求，优化器会自动选择这个索引，不需要改SQL逻辑。

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