文章目录

  • 前言
  • 1.单个字段测试
  • varchar字段测试
  • name字段
  • name and status 测试
  • name and status and address 测试
  • 2.最左前缀法则
  • 复合索引测试：
  • 打乱顺序测试：
  • 跳字段测试(测试以name和address字段，跳过status字段)
  • 舍弃name （复合索引的第一列测试）
  • 总结
  • 3.范围查询
  • 测试 范围查询status字段
  • 总结 ：
  • 4.运算操作
  • 测试 对 name 字段进行截取
  • 1 截取name字段
  • 测试对 status字段进行截取
  • 总结
  • 5.覆盖索引
  • 6.or分割
  • or分割失效问题总结
  • or分割失效问题解决
  • 7.like 模糊查询时失效
  • like模糊查询%在前测试
  • like模糊查询%在后测试
  • like模糊查询失效总结
  • like模糊查询失效解决
  • 8.in and not in 问题
  • in测试
  • not in 测试
  • in and not in 总结
  • 9.is NOLL and is NOT NOLL 问题
  • is noll 测试
  • is not null测试
  • is NOLL and is NOT NOLL 总结
  • 10.单列索引使用问题
  • 单列索引不起效问题
  • 单列索引不起效问题总结
  • 多个单列索引一起使用的选择问题
  • 多个单列索引一起使用的选择问题总结


前言

在Mysql的表中定义好索引后，有时我们会遇到索引失效的情况，下边我们就来探究一下索引失效的原因和解决方法

我们以 一下数据库表作为示范 

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','志远教育科技有限公司','志远','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','老闫','老闫','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

1.单个字段测试

首先我们创建一个复合索引

create index index_n_st_add on tb_seller(name,status,address) – 创建名为：index_n_st_add 的索引

varchar字段测试

explain select * from tb_seller where status = 1

mysql 索引不生效 mysql的索引失效_mysql

在查找varchar字段status时，虽然我们的sql语句查询不带单引号‘’ 1也可以查得到，但是没有使用索引。

name字段

explain select * from tb_seller where name= ‘阿里巴巴’;

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_02

此时我们只创建了一个index_n_st_add的复合索引，测试后发现 只通过name查找还是使用了此索引。索引的key_len为：403

name and status 测试

explain select * from select name=‘阿里巴巴’ and status = ‘1’

mysql 索引不生效 mysql的索引失效_mysql_03


此时我们在where 后加了一个 status的条件 发现此时使用的还是index_n_st_add这个复合索引。此时索引的key_len为 ：410

name and status and address 测试

explain select * from tb_seller where name = ‘老闫’ and status =‘1’ and address=‘北京’

mysql 索引不生效 mysql的索引失效_索引_04

此时可以看到key中使用的就是我们创建的 index_n_st_add 这个复合索引。key_len的索引长度为：813

这就是我们的最左前缀法则

2.最左前缀法则

– 最左边原则就是：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

复合索引测试：

explain select * from tb_seller where name = ‘老闫’ and status =‘1’ and address=‘北京’
– 这一句where后边有三个条件，而这些条件的判断顺序和我们定义的复合索引的字段顺序一致，此时这个复合索引使用成功

mysql 索引不生效 mysql的索引失效_索引_04

此时可以看到key中使用的就是我们创建的 index_n_st_add 这个复合索引。

打乱顺序测试：

explain select * from tb_seller where name =‘阿里巴巴’ and address = ‘北京’ and statu = ‘1’

mysql 索引不生效 mysql的索引失效_字段_06


这时我们运行，显示还是使用了索引，所以我们得出结论，where后与顺序无关，只要出现了最左到最右的所以字段即可

跳字段测试(测试以name和address字段，跳过status字段)

explain select * from tb_seller where name = ‘阿里巴巴’ and address=‘北京’

此时我们看执行结果

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_07

此时虽然我们也使用了索引，但是可以看到在key_len中索引的字段长度发生改变，与我们上面只使用where name =‘阿里巴巴’时的key_len索引长度是一样的，依此得出结论。跳过字段后，只有跳字段之前的字段使用了索引，在跳字段后的字段无法使用索引只有 按照顺序的有索引，跳顺序的没有 <如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：>

舍弃name （复合索引的第一列测试）

explain select * from tb_seller where status = ‘1’ and address = ‘北京’

mysql 索引不生效 mysql的索引失效_索引_08

此时我们看到在运行后 key列显示没有使用任何的索引

总结

最左原则就是where后要按照顺序来，只有一个个的复合才可以接着往后边匹配，中间跳过字段的话只有跳字段的左边有效，这个顺序是按照创建索引时定义字段的顺序来定，不出现第一个字段索引 ，后面的两个都会失效 。

3.范围查询

测试 范围查询status字段

explain select * from tb_seller where name = ‘阿里巴巴’ and status >0 and address = ‘北京’

mysql 索引不生效 mysql的索引失效_mysql_09

此时我们可以看到，在status地段执行了范围查询后 ，address字段就使用不了索引了 key_len的索引长度为410 ，与 只查询 name and status 两个字段是一样的效果，

总结 ：

在where后对某一字段进行范围查询后，进行范围查询后面的字段就不能使用索引了。

4.运算操作

测试 对 name 字段进行截取

1 截取name字段

explain select * from tb_seller where substring(name,0,2) = ‘小米’;

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_10

此时我们看到 在对name字段进行截取（运算）操作后 此语句中的复合索引失效

测试对 status字段进行截取

explain select * from tb_seller where name = ‘阿里巴巴’ and substring(status,0,1) = ‘0’ and address =‘北京’

