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). For mysql, innodb, your first sql

DESC SELECT * FROM `user` ORDER BY id DESC 

It will definitely show that the primary key index is used. Because innodb is an index clustered table, the data items are on the leaf nodes of the primary key index. So it can definitely be sorted by the primary key.

2). The secondary index of innodb stores the current column + the corresponding primary key. When querying, use the primary key value to query the corresponding row in the primary key index.

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

If this statement is sorted by the index on num, the primary key index will be checked in the order of the num index. In extreme cases, it will be 40,000 random queries. It is not as fast as filesort.

3).

SELECT * FROM `user` WHERE num = 23 ORDER BY num DESC

This sums to:

SELECT * FROM `user` WHERE num = 23

Is there any difference? Just use the index directly to find the column with num=23.

If the result set is small, for example:

SELECT * FROM `user` WHERE num between 23 and 30 ORDER BY num DESC

It may be sorted by the index on num.

Answer 2

First of all, let’s make it clear that there are no filter conditions in SELECT * FROM user ORDER BY id DESC, and you returned all fields, so this is a SQL that scans the entire table.
For this kind of SQL, MySQL's optimization strategy is not to use indexes, because a full table scan will eventually read all the records. If indexes are not used, MySQL can read the data in the order on the disk. For traditional hard disks, This is the reading method with the highest throughput. If an index is used, it is likely to cause a large number of random reads, which will actually slow it down.
Of course, this optimization strategy is just an estimate, so MySQL may guess wrong. If you firmly believe that you are right, you can force MySQL to use an index, such as:
SELECT * FROM user FORCE KEY(id) ORDER BY id DESC
Or:
SELECT * FROM user FORCE INDEX(NewIndex1) ORDER BY num DESC
Or you can try returning only a few specified fields, such as:
SELECT num FROM user ORDER BY id DESC