【MySQL】order by 的排序策略

目录

一、全字段排序

二、磁盘临时文件辅助排序

三、RowId 排序

四、优化 order by

---

在前面的文章中我们分析了 explain 执行计划的各个字段，其中有一个 Extra 字段。

在执行计划中，Extra 字段如有 Using filesort 则表示使用到了排序。

而 MySQL 的排序机制相当复杂，它会根据不同的情况选择最适合的排序策略。

一般来说，MySQL 会尝试利用索引来避免排序操作，但如果必须进行排序，它会尽可能地使用内存中的排序来提高效率。

当数据量过大时，MySQL 会使用磁盘上的临时文件来进行排序。

这些策略的选择取决于多种因素，包括表结构、索引、查询条件、系统资源等。

一、全字段排序

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。

什么时候把字段放进去排序呢，其实是通过索引找到对应的数据，才把数据放进去啦。 

 

整体的执行流程：

1. MySQL 为对应的线程初始化 sort_buffer，放入需要查询的字段；

2. 从索引树找到第一个满足条件的主键 id；

3. 到主键 id 索引树拿到满足条件的这一行数据， 取查询的字段的值，存到sort_buffer；

4. 从索引树拿到下一个记录的主键 id；

5. 重复步骤 3、4 直到不满足条件为止；

6. 前面5步已经查找到了所有满足条件的数据，在 sort_buffer中，将所有数据进行排序；

7. 按照排序结果返回给客户端。

此时就存在一个问题：如果数据太多，内存放不下怎么办？ 

二、磁盘临时文件辅助排序

实际上，sort_buffer 的大小是由一个参数控制的：sort_buffer_size。

如果要排序的数据小于 sort_buffer_size，排序在 sort_buffer 内存中完成，如果要排序的数据大于 sort_buffer_size，则借助磁盘文件来进行排序。

使用了磁盘临时文件，整个排序过程又是怎样的呢？

1. 从主键Id索引树，拿到需要的数据，并放到 sort_buffer 内存块中。当 sort_buffer 快要满时，就对 sort_buffer 中的数据排序，排完后，把数据临时放到磁盘一个小文件中。

2. 继续回到主键 id 索引树取数据，继续放到 sort_buffer 内存中，排序后，也把这些数据写入到磁盘临时小文件中。

3. 继续循环，直到取出所有满足条件的数据。最后把磁盘的临时排好序的小文件，合并成一个有序的大文件。

TIP: 借助磁盘临时小文件排序，实际上使用的是归并排序算法。

 

三、RowId 排序

rowid 排序就是，只把查询 SQL 需要用于排序的字段和主键id，放到 sort_buffer 中。

那怎么确定走的是全字段排序还是 rowid 排序排序呢？

实际上有个参数控制的。这个参数就是 max_length_for_sort_data，它表示 MySQL 用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换 rowid 排序，我们可以通过命令看下这个参数取值。

使用 rowid 排序的话，整个 SQL 执行流程又是怎样的呢？

1. MySQL 为对应的线程初始化 sort_buffer，放入需要排序的字段，以及主键 id；

2. 从索引树找到第一个满足条件的主键 id；

3. 到主键 id 索引树拿到满足条件的这一行数据， 取需要排序的字段和主键 id 的值，存到 sort_buffer；

4. 从索引树拿到下一个记录的主键 id；

5. 重复步骤 3、4 直到不满足条件为止；

6. 前面5步已经查找到了所有满足条件的数据，在 sort_buffer中，将所有数据根据需要排序的字段进行排序；

7. 遍历排序结果，并按照 id 的值回到原表中，取出需要查询的字段返回给客户端。

 

对比一下全字段排序的流程，rowid 排序多了一次回表。 

回表是指在使用非主键索引进行查询时，数据库系统需要根据索引中获取的主键值回到数据表中获取完整行数据的过程。这种操作通常发生在索引不能直接提供查询所需的所有数据时。 

四、优化 order by

那可以如何优化 order by 语句呢？

• 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化 order by 语句。

• 还可以通过调整 max_length_for_sort_data 等参数优化；

可以建立联合索引，这样查询的数据就不需要用到内存排序了，在索引树上就是我们想要的顺序效果。

 

一  叶  知  秋，奥  妙  玄  心