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

2022-12-02 10:33:05 浏览数 (1)

目录标题

  • 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去线上预执行
  • 减少表关联，一般最好不要超过三张表
sql 数据库 云数据库SQLServer

0 人点赞