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

​ 今天我们来看一下 MySQL 中 “ order by ” 是怎么工作的。 更好的阅读体验，<a href="https://www.yinkai.cc/post/3050ed4309a12c35515cfa2b317aea3c">order by 是怎么工作的</a> ------ ​ 我们以一个实际的例子，来探讨这个问题： ​ 假设我们的表是这样定义的： ```sql 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 语句是这样写的： ```sql select city,name,age from t where city='杭州' order by name limit 1000 ; ``` ​ 这个语句看上去逻辑很清楚，但你了解它的执行流程吗？下面我们就一起来聊聊这个语句是怎么执行的。 # 全字段排序 ​ 我们都知道，如果一个查询语句不走索引，就会全表扫描，为了避免全表扫描，我们在创建表的时候为 city 字段建立索引了，也可以使用下面的命令重新建立： ```sql 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，排序就在内存中完成。但如果排序数据量太大，内存放不下，则需要利用磁盘临时文件辅助排序。 ​ 你可以使用下面的方法，来确定要给排序语句是否使用了临时文件： ```sql /* 打开了 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 采用另外一种算法。 ```sql 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 存不下几行记录，因此需要更多的文件进行存储，文件数量越多，排序的效率就会越差。 > > 为了避免上述的麻烦，我们在使用查询语句时，尽量走索引去查询，以减少不必要的排序开销和内存开销。