【MySql专栏】—— 排序order by的过程

在开发过程中，我们经常需要根据某个字段进行排序，在拿到我们需要的结果即，那么当我们在使用order by这个语句的使用，mysql内部都做了那些操作的，这就是这篇文章想要说明的问题，首先我们来创建一个订单表，里面有三个字段主键id，订单id和一个创建时间，创建表的语句如下：

CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` varchar(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `money` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)，
  KEY `orderId` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

模拟一些插入数据

INSERT INTO `order` (`id`, `order_id`, `create_time`, `money`)
VALUES
	(1, '1', '2019-01-01 00:00:00', 20.00),
	(2, '1', '2019-01-02 00:00:00', 25.00),
	(3, '1', '2019-01-03 00:00:00', 34.00),
	(4, '2', '2019-01-02 00:00:00', 54.00),
	(5, '2', '2019-01-20 00:00:00', 33.00),
	(6, '2', '2019-01-10 00:00:00', 22.00),
	(7, '3', '2019-01-25 00:00:00', 12.00),
	(8, '4', '2019-01-22 00:00:00', 5.00),
	(9, '5', '2019-01-24 00:00:00', 67.00),
	(10, '6', '2019-01-28 00:00:00', 11.00);

如果有一个查询是要获取订单为1，并且根据创建时间获取前两条数据，那么sql语句就可以这么写

select id,order_id,create_time from `order` where order_id = '1' order by create_time limit 2;

一、全字段排序

首先我们可以用explain来分析一下这个语句的执行情况：

其中Extra的Using filesort 表示需要进行排序，mysql会给每个线程一个内存用于排序，称为sort_buffer,整个语句的执行流程如下：

1.初始化sort_buffer,确定放入ID，order_id,create_time三个字段，

2.在order_id索引上找到第一满足order_id = '1'的条件的主键id，

3.然后根据主键id进行会回表查询到id，order_id和create_time的值，存入到sort_buffer中，

4.在order_id索引上去下一个值拿到主键id，

5.重复步骤3，4直到找到order_id不等于1为止，

6.对sort_buffer中的结果集按照create_time字段进行快速排序，

7.按照排序结果取出前2条结果返回。

其中步骤6这个排序过程，可能在内存中完成，也可能需要外部排序，这主要取决于排序所需的内存和sort_buffer_size的大小比较，sort_buffer_size就是mysql中为sort_buffer开辟的空间大小，如果排序所需的内存小于sort_buffer_size,就直接在内存中进行排序就可以了，反之，则就需要磁盘临时文件进行外部排序。

二、rowid排序

使用全字段排序，直将需要查询出来的字段全部放入到sort_buffer中，那如果需要取出的字段非常的多，那么一行数据就会占用很多内存，那么我们就需要更多的临时文件来进行外部排序，这样效率就会不太好。

mysql也想到了对这个问题的优化方法，mysql中有一个值，用来判断单行数据是否过大，max_length_for_sort_data 这个值就是用来定义单行数据的大小的，如果单行数据的值大于这个设置的值，就不会使用全字段排序，而是使用rowid排序;

使用rowid排序，只会将需要排序的字段和主键ID放入到sort_buffer当中，不会在放入全部的字段，那么一个查询排序的语句执行过程如下：

1.初始化sort_buffer，放入id和create_time字段，

2.在order_id索引上找到第一满足order_id = '1'的条件的主键id，

3.然后根据主键id进行会回表查询到id和create_time的值，存入到sort_buffer中，

4.在order_id索引上去下一个值拿到主键id，

5.重复步骤3，4直到找到order_id不等于1为止，

6.对sort_buffer中的结果集按照create_time字段进行快速排序，

7.遍历结果集，取出前两个满足条件的id，根据id进行回表查询，得到需要的三个字段，返回给客户端。

对比于全字段排序，不难发现，使用rowid排序，需要多一次回表查询的操作，这个时候需要扫面的行数就是满足条件的3条和需要进行回表查询的2条，总共5条。

三、全字段排序与rowid排序对比

在mysql只有当排序内存sort_buffer过小，影响排序效率，这时mysql才会去使用rowid排序进行排序，因为rowid排序，需要多一次回表查询的操作，增加磁盘访问，因此只要当排序内存空间足够，那么优先选择全字段排序，不需要增加回表操作。

但是不论是使用全字段排序和rowid排序，对于mysql来说，都是比较影响性能的!那么又没有什么更好的方法来优化呢，答案当然是有的。

四、优化排序

我们为什么需要进行排序呢，是因为我们按照条件查询，取出来的结果是无需的，所以我们需要使用sort_buffer对取出的结果集进行排序，但是如果我们取出来的结果集，已经是按照我们需要的条件排序好的，那么是不是就可以不需要排序了呢。那么这么保证我们查询出来的结果是有序的，这个时候就需要用到联合索引了，我们创建一个order_id字段和create_time的联合索引，语句如下：

alter table `order` add index order_time(order_id,create_time);

这个使用我们在执行一下explain语句：

explain select id,order_id,create_time,money from `order` where order_id = '1' order by create_time limit 2;

结果如下：

这个时候我们发现Extra没有了Using filesort，表示这个没有在对结果集进行排序了，那么现在语句的执行流程如下：

1.在联合索引(order_id,create_time)中，找到第一个满足order_id = 1 的条件的主键id，

2.根据主键id到主键索引上取出整行数据的值，然后取出id，order_id,create_time三个字，返回；

3.从联合索引(order_id,create_time)中，找到下一个满足条件的主键id

4.重复步骤2，3，直到查到limit限制的2条数据或者order_id 不等于2为止，循环结束。

那么还可不可以更近一步就行优化呢？答案是可以的，因为我们查询的结果，没有使用覆盖索引，所以需要进行一次回表操作，如果我们直接使用覆盖索引，不需要进行回表操作，可以更好的优化我们的语句，

例如：我们只需要order_id,create_time两个字段，不需要money，那么查询语句就可以这么写：

select order_id,create_time from `order` where order_id = '1' order by create_time limit 2;

这个使用我们用explain查看这个语句：

其中Extra中的Using index表示使用了覆盖索引。