一个 MySQL 主键索引参与排序的问题

[ben548]

问一个 mysql 索引的问题：
背景信息：test 表上 a 字段有 btree 索引
有这样一个查询语句：
select * from test where a = 1 order by b desc
有以下两种场景：
场景一：当 b 是普通索引的时候，explain 结果会使用到 using filesort 这样的关键字（为了不进行 filesort ，需要对 a 和 b 两个字段建立联合索引）；
场景二：b 是主键索引的时候 explain 的结果确是 using where ；
我的理解：
场景一是符合预期的，因为在 a 的 btree 索引上并不能保证 b 字段的有序性，所以会使用到 filesort ；
场景二我理解是因为 a 字段的 btree 索引本身就是带有主键 id 这个信息的（理解是为了回表用的）
但是我不太理解的是，为什么这里的主键 id 排序不需要走 filesort ，是否可以理解为表中任意字段的 btree 索引其实等价于字段索引+主键索引的组合索引，如果这么理解的话是可以解释的通的，我不太确定的是内部机制是否真的是这样实现的？还是说依赖了主键索引其他方面的能力才导致不需要走 filesort ？

[JKeita]

id 索引本来就有序啊

[doraf]

我认为你的理解是对的。

这个页面 https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html 上说，

Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

[realpg]

我记得 innodb 的普通索引机制是利用了主键的

[awalkingman]

“是否可以理解为表中任意字段的 btree 索引其实等价于字段索引+主键索引的组合索引” ==> 是的，所有 btree 索引都可以认为是 目标索引后面加个 id （主键）的 联合索引。

[zjq07]

所有的非聚簇索引，都可以认为是（索引列+主键 id ）的联合索引

[awalkingman]

@zjq07 正解，我的表述不准确

[opengps]

@JKeita mysql 的 id 不一定是聚集的，之所以平常见到的 id 是聚集的，原因在于 id 放在第一列上。

[JKeita]

@opengps mysql 主键索引不就是聚簇索引，跟哪一列没关系吧。。。

[opengps]

@JKeita 是我记错了，查了下是没有设置为主键时候才是第一列聚集索引。聚集索引决定物理存放顺序。

[nothingistrue]

Mysql InnoDB 引擎下，有两个特殊性：一，主键索引是聚集索引，主键索引就是最终数据存储；二，基于一，其他索引指向的目的地是主键，而非通常的隐藏 rownum 。

对于特殊性一，因为主键索引就是全部数据，而你只用主键做排序条件，主键索引又是 btree 的天然具有顺序性，所以就无需做排序了，直接在主键索引上筛选数据就够了。这就是你 explain 只看到 using where 的原因。

对于特殊性二，a 字段的 btree 索引上，索引值就是 b 字段的值，故全部查询内容都在这个索引上，可以直接在这个索引上做查询和排序，也不用回落数据文件。当然这个并没有被采用，因为用特殊性一的处理会更快。

[iosyyy]

对于 innodb 来说他的数据和 id 是存在一起的 btree 底层具有排序信息所以不需要额外对 id 排序 也就是不需要走 filesort