order-by - 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