MySQL - Explain深度剖析

傷城~ 2023-02-28 11:10 8阅读 0赞

### 文章目录 ###

*  生猛干货
 *  官方文档
 *  Explain介绍
 *  测试数据
 *  explain 使用
 *  explain重要列说明
 *   *  id
     *  select\_type
     *   *  simple
         *  primary
         *  subquery
         *  derived
         *  union
     *  table
     *  type
     *   *  NULL
         *  const, system
         *  eq\_ref
         *  ref
         *  range
         *  index
         *  ALL
     *  possible\_keys
     *  key
     *  key\_len
     *   *  key\_len计算规则
     *  ref
     *  rows
     *  Extra
     *   *  Using index
         *  Using where
         *  Using index condition
         *  Using temporary
         *  Using filesort
         *  Select tables optimized away
 *  搞定MySQL实战

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

# 生猛干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

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

# 官方文档 #

[https://dev.mysql.com/doc/refman/5.7/en/explain-output.html][https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]

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

# Explain介绍 #

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分查询语句或是结构的性能瓶颈

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 。

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

# 测试数据 #

DB Version

mysql> select version();
    +------------+
    | version()  |
    +------------+
    | 5.7.29-log |
    +------------+
    1 row in set (0.00 sec)
    
    mysql>

DROP TABLE IF EXISTS `actor`;
    
    CREATE TABLE `actor` (
     `id` int(11) NOT NULL,
     `name` varchar(45) DEFAULT NULL,
     `update_time` datetime DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
    
    ###############################
    
    DROP TABLE IF EXISTS `film`;
    
    CREATE TABLE `film` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(10) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
    
    ###############################
    
    DROP TABLE IF EXISTS `film_actor`;
    
    CREATE TABLE `film_actor` (
     `id` int(11) NOT NULL,
     `film_id` int(11) NOT NULL,
     `actor_id` int(11) NOT NULL,
     `remark` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_film_actor_id` (`film_id`,`actor_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

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

# explain 使用 #

explain 两个扩展的使用

explain extended: 提供： 额外一些查询优化的信息 （**‘EXTENDED’ is deprecated and will be removed in a future release.**）

mysql> explain extended select * from film where id=1;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 2 warnings (0.00 sec)
    
    
    # 2 warnings
    
    # 可以通过 show warnings 命令查看
    
    
    mysql> show warnings;
    +---------+------+----------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                          |
    +---------+------+----------------------------------------------------------------------------------+
    | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                |
    | Note    | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
    +---------+------+----------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql>

filtered 列： 百分比，计算公式 rows \* filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表） ， 供参考

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

第二个 **‘PARTITIONS’ is deprecated and will be removed in a future**

mysql> explain partitions select * from film where id=1;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 2 warnings (0.00 sec)
    
    mysql> show warnings;
    +---------+------+----------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                          |
    +---------+------+----------------------------------------------------------------------------------+
    | Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release.              |
    | Note    | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
    +---------+------+----------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql>

所以只使用explain就足够了 。

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

# explain重要列说明 #

## id ##

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

执行顺序：

id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

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

## select\_type ##

表示的对应行是简单还是复杂的查询

### simple ###

简单查询，查询不包含子查询和union

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

### primary ###

复杂查询中最外层的 select

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

### subquery ###

包含在 select 中的子查询（不在 from 子句中）

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

### derived ###

**包含在 from 子句中的子查询。**

**MySQL会将结果存放在一个临时表中，也称为派生表**

derived: 衍生的

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

### union ###

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

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

## table ##

表示 explain 的一行正在访问哪个表

**当 from 子句中有子查询时，table列是 `<derivenN>` 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。**

**当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。**

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

## type ##

表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：**system > const > eq\_ref > ref > range > index > ALL**

**一般来说，得保证查询达到range级别，最好达到ref**

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

### NULL ###

**mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引.**

例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

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

### const, system ###

**mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。**

**system是const的特例，表里只有一条元组匹配时为system.**

mysql> EXPLAIN select * from (select * from film where id=1) t ;
    +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
    |  2 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +-------+------+---------------------------------------------------------------+
    | Level | Code | Message                                                       |
    +-------+------+---------------------------------------------------------------+
    | Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
    +-------+------+---------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

### eq\_ref ###

**primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。**

mysql> EXPLAIN select * from film_actor a  left join film b on a.film_id = b.id ;
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    |  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             |    3 |   100.00 | NULL  |
    |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | dbtest.a.film_id |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                                                                                                                                                                                                   |
    +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Note  | 1003 | /* select#1 */ select `dbtest`.`a`.`id` AS `id`,`dbtest`.`a`.`film_id` AS `film_id`,`dbtest`.`a`.`actor_id` AS `actor_id`,`dbtest`.`a`.`remark` AS `remark`,`dbtest`.`b`.`id` AS `id`,`dbtest`.`b`.`name` AS `name` from `dbtest`.`film_actor` `a` left join `dbtest`.`film` `b` on((`dbtest`.`b`.`id` = `dbtest`.`a`.`film_id`)) where 1 |
    +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

### ref ###

**相比 eq\_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行.**

【 简单 select 查询，name是普通索引（非唯一索引）】

【关联表查询，idx\_film\_actor\_id是film\_id和actor\_id的联合索引，这里使用到了film\_actor的左边前缀film\_id部分。】

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

### range ###

**范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。**

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

### index ###

**扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些**

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

### ALL ###

**全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了**

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

## possible\_keys ##

**显示查询可能使用哪些索引来查找**

explain 时可能出现 possible\_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

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

## key ##

**mysql实际采用哪个索引来优化对该表的访问。**

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible\_keys列中的索引，在查询中使用 force index、ignore index。

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

## key\_len ##

**显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。**

举个例子 ：

film\_actor的联合索引 idx\_film\_actor\_id 由 film\_id 和 actor\_id 两个int列组成，并且每个int是4字节。

通过结果中的key\_len=4可推断出查询使用了第一个列：film\_id列来执行索引查找。

### key\_len计算规则 ###

【字符串】

*  char(n)：n字节长度
 *  varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

【数值类型】

*  tinyint：1字节
 *  smallint：2字节
 *  int：4字节
 *  bigint：8字节

【时间类型】

*  date：3字节
 *  timestamp：4字节
 *  datetime：8字节

**如果字段允许为 NULL，需要1字节记录是否为 NULL**

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

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

## ref ##

显示了在key列记录的索引中，表查找值所用到的列或常量，**常见的有：const（常量），字段名（例：film.id）**

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

## rows ##

**mysql估计要读取并检测的行数，注意这个不是结果集里的行数。**

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

## Extra ##

展示的是额外信息。

列举几个常见的值

### Using index ###

**使用覆盖索引** ： 无需回表

mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；

覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

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

### Using where ###

**使用 where 语句来处理结果，并且查询的列未被索引覆盖**

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

### Using index condition ###

**查询的列不完全被索引覆盖，where条件中是一个前导列的范围；**

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

### Using temporary ###

**mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。**

【actor.name没有索引，此时创建了张临时表来distinct】

【film.name建立了idx\_name索引，此时查询时extra是using index,没有用临时表】

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

### Using filesort ###

**将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的**

【 actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录】

【film.name建立了idx\_name索引,此时查询时extra是using index】

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

### Select tables optimized away ###

使用某些聚合函数（比如 max、min）来访问存在**索引**的某个字段

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]

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

# 搞定MySQL实战 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20230209/811f7af211804517b301bdd3d072ca5d.png
[MySQL]: https://marketing.csdn.net/poster/3766
[https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]: /images/20230209/e8c7a58048df458abe70ab905d2edacc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]: /images/20230209/69abc68660ae44cb8355de06b5df2076.png