Effective MySQL之MySQL EXPLAIN命令详解

作为一名日常DBA，最常重复的任务就是在生产环境中检查和优化运行的SQL语句。在MySQL软件安装、配置以及正常运行之后，监控数据库的性能问题就成为一项经常重复的工作。了解如何正确地截取有问题的SQL语句以及检查并做适当的调整，这已经成为一个专业DBA的必备技能。尽管MySQL是一个关系型数据库管理系统(RDBMS)，有Oracle或者SQL Server背景的有经验的数据库管理员还是需要学习如何在MySQL术语中正确地应用SQL查询分析理论，而这需要阅读并理解查询执行计划(QEP)，了解MySQL优化器功能的限制和不足，还要理解不同的MySQL存储引擎是如何改变索引的高效使用方式的。

SQL语句的优化不仅仅是数据库管理员的责任。本书将帮助读者理解MySQL索引和存储引擎是如何运行的，这对一个由数据架构师设计的优化过的数据库来说是更重要的实现考虑因素。软件开发人员将能够截取和分析所有SQL语句，以此来确保性能瓶颈能够在开发早期被发现然后由合适的人去处理。

优化SQL语句是改进性能和扩展性的一个关键部分。

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL优化器是如何执行SQL语句的。这条命令并没有提供任何调整建议，但它能够提供重要的信息帮助你做出调优决策。

在最后的这一章中，我们将详细介绍EXPLAIN命令的全部语法和选项。

语法

QEP是通过EXPLAIN命令生成的，它的语法包含两项：

mysql> EXPLAIN [EXTENDED | PARTITIONS ]-> SELECT ...

或者

mysql> EXPLAIN table

的EXPLAIN语法可以运行在SELECT语句或者特定表上。如果作用在表上，那么此命令等同于DESC表命令。UPDATE和DELETE命令也需要进行性能改进，当这些命令不是直接在表的主码上运行时，为了确保最优化的索引使用率，需要把它们改写成SELECT语句(以便对它们执行EXPLAIN命令)。请看下面的示例：

UPDATE  table1SET             col1= X, col2 = YWHERE       id1 = 9 AND             dt >='2010-01-01

这个UPDATE语句可以被重写成为下面这样的SELECT语句：

SELECT  col1, col2FROM            table1WHERE       id1= 9AND             dt >='2010-01-01';

MySQL优化器是基于开销来工作的，它并不提供任何的QEP的位置。这意味着QEP是在每条SQL语句执行的时候动态地计算出来的。在MySQL存储过程中的SQL语句也是在每次执行时计算QEP的。存储过程缓存仅仅解析查询树。

各列详解

MySQL EXPLAIN命令能够为SQL语句中的每个表生成以下信息：

mysql> EXPLAIN SELECT * FROM inventory ->WHERE item_id = 16102176\G********************* 1. row***********************id:1 select_type:SIMPLE table:inventory type:ALLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows:787338 Extra: Using where

这个QEP显示没有使用任何索引(也就是全表扫描)并且处理了大量的行来满足查询。对同样一条SELECT语句，一个优化过的QEP如下所示：

mysql> EXPLAIN SELECT * FROM inventory ->WHERE item_id = 16102176\G********************* 1. row***********************id:1 select_type: SIMPLE table:inventory type:refpossible_keys: item_id key:item_id key_len:4 ref:const rows:1 Extra:

在这个QEP中，我们看到使用了一个索引，且估计只有一行数据将被获取。本章中将会详细介绍如何读取并解释这些信息。QEP中每个行的所有列表如下所示：

• id
• select_type
• table
• partitions(这一列只有在EXPLAIN PARTITIONS语法中才会出现)
• possible_keys
• key
• key_len
• ref
• rows
• filtered(这一列只有在EXPLAINED EXTENDED语法中才会出现)
• Extra

这些列展示了SELECT语句对每一个表的QEP。一个表可能和一个物理模式表或者在SQL执行时生成的内部临时表(例如从子查询或者合并操作会产生内部临时表)相关联。

可以参考MySQLReference Manual获得更多信息：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。

下面我们将按照分析以及快速有效地理解QEP的重要程度的顺序来介绍这些列。

