《MySQL45讲》读书笔记(十二)：order by

发表于 | 分类于
字数统计: 1.2k

此文为极客时间：MySQL实战45讲的16节order by相关内容的笔记

一、order by 的执行流程

1.全字段排序

假如此时有这么一张表：

1
2
3
4
5
6
7
8
9
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;

现在要按姓名排序查询杭州1000个人的信息

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

这条 sql 的执行流程如下：

  1. 在内存中初始化 sort_buffer，放入 city，name，age 三个字段
  2. 从 city 索引树找到 city = '杭州' 叶子节点存放的主键 id
  3. 回表根据主键 id 找到数据，取出全部字段放入 sort_buffer
  4. 重复上述过程，直到全部符合条件的数据都放入内存
  5. 在 sort_buffer 对数据按 name 进行排序
  6. 按照排序结果取前 1000 行返回

因为先查了全部需要字段才排序，我们把这个叫做全字段排序。

不难看出，跟 join 有点像，排序也专门在内存里开辟了一块空间 sort_buffer。其中，sort_buffer_size参数的大小决定了 sort_buffer 的大小，如果 sort_buffer 放不下全部的字段，就必须在磁盘中创建临时文件辅助排序，这个排序使用的是归并排序，即先让分片变得有序，最后再总体进行排序。sort_buffer 越小，就会让临时文件越多。

2.rowid 排序

全字段排序的问题在于，如果需要查询返回的数据行数过多，那么就会导致少量的行数也会占满 sort_buffer 。

为此我们可以调整 max_length_for_sort_data 参数，即当单行的长度超过这个值，就会只在 sort_buffer 中放入主键 id 和需要排序的字段，其他的字段等到排完序再回表查询。

这个算法的执行流程如下：

  1. 在内存中初始化 sort_buffer，只放入 id，name 两个字段
  2. 从 city 索引树找到 city = '杭州' 叶子节点存放的主键 id
  3. 回表根据主键 id 找到数据，只取出 id 和 name 字段放入 sort_buffer
  4. 重复上述过程，直到全部符合条件的数据都放入内存
  5. 在 sort_buffer 对数据按 name 进行排序
  6. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回。

因为排序的时候只拿需要排序的字段，我们可以把这个叫做 rowid 排序

二、优化

我们可以看到，rowid 排序只担心单行长度过大导致 sort_buffer 一次只能放入少量数据的时候使用，因为 rowid 排序完以后还需要回表进行一次查询，会造成磁盘读，因此不会是一个优先的选择。

这也是 mysql 优化查询过程中的原则，即能在内存里处理就尽量在内存里处理，避免回表。

排序是一个消耗颇大的操作，但是我们可以借助索引结构的有序性避免一些额外的排序过程。同样是上面的例子，我们建一个（city，name）联合索引，那么就可以保证在遍历过程中，只要 city=‘杭州’，那么 name 就一定是有序的。

这样一来，查询流程就是：

  • 从联合索引找到第一条city='杭州'的数据对应的主键
  • 回表找到city，name，age三个数据加入数据集
  • 重复上述过程直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束

可以看到，由于索引已经确保的数据的有序性，索引就不需要加入内存中的临时表进行排序的过程了。

如果只查询 city 和 name，或者为联合索引再加上 age 字段，就能实现索引覆盖，甚至不需要回表。不过考虑到维护索引的代价，这个就需要根据业务取舍了。

三、总结

mysql 的 order by 分全字段排序和 rowid 排序。

全字段排序需要先查出数据放入 sort_buffer ，然后在内存中排序后返回。如果数据超过了sort_buffer_size参数，就会在磁盘生成临时文件，辅助进行归并排序。

如果单行长度超过max_length_for_sort_data 参数，就会转为 rowid 排序。即只取 id 和排序字段放入内存，排序完后回表取出对应的数据。

通过索引结构天然有序，可以借助合适的索引避免额外的排序过程。如果实现了索引覆盖还可以避免回表。

0%