【MySQL】谈谈order by

在开发时，肯定会遇到过需要字段排序显示结果的需求，以一张市民表为例：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `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`)
) ENGINE=InnoDB;

我需要查询城市为“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。

select city,name,age from t where city='杭州' order by name limit 1000  ;

它是怎么执行的呢？

全字段排序

为了避免全表扫描，我们需要在city字段加上索引。

然后使用explain分析sql语句，可以发现在Extra中有：Using index condition; Using filesort

字段Using filesort表示需要排序，MySQL会给每一个线程分配一块内存用于排序，称为sort_buffer。

它的执行流程如下：

初始化sort_buffer，确定放入name、city、age三个字段。
从索引city中找到第一个满足city='杭州'条件的主键id
到主键id索引中取出整行，取所需字段存入sort_buffer中
从索引city继续往下取，直到不满足条件为止
对sort_buffer中数据按照字段给name做排序
取排序结果前1000行返回给客户端

这个排序过程称为全字段排序。

看回高亮处，它是怎么排序的呢？

可能在内存中完成，也可能需要外部排序，这取决于排序所需内存和参数sort_buffer_size

下面记录一下判断方法。

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
 
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
 
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

可以查看OPTIMIZER_TRACE的结果确认，可以看到字段number_of_tmp_files 使用临时文件的数量。当内存放不下时，需要使用外部排序，而外部排序一般使用归并算法。可以理解为MySQL把需要排序的数据分成x分，每一份单独排序后存在这些临时文件中，最后再把这x份有序文件合并成一个大文件。

如果sort_buffer_size超过需要排序的数据量大小，那就不用临时文件，直接在内存中排序。

还有一些参数可以解释一下：

examined_rows=4000，表示参与排序的行数是4000

sort_mode的packed_additional_fields 表示排序过程对字符串做了紧凑处理，即使name字段的定义是varchar(16)，排序过程还是会按照实际长度分配空间。

最后一个查询语句 select @b-@a 的返回结果是 4000，表示整个执行过程只扫描了 4000 行

查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。所以可以把它改成MyISAM。

rowid排序

上面的算法有个问题，如果查询要返回的字段很多，那sort_buffer里面要放的字段太多，会分成多个临时表，排序性能很差。

如果单行很大，这个方法效率不高。

可以修改一个参数，让MySQL执行另一种算法：

SET max_length_for_sort_data = 16;

新算法放入sort_buffer的字段少了，只有要排序的列（即name）和主键id

那剩余需要的值，就不能直接返回，而是在最后排序出结果后，回表取出所需字段返回。

实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

这样可以使排序需要的临时文件变小了，排序更快了。

哪个更好？

如果内存够，就要多利用内存，尽量减少磁盘访问。

如果MySQL内存足够大，会优先选择全字段排序，把所有的字段放到sort_buffer中，这样就能返回查询结果了；不然就会采用rowid，可以排序更多行，但是需要回表取数据。

但想想，为什么它还要再排序一次呢，可不可以使用索引的有序性？

可以创建(city, name)的联合索引，当查到满足条件的city时，name同时也是有序的

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

゛魚生