mysql 优化利器 EXPLAIN （二）

EXPLAIN 介绍

先从一个最简单的查询开始：

Query-1:explain select zipcode,firstname,lastname from people;

mysql> explain select zipcode,firstname,lastname from people;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | index | NULL          | zipcode | 480     | NULL |    3 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

EXPLAIN输出结果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra几列。

id

Query-2:explain select zipcode from (select * from people a) b;

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     |  | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

id是用来顺序标识整个查询中SELELCT 语句的，通过上面这个简单的嵌套查询可以看到id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果，比如UNION语句：

Query-3:explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;

+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | people     | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where     |
|  2 | UNION        | people     | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where     |
| NULL | UNION RESULT |  | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)

select_type

SELECT语句的类型，可以有下面几种。

SIMPLE

最简单的SELECT查询，没有使用UNION或子查询。见Query-1。

PRIMARY

在嵌套的查询中是最外层的SELECT语句，在UNION查询中是最前面的SELECT语句。见Query-2和Query-3。

UNION

UNION中第二个以及后面的SELECT语句。见Query-3。

DERIVED

派生表SELECT语句中FROM子句中的SELECT语句。见Query-2。

UNION RESULT

一个UNION查询的结果。见Query-3。

DEPENDENT UNION

顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询。

Query-4:explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref  | rows | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
|  1 | PRIMARY            | people     | ALL    | NULL            | NULL    | NULL    | NULL |    3 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 | Using where     |
| NULL | UNION RESULT       |  | ALL    | NULL            | NULL    | NULL    | NULL | NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

Query-4中select id from people where zipcode = 200000的select_type为DEPENDENT UNION。

你也许很奇怪这条语句并没有依赖外部的查询啊。

这里顺带说下MySQL优化器对IN操作符的优化，优化器会将IN中的uncorrelated subquery优化成一个correlated subquery（关于correlated subquery参见这里）。

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2); 类似这样的不相关子查询语句会被重写成这样：

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
Query-5:explain select * from people o where exists (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000 and id = o.id);

+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref       | rows | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
|  1 | PRIMARY            | o          | ALL    | NULL            | NULL    | NULL    | NULL      |    3 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
| NULL | UNION RESULT       |  | ALL    | NULL            | NULL    | NULL    | NULL      | NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
4 rows in set (0.00 sec)

题外话：有时候MySQL优化器这种太过“聪明” 的做法会导致WHERE条件包含IN()的子查询语句性能有很大损失。可以参看《高性能MySQL第三版》6.5.1关联子查询一节。

SUBQUERY

子查询中第一个SELECT语句。

Query-6:explain select * from people where id = (select id from people where zipcode = 100000);

+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | index | zipcode       | zipcode | 480     | NULL  |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

DEPENDENT SUBQUERY

和DEPENDENT UNION相对UNION一样。见Query-5。

除了上述几种常见的select_type之外还有一些其他的这里就不一一介绍了，不同MySQL版本也不尽相同。

table

显示的这一行信息是关于哪一张表的。有时候并不是真正的表名。

Query-7:explain select * from (select * from (select * from people a) b ) c;

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     |  | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     |  | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  3 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.00 sec)

可以看到如果指定了别名就显示的别名。

N就是id值，指该id值对应的那一步操作的结果。

还有这种类型，出现在UNION语句中，见Query-4。

注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

数据库