Smarticen Notes

order by如何工作

8 min read

开发应用地时候，一定会碰到需要根据指定的字段排序来显示结果的需求。现在就需要了解这个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;

在这个表中在city字段上建立了索引。现在查询城市是杭州的所有人的名字，并按照姓名排序返回前1000个人的姓名、年龄。

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

全字段排序

使用explain来分析sql的执行情况。

可以看到选择了city索引，不过我们需要关注的是Extra这个字段中的”Using filesort”，表示需要进行排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

这条语句的执行流程如下所示：

  1. 初始化sort_buffer，确定放入name, city, age这三个字段。
  2. 从索引city找到第一个满足city=‘杭州’条件的主键id，也就是图中的ID_X；
  3. 到主键id索引取出整行，取name, city, age三个字段的值，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id
  5. 重复3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y;
  6. 对sort_buffer中的数据按照字段name做快速查询。
  7. 按照排序结果返回前1000行给客户端

按照name进行排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size是MySQL为排序开辟的内存的大小，如果要排序的数据量小于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中查询是否使用了临时文件。 number_of_tmp_files的数量为多少，就表明使用了多少临时文件。这里表示使用了12个临时文件。Mysql的外部排序一般使用归并排序算法，将每个临时文件的内容单独排序后再合并成一个大的有序文件。

需要排序的数据量超过了sort_buffer_size，那么就需要使用临时文件进行外部排序，否则再内存中就可以完成了。

rowid排序

如果我们查询除了很多的字段，然后按照其中某个字段进行排序，sort_buffer将所有的字段都存入sort_buffer的话，那么其能够存下的行数很少，要分成很多个临时文件，排序的性能就会很差。

所以如果单行很大的话，全字段排序效率不够好。对于这种情况，可以修改一个参数，让MySQL采用另外一种算法：

SET max_length_for_sort_data = 16;

这个参数意思就是，如果单行的长度超过设置的这个值，MySQL就认为单行太大，要换一个算法，新的算法放入sort_buffer中的字段，只有要排序的列和主键id。

不过这样，排序的结果因为缺少字段就不能够直接返回了，整个执行流程变更如下：

  1. 初始化sort_buffer，确定放入两个字段，即name, id。
  2. 从索引city找到第一个满足city=‘杭州’条件的主键id，也就是图中的ID_X。
  3. 到主键id索引取出整行，取name, id这两个字段，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到不满足city=‘杭州’条件为止，也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name进行排序
  7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city, name和age三个字段返回给客户端。不过需要注意，这里回到主键索引查询的结果每查到一条就可以返回一条不需要将将结果暂存到内存中统一返回，所以不需要额外耗费内存存储结果。

全字段排序vs Rowid排序

MySQL担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要回到原表取数据。

但是MySQL认为内存足够大，会优先选择全字段排序，把需要排序的字段都放到sort_buffer中，这样排序就可以直接返回里面的结果，不需要再回到原表取数据。

这体现了MySQL的一个设计思想：如果内存足够，就要多利用内存，尽量减少磁盘访问。

rowid排序会要求徽标多造成磁盘读，因此不会被优先选择。

利用索引优化排序

其实order by也并不是要求一定进行排序，如果查询出来的结果，天然就是按照排序字段排列的话，那么就无需排序了。

比如在这个例子中，我们可以在市民表上创建一个city和name的联合索引，对应的sql语句是：

alter table t add index city_user(city, name);

我们指定了city=‘杭州’，那么对于符合这个条件的记录来说，他们就是按照name字段递增的顺序进行排列的。

现在执行前面的查询语句，使用explain进行分析就可以直到，这个过程中既不需要临时表，也不需要排序。

由于我们查询的是city,name,age，那么还可以利用覆盖索引进一步优化查询速度，那就会将(city,name,age)建立一个联合索引。

Graph View

Backlinks