前言：最近在做到一道给出SQL，说出执行计划的题目，刚开始有点蒙圈了，因为平时在优化慢查询的时候一般会去反推，突然要说出执行计划难免有点措手不及的感觉，今天在这里做个小结，也算是完善下自己在这方面的理论知识吧。

一、EXPLAIN中的列

我们首先来看个执行计划，结合具体的执行计划来看各个列的含义。

root@localhost [xucl]>desc select a,b,c from zst where a=1 and b>1 and c like '%c%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | zst   | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 5       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到整个执行计划共有12个列，下面我们分别来看下每个列的含义

id：标识SELECT所属的行。如果在语句中没有子查询或联合查询，那么只会有唯一的SELECT，于是每一行在这个列中都将显示一个1，否则内存的SELECT语句一般会顺序编号，对应于其在原始语句中的位置
select_type：这一列对应是简单查询还是复杂SELECT，包含有如下几种情况
- SUBQUERY：包含在SELECT列表中的子查询中的SELECT（换句话说，不在FROM子句中）
- DERIVED：表示包含在FROM子句的子查询SELECT，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其”派生表”，因为该临时表是从子查询中派生而来的
- UNION：在UNION中的第二个和随后的SELECT被标记为UNION。第一个SELECT被标记为就好像它以部分外查询来执行。在UNION中的第一个SELECT会显示为PRIMARY，如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED
- UNION RESULT：用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT
- DEPENDENT：意味着SELECT依赖于外层查询中发现的数据
- UNCACHEABLE：意味着SELECT中的某些特性阻止结果被缓存于一个Item_cache中（比较少碰到）
table：显示了对应行正在访问哪个表。在通常情况下，它相当明了：它就是那个表，或是该表的别名
partitions
type：显示了关联类型，就是MySQL决定如何查找表中的行，下面是最重要的访问方法，依次从最差到最优
- ALL：全表扫描
- INDEX：这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行，它的主要优点是避免了排序，最大的缺点是需要承担按索引次序读取整个表的开销，这通常意味着若是按随机次序访问行，开销将会非常大。如果在xtra列中看到”Using index”，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少得多
- range：范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行，这比全索引扫描好一些，因为它不用遍历全部索引。常见于where子句中的>、BETWEEN，有时也会出现在IN()或者OR()列表
- ref：这是一种索引访问（有时也叫索引查找），它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一索引或者唯一索引的非唯一前缀时才会发生。
- ref_or_null：是ref的一个变体，意味着MySQL必须在初次查找的结果里进行二次查找以找出NULL的条目
- eq_ref：使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到，它会将他们与某个参考值做比较，是属于优化做的比较好的访问方式，因为它知道无需估计匹配行的范围火灾找到匹配行后再继续查找
- const，system：当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型
- NULL：这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着在访问表或者索引。例如，从一个索引列里选取最小值通过单独查找索引来完成，不需要回表
possible_keys：表示查询可能会用到的索引
key：表示MySQL使用了哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么MySQL选用它是处于另外的原因-例如，它可能选择了一个覆盖索引，哪怕没有WHERE子句
key_len：显示了MySQL在索引里使用的字节数，key_len的计算方法后面会讲
ref：显示了之前的表在key列记录的索引中查找值所用的列或常量。
rows：显示了MySQL估计为了找到所需的行而要读取的行数，根据表的统计信息和索引的选用情况，这个估算可能很不精确
filtered：显示的是针对表里符合某个条件的记录数的百分比所做的一个悲观估算
Extra：这一列包含的是不适合在其他列显示的额外信息，最重要的值如下所示：
- Using index：表示MySQL将使用覆盖索引，以避免回表
- Using where：表示MySQL服务器将在存储引擎检索行后再进行过滤
- Using temporary：意味着MySQL在对查询结果排序时会使用一个临时表
- Using filesort：MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行
- Range checked for each record：这个值表示没有好用的索引，新的索引将在连接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的

二、树形结构输出

