索引设计时,尽量满足既能满足排序,又能查找行。
ORDER BY优化时尽量使用索引排序。
只有当索引列的顺序和ORDER BY的字段顺序一致,排序方向也一致,并且索引包含要查找的行,满足最左前缀原则,才能使用索引来对结果排序。
例如给student表建立联合索引NAME
,AGE
,CLASS
CREATETABLE`student`(`ID`int(11)NOTNULLAUTO_INCREMENT,`NAME`varchar(30)NOTNULL,`SEX`char(2)NOTNULL,`AGE`int(11)NOTNULL,`CLASS`varchar(10)NOTNULL,`GRADE`varchar(20)NOTNULL,`HOBBY`varchar(100)DEFAULTNULL,PRIMARYKEY(`ID`),KEY`index_t`(`NAME`,`AGE`,`CLASS`))ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8;
如下sql能使用索引排序,Extra列没有Using filesort
EXPLAINSELECT`NAME`,`AGE`,`CLASS`FROM studentORDERBY`NAME`,`AGE`,`CLASS`
排序方向不一致时,不能使用索引排序
EXPLAINSELECT`NAME`,`AGE`,`CLASS`FROM studentORDERBY`NAME`,`AGE`,`CLASS`DESC
查找主键列时,主键列属于被索引列包含的范围
EXPLAINSELECT`ID`,`NAME`,`AGE`,`CLASS`FROM studentORDERBY`NAME`,`AGE`,`CLASS`
有一种情况可以不满足最左前缀原则,那就是前导列为常量时。
如下SQL用到了索引排序
SELECT*FROM studentWHERE NAME='tom'ORDERBY AGE,CLASS
如果是多表关联查询,则ORDER BY的字段必须全部是第一个表的字段,并满足上述原则,才能使用索引来对结果排序。