MySQL性能优化——Explain执行计划详解

﹏ヽ暗。殇╰゛Y 2022-12-19 00:59 235阅读 0赞

**目录**

Explain简介

Explain 分析

1、id列

2、select\_type 列

3、type 连接类型

4、possible\_key、key 列

5、key\_len列

6、rows 列

7、filtered 列

8、ref 列

9、Extra 列

SQL脚本

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

# Explain简介         #

MySQL提供了一个执行计划的工具，在MySQL架构中，SQL语句通过优化器最终生成的就是一个执行计划。通过EXPLAIN我们可以模拟优化器执行SQL查询语句的过程，来知道MySQL是怎么处理一条SQL语句的。通EXPLAIN我们可以分析语句或者表的性能瓶颈，然后进行优化。

官方地址：

[MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format][MySQL _ MySQL 5.7 Reference Manual _ 8.8.2 EXPLAIN Output Format]

我们新建三张表，三张表只有主键索引，来详细讲解一下EXPLAIN。**（建表SQL在文章末尾）。**

# Explain 分析 #

接下来我们将分析图中 explain 中每列的信息。

## 1、id列 ##

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

select id是SQL执行的顺序的标识，SQL从大到小的执行

*  id相同时，执行顺序由上至下
 *  如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
 *  如果id相同，则认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

**        1）id 值不同，id 值越大优先级越高，越先被执行**

我们查询book表name=java的email信息

EXPLAIN 
    SELECT 
        email 
    FROM
        contact
    WHERE id = (
        SELECT contact_id  FROM author
        WHERE id = ( 
            SELECT author_id FROM book WHERE NAME = 'java' 
        )
    );

我们先根据name=java从book表中获取author\_id，再根据author\_id从author表中获取contact\_id，根据contact\_id查询contact表获取email。

上面SQL语句执行的顺序为book —> author —> contact，在执行计划中，id依次为 3、2、1

![20201104153652628.png][]

**        2）id值相同，由上到下顺序执行**

我们获取book表id=1和author表contact\_id =2的数据

EXPLAIN
    SELECT
        book.name,author.name,email 
    FROM
        book,author,contact
    WHERE
        book.author_id = author.id 
        AND author.contact_id = contact.id 
        AND (book.id = 1 or contact.id = 2)

我们从执行计划中，可以看到id值一样，book表在最后，执行的顺序为：book（4条）—> author （3条）—> contact （3条）

![20201104153257543.png][]

我们往author表中新增2条数据，再次执行上面的执行计划

INSERT INTO `author`(`id`, `name`, `contact_id`) VALUES (4, '张一', 4);
    INSERT INTO `author`(`id`, `name`, `contact_id`) VALUES (5, '李二', 5);

此时执行的顺序为：contact （3条）—>  author （5条）—> book（4条）

![20201104153436266.png][]

随着表数据的改变，执行顺序也会跟着改变，

**        结论：**

**        id值不同，先大后小，id 值相同时，从上往下顺序执行。**

## 2、select\_type 列 ##

select\_type ：查询类型，表示对应行是是简单还是复杂的查询，常见的查询类型：

**        1）simple：简单查询**

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

EXPLAIN
    SELECT * FROM book;

![2020110315384526.png][]

**        2）primary：复杂查询中最外层的 select**

PRIMARY 子查询 SQL 语句中的主查询，也就是最外面的那层查询。

EXPLAIN  
    SELECT * FROM author 
    WHERE id = ( 
        SELECT author_id FROM book WHERE NAME = 'java' 
    );

![2020110315453415.png][]

**        3）subquery：包含在 select 中的子查询（不在 from 子句中）**

SUBQUERY 子查询中所有的内层查询都是 SUBQUERY 类型的。

EXPLAIN  
    SELECT * FROM author 
    WHERE id = ( 
        SELECT author_id FROM book WHERE NAME = 'java'
    );

![2020110315453415.png][]

**        4）derived：衍生查询**

derived 查询包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

EXPLAIN SELECT * FROM (SELECT max(price) FROM book) tab;

![2020110316515094.png][]

**        5）union：联合查询**

union查询在 union 中的第二个和随后的 select

**        6）union result：**

从 union 临时表检索结果的 select

EXPLAIN SELECT * FROM book WHERE id=1 UNION SELECT * FROM book WHERE id=2

![20201103165757987.png][]

## 3、type 连接类型 ##

EXPLAIN输出的type列描述了表是如何连接的，从最佳类型到最差类型排序：

system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > range > index > all

以上访问类型除了 all，都能用到索引。

type 所有的连接类型中，常用的连接类型：system、const 、eq\_ref、ref、range、index、all。

**        1）const**

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

EXPLAIN SELECT * FROM book WHERE id=1;

2）system

system 是 const 的一种特例，官网上的说明：

> The table has only one row (= system table). This is a special case of the [const][] join type.

只有一行满足条件。例如：只有一条数据的系统表。

EXPLAIN SELECT * FROM mysql.proxies_priv;

![2020110319250157.png][]

