MySQL数据库ORDER BY优化是怎样的呢

共计 3053 个字符，预计需要花费 8 分钟才能阅读完成。

今天就跟大家聊聊有关 MySQL 数据库 ORDER BY 优化是怎样的呢，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

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

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

1. 环境准备

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. 创建 btree 索引

create index idx_c1234 on test(c1,c2,c3,c4); show index from test;

3. 范围扫导致全表扫描

explain select * from test where c1 a1  order by c1;

分析：

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

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

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

4、覆盖索引 –》优化

explain select c1 from testwhere c1 a1  order by c1;

分析：

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

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

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

5. 没有按最左列索引排序

explain select c1 from testwhere c1 a1  order by c2;

分析：

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

6. 排序索引列与索引创建的顺序相反

explain select c1 from testwhere c1 a1  order by c2,c1;

分析：

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

7. order by 索引列排序不一致

explain select c1 from testwhere 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 运算。

解决方式：

增加 sort_buffer_size 参数的设置。

增大 max_length_for_sort_data 参数的设置。

4. 提升 order by 速度

在使用 order by 时，不要用 select  *，只查询所需的字段。因为当查询字段过多时，会导致 sort_buffer 不够，从而使用多路排序或进行多次 I / O 操作。

增加 sort_buffer_size。

增加 max_length_for_sort_data。

5. 优化 group by

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

看完上述内容，你们对 MySQL 数据库 ORDER BY 优化是怎样的呢有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。