MySQL ORDER BY 如何利用索引

面试频率：★★★★☆
工作频率：★★★★★

⚡ 30 秒速记

ORDER BY 不一定需要 MySQL 额外排序。

因为：

B+ Tree 索引本身就是有序的，如果 SQL 要求的排序顺序刚好符合索引顺序，MySQL 就可能直接按照索引顺序读取数据，避免额外排序。

例如联合索引：

INDEX(name, age)

查询：

SELECT *
FROM user
WHERE name = '张三'
ORDER BY age;

可以理解：

先通过name找到张三这一段
        ↓
张三这一段中的age本来就是有序的
        ↓
直接按照索引顺序读取
        ↓
可能不需要额外排序

如果 EXPLAIN 中看到：

Using filesort

表示：

MySQL 需要进行额外的排序操作。

注意：

Using filesort
≠
一定使用磁盘文件排序

它的重点是：

没有直接利用索引顺序完成排序

一、普通 ORDER BY 怎么执行？

例如：

SELECT *
FROM user
ORDER BY age;

如果：

age没有合适索引

MySQL 可以简单理解为：

读取数据
   ↓
按照age排序
   ↓
返回结果

这时候：

EXPLAIN
SELECT *
FROM user
ORDER BY age;

Extra 可能看到：

Using filesort

二、什么是 Using filesort？

看到：

Using filesort

不要理解成：

一定在磁盘文件里面排序

更准确：

MySQL 需要执行额外的排序操作，而不是直接按照某个索引的有序顺序得到最终结果。

所以：

Using filesort

重点记：

额外排序

而不是：

一定磁盘排序

三、为什么索引可以帮助 ORDER BY？

假设：

CREATE INDEX idx_age
ON user(age);

B+ Tree 叶子节点按照：

age

有序排列。

可以简单理解：

18
↓
20
↓
21
↓
25
↓
30
↓
35
↓
40

现在执行：

SELECT age
FROM user
ORDER BY age;

查询要求：

按照age排序

而索引：

本来就按照age排序

所以 MySQL 有机会：

直接按照索引顺序读取

而不是：

读取数据
   ↓
放到排序区域
   ↓
重新按照age排序

四、核心原理

本质就是：

ORDER BY要求的顺序
        ↓
是否和索引顺序一致？
      ↙            ↘
    一致            不一致
     ↓                ↓
可能利用索引       需要额外排序
     ↓                ↓
避免filesort       Using filesort

所以：

ORDER BY 能否利用索引，核心还是利用 B+ Tree 的有序性。

五、联合索引 ORDER BY

假设：

CREATE INDEX idx_name_age
ON user(name, age);

联合索引：

(name, age)

排序规则：

先按照name排序

↓

name相同

↓

再按照age排序

例如：

李四 18
李四 25
李四 30

张三 18
张三 20
张三 30

王五 19
王五 21

六、WHERE name + ORDER BY age

执行：

SELECT *
FROM user
WHERE name = '张三'
ORDER BY age;

索引：

(name, age)

首先：

name = 张三

定位：

张三18
张三20
张三30

然后你会发现：

18
20
30

已经按照：

age

排好序了。

所以：

name
↓
负责定位


age
↓
天然有序
↓
负责排序

因此：

INDEX(name, age)

非常适合：

WHERE name = ?
ORDER BY age;

七、为什么单独 ORDER BY age 不一定能利用 (name, age)？

还是索引：

(name, age)

整个索引：

李四18
李四25
李四30

张三18
张三20
张三30

王五19
王五21

现在只看：

age

实际上：

18
25
30

18
20
30

19
21

你会发现：

age并不是全局有序

原因：

索引第一排序字段是name

只有：

name相同

以后：

age

才有序。

所以：

SELECT *
FROM user
ORDER BY age;

通常不能简单依赖：

INDEX(name, age)

直接得到全局按照 age 排序的结果。

八、这和最左匹配非常像

之前学联合索引：

INDEX(A, B)

索引排序：

先A

↓

A相同再B

所以：

B

不是：

全局有序

而是：

A相同时

↓

B有序

因此：

WHERE A = ?
ORDER BY B;

就非常合适。

因为：

A已经被固定