**        注意：**

**        proxies\_priv表的存储引擎是MyISAM，我们新建一张表，存储引擎为MyISAM，表里面只有一条数据，type结果也是system，将存储引擎修改为InnoDB，type的结果就会变为const。**

**        3）eq\_ref**

eq\_ref 通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。 eq\_ref 是除 const 之外最好的访问类型。

EXPLAIN SELECT * FROM book,author WHERE book.author_id=author.id;

![20201103194207159.png][]

**        4）ref**

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

我们先在book表nam字段上建立一个索引

ALTER TABLE book ADD INDEX `idx_name`(`name`);

根据name 非唯一性索引进行查询

EXPLAIN SELECT * FROM book WHERE name = 'java';

![2020110319521445.png][]

关联操作只使用了索引的最左前缀，我们将author表的主键字段，由id修改为联合主键（id，name）

![2020110319572987.png][]

EXPLAIN SELECT * FROM book,author WHERE book.author_id=author.id;

![20201103195819164.png][]

当author表id字段为主键的时候，关联查询type为eq\_ref，当建立联合索引后，关联查询，id字段使用了最左匹配原则，使用了（id，name）联合索引，type此时为ref。

**        每小节我们执行完修改表操作，我们都执行rollback操作，保持表最原先的结构，方便后续内容的讲解，后面就不再说明。**

rollback;

**        5）range**

我们在book表重新建立了name索引，此时我们执行模糊查询

ALTER TABLE book ADD INDEX `idx_name`(`name`);
    
    
    EXPLAIN SELECT * FROM book WHERE name like 'java';

![20201103200457522.png][]

EXPLAIN SELECT * FROM book WHERE id BETWEEN 2 AND 4;

![20201103200618700.png][]

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

6）all

不走索引一定是全表扫描ALL。

EXPLAIN SELECT * FROM book WHERE price > 60;

![20201103200835341.png][]

## 4、possible\_key、key 列 ##

possible\_key是可能用到的索引，key实际用到的索引，如果key为NULL 就表示没有用到索引。 possible\_key 可以有一个或者多个，可能用到索引不代表一定用到索引。 反过来，possible\_key 为空，key 可能有值吗？

我们在book表上建立2个索引，一个是name，一个是（name，price）联合索引。

ALTER TABLE book ADD INDEX `idx_name`(`name`);
    
    ALTER TABLE book ADD INDEX `idx_name_price`(`name`, `price`);

我们查询name=java的书籍，发现有2个索引，idx\_name和idx\_name\_price，最终使用了idx\_name索引，

EXPLAIN SELECT * FROM book WHERE name = 'java';

![20201104094757754.png][]

我们再来查询 author\_id = 1 的数据，由于author\_id 没有索引，所以possible\_key和key都为Null，走全表扫描。

EXPLAIN SELECT * FROM book WHERE author_id = 1;

![20201104095138301.png][]

## 5、key\_len列 ##

key\_len是查询使用到索引的长度，跟索引字段的类型、长度有关。

详细的key\_len计算可以看[《Explain执行计划key\_len详解》][Explain_key_len]这篇博客。

我们在book表name和author字段分表建立索引

ALTER TABLE book ADD INDEX `idx_name`(`name`);
    
    ALTER TABLE book ADD INDEX `idx_author_id`(`author_id`);

我们根据name索引进行查询，name字段类型为varchar(255)，允许为空，CHARSET=utf8mb4 ，key\_ken = 4 \* 255 + 2 + 1 = 1023 ；

EXPLAIN SELECT * FROM book WHERE name = 'java';

![20201104104400895.png][]

我们根据author\_id 索引进行查询，author\_id 字段类型为int，允许为null ，key\_len = 4 + 1 = 5；

EXPLAIN SELECT * FROM book WHERE author_id = 1;

![20201104104728564.png][]

## 6、rows 列 ##

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

EXPLAIN
    SELECT 
        * 
    FROM
        book,author
    WHERE
        book.author_id = author.id and author.name ='张三';

![20201104175233252.png][]

## 7、filtered 列 ##

filtered表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比，在author表中有三条数据，我们查询name=张三的记录，只有一条，也就是33.33%符合查询结果。

EXPLAIN 
    SELECT 
        * 
    FROM 
        book,author 
    WHERE 
        book.author_id = author.id and author.name ='张三';

![2020110417523656.png][]

## 8、ref 列 ##

ref使用哪个列或者常数和索引一起从表中筛选数据。

EXPLAIN 
    SELECT
        * 
    FROM 
        author,contact 
    WHERE
        contact.id = author.contact_id and name ='李四';

![20201104174856262.png][]

## 9、Extra 列 ##

Extra执行计划给出的额外的信息说明，可以根据Extra来进行优化。

**        1）Using index**

Using index 用到了覆盖索引，不需要回表。

ALTER TABLE book ADD INDEX `idx_name_price`(`name`, `price`);

EXPLAIN SELECT name,price FROM book WHERE name = 'java';

![20201104193316170.png][]

**        2）Using where**

