转载&＃xff1a;http://blog.sina.com.cn/s/blog_76abecad0100sicf.html
我们平常所说的索引&＃xff0c;如果没有特别指明&＃xff0c;都是指B&＃43;树结构组织的B-Tree索引。其中聚集索引&＃xff0c;次要索引&＃xff0c;覆盖索引&＃xff0c;复合索引&＃xff0c;前缀索引&＃xff0c;唯一索引默认都是使用B&＃43;树索引&＃xff0c;统称索引。当然&＃xff0c;除了B&＃43;树这种类型的索引之外&＃xff0c;还有哈稀索引(hash index)等。
MySQL InnoDB B-Tree索引使用Tips
这里主要讨论一下InnoDB B-Tree索引的使用&＃xff0c;不提设计&＃xff0c;只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试
CREATE TABLE &＃96;friends&＃96; ( &＃96;ID&＃96; int(10) UNSIGNED NOT NULL AUTO_INCREMENT, &＃96;uid&＃96; bigint(20) UNSIGNEDNOT NULL DEFAULT &＃39;0&＃39;, &＃96;fuid&＃96; bigint(20) UNSIGNED NOT NULL DEFAULT &＃39;0&＃39;, &＃96;fname&＃96; varchar(50) NOTNULL DEFAULT &＃39;&＃39;, &＃96;fpicture&＃96; varchar(150) NOT NULL DEFAULT &＃39;&＃39;, &＃96;fsex&＃96; tinyint(1) NOT NULL DEFAULT&＃39;0&＃39;, &＃96;status&＃96; tinyint(1) NOT NULL DEFAULT &＃39;0&＃39;, PRIMARY KEY (&＃96;ID&＃96;) ) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;1DEFAULT CHARSET&＃61;utf8; ALTER TABLE &＃96;friends&＃96; ADD INDEX uid_fuid (uid, fuid);
欢迎大家阅读《MYSQL order by排序与目录关系总结》,跪求各位点评&＃xff0c;by 搞代码
1.如果索引了多列&＃xff0c;要遵守最左前缀法则。所谓最左前列&＃xff0c;指的是查询从索引的最左前列开始&＃xff0c;并且不跳过索引中的列。
第2条语句,从索引的第二列开始查找&＃xff0c;使用索引失败&＃xff0c;导致MySQL采用ALL访问策略&＃xff0c;即全表查询.在开发中&＃xff0c;应该尽量避免全表查询。
2.当MySQL一旦估计检查的行数可能会”太多”&＃xff0c;范围查找优化将不会被使用。
第2条语句使用了全表查询&＃xff0c;它与第1条语句唯一的区别在于需要检查的行数远远多于第1条语句。在应用中&＃xff0c;可能不会碰到这么大的查询&＃xff0c;但是应该避免这样的查 询出现: select uid from users where registered <1295001384
3.索引列不应该作为表达式的一部分&＃xff0c;即也不能在索引列上使用函数
第2和3条语句都有使用表达式&＃xff0c;索引派不上用场。
4.尽量借用覆盖索引&＃xff0c;减少select * from …语句使用
第1句Extra中使用了Using index表示使用了覆盖索引。第3句也使用了覆盖索引,虽然ID不在索引uid_fuid索引列中&＃xff0c;但是InnoDB二次索引(second index)叶子页的值就是PK值&＃xff0c;不同于MyISAM。Extra部分的Using index表示应用了索引&＃xff0c;不要跟type中的index混淆。第2句没有使用覆盖索引&＃xff0c;因为fsex不在索引中。
5.ORDER BY子句&＃xff0c;尽量使用Index方式排序,避免使用FileSort方式排序
MySQL支持二种方式的排序&＃xff0c;FileSort和Index&＃xff0c;后者效率高&＃xff0c;它指MySQL扫描索引本身完成排序。FileSort方式效率较低。ORDER BY满足以下情况&＃xff0c;会使用Index方式排序:
a)ORDER BY 语句使用索引最左前列。参见第1句
b)使用Where子句与Order BY子句条件列组合满足索引最左前列。参见第2句.
以下情况&＃xff0c;会使用FileSort方式的查询
a)检查的行数过多&＃xff0c;且没有使用覆盖索引。第3句&＃xff0c;虽然跟第2句一样&＃xff0c;order by使用了索引最左前列uid&＃xff0c;但依然使用了filesort方式排序&＃xff0c;因为status并不在索引中&＃xff0c;所以没办法只扫描索引。
b)使用了不同的索引&＃xff0c;MySQL每回只采用一个索引.第4句,order by出现二个索引&＃xff0c;分别是uid_fuid和聚集索引(pk)
c)对索引列同时使用了ASC和DESC。 通过where语句将order by中索引列转为常量&＃xff0c;则除外。第5句,和第6句在order by子句中&＃xff0c;都出现了ASC和DESC排序,但是第5句却使用了filesort方式排序,是因为第6句where uid取出排序需要的数据,MySQL将其转为常量,它的ref列为const。
d)where语句与order by语句&＃xff0c;使用了不同的索引。参见第7句。
e)where语句或者ORDER BY语句中索引列使用了表达式&＃xff0c;包括函数表达式。参见第8&＃xff0c;9句
f)where 语句与ORDER BY语句组合满足最左前缀&＃xff0c;但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件&＃xff0c;但是where子句中使用的是条件查询。
g)order by子句中加入了非索引列,且非索引列不在where子句中。
h)order by或者它与where组合没有满足索引最左前列。参见第11句和12句,where与order by组合&＃xff0c;不满足索引最左前列. (uid, fsex)跳过了fuid
i)当使用left join&＃xff0c;使用右边的表字段排序。参见第13句&＃xff0c;尽管user.uid是pk&＃xff0c;依然会使用filesort排序。
6.慎用left join语句,避免创建临时表 使用left join语句的时候&＃xff0c;避免出现创建临时表。尽量不要用left join&＃xff0c;分而治之。非要使用的时候&＃xff0c;要询问自己是不是真要必须要使用。
7.高选择性索引列。 尽量使用高选择性的过引来过滤数据。高选择性指Cardinality/#T越接近1&＃xff0c;选择性越高&＃xff0c;其中Cardinality指表中索引列不重复值(行)的总数。PK和唯一索引&＃xff0c;具有最高的选择性&＃xff0c;即1。推荐可选性达到20%以上。
这里有二个索引可供使用,而MySQL选择PRIMARY&＃xff0c;是因为它具有更高的选择性。
8.谨防where子句中的OR。where语句使用or&＃xff0c;且没有使用覆盖索引,会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR
第1句虽然使用了索引,但是查行时间依然不可以恭维&＃xff0c;mysql要检查的行很多,但是返回的行却很少.Extra中的using where表示需要通过where子句扔弃不需要的数据行。
9.LIMIT与覆盖索引 limit子句&＃xff0c;使用覆盖索引时比没有使用覆盖索引会快很多
京公网安备 11010802041100号