『MySQL 实战 45 讲』16 - “order by” 是怎么工作的

Gavinjou大笨象

已于 2023-05-22 10:12:37 修改

阅读量444

点赞数

分类专栏：『数据库』文章标签： mysql 数据库 sql

于 2023-05-18 01:16:02 首次发布

本文链接：https://blog.csdn.net/zzz805/article/details/130737736

版权

『数据库』专栏收录该内容

19 篇文章 0 订阅

订阅专栏

“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 字段上创建索引，然后执行下面语句

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

通过 explain 结果会出 Extra 字段中，出现 Using filesort，表示需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer
上述语句执行流程如下

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

其中，排序的时候可能会用到外部排序，就需要设置 sort_buffer_size，避免导致 sort_buffer 太小而不得不利用磁盘临时文件来辅助排序
确定排序语句是否使用了临时文件的方法

/* 打开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;

其中，在 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G 的内容中如果出现 number_of_tmp_files，就表明用了临时表的份数（8.0 版本好像没看着）

而 SELECT @b-@a; 代表的是查询前后获取的值的相减，得到的意思是整个过程扫描了多少行

如果发现值和预期值多 1，就需要设置 internal_tmp_disk_storage_engine 属性为 MyISAM（默认 InnoDB），可能是因为查询 OPTIMIZER_TRACE 时，用到了临时表，所以会加 1

rowid 排序

全字段排序会把要返回的字段放到 sort_buffer 中，如果字段太多，就会分成多个临表
当排序单行太大，MySQL 会用另外一种算法，例如更改 max_length_for_sort_data 专门用于控制排序的行数据参数

SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36 > 16，所以 sort_buffer 只会放入 排序的列 和 主键 id

初始化 sort_buffer，确定放入两个字段，即 name 和 id
从索引 city 找到第一个满足 city=‘杭州’ 条件的主键 id，也就是图中的 ID_X
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中
从索引 city 取下一个记录的主键 id
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y
对 sort_buffer 中的数据按照字段 name 进行排序
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端
- 其中 “结果集” 是逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的

如果用刚刚的 确定临时文件的方法，会发现比以前多了一些值，就是因为要 id 去原表取值

联合索引与索引覆盖

对表的字段 city 和 name 的联合索引

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

查询过程就变成下面这样

从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回
从索引 (city,name) 取下一个记录主键 id
重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=‘杭州’ 条件时循环结束

通过 explain 查询，就发现没有出现 Using filesort
如果想更快，就进行索引覆盖吧

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

执行流程将会如下

从索引 (city,name,age) 找到第一个满足 city=‘杭州’ 条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回
从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回
重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=‘杭州’ 条件时循环结束