利用pt-visual-explain

[root@zst2 vagrant]#  mysql -uroot -p -S /tmp/mysql3306.sock -Dxucl -e "EXPLAIN select * from tab1 where name = 'aaa'" | pt-visual-explain 
Enter password: 
Filter with WHERE
+- Table scan
   rows           2
   +- Table
      table          tab1

json格式输出

 {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.40"
    },
    "table": {
      "table_name": "tab1",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 1,
      "filtered": "50.00",
      "cost_info": {
        "read_cost": "1.20",
        "eval_cost": "0.20",
        "prefix_cost": "1.40",
        "data_read_per_join": "40"
      },
      "used_columns": [
        "name"
      ],
      "attached_condition": "(`xucl`.`tab1`.`name` = 'aaa')"
    }
  }
}

看上去json格式的输出更加详细一些

三、key_len计算方法

key_len 大小的计算规则是：

一般地，key_len 等于索引列类型字节长度，例如tinyint类型为1 bytes，int类型为4 bytes，bigint为8 bytes；
如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90 bytes；
若该列类型定义时允许NULL，其key_len还需要再加 1 bytes；
若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes;

key_len例子

列类型	KEY_LEN	备注
id int	key_len=4+1=5	允许NULL，加1-bytes
id int not null	key_len=4	不允许NULL
user char(30) utf8	key_len=30*3+1	允许NULL
user varchar(30) not null utf8	key_len=30*3+2	动态列类型，加2-bytes
user varchar(30) utf8	key_len=30*3+2+1	动态列类型，加2-bytes；允许NULL，再加1-bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT列截取部分，被视为动态列类型，加2-bytes；且允许NULL

备注，key_len 只指示了WHERE中用于条件过滤时被选中的索引列，是不包含 ORDER BY/GROUP BY 这部分被选中的索引列。

四、例子大法

现有表结构如下：

root@localhost [xucl]>create table tx(
    -> id int(11) not null auto_increment comment '记录ID',
    -> shid int(11) not null comment '商店ID',
    -> gid int(11) not null comment '物品ID',
    -> type tinyint(11) not null comment '支付方式',
    -> price int(11) not null comment '物品价格',
    -> comment varchar(200) not null comment '备注',
    -> primary key(id),
    -> unique key uk_shid_gid(shid,gid),
    -> key idx_price(price),
    -> key idx_type(type)
    -> )engine=innodb auto_increment=1 default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

下面进行一些SQL查询

例1：

root@localhost [xucl]>explain socalhoid,shid,gid from tx order by shid,gid;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tx    | NULL       | index | NULL          | uk_shid_gid | 8       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

解释：这个例子用到了覆盖索引，并且匹配到的列是shid和gid，因此key_len为4+4=8

例2：

root@localhost [xucl]>desc select * from tx where shid=2 order by gid desc;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tx    | NULL       | ref  | uk_shid_gid   | uk_shid_gid | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

解释：这个会用到索引uk_shid_gid的shid列，所以key_len为4

例3：

root@localhost [xucl]>desc select * from tx where shid=6 and gid>1 order by gid ;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tx    | NULL       | range | uk_shid_gid   | uk_shid_gid | 8       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

解释：这个例子中，也会用到uk索引，并且因为开启ICP的原因，会用到两个列，并且不需要额外的排序，因此key_len为8

例4：

root@localhost [xucl]>desc select * from tx where type=1 order by shid,gid;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | tx    | NULL       | ref  | idx_type      | idx_type | 1       | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

解释：这个例子中，用到了idx_type列的索引，tinyint的key_len为1，另外由于需要对shid和gid排序，所以需要额外的排序操作

参考文章：
高性能MySQL附录EXPLAIN
http://imysql.com/2017/08/08/quick-deep-into-mysql-index.shtml

MySQL知识库

【MySQL】EXPLAIN小结

一、EXPLAIN中的列

二、树形结构输出

三、key_len计算方法

四、例子大法