order by执行过程

目录

全字段排序

排序执行流程？

如何确定排序语句是否使用了临时文件？

 rowid 排序

排序执行流程？

全字段排序 vs rowid排序

优化1：需排序字段本身有序

优化2：覆盖索引

---

平时在开发过程中，列表排序使用的挺多的。

例如：要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 100 个人的姓名、年龄。

表结构如下：

sql实现： 

select city, name, age from myuser where city='杭州' order by name limit 100;

数据情况：

explain执行情况

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

全字段排序

排序执行流程？

1、初始化sort_buffer，确定放入select字段（name、city、age）

2、从city的普通索引树找到一个满足city = '杭州' 的主键ID

3、根据ID 回表主键索引树，拿到name、city、age三个字段的值，存入sort_buffer中

4、重复2、3步骤，直到city值不满足查询条件为止

5、对sort_buffer中的数据按照字段name排序

6、按照排序结果取前1000行返回客户端

其中，步骤5排序，根据sort_buffer_size与当前排序所需要的内存的关系，来决定是在内存中完成，还是需要外部文件排序。

排序需要的内存 < sort_buffer_size，在内存中完成；反之，就需要利用磁盘临时文件辅助排序

如何确定排序语句是否使用了临时文件？

执行下方sql

/* 打开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 输出，主要关注filesort_summary的内容*/ 
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;

mysql> SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
*************************** 1. row ***************************
                            QUERY: select city, name,age from myuser where city='杭州' order by name limit 100
                            TRACE: {
  "steps": [
    ...// 省略
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

摘出filesort_summary

select @b-@a 的返回结果是 221，表示整个执行过程只扫描了 221 - 1 行。 (备注：因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1)

 

 rowid 排序

 如果排序后需要返回的字段太多，那么sort_buffer里能同时放下的行数很少，就要分成很多个临时文件，排序的性能会很差。

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度（也就是city、name、age总长36）的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

排序执行流程？

1、初始化sort_buffer，确定放入需要排序字段与ID（name、id）id

2、从city的普通索引树找到一个满足city = '杭州' 的主键ID

3、根据ID 回表主键索引树，拿到name、id两个字段的值，存入sort_buffer中

4、重复2、3步骤，直到city值不满足查询条件为止

5、对sort_buffer中的数据按照字段name排序

6、遍历排序结果，取前100行，并根据ID 回表主键索引树，取出city、name、age三个字段返回客户端（不用存储到sort_buffer了）

全字段排序 vs rowid排序

如果排序内存太小，mysql认为会影响排序效率，就会使用rowid排序算法，优点：一次可以排序更多行；缺点：最后结果集需要再次回表

如果内存足够大，mysql 会优先选择全字段排序，优点：将需要的字段都放入sort_buffer中，不需要二次回表；缺点：对于rowid来说，需要的临时文件较多

优化1：需排序字段本身有序

如果本身排序字段就有索引？执行sql给name添加索引

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

再看下explain结果

 extra中没有using filesort，也就是说不需要排序了

并且由于name本身有序，只需要根据索引树找到前100条数据就完成了，所以只需要扫描100次

优化2：覆盖索引

使用覆盖索引不需要回表，提高查询效率。

执行sql，添加索引

alter table myuser add index city_user_age(city, name, age);

再看下explain结果

 可见，使用到了覆盖索引

备注：因为测试数据量少，对于优化1与优化2的执行时间没有明显的差距。