掌握Explain分析性能瓶颈、避免索引失效

目录标题

• EXPLAIN
  • SELECT_TYPE
  • TABLE
  • TYPE (访问类型)
  • possible_keys
  • key
  • Extra (附加信息)
• 索引失效常见问题
• 怎么去避免索引的失效

有个面试题分享给大家： 创建了组合索引 （ A , B , C） 查询条件where C =1 AND A =1 AND B >1,是否用到索引呢？怎么证明？ 有关索引介绍及详解，可以参考我的一篇博客： 链接: MySQL索引详解及演进过程以及延申出面试题（别再死记硬背了，跟着我推演一遍吧）

EXPLAIN

当我们执行查询语句时，在前面加上EXPLAIN便可以查看本次执行的相关信息

在这里插入图片描述

里面的字段都有哪些值，分别是都是啥意思呢？

SELECT_TYPE

1. SIMPLE （简单SELECT, 不使用UNION 或者子查询）
2. PRIMARY (子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
3. UNION(UNION 中的第二个或者后面的SELECT语句)
4. DEPENDENT UNION(子查询中的 UNION 查询第二个或后面的SELECT 语句，取决于外面的查询)
5. UNION RESULT(UNION的结果，UNION语句中的第二个SELECT 开始后面的所有SELECT)
6. SUBQUERY/MATERIALIZED (子查询中使用 = 和IN的区别，= 是SUBQUERY、 IN是METARIALIZED )
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
8. DERIVED(派生表的SELECT * FROM (SELECT…)子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行 )
10. UNCACHEABLE UNION (一个子查询的 UNION 属于不可缓存子查询)

TABLE

这个其实是 表名 或者显示的别名

TYPE (访问类型)

const：使用了主键索引，通常只会匹配一行，这种性能是非常高的。 ref：使用了二级索引，如果查询只是用到了最左匹配原则，查询条件列是使用了索引，但是不是使用了主键索引或者是唯一索引 range：使用了索引检索一个范围的数据，意味着这个级别已经走了索引，一般能的达到这个级别就OK。 index：全索引扫描 all：全表扫描 从上往下性能越来越差，我们程序员对于上线的SQL，起码要保证 range，不能保证就建索引，能命中就命中

possible_keys

显示这张表中可能用到的索引

key

我们实际用到的索引，如果为NULL 就没有用到索引

Extra (附加信息)

1. Using where 使用了where 过滤
2. Using index 使用了索引，主键索引或者二级索引
3. using filesort 查询用到了索引，但是排序没有用到索引
4. using index condition 索引下推 使用了二级索引，但是我们需要回表去查询数据 索引下推以及回表的概念可以参考我的另一篇文章： 链接: MySQL 的回表、覆盖索引、索引下推

这些字段中，type，key，extra尤为重要

有时候我们设置了索引，但是查询却不走索引，这是为啥？ 这里我总结了常见的原因：

索引失效常见问题

1. 索引列上发生了类型转换比如 IDCARD = ‘44538120010690232’ 实际传递了44538120010690232，出现了隐式或者强制转换。
2. 索引列上发生了计算比如：SELECT id FROM TABLE WHERE AGE + 1 = 18。
3. 索引列上使用了系统函数，比如 WHERE REMARK IS NULL 等等。
4. 索引列上使用了范围查询，比如 > < = != between or 等等。
5. 索引列上使用了Like 百分号前置，比如 like ‘%xxx’。
6. 比如联合索引桥，最左匹配原则，其实就是索引桥原理，联合索引是根据我们创建索引的顺序去决定的，从左到右行成索引桥，假如ABC 你需要命中A 再命中B 再命中C，不可以跳过A 去命中BC，同理不能跳过AB去命中C。如果有任何一个索引使用了范围查询会导致当前列后面的索引失效，如果使用了like 百分号前置会导致当前索引列名和之后的索引失效。

索引失效其实是由于我们索引树存储数据的方式去决定的，使用了某些系统函数，或者是在索引列上做计算，会导致表扫描，使得我们没办法命中我们的索引树，至于到底是否失效，这个跟数据库版本，表内数据的具体情况由我们的的优化器去决定的，我们说了不算，要具体问题，具体分析。

怎么去避免索引的失效

• 建立合适的索引
• 离散度低的列 不要建立索引，或者是频繁更新修改的列不要建立索引
• 尽量建立联合索引，减少索引树，优先建立经常查询数据列权重较高的放前面，与order by 经常用的列名。
• 尽量使用覆盖索引，减少回表，求你了别写 SELECT *
• 预执行，拿不准的情况，拿SQL去线上预执行
• 减少表关联，一般最好不要超过三张表