MySQL高级（四）——索引失效的情况

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