MySQL排序优化与工作原理

MySQL排序是指对数据库中的数据进行排序操作。排序是一种常见的数据处理方式，可以将数据按照一定的规则进行排列，使得数据更加有序，方便查询和分析。MySQL提供了多种排序方式，包括升序排序、降序排序、多列排序等。

基本用法

ORDER BY子句用于按照指定列对结果集进行排序。列名可以是一个或多个列名，多个列名之间用逗号分隔。ASC和DESC是可选的关键字，用于指定升序或降序排序。如果不指定，默认按照升序排序。

SELECT 列1, 列2, ...
FROM 表名
ORDER BY 列名 [ASC|DESC];

排序方式

当使用ORDER BY语句进行排序时，MySQL会尝试使用不同的方式来优化查询。

索引排序

我们先看下下面的表，其中 name 是索引

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nameIndex` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当我们使用索引排序，我们用 explain 看下执行情况

Extra这个字段中的Using index表示的就是使用了索引，因为索引本身是有序的，而且查询字段本身就是索引，不需要回表查询，只需要扫描索引就行。也就是覆盖索引。

内存排序

当我们使用非索引字段排序，我们再 explain 看下执行情况

Extra这个字段中的Using filesort 表示的就是需要排序,我们看下这个SQL查询语句的执行过程。

初始化sort_buffer,MySQL会给每个线程分配一块内存用于排序，称为sort_buffer
从索引name找到第一个满足 name='李'条件的主键id
通过主键id去主键索引取出整行，取id、 name、 age三个字段的值，存入sort_buffer中
从索引name取下一个记录的主键id；
重复步骤3、 4直到值不满足查询条件为止
对sort_buffer中的数据按照字段age做快速排序；
按照排序结果返回给客户端

文件排序

如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，此时就会使用磁盘临时文件辅助排序。可以使用下面命令查看值。

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 262144  |
| sort_buffer_size        | 2097152 |
+-------------------------+---------+
3 rows in set (0.04 sec)
复制代码

那怎么确定一个排序语句是否使用了临时文件？

首先执行计划中的Extra字段为Using filesort
接着通过查看 OPTIMIZER_TRACE 的结果来确认的

/* 打开optimizer_trace， 只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select name,age from `user` where name='李' order by age;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`
复制代码

从number_of_tmp_files中。看到是否使用了临时文件。number_of_tmp_files=0，表示排序可以直接在内存中完成

  "filesort_summary": {
              "rows": 2,
              "examined_rows": 2,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 867728,
              "sort_mode": "<sort_key, packed_additional_fields>"
            }
复制代码

number_of_tmp_files表示的是，排序过程中使用的临时文件数。内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。

MySQL将需要排序的数据分成一定的份，每一份单独排序后存在这些临时文件中。然后把这这些有序文件再合并成一个有序的大文件。

`rowid`排序

那如果查询的字段很多的话，那么sort_buffer里能够同时放下的行数很少，就不得不要分成很多个临时文件，导致排序的性能会很差。

MySQL 使用 max_length_for_sort_data(默认值=1024)的参数进行控制，它是用于排序的行数据的长度的一个参数。如果单行的长度超过这个值，放入sort_buffer的字段，只有要排序的age列和主键id。

如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回。如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作。

注意：在MySQL中如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

我们把max_length_for_sort_data 设置小一点试下

SET max_length_for_sort_data = 4;
/* 打开optimizer_trace， 只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select name,age from `user` where name='李' order by age;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`
复制代码

查看 filesort_summary结果，sort_mode 变成了<sort_key, rowid>，表示参与排序的只有age和id 这两个字段

 "filesort_summary": {
              "memory_available": 2097152,
              "key_size": 9,
              "row_size": 9,
              "max_rows_per_buffer": 1129,
              "num_rows_estimate": 1129,
              "num_rows_found": 2,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 32784,
              "sort_algorithm": "std::sort",
              "unpacked_addon_fields": "max_length_for_sort_data",
              "sort_mode": "<fixed_sort_key, rowid>"
            }
复制代码

那如果需要排序的字段长度很大或者当不得不对text、blob列进行排序时，会怎么样呢？

通过max_sort_length 参数用于设置排序时字符串的最大长度。如果排序的字符串长度超过了该参数设置的长度，则会被截断。该参数默认值为1024。

排序优化

降低 max_length_for_sort_data 的值，可以让 sort_buffer_size 里存放更多的需要排序的数据，以避免触发文件排序。适用于MySQL 8.0.20 之前的版本。从8.0.20开始max_length_for_sort_data已被弃用。
检查是否可以让MySQL使用索引来提高ORDER BY速度。如果不能，尝试增加 sort_buffer_size 变量值。理想情况下，该值应足够大，以使整个结果集适合排序缓冲区（以避免磁盘文件排序）。如果存储长字符串列的值，并且增加了 max_sort_length 的值，则排序缓冲区sort_buffer 的大小也会增加，可能需要增加 sort_buffer_size。如果Sort_merge_passes （排序算法执行的合并次数）此值很大，则应考虑增加sort_buffer_size的值。
增加 read_rnd_buffer_size 的值，以便一次读取更多行。这个参数决定了在使用 ORDER BY 时用于随机读取的缓冲区的大小。增加此值可以在排序大型结果集时提高性能。