MySQL如何对order by优化？

对于order by的优化，MySQL若可以利用索引的有序性进行排序，则优先使用索引进行排序，这种情况的执行效率是最快的；若无法有效利用索引的情况下，MySQL主要有3排序种算法对其进行优化每个算法都有一定的适用场景。

一、 利用索引排序

B-tree索引可以很好的支持单点查询、范围查询、有序性查询。所以对于order by 的排序查询，我们可以利用B-tree的有序性来有效的利用索引进行排序查询。当然，如果可以利用索引进行排序对我们的SQL查询本身也是有一定的要求限制的。

1.1 利用索引排序的特点

1）排序列必须有B-tree索引

2）如果为多表关联查询，排序列必须是对驱动表字段的排序

1.2、示例

##建表语句，sbtest3与sbtest4表字段与索引一致，sbtest3的表数据量为30000，sbtest4的表数据量为60000
CREATE TABLE `sbtest4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_4` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=62768 DEFAULT CHARSET=utf8mb4

##单表排序查询
##order by字段为B-tree索引字段，可以看到执行计划有效利用了索引进行排序查询
root@mysql57 13:25:  [db2]> explain select * from sbtest4 t4 order by t4.k desc limit 5;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | k_4  | 4       | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


##多表关联排序查询
##sbtest3为小表，在表关联中作为驱动表与sbtest4进行关联查询，order by字段为驱动表b-tree索引字段，可有效利用索引进行查询
root@mysql57 13:26:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id order by t3.k limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | index  | PRIMARY       | k_3     | 4       | NULL      |    5 |   100.00 | NULL  |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

1.3、有效利用复合索引进行排序查询

1、当复合索引的最左前缀列为过滤条件的常量过滤时，order by字段配合常量过滤字段满足最左前缀时可以使用复合索引进行排序优化。

如，建立复合索引(a,b,c)可以使用复合索引扫描排序有：

from tbl_name where a=xx order by b,c;
from tbl_name where a=xx order by b;

2、过滤字段不是复合索引中的常量，但是order by列满足最左前缀是可以使用覆盖索引：

from tbl_name where a>xx order by a,b     #order by字段满足最左前缀

3、一些情况不能使用复合索引扫描排序的情况

from tbl_name where a=xx order by b desc,c asc;  #一列为升序一列为降序
from tbl_name where a=xx order by b,d;      #order by列引用了一个不在索引中的字段
from tbl_name where a=xx order by c;  #无法组合成索引的最左前缀
from tbl_name where a=xx and b in (xx,xx) order by c;    #存在范围查询

4、值得注意的一个小问题

如果我们建立单列索引（A），实际上相当于在（A，ID）上建立了索引，其中ID为主键。这中情况下对于 where A=xx order by ID的查询是非常有帮助的。但是如果我们建立了复合索引（A,B）,那么就相当于在(A,B,ID)上建立了索引，那么对于where A=xx order by ID这样的查询，就使用不到索引扫描排序，只能用filesort排序(using filesort)了。

1.4、无法使用索引排序的几种情况

1）排序基准太多，无法有效利用索引满足所有的排序字段准则

2）对多个union子查询进行排序

3）需要随机获取结果记录

二、仅对驱动表排序

2.1、仅对驱动表进行排序的特点

1）多表关联查询中，排序字段为驱动表字段，且该字段无法有效利用索引

2）被驱动表关联字段为有效索引字段，有效利用INLJ算法进行表关联

2.2、示例

##sbtest3作为驱动表，通过k字段的索引进行过滤找到满足where条件的记录
##由于order by字段c无法有效利用索引，所以必须将满足过滤条件的记录放至sort buffer进行排序处理
##在执行计划中，我们可以看到“Using filesort”表示使用filesort进行了排序处理

root@mysql57 13:55:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id where t3.k<1000 order by t3.c limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                                 |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range  | PRIMARY,k_3   | k_3     | 4       | NULL      |    1 |   100.00 | Using index condition; Using filesort |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)

2.3、对于使用驱动表排序的优化

若SQL无法有效利用索引进行优化，且仅仅是对驱动表进行排序处理，这已然是一种相对较好的情况，我们更多的只需要关注SQL的where过滤条件是否可以有效利用索引减少驱动表需要扫描以及排序的记录数。

三、使用临时表进行排序

3.1、使用临时表表进行排序的特点

多表关联查询中，排序字段为被驱动表字段，MySQL必须获取到多表关联的结果后才可以对这些记录进行排序处理

3.2、示例

