你知道 MySQL 中的 order by 是怎么工作的吗？

技术
1

今天我们来看一下 MySQL 中 “ order by ” 是怎么工作的。 更好的阅读体验，https://www.yinkai.cc/post/3050ed4309a12c35515cfa2b317aea3c">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;

我们的 SQL 语句是这样写的：

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

这个语句看上去逻辑很清楚，但你了解它的执行流程吗？下面我们就一起来聊聊这个语句是怎么执行的。

全字段排序

我们都知道，如果一个查询语句不走索引，就会全表扫描，为了避免全表扫描，我们在创建表的时候为 city 字段建立索引了，也可以使用下面的命令重新建立：

CREATE INDEX idx_city ON t (city);

建立完索引后，我们使用 explain 命令来看看这个语句的执行情况：

外部图片

下面我们对输出的字段进行解释：

:::details 感兴趣的可以点开查看

  1. **id：**每个 SELECT 语句都会 分配一个唯一的标识符，在复杂查询中，可以通过这个字段来区分各个子查询。
  2. **select_type：**描述了查询的类型，常见的有：
    • SIMPLE：简单查询，不包含子查询或联合查询
    • PRIMARY：复杂查询中的最外层查询
    • SUBQUERY：子查询。
    • 其他值表示更复杂的查询类型。
  3. table：表示查询涉及的表名。
  4. **partitions：**表示查询涉及的分区信息
  5. **type：**表示链接类型，有：
    • system：表只有一行
    • const：使用主键或唯一索引进行等值查询，最多返回一行
    • eq_ref：类似于 const，但用于连接的是唯一索引
    • ref：非唯一索引的等值查询
    • range：用于范围查询
    • index：完全扫描索引来匹配查询
    • ALL：完全表扫描。
  6. **possible_keys：**列出可能用于查询的索引，但并不一定会被选择
  7. **key：**实际被查询优化器选择的索引
  8. **key_len：**表示索引中使用的字节数
  9. **ref：**显示索引的哪一列被用于查找
    • 当值为 const 时，表示查询是通过常量条件来使用索引进行查找的
  10. **rows：**表示估计需要检查的行数
  11. **filtered：**表示在表中满足条件的行所占的百分比
  12. **Extra：**提供关于查询执行的额外信息，如使用索引的条件，是否使用文件排序

:::

我们需要强调是就是 Extra 字段中的 “Using filesort”，表示需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

外部图片

通常情况下，这个语句的执行流程如下：

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

我们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示，

外部图片

上图中，按 “name” 排序这个动作，可能在内存中完成，也有可能需要使用外部排序，这取决于所需的内存和参数 sort_buffer_size。

sort_buffer_size 是 MySQL 为排序开辟的内存（sort_buffer）的大小，如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则需要利用磁盘临时文件辅助排序。

你可以使用下面的方法，来确定要给排序语句是否使用了临时文件：

/* 打开了 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 差值,该差值表示在执行查询期间从 InnoDB 表中读取的行数。 */

select @b-@a;

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，我们可以从 num_initial_chunks_spilled_to_disk 可以得知，排序可以完全在内存中完成，不需要使用临时文件；如果大于零，表示可能使用了临时文件。

外部图片

如果看到了 number_of_tmp_files 字段，并且后面的数字不为 0，则表示排序过程中使用的临时文件数。由于内存放不下，就需要使用外部排序，通常外部排序使用归并排序算法。可以理解为，MySQL 将需要排序的数据分为 12 份，每一份单独排序后存在这些临时文件中，然后把这 12 个有序文件再合并成一个有序的大文件。

外部图片

我们再挑两个比较重要的记录来介绍一下。

一个是 num_examined_rows = 187，表示参与排序的行数是 187 行。

sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

最后一个查询语句 select @b-@a 的返回结果应该是 187，表示整个过程中在表中读取的行数。

:::warning

根据 internal_tmp_disk_storage_engine 这个系统变量的不同，可能会对最后的结果造成影响。

internal_tmp_disk_storage_engine 用于指定在执行排序和临时表创建时使用的存储引擎。这个变量决定了 MySQL 在执行排序等操作时，是否将临时数据存储在磁盘上。

因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，如果使用 InnoDB 引擎的话，把数据从临时表里取出来的时候，会让 Innodb_rows_read 的值 + 1。

为了避免影响，我们可以把 internal_tmp_disk_storage_engine 修改为 MyISAM。

:::

rowid 排序

在上面这个算法过程里面，只会对原表的数据读一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。这样就会存在一个问题：如果查询要返回的字段很多，就会导致 sort_buffer 里面要存放的字段数很多，这样内存中能存放的行数就会变少，因此就需要分成很多个临时文件，排序的性能就会很差。

所以，单行很大，这个算法的效率就稍差一些。