所以：

B在这个范围内有序

九、经典面试题

索引：

INDEX(A, B)

查询：

SELECT *
FROM test
WHERE A = 10
ORDER BY B;

能不能利用索引排序？

可以这样分析：

INDEX(A,B)
     ↓
先按照A排序
     ↓
A = 10
     ↓
A已经固定
     ↓
A=10这一段里面
     ↓
B天然有序
     ↓
可以按照索引顺序读取

所以：

有机会直接利用联合索引完成排序，避免额外 filesort。

十、如果没有 WHERE A = 10 呢？

索引：

INDEX(A, B)

查询：

SELECT *
FROM test
ORDER BY B;

索引可能：

A=1：

B=10
B=20
B=30


A=2：

B=5
B=15
B=25


A=3：

B=8
B=18
B=28

整个 B：

10
20
30
5
15
25
8
18
28

并不是：

全局有序

所以：

ORDER BY B

不能简单利用：

INDEX(A,B)

直接获得最终排序结果。

十一、INDEX(A,B,C) 怎么理解？

假设：

INDEX(A, B, C)

排序方式：

先A

↓

A相同再B

↓

A、B都相同再C

所以：

A

全局有序。

B

在：

A相同

的范围内有序。

C

在：

A、B都相同

的范围内有序。

十二、WHERE A=? ORDER BY B

索引：

(A,B,C)

SQL：

WHERE A = ?
ORDER BY B;

分析：

A固定
 ↓
进入某一个A范围
 ↓
这一段B有序

所以：

B可以用于排序

十三、WHERE A=? AND B=? ORDER BY C

索引：

(A,B,C)

SQL：

SELECT *
FROM test
WHERE A = 1
AND B = 2
ORDER BY C;

分析：

A固定
 ↓
B固定
 ↓
进入(A=1,B=2)这一段
 ↓
C天然有序

所以：

ORDER BY C

可以很好地利用索引顺序。

十四、联合索引设计的重要思想

实际业务经常出现：

SELECT *
FROM orders
WHERE user_id = ?
ORDER BY create_time DESC;

如果这是：

高频SQL

可以考虑联合索引：

INDEX(user_id, create_time)

为什么？

因为：

user_id
↓
负责WHERE定位


create_time
↓
负责ORDER BY排序

查询过程：

user_id = 100
      ↓
找到这个用户的订单范围
      ↓
create_time本身有序
      ↓
按照索引顺序读取

这就是联合索引设计中非常常见的：

WHERE字段
+
ORDER BY字段

一起设计索引。

十五、DESC 能不能利用索引？

例如：

SELECT *
FROM user
WHERE name = '张三'
ORDER BY age DESC;

索引：

(name, age)

不要认为：

索引默认ASC

↓

DESC一定不能使用

MySQL 可以在很多场景下：

反向扫描索引

例如索引：

18
20
25
30

正向：

18 → 20 → 25 → 30

反向：

30 → 25 → 20 → 18

所以：

ORDER BY age DESC

也可能利用索引顺序。

十六、多个排序字段要注意方向

例如：

INDEX(A, B)

查询：

ORDER BY A ASC, B ASC;

索引顺序一致：

A ↑

A相同时：

B ↑

比较容易利用索引。

如果：

ORDER BY A DESC, B DESC;

在合适情况下：

整个索引反向扫描

也可以得到：

A ↓

A相同时：

B ↓

但是如果：

ORDER BY A ASC, B DESC;

属于：

混合排序方向

能否完全利用索引，要结合：

索引定义

MySQL版本

执行计划

现代 MySQL 支持降序索引，例如：

INDEX(A ASC, B DESC)

所以不要死记：

ASC + DESC
=
一定filesort

最终还是：

EXPLAIN

验证。

十七、ORDER BY 和覆盖索引

假设：

INDEX(name, age)

查询：

SELECT name, age
FROM user
WHERE name = '张三'
ORDER BY age;

这里非常舒服。

第一：

name
↓
WHERE定位

第二：

age
↓
利用索引顺序排序

第三：

SELECT：

name
age

全部：

存在于索引

所以：

覆盖索引
↓
不用回表

最终：

WHERE
↓
利用索引


