MySQL Explain详解
MySQL
MySQL 2019-07-27

简介

explain命令可以对Select语句进行分析，并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

explain的用法，只需要在Select语句之前加入explain即可

explain SELECT * from user where id=1

建立测试表

为了方便测试explain的功能，需要建立一些测试表

user_info

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_index` (`name`)
    )ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

order_info

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)  DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

Explain 输出分析

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

下面逐一的分析各个字段的含义

select_type

表示查询类型，在MySQL中常见的查询类型有以下几种

type explain
SIMPLE 查询不包含UNION查询以及子查询
PRIMARY 表示此查询是最外层的查询
UNION 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT UNION 的结果
SUBQUERY 子查询中的第一个 SELECT
DEPENDENT SUBQUERY 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

最常见的就是查询类型就是SIMPLE

mysql> explain select * from user_info where id = 2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

UNION查询

mysql> explain select * from user_info where id = 2 union select * from user_info where id = 4;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | user_info  | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | user_info  | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

table

表示查询涉及的表或者中间表

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描还是索引扫描等.

type explain
system
const 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据
eq_ref 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =
ref 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询
range 范围扫描
index 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据
All 表示全表扫描, 这个类型的查询是性能最差的查询之一

各查询类型的效率如下

ALL < index < range < ref < eq_ref < const < system

下面我们测试一下各类型

const

const扫描的条件为

  1. 命中主键或者唯一索引

  2. 连接的部分是常量

     mysql> explain select * from user_info where id=1;
 +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
 | id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
 +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
 |  1 | SIMPLE      | user_info | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
 +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

eq_ref

eq_res扫描的条件

  1. join操作
  2. 命中主键或者非空唯一索引

  3. 等值连接

     mysql> explain select * from user_info,order_info where user_info.id=order_info.user_id;
 +----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+-------------------------+------+----------+--------------------------+
 | id | select_type | table      | partitions | type   | possible_keys             | key                       | key_len | ref                     | rows | filtered | Extra                    |
 +----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+-------------------------+------+----------+--------------------------+
 |  1 | SIMPLE      | order_info | NULL       | index  | user_product_detail_index | user_product_detail_index | 254     | NULL                    |    9 |   100.00 | Using where; Using index |
 |  1 | SIMPLE      | user_info  | NULL       | eq_ref | PRIMARY                   | PRIMARY                   | 8       | test.order_info.user_id |    1 |   100.00 | NULL                     |
 +----+-------------+------------+------------+--------+---------------------------+---------------------------+---------+-------------------------+------+----------+--------------------------+

    ref

    req扫描条件

  4. 多表join

  5. 命中非主键或者非唯一索引

     mysql> explain select * from user_info,order_info where user_info.id=order_info.user_id and order_info.user_id=1;
 +----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
 | id | select_type | table      | partitions | type  | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra       |
 +----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
 |  1 | SIMPLE      | user_info  | NULL       | const | PRIMARY                   | PRIMARY                   | 8       | const |    1 |   100.00 | NULL        |
 |  1 | SIMPLE      | order_info | NULL       | ref   | user_product_detail_index | user_product_detail_index | 9       | const |    1 |   100.00 | Using index |
 +----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
 2 rows in set, 1 warning (0.00 sec)

range

mysql> explain select * from user_info where id in(1,2);
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

index

扫描的数据是索引

mysql> explain select name from user_info;
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | name_index | 152     | NULL |   10 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

All

全表扫描，效率低下，需要进行优化

mysql> explain select * from user_info where age = 20;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

possible_keys

表示查询中可能用到的索引，有些索引在possible_keys中出现，不代表此索引会真正的被用到。MySQL中真正用的索引需要看key字段

key

MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

计算规则

  • 字符串

    • char(n): n字节长度
    • varchar(n): 如果是utf-8编码，则是3n+2

  • 数值类型

    • TINYINT: 1字节
    • SMALLINT: 2字节
    • MEDIUMINT: 3字节
    • INT: 4字节
    • BIGINT: 8字节

  • 时间类型

    • DATE: 3字节
    • TIMESTAMP: 4字节
    • DATETIME: 8字节

  • 字段属性

    NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性

示例如下：

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH';
+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | order_info | NULL       | range | user_product_detail_index | user_product_detail_index | 9       | NULL |    1 |    11.11 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+

order_info中有个联合索引

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

在这个查询语句WHERE user_id < 3 AND product_name = ‘p1’ AND productor = ‘WHH’中，因为先进行了user_id的范围查询，而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有user_id, 因此在EXPLAIN中, 显示的key_len为9. 因为 user_id字段是BIGINT, 占用8字节, 而NULL属性占用一个字节, 因此总共是9个字节.

来看下一个例子

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1';
+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | order_info | NULL       | ref  | user_product_detail_index | user_product_detail_index | 161     | const,const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------------+

因为我们的查询条件 WHERE user_id = 1 AND product_name = ‘p1’ 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161 ，因为是utf-8编码，所以是3n+2

rows

rows也是一个重要的字段. MySQL查询优化器根据统计信息, 估算SQL要查找到结果集需要扫描读取的数据行数.

extra

explain很多额外信息会在extra中展示，常见的有以下几种内容

type explain
Using filesort 表示MySQL需额外的排序操作，建议优化
Using index SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录，性能较好
Using temporary 需要建立临时表(temporary table)来暂存中间结果，性能较低，需要优化

下面来看第一个例子

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name;
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | order_info | NULL       | index | NULL          | user_product_detail_index | 254     | NULL |    9 |   100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+

product_name在联合索引中

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

但是使用product_name排序，不能利用索引进行优化，因此需要额外排序，进而变成using filesort。如果修改为 order by user_id,product_name则不会出现using filesort

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id,product_name;
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order_info | NULL       | index | NULL          | user_product_detail_index | 254     | NULL |    9 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+

出现Using temporary

mysql> explain select name,age from user_info group by name,age order by age;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集

参考文献

赏

Reprint please specify: wbl MySQL Explain详解

Previous
TCP之拥塞控制 TCP之拥塞控制
流量控制如果发送方发送数据过快，那么接收方来不及接收，就会丢弃数据。为了避免分组丢失，需要进行流量控制，避免发送方的数据将接收方淹没。 实现流量控制的实现是基于滑动窗口。接收方在发送给发送方的ACK中包含了自己的接收窗口大小，当接收窗口为0
2019-07-30 计算机基础
计算机网络
Next
TCP三次握手与连接释放 TCP三次握手与连接释放
TCP连接为什么需要三次握手TCP是一个可靠的通信协议，所谓的可靠是指接收方收到的数据是完整的，有序的，无差错的。为了实现这个目标，TCP的通信双方需要判断自己的数据是否已经被对方接收，如果没有，那么需要重发。为了实现这个需求，TCP引入了
2019-07-25 计算机基础
计算机网络