大量实例助你看懂Explain的输出内容，轻松搞定慢查询

EXPLAIN：查看SQL语句的执行计划

EXPLAIN命令可以帮助我们深入了解MySQL基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用，在优化慢查询时非常有用

执行explain之后结果集包含如下信息

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

下面将对每一个值进行解释

1、id

id用来标识整个查询中SELELCT语句的顺序，在嵌套查询中id越大的语句越先执行，该值可能为NULL

id如果相同，从上往下依次执行。id不同，id值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为NULL

2、select_type

select_type表示查询使用的类型，有下面几种：

simple： 简单的select查询，没有union或者子查询

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

primary： 最外层的select查询

mysql> explain select * from (select * from test where id = 1000) a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ |  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  | |  2 | DERIVED     | test       | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

union： union中的第二个或随后的select查询，不依赖于外部查询的结果集

mysql> explain select * from test where id = 1000 union all select * from test2 ; +----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+ | id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows  | Extra           | +----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+ |  1 | PRIMARY      | test       | const | PRIMARY       | PRIMARY | 8       | const |     1 | NULL            | |  2 | UNION        | test2      | ALL   | NULL          | NULL    | NULL    | NULL  | 67993 | NULL            | | NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  |  NULL | Using temporary | +----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+

dependent union： union中的第二个或随后的select查询，依赖于外部查询的结果集