前言:数据库索引优化,但是可能出现索引失效,我们该怎样防止这种情况发生呢》》
文章目录
1. 案例建表:1.1 staffs表1.2 建立索引:2.案例:索引失效2.1 全面匹配索引的字段最好,第一个字段不能少,也不能,跳着使用字段。2.2 不要在索引的列上做任何操作(计算,函数(自动或者手动)类型转化),会导致索引失效而转向全表扫描2.3 存储引擎不能使用索引中范围条件右边的列:2.4 尽量使用索引覆盖 ,少用select *:2.5 在使用 != or<> 导致索引失效,全表扫描2.6 is null 或者 is not null 也会导致索引失效2.7 like以通配符%开头会变成全表扫描2.8 varchar类型 不写单引号,会索引失效!2.9 少用 or ,他也会导致索引失效!3 小总结:1. 案例建表:
1.1 staffs表
craete table staffs(id int primary key auto_increment,name varchar(20) not null ,age int not null,pos varchar(20) not null comment '职位',add_time timestamp not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into staffs(name,age,pos,add_time)values('z3',18,'manager',now());insert into staffs(name,age,pos,add_time)values('l4',20,'manager',now());insert into staffs(name,age,pos,add_time)values('w5',23,'manager',now());select * from staffs;
1.2 建立索引:
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
2.案例:索引失效
下面为索引失效的一些情况:
2.1 全面匹配索引的字段最好,第一个字段不能少,也不能,跳着使用字段。
但是注意下面的例子:
直接从所建索引的第二个字段开始,则 type 是 ALL,没有用到索引。索引失效了! 第一个字段不能少!!!
但是如果只有第一个字段,索引是有效的!!!
!这情况就好比是上楼梯:修好的楼梯是一二三楼,而你使用的时候直接用二三楼,那怎么能上去!所有索引失效了!
这个中间字段没有直接有的第三个字段!
发现第一个字段显然用到了自带,但是第三个却灭有!
也不能,跳着使用字段。
!所以在使用的时候最好使用索引的全部字段,不能么有第一个字段,中间字段也不能断,那样索引就失效了
2.2 不要在索引的列上做任何操作(计算,函数(自动或者手动)类型转化),会导致索引失效而转向全表扫描
上面两个 查出来的结果相同,但是第二个用了 left()函数,看看他们两个的过程分析:
显然,第二种索引使用为 null,索引失效了!
2.3 存储引擎不能使用索引中范围条件右边的列:
当我们所查列中包含范围的情况:
我们可以看出 name,age 用到了索引,后面的没用到索引,type 从 ref --》range 效率还降低了
索引又失效了!
!通俗的说就是使用范围的情况之后,索引就失效了
2.4 尽量使用索引覆盖 ,少用select *:
当查询字段是索引所包含的列时候,出现了using index ,索引覆盖!这种情况就比前者更好!
2.5 在使用 != or<> 导致索引失效,全表扫描
看下图:
使用!= 或者 <> 索引失效!
2.6 is null 或者 is not null 也会导致索引失效
看结果:
2.7 like以通配符%开头会变成全表扫描
上图三条sql 除了like后面的%位置不同,其他都一样。可以看出以% 开头都是全部扫描!
通配符的位置不同,查结果不同
但是 必须使用这情况应该怎样解决!
!使用索引覆盖可以优化这种情况
这样就可了!
2.8 varchar类型 不写单引号,会索引失效!
原因是mysql自动产生了类型转化!!!
2.9 少用 or ,他也会导致索引失效!
结果可以查出来,但是过程曲折!
3 小总结:
假设符合索引 inded(a,b,c);
做下面的题!
可看其他案例:三表案例