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

“order by” 是怎么工作的

1. 首先创建一个表

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;

全字段排序

1. 在 city 字段上创建索引，然后执行下面语句

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

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

• 初始化 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 行返回给客户端

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

/* 打开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 版本好像没看着）
  

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

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

rowid 排序

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

SET max_length_for_sort_data = 16;

3. 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 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的

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

联合索引与索引覆盖

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

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

2. 查询过程就变成下面这样
   

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

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

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

5. 执行流程将会如下
   

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