高性能MySQL(EXPLAIN 附录D)

调用EXPLAIN

要使用EXPLAIN，只需要在查询的select关键字前面机上explain这个词。下面是一个简单的explain结果：

mysql> explain select 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
1 row in set (0.00 sec)

EXPLAIN有两个主要变种。EXPLAIN EXTENDED,EXPLAIN PARTITIONS。EXPLAIN 时 ，SELECT语句也会实际执行。EXPLAIN只是个近似结果，以下是一些相关限制。

• EXPLAIN不会显示触发器、存储过程、UDF的性能。
• 不会显示一个MySQL的内置优化
• 不区分具有相同名字的事物。例如内存排序和临时文件排序都使用"filesort"，对于磁盘和内存中的临时表都表示"Using temporary"

EXPLAIN中的列

• id列

编号标识SELECT所属的行。若语句中没有子查询或联合那么id应该是相同的1。否则内层的select语句一般会顺序编号，对应其在原始语句的位置。MySQL将SELECT分为简单和复杂两种，复杂查询主要三大类：简单子查询、from子句中的子查询、UNION查询。下面是一个简单的子查询，

#简单子查询
mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
|  1 | PRIMARY     | film  | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 | Using index |
|  2 | SUBQUERY    | actor | index | NULL          | idx_actor_last_name | 137     | NULL |  200 | Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+

#from子句子查询 这是有一个der临时表
mysql> explain select film_id from (select film_id from film) AS der;
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL               | NULL    | NULL | 1000 | NULL        |
|  2 | DERIVED     | film       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 | Using index |
+----+-------------+------------+-------+---------------+--------------------+---------+------+------+-------------+

#union查询的结果总是放到一个临时表里，因为不在原生SQL中，所以id列为null
mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

• select_type

这一列显示了对应航是简单还是复杂SELECT。SIMPLE 指简单查询，不熬扩子查询和UNION。如果包含复杂查询，则最外层显示PRIMARY,内层部分可能显示如下:
SUBQUERY 包含在select 查询列中的子查询。
DERIVED 包含在from子句的子查询，MySQL会递归执行并将结果放到一个临时表中。
UNION 在UNION中的第二个和随后的select被标记为union。如果union被from 子句中的子查询包含，那第一个select会被标记为DERIVED。
UNION RESULT 用来标记UNION的匿名临时表检索结果的select。

• table 列

这一列显示了对应航正在访问表的表名或别名。如下看一看到这条SQL的查询顺序，先关联actor&film_actor，再关联actor

mysql> explain select film.film_id from film inner join film_actor using(film_id) inner join actor using(actor_id);
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys          | key                 | key_len | ref                       | rows | Extra       |
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | actor      | index  | PRIMARY                | idx_actor_last_name | 137     | NULL                      |  200 | Using index |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY             | 2       | sakila.actor.actor_id     |   13 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY             | 2       | sakila.film_actor.film_id |    1 | Using index |
+----+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+

派生表和联合。FROM子句有子查询时，table列时<derivedN>的形式，其中N是子查询的id,指向下一个查询的行。
当有UNION时，UNION RESULT的table包含所有参与UNION的查询行id。下面我们来看一个复杂的查询例子,:

mysql> explain select actor_id, 
(select 1 from film_actor where film_actor.actor_id = der_1.actor_id limit 1) 
from  
(select actor_id from actor limit 5) as der_1 
union all select film_id, 
(select @var1 from rental limit 1) 
from 
(select film_id,
(select 1 from store limit 1) from film limit 5) as der_2;
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
| id | select_type          | table      | type  | possible_keys | key                 | key_len | ref            | rows  | Extra           |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
|  1 | PRIMARY              | <derived3> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  3 | DERIVED              | actor      | index | NULL          | idx_actor_last_name | 137     | NULL           |   200 | Using index     |
|  2 | DEPENDENT SUBQUERY   | film_actor | ref   | PRIMARY       | PRIMARY             | 2       | der_1.actor_id |    13 | Using index     |
|  4 | UNION                | <derived6> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  6 | DERIVED              | film       | index | NULL          | idx_fk_language_id  | 1       | NULL           |  1000 | Using index     |
|  7 | SUBQUERY             | store      | index | NULL          | idx_unique_manager  | 1       | NULL           |     2 | Using index     |
|  5 | UNCACHEABLE SUBQUERY | rental     | index | NULL          | idx_fk_staff_id     | 1       | NULL           | 16008 | Using index     |
| NULL | UNION RESULT         | <union1,4> | ALL   | NULL          | NULL                | NULL    | NULL           |  NULL | Using temporary |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+

