8.2.1.15 ORDER BY Optimization

有的时候,MySQL不用额外的排序而是使用索引就能满足order by操作。
即使order by不直接匹配索引，索引也能用到，只要所有未用到索引的部分和order by之外的索引列在where字句中，
下面的查询order by部分使用到了索引
SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;


SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;


SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;


SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;


SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;


SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;


SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;


但是有的时候，MySQL不会使用索引来解决order by的问题，即使where条件已经用到索引
1.order by使用不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
2.order by部分列非索引前缀
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
3.升降序混合
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
4.获取行的索引与order by部分不是同一个索引
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5.order by列上有函数
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;