mysql索引优化总结:
假设index(a,b,c):
Where语句 | 索引是否被引用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b= 5 | Y,使用到a,b |
where a = 3 and b= 5 and c =6 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
where a = 3 and c = 5 | Y,使用到a,但是c不可以。b中间断了 |
where a = 3 and b > 4 and c = 5 | Y,使用到a和b。c不能用在范围之后,b断了 |
where a =3 and b like ‘kk%’ and c = 4 | Y,使用到a,b,c |
where a =3 and b like ‘%kk’ and c = 4 | Y,只使用到a |
where a =3 and b like ‘%kk%’ and c = 4 | Y,只使用到a |
where a =3 and b like ‘k%kk’ and c = 4 | Y,使用到a,b,c |
优化总结口诀
全值匹配我最爱,最左前缀要遵守。
带头大哥不能死,中间兄弟不能断。
索引列上少计算,范围之后全失效。
LIKE百分写最右,覆盖索引不写星。
不等空值还有or,索引失效要少用。
VAR引号不可丢,SQL高级也不难。
索引失效的几种情况
1.查询条件全值匹配时,效率最高。
2.最佳左前缀法则。(如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。)
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
4.储存引擎不能使用索引中范围条件右边的列。
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*。
6.mysql在使用不等于(!= 或者 <> )的时候无法使用索引会导致全表扫描。
7.is null,is not null 也无法使用索引。
8.like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。
9.字符串不加单引号索引失效。
10.少用or,用它来连接时索引会失效。
一般性建议
1、对于单键检索,尽量选择针对当前query过滤性更好的索引。
2、在选择组合索引的时候,dang前Query中过滤性最好的字段在索引字段中。位置越靠前越好。
3、在选择组合索引的时候,尽量选择可以能够包含当前Query中where字句中更多字段的索引。
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。