1. 案例

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'dev' ORDER BY age

最左前缀法则：中间字段不能断，因此查询只用到了name索引，从key_len也能看出

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position

从key_len看出，只用到了name索引，由于用了position排序，跳过了age，出现了Using filesort

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age,position

查看只用到索引name，age和position用于排序

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position,age

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 18 ORDER BY position,age

Extra中未出现Using filesort，因为age是常量，排序中被优化了，所以索引未颠倒，不会出现Using filesort

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age ASC, position DESC

虽然排序字段列和索引顺序一致，且order by默认升序，这里position desc变成了降序，导致索引的排序方式不同，从而产生Using filesort，MySQL8以上支持这种

2. 总结

• MySQL支持两种方式排序filesort和index，Using index只MySQL扫描索引本身完成排序，index效率高，filesort效率低

• order by满足两种情况会使用Using index

• order by语句使用索引最左前缀列

• 使用where子句与order by子句条件列组合满足最左前缀列

• 尽量在索引列上完成排序，遵循索引建立的顺序

• 如果order by的条件不在索引列上，会产生Using filesort

• 能用覆盖索引就用覆盖索引

• group by 和 order by类似，就是先排序后分组，遵循最左前缀法则

3. Using filesort

filesort文件排序方式

• 单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

• 双路排序（回表排序模式）：根据相应条件取出相应的排序字段和可以直接定位行数据的行ID，在sort buffer中排序，排完序后再次取回需要的字段，用trace工具可以在sort_mode中看到< sort_key, rowid >

MySQL通过比较默认系统值max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用那种模式

• max_length_for_sort_data > 查询字段总长度 ， 使用单路排序模式

• max_length_for_sort_data < 查询字段总长度， 使用双路排序模式

单路排序详细过程：

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id

1. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中

1. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id

1. 重复步骤 2、3 直到不满足 name = ‘zhuge’

1. 对 sort_buffer 中的数据按照字段 position 进行排序

1. 返回结果给客户端

双路排序详细过程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id

1. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中

1. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id

1. 重复 3、4 直到不满足 name = ‘zhuge’

1. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序

1. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

单路排序会把查询到的字段信息全部放到sort buffer中，而双路排序只会把主键和需要排序的字段放到sort buffer中，然后通过主键回表查询

如果MySQL内存配置小且无法增加，可以把max_length_for_sort_data 调小，去选择双路排序，sort buffer一次可以放更多排序值

单路排序是从内存sort buffer去拿数据，要快于从磁盘中拿数据