阅读MySQL实战45讲第11天
目录
引言：
基本原理
排序方式
索引排序
文件排序（File Sort）
优化建议
一、全字段排序
二、rowid 排序
如果 MySQL 认为排序的单行长度太大会怎么做呢？
1. 使用磁盘临时表
2. 分阶段排序
3. 产生警告或错误
三、全字段排序 VS rowid 排序
MySQL 排序算法选择策略
MySQL 设计思想
引言：
在 MySQL 里，ORDER BY 子句用于对查询结果进行排序，下面从基本原理、排序方式以及优化方面为你介绍它的工作机制。
基本原理
- 扫描数据：执行 ORDER BY 时，MySQL 首先会根据查询条件从表中扫描出符合条件的数据行。
- 排序操作：将扫描出的数据按照 ORDER BY 子句指定的列和排序规则（升序 ASC 或降序 DESC）进行排序。排序操作可以在内存或磁盘上进行，具体取决于数据量大小和可用内存。
- 返回结果：排序完成后，MySQL 将排序好的数据作为查询结果返回。
排序方式
索引排序
- 原理：若 ORDER BY 子句中的列存在索引，MySQL 会优先使用索引进行排序。因为索引本身就是有序的，所以可以直接从索引中获取有序的数据，避免了额外的排序操作。
- 示例：
在这个例子中，age 列上有索引，MySQL 可以直接利用该索引进行排序，提高查询效率。
文件排序（File Sort）
- 原理：当无法使用索引进行排序时，MySQL 会采用文件排序算法。它会将需要排序的数据读取到 sort_buffer 中进行排序，如果数据量超过 sort_buffer 的大小，会将数据分成多个临时文件，分别排序后再合并成一个有序的结果。
- 示例：
优化建议
- 创建合适的索引：为 ORDER BY 子句中的列创建索引，以提高排序效率。但要注意避免创建过多的索引，以免影响插入、更新和删除操作的性能。
- 优化查询语句：尽量减少 SELECT 子句中返回的列，避免不必要的列参与排序。可以使用覆盖索引，即索引包含了查询所需的所有列，这样可以直接从索引中获取数据，避免回表操作。
- 调整系统参数：适当调整 sort_buffer_size 参数，增大排序缓冲区的大小，以减少磁盘 I/O 操作。但要注意不要设置得过大，以免占用过多的内存资源。
通过以上的阐述，一起了解一下order by是如何工作的？聊聊以上语句是怎么执行的，以及有什么参数会影响执行的行为。
一、全字段排序
前面我们介绍过索引，所以你现在就很清楚了，为避免全表扫描，我们需要在 city 字段加上索引。
在 city 字段上创建索引之后，我们用 explain 命令来看看这个语句的执行情况。
图 1 使用 explain 命令查看语句的执行情况
Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
为了说明这个 SQL 查询语句的执行过程，我们先来看一下 city 这个索引的示意图。
图 2 city 字段的索引示意图
从图中可以看到，满足 city='杭州’条件的行，是从 ID_X 到 ID_(X+N) 的这些记录。
通常情况下，这个语句执行流程如下所示 ：
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 行返回给客户端。
暂且把这个排序过程，称为 全字段排序
形象总结这个流程：就是通过一个毛线球的引线，去找到喜爱的花色，然后再用喜爱的花色织毛衣的时候，发现不够了，再去接着找。
执行流程的示意图如下所示：
图 3 全字段排序
图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。 （小孩自己能解决的问题就自己解决，自己解决不了就找大人）
如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。
否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。
额外补充：MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
MySQL 中这种将需要排序的数据分成多份，分别排序后再合并的原理是利用了 分治策略和 归并排序的思想，具体如下：
- 分治策略
- 先将大规模的排序问题分解成若干个小规模的排序问题，即将需要排序的数据分成 12 份，这样可以降低每个子问题的处理难度。因为对较小规模的数据进行排序相对容易，并且可以在内存中更高效地完成。
- 对这 12 份数据分别进行单独排序，得到 12 个有序的临时文件。此时每个临时文件中的数据都是局部有序的。
- 归并排序思想
- 归并排序是一种稳定的排序算法，其核心思想是将两个或多个有序序列合并成一个有序序列。在这个场景中，就是将 12 个有序的临时文件合并成一个有序的大文件。
- 合并过程通常使用多路归并的方法。它会同时读取这 12 个有序文件的开头部分，比较它们的键值大小，然后将最小（或最大，根据排序要求）的键值及其对应的数据放入最终的有序大文件中。接着，从相应的临时文件中读取下一个数据，继续比较和合并，直到所有临时文件中的数据都被合并到大文件中。
通过这种分而治之再合并的方式，MySQL 能够处理大规模的数据排序，避免了一次性对大量数据进行排序时可能出现的内存不足等问题，提高了排序的效率和可扩展性。
二、rowid 排序
如果 MySQL 认为排序的单行长度太大会怎么做呢？
1. 使用磁盘临时表
通常情况下，MySQL 优先尝试在内存中进行排序操作，会使用内存临时表。但当单行长度过大，内存临时表无法容纳时，MySQL 会转而使用磁盘临时表。
磁盘临时表是将数据存储在磁盘上的临时文件，这会带来一定的性能开销，因为磁盘的读写速度远低于内存。例如，在处理包含大量文本字段或者 blob、text 类型数据的排序时，就可能出现这种情况。
2. 分阶段排序
- 外部排序：当数据无法全部放入内存进行排序时，MySQL 会采用外部排序算法。它会将数据分成多个较小的块，这些块可以分别在内存中排序，排序后将结果存储在磁盘上的临时文件中。之后，再将这些有序的临时文件合并成一个最终的有序结果。
- 多趟排序：如果单行长度大导致内存只能容纳少量数据行，MySQL 可能需要进行多趟排序。每一趟排序处理一部分数据，然后逐步合并这些部分排序的结果。
3. 产生警告或错误
如果排序操作因为单行长度过大而无法正常进行，MySQL 可能会产生警告或错误信息。比如，当磁盘空间不足，无法创建磁盘临时表时，就会抛出错误，导致排序操作失败。
示例
假设你有一个表 large_table，其中包含一个text类型的字段 long_text，当你尝试对这个字段进行排序时，可能会触发上述机制。
在执行这个查询时，如果 long_text 字段中的数据过长，导致单行长度超出了 MySQL 内存临时表的限制，MySQL 就会切换到磁盘临时表进行排序。你可以通过查看 MySQL 的错误日志或者使用 SHOW WARNINGS; 语句来查看是否有相关的警告信息。
图 4 rowid 排序
对比图 3 的全字段排序流程图你会发现，rowid 排序多访问了一次表 t 的主键索引，就是步骤 7。
最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。
三、全字段排序 VS rowid 排序
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想： 如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
MySQL 排序算法选择策略
- rowid 排序：当 MySQL 担心排序内存太小，影响排序效率时，会采用 rowid 排序算法。该算法一次可以排序更多行，它先根据 rowid（在 InnoDB 中，如果表没有显式定义主键，会有一个隐藏的 rowid 用于唯一标识行）对数据进行排序，排序完成后再根据 rowid 回到原表中获取所需的其他字段数据。由于需要回表操作，会造成额外的磁盘读，所以通常情况下不是优先选择的排序方式。
- 全字段排序：当 MySQL 认为内存足够大时，会优先选择全字段排序。会把查询所需的所有字段都放到 sort_buffer 中进行排序，排序完成后可以直接从内存中返回查询结果，避免了回表操作，从而减少了磁盘访问，提高了查询效率。
MySQL 设计思想
从 MySQL 对这两种排序算法的选择可以看出其设计思想是：如果内存充足，就充分利用内存来存储和处理数据， 尽量减少对磁盘的访问。因为磁盘的读写速度远慢于内存，减少磁盘访问可以显著提高数据库的性能和查询效率。通过根据不同的内存情况选择合适的排序算法，MySQL 能够在各种环境下尽可能地优化查询性能，以适应不同的业务需求和数据量规模