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

oYto · · 41625 次点击 · 开始浏览    置顶

​ 今天我们来看一下 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 命令来看看这个语句的执行情况: ![explain](/images/explain.png) ​ 下面我们对输出的字段进行解释: :::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。 ![city_index.png](/images/city_index.png) ​ 通常情况下,这个语句的执行流程如下: 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 行返回给客户端。 ​ 我们暂且把这个排序过程,称为全字段排序,执行流程的示意图如下所示, ![全字段排序.png](/images/全字段排序.png) ​ 上图中,按 “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 可以得知,排序可以完全在内存中完成,不需要使用临时文件;如果大于零,表示可能使用了临时文件。 ![排序的部分结果.png](/images/排序的部分结果.png) ​ 如果看到了 number_of_tmp_files 字段,并且后面的数字不为 0,则表示排序过程中使用的临时文件数。由于内存放不下,就需要使用外部排序,通常外部排序使用归并排序算法。可以理解为,MySQL 将需要排序的数据分为 12 份,每一份单独排序后存在这些临时文件中,然后把这 12 个有序文件再合并成一个有序的大文件。 ![排序的部分结果2.png](/images/排序的部分结果2.png) ​ 我们再挑两个比较重要的记录来介绍一下。 ​ 一个是 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过程.png](/images/rowid过程.png) ​ 我们对比全字段排序的流程图就会发现,rowid 排序多访问了一次表 t 的主键索引,即步骤 7。 :::danger 最后的“结果集”是一个逻辑概念,实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。 ::: ​ 我们使用同样的方法,对 rowid 排序执行一下 select @b-@a,看看结果是有什么不同。 ![rowid过程2.png](/images/rowid过程2.png) ​ 可以看到 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 存不下几行记录,因此需要更多的文件进行存储,文件数量越多,排序的效率就会越差。 > > 为了避免上述的麻烦,我们在使用查询语句时,尽量走索引去查询,以减少不必要的排序开销和内存开销。

有疑问加站长微信联系(非本文作者)

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

41625 次点击  ∙  1 赞  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传