总结：mysql索引失效的一些情况

首先，执行以下sql语句，生成测试所需要的数据，以方便验证索引失效的情况。

create table table_test(
id int(4) primary key auto_increment,
c1 char(10) not null,
c2 char(10) not null,
c3 char(10) not null,
c4 int(4) not null 
);

insert into table_test(c1, c2, c3, c4) values ('11one','12','13',14); 
insert into table_test(c1, c2, c3, c4) values ('21two','22','23',24); 
insert into table_test(c1, c2, c3, c4) values ('31three','32','33',34);
insert into table_test(c1, c2, c3, c4) values ('41four','42','43',44);
insert into table_test(c1, c2, c3, c4) values ('51five','52','53',54);
insert into table_test(c1, c2, c3, c4) values ('51six','52','53',64);

create index c1_c2_c3 on table_test(c1, c2, c3);
show index from table_test;

以上sql执行后，查询所有数据，结果如下图所示：

 然后，再查询该表的所有索引，结果如下图所示：

 其中，c1_c2_c3是复合索引。

1.like后面的字段以%开头，索引无效；不以%开头，以%结尾则索引有效

执行以下sql语句：

explain select * from table_test where c1 like '%one';

 结果如下图所示：

 由截图所示结果可知，like后面的字段以%开头，索引无效。

再执行如下sql语句：

explain select * from table_test where c1 like '11%';

结果如下图所示：

  由截图所示结果可知，like后面的字段不以%开头，但是以%结尾，索引有效。

2.or语句前后没有同时使用索引，则索引无效

执行如下sql语句，在该语句中，c1为索引，c4不为索引：

 explain select * from table_test where c1='11one' or c4=24;

执行结果如下图所示：

 由结果可知，or语句前后没有同时使用索引，则索引无效。

执行如下sql语句，在该语句中or的前后同时使用了复合索引字段c1和c2：

 explain select * from table_test where c1='11one' or c2='22';

执行结果如下图所示：

 由截图可知，当or左右的字段都使用了复合索引的字段时，索引失效。

执行如下sql语句，在该语句中or的前后同时使用了复合索引字段c1和主键id：

explain select * from table_test where c1='11one' or id='2';

执行结果如下图所示：

 由截图可知，当or左右的字段都使用了索引时，索引是有效的，注意的是or左右字段如果都是同一个复合索引的字段时，则索引是无效的。

3.字符串不加单引号时，索引无效

执行以下sql语句，向table_test中插入一条数据：

insert into table_test(c1, c2, c3, c4) values ('6111','52','53',64);

 查询表中所有的数据，结果如下图所示：

执行如下sql语句，在该语句中使用复合索引的字段c1，但是字符串没有加单引号：

 explain select * from table_test where c1=6111;

执行结果如下图所示：

 由截图可知，字符串不加单引号时，索引无效。

4.组合索引时，不使用第一列索引，则索引无效

执行如下sql语句，在该语句中使用复合索引的字段c2，没有使用复合索引c1_c2_c3的第一列字段：

explain select * from table_test where c2='12';

执行结果如下图所示：

 5.组合索引时，使用了第一列索引，但是后续索引不连续，则后续索引失效

 explain select * from table_test where c1='11one' and c3='13';

然后再只使用第一列索引，

 explain select * from table_test where c1='11one';

执行结果如下图所示：

由截图可知，两条语句的key_len都是10；

第一条语句中，虽然使用了c3的字段，但是由于没有使用c2字段，c3字段并没有生效，但是c1是生效的。

6.在索引列上使用is null 或者 is not null，索引不一定失效

执行以下sql语句，在该语句中，使用了c1这个索引列，而且使用了is null：

explain select * from table_test where c1 is null;

执行结果如下图所示：

 由截图可知，当索引列中的数据都为非null时，在索引列上使用is null索引是失效的。

下面，看一下索引不失效的情况，首先执行以下sql，向table_test表插入一条c1列为null的数据：

alter table table_test modify c1 char(10) default null;
insert into table_test(c2, c3, c4) values ('62', '63', '64');

执行后，查询table_test表中的数据，结果如下图所示：

 可以看到id为8的数据行，c1的值为NULL，接下来执行以下sql语句：

 explain select * from table_test where c1 is null;

执行结果如下图所示：

 由截图中的查询结果可以知道，在c1列中有为NULL的记录时，使用is null作为where的条件，索引没有失效。

综上，在索引列上使用is null 或者 is not null，索引不一定失效。

7.在索引字段上使用not，<>，!=，索引失效

对于not，执行以下sql语句：

explain select * from table_test where c1 not in('11one','21two','31three','41four');

执行结果，如下图所示：

 由截图可知，索引失效。

对于<>，执行以下sql语句，建立索引c4：

create index c4 on table_test(c4);

 执行以下sql语句，验证<>是否失效：

 由截图中的结果可知，在索引列上使用<>时，索引不生效。

对于 !=，执行以下sql语句：

 explain select * from table_test where c4 != 44;

 执行结果如下图所示：

 由截图可知，在索引列上使用!=，索引失效。

8.在索引列上使用函数或进行计算操作，索引失效

执行以下sql语句：

explain select * from table_test where c4/2 = 7;

 执行结果如下图：

 由截图结果可知，在索引列上使用函数或进行计算操作，索引失效。

结语

以上便是mysql中索引失效的情况，希望能够帮到需要的同学！