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版本的更新迭代，对这些查询语句进行内部优化，一些索引失效的情况就会消失。除了以上这些情况会导致索引失效，还有哪些情况会导致索引失效呢？