mysql 为什么主键排序用不到索引
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引擎也可以在主键排序的时候用到索引 这是为什么？
缺舟—帆渡
10 years, 3 months ago
Answers
首先先明确一点，
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
阿什房间里很快
answered 10 years, 3 months ago
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上的索引排序.
冰-ica
answered 10 years, 3 months ago