1). 对mysql, innodb, 你的第一条sql
DESC SELECT * FROM `user` ORDER BY id DESC
一定会显示使用了主键索引. 因为innodb是index clustered table, 数据项在主键索引的叶节点上. 所以肯定可以用主键排序.
2). innodb的二级索引 存的是 当前column+对应的主键, 查询时用 主键值去 主键索引中查询相对应的row.
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.
DESC SELECT * FROM `user` ORDER BY num DESC
这条语句如果用num上的索引来排序, 则 按num索引的顺序 去主键索引查, 极端情况下就是40000次随机查询. 反而不如做filesort来的快.
3).
SELECT * FROM `user` WHERE num = 23 ORDER BY num DESC
这条和:
SELECT * FROM `user` WHERE num = 23
有差别么? 直接 索引找到num=23 的列就完了.
如果结果集较小, 比如:
SELECT * FROM `user` WHERE num between 23 and 30 ORDER BY num DESC
则可能会是用num上的索引排序.