ORDER BY子句为何能利用索引？结合MySQL实例解析疑问

假设在tableX表中，包含id（主键）、name、age和phone字段，且所有字段均已创建索引。针对查询语句：

select phone from tableX where name='Dennis' order by age

我猜测它的执行流程如下：

• 利用name索引获取匹配Dennis的id，记为id集合S
• 利用age索引对步骤1得到的id进行排序，得到排序后的id列表L
• 通过排序后的id列表L获取对应的phone值

我认为步骤2中可能会对B+树的叶子节点进行顺序扫描，检查叶子节点中的id是否存在于步骤1得到的id集合S中，若存在则将其加入列表L，最终得到按age排序的id列表L。

但这种方式比普通的全表顺序扫描更优的点在哪里？两者不都是顺序扫描吗？

执行explain语句后发现，该查询使用了name索引并执行了filesort操作，执行计划如下：

+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | tableX | NULL       | ref  | idx_name      | idx_name | 123     | const |    1 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+----------------+

实际上我不确定order by子句在何种场景下可以利用索引，因此举了一个不太恰当的例子来阐述疑问。

mysql> create table tableX(
    -> id int primary key,
    -> name varchar(30),
    -> age int,
    -> phone varchar(30)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> create index idx_name on tableX(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_age on tableX(age);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_phone on tableX(phone);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tableX;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tableX |          0 | PRIMARY   |            1 | id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tableX |          1 | idx_name  |            1 | name        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tableX |          1 | idx_age   |            1 | age         | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tableX |          1 | idx_phone |            1 | phone       | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

mysql> select * from tableX;
+----+--------+------+-------+
| id | name   | age  | phone |
+----+--------+------+-------+
|  1 | Jack   |   20 | 180   |
|  2 | Dennis |   22 | 180   |
|  3 | Dennis |   18 | 1790  |
+----+--------+------+-------+

执行流程的实际逻辑 #

你猜测的流程不符合MySQL的实际处理逻辑，从explain结果能看到，查询用了idx_name索引并执行filesort，真实流程是：

1. 通过idx_name索引定位到所有name='Dennis'的记录，回表取出对应的age和phone字段（或先取主键id再回表，取决于索引覆盖情况）
2. 将筛选出的小批量记录放入内存（或磁盘临时文件），执行filesort操作按age排序
3. 返回排序后的phone字段

为什么你的假设流程不会被采用？ #

单独扫描age索引并匹配id集合S的方式存在两个核心问题：

• 过滤成本极高：age索引的叶子节点包含全表的age和主键id，需要遍历整个索引叶子节点逐一校验id是否在S中，数据量越大，这个过滤操作的耗时越久，远不如先筛选再排序高效。
• 浪费索引筛选的价值：name索引已经帮我们把数据集缩小到了符合条件的小范围，后续只需要对这个小数据集排序即可，无需遍历全量的age索引数据。

全表扫描 vs 索引筛选+排序的差异 #

两者虽都有顺序扫描环节，但扫描的数据量级天差地别：

• 全表扫描需要遍历所有表数据，无论是否符合name='Dennis'的条件
• 当前执行计划是先通过name索引筛选出小批量目标数据，再对这个子集排序，扫描和排序的数据量远小于全表

ORDER BY 利用索引的场景 #

MySQL能利用索引避免filesort的核心条件是：排序字段和查询过滤字段能组成覆盖索引，或排序字段是联合索引的后续字段（满足前缀匹配规则）。

比如针对你的查询，若创建联合索引idx_name_age_phone(name, age, phone)，此时执行：

select phone from tableX where name='Dennis' order by age

就可以直接通过联合索引获取有序的phone数据：联合索引的叶子节点按name排序，相同name的记录按age排序，直接遍历该索引的对应区间就能得到有序结果，无需额外排序。

内容的提问来源于stack exchange，提问作者Name Null