#从上往下看，第一行id=1 table=<derived3> 指向id=3 的行建立的临时表，即原SQL 第一行从临时表der_1查询部分；
#第二行id=3 ,嵌套在from 子句中的子查询派生表，所以select_tyoe=DERIVED, 即原SQL 第四行 建立der_1临时表这部分 ；
#第三行id=2，DEPENDENT SUBQUERY依赖子查询，这里即原SQL中第二行依赖der_1即id=3的子查询派生表,所以她在id=3更高的行后面，暗示了在第二行后面执行；
#第四行id=4为UNION，意味着是原生SQL中UNION 后的第二个或之后的select，即原SQL第六行检索@var1这部分，table=<derived6>指向id=6的DRIVERD行;
#第五行id=6在原生SQL中第九行，定义der_2临时表的派生表子查询；
#第六行id=7 是一个简单子查询SUBQUERY，原生SQL定义der_2的select store 部分,另外因为下面一行id=5,所以id=6的DERIVED嵌套子查询到这一行结束；
#第七行id=5即原生SQL有用户变量@var1部分，这里已经不是派生表嵌套子查询部分，而逝检索派生表部分；
#最后一行UNION RESULT表明了从临时表中读取行的，这里1 ,4说明是读取id=1,4的行的临时表即<derived3>、<derived6> 

• type列

访问类型，换言之就是MySQL如何查找表中的行。
ALL: 俗称的全表扫描，通常意味着MySQL必须扫描整张表，去找到需要的行。如果查询中使用了LIMIT关键字，或者在Extra列中显示"Using distinct/not exists"。
index: 全索引扫描，跟全表扫描一样，不过是按索引次序进行而不是行顺序。优点是避免了排序，缺点是要按照索引次序回表读取的开销，因为此时回表很可能是随机访问，开销非常大。不过如果Extra列显示的是Using index，说明使用了覆盖索引，只扫描索引的数据，没有回表操作，开销要大大减少。
range: 范围扫描是一个有限制的索引扫描它开始于索引里的某一点，返回匹配这个值域的行，这笔全索引扫描好一些，因为用不着全部遍历，显然是带有BETWEEN或WHERE 子句带>的查询。MySQL中使用索引去查一系列值时，IN()或OR列表，也会显示为范围查询，但这两者其实是不同的访问类型。
ref: 索引访问，它返回所有匹配某个单个值的行，可能会找到多个符合条件的行。此类索引访问只有使用非唯一索引或者唯一性索引的非唯一性前缀时才会发生。取名ref因为索引要跟某个参考值作比较，这个参考值或者是一个常数或者是来自多表查询里一个表的结果值。ref_or_null是ref的一个变体，它表示MySQL必须在初次查找的结果中查找出NULL条目。
eq_ref: 对多只返回一条记录的索引查找。在使用主键或者唯一性索引查找时看到，它会将索引与某个参考值作比较。
const, system: 当MySQL能对查询的某部分进行优化并转换成一个常量时，就会使用这些访问类型。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system。
NULL: 这种访问方式意味着MySQL能在优化阶段分解语句，在执行阶段甚至用不着在访问表或者索引。例如，从一个索引列里选取最小值可以单独通过查找索引来完成。

• possible_keys列

这一列显示了查询可以使用那些索引。

• key列

这一列显示了MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在poosible_keys中，那么选用它是出于另外的原因，可能是选择了覆盖索引。
换句话说，possible_keys揭示了哪一个索引能有助于高效的行查找，key显示的是优化采用什么索引减少查询成本。

• key_len列

该列显示了MySQL在索引里使用的的字节数。

• ref列

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。

• rows列

这一列是MySQL为了找到所需的行而要读取的行数，注意并不是结果集里的行数。

• filtered列

显示的是针对表里符合某个调价你的记录数的百分比悲观估算。

• Extra

"Using index" 表示MySQL将使用覆盖索引，以避免表访问。
"Using where" 表示MySQL服务器将在存储引擎检索行后在进行过滤。有些WHERE条件里涉及索引当中的列，存储引擎读取索引时就可以进行校验，此时便不会显示Using Where。
"Using temporary" 这意味MySQL在对结果排序时会使用一个临时表。
"Using filesort" 这意味着MySQL会对结果使用一个外部索引排序，而不是按照索引次序。
"Range checked for each record(index map:N)" 这个值意味着没有好用的索引，新的索引在连接的每一行上重新估算。