前面说了explain的table是表名，显示在前面的代表驱动表，正常select会出现不同的id，但如果子查询本来是两个select，但被优化成连接查询，就会导致是相同的id，union查询会出现临时表，id为null，这个临时表作用于去重，union all不需要去重，所以也就不需要建立临时表。
Select type
我们都知道sql里会包含若干个select，每个select代表一个小的查询语句，每个select的from都可以关联若干张表，每张表对应执行计划输出一条数据，对于同一个select下面，id是相同的。
Mysql又为每个select定义了type，取名为select type，这样就知道每个select扮演什么样的角色。
Simple
查询语句不含包子查询或者union的都算simple类型，比方说下面的单表查询就是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 | 9688 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
当然连接查询也算simple，因为没子查询和union
mysql> EXPLAIN SELECT * FROM s1 INNER 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 | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
Primary
对于union 和union all 都分为好几个select，其中最左边的select就是primary类型
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 | 9688 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 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.00 sec)
从上面可以看到s1查询的select_type就是primary类型。
Union
对于union和union all都是有好几个select组成，除了最左边的是primary外，其他的都是union，从上面的例子就可以看到。
Union Result
当使用union去重时候，会创建临时表，这个临时表的select type就是union result。
SUBQUERY
如果子查询不能满足semi-join的查询条件，该子查询是不相关子查询，并且mysql优化器会选择物化方式执行sql，这时候子查询的select_type就是subquey
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 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 | 9688 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
可以看到外层的就是primary，子查询就是subquery，需要注意的是，子查询会被物化，所以只需要执行一次。
DEPENDENT SUBQUERY
如果包含子查询不能转成semi-join的形式，并且该子查询是相关子查询，这时候select type 就是dependent subquery
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+ | 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 | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key2,idx_key1 | idx_key2 | 5 | xiaohaizi.s1.key2 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+-------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
需要注意，因为s2是相关子查询，没有物化，所以需要执行多次。
DEPENDENT UNION
在包含union的或者union all的大查询中，各个小查询都依赖外层查询的话，除了最左边那个小查询，其他小查询都属于dependent union
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 12 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | Using where; Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.03 sec)
从上面可以看到，第一个外层查询是primary，子查询里面最左边的是dependent subquery ，其余的子查询里都是dependent union
DERIVED
前面我们说的派生查询，大家还记得吗，当子查询在外层的from后面，即为派生查询，
mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 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 | 9688 | 33.33 | Using where | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9688 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
对于上面物化采用派生查询的方式,s1查询就是物化之后，派生查询，所以select_type是derived，上面的是以物化临时表查询的，所以table是derived2。(注意这里mysql优化器选的是物化查询，如果转成连接查询，就不会有临时表了)
materialized
当吧子查询物化之后，再把子查询与外层连接查询
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 | 9688 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+ 3 rows in set, 1 warning (0.01 sec)
这里可以看到，吧s2表物化了，物化之后，吧他们连接查询，所以是simple。
uncacheable subquery和uncacheable union不常用。
partitions
这个是分区的意思，稍微了解一下，mysql是可以分区分表的，因为我们这里没有分区，所以都显示为null
