Mysql中索引失效得原因和解决

2022-08-05 12:47:26

前言

在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,
    primarykey(`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

在这里插入图片描述在查找varchar字段status时,虽然我们的sql语句查询不带单引号‘’ 1也可以查得到,但是没有使用索引。

name字段

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

在这里插入图片描述此时我们只创建了一个index_n_st_add的复合索引,测试后发现 只通过name查找还是使用了此索引。索引的key_len为:403

name and status 测试

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

在这里插入图片描述
此时我们在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=‘北京’

在这里插入图片描述此时可以看到key中使用的就是我们创建的 index_n_st_add 这个复合索引。key_len的索引长度为:813

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

2.最左前缀法则

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

复合索引测试:

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

在这里插入图片描述此时可以看到key中使用的就是我们创建的 index_n_st_add 这个复合索引。

打乱顺序测试:

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

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

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

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

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

舍弃name (复合索引的第一列测试)

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

在这里插入图片描述此时我们看到在运行后 key列显示没有使用任何的索引

总结

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

3.范围查询

测试 范围查询status字段

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

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

总结 :

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

4.运算操作

测试 对 name 字段进行截取

1 截取name字段

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

在这里插入图片描述此时我们看到 在对name字段进行截取(运算)操作后 此语句中的复合索引失效

测试对 status字段进行截取

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

在这里插入图片描述此时复合索引使用成功 ,但是仅有name字段使用了索引

总结

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

5.覆盖索引

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

首先,此查询语句不符合最左原则<跳过了name字段> ,按说此语句不会使用我们的复合索引 ,接下来我们看结果
在这里插入图片描述奇怪的是我们看到的结果里面使用了索引,而且key_len的值还是我们测试三个字段都加上后的值:813 。

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

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

https://www.cnblogs.com/happyflyingpig/p/7662881.html

6.or分割

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

在这里插入图片描述此时我们看到,在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 = ‘阿里小店’

此时我们看结果
在这里插入图片描述虽然我们多查了一次表,但是可以看到,id为2(id大的先执行)虽然查询还是没有使用索引(他本来就没有索引) 但是 id为1的查询使用了索引,这样我们查询的效率就会变快(如何判断sql语句的快慢可以去我的博客《关于explain分析sql语句各个列的作用看一下》)

7.like 模糊查询时失效

like模糊查询%在前测试

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

在这里插入图片描述没用到索引;

like模糊查询%在后测试

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

在这里插入图片描述

like模糊查询失效总结

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

like模糊查询失效解决

使用 覆盖索引方式解决

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

在这里插入图片描述此时解决问题

8.in and not in 问题

in测试

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

在这里插入图片描述在 where 后 使用 in 查询,此时索引起作用

not in 测试

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

在这里插入图片描述此时,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

在这里插入图片描述此时使用了索引。

is not null测试

explain select * from tb_seller where address is not null

在这里插入图片描述此时没有使用了索引。

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 = ‘北京’

在这里插入图片描述此时显示我们的sql语句未使用索引,这个是什么问题呢?

我们来看一下tb_seller的记录
在这里插入图片描述可以看到,在address字段中的记录大多都是北京 ,所以当我们在where 后边查找 address=‘北京’时,MySql的优化器认为使用索引定位查询没有检索全表快,所以就放弃使用索引

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

在这里插入图片描述但是现在我们执行:explain select * from tb_seller where address = '西安’此语句时,使用索引就要快很多,所以优化器使用了索引。

单列索引不起效问题总结

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

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

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

在这里插入图片描述此时,使用的是 name字段的索引 ,

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

在这里插入图片描述此时使用了name字段的索引。

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

我们单独运行

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

在这里插入图片描述

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

在这里插入图片描述

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

  • 作者:你看星星很亮
  • 原文链接:https://blog.csdn.net/weixin_56320090/article/details/117088591
    更新时间:2022-08-05 12:47:26