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对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。