【MySQL】性能调优（四）：SQL。EXPLAIN 执行计划详解

系统管理员 2022-11-22 12:44 385阅读 0赞

使用 EXPLAIN 关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈，另外

*  explain extend + show warnings：可以查看mysql优化后的sql语句
 *  explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

在看执行计划前，我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（三张表没有任何索引）：

-- 课程表
    DROP TABLE IF EXISTS course;
    CREATE TABLE `course`(
        `cid` int(3) DEFAULT NULL,
        `cname` varchar(20) DEFAULT NULL, 
        `tid` int(3) DEFAULT NULL  
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- 老师表
    DROP TABLE IF EXISTS teacher; 
    CREATE TABLE `teacher`(
        `tid` int(3) DEFAULT NULL,
        `tname` varchar(20) DEFAULT NULL, 
        `tcid`int(3) DEFAULT NULL 
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- 老师联系方式表
    DROP TABLE IF EXISTS teacher_contact; 
    CREATE TABLE `teacher_contact`(
        `tcid` int(3) DEFAULT NULL, 
        `phone` varchar(200) DEFAULT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `course` VALUES('1','mysql','1');
    INSERT INTO `course` VALUES('2','jvm','1');
    INSERT INTO `course` VALUES('3','juc','2');
    INSERT INTO `course` VALUES('4','spring','3');
    
    INSERT INTO `teacher` VALUES('1','zhangsan','1'); 
    INSERT INTO `teacher` VALUES('2','lisi','2'); 
    INSERT INTO `teacher` VALUES('3','wangwu','3');
    
    INSERT INTO `teacher_contact` VALUES('1','13688888888'); 
    INSERT INTO `teacher_contact` VALUES('2','18166669999'); 
    INSERT INTO `teacher_contact` VALUES('3','17722225555');

explain的结果有很多的字段，下面的篇幅我们会逐一分析。先确认一下环境：

select version(); 
    show variables like '%engine%';

## 1.id：查询序列编号 ##

**1.id值不同**

id值不同的时候，先查询id值大的（先大后小）。

-- 查询 mysql 课程的老师手机号 
    EXPLAIN SELECT tc.phone FROM teacher_contact tc WHERE tcid=(
        SELECT tcid FROM teacher t WHERE t.tid=(
            SELECT c.tid FROM course c WHERE c.cname='mysql'
        )
    );

查询顺序：course c --> teacher t --> teacher\_contact tc。

![在这里插入图片描述][2020110414180439.png_pic_center]

**2.id值相同**

-- 查询课程 ID 为 2，或者联系表 ID 为 3 的老师
    EXPLAIN SELECT t.tname, c.cname, tc.phone 
    FROM teacher t,course c,teacher_contact tc 
    WHERE t.tid=c.tid AND t.tcid=tc.tcid AND(c.cid=2 OR tc.tcid=3);

![在这里插入图片描述][20201104141848196.png_pic_center]

id 值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的id都是1，查询的顺序是：teacher t（3条）–> course c（4条）–> teacher\_contact tc（3条）。

现在给teacher表插入3条数据，然后再执行上面的查询语句

INSERT INTO `teacher` VALUES(4,'james',4); 
    INSERT INTO `teacher` VALUES(5,'tom',5); 
    INSERT INTO `teacher` VALUES(6,'seven',6); 
    
    -- 测试完后删除
    DELETE FROM teacher where tid in(4,5,6);

![在这里插入图片描述][20201104141904925.png_pic_center]

id 也都是1，但是从上往下查询顺序变成了： teacher\_contact tc（3条）–> teacher t（6条）–> course c（4条）。

> 为什么数据量不同的时候顺序会发生变化呢？这个是由笛卡尔积决定的。
> 
> 举例：假如有a、b、c三张表，分别有2、3、4条数据，如果做三张表的联合查询，当查询顺序是 a→b→c 的时候，它的笛卡尔积是：`2*3*4=6*4=24`。如果查询顺序是 c→b→a，它的笛卡尔积是`4*3*2=12*2=24`
> 
> 因为MySQL要把查询的结果，包括中间结果和最终结果都保存到内存，所以MySQL会优先选择中间结果数据量比较小的顺序进行查询。所以最终联表查询的顺序是a→b→c。这个就是为什么teacher表插入数据以后查询顺序会发生变化。（小表驱动大表的思想）

如果ID有相同也有不同，就是ID不同的先大后小，ID相同的从上往下。

## 2.select type：查询类型 ##

下面列举了一些常见的查询类型：

**1.简单查询**

SIMPLE，简单查询，不包含子查询，不包含关联查询union。

EXPLAIN SELECT * FROM teacher;

![在这里插入图片描述][20201104141928679.png_pic_center]

**2.子查询型**

*  PRIMARY：子查询SQL语句中的主查询，也就是最外面的那层查询。
 *  SUBQUERY：子查询中所有的内层查询都是SUBQUERY类型的。where后的子查询
 *  DERIVED：MySQL 会将结果存放在一个临时表中，也称为派生表。from后的子查询

-- 查询 mysql 课程的老师手机号 
    EXPLAIN SELECT tc.phone 
    FROM (SELECT * FROM teacher_contact) tc 
    WHERE tcid=(
        SELECT tcid FROM teacher t WHERE t.tid=(
            SELECT c.tid FROM course c WHERE c.cname='mysql'
        )
    );

![在这里插入图片描述][20201104141948172.png_pic_center]

**3.并查询型**

*  UNION：用到了UNION查询（对于关联查询，先执行右边的 table（UNION），再执行左边的 table）
 *  UNIONRESULT：主要是显示哪些表之间存在UNION 查询。<union2,3>代表 id=2 和id=3 的查询存在UNION

-- 查询 ID 为 1 或 2 的老师教授的课程 
    EXPLAIN SELECT cr.cname 
    FROM( 
        SELECT * FROM course WHERE tid=1 
    UNION 
        SELECT * FROM course WHERE tid=2
    )cr;

![在这里插入图片描述][2020110414200640.png_pic_center]

> 这里并没有列举全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

## 3.table：查询的表 ##

访问的哪个表，注意以下三种特殊情况：

*  `<derivenN>`：当 from 子句中有子查询时，表示当前查询依赖 id=N 的查询
 *  `<union1,2>`：当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id
 *  NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

![在这里插入图片描述][20201104142029582.png_pic_center]

## 4.type：连接类型 ##

在常用的链接类型中：system > const > eq\_ref > ref > range > index > all。以上访问类型除了all，都能用到索引。一般保证达到range，最好是ref。

-- 为teacher表的tcid（第三个字段）创建普通索引。
    ALTER TABLE teacher ADD INDEX idx_tcid(tcid);
    
    -- 为 teacher_contact 表的 tcid（第一个字段）创建一个索引。
    ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

**1.const**

主键索引或者唯一索引，只能查到一条数据的SQL。

-- 查询id为1的teacher联系方式
    EXPLAIN SELECT * FROM teacher_contact WHERE tcid=1;

![在这里插入图片描述][20201104142041680.png_pic_center]  
**2.system**

system 是 const 的一种特例。表中只有一行数据，且使用唯一索引（primary key / unique key）查询。

EXPLAIN SELECT * FROM (SELECT * FROM `teacher_contact` where tcid=1)tmp;

![在这里插入图片描述][20201104142300533.png_pic_center]

**3.eq\_ref**

通常出现在多表的联查（join），primary / unique key被作为连接部分（on），所以对于前表的每一个结果，都只能匹配到后表的一行结果。

EXPLAIN select t.tcid from teacher t LEFT JOIN teacher_contact tc ON t.tcid=tc.tcid;

![在这里插入图片描述][20201104142319828.png_pic_center]

简单查询不会出现该类型。eq\_ref是除const之外最好的访问类型。

> 小结：以上三种system，const，eq\_ref，都是可遇而不可求的，基本上很难优化到这个状态。

**4.ref**

使用非唯一索引查询，或者关联操作只使用了索引的最左前缀，可能匹配到多行。

-- 使用tcid上的普通索引查询
    explain SELECT * FROM teacher where tcid=3;

![在这里插入图片描述][20201104142339350.png_pic_center]

**5.range**

索引范围扫描。如果where后面是 between and 或 <或 > 或 >= 或 <=或in这些， type类型就为range。

EXPLAIN SELECT * FROM teacher_contact WHERE tcid>=1;
    EXPLAIN SELECT * FROM teacher_contact WHERE tcid BETWEEN 1 AND 2;
    EXPLAIN SELECT * FROM teacher_contact WHERE tcid IN (1,2,3);

![在这里插入图片描述][20201104142352330.png_pic_center]

**6.index**

扫描全表索引（Full Index Scan)，这通常比ALL快一些。（index是从(*覆盖*)索引中读取的，而all是从硬盘中读取）

EXPLAIN SELECT tcid FROM teacher;

![在这里插入图片描述][20201104142406901.png_pic_center]

**7.ALL**

Full Table Scan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。

EXPLAIN SELECT * FROM teacher;

![在这里插入图片描述][20201104142438747.png_pic_center]

**8.Null**

不用访问表或者索引就能得到结果，例如：

EXPLAIN select 1 from dual where 1=1;

![在这里插入图片描述][20201104142449180.png_pic_center]

> 一般来说，需要保证查询至少达到range级别，最好能达到ref。ALL（全表扫描）和index（查询全部索引）都是需要优化的。

## 5.possible\_keys、key、key\_len：索引信息 ##

*  possible\_keys: 可能使用的索引
 *  keys: 实际使用的索引，可能出现由于数据较少possible\_keys有值，keys=NULL，因为mysql内部优化为全表扫描
 *  key\_len：索引的长度（使用的字节数）。
    
     *  跟索引字段的类型、长度有关
     *  对于联合索引而言，可以推算出使用了什么索引列

-- 给Course表创建一个联合索引
    ALTER TABLE course ADD INDEX idx_cname_tid(cname,tid);

EXPLAIN SELECT * FROM course WHERE tid=1;

![在这里插入图片描述][20201104142501138.png_pic_center]

key\_len是5，这个5怎么来的呢？

- 字符串:
      - char（n）：n
      - varchar（n）：3n+2 （2字节用来保存长度）
    - 数值类型:
      - tinyint：1
      - smallint：2
      - int：4
      - bigint：8
    - 时间类型:
      - date：3
      - timestamp：4
      - datatime：8
    - 若允许NULL还有一位用来记录

> 注：如果数值类型不采用默认长度，而是自己指定了长度，就需要2个字节保存。所以上面的5=指定的3+保存长度的2。

## 6.ref、rows ##

*  ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
 *  rows：读取并检测的行数，不是结果集数。是一个预估值，一般来说行数越少越好。

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

## 7.Extra：额外信息 ##

**1.Using index**

覆盖索引 + 相应索引查找

EXPLAIN SELECT tcid FROM teacher;

![在这里插入图片描述][20201104142514151.png_pic_center]

**2.Using where**

无覆盖索引 + 无索引查找 / 所用索引非前导列

EXPLAIN SELECT * FROM teacher WHERE tname='张三';

![在这里插入图片描述][20201104142524683.png_pic_center]

**3.Using index；Using where**

覆盖索引 + 无索引查找（其实是[索引下推][Link 1]）

EXPLAIN SELECT tid FROM course WHERE cname='jvm';

![在这里插入图片描述][20201104142534242.png_pic_center]

**4.NULL**

无覆盖索引 + 索引查找

EXPLAIN SELECT * FROM teacher WHERE tcid=1;

![在这里插入图片描述][20201104142548504.png_pic_center]

**5.Using index condition**

联合索引下，只使用了部分索引列

EXPLAIN SELECT * FROM course WHERE tid>1;

![在这里插入图片描述][20201104142600244.png_pic_center]

**6.Using temporary**

创建了临时表。一般需要索引去优化，例如创建复合索引。

EXPLAIN select DISTINCT(tid) from teachert; -- distinct 非索引列
    EXPLAIN select tname from teacher group by tname; -- group by 非索引列
    EXPLAIN select t.tid from teacher t join course c on t.tid=c.tid group by t.tid; -- 使用join的时候，group任意列

![在这里插入图片描述][20201104142629996.png_pic_center]

**7.Using filesort**

不能使用索引来排序，数据都查出来后排序，用到了额外的排序（跟磁盘或文件没有关系）。需优化。

EXPLAIN select * from teacher order by(tname);

![在这里插入图片描述][20201104142640724.png_pic_center]

> 总结一下：模拟优化器执行SQL查询语句的过程， 来知道MySQL是怎么处理一条SQL语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。分析出问题之后，就是对SQL语句的具体优化。比如怎么用到索引，怎么减少锁的阻塞等待…

[2020110414180439.png_pic_center]: /images/20221120/62f4885d762449ba83d0e00ee1e015a4.png
[20201104141848196.png_pic_center]: /images/20221120/91cb5bdb12af4be9a155ecc3db742906.png
[20201104141904925.png_pic_center]: /images/20221120/7e12a3a575b7472b912082bd5195dab3.png
[20201104141928679.png_pic_center]: /images/20221120/55f51e9aaabf4d63b7a8262e6ed02f23.png
[20201104141948172.png_pic_center]: /images/20221120/90b8b1408518479cb5b2a4f51f18e728.png
[2020110414200640.png_pic_center]: /images/20221120/7a555344caf34489868f2e9dc0e6daec.png
[20201104142029582.png_pic_center]: /images/20221120/f8a7e92e38664b23a42dc0f52bdb60ee.png
[20201104142041680.png_pic_center]: /images/20221120/1367a4ffd30c466bb4007499826b7ec0.png
[20201104142300533.png_pic_center]: /images/20221120/4c293a82eb40494c9e3dbb2af21fdbcb.png
[20201104142319828.png_pic_center]: /images/20221120/11c040d6258a42b0b51d6e7c88e6c618.png
[20201104142339350.png_pic_center]: /images/20221120/b880c4fea99e4f4d87715f5b4684841f.png
[20201104142352330.png_pic_center]: /images/20221120/985f88f93b43488995bc48ecfcc071b1.png
[20201104142406901.png_pic_center]: /images/20221120/4e844be5adf54d8dba7d761e30708700.png
[20201104142438747.png_pic_center]: /images/20221120/4c8c54951471409dbd3efbf266c38e92.png
[20201104142449180.png_pic_center]: /images/20221120/9f439f0adaed4a799efa536909b375ad.png
[20201104142501138.png_pic_center]: /images/20221120/fa5bb350de2641ea8aef8fa09f61c6ef.png
[20201104142514151.png_pic_center]: /images/20221120/4f9df1598e8a4227af3118c64c328cc6.png
[20201104142524683.png_pic_center]: /images/20221120/b5e76aab43144eaeb7e643b43f585a06.png
[Link 1]: https://blog.csdn.net/weixin_43935927/article/details/109361168
[20201104142534242.png_pic_center]: /images/20221120/3be6e114f1634eecbb45bfcd23199a91.png
[20201104142548504.png_pic_center]: /images/20221120/9154708309d1416c9eb142b14ab56e4b.png
[20201104142600244.png_pic_center]: /images/20221120/c8d1633852c74e5c95bb238ae9bc37f2.png
[20201104142629996.png_pic_center]: /images/20221120/ee5d9b9f7cc94aa3820b7312c1fb20dc.png
[20201104142640724.png_pic_center]: /images/20221120/787339670e8043e894ab931c5e1e96f8.png