索引失效的几种情况

一、索引的创建

1.创建普通索引

create index 索引名 on 表名（字段名）

create index index_mobile on tb_address(mobile)

2.创建唯一索引

create unique index 索引名 on tb_address(字段名)

create unique index index_address on tb_address(address)

在mysql 的innodb引擎中，是允许在唯一索引的字段中出现多个null值的。

3.创建联合索引

create index 联合索引名 on student(多个字段名)

create index merge on student(name,age,address)

4.查看索引

show index from 表名

show index from tb_address 

5.删除索引

drop index 索引名 on 表名

drop index index_address on tb_address

二、索引失效

1.当于表中字段类型为varchar，且值为数字，如果没有带引号，则不会命中索引

【字段虽然为varchar类型但是值为数字，所以带不带引号都不会报错】

表中的id_card为唯一索引

explain select * from   student where id_card = 11

如果有引号，则会命中索引

explain select * from student where id_card = '11'

注意：在当数据类型为int类型时，即使不使用引号也会命中索引

explain select * from student where age = 21

2.使用 like 进行时模糊查找时，%在关键词前

explain select * from student where address like '%山'

%在后边可以命中索引

explain select * from student where address like '山%'

使用模糊查询可以使用 % 也可以使用  _，以下使用下划线的方式进行模糊查询的测试。

当下划线在关键字之前

explain select * from student where address like '_山'

当下划线在关键字之后

explain select * from student where address like '山_'

结论：使用_或%进行模糊查询时，索引命中机制相同

3索引字段作为查询条件时，使用了计算或者函数

字段不进行计算时

explain select * from student where age = 27

字段进行计算时

explain select * from student where age + 1 = 27

4.sql语句中有or

单独查询age时，命中索引

explain select * from student where age = 27 

单独查询name时

explain select * from student where name = '小强'

当二者结合起来时

5.最佳左前缀原则【建立在联合索引之上】

首先创建联合索引（姓名，年龄，地址）

create index merge on student(name,age,address)

首先测试姓名

explain select * from student where name = '小强'

测试年龄

explain select * from student where age = 21

测试地址

explain select * from student where address = '山西'

以下进行组合测试【组合索引中  name为第一个  age 为第二个  address为第三个】

explain select * from student where name = '小李' and age = 21    命中索引

explain select * from student where name = '小李' and address = '山西'    命中索引

 explain select * from student where age = 21 and address = '山西'     未命中索引

得出结论：联合索引中，where后的条件，只要包含联合索引的第一个字段就可以命中索引，否则无法命中。 

 6. is not null

is not null 的测试

explain select * from student where age is not null

 

注意：is null 是可以命中索引的

explain select * from student where age is null