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

文章插图

在我们使用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 ，而且无法利用索引完成的排序操作称为“文件排序”