文章目录
- MySQL45讲 第十六讲 “order by”是怎么工作的？
- 一、引言
- 二、全字段排序
- （一）索引创建与执行情况分析
- （二）执行流程
- （三）查看是否使用临时文件
- 三、rowid 排序
- （一）参数控制与算法改变
- （二）执行流程
- （三）全字段排序和rowid 排序性能对比
- 四、利用联合索引避免排序
- （一）创建联合索引
- （二）执行流程简化
- （三）覆盖索引优化
- 五、总结与思考
MySQL45讲 第十六讲 “order by”是怎么工作的？
一、引言
在应用开发中，经常需要根据指定字段排序显示结果。本文以查询城市为 “杭州” 的市民信息并按姓名排序为例，深入探讨 MySQL 中 “order by” 语句的执行流程、不同算法以及相关优化策略，避免在开发中出现性能问题。
例子：假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回 前1000个人的姓名、年龄。
假设这个表的部分定义是这样的：
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;
这时，你的SQL语句可以这么写：
select city,name,age from t where city='杭州' order by name limit 1000;
二、全字段排序
（一）索引创建与执行情况分析
-
MySQL会给每个线程分配一块内存用于排序，这块内存称为
sort_buffer。为避免全表扫描，需在city字段创建索引。
-
使用
explain命令查看执行情况，Extra字段中的Using filesort表示需要排序，MySQL 会为每个线程分配sort_buffer内存用于排序。
（二）执行流程
- 初始化
sort_buffer，确定放入name、city、age三个字段。 - 从
city索引找到满足条件的第一个主键id。 - 到主键
id索引取出整行，取相关字段值存入sort_buffer。 - 从
city索引取下一个记录的主键id，重复 3、4 步直到不满足条件。 - 对
sort_buffer中的数据按name字段做快速排序（可能在内存或使用外部排序，取决于sort_buffer_size参数和排序数据量）。 - 取前 1000 行返回给客户端。
（三）查看是否使用临时文件
-
通过设置
optimizer_trace为enabled=on，计算执行语句前后performance_schema.session_status中Innodb_rows_read的差值，并查看OPTIMIZER_TRACE结果中的number_of_tmp_files，可确定是否使用临时文件。若该值大于 0，表示使用了外部排序，MySQL 将数据分成多份排序后合并；若为0，表示可在内存中完成排序。 -
下图中的
number_of_tmp_files=12代表MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。
三、rowid 排序
在全字段排序算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，即行长度过长，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。这时候就需要使用rowid排序。
（一）参数控制与算法改变
SET max_length_for_sort_data = 16;
- 当
max_length_for_sort_data参数设置为较小值（如 16），且单行长度超过该值时，MySQL 采用rowid排序算法。此算法放入sort_buffer的字段只有要排序的列（如 “name”）和主键 id。
（二）执行流程
-
初始化
sort_buffer，确定放入name和id字段。 -
从
city索引找到满足条件的第一个主键id。 -
到主键
id索引取出整行，取name和id字段存入sort_buffer。 -
从
city索引取下一个记录的主键id，重复 3、4 步直到不满足条件。 -
对
sort_buffer中的数据按name排序。 -
遍历排序结果取前 1000 行，按
id值回原表取出city、name和age字段返回给客户端。此算法多了一次回表操作，但在单行数据较大时，可在排序过程中一次排序更多行。
（三）全字段排序和rowid 排序性能对比
- 全字段排序在内存足够时优先选择，可直接从内存返回结果，减少磁盘访问；
- rowid 排序在内存较小时使用，虽排序时能处理更多行，但需回表取数据，增加磁盘读操作。
四、利用联合索引避免排序
（一）创建联合索引
-
创建
city和name的联合索引（如city_user (city, name)），可确保从该索引取出行时按name递增排序，无需再进行排序操作。
-
无需继续创建临时表和排序，使用
explain指令查看，Extra字段已经没有Using filesort,证明
（二）执行流程简化
-
从联合索引找到满足条件的第一个主键
id。 -
到主键
id索引取整行相关字段值直接返回。 -
从联合索引取下一个记录主键
id，重复 2 步直到满足条件结束。
（三）覆盖索引优化
-
进一步创建
city、name和age的联合索引（如city_user_age (city, name, age)），可利用覆盖索引，直接从索引获取数据返回，无需回表从主键索引取数据，性能更快，但需权衡索引维护代价。覆盖索引是指，索引上的信息足够满足查询请求，不需要回到主键索引上去取数据。 -
这样整个查询语句的执行流程就变成了：
- 从索引
(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回； - 从索引
(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回； - 重复执行步骤2，直到查到第1000条记录，或者是不满足
city='杭州’条件时循环结束。
- 从索引
五、总结与思考
MySQL 中 order by 语句有多种执行算法，开发人员应清楚其排序逻辑和系统资源消耗，根据实际情况选择合适方案。
- 全字段排序可能需要使用临时表进行排序，在字段过多的情况下性能可能会很差。
- 为了减少字段过长导致的排序性能下降，rowid排序算法放入
sort_buffer的字段只有要排序的列（如 “name”）和主键 id。 - 如果需要进一步提高性能，可以采取联合索引乃至覆盖索引（字段已排序），这样就可以避免排序，但是需要消耗空间存储索引和维护索引为代价。
