MySQL高级(四)——索引失效的情况
#创建表格
CREATE TABLE `staffs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表';
#插入数据
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values('z3','22','manager',NOW());
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values('July','23','dev',NOW());
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values('2000','23','dev',NOW());
#查看表格
select * from staffs;
#创建索引,创建表格时,默认只创建主键索引,这里创建复合索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);
1、全值匹配
全值匹配:当前表格索引有主键索引和手动创建的复合索引,当我们利用复合索引查询时,如果查询的字段是索引包含的字段时,WHERE查询字段的个数小于等于复合索引包含的字段,字段的顺序可以和索引中包含的字段顺序不一致。
2、最前左前缀法则
如果索引了多列,要遵循最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
这里都复合最佳左前缀法则,建立的索引都用上了。
#不遵循索引从最前列开始,导致索引失效,全表扫描
explain select * from staffs where age = 23 and pos = 'dev';
#遵循了缩影从最前列开始,但是跳过了age这个索引,导致pos这个索引失效
explain select * from staffs where name = 'July' and pos = 'dev';
3、不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
4、存储引擎不能使用索引中范围条件右边的列
由于age>11是一个范围,所以导致pos='manager’这个索引失效。
5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
6、is not null 也无法使用索引,但是is null是可以使用索引的
7、like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
8、字符串不加单引号索引失效
9、总结
假设创建的复合索引是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 |