InnoDB
引擎有两种排序方式
- 对返回的数据进行排序,
Explain
的Extra
列显示using filesort
,所有不是通过索引直接返回排序结果的排序都称为Filesort
- 通过有序索引顺序扫描直接返回有序数据,
Explain
的Extra
列显示using index
MySQL
优化排序的思路:尽量减少额外的排序,或者通过有序索引直接返回有序数据。通常情况下,where
条件和order by
使用相同的索引,并且order by
后面的索引要和索引定义的顺序保持一致,而且要保证多字段排序都是正序或倒序,否则就会出现Filesort
。
Filesort
的两种排序算法
-
两次扫描算法
MySQL4.1
之前,默认使用这种方式。大致流程:首先根据条件取出排序字段和行指针信息,然后在排序区sort buffer
中排序,如果sort buffer
大小不够,将排序结果放入temporary table
中,完成排序后,根据行指针回表查询数据。这算法可能会有大量的IO
操作,效率低。 -
一次扫描算法
一次性将满足条件的所有字段放入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;