那 MySQL 在面对这种排序的单行长度太大的情况会怎么做呢？

我们可以人为地修改 max_length_for_sort_data 这个参数，让 MySQL 采用另外一种算法。

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 和 age 字段的值，不能直接返回了，故整个执行流程就变成了下面的样子：

  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city= '杭州' 条件的主键 id，即图中的 ID.x；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city= '杭州' 条件为止，即图中的 ID.Y；
  6. 对 sort_buffer 中的数据按照字段 name 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

这个执行的流程示意图如下，我们可以称它为 rowid 排序：

外部图片

我们对比全字段排序的流程图就会发现，rowid 排序多访问了一次表 t 的主键索引，即步骤 7。

:::danger

最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

:::

我们使用同样的方法，对 rowid 排序执行一下 select @b-@a，看看结果是有什么不同。

外部图片

可以看到 num_examined_rows 的值还是 187，但是 @b-@a 的结果却变成了 375，这是因为对于 sort_buffer 中排序后的 187 条数据，还需要回到原表中去获取我们需要的其他记录值，所以最终在表中读取的行数为 187 + 1 + 187 = 375.

小结

对于学习的内容，我们可以总结一下：

全字段排序：

  1. 首先初始化 sort_buffer，确定放入我们需要查询的字段
  2. 根据索引，查询满足过滤条件的主键 ID
  3. 到主键 id 索引取出整行，取出我们需要查询的字段，存入 sort_buffer 中；
  4. 从索引取下一个记录的主键 id；
  5. 重复步骤 3、4 直到下一条记录的值不满足查询条件为止
  6. 对 sort_buffer 中那点数据按照排序字段进行排序；
    • 内存中一般使用快速排序
    • 外部排序一般使用归并排序
  7. 按照排序结果取出对应的记录返回给客户端。

rowid 排序：

  1. 初始化 sort_buffer，确定放入的字段，即主键 id 和排序字段
  2. 根据索引，查询满足过滤条件的主键 ID
  3. 到主键 id 索引取出整行，取出我们需要查询的字段，存入 sort_buffer 中；
  4. 从索引取下一个记录的主键 id；
  5. 重复步骤 3、4 直到下一条记录的值不满足查询条件为止
  6. 对 sort_buffer 中那点数据按照排序字段进行排序；
    • 内存中一般使用快速排序
    • 外部排序一般使用归并排序
  7. 遍历排序结果，取出对应的记录，并按照 id 的值回到原表中取出我们需要查询的所有字段返回给客户端。

全字段排序和 rowid 排序的差异：

  • 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
  • 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

前者需要考虑的是排序效率的影响，后者需要考虑的是磁盘访问效率的影响。

全字段排序也体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

:::warning

我们要明确一点：并不是所有的 order by 语句都需要排序操作。从上面的分析过程，我们可以看到，MySQL 之所以生成临时表，并且在临时表上做排序操作，其原因是原来的数据是无序的

如果能够保证从 city 这个索引取出来的行，天然就是按照 name 递增排序的话，就可以不用再排序了。

感兴趣的友友，可以通过给 name 和 city 字段建立联合索引，再走一遍上面的分析过程，就会非常清晰了，这里由于篇幅有限，就不作过多赘述了。

:::

> 问：MySQL 中 order by 是怎么工作的？ > > 答：首先 MySQL 会根据排序的行数据的长度，来选择不同的排序算法。这个长度是由 max_length_for_sort_data 控制的，如果单行的长度超过这个值，MySQL 就认为单行太大了，将采用 rowid 排序，反之采用全字段排序。 > > 说，“这里需要展开介绍一下这两种算法吗？”。 > > 全字段排序，首先会初始化 sort_buffer，确定需要放入的字段，即我们查询的需要返回的字段。然后根据查询条件，找出符合的主键 id，根据主键 id 到主键索引取出整行记录，然后拿出我们查询的字段放入 sort_buffer 中，查询到不满足查询条件为止。最后对 sort_buffer 中的数据按照排序字段进行排序，将结果返回客户端。 > > rowid 排序 与 全字段排序大致过程差不多，不同的点是：它只会将排序字段和主键 id 放入 sort_buffer 中，在对 sort_buffer 中的数据排序后，再按照主键 id 的值返回到原表中取出我们需要的其他字段的值返回给客户端。 > > rowid 排序会要求回表次数较多，造成磁盘读，因此不会被优先选择。 > > 全字段排序，也会存在一些问题，就是在单行记录过大时，会导致 sort_buffer 存不下几行记录，因此需要更多的文件进行存储，文件数量越多，排序的效率就会越差。 > > 为了避免上述的麻烦，我们在使用查询语句时，尽量走索引去查询，以减少不必要的排序开销和内存开销。

这是一个从 https://studygolang.com/topics/16985 下的原始话题分离的讨论话题