order by 是怎样工作的

关注

原创最新推荐文章于 2026-05-16 06:13:24 发布 331 阅读 AI 写同款· GEO 优化 ›

MYSQL order by到底是如何工作的？在你开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求。还是以我们前面举例用过的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前个人的姓名、年龄。这个语句看上去逻辑很清晰，但是你了解它的执行流程吗？今天，我就和你聊聊这个语句是怎么执行的，以及有什么参数会影响执行的行为。阅读详情

一图胜千言

rowid排序

排序的时候，只将 order by 后面排序字段以及记录主键放入到 sort buffer 中。比如 select a，b，c from t order by a desc，

只将字段a，表主键id 放入 sort buffer 中

全字段排序

排序的时候，将select sql中所有字段都放入到 sort buffer 中一起参与排序，无需根据rowid 再去主键索引上获取记录。

比如 select a，b，c from t order by a desc，只将 a，b，c 三个字段让如sort buffer中，如果表 t 还有字段 d，字段 d 是不会被放入 sort buffer 中

sort_buffer

属于 server层，MySQL 会给每个线程（也就是每一个 session）分配一块内存用于排序，称为 sort_buffer。可以使用 sort_buffer_size 进行设置。

max_length_for_sort_data

全字段排序的最大行数据，默认值是 1024 字节。

怎么做比较的，需要排序的数据字段的定义大小的和大于max_length_for_sort_data，就使用 “rowid排序”，反之使用“全字段排序”。

例如：select a，b，c from t order by a desc，a，b，c 数据类型都是 varchar(8) max_length_for_sort_data 配置的是 16

a，b，c 字段定义之和是24 大于 16 就使用“rowid排序”

需要说明的是，如果是 rowid 排序，在排序完成后需根据排好序的 rowid，去主键索引获取记录，也就是需要回表操作

举例一条 order by sql的执行流程

CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_a` (`a`) ) ENGINE=InnoDB;

在表 t2 中插入数据 1000 条数据

for(int i=1; i<=1000; i++) { insert into t2(id, a, b) values(i, i%100, i); }

sql select a, b from t2 where a = 10 order by b;

这里假设使用的是“全字段排序”，并且需要排序的数据都可以放入 sort buffer 中。

执行流程如下

初始化 sort_buffer；
从索引 a 找到第一个满足 a=10 条件的主键 id；
到主键 id 索引取出整行，取 a, b 两个字段的值，存入 sort_buffer 中；
从索引 a 取下一个记录的主键 id；
重复步骤 3、4 直到 a 的值不满足查询条件为止；
对 sort_buffer 中的数据按照字段 b 做快速排序；
返回数据给客户端

有个疑问，从执行流程看，扫描行数应该是10行，为什么是 10行，因为sql执行的时候会踩中字段a的索引，符合a=10的记录只有10行，所以扫描行数是 10 行。但是通过观察慢日志，实际扫描行数却是 20 行，为什么？？一直没搞明白。

如何验证 order by 是否使用了临时表

通过以sql命令，验证是否使用了临时表

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

下面图片中的信息是查看 OPTIMIZER_TRACE 输出的