MySQL中order by 排序必知

在开发过程时，我们经常会遇到 order by 排序操作，那么你知道什么时候 MySQL 才会进行 排序 操作，什么时候不需要时间排序操作？，下面我们就从一个很小的例子中了解一下排序场景。

表结构如下：

CREATE TABLE t (

id int(11) unsigned NOT NULL AUTO_INCREMENT,

city varchar(16) NOT NULL,

name varchar(16) NOT NULL,

age int(11) NOT NULL,

addr varchar(128) DEFAULT NULL,

PRIMARY KEY ( id ),

KEY city ( city ) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

这里只有一个索引，我们执行一条 SQL 语句：

SELECT city, name  FROM WHERE city=’杭州’ ORDER BY name LIMIT 1000 ;

通过Explain命令查看执行情况

发现Extra字段里有”Using filesort”，说明使用了排序，而排序必用到了sort_buffer, 这是由数据库为了专门进行排序操作而分配的一块内存。

下面我们专门来看一下这条语句的执行流程是如何的？在此以前我们需要了解一个概念，就是索引的特性是有序的，系统搜索时，一旦找到最后一条满足条件的记录后就立即停止，后面的记录则不再进行扫描，这样就可以用来解决全表扫描的性能问题。

这里执行过程大概如下：

1、从city索引树中第一条记录开始扫描，找到第一条满足条件的记录，获取到主键ID（每个索引都包含主键ID）。如果第一条记录的city不是”杭州”的话，则继续下一条。

2、根据主键ID进行回表，将 city, name 读取出来放在 sort_buffer

3、继续下一条并重复1、2步骤

4、直到所有满足条件的记录都放到了sort_buffer后，根据name进行排序

5、读取前1000条记录，并返回给客户端

可以看到我们这里使用到了在sort_buffer中排序操作。

我们一般将这个排序过程称为 “全字段” 排序。

下面我们修改一个city索引，执行SQL语句

alter table t drop index city;

alter table t add index ( city , name );

这里我们将city索引包含了city和name两个字段。现在我们再用Explain查看一下执行结果，发现Extra字段变成了 “Using where; Using index”，原来的”Using filesort”消失了，多了一个 “Using index”。说明没有了排序操作，并且使用到了索引，这里使用的是覆盖索引。索引中已经包含了我们所需要的city和name字段，且索引都是已经排序过的，正好符合了我们的要求，即不需要进行回表操作，也不需要用到sort_buffer排序操作了。所以这里不需要用到排序操作。

执行过程如下：

1、搜索city索引树，找到第一条记录，直接返回索引中的city和name字段

2、继续下一条,重复上面的步骤

3、找到1000条记录的时候立即停止，直接返回给客户端就可以了（丢失后面的记录，不管它满足不满足，反正要的数据已经够了）

下面我们再来看一下另一个SQL语句是否会用到索引呢？

SELECT city,name FROM t WHERE city IN (‘杭州’, ‘苏州’) ORDER BY name LIMIT 1000;

这里仍是按name进行排序，但城市变成了2个，根据索引的排序特点很明显这条语句会用到sort buffer排序。由于我们索引树city是按 “一个city+name” 来排序的，虽然可以在索引权中找到所有两个城市的所有记录(city、name)，但无法保证最终name字段是有序的，所以要想取到排序后的两个城市的结果就需要将两个城市所有记录都存储到sort buffer中，然后再进行排序操作，最后返回给客户端。

执行过程如下：

1、搜索city 索引权，找到满足条件的记录(city为杭州和苏州），获取city和name字段

2、继续下一条，重复上面的步骤

3、直到所有的记录都放在了sort buffer中，根据name进行排序，返回给客户端

最后：

这里需要提醒一下大家要注意排序参数 sort_buffer_size 大小的设置，如果设置过小，而排序时系统读取的字段内容过多的话，可能需要借助临时磁盘文件排序（系统会根据排序数据量大小分成多个文件排序，然后使用并归 排序算法 再次排序，排序效率会慢很多，所以避免使用磁盘相关的交互）。如果设置的大小大于排序的内容的话，则直接在sort buffer内存就排序好了，内存效率要比磁盘高的太多了。

另分析SQL执行结果效率除了Explain以后，可以使用

/* 查看 OPTIMIZER_TRACE 输出 */

SELECT * FROM information_schema . OPTIMIZER_TRACE \G

可以查看是否使用了临时文件，其中的number_of_tmp_files表示使用到了多少个临时文件，如果表示0则表示未使用到临时文件，如果大于0，如8的话，则表示排序的时候将分成8个文件分别进行分组排序，最后再使用归并算法合到一起，最终返回排序的结果，返回给客户端。