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

Question

CREATETABLE`user`(
`id`INT(11)NOTNULLAUTO_INCREMENT,
`name`VARCHAR(20)DEFAULTNULL,
`num`INT(11)DEFAULTNULL,
`email`VARCHAR(20)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`NewIndex1`(`num`)
)ENGINE=MYISAMAUTO_INCREMENT=1DEFAULTCHARSET=utf8
DESCSELECT*FROM`user`ORDERBYidDESC
DESCSELECT*FROM`user`ORDERBYnumDESC#都用不到索引
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEuserALLNULLNULLNULLNULL40000Usingfilesort
DESCSELECT*FROM`user`WHEREnum=23ORDERBYnumDESC#可以用到索引
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEuserrefNewIndex1NewIndex15const1NULL
或者使用innodb引擎也可以在主键排序的时候用到索引这是为什么？

Answer 1

1).对mysql,innodb,你的第一条sqlDESCSELECT*FROM`user`ORDERBYidDESC一定会显示使用了主键索引.因为innodb是indexclusteredtable,数据项在主键索引的叶节点上.所以肯定可以用主键排序.2).innodb的二级索引存的是当前column+对应的主键,查询时用主键值去主键索引中查询相对应的row.InInnoDB,eachrecordinasecondaryindexcontainstheprimarykeycolumnsfortherow,aswellasthecolumnsspecifiedforthesecondaryindex.InnoDBusesthisprimarykeyvaluetosearchfortherowintheclusteredindex.DESCSELECT*FROM`user`ORDERBYnumDESC这条语句如果用num上的索引来排序,则按num索引的顺序去主键索引查,极端情况下就是40000次随机查询.反而不如做filesort来的快.3).SELECT*FROM`user`WHEREnum=23ORDERBYnumDESC这条和:SELECT*FROM`user`WHEREnum=23有差别么?直接索引找到num=23的列就完了.如果结果集较小,比如:SELECT*FROM`user`WHEREnumbetween23and30ORDERBYnumDESC则可能会是用num上的索引排序.

Answer 2

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