key

key列指出优化器选择使用的索引。一般来说SQL查询中的每个表都仅使用一个索引。也存在索引合并的少数例外情况，如给定表上用到了两个或者更多索引。

下面是QEP中key列的示例：

key: item_idkey: NULLkey: first, last

SHOW CREATE TABLE <table>命令是最简单的查看表和索引列细节的方式。

和key列相关的列还包括possible_keys、rows以及key_len。

rows

rows列提供了试图分析所有存在于累计结果集中的行数目的MySQL优化器估计值。QEP很容易描述这个很困难的统计量。查询中总的读操作数量是基于合并之前行的每一行的rows值的连续积累而得出的。这是一种嵌套行算法。

以连接两个表的QEP为例。通过id=1这个条件找到的第一行的rows值为1，这等于对第一个表做了一次读操作。第二行是通过id=2找到的，rows的值为5。这等于有5次读操作符合当前1的积累量。参考两个表，读操作的总数目是6。在另一个QEP中，第一rows的值是5，第二rows的值是1。这等于第一个表有5次读操作，对5个积累量中每个都有一个读操作。因此两个表总的读操作的次数是10(5+5)次。

最好的估计值是1，一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。

在下面的QEP中，外面的嵌套循环可以通过id=1来找到，其估计的物理行数是1。第二个循环处理了10行。

********************* 1. row ***********************id:1 select_type:SIMPLE table:p type:constpossible_keys: PRIMARY key:PRIMARY key_len:4 ref:const rows:1 Extra:********************* 2. row***********************id:1 select_type:SIMPLE table:c    type:refpossible_keys:parent_id key:parent_id key_len:4ref:const rows:10 Extra: 

可以使用SHOW STATUS命令来查看实际的行操作。这个命令可以提供最佳的确认物理行操作的方式。请看下面的示例：

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';+--------------------------------------+---------+| Variable_name                        | Value   |+--------------------------------------+---------+| Handler_read_first                   | 0       || Handler_read_key                     | 5       || Handler_read_last                    | 0       || Handler_read_next                    | 10      || Handler_read_prev                    | 0       || Handler_read_rnd                     | 0       || Handler_read_rnd_next                | 0       |+--------------------------------------+---------+

在下一个QEP中，通过id=1找到的外层嵌套循环估计有160行。第二个循环估计有1行。

********************* 1. row ***********************id: 1select_type:SIMPLE table:p type:ALLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows:160 Extra:********************* 2. row ***********************id:1 select _type: SIMPLE table:c type:refpossible_keys:PRIMARY,parent_id key:parent_id key_len:4 ref:test.p.parent_id rows:1 Extra: Using where

通过SHOW STATUS命令可以查看实际的行操作，该命令表明物理读操作数量大幅增加。请看下面的示例：

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

+--------------------------------------+---------+| Variable_name                        | Value   |+--------------------------------------+---------+| Handler_read_first                   | 1       || Handler_read_key                     | 164     || Handler_read_last                    | 0       || Handler_read_next                    | 107     || Handler_read_prev                    | 0       || Handler_read_rnd                     | 0       || Handler_read_rnd_next                | 161     |+--------------------------------------+---------+

相关的QEP列还包括key列。

possible_keys

possible_keys列指出优化器为查询选定的索引。

一个会列出大量可能的索引(例如多于3个)的QEP意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。可以用第2章详细介绍过的SHOWINDEXES命令来检查索引是否有效且是否具有合适的基数。

为查询确定QEP的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。

相关的QEP列还包括key列。

key_len

key_len列定义了用于SQL语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。

此列的一些示例值如下所示：

key_len: 4      // INT NOT NULLkey_len: 5      //INT NULL key_len: 30 // CHAR(30) NOT NULL key_len: 32     //VARCHAR(30) NOT NULLkey_len: 92     // VARCHAR(30)NULL CHARSET=utf8

从这些示例中可以看出，是否可以为空、可变长度的列以及字符集都会影响到表索引的内部内存大小。

key_len列的值只和用在连接和WHERE条件中的索引的列有关。索引中的其他列会在ORDER BY或者GROUP BY语句中被用到。

