MySQL索引失效和EXPLAIN工具:你建立的索引真的起到作用了吗?

在这里插入图片描述
在我们使用MySQL的过程中,随着数据量越来越大,查询显得有些吃力,这时候就要针对查询就行优化,针对查询优化,通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压能力。在上一篇我们讲了给数据库中的表添加索引,来提高它的查询速度,但是会有另外一种情况出现,那就是我们给表中字段加了索引,但是查询的时候依旧很慢,没有什么变化,这时就是因为建立的索引失效了,今天就来讲一讲索引失效的情况!
在这里插入图片描述
对于查看索引是否失效,MySQL数据库针对每一条SQL语句,提供了一个查看它的执行计划的工具,叫做EXPLAIN,咱们先来了解一下这个工具。首先说一下接下来用到的表,创建的表结构为:

CREATE TABLE `tb_user` (
 `id` BIGINT (20),
 `user_name` VARCHAR (200),
 `user_password` VARCHAR (200),
 `birth` DATETIME ,
 `sex` CHAR (4),
				 `age` int(8),
 `email` VARCHAR (200),
 `mobile` VARCHAR (200),
 `create_date` DATETIME ,
 `update_date` DATETIME ,
 `description` VARCHAR (800)
) ENGINE=INNODB;

创建一个存储函数,向里面插入一百万条数据:

DELIMITER $$
CREATE
 PROCEDURE insert_tb_user()
 BEGIN
 DECLARE i INT;
 SET i=0;
 WHILE i>=0 && i<= 1000000 DO
 INSERT INTO tb_user
 (`id`, `user_name`, `user_password`, `birth`, `sex`, `age`, `email`, `mobile`, `create_date`, `update_date`, `description`) VALUES
 (i,concat('admin',i),concat('admin',i),NOW(),'M',i,'admin@qq.com',concat('12345',i),NOW(),NOW(),concat('超级管理员',i));
 SET i=i + 1;
 END WHILE;
 
 END$$
DELIMITER ;
-- 执行存储函数
call insert_tb_user()

生成的表数据如下:
在这里插入图片描述
利用这个表,我们来介绍一下执行计划和索引失效的情况。

首先来看一下执行计划的结果是什么样的,执行计划的查看,就是在查询语句的前面加上EXPLAIN关键字就可以了:
在这里插入图片描述
对于这些字段的意思,咱们一一来解释:

一、 id,SELECT识别符。

*id相同时,执行顺序由上至下

*如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

二、select_type,查询中每个select子句的类型

*SIMPLE(简单SELECT,不使用UNION或子查询等)

*PRIMARY(子查询中最外层查询)

*UNION(UNION中的第二个或后面的SELECT语句)

*DEPENDENT UNION(UNION中的第二个或后面的SELECT语句)

*UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)

*SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)

*DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)

  • DERIVED(派生表的SELECT, FROM子句的子查询)

  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

三、 table,显示这一步所访问数据库中表名称。

四、type,对表访问方式

*all:Full Table Scan, MySQL将遍历全表以找到匹配的行

*index: Full Index Scan,index与ALL区别为index类型只遍历索引树

*range:只检索给定范围的行,使用一个索引来选择行

*ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

*eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

*const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

*NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、 possible_keys,可能使用的索引。

六、key,实际使用的索引

七、key_len,索引中使用的字节数,可通过该列计算查询中使用的索引的长度

八、ref,列与索引的比较,表示上述表的连接匹配条件

九、rows,估算出结果集行数

十、Extra,MySQL解决查询的详细信息

*Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据

*Using temporary:表示MySQL需要使用临时表来存储结果集,group by ; order by

*Using filesort:包含 order by ,而且无法利用索引完成的排序操作称为“文件排序”

*Using join buffer:该值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。

*Impossible where:这个值强调了where语句会导致没有符合条件的行。

*Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

*No tables used:Query语句中使用from dual 或不含任何from子句

以上就是对EXPLAIN工具的一个介绍,了解了这个工具后,我们结合这个执行工具,来看一看哪些情况会导致这个索引失效!

第一种情况:针对联合索引,是否遵循最左匹配原则;

我们user_name,user_password,mobile建立一个联合索引,如下:
在这里插入图片描述
联合索引其实是建立了三个索引,也就是user_name的索引,user_name,user_password的索引,user_name,user_password,mobile的索引,最左匹配原则的意思,是否在语句中使用了最左建立的索引,也就是user_name的索引,演示一下:
在这里插入图片描述
当我们把user_name的查询条件去掉之后,会是什么情况呢?
在这里插入图片描述
我们会看到索引已经失效了,用的是全表扫描,违背了最左匹配的原则,那么对于查询语句select * from tb_user where user_password=‘admin66666’ and user_name=‘admin66666’ and mobile='1234566666’会用到索引吗?答案如下:
在这里插入图片描述
也是用到了联合索引,这和你条件中写的顺序是没有关系的!

第二种情况:在索引列上做了函数操作,会导致索引失效而导致全表扫描

我们先把那个联合索引删除掉,然后在user_name这一列上建立一个唯一索引:
在这里插入图片描述在这里插入图片描述在这里插入图片描述
先不在索引列上做函数操作,执行计划如下:
在这里插入图片描述
很显然使用了索引,那么索引列进行函数操作呢?例如做一个字符拼接的操作:
在这里插入图片描述
第三种情况:语句中like查询是以%开头,索引会失效变成全表扫描,覆盖索引。

示例如下:
在这里插入图片描述
如果模糊查询不是以%开头的,那么也是可以用到索引的:
在这里插入图片描述
第四种情况:使用is not null 会导致无法使用索引

示例如下:
在这里插入图片描述
第五种情况:查询语句中,如果条件中有or,即使其中有条件带索引也不会使用。要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
在这里插入图片描述
上面的sql语句是可以用到索引的,当我们把and换成or时,就会变成全表扫描:
在这里插入图片描述
这时我们对mobile也加上索引,这条sql语句也就会使用上索引:
在这里插入图片描述在这里插入图片描述
第六种情况:使用不等于(!= 或者<>)的时候,无法使用索引,会导致索引失效

第七种情况:不能使用索引中范围条件右边的列,范围之后索引失效。(< ,> between and)

这些情况就不在进行实际操作了,感兴趣的朋友可以动手操作一下,也许随着MySQL版本的更新迭代,对这些查询语句进行内部优化,一些索引失效的情况就会消失。除了以上这些情况会导致索引失效,还有哪些情况会导致索引失效呢?
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值