mysql 为什么主键排序用不到索引

Question

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `num` INT(11) DEFAULT NULL,
  `email` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NewIndex1` (`num`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

DESC SELECT * FROM `user` ORDER BY id DESC 
DESC SELECT * FROM `user` ORDER BY num DESC#都用不到索引

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      user    ALL     NULL     NULL   NULL    NULL    40000   Using filesort

DESC SELECT * FROM `user` WHERE num = 23 ORDER BY num DESC#可以用到索引

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      user    ref     NewIndex1       NewIndex1   5   const   1   NULL

或者使用innodb引擎也可以在主键排序的时候用到索引 这是为什么？

Answer 1

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上的索引排序.

Answer 2

首先先明确一点，SELECT * FROM user ORDER BY id DESC里没有任何过滤条件，而且你返回了所有字段，所以这是一个全表扫面的SQL。
对于这种SQL，MySQL的优化策略是不使用索引，因为全表扫描终究会把所有的记录都读一遍，如果不使用索引，MySQL可以按照磁盘上的顺序读取数据，对于传统硬盘而言，这是吞吐量最大的读方式，如果使用了索引，很可能会造成大量的随机读，反倒会慢下来。
当然这种优化策略只是一种估计，所以MySQL可能会猜错，如果你坚信自己是对的，你可以强制MySQL使用索引，比如：
SELECT * FROM user FORCE KEY(id) ORDER BY id DESC
或者：
SELECT * FROM user FORCE INDEX(NewIndex1) ORDER BY num DESC
或者你还可以试一下只返回少数制定字段，比如：
SELECT num FROM user ORDER BY id DESC