下面这个来自于著名的开源博客软件WordPress的表展示了如何以最佳方式使用带有定义好的表索引的SQL语句：

CREATE TABLE `wp_posts` ( `ID` bigint(20)unsigned NOT NULL AUTO_INCREMENT,... `post_date` datetimeNOT NULL DEFAULT '0000-00-0000:00:00', `post_status`varchar(20) NOT NULL DEFAULT'publish',`post_type`varchar(20) NOT NULL DEFAULT 'post',... PRIMARY KEY (`ID`), KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),) DEFAULT CHARSET=utf8

这个表的索引包括post_type、post_status、post_date以及ID列。下面是一个演示索引列用法的SQL查询：

mysql> EXPLAIN SELECT ID, post_title-> FROM wp_posts -> WHEREpost_type='post' ->AND post_date > '2010-06-01';

这个查询的QEP返回的key_len是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE语句中使用了post_type和post_date列，但只有post_type部分被用到了。其他索引没有被使用的原因是MySQL只能使用定义索引的最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

mysql> EXPLAIN SELECT ID, post_title->FROM wp_posts ->WHERE post_type='post' -> AND post_status='publish'-> AND post_date > '2010-06-01';

在SELECT查询的添加一个post_status列的限制条件后，QEP显示key_len的值为132，这意味着post_type、post_status、post_date三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID的定义是使用MyISAM存储索引的遗留痕迹。当使用InnoDB存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len的用法看出来。

相关的QEP列还包括带有Usingindex值的Extra列。

table

table列是EXPLAIN命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合。

下面是QEP中table列的一些示例：

table: itemtable: <derivedN>table: <unionN,M>

表中N和M的值参考了另一个符合id列值的table行。

相关的QEP列还有select_type。

select_type

select_type列提供了各种表示table列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED和UNION。其他可能的值还有UNION RESULT、DEPENDENTSUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION以及UNCACHEABLE QUERY。

1. SIMPLE

对于不包含子查询和其他复杂语法的简单查询，这是一个常见的类型。

2. PRIMARY

这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED和UNION类型混合使用时见到。

3. DERIVED

当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL语句给出了一个QEP中DERIVEDselect-type类型的示例：

mysql> EXPLAIN SELECT MAX(id)-> FROM (SELECT id FROM users WHERE first ='west') c;

4. DEPENDENT SUBQUERY

这个select-type值是为使用子查询而定义的。下面的SQL语句提供了这个值：

mysql> EXPLAIN SELECT p.*->FROM parent p->WHERE p.id NOT IN (SELECT c.parent_id FROM childc);

5. UNION

这是UNION语句其中的一个SQL元素。

6. UNION RESULT

这是一系列定义在UNION语句中的表的返回结果。当select_type为这个值时，经常可以看到table的值是<unionN,M>，这说明匹配的id行是这个集合的一部分。

下面的SQL产生了一个UNION和UNIONRESULT select-type：

mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.valLIKE 'a%'->UNION->SELECT p.* FROM parent p WHERE p.id > 5;

partitions

partitions列代表给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS语句中出现。

Extra

Extra列提供了有关不同种类的MySQL优化器路径的一系列额外信息。Extra列可以包含多个值，可以有很多不同的取值，并且这些值还在随着MySQL新版本的发布而进一步增加。下面给出常用值的列表。你可以从下面的地址找到更全面的值的列表：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。

1. Using where

这个值表示查询使用了where语句来处理结果——例如执行全表扫描。如果也用到了索引，那么行的限制条件是通过获取必要的数据之后处理读缓冲区来实现的。

2. Using temporary

这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的列上使用了DISTINCT，或者使用了不同的ORDER BY和GROUP BY列。

想了解更多内容可以访问http://forge.mysql.com/wiki/Overview_ of_query_execution_and_use_of_temp_tables。

可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：

• 内部临时表占用的空间超过min(tmp_table_size，max_ heap_table_size)系统变量的限制
• 使用了TEXT/BLOB列

3. Using filesort

这是ORDER BY语句的结果。这可能是一个CPU密集型的过程。

可以通过选择合适的索引来改进性能，用索引来为查询结果排序。详细过程请参考第4章。

