目录
- 一、全值匹配我最爱
- 1.SQL1
- 2.SQL2
- 3.SQL3
- 4.结论
- 二、最佳左前缀法则
- 1.总结
- 三、计算、函数导致索引失效
- 1.总结
- 四、范围条件右边的列索引失效
- 1.总结
- 五、不等于(!= 或者<>)索引失效
- 六、is not null无法使用索引,is null可使用索引
- 七、like以通配符%开头索引失效
- 八、类型转换导致索引失效
- 九、练习
一、全值匹配我最爱
我们接下来进行3个sql的查询
1.SQL1
可以看出全表扫描
耗时0.120s
创建索引
create index idx_age on emp(age);
扫描46112行,type为ref
耗时0.055s
2.SQL2
耗时0.110s
创建复合索引
create index idx_age_deptId on emp(age,deptId);
耗时0.001s
3.SQL3
耗时0.110s
创建复合索引
create index idx_age_deptId_name on emp(age,deptId,name);
耗时0.001s
4.结论
全职匹配我最爱指的是,查询的字段按照顺序在索引中都可以匹配到!
SQL中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响SQL执行结果的前提下,给你自动地优化。
二、最佳左前缀法则
创建复合索引
create index idx_age_deptId_name on emp(age,deptId,name);
# 跳过了deptId 导致后面的name 不能使用索引!
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd';
或者
# 直接跳过了age ,后面的都不能使用索引!
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
# 创建的索引列都存在! mysql 的优化器会自动给你排序!
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.age=30 AND emp.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'abcd' AND emp.age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abcd' AND deptid=4 AND emp.age=30;
1.总结
结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
三、计算、函数导致索引失效
创建一个单值索引
create index idx_age on emp(age);
1.总结
结论:计算、函数导致索引失效
四、范围条件右边的列索引失效
创建索引
create index idx_age_deptId_name on emp(age,deptId,name);
#name字段索引会失效
SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;
或者
SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>20 ;
1.总结
建议:将可能做范围查询的字段的索引顺序放在最后
五、不等于(!= 或者<>)索引失效
六、is not null无法使用索引,is null可使用索引
七、like以通配符%开头索引失效
八、类型转换导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name=123;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name='123';
- name=123发生类型转换,索引失效。
- 设计实体类属性时,一定要与数据库字段类型相对应,否则会出现类型转换的情况。
九、练习
假设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 |