MySQL排序机制详解：内存排序与索引优化策略-CSDN博客

本文链接：https://blog.csdn.net/weixin_41165867/article/details/147917889

引言

在MySQL中，排序操作涉及内存和磁盘资源的合理利用，直接影响查询性能。MySQL为每个线程分配专门的排序缓冲区（sort_buffer），并根据排序数据长度和配置参数，决定采用全字段排序还是RowID排序。此外，合理设计索引还能避免排序步骤，提升查询效率。本文将通过具体示例，详细解析MySQL在不同排序场景下的执行流程及优化思路，帮助读者深入理解排序机制及索引优化策略。

MySQL排序的方式

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

通过max_length_for_sort_data配置以及排序行数据长度和，决定使用全字段排序还是rowid排序。例如，max_length_for_sort_data长度40，city、name、age三个字段长度和为36，则使用全字段排序，否则使用rowid排序。

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;

select city,name,age from t where city='杭州' order by name limit 1000;

全字段排序

全字段排序：取出select和order by所有字段，都放到sort_buffer进行排序。

是在内存中完成，还是需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端；

rowid排序

rowid排序：只取id和order by字段，都放到sort_buffer进行排序，排序后在回表查询。因此访问次数增加了。

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

覆盖索引无需排序

alter table t add index city_user(city, name);

从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
从索引 (city,name) 取下一个记录主键 id；
重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束；

感谢您的阅读！如果文章中有任何问题或不足之处，欢迎及时指出，您的反馈将帮助我不断改进与完善。期待与您共同探讨技术，共同进步！