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引擎也可以在主键排序的时候用到索引 这是为什么?
迷茫2017-04-17 11:40:21
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.
天蓬老师2017-04-17 11:40:21
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