文章目录
> 全值匹配我最爱
- 建立索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
- 查看下列sql的Explian
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'
- 最后一行因为建的索引都用上了!快的一批
- 结论:全值匹配我最爱,指的是查询的字段按照顺序在索引中都可以匹配到,索引都派上用场了
- 更换条件顺序查看索引能否被应用
- 结论:SQL中查询字段的顺序,跟使用索引中字段的顺序没有关系,有优化器用AND把条件连起来 会自动优化。
> 最佳左前缀法则
- 使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列,跳了后面的索引会没用上。
- 结论:带头大哥不能死,中间兄弟不能断,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
> 索引列上做计算
- 在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
- 在查询列上使用了函数
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
- 结论:等号左边无计算!等号左边不能有计算。
- 在查询列上做了转换
create index idx_name on emp(name);
explain select sql_no_cache * from emp where name='30000';
explain select sql_no_cache * from emp where name=30000;
- 结论:等号右边无转换!等号右边不进行类型转换
> 索引列上进行范围查询
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<5 AND emp.name = 'abcd';
- 总结:中间用了范围查询,会使后面的兄弟索引都失效,所以要将可能做范围查询的字段的索引顺序放在最后
> 使用覆盖索引
- 查询列和索引列时,不要写select *,即使要查50个字段就列50个字段
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
> 使用不等于(!= 或者<>)
- 使用不等于(!= 或者<>)时,有时会无法使用索引导致全表扫描。因为系统不知道哪个不等于,进行全表扫描比较
mysql> explain select SQL_NO_CACHE * from emp where emp.age = 30 ;
mysql> explain select SQL_NO_CACHE * from emp where emp.age != 30 ;
> is not null 和 is null
- 查看索引字段是否允许为空
mysql> desc emp;
- 当字段允许为Null的条件下
explain select * from emp where age is null
explain select * from emp where age is not null
结论:is not null用不到索引,is null可以用到索引。
> like的前后模糊匹配
- 前缀模糊查询会导致索引失效,最前面字母要确定下来
mysql> create index idx_name_emp on emp (name);
mysql> explain select * from emp where name like '%a' ;#索引失效
mysql> explain select * from emp where name like '%a%' ;#索引失效
mysql> explain select * from emp where name like 'a%' ;#索引不失效
mysql> explain select * from emp where name like '_a%' ;#索引失效
> 使用or
- OR查询会导致索引失效
mysql> explain select * from emp where age = 30 or age = 40 ;
- 使用union all或者union来替代OR便不会失效
mysql> explain select * from emp where age = 30 union select * from emp where age =40 ;
> 总结
- 建立索引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 或者 where b = 3 and c = 4 或者 where c = 4 | N,大哥不在了 |
where a = 3 and c = 5 | 使用到a, 但是c不可以,b中间断了 |
where a = 3 and b > 4 and c = 5 | 使用到a和b, c不能用在范围之后,b断了 |
where a is null and b is not null | is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用 |
where a <> 3 | 不能使用索引 |
where abs(a) =3 | 不能使用 索引,做了计算 |
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,索引影响要注意;
用博客见证成长,用行动证明我在努力。
如果你有缘看到我博客,对你有帮助、喜欢博客内容,请“点赞” “评论”“收藏”
一键三连哦!