使用索引优化 Order By

在某些情况下，MySQL可能会使用索引来满足一个 ORDER BY 子句，并避免执行 filesort 操作时涉及的额外排序。
ORDER BY 涉及到的列需要在 where 子句中才能使用到索引 。
如果索引不包含查询访问的所有列，则仅在索引访问比其他访问方法更便宜（访问成本）时才使用索引。
假设存在联合索引 (key_part1, key_part2) ，则以下查询可以使用索引来解析该部分。
在此查询中，联合索引 (key_part1, key_part2) 会启动优化，避免排序：

  1. SELECT * FROM t1 ORDER BY key_part1, key_part2;

如果回表次数过多，产生的 I/O 成本过大，这个索引就不会被用到。所以有时候要避免使用 SELECT * 操作。
如果 t1 的引擎是 InnoDB，那么主键就是索引，可以被 Order By 语句解析：

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

但有些情况，MySQL 不能使用索引优化 Order By。
使用两个不同的索引排序：

SELECT * FROM t1 ORDER BY key1, key2;

这种情况最为常见，其他情况暂不提及。了解二级索引的数据结构和数据构成，可以将其他情况举一反三。

使用 filesort 优化 Order By

如果索引不能用于满足 ORDER BY 子句，则 MySQL 执行 filesort 读取表行并对其进行排序的操作。filesort 构成查询执行中的额外排序阶段。
要获取 filesort 操作的内存，优化器会预先分配固定数量的 sort_buffer_size 字节。单个 session 可以根据需要更改此变量的 session 值，以避免过多的内存使用，或根据需要分配更多内存。
如果结果集是太大，无法在内存中，filesort 会使用磁盘进行排序。
某些类型的查询特别适合于内存中的 filesort 操作。
例如，优化器可以用来 filesort 在内存中有效地处理 ORDER BY 以下形式的查询（和子查询）的操作，而无需临时文件：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

Order By 执行计划信息

使用 EXPLAIN 可以检查 MySQL 是否可以使用索引来解析 ORDER BY 子句：

  1. 如果输出 Extra 列缺少 Using filesort，则使用索引。
  2. 如果输出 Extra 列包含 Using filesort，则不使用索引。

此外，如果 Extra 列中有 filesort ，则 optimizer trace 输出包括 filesort_summary 块。例如：

{
    "filesort_summary": {
      "rows": 100,
      "examined_rows": 100,
      "number_of_tmp_files": 0,
      "sort_buffer_size": 25192,
      "sort_mode": "<sort_key, packed_additional_fields>"
    }
}

EXPLAIN 不区分优化器是否在内存中执行文件排序。在 optimizer trace 输出中可以看到这个排序过程是否在内存内部进行。

参考资料

ORDER BY Optimization