(转)MYSQL order by排序与索引关系总结

小鱼儿 2022-03-27 13:25 225阅读 0赞

转自:[http://phpdo.com/post/2013-02-26/40049082902][http_phpdo.com_post_2013-02-26_40049082902]

这里主要讨论一下InnoDB B-Tree索引的使用，不提设计，只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试

CREATE TABLE `friends`(
    	`ID` INT(10)UNSIGNED NOT NULL AUTO_INCREMENT,
    	`uid` BIGINT(20)UNSIGNED NOT NULL DEFAULT '0',
    	`fuid` BIGINT(20)UNSIGNED NOT NULL DEFAULT '0',
    	`fname` VARCHAR(50)NOT NULL DEFAULT '',
    	`fpicture` VARCHAR(150)NOT NULL DEFAULT '',
    	`fsex` TINYINT(1)NOT NULL DEFAULT '0',
    	`status` TINYINT(1)NOT NULL DEFAULT '0',
    	PRIMARY KEY(`ID`)
    )ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
    
    ALTER TABLE `friends` ADD INDEX uid_fuid(uid, fuid);

1.如果索引了多列，要遵守最左前缀法则。所谓最左前列，指的是查询从索引的最左前列开始，并且不跳过索引中的列

![20190113213523714.png][]

第2条语句,从索引的第二列开始查找，使用索引失败，导致MySQL采用ALL访问策略，即全表查询.在开发中，应该尽量避免全表查询。

2.当MySQL一旦估计检查的行数可能会”太多”，范围查找优化将不会被使用。

![26133437_NqDh.jpg][]

第2条语句使用了全表查询，它与第1条语句唯一的区别在于需要检查的行数远远多于第1条语句。在应用中，可能不会碰到这么大的查询，但是应该避免这样的查 询出现: select uid from users where registered < 1295001384

3.索引列不应该作为表达式的一部分，即也不能在索引列上使用函数

![26133437_D94I.jpg][]

第2和3条语句都有使用表达式，索引派不上用场。

4.尽量借用覆盖索引，减少select \* from …语句使用

![26133438_c824.jpg][]

第1句Extra中使用了Using index表示使用了覆盖索引。第3句也使用了覆盖索引,虽然ID不在索引uid\_fuid索引列中，但是InnoDB二次索引(second index)叶子页的值就是PK值，不同于MyISAM。Extra部分的Using index表示应用了索引，不要跟type中的index混淆。第2句没有使用覆盖索引，因为fsex不在索引中。

5.ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序  
MySQL支持二种方式的排序，FileSort和Index，后者效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。ORDER BY满足以下情况，会使用Index方式排序:  
            a)ORDER BY 语句使用索引最左前列。参见第1句  
            b)使用Where子句与Order BY子句条件列组合满足索引最左前列。参见第2句.  
以下情况，会使用FileSort方式的查询

![26133439_dKZf.jpg][]

a)检查的行数过多，且没有使用覆盖索引。第3句，虽然跟第2句一样，order by使用了索引最左前列uid，但依然使用了filesort方式排序，因为status并不在索引中，所以没办法只扫描索引。  
b)使用了不同的索引，MySQL每回只采用一个索引.第4句,order by出现二个索引，分别是uid\_fuid和聚集索引(pk)  
c)对索引列同时使用了ASC和DESC。 通过where语句将order by中索引列转为常量，则除外。第5句,和第6句在order by子句中，都出现了ASC和DESC排序,但是第5句却使用了filesort方式排序,是因为第6句where uid取出排序需要的数据,MySQL将其转为常量,它的ref列为const。  
d)where语句与order by语句，使用了不同的索引。参见第7句。  
e)where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。参见第8，9句  
f)where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。  
g)order by子句中加入了非索引列,且非索引列不在where子句中。  
h)order by或者它与where组合没有满足索引最左前列。参见第11句和12句,where与order by组合，不满足索引最左前列. （uid, fsex)跳过了fuid  
i)当使用left join，使用右边的表字段排序。参见第13句，尽管user.uid是pk，依然会使用filesort排序。

6.慎用left join语句,避免创建临时表 使用left join语句的时候，避免出现创建临时表。尽量不要用left join，分而治之。非要使用的时候，要询问自己是不是真要必须要使用。

![26133441_Ipdn.jpg][]

7.高选择性索引列。 尽量使用高选择性的过引来过滤数据。高选择性指Cardinality/\#T越接近1，选择性越高，其中Cardinality指表中索引列不重复值(行)的总数。PK和唯一索引，具有最高的选择性，即1。推荐可选性达到20%以上。

![26133442_ijct.jpg][]

这里有二个索引可供使用,而MySQL选择PRIMARY，是因为它具有更高的选择性。

8.谨防where子句中的OR。where语句使用or，且没有使用覆盖索引,会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR

![26133442_jetn.jpg][]

第1句虽然使用了索引,但是查行时间依然不可以恭维，mysql要检查的行很多,但是返回的行却很少.Extra中的using where表示需要通过where子句扔弃不需要的数据行。  
9.LIMIT与覆盖索引 limit子句，使用覆盖索引时比没有使用覆盖索引会快很多

[http_phpdo.com_post_2013-02-26_40049082902]: http://phpdo.com/post/2013-02-26/40049082902
[20190113213523714.png]: /images/20220327/211decfa9dfb4eefbc26ffe37d0208e7.png
[26133437_NqDh.jpg]: /images/20220327/974f2d41db3c4c6c89812e09fd513136.png
[26133437_D94I.jpg]: /images/20220327/68f4a471e50342988fd63262802d6c71.png
[26133438_c824.jpg]: /images/20220327/44c2e4209ca64ee3a4e45f8a61b27449.png
[26133439_dKZf.jpg]: /images/20220327/e281d466e0114518a35e8508ef2c55a5.png
[26133441_Ipdn.jpg]: /images/20220327/b8eeefcd47164e4c8e7b02438f9ba8e2.png
[26133442_ijct.jpg]: /images/20220327/dbda9b706474494581f88e9b2bc12076.png
[26133442_jetn.jpg]: /images/20220327/fbfadcce52b04f2a8dcf6d4e56506844.png