ORDER BY
↓
利用索引


SELECT
↓
覆盖索引

一条索引同时解决多个问题。

十八、ORDER BY 和回表

例如：

INDEX(name, age)

查询：

SELECT *
FROM user
WHERE name = '张三'
ORDER BY age;

虽然：

ORDER BY age

可以利用索引顺序。

但是：

SELECT *

需要：

完整行

二级索引：

(name, age, 主键)

并没有：

所有字段

所以：

仍然可能需要回表

因此：

利用索引排序

≠

覆盖索引

这两个概念不要混。

十九、Using filesort 一定很慢吗？

不一定。

例如：

只有100条数据

额外排序：

100条

成本可能非常低。

所以：

Using filesort

不是看到就必须优化。

还是要结合：

数据量

rows

LIMIT

索引情况

查询频率

业务场景

判断。

二十、ORDER BY + LIMIT

实际开发中非常常见：

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 20;

如果索引：

INDEX(user_id, create_time)

可以：

找到user_id=100
        ↓
按照create_time索引顺序读取
        ↓
拿前20条
        ↓
停止

这种：

ORDER BY
+
LIMIT

如果能够利用索引顺序：

性能收益可能非常明显。

因为不需要：

找到大量数据
↓
全部排序
↓
再取前20条

二十一、EXPLAIN 怎么分析 ORDER BY？

例如：

EXPLAIN
SELECT *
FROM user
WHERE name = '张三'
ORDER BY age;

重点看：

key

实际使用：

哪个索引

然后看：

Extra

如果出现：

Using filesort

说明：

需要额外排序

如果没有：

Using filesort

并且执行计划使用了合适的索引：

可能直接利用索引顺序完成排序

二十二、分析 ORDER BY 的思路

以后看到：

WHERE A = ?
ORDER BY B;

第一反应：

有没有：

INDEX(A,B)

如果有：

A
↓
WHERE定位

B
↓
ORDER BY排序

这是非常经典的联合索引设计方式。

二十三、和最左匹配串起来

联合索引：

(A,B,C)

本质：

A全局有序

↓

A相同时B有序

↓

A、B都相同时C有序

所以：

WHERE A = ?
ORDER BY B;

可以：

A固定
↓
B有序

而：

WHERE A = ?
AND B = ?
ORDER BY C;

可以：

A固定
↓
B固定
↓
C有序

所以 ORDER BY 利用索引：

本质上还是利用联合索引的排序规则。

二十四、和前面的知识串起来

现在：

B+ Tree
   ↓
叶子节点有序
   ↓
联合索引
   ↓
A → B → C排序
   ↓
最左匹配

查询：

WHERE
↓
利用索引定位

然后：

ORDER BY
↓
利用剩余索引顺序
↓
避免额外排序

如果查询字段：

全部在索引

还能：

覆盖索引
↓
避免回表

如果最终必须回表：

ICP
↓
提前过滤
↓
减少回表

现在这些知识已经开始串起来了。

二十五、一张图记住

索引：

INDEX(A,B)

SQL：

SELECT *
FROM test
WHERE A = 10
ORDER BY B;

执行：

             INDEX(A,B)
                  ↓
             根据A定位
                  ↓
                A=10
                  ↓
        ┌─────────┴─────────┐
        ↓                   ↓
      B=10                B=20
        ↓                   ↓
      B=30                B=40

因为：

A已经固定

↓

B天然有序

所以：

直接按照索引顺序读取

↓

可能避免额外排序

🎤 面试回答

问：

ORDER BY 怎么利用索引？

答：

B+ Tree 索引本身是有序的，如果 ORDER BY 要求的排序顺序与索引的排序顺序一致，MySQL 就可以直接按照索引顺序读取数据，从而避免额外排序。

例如存在联合索引 (name, age)，执行 WHERE name = ? ORDER BY age 时，先通过 name 定位到对应的索引范围，由于在 name 相同的情况下 age 本身就是有序的，因此可以利用索引完成排序。

如果 EXPLAIN 的 Extra 中出现 Using filesort，则说明 MySQL 需要执行额外的排序操作。

🎯 面试追问

Q1：Using filesort 是什么意思？

答：

MySQL需要进行额外排序

