假设在tableX中，我们有id(主键)name和age,phone，它们都有索引.
在此查询中:
select phone from tableX where name='Dennis' order by age个
我想这个过程是
-
使用
name索引获取与Dennis匹配的ID.用S表示ID集合 -
使用
age索引对1中获得的ID执行ORDER BY，得到一个排序的ID列表，用L表示 -
使用已排序的ID列表
L来获取phone
我假设在步骤2中，它可以使用沿着B+树叶 node 的顺序扫描，判断该叶 node 中的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 |
+----+--------+------+-------+