您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

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

时间:2019-09-07 11:27:53  来源:  作者:
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()

生成的表数据如下:

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

 

利用这个表,我们来介绍一下执行计划和索引失效的情况。

首先来看一下执行计划的结果是什么样的,执行计划的查看,就是在查询语句的前面加上EXPLAIN关键字就可以了:

MySQL索引失效和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建立一个联合索引,如下:

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

 

联合索引其实是建立了三个索引,也就是user_name的索引,user_name,user_password的索引,user_name,user_password,mobile的索引,最左匹配原则的意思,是否在语句中使用了最左建立的索引,也就是user_name的索引,演示一下:

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

 

当我们把user_name的查询条件去掉之后,会是什么情况呢?

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

 

我们会看到索引已经失效了,用的是全表扫描,违背了最左匹配的原则,那么对于查询语句select * from tb_user where user_password='admin66666' and user_name='admin66666' and mobile='1234566666'会用到索引吗?答案如下:

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

 

也是用到了联合索引,这和你条件中写的顺序是没有关系的!

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

我们先把那个联合索引删除掉,然后在user_name这一列上建立一个唯一索引:

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

 


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

 


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

 

先不在索引列上做函数操作,执行计划如下:

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

 

很显然使用了索引,那么索引列进行函数操作呢?例如做一个字符拼接的操作:

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

 

第三种情况:语句中like查询是以%开头,索引会失效变成全表扫描,覆盖索引。

示例如下:

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

 

如果模糊查询不是以%开头的,那么也是可以用到索引的:

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

 

第四种情况:使用is not null 会导致无法使用索引

示例如下:

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

 

第五种情况:查询语句中,如果条件中有or,即使其中有条件带索引也不会使用。要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

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

 

上面的sql语句是可以用到索引的,当我们把and换成or时,就会变成全表扫描:

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

 

这时我们对mobile也加上索引,这条sql语句也就会使用上索引:

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

 


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

 

第六种情况:使用不等于(!= 或者<>)的时候,无法使用索引,会导致索引失效

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

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



Tags:MySQL索引   点击:()  评论:()
声明:本站部分内容来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除,谢谢。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述随着电商的发展,使用数据库的业务越来越复杂,除了掌握哪些场景可以使用索引,哪些场景适合使用索引,还需要掌握索引在运行过程中的一些使用规则,特别是组合索引的使用。比如索...【详细内容】
2020-07-06   MySQL索引  点击:(2)  评论:(0)  加入收藏
1 初识索引索引在MySQL中也叫是一种“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发...【详细内容】
2019-11-18   MySQL索引  点击:(35)  评论:(0)  加入收藏
概述在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页...【详细内容】
2019-09-12   MySQL索引  点击:(36)  评论:(0)  加入收藏
在我们使用MySQL的过程中,随着数据量越来越大,查询显得有些吃力,这时候就要针对查询就行优化,针对查询优化,通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压...【详细内容】
2019-09-07   MySQL索引  点击:(1)  评论:(0)  加入收藏
1 初识索引索引在MySQL中也叫是一种“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发...【详细内容】
2019-09-05   MySQL索引  点击:(9)  评论:(0)  加入收藏
索引是一种用于快速查询行的数据结构,就像一本书的目录就是一个索引,如果想在一本书中找到某个主题,一般会先找到对应页码。在mysql中,存储引擎用类似的方法使用索引,先在索引中...【详细内容】
2019-09-05   MySQL索引  点击:(162)  评论:(0)  加入收藏
索引目的索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把...【详细内容】
2019-08-29   MySQL索引  点击:(61)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条