而不是：

直接利用索引顺序得到结果

Q2：Using filesort 一定使用磁盘吗？

答：

不是

它表示：

需要额外排序

并不等于：

一定在磁盘文件中排序

Q3：为什么索引可以优化 ORDER BY？

答：

因为：

B+ Tree索引本身有序

如果：

ORDER BY顺序

符合：

索引顺序

就可以：

直接按索引顺序读取

避免额外排序。

Q4：INDEX(A,B)，WHERE A=? ORDER BY B 能利用索引吗？

通常可以很好地利用。

因为：

A固定
↓
A这一段里面
↓
B有序

Q5：INDEX(A,B)，单独 ORDER BY B 呢？

通常不能简单利用 (A,B) 直接得到全局 B 排序。

因为：

B只有在A相同时才有序

而：

B不是全局有序

Q6：INDEX(A,B,C)，WHERE A=? AND B=? ORDER BY C 呢？

非常适合。

因为：

A固定
↓
B固定
↓
C有序

Q7：ORDER BY DESC 一定不能利用索引吗？

不是。

MySQL 可以在合适场景：

反向扫描索引

所以：

ORDER BY age DESC

也可能利用索引。

Q8：利用索引排序是不是就不用回表？

不是。

利用索引排序

解决：

排序问题

而：

覆盖索引

解决：

回表问题

例如：

SELECT *
FROM user
WHERE name = ?
ORDER BY age;

即使：

(name,age)

可以完成排序：

SELECT *

仍然可能需要：

回表

Q9：为什么 ORDER BY + LIMIT 很适合索引优化？

例如：

WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 20;

索引：

(user_id, create_time)

可以：

定位user_id
↓
按create_time顺序读取
↓
取20条
↓
停止

避免：

读取大量数据
↓
额外排序
↓
再取20条

Q10：怎么判断 ORDER BY 有没有额外排序？

使用：

EXPLAIN

重点看：

Extra

如果：

Using filesort

说明：

进行了额外排序

⚠️ 易错点

1. Using filesort 不代表一定使用磁盘

错误：

filesort
=
磁盘排序

正确：

filesort
=
MySQL需要额外排序

2. INDEX(A,B) 不代表 ORDER BY B 一定可以使用索引排序

因为：

B不是全局有序

而是：

A相同以后

↓

B才有序

3. DESC 不代表索引失效

MySQL：

可以反向扫描索引

所以：

ORDER BY B DESC

也可能利用索引。

4. 利用索引排序 ≠ 覆盖索引

利用索引排序
↓
避免额外排序

覆盖索引
↓
避免回表

是两个不同的优化点。

5. Using filesort 不一定需要优化

如果：

数据量很小

排序成本可能：

非常低

最终还是结合：

rows
数据量
LIMIT
查询频率

判断。

❓ 自测

1. ORDER BY 为什么可以利用索引？

2. B+ Tree 的什么特性可以帮助 ORDER BY？

3. Using filesort 是什么意思？

4. Using filesort 是否代表一定使用磁盘？

5. INDEX(age) 对 ORDER BY age 有什么帮助？

6. INDEX(A,B) 的排序规则是什么？

7. 为什么 INDEX(A,B) 中 B 不是全局有序？

8. INDEX(A,B)，WHERE A=? ORDER BY B 为什么可以利用索引？

9. INDEX(A,B)，单独 ORDER BY B 为什么通常不能直接得到全局排序？

10. INDEX(A,B,C) 的排序规则是什么？

11. WHERE A=? AND B=? ORDER BY C 为什么适合 (A,B,C)？

12. ORDER BY DESC 是否一定不能使用索引？

13. 什么叫反向扫描索引？

14. 利用索引排序和覆盖索引有什么区别？

15. 利用索引排序以后是否一定不用回表？

16. 为什么 WHERE user_id=? ORDER BY create_time DESC 适合 (user_id,create_time)？

17. 为什么 ORDER BY + LIMIT 利用索引时收益可能很大？

18. EXPLAIN 怎么判断是否进行了额外排序？

19. Using filesort 是否一定代表 SQL 很慢？

20. 联合索引设计为什么要同时考虑 WHERE 和 ORDER BY？