likes
comments
collection
share

MySQL Explain详解+实战

作者站长头像
站长
· 阅读数 42

文章分为3部分，根据自己需求查看。本文MySQL版本：5.7.38

  1. Explain使用
  2. Explain执行计划的每个字段详解
  3. Sql实战分析执行计划

1. Explain使用

MySQL中的Explain语法很简单，只需要在select语句前面加上explain关键字即可。比如现在有一条查询语句

select * from user1;

那么他的执行计划就是

explain select * from user1;

来简单看下输出结果吧

MySQL Explain详解+实战

2. Explain执行结果的字段详解

从第一部分的执行结果可以看到执行结果共有12个字段。下面介绍下12个字段的详细解释

  1. id：Sql可以分为单表查询、多表查询、子查询、联合查询这几种。这里的ID，一般情况下每个ID对应一个SELECT关键字。如果是联表查询，那么一个ID可能重复出现，第一个出现的就是驱动表，第二个出现的是被驱动表

  2. select_type 执行的SQL类型，MySQL将执行语句分为好多种

    1. SIMPLE:单表查询的select_type都是SIMPLE类型，连表查询也是SIMPLE，这是因为MySQL把联表分为两步单表查询。

    2. PRIMARY：如果有嵌套查询，或者大查询（比如UNION）最外层的SELECT（或者第一个SELECT）会被标记为该类型

    3. UNION：使用了UNION关键字就会被标记为该类型，比如下面这个查询

      explain select * from user  union all select * from user;

      MySQL Explain详解+实战

    4. UNION RESULT：一般出现在不带ALL的UNION语句中

    5. SUBQUERY：如果优化器不能把子查询转化为semi-join（半连接）的形式，并且是不想关子查询，查询类型就会是这个。

    6. DEPENDENT SUBQUERY：如果优化器不能把子查询转化为semi-join（半连接）的形式，并且是相关子查询会出现该类型

    7. DEPENDENT UNION再在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION

    8. DERIVED：派生表会出现该类型

      explain select * from (select count(1) from user) as a;

      MySQL Explain详解+实战

    9. MATERIALIZED：对于子查询，优化器采用了物化表的方式与外层查询进行连接查询。

    10. UNCACHEABLE SUBQUERY：一般遇不到

    11. UNCACHEABLE UNION：一般遇不到

  3. table：每个单表查询都要查的那张表就会记录在该字段中

  4. partitions：分区相关的。一般用不到。

  5. type：衡量SQL执行效率的重要指标，执行效率从好到坏依次是：system->const->eq_ref->ref->range->index->all 其中const和eq_ref的意思差不多，都是指用到了唯一索引或者主键。区别是const指的是单表查询。eq_ref指的是非单表查询。

  6. possible_keys：可能会用到的索引（既然都是可能了，也没啥子必要看了）

  7. key：实际用到的索引列

  8. key_len：实际用到的索引列的长度，按字节统计（个人感觉也没啥子大用）

  9. ref：当使用索引查询时（type不是all），这个字段展示的就是与索引列匹配的值是啥。比如

  10. explain select * from user where id = 2;

    MySQL Explain详解+实战

    ref列展示的是const，表示id匹配的是个常量

  11. rows：预计扫描的行数，和索引统计有关。

  12. filtered：（预计扫描的行数/实际结果集的行数）*100%就是该列的值

  13. Extra：比较重要！常见的值有

    1. using index

    2. using index condition

    3. using where

    4. using join buffer (Block Nested Loop)

    5. using filesort：不能容忍！使用了文件排序，效率会很差。一般是由于order by的字段没有索引导致的。

    6. using temporary：不能容忍！使用了临时表，效率会很差。一般是由于group by|distinct|union的字段没有索引导致的。比如下面这个SQL

      explain select distinct username from user ;

如果我们关注SQL的执行效率可以关注type字段，该字段只要不是ALL全表扫描，那就代表使用了索引。

3. 实战SQL