4. Using index

这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。请参考第5章的详细示例来理解这个值。

5. Using join buffer

这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。

如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。

6. Impossible where

这个值强调了where语句会导致没有符合条件的行。请看下面的示例：

mysql> EXPLAIN SELECT * FROMuser WHERE 1=2;

7. Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。请看下面的示例：

mysql> EXPLAIN SELECT COUNT(*)-> FROM (SELECT id FROM users WHERE first = 'west') c

8. Distinct

这个值意味着MySQL在找到第一个匹配的行之后就会停止搜索其他行。

9. Index merges

当MySQL决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。

• Using sort_union(...)
• Using union(...)
• Using intersect(...)

id

id列是在QEP中展示的表的连续引用。

ref

ref列可以被用来标识那些用来进行索引比较的列或者常量。

filtered

filtered列给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表。

这一列只有在EXPLAIN EXTENDED语句中才会出现。

type

type列代表QEP中指定的表使用的连接方式。下面是最常用的几种连接方式：

• const           当这个表最多只有一行匹配的行时出现
• system          这是const的特例，当表只有一个row时会出现
• eq_ref          这个值表示有一行是为了每个之前确定的表而读取的
• ref             这个值表示所有具有匹配的索引值的行都被用到
• range           这个值表示所有符合一个给定范围值的索引行都被用到
• ALL             这个值表示需要一次全表扫描

其他类型的值还有fulltext、ref_or_null、index_merge、unique_subquery、index_subquery以及index。

想了解更多信息可以访问http://dev.mysql.com/doc/refman/ 5.5/en/explain-output.html。

解释EXPLAIN输出结果

理解你的应用程序(包括技术和实现可能性)和优化SQL语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP会显示三种不同的路径。

mysql> EXPLAIN SELECT p.* ->FROM parent p ->WHERE p.id NOT IN (SELECT c.parent_id FROM childc)\G********************* 1. row***********************id: 1select _type: PRIMARY table:p type:ALLpossible_keys: NULL key:NULL key_len:NULL ref:NULL rows:160 Extra:Using where********************* 2. row ***********************id: 2 select_type:DEPENDENT SUBQUERYtable:c type: index_subquerypossible_keys: parent_idkey: parent_id key_len:4 ref: func rows: 1Extra:Using index2 rows in set (0.00 sec) mysql> EXPLAIN SELECT p.* ->FROM parent p ->LEFT JOIN child c ON p.id = c.parent_id->WHERE c.child_id IS NULL\G********************* 1. row***********************id:1 select_type:SIMPLE table:p type:ALLpossible_keys:NULL key:NULL key_len:NULL ref:NULL rows:160 Extra:********************* 2. row***********************id:1 select_type: SIMPLE table:c type: refpossible_keys:parent_idkey:parent_id key_len:4 ref:test.p.id rows:1 Extra:Using where; Using index; Not exists2 rows in set (0.00 sec) mysql> EXPLAIN SELECT p.* ->FROM parent p ->WHERE NOT EXISTS ->SELECT parent_id FROM child c WHERE c.parent_id=p.id)\G********************* 1. row***********************id:1 select_type:PRIMARY table:p type:ALLpossible_keys: NULL key:NULLkey_len:NULL ref:NULL rows:160 Extra:Using where********************* 2. row***********************id:2 select_type:DEPENDENT SUBQUERY table:c type:refpossible_keys:parent_id key:parent_id key_len:4 ref:test.p.id rows:1 Extra:Using index2 rows in set (0.00 sec)

哪种方式是最好的？随着数据的增长另一种QEP会更高效吗？这本书的目的就是介绍那些用来优化你的SQL语句的工具和方法。

小结

使用EXPLAIN命令阅读并理解MySQL查询执行计划(QEP)是获取优化SQL语句的信息的不可或缺的途径之一。把这个命令和本书中介绍的其他来源的信息相结合就能够确保你掌握足够的数据来做出合理的选择。

 

《Effective MySQL之SQL语句最优化》将在2013年1月出版，需要本书试读样章可留下信箱。

数据库QQ交流群：11838669，长期有赠书活动。