要判断一个查询有没有用到索引,以及索引有没有失效,前提就是会使用explain,以及看懂explain返回的信息
索引的作用
索引用于 查找 和 排序。
索引优化口诀
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写*;
不等空值还有or,索引失效要少用;
1、全值匹配我最爱,最左前缀要遵守;
意思就是有组合索引(a,b,c),最好是a=X and b=X and c=X这种形式,索引的顺序是a_b_c那么最好写sql的时候也是按照这个顺序写,否则内部会自行调整优化
2、带头大哥不能死,中间兄弟不能断;
意思是组合索引(a,b,c),那么a必须要有,否则就用不上这个组合索引,并且中间不能断,故子集是a,ab,abc 三种,如果出现ac那么这个abc索引只能用到a,不会完整的使用
3、索引列上少计算,范围之后全失效;
意思就算索引列尽量少用函数进行操作,因为一旦使用了就会进行全表扫描,导致索引失效。
范围之后指的是 >、< 等操作符会导致后面的索引失效(注意与sql书写的条件顺序无关,因为自行会调整,例如a_b_c,如果出现b>20等运算就会导致c无法被使用,故只能使用部分索引a_b)
4、Like百分写最右,覆盖索引不写*;
在写like进行筛选的时候%匹配尽量的写右边,要以常量开头,这样索引依然生效,如果实在是业务需求,那么考虑使用覆盖索引来代替,对于覆盖索引不能写select * 进行操作,否则会导致覆盖索引失效
5、不等空值还有or,索引失效要少用;
写sql的时候尽量不要写or进行连接条件,因为一写上去就会导致索引失效
索引失效的情形
-
使用范围操作符(>、<、in、!=、<> 等操作)后续的字段索引失效(a_b_c,如果b用到了范围操作符并且where条件的顺序也是a_b_c,那么会导致索引失效,如果是c使用到了范围,那么不影响,因为底层mysql会进行优化)
,(如果使用order by排序还会产生文件内排序) -
最佳左前缀法则
,例如索引 a_b_c,索引a必须要有,如果出现a,c少了b也导致失效(带头大哥不能死,中间不能断),也就是a、a_b、a_b_c这3种是可以用到索引a_b_c的组合索引 -
在索引列上做任何操作
(计算、函数、类型转换(自动或者手动))会导致索引失效,导致全表扫描。 -
like 通配符使用%开头会导致索引失效。
(如果一定要是 %X% 进行匹配,可以建立覆盖索引来优化查询) -
覆盖索引中 select 字段要和覆盖索引个数顺序完全一致,否则会导致覆盖索引失效
-
字符串不加''会导致索引失效
-
使用or进行连接条件会导致索引失效
(建议少使用or进行连接)
1、范围操作符导致索引失效举例
例如 创建了一个复合索引 index_col1_col2_col2,将表中的col1、col2、col3 三个字段建立复合索引。
如果我们的SQL中用到了范围操作符
例如:
下面的sql中使用到了组合索引,但是col2使用了范围操作符,导致产生了内排序
id 和 tb 分别是主键和被查询的表。
select id from tb where col1=1 and col2>1 order by col3 limit 1;
通过explain可以看出type是range,并且extra中出现了using filesort
,通过我们前面讲的explain的性能顺序system > const > eq_ref > ref > range > index > all
range不是最好的,如果我们将上面的 >
变成=
那么会发现range
变成了ref
并且没有出现using filesort
。说明这种是比较好的情形,但是实际上我们不能用=
来代替>
这类条件操作符,因为实际上的业务是有要求的,因此这里建议和产品经理谈需求的时候尽量用=
条件不用范围操作符。
索引优化(避免索引失效就算是索引优化)
索引优化(单表)
解决方案(非常重要!)
:
对于经常范围操作的字段我们不应当建立对应的组合索引,因此drop删除原先的索引,然后重新建立不带这个字段的组合索引。
以上面的例题为例的话,col2经常会进行范围操作,那么我们可以建立col1和col3的组合索引代替原先的索引
这样就会发现原先的sql查询去掉了using filesort
并且查询也用到了索引,type变成了ref。这个结果非常的理想。
索引优化(两表)
left join 需要对右边的表建立索引
right join 需要对左边的表建立索引。
索引优化(三表)
3表关联查询和2表一样,例如 a left join b left join c
对b,c创建索引即可
例题:
假设有组合索引 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=4 | Y,使用到了a ,b,c |
where b=3 或者 b=3 and c=4 | N |
where a=3 and c=4 | Y,使用到了a,但c不可以 |
where a=3 and b>4 and c=5 | Y,使用到了a,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 |
建立索引建议
- 对于组合索引,对于过滤较好的字段(
经常做等值过滤的
)索引越靠前(左
),对于需要范围判断或者排序的放在后面,这样的组合索引效果最好
在我们的sql中会有order by
、group by
。需要注意的点
order by 很可能会产生using filesort
。而group by
会产生using temporary 和 using filesort
。因为分组必排序。