##sbtest3作为驱动表，通过k字段的索引进行过滤找到满足where条件的记录
##由于order by字段为被驱动表的c字段，所以必须将获取到两表join的结果集，然后进行排序
##在执行计划中，我们可以看到“Using temporary; Using filesort”表示使用临时表进行了排序处理

root@mysql57 14:00:  [db2]> explain select * from sbtest4 t4 join sbtest3 t3 on t4.id=t3.id where t3.k<1000 order by t4.k limit 5;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range  | PRIMARY,k_3   | k_3     | 4       | NULL      |    1 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | t4    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db2.t3.id |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

3.3、对于使用临时表排序的优化

对于使用临时表进行排序的查询其资源消耗是以上说到的三种排序方式下资源消耗最大的一种排序方式。在这种模式下，优先考虑排序字段是否可以等价替换为驱动表字段，将其转换为只对驱动表进行排序；若以上手段无效，我们只能通过where过滤条件有效利用索引，通过索引过滤尽量减少SQL查询扫描数据量；select只查询需要的字段，避免select *，尽量减少磁盘临时表的使用。

四、三种排序方式对比

4.1 利用索引进行排序

对于order by ... limit N的排序查询，MySQL会优先权衡是否可以通过优先级队列排序，在内存中完成排序。

4.2 filesort排序

MySQL优化器会优先选择通过索引进行排序查询，若SQL无有效索引可利用，一般会优先根据where条件进行索引过滤，将需要满足过滤条件的记录放在sort buffer中进行排序处理。若需要排序的记录较少，sort_buffer_size的大小即可以满足，此时的排序处理是相对比较快的；若需要排序的记录较大或者有textblob这种大字段，sort_buffer_size大小无法满足一次性对这些记录进行排序，那么MySQL会将需要排序的记录切分为多块儿，每块儿通过sort buffer进行排序后，将结果集转储至磁盘临时表，最终将这些排好序的记录进行合并返回，这种排序方式也较多“多路并归排序”。

4.3 关于sort_buffer_size

对于多路并归的排序方式，理论上只要我们的sort_buffer_size足够大，就可以避免使用到磁盘临时表，但是若该参数是基于会话级别的，若设置不合理极有可能占用过多内存，导致OOM。

4.4 排序相关参数以及具体含义

mysql>show global status like 'sort%';
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| Sort_merge_passes       | 279044          |   //多路并归方式处理的合并次数
| Sort_range              | 33816597        |   //通过索引范围扫描检索的结果进行排序的次数
| Sort_rows               | 7349842715      |   //目前为止已排序的全部记录数
| Sort_scan               | 148047752       |   //通过全表扫描检索的结果进行排序的次数
+-------------------------+-----------------+
返回行数：[4]，耗时：8 ms.

五、三种排序扫描算法

5.1 两次扫描算法

通过两次扫描算法的基本处理流程：

1、排序时，只将排序列和主键值放入sort buffer中进行排序处理，此步骤为第一次扫描，顺序IO；

2、若sort buffer可以一次性存储所有需要排序字段，则直接在sort buffer中进行排序；

3、若sort_buffer_size无法满足一次性存储全部的排序字段，则会将每次读取到sort buffer中的排序记录固化到磁盘，多路归并排序算法，保证临时文件中记录是有序的。

4、根据排好序的记录通过主键回表查询，读取需要的表数据。由于此时是根须排序字段进行排序的，通过主键回表查询会产生大量的随机IO，所以MySQL会将这些记录放至缓冲区按照主键进行排序，缓冲区大小由read_rnd_buffer_size控制，最终通过排好序的主键进行回表扫描查询，此为第二次扫描。

5、一般需要排序记录较大超过max_length_for_sort_data设置值或者查询select中包含BLOB或者TEXT类型字段的情况下会使用该算法进行排序。

5.2 一次扫描算法

一次扫描算法的基本处理流程：

1、排序时，将查询的所有列（排序列以及非排序列）全部放入sort buffer进行排序，此为一次扫描；

2、若sort_buffer_size不够大，需要将每次排好序的记录固化到磁盘；

3、按照排好序的记录直接输出结果；

4、该算法下，只需要扫描一次表数据，避免了回表查询。只有当需要排序的记录小于max_length_for_sort_data定义参数大小时，MySQL才会优先使用一次扫描算法。

5.3 优先队列排序

优先队列排序也成为堆排序，主要是针对order by ... limit M,N的优化。虽然该排序算法需要扫描所有的记录，但是对于sort_buffer_size来讲该排序算法下仅仅需要M+N个元组的空间即可进行排序，避免了sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。