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的值。
  

mysql> show status  like "sort%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 321   |
| Sort_range        | 0     |
| Sort_rows         | 10    |
| Sort_scan         | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

• 增加 read_rnd_buffer_size 的值，以便一次读取更多行。这个参数决定了在使用 ORDER BY 时用于随机读取的缓冲区的大小。增加此值可以在排序大型结果集时提高性能。
  

小结


   其实作为开发人员其实应该更多的从 SQL 上入手，比如：使用索引排序、减少排序的数据量、使用覆盖索引、使用整型字段进行排序等等。但是理解 ORDER BY 的工作流程，以及一些参数配置，可以更便于我们进行优化。