MySQL高级知识（八）——ORDERBY优化

前⾔：在使⽤order by时，经常出现Using filesort，因此对于此类sql语句需尽⼒优化，使其尽量使⽤Using index。0.准备

#1.创建test表。

drop table if exists test;create table test(

id int primary key auto_increment,c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10)

) ENGINE=INNODB default CHARSET=utf8;

insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

#2.创建索引。

1.根据Case分析order by的使⽤情况Case 1：

分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使⽤范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要⽤于排序，并不是查询。②使⽤c1进⾏排序，出现了Using filesort。③解决⽅法：使⽤覆盖索引。

Case 1.1：

分析：

排序时按照索引的顺序，所以不会出现Using filesort。Case 1.2：

分析：

出现了Using filesort。原因：排序⽤的c2，与索引的创建顺序不⼀致，对⽐Case1.1可知，排序时少了c1（带头⼤哥），因此出现Using filesort。Case 1.3：

分析：

出现了Using filesort。因为排序索引列与索引创建的顺序相反，从⽽产⽣了重排，也就出现了Using filesort。Case 2：

分析：

直接使⽤c2进⾏排序，出现Using filesort，因为不是从最左列索引开始排序的（没有带头⼤哥）。Case 2.1：

分析：

排序使⽤了索引顺序（带头⼤哥在），因此不会出现Using filesort。Case 2.2：

分析：

虽然排序的字段列与索引顺序⼀样，且order by默认升序，这⾥c2 desc变成了降序，导致与索引的排序⽅式不同，从⽽产⽣Using filesort。总结：

①MySQL⽀持两种⽅式的排序filesort和index，Using index是指MySQL扫描索引本⾝完成排序。index效率⾼，filesort效率低。②order by满⾜两种情况会使⽤Using index。#1.order by语句使⽤索引最左前列。

#2.使⽤where⼦句与order by⼦句条件列组合满⾜索引最左前列。

③尽量在索引列上完成排序，遵循索引建⽴（索引创建的顺序）时的最佳左前缀法则。④如果order by的条件不在索引列上，就会产⽣Using filesort。#1.filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使⽤双路排序，就是两次磁盘扫描，得到最终数据。读取⾏指针和order by列，对他们进⾏排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进⾏排序，再从磁盘取其他字段。

如果使⽤双路排序，取⼀批数据要对磁盘进⾏两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进⾏排序，然后扫描排序后的列表进⾏输出。它的效率更⾼⼀些，避免了第⼆次读取数据，并且把随机I/O变成了顺序I/O，但是会使⽤更多的空间，因为它把每⼀⾏都保存在内存中了。#2.单路排序出现的问题。

当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产⽣多次I/O，反⽽增加其I/O运算。解决⽅式：

a.增加sort_buffer_size参数的设置。

b.增⼤max_length_for_sort_data参数的设置。⑤提升order by速度的⽅式：

#1.在使⽤order by时，不要⽤select *，只查询所需的字段。

因为当查询字段过多时，会导致sort_buffer不够，从⽽使⽤多路排序或进⾏多次I/O操作。#2.尝试提⾼sort_buffer_size。

#3.尝试提⾼max_length_for_sort_data。⑥附上⼀张从视频中截取出来的总结图。

⑦group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当⽆法使⽤索引列的时候，也要

对sort_buffer_size和max_length_for_sort_data参数进⾏调整。注意where⾼于having，能写在where中的限定条件就不要去having限定了。by Shawn Chen，2018.6.26⽇，上午。相关内容