MySQL EXPLAIN属性分析

素颜马尾好姑娘i 2023-06-28 06:07 22阅读 0赞

**目录**

测试数据准备

EXPLAIN 如何使用？

属性分析

select\_type

type

possible\_key、key

key\_len

ref

rows

filtered

Extra

--------------------

EXPLAIN 语句提供了有关 MySQL 如何执行 SQL 的相关信息，是我们必须掌握的SQL优化神器。

官网对EXPLAIN的解释：[https://dev.mysql.com/doc/refman/5.7/en/explain-output.html][https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]

## 测试数据准备 ##

建表：

DROP TABLE IF EXISTS course;
    CREATE TABLE `course`
    (
        `cid`   int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
        `tid`   int         DEFAULT NULL COMMENT '教师编号',
        `cname` varchar(20) DEFAULT NULL COMMENT '课程名称',
        PRIMARY KEY (cid)
    ) COMMENT = '课程信息表';
    
    DROP TABLE IF EXISTS teacher;
    CREATE TABLE `teacher`
    (
        `tid`   int NOT NULL AUTO_INCREMENT COMMENT '教师编号',
        `tname` varchar(20) DEFAULT NULL COMMENT '教师名字',
        PRIMARY KEY (tid)
    ) COMMENT = '教师信息表';
    
    DROP TABLE IF EXISTS teacher_detail;
    CREATE TABLE `teacher_detail`
    (
        `tdid`  int NOT NULL AUTO_INCREMENT COMMENT '详情编号',
        `tid` int DEFAULT NULL COMMENT '教师编号',
        `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
        PRIMARY KEY (tdid),
        UNIQUE KEY (tid),
        KEY(phone)
    ) COMMENT = '教师详细信息表';

初始化数据 ：

delimiter $$
    DROP PROCEDURE IF EXISTS init;
    create procedure init()
    begin
        declare i int;
        declare id1 int;
        declare id2 int;
        declare id3 int;
        declare id4 int;
        declare id5 int;
        set i = 1;
    
        while i < 5000 do
        set id1 = i;
        set id2 = i + 4;
        set id3 = i + 5;
        set id4 = i + 6;
        set id5 = i + 9;
    
        INSERT INTO `teacher`
        VALUES (id1, CONCAT('老师', id1)),
               (id2, CONCAT('教师', id2)),
               (id3, CONCAT('老师', id3)),
               (id4, CONCAT('老师', id4)),
               (id5, CONCAT('老师', id5));
    
       INSERT INTO `course`
        VALUES (id1, id1,'英语'),
               (id2, id2,'数学'),
               (id3, id3,'物理'),
               (id4, id4,'化学'),
               (id5, id5,'语文');
    
        INSERT INTO `teacher_detail`
        VALUES (id1,id1, (13800000000 + id1)),
               (id2,id2, (13800000000 + id2)),
               (id3,id3, (13800000000 + id3)),
               (id4,id4, (13800000000 + id4)),
               (id5,id5, (13800000000 + id1));
    
        set i = i + 10;
        end while;
    end
    $$
    
    call init();

## EXPLAIN 如何使用？ ##

EXPLAIN 是MySQL 提供的原生关键字，使用非常简单，只需要在目标 SQL 前加上 EXPLAIN 再执行即可。

例如：

EXPLAIN SELECT * FROM teacher;

![20200108142841686.png][]

下面就依次分析返回结果中的属性。

## 属性分析 ##

### id ###

id字段体现了SQL的执行顺序。

id相同时，先执行排在上面的。

EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;

![20200108143755155.png][]

id不同时，先执行id较大的。

EXPLAIN SELECT *
            FROM course c WHERE c.tid = (SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040');

![20200108143823589.png][]

### select\_type ###

select\_type即查询类型，这里只介绍一些常见的类型。

**SIMPLE**：简单查询，不包含子查询与联合查询UNION。

**PRIMARY**：包含子查询SQL中的主查询。

**SUBQUERY**：包含子查询SQL中的内嵌查询。

**DERIVED**：衍生查询，表示在得到最终查询结果之前会用到临时表。

例：

EXPLAIN 
        SELECT cr.cname
        FROM (
        SELECT * FROM course WHERE tid = 2 UNION
        SELECT * FROM course WHERE tid = 3 ) cr;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyMTIzOTQ_size_16_color_FFFFFF_t_70][]

**UNION**：用到了联合查询。

**UNION RESULT**：主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。

### type ###

type代表连接类型，是执行计划中最为重要的属性之一，下面列举最常见的几种type。

效率依次从高到低：system > const > eq\_ref > ref > range > index ≈ all

**system**：表中只有一行记录（等于系统表），const类型的特例。

**const**：使用主键或唯一索引进行比较。

**eq\_ref**：通常出现在多表的 JOIN 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。

例：

EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;

![20200108144913553.png][]

**ref**：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。

例：

EXPLAIN SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040';

![20200108145115688.png][]

**range**：使用索引范围扫描。

例：

EXPLAIN  SELECT * FROM teacher WHERE tid < 1000;

![20200108145348634.png][]

**index**：全索引扫描。一般出现在索引条件区分度不高的场景下，未必比全表扫描快。

例：

EXPLAIN SELECT phone FROM teacher_detail WHERE phone LIKE '1380000%';

![20200108150102737.png][]

**all**：全表扫描。

总结：一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。index、与all 都是需要优化的。

### possible\_key、key ###

可能用到的索引和实际用到的索引。

possible\_key 可以有一个或者多个，可能用到的索引不代表一定会用到。

反过来如果possible\_key 为 null，key 就一定为空吗？

EXPLAIN SELECT phone FROM teacher_detail WHERE phone LIKE '%3800000040%';

![20200108151618935.png][]

当查询条件无法命中索引，但查询结果为索引字段时，就会出现这种情况，这里相对于用到了覆盖索引。

### key\_len ###

索引的长度(使用的字节数)。跟索引字段的类型、长度有关。

### ref ###

这一列显示了在索引列中，查找值所用到的列或常量，常见的有const（常量）、字段名、NULL。

### rows ###

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

### filtered ###

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

例如全表扫描后，只有一条记录满足查询条件，那么 filtered 的值就会比较低。

EXPLAIN SELECT * FROM course WHERE tid = 100;

![20200108153350114.png][]

### Extra ###

执行计划给出的额外的信息说明。

**Using index**：查询使用了覆盖索引，不需要回表。

**Using where**：两种情况：

1.  查询使用了 where 过滤，但没有用到索引；
2.  有索引的过滤，且不需要回表。

**Using index condition**：查询用到索引且需要回表。这里说的回表有可能是为了拿数据，有可能是为了进一步过滤（索引下推，[索引下推官方解释][Link 1]）。

**Using filesort**：不能使用索引来排序，用到了临时文件排序。

**Using temporary**：用到了临时表（内存或磁盘）。例如：

1.  distinct 非索引列；
2.  group by 非索引列；
3.  使用 join 的时候，group by 非驱动表的索引列同样会产生临时表。

[https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[20200108142841686.png]: https://img-blog.csdnimg.cn/20200108142841686.png
[20200108143755155.png]: https://img-blog.csdnimg.cn/20200108143755155.png
[20200108143823589.png]: https://img-blog.csdnimg.cn/20200108143823589.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyMTIzOTQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200108144227705.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyMTIzOTQ=,size_16,color_FFFFFF,t_70
[20200108144913553.png]: https://img-blog.csdnimg.cn/20200108144913553.png
[20200108145115688.png]: https://img-blog.csdnimg.cn/20200108145115688.png
[20200108145348634.png]: https://img-blog.csdnimg.cn/20200108145348634.png
[20200108150102737.png]: https://img-blog.csdnimg.cn/20200108150102737.png
[20200108151618935.png]: https://img-blog.csdnimg.cn/20200108151618935.png
[20200108153350114.png]: https://img-blog.csdnimg.cn/20200108153350114.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html