关键词
order by、排序、排序优化
0.引子
在开发的时候，你经常会遇到需要用指定字段排序来显示结果的需求。举个例子，假设在一个市民表中，你要查找在“杭州”的人，并按照姓名返回前 1000 个人的姓名、年龄。
假设这个表的定义如下：
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;
你的 SQL 语句可以这么写：
select city,name,age from t where city='杭州' order by name limit 1000 ;
在这里，我们就说一说这个语句的执行过程，以及一些参数对它的影响。
1.全字段排序
首先，为了加快查找，我们可以在 city 字段上添加索引，
然后使用 explain 查看这个语句的执行情况：
16-全字段-ex.png
Extra 中的 Using filesort 表示使用了排序。MySQL 会为每个线程分配一块内存用于排序，称为 sort_buffer。
假设 city 索引示意图如下：
16-全字段-city索引.png
以上图的数据为例，这个语句的执行流程如下：
- 初始化 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 行返回给客户端。
这个过程如下：
16-全字段-排序过程.jpg
在图中，“按 name 排序”这个动作可能在内存中完成，也可能借助磁盘进行外部排序。这主要看 sort_buffer 的大小能否完全装入要排序的数据。
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;
我们是使用 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看查看是否适用了临时文件：
16-全字段-分析.png
这参数表示的是，在排序过程中使用到的临时文件的数目。外部排序一般使用归并排序（内部使用快排），对于参数 12 ，你可这么理解：MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
如果内存足够大，number_of_tmp_files 就是 0。
除此之外，还有几个参数：
- examined_rows=4000 ，表示参与排序（满足 city="杭州" ）的记录有 4000 行。
- sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。
- select @b-@a 的返回结果是 4000，表示整个执行过程只扫描了 4000 行。
2.rowid排序
在上面排序过程中，一条数据的 city、name、age 都被放入了内存。但是这是一种比较占内存的方法，因为我们排序的最主要依据是 name，对于排序的过程来说，city、age 是不需要的。
依照这个思想，我们可以使用 rowid 排序。
首先，看一下一个参数：
SET max_length_for_sort_data = 16;
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
在上面的例子中， city、name、age 的总长度为 36，如果我们将 max_length_for_sort_data 设置为 16，排序的过程会发生如下改变：
- 初始化 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 三个字段返回给客户端。
16-rowid-排序过程.jpg
和全字段排序对比，你会发现，worid 多访问了一次表 t 的主键索引。
分析一下这个流程的执行：
16-rowid-分析.png
你会发现：
- examined_rows 的值还是 4000，表示用于排序的数据是 4000 行。
- 但是 select @b-@a 这个语句的值变成 5000 了，因为这时候除了排序过程外，在排序完成后，还要根据 id 去原表取值。由于语句是 limit 1000，因此会多读 1000 行。
- sort_mode 变成了 <sort_key, rowid>，表示参与排序的只有 name 和 id 这两个字段。
- number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。
3.全字段 VS rowid
对比两个执行流程，我们有如下结论：
- 如果你的内存比较小，可以使用 rowid，在有限的内存中尽可能多地排序，并减少与磁盘的交换。但是要进行回表。
- 如果你的内存足够大，直接使用全字段排序，这样不需要回表。
- 这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
- 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。即默认使用全字段排序。
其实，对于 order by 来说，如果数据本身就是有序的，则不需要对数据进行排序了。
4.使用索引避免排序
你是否还记得联合索引的构建方式呢？下面我们构建一个 city 和 name 的联合索引：
alter table t add index city_user(city, name);
这个索引的示意图如下：
16-联合索引示意图.png
你会发现，如果 city 的值是 “杭州”，name 的值就已经有序了。在这种情况下，查询流程就会变成如下的情况：
- 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
- 从索引 (city,name) 取下一个记录主键 id；
- 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
16-联合索引-执行流程.jpg
你可以使用 explain 分析一下：
16-联合索引-分析.png
你会发现：Extra 字段中没有 Using filesort 了，也就是不需要排序了。
使用联合索引是不是非常好？是的，但是还可以更好：
还记得之前讲过的索引优化方法吗？其中有一个方法是，使用覆盖索引避免回表。
这里我们可以再稍微复习一下。覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
针对这个查询，我们可以创建一个 city、name 和 age 的联合索引，对应的 SQL 语句就是：
alter table t add index city_user_age(city, name, age);
随后，查询语句的执行流程就变成了：
- 从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
- 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
- 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
16-覆盖索引-流程.jpg
看一下语句分析：
16-覆盖索引-ex.png
可以看到，Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。
当然，你要考虑这个索引的维护代价。
小结
我们介绍了几种 order by 的实现流程，以及他们的性能差别：
- 全字段排序：默认，占用内存多。
- rowid：可以减少外部排序的次数，内存紧张时可以使用，但是会多一次回表。
- 联合索引：索引已经有序，省去了排序过程。
- 覆盖索引：省去了回表，可以直接返回数据。但是你要注意这个索引的维护代价。
上期答案
上期的问题是，当 MySQL 去更新一行，但是要修改的值跟原来的值是相同的，这时候 MySQL 会真的去执行一次修改吗？还是看到值相同就直接返回呢？
- 第一个选项是，MySQL 读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。
假设，当前表 t 里的值是 (1,2)。
16-上期问答1.png
session B 的 update 语句被 blocked 了，加锁这个动作是 InnoDB 才能做的，所以排除选项 1。
- 第二个选项是，MySQL 调用了 InnoDB 引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。
这里我用一个可见性实验来确认,假设当前表里的值是 (1,2)。
16-上期问答2.png
session A 的第二个 select 语句是一致性读（快照读)，它是不能看见 session B 的更新的。现在它返回的是 (1,3)，表示它看见了某个新的版本，这个版本只能是 session A 自己的 update 语句做更新的时候生成。(具体知识可以看第 8 章)
- 所以，我们上期思考题的答案应该是选项 3，即：InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作，该加锁的加锁，该更新的更新。
然后你会说，MySQL 怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费 InnoDB 操作，多去更新一次了？
其实 MySQL 是确认了的。只是在这个语句里面，MySQL 认为读出来的值，只有一个确定的 (id=1), 而要写的是 (a=3)，只从这两个信息是看不出来“不需要修改”的。
作为验证，你可以看一下下面这个例子。
16-上期问答3.png
本期思考
假设你的表里面已经有了 city_name(city, name) 这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前 100 条记录。如果 SQL 查询语句是这么写的 ：
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;
- 那么，这个语句执行的时候会有排序过程吗，为什么？
- 如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？
- 进一步地，如果有分页需求，要显示第 101 页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？
这节的内容就到这里，愿你能够有序生活。
注：本文章的主要内容来自我对极客时间app的《MySQL实战45讲》专栏的总结，我使用了大量的原文、代码和截图，如果想要了解具体内容，可以前往极客时间
网友评论