Using where 使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要 在 server 层进行过滤（跟是否使用索引没有关系）。

EXPLAIN SELECT * FROM book WHERE author_id = 1;

![20201104193553836.png][]

**        3）Using index condition**

Using index condition索引条件下推

ALTER TABLE book ADD INDEX `idx_name`(`name`);
    
    EXPLAIN SELECT * FROM book WHERE name like 'j%';

![20201104110251325.png][]

> https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

**        4）Using filesort**

Using filesort 不能使用索引来排序，用到了额外的排序。这种情况下一般也是要考虑使用索引来优化的。

EXPLAIN SELECT * FROM book order by price;

![20201104193810629.png][]

**        5）Using temporary**

Using temporary 表示用到了临时表。mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

EXPLAIN SELECT DISTINCT(author_id) FROM book;

![20201104193940595.png][]

# SQL脚本  #

CREATE TABLE `author` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `contact_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    CREATE TABLE `book` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `price` decimal(10,2) DEFAULT NULL,
      `author_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    CREATE TABLE `contact` (
      `id` int(11) NOT NULL,
      `email` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (1, 'java', 99.00, 1);
    INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (2, 'spring', 80.00, 1);
    INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (3, 'jvm', 109.00, 2);
    INSERT INTO book(`id`, `name`, `price`, `author_id`) VALUES (4, 'mybatis', 66.00, 3);
    
    INSERT INTO author(`id`, `name`, `contact_id`) VALUES (1, '张三', 1);
    INSERT INTO author(`id`, `name`, `contact_id`) VALUES (2, '李四', 2);
    INSERT INTO author(`id`, `name`, `contact_id`) VALUES (3, '王五', 3);
    
    INSERT INTO contact(`id`, `email`) VALUES (1, 'zhangsan@163.com');
    INSERT INTO contact(`id`, `email`) VALUES (2, 'lisi@qq.com');
    INSERT INTO contact(`id`, `email`) VALUES (3, 'wangwu@sina.com');

[MySQL _ MySQL 5.7 Reference Manual _ 8.8.2 EXPLAIN Output Format]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[20201104153652628.png]: /images/20221120/fca64c5a663840b089ae0c38c4a9cbfc.png
[20201104153257543.png]: /images/20221120/53cfd261797a498c8e031e18ec18ef43.png
[20201104153436266.png]: /images/20221120/057c77e2a1d74b45b5ba24ac5722a2f6.png
[2020110315384526.png]: /images/20221120/84761ca5cbd2456896783a9601c7455d.png
[2020110315453415.png]: /images/20221120/d609dd5df1d34a6280af5ca3b0f2b273.png
[2020110316515094.png]: /images/20221120/34b5191317a34eb9affc1b86accdaa5b.png
[20201103165757987.png]: /images/20221120/f78ada68baf74de591b7564aa7725d6d.png
[const]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_const
[2020110319250157.png]: /images/20221120/45726c52c6314c849de21975f92cb36a.png
[20201103194207159.png]: /images/20221120/8b5ca325306e48369f949333564f38ae.png
[2020110319521445.png]: /images/20221120/cffb2a888fcf4eed91814aa267f36852.png
[2020110319572987.png]: /images/20221120/9d2aa86fe0b3454ca0fb4eeea6eff968.png
[20201103195819164.png]: /images/20221120/2b423e2d299e4836a4803c439e57c948.png
[20201103200457522.png]: /images/20221120/1ef96bc4a12648dc80d3d2090ec4fde4.png
[20201103200618700.png]: /images/20221120/f3c47206649f4863bcf46828af8e20c7.png
[20201103200835341.png]: /images/20221120/096ff1bcbce2474bbed811696effa495.png
[20201104094757754.png]: /images/20221120/2ad568c5ffa94c5f8d5f5d7134af8ddc.png
[20201104095138301.png]: /images/20221120/53d29616b7e5499bbd486fcda89353fd.png
[Explain_key_len]: https://blog.csdn.net/zhangchaoyang/article/details/109501696
[20201104104400895.png]: /images/20221120/7ab8291369a2405cb24ec66874bae0e2.png
[20201104104728564.png]: /images/20221120/7b049eed58ab4755b7cb93371f6198b2.png
[20201104175233252.png]: /images/20221120/717fba081a754a6caf4aada24ec5d57c.png
[2020110417523656.png]: /images/20221120/f11ca46ecc4a4553b1f9342865686335.png
[20201104174856262.png]: /images/20221120/75b976bb8abb41ba9a83ad75009e9a9a.png
[20201104193316170.png]: /images/20221120/a6be8c39f0cf42c987d4922bd7657920.png
[20201104193553836.png]: /images/20221120/4dea748c6da54f6b85202a5aa25b7bde.png
[20201104110251325.png]: /images/20221120/34cea676310647ef9fe9c9a84c729a3b.png
[20201104193810629.png]: https://img-blog.csdnimg.cn/20201104193810629.png
[20201104193940595.png]: https://img-blog.csdnimg.cn/20201104193940595.png