详细讲解每一列的作用，当做字典查看。
前置知识
半连接
子查询物化
索引合并
table
无论查询语句中包含多少张表，最终都会转换为对单表的访问，所以EXPLAIN语句输出的每条记录都对应着对单表的访问方法，也即table列代表着表名。
普通单表
mysql> explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
普通连接查询
mysql> explain select * from s1 join s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)
id
查询语句中，为每个select分配一个唯一id，当语句中包含多个select时（union、子查询），id的大小表示不同select的查询先后顺序。
当id相同table不同时（一般出现在连接语句），在前面的表示驱动表，在后面的是被驱动表。
普通单id
mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
复杂连接语句
语句中，id相同，s1是驱动表，s2是被驱动表。
mysql> explain select * from s1 join s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)
语句中，id不同，表示MySQL先查询s1，后查询s2。
mysql> explain select * from s1 where key1 in (select id from s2) or key2 = 'b';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key2 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | PRIMARY | idx_key2 | 5 | NULL | 10143 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)
语句优化（子查询转连接）
虽然语句属于子查询，理应分配2个id，但是执行计划看出来只有id=1，因为在MySQL优化器将这种明显可以转换为连接查询优化速度的语句改写为连接查询了。
可以通过后面的show warnings ;语句查看code=1003的优化过程。
mysql> explain select * from s1 where key1 in (select id from s2 where key1 = 'b');
+----+-------------+-------+------------+------+------------------+----------+---------+-------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+----------+---------+-------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | s2 | NULL | ref | PRIMARY,idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 10143 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+------------------+----------+---------+-------+-------+----------+----------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)
mysql> show warnings ;
| Level | Code | Message
| Note | 1003 | select 此处省略一堆查询列节省篇幅 from `xiaohaizi`.`s2` join `xiaohaizi`.`s1` where ((`xiaohaizi`.`s2`.`key1` = 'b') and (`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`id`)) |
3 rows in set (0.00 sec)
去重临时表
当语句使用union对结果进行合并时，会使用临时表对结果进行去重。所以生成一个id=null的结果集。
当使用union all时，因为不需要对结果进行去重，故不会产生NULL记录。
mysql> explain select * from s1 union select * from s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
3 rows in set, 1 warning (0.02 sec)
mysql> mysql> explain select * from s1 union all select * from s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
select_type
MySQL为每个select关键字代表的小查询都定义了一个名为select_type的属性，只要知道了小查询的select_type属性，也就知道了这个小查询在大查询中的角色。
SIMPLE
查询语句中不包含union或者子查询，就是简单的查询就是SIMPLE。
mysql> explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from s1 join s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------------+
2 rows in set, 1 warning (0.01 sec)
PRIMARY
对于union/union all/或者子查询等大查询来说，是有多个小查询组合而来的，最左边的那个查询就是PRIMARY。
mysql> explain select * from s1 union all select * from s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
UNION
对于union/union all/或者子查询等大查询来说，是有多个小查询组合而来的，除最左边的那个查询是PRIMARY外，其余所有小查询都是UNION，执行计划同上。
UNION RESULT
MySQL选择使用临时表来完成UNION查询的去重工作，针对此临时表select_type就是UNION RESULT，执行计划同「去重临时表」章节。
SUBQUERY
如果包含子查询的语句不能转换为对应的半连接形式，并且该子查询是不相关子查询，而且查询优化器决定采用子查询物化的方案来执行该子查询时，select_type就是SUBQUERY。
mysql> explain select * from s1 where key1 in (select key2 from s2) or key3 = 'b';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | idx_key2 | idx_key2 | 5 | NULL | 10143 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
DEPENDENT SUBQUERY
如果包含子查询的查询语句不能转换为对应的半连接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询的第一个select关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
select_type=DEPENDENT SUBQUERY的子查询可能会被执行多次。
mysql> explain select * from s1 where key1 in (select key2 from s2 where s1.key2 = s2.key2) or key3 = 'b';
+----+--------------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+--------------------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | xiaohaizi.s1.key2 | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.01 sec)
DEPENDENT UNION
在包含union或union all的大查询中，如果各个小查询都依赖外层查询，则除了最左边的小查询select_type=DEPENDENT SUBQUERY外，其余小查询都是DEPENDENT UNION。
mysql> explain select * from s1 where key1 in (select key2 from s2 union select key3 from s2 union select key1 from s2) or key3 = 'b';
+----+--------------------+--------------+------------+------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------------+------------+------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key2 | idx_key2 | 5 | func | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | s2 | NULL | ref | idx_key3 | idx_key3 | 303 | func | 1 | 100.00 | Using index |
| 4 | DEPENDENT UNION | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index |
| NULL | UNION RESULT | <union2,3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+--------------+------------+------+---------------+----------+---------+------+-------+----------+--------------------------+
5 rows in set, 1 warning (0.01 sec)
DERIVED
在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询的select_type=DERIVED。
mysql> explain select * from (select key1, count(*) as total from s2 group by key1) t where total > 1;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10143 | 33.33 | Using where |
| 2 | DERIVED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 10143 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
MATERIALIZED
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询，该子查询对应的select_type=MATERIALIZED。
第三条记录的select_type=MATERIALIZED，说明优化器将子查询先转换为物化表，执行计划的前两条记录的id都=1，说明这两条记录对应的表进行的是连接查询。
第二条记录的table列的值是
mysql> explain select * from s1 where key1 in (select key1 from s2);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 10143 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)
UNCACHEABLE SUBQUERY、UNCACHEABLE UNION
- UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
- UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）
partitions
版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
type
表示单表的访问方法，最为核心的字段之一。
system
当表只有一条记录且该表的存储引擎为MyISAM、MEMORY时，为SYSTEM。
const
根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法为const。
eq_ref
执行连接查询时，如果被驱动表是通过主键或者不允许存储NULL值的唯一二级索引列等值匹配的方式进行访问的（如果该主键或者不允许存储NULL值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。
mysql> explain select * from s1 join s2 where s1.id = s2.id; --通过主键索引访问s2
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10143 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.s1.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select * from s1 join s2 on s1.key2 = s2.key2; --通过唯一二级索引访问s2
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key2 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | xiaohaizi.s1.key2 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select * from s1 join s2 on s1.key2 = s2.key3; --通过唯一二级索引访问s1，注意这里是s1做被驱动表！！
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-----------------------+
| 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 1 | SIMPLE | s1 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | xiaohaizi.s2.key3 | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+-------+----------+-----------------------+
2 rows in set, 2 warnings (0.00 sec)
ref
当通过普通二级索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法就可能是ref。
mysql> explain select * from s1 join s2 on s1.key3 = s2.key3;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 1 | SIMPLE | s1 | NULL | ref | idx_key3 | idx_key3 | 303 | xiaohaizi.s2.key3 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
fulltext
全文索引。
ref_or_null
当对普通二级索引列进行等值匹配且该索引列的值也可以是NULL值时，记为ref_or_null。
mysql> explain select * from s1 where key1 = 'a' or key1 is null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
index_merge
一般情况下只会为单个索引生成扫描区间，但当在某些场景下可以使用Intersection、union、sort-union进行索引合并。
mysql> explain select * from s1 where key1 = 'a' or key3 = 'b';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.01 sec)
unique_subquery
类似于两表连接中被驱动表的eq_ref访问方法。针对一些包含in子查询的查询语句。如果查询优化器决定将in子查询转换为exists子查询，而且子查询在转换后可以使用主键或唯一二级索引进行等值匹配，记为unique_subquery。
mysql> explain select * from s1 where common_field in (select id「这里主键/唯一二级索引是核心」 from s2 where s1.common_field = s2.common_field ) or key3 = 'b';
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)
index_subquery
类似于unique_subquery，只不过在访问子查询中的表时使用的是普通的索引。
mysql> explain select * from s1 where common_field in (select key3「这里是普通索引」 from s2 where s1.common_field = s2.common_field ) or key3 = 'b';
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 10143 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key3 | idx_key3 | 303 | func | 1 | 10.00 | Using where |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
range
如果使用索引获取某些单点扫描区间的记录，那么就可能使用到range访问方法。
mysql> explain select * from s1 where key3 in ('a','b','c','abc');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key3 | idx_key3 | 303 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
index
当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。
- InnoDB特殊的点：当我们需要执行全表扫描，并且需要对主键进行排序时，type列的值也是index。
mysql> explain select key_part2 from s1 where key_part2 = 'ab';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | s1 | NULL | index | idx_key_part | idx_key_part | 909 | NULL | 10143 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
all
全表扫描。
possible_keys、key
- possible_keys：可能使用的索引。
- key：实际用到的索引。
注意点：
- possible_keys当type=index时显示为空，此时key展示的是实际使用的索引。
- possible_keys不是越多越好，越多查询优化器在计算查询成本时花费的时间就越长，尽可能删除用不到的索引。
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。
ref
当访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery中的其中一个时，ref列展示的就是与索引列进行等值匹配的东西是什么。
- 常量，即右侧的值
'1'为常量。
mysql> explain select * from s1 where id = '1';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
- 某个列，比如下面s2作为被驱动表时，s2的type=ref，ref=xiaohaizi.s1.key1
mysql> explain select * from s1 join s2 on s1.key1 = s2.key1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 12242 | 100.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
- 某个函数，比如下面s2作为被驱动表时，s2的type=ref，ref=func表示进行了函数操作。
mysql> explain select * from s1 join s2 on s1.key1 = upper(s2.key1);
+----+-------------+-------+------------+------+---------------+----------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 12558 | 100.00 | NULL |
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+------+-------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
rows
- 在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估算行数。
- 如果使用索引来执行查询，执行计划的rows列就代表预计扫描的索引记录行数。
mysql> explain select * from s1 where key1 > 'z';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
filtered
计算MySQL驱动表扇出的一种策略。
- 如果使用全表扫描执行单表查询，那么计算驱动表扇出时需要估计出全部搜索条件的记录到底有多少条。
- 如果使用索引来执行单表查询，那么计算驱动表扇出时需要估计出满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。
在单表查询时没太大意义，在连表查询时对估算执行次数有帮助。
下表中，s1做驱动表，s2做被驱动表，s1的filtered=10，rows=12242，12242* 10%=1224.2，s2的filtered=100，rows=1，表示还要对被驱动表执行答曰1224次查询。
mysql> explain select * from s1 join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 12242 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
Extra
展示额外信息时使用，比较多，此处按照书中描述，只记录常出现的内容。
No tables used
当查询语句中没有FROM子句时，会提示。比如SELECT 1之类。
Impossible WHERE
查询语句的WHERE子句永远为FALSE时，会提示。比如SELECT * FROM s1 WHERE 1 <> 1。
No matching min/max row
当查询列表处有MIN或MAX聚集函数，但是没有记录符合WHERE子句中的搜索条件时，会提示。比如SELECT min(key1) FROM s1 WHERE key1 = '一个不存在的值'。
Using index
使用覆盖索引执行查询时，不用回表，会提示。比如SELECT key1 FROM s1 WHERE key1 = 'a'。
Using index condition
搜索条件中虽然出现了索引列，但却不能充当边界条件来形成扫描区间，也就是说不能用来减少需要扫描的记录数量，会提示。
比如：SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'，其中的key1 > 'z'可以用来形成扫描区间，但是key1 LIKE '%a'却不能。
即索引覆盖（索引下推）达成条件。
只对于二级索引有效，对于二级索引等值判断不生效（这时Extra=NULL），对于主键索引不生效（直接查数据）。
Using where
当某个搜索条件需要在server层进行判断时，在Extra列会提示，比如索引覆盖没有达成的情况。
SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'，由于comon_field无法在引擎层判断，只能通过key1形成扫描区间后，在server层判断。
Using join buffer（Block Nested Loop）
在连接过程中，当被驱动表不能有效的利用索引加快访问速度时，MySQL会分配一块JoinBuffer内存块来加快查询速度，基于块的嵌套循环算法执行连接查询。
Using intersect(...)、Using union(...)和Using sort_union(...)
- intersect：准备使用Intersection索引合并的方式执行查询，括号中包含需要合并的索引名称。
- union：准备使用union索引合并方式执行查询。
- sort_union：准备使用Sort-Union索引合并的方式执行查询。
zero_limit
当Limit子句参数为0时，会提示。
Using filesort
对结果集中记录进行排序时，使用索引则不会出现，但很多情况无法使用索引进行排序，则会出现filesort。
Using temporary
MySQL利用临时表完成去重、排序时。
比如DISTINCT、GROUP BY、UNION等子句查询过程中如无法使用索引则会提示。
Start temporary，End temporary
子查询时，查询优化器会优先尝试将IN子查询转换为半连接。半连接有很多种策略，当选择Duplicate Weedout时，也就是通过建立临时表来为外城查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary，被驱动表则会显示End temporary。
LooseScan
在将IN子查询转换为半连接时，如果采用的是LooseScan执行策略，则驱动表执行计划的Extra列就显示LooseScan提示。
FirstMatch(table_name)
在将IN子查询转换为半连接时，如果采用的是FirstMatch执行策略，则被驱动表执行计划的Extra列就显示FirstMatch提示。
建表和1w条数据
表
CREATE TABLE s1 (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
CREATE TABLE s2 (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
插入数据
delimiter $
create procedure insertNData(n int)
begin
declare total int default 0;
declare num int default 1;
while total <= n
do
set total = total + 1;
set num = num + 1;
INSERT INTO xiaohaizi.s1 (id, key1, key2, key3, key_part1, key_part2, key_part3, common_field)
VALUES (null, substring(md5(rand()), 1, 10), floor(rand() * 100000000), substring(md5(rand()), 1, 10),
substring(md5(rand()), 1, 10), substring(md5(rand()), 1, 10), substring(md5(rand()), 1, 10),
substring(md5(rand()), 1, 10));
INSERT INTO xiaohaizi.s2 (id, key1, key2, key3, key_part1, key_part2, key_part3, common_field)
VALUES (null, substring(md5(rand()), 1, 10), floor(rand() * 100000000), substring(md5(rand()), 1, 10),
substring(md5(rand()), 1, 10), substring(md5(rand()), 1, 10), substring(md5(rand()), 1, 10),
substring(md5(rand()), 1, 10));
end while;
select count(*) from xiaohaizi.s1;
end $
call insertNData(10000) $