先准备一张表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age__index` (`age`),
  KEY `user_age_birthday_index` (`age`,`birthday`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

表中一个主键ID，一个二级索引age，一个联合索引(birthday,age)，其他列都是普通列

Extra列的实战

Extra中出现的提示信息有的是可以接受的有的不可以接受，一般来说，出现了using temporary与using filesort都是不太好的征兆。因为会涉及到大量的内存&磁盘IO操作。

Extra列using where

MySQL查询是由两个组件完成的：1是服务层；2是存储引擎层。

当extra出现了这个提示，意味着存储引擎层返回的数据需要在服务层过滤。比如下面这个SQL

explain select * from user where username = '11';

因为username不是索引，所以不会被存储引擎过滤掉。会进行全表扫描，然后把数据然会给服务层，服务层进行过滤掉不符合条件的数据。所以这里的Extra会展示using where。以后碰见using where知道咋回事了把，就是服务层进行工作啦呗。

啥？你不知道服务层和存储引擎层是啥？为啥数据不在存储引擎层过滤？推荐看小孩子的书《MySQL是怎样运行的——从根上理解MySQL》。并非广告，只是它对我的帮助确实很大，希望不懂得同学也可以入坑。

Extra列using index

当查询使用到了普通索引就会出现这个提示。比如下面这个SQL

explain select age from user;

你看，我连where条件都没加，怎么走的索引呢？这既是索引存储的知识了。innodb存储引擎会为每个索引建立一颗B+树。上面这个SQL虽然没有过滤条件，但是它只查询age一个字段，刚好这个字段是建了索引的。所以age的数据都存在age_index这个索引树中。无需回表就能完成查询。

出现了该提示意味着SQL走了索引，也是比较好的提示

Extra列using index condition

出现该提示表示innodb使用了索引下推（什么是索引下推自行搜索）

explain select * from user where age>23 and birthday = '12';

这主要是因为表中有一个联合索引(birthday,age)那么查询语句的过滤条件使用了这两个字段就像上面这个SQLwhere age>23 and birthday = '12'，就会出现using index condition的提示。索引下推是MySQL5.6的新功能，默认开启。

有同学疑问了，难道不是using index才对吗？索引就是这俩字段咋还来一个下推？什么是下推？

这要从MySQL5.6之前的版本说起，如果是5.6之前的版本，即使有联合索引并且where条件中使用了联合索引的字段并且满足最左前缀规则，但是MySQL依旧只会使用到第一个字段，然后把数据传到上层（服务层）去筛选数据。这里的下推就是指服务层不再参与联合索引的筛选工作了。把这个工作下推给了存储引擎。

什么？难道这不是存储引擎本就应该做的事吗？这就是一个产品的升级更新了，是产品总会有不足，这个缺点还好现在已经不存在了。而这个特性就是索引下推

Extra列using filesort

当order by中的字段没有索引，但又需要排序时，就会出现该提示，比如下面这个SQL

explain select * from user order by username;

为什么会出现这个提示呢？要知道innodb存储引擎对于索引的存放是顺序存储的，但是没有索引，可就是乱放一通了。就好像字典里的字，如果不给你前面的索引目录，找到一个字得查遍整个字典，这是很耗时的操作。

所以如果出现该提示，一定要优化！

Extra列using temporary

出现该提示是由于group by|distinct|union的字段没有索引导致的，比如下面的SQL

explain select distinct username from user ;
explain select count(1) from user group by username;
-- 这里为了方便，使用user表当两张表用了
explain select * from user  union select * from user;

其中都会涉及到大量的IO操作。是很费力的， 如果出现该提示也一定要优化！

type实战

以下分析还是基于user表

const

第一种：根据主键ID查询user表

explain select * from user where id = 1;

MySQL Explain详解+实战

第二种：根据唯一索引查询user表（这里就不举例了）

eq_ref

explain select * from user a left join user b using(id);

MySQL Explain详解+实战

ref

使用普通索引进行等值查询时type字段就是ref

explain select * from user where age = 1;

MySQL Explain详解+实战

range

使用普通索引进行范围查询时type字段就是ref

explain select * from user where age > 1;

MySQL Explain详解+实战

index

使用到了普通索引，但没办法进行等值匹配或者范围匹配，这种情况通常出现在联合索引中。比如(age_birthday)索引，如果只是用birthday字段进行查询，由于不满足最左前缀规则，所以只能全表扫描索引树（这里的全表扫描指的是扫描索引树哦）

explain select * from user where birthday = '1222';

all

第一种：当列值没有索引就会是全表扫描 第二种：当全表扫描的代价少于使用索引时，也会走全表扫描（数据量少的情况，数据量大的情况下一般都会走索引）

转载自:https://juejin.cn/post/7190595503640281145
评论
请登录