mysql select type_explain之select_type分析

MySQL EXPLAIN分析 select_type字段详解

SIMPLE

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

示例

下面的单标查询不包含子查询和union，则 select_type就为SIMPLE

mysql> explain select * from article;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 38 | 100.00 | NULL |

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

PRIMARY

查询中包含任意复杂的子部分，最外层查询会被标记为primary

示例

mysql> explain select id, (select count(id) from article_detail where article_id=w1.id) from article as w1;

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

| 1 | PRIMARY | w1 | NULL | index | NULL | PRIMARY | 130 | NULL | 38 | 100.00 | Using index |

| 2 | DEPENDENT SUBQUERY | article_detail | NULL | eq_ref | idx_article_id | idx_article_id | 34 | lrw.w1.id | 1 | 100.00 | Using where; Using index |

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

2 rows in set, 2 warnings (0.00 sec)

SUBQUERY

查询中包含子查询

示例

mysql> explain select id, (select count(id) from article_detail where article_id=w1.id) from article as w1;

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

| 1 | PRIMARY | w1 | NULL | index | NULL | PRIMARY | 130 | NULL | 38 | 100.00 | Using index |

| 2 | DEPENDENT SUBQUERY | article_detail | NULL | eq_ref | idx_article_id | idx_article_id | 34 | lrw.w1.id | 1 | 100.00 | Using where; Using index |

+----+--------------------+----------------+------------+--------+----------------+----------------+---------+-----------+------+----------+--------------------------+

2 rows in set, 2 warnings (0.00 sec)

DERIVED

示例

衍生表, 子查询会被标记为derived， mysql会递归这些子查询，把结果放入临时表

EXPLAIN SELECT

*

FROM

contract_installment

WHERE

contract_id IN (

SELECT

b1.contract_id

FROM

( SELECT contract_id, sum( pay_amount ) AS totalPayAmount FROM contract_installment WHERE pay_off = 1 GROUP BY contract_id ) as b1)

+----+-------------+----------------------+------------+------+---------------+-------------+---------+---------------------------------------+------+----------+----------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+----------------------+------------+------+---------------+-------------+---------+---------------------------------------+------+----------+----------------------------------------------+

| 1 | PRIMARY | contract_installment | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

| 1 | PRIMARY | | NULL | ref | | | 5 | test.contract_installment.contract_id | 2 | 100.00 | FirstMatch(contract_installment) |

| 3 | DERIVED | contract_installment | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using temporary; Using filesort |

+----+-------------+----------------------+------------+------+---------------+-------------+---------+---------------------------------------+------+----------+----------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

union

当union出现在第二个select中，则被标记为union

当union出现在子查询中，最外层select被标记为derived

示例

mysql> explain select * from contract where id in(1,2,3) union select * from contract where id in(4,5,6);

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | contract | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |

| 2 | UNION | contract | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

3 rows in set, 1 warning (0.00 sec)

union result

union的结果集

示例

mysql> explain select * from contract where id in(1,2,3) union select * from contract where id in(4,5,6);

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | contract | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |

| 2 | UNION | contract | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

3 rows in set, 1 warning (0.00 sec)