mysql 索引不生效 mysql的索引失效_数据库_11

此时复合索引使用成功 ，但是仅有name字段使用了索引

总结

在where后对某一个字段使用运算操作后 ，此字段后的所有字段都不会使用索引。

5.覆盖索引

explain select name ,status , address from tb_seller where status=‘1’

首先，此查询语句不符合最左原则<跳过了name字段> ，按说此语句不会使用我们的复合索引 ，接下来我们看结果

mysql 索引不生效 mysql的索引失效_字段_12

奇怪的是我们看到的结果里面使用了索引，而且key_len的值还是我们测试三个字段都加上后的值：813 。

这里面就涉及到了覆盖索引，因为我们要select的值也正好在这个联合索引树中，所以mysql会直接扫描这个联合索引树，也就是我们select后的值都是索引列，此时就会使用索引

关于覆盖索引我们这边就不细讲了，有兴趣的童鞋就去这个网址看一下哦


6.or分割

explain select * from tb_seller where name = ‘阿里巴巴’ or nickname=‘阿里小店’

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_13

此时我们看到，在possible_keys（可能会使用到的索引）中显示可以会使用到index_n_st_add 这一索引 ，但是在key（实际使用到的索引）中显示为空，也就是没有使用到索引。而name字段是有索引的。为什么没有使用到呢？

or分割失效问题总结

用or分割开的条件，如果前面的条件列有索引，但是or后边的列没有索引，那么前面的列的索引也不会被使用。

or分割失效问题解决

使用 union 语句

explain select * from tb_seller where name = ‘阿里巴巴’ union select * from tb_seller where nickname = ‘阿里小店’

此时我们看结果

mysql 索引不生效 mysql的索引失效_字段_14

虽然我们多查了一次表，但是可以看到，id为2（id大的先执行）虽然查询还是没有使用索引（他本来就没有索引） 但是 id为1的查询使用了索引，这样我们查询的效率就会变快（如何判断sql语句的快慢可以去我的博客《关于explain分析sql语句各个列的作用看一下》）

7.like 模糊查询时失效

like模糊查询%在前测试

explain select * from tb_seller where name like ‘%米’ ；

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_15

没用到索引；

like模糊查询%在后测试

explain select * from tb_seller where name like ‘小%’;

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_16

like模糊查询失效总结

在where后·使用like时 %在前时索引失效 ，当%在后面时索引使用成功

like模糊查询失效解决

使用 覆盖索引方式解决

explain select name , status ,address where name like ‘%小米%’

mysql 索引不生效 mysql的索引失效_索引_17

此时解决问题

8.in and not in 问题

in测试

explain select * from tb_seller where name in (‘阿里巴巴’,‘千度科技’)

mysql 索引不生效 mysql的索引失效_字段_18

在 where 后 使用 in 查询，此时索引起作用

not in 测试

explain select * from tb_selller where name not in(‘阿里巴巴’,‘千度科技’)

mysql 索引不生效 mysql的索引失效_mysql_19

此时，not in 显示未使用索引

in and not in 总结

在where 后使用 in 可以使用索引，但是使用 not in 不能使用索引。

9.is NOLL and is NOT NOLL 问题

is noll 测试

explain select * from tb_seller where address is null

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_20

此时使用了索引。

is not null测试

explain select * from tb_seller where address is not null

mysql 索引不生效 mysql的索引失效_索引_21

此时没有使用了索引。

is NOLL and is NOT NOLL 总结

is null 会使用索引 ， is not null 不会使用索引。

10.单列索引使用问题

还是原来的表结构 。 我们删除符合索引并且为每个字段都加入单列索引

– 1. 删除符合索引
drop index index_n_st_add on tb_seller ;
– 2.创建单列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

单列索引不起效问题

explain select * from tb_seller where address = ‘北京’

mysql 索引不生效 mysql的索引失效_索引_22

此时显示我们的sql语句未使用索引，这个是什么问题呢？我们来看一下tb_seller的记录

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_23

可以看到，在address字段中的记录大多都是北京 ，所以当我们在where 后边查找 address=‘北京’时，MySql的优化器认为使用索引定位查询没有检索全表快，所以就放弃使用索引

explain select * from tb_seller where address = ‘西安’

mysql 索引不生效 mysql的索引失效_数据库_24

但是现在我们执行：explain select * from tb_seller where address = '西安’此语句时，使用索引就要快很多，所以优化器使用了索引。

单列索引不起效问题总结

如果MySQL评估使用索引比全表更慢，则不使用索引。（看有没有必要，有需要就用，没必要不用，像个渣男）

多个单列索引一起使用的选择问题

explain select * from tb_seller where name = ‘阿里巴巴’ and status = ‘1’

mysql 索引不生效 mysql的索引失效_数据库_25

此时，使用的是 name字段的索引 ，

explain select * from tb_seller where name = ‘阿里巴巴’ and address = ‘北京’

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_26

此时使用了name字段的索引。

多个单列索引一起使用的选择问题总结

我们单独运行

explain select * from tb_seller where address = ‘北京’

mysql 索引不生效 mysql的索引失效_数据库_27

explain select * from tb_seller where name = ‘阿里巴巴’

mysql 索引不生效 mysql的索引失效_mysql 索引不生效_28

发现问题：
当我们单独运行where后边只有一列值索搜索时发现，key_len的索引长度不同，对比我们上边在where后写了两个判断语句的运行结果发现，当我们运行多个单列索引字段时，key_len的长度越小代表辨识度越高。优化器会选择辨识度高的单值索引来使用