mysql排序优化技巧

概述

在使用order by时，经常出现Using filesort，所以对于此类sql语句我们需要去尽力优化，使其尽量使用Using index。

那么，我们对于这类型的语句我们怎么去做优化呢？因为这一块还是比较容易混淆的，所以我弄了个实验，相信大家跟我一起做下实验就都能理解了~

1、环境准备ｄｒｏｐ 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;ｉｎｓｅｒｔ into test(c1,c2,c3,c4,c5) values(‘a1‘,‘a2‘,‘a3‘,‘a4‘,‘a5‘);ｉｎｓｅｒｔ into test(c1,c2,c3,c4,c5) values(‘b1‘,‘b2‘,‘b3‘,‘b4‘,‘b5‘);ｉｎｓｅｒｔ into test(c1,c2,c3,c4,c5) values(‘c1‘,‘c2‘,‘c3‘,‘c4‘,‘c5‘);ｉｎｓｅｒｔ into test(c1,c2,c3,c4,c5) values(‘d1‘,‘d2‘,‘d3‘,‘d4‘,‘d5‘);ｉｎｓｅｒｔ into test(c1,c2,c3,c4,c5) values(‘e1‘,‘e2‘,‘e3‘,‘e4‘,‘e5‘);

2、创建btree索引create index idx_c1234 on test(c1,c2,c3,c4);show index from test;

3、范围扫导致全表扫描 explain ｓｅｌｅｃｔ * from test where c1>‘a1‘ order by c1;

分析：

1）在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。

2）使用c1进行排序，出现了Using filesort。

3）解决方法：使用覆盖索引。

4、覆盖索引--》优化explain ｓｅｌｅｃｔ c1 from test where c1>‘a1‘ order by c1;

分析：

1) 使用了覆盖索引，不走全扫，走索引范围扫描

2）排序时按照索引的顺序，所以不会出现Using filesort。

这里不懂没关系，后面我会分享索引的八大法则，保证看得懂...

5、没有按最左列索引排序explain ｓｅｌｅｃｔ c1 from test where c1>‘a1‘ order by c2;

分析：

这里出现了Using filesort，是因为排序用的c2，与索引的创建顺序（c1,c2,c3,c4）不一致。

6、排序索引列与索引创建的顺序相反explain ｓｅｌｅｃｔ c1 from test where c1>‘a1‘ order by c2,c1;

分析：

这里出现了Using filesort。因为排序索引列(c2,c1)与索引创建的顺序(c1,c2)相反，从而产生了重排，也就出现了Using filesort。

7、order by索引列排序不一致explain ｓｅｌｅｃｔ c1 from test where c1>‘a1‘ order by c1 asc,c2 desc;

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。如果是order by c1 asc,c2 asc或者order by c1 desc,c2 desc就会是using index了。

实验总结

1、MySQL支持两种方式的排序filesort和index

Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、为排序使用索引

假设KEY test(a,b,c)

1)order by 能使用索引最左前缀

-order by a-order by a,b-order by a,b,c-order by a asc,b asc,c asc-order by a desc,b desc,c desc

2)如果where使用索引最左前缀定位为常量，则order by可以使用索引

-where a= const order by b,c-where a= const and b= const order by c-where a= const and b> consst order by b,c

3)不能使用索引进行排序

-order by a asc,b desc, c desc /*排序不一致*/-where g=const order by b,c /*丢失a索引*/-where a=const order by c /*丢失b索引*/-where a=const order by a,d /*d不是索引一部分*/-where a in (....) order by b,c /*对于排序来说，多个相等条件也是范围查询*/

3、filesort有两种排序算法：双路排序和单路排序

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。但当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

解决方式：

1）增加sort_buffer_size参数的设置。2）增大max_length_for_sort_data参数的设置。

4、提升order by速度

1）在使用order by时，不要用ｓｅｌｅｃｔ *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。2）增加sort_buffer_size。3）增加max_length_for_sort_data。

5、优化group by

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~