InnoDB引擎之Order By排序优化

InnoDB引擎有两种排序方式

1. 对返回的数据进行排序，Explain的Extra列显示using filesort，所有不是通过索引直接返回排序结果的排序都称为Filesort
2. 通过有序索引顺序扫描直接返回有序数据，Explain的Extra列显示using index

MySQL优化排序的思路：尽量减少额外的排序，或者通过有序索引直接返回有序数据。通常情况下，where条件和order by使用相同的索引，并且order by后面的索引要和索引定义的顺序保持一致，而且要保证多字段排序都是正序或倒序，否则就会出现Filesort。

Filesort的两种排序算法

1. 两次扫描算法
   MySQL4.1之前，默认使用这种方式。大致流程：首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer大小不够，将排序结果放入temporary table中，完成排序后，根据行指针回表查询数据。这算法可能会有大量的IO操作，效率低。

2. 一次扫描算法
   一次性将满足条件的所有字段放入sort buffer中排序，内存开销大，但排序效率比两次扫描要高。

注意区分加粗的字，排序字段和行指针信息 和 所有字段 这算是两种算法的核心。

Filesort 无法避免时的处理方式

MySQL通过比较系统变量max_length_for_sort_data的大小和Query取出的字段总大小来判断用哪种算法排序，如果max_length_for_sort_data足以放下所有字段，那么使用一次扫描算法。我们可以适当增大max_length_for_sort_data和sort_buffer_size系统变量扩大排序区的大小，提高排序效率。

查看大小
show variables like 'max_length_for_sort_data';
show variables like 'sort_buffer_size';

设置当前连接的大小
set max_length_for_sort_data = 2048;
set sort_buffer_size = 2097152;
设置全局大小
set global max_length_for_sort_data = 2048;
set global  sort_buffer_size = 2097152;