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=utf8DESCSELECT*FROM`user`ORDERBYidDESCDESCSELECT*FROM`user`ORDERBYnumDESC#都用不到索引idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEuserALLNULLNULLNULLNULL40000UsingfilesortDESCSELECT*FROM`user`WHEREnum=23ORDERBYnumDESC#可以用到索引idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEuserrefNewIndex1NewIndex15const1NULL或者使用innodb引擎也可以在主键排序的时候用到索引这是为什么？

Answer 1

1).对mysql,innodb,你的第一条sql
DESCSELECT*FROM`user`ORDERBYidDESC
一定会显示使用了主键索引.因为innodb是indexclusteredtable,数据项在主键索引的叶节点上.所以肯定可以用主键排序.
2).innodb的二级索引存的是当前column+对应的主键,查询时用主键值去主键索引中查询相对应的row.
InInnoDB,eachrecordinasecondaryindexcontainstheprimarykey
columnsfortherow,aswellasthecolumnsspecifiedforthe
secondaryindex.InnoDBusesthisprimarykeyvaluetosearchforthe
rowintheclusteredindex.
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