【JAVA核心知识】33：MySQL EXPLAIN的使用 ---《高性能MySQL》读书笔记

EXPLAIN用来获取关于SQL的执行计划信息。MySQL 5.6之前能进对SELECT语句进行解释，MySQL 5.6开始能对UPDATE，INSERT，DELETE进行解释。EXPLAIN具有局限性，并不总会说出真相，但已经是可以获取的最好信息。
要使用EXPLAIN，只需要在SQL之前增加EXPLAIN这个关键词。他会返回一行或多行信息，显示执行计划中的每一部分和执行次序。
一个常见的错误是：增加EXPLAIN时SQL不会实际执行。有时间我们用这个特性来快速获得要求不是那么精确的行数信息。然而这个结论并不全面，在简单SQL中确实如此，如：

explain select * from my_table where ID = '0'

查询语句确实不会执行。但是如果涉及子查询：

explain select * from my_table where ID = (select id from my_table2 where mu = 't')

那么MySQL实际上会执行子查询。子查询的结果将放在一个临时表中以完成外层查询解释。因此如果语句中包含开销较大的子查询或是使用临时表算法的视图，即使是EXPLAIN，也依然会对服务器带来大量的工作。但是MySQL 5.6好像进行了改进，允许直接解释带子查询的查询语句，而不需要实际的执行子查询。

EXPLAIN中的列

id列

编号列，标识查询所属的行。如果没有联合查询或者子查询，那么就只有唯一的一行，编号为1。如果有联合查询或者子查询，1位最外层的查询，根据层级编号逐次增加。如果某一环节读取的是临时表，那么id列就是NULL。

select_type列

定义SQL类型，如果只是简单的没有联合查询或者子查询的SQL。那么SELECT语句就是SIMPLE(简单查询)，INSERT就是INSERT,DELETE就是DELETE，UPDATE就是UPDATE。
如果是复杂查询，那么最外层标记(也就是id为1的)为PRIMARY(主查询)，内部查询可能会有以下值：

• SUBQUERY：不在FROM内的子查询。如下面括号内的查询语句

explain select * from my_table where ID = (select id from my_table2 where mu = 't')

• DERIVED：FROM内的子查询，如下面括号内的查询语句

explain select * from (select * from my_table2) as t where t.mu = 't'; // 要定义别名哦，MySQL要求每个派生表都必须有自己的别名

• UNION：UNION语句中的第二个和随后的查询被标记为UNION，如下面对my_table2的查询语句

explain select * from my_table1 union select * from my_table2

• UNION RESULT：对UNION的临时表的子查询，如下面括号内的查询语句

explain select * from (select * from my_table1 union select * from my_table2)

table列

显示查询在访问那个表。一般情况下，这个字段相当明了，就是目标表或者表的别名。当涉及到派生表和联合查询时，因为没有具体的表名，这个列会展示涉及到哪个子查询。如<union1,2>意味会用到id为1的那个子查询和id为2的那个子查询。

type列

查询类型-就是MySQL决定如何去查询表中的行。是一个很重要的指标。性能从差到优有以下几种类型：

• ALL：全表扫描。按照数据的存储顺序。
• index：同样是全表扫描，不过是按照索引的顺序进行扫描。好处是避免了排序，缺点是通常意味着随机访问，开销会更大。如果Extra列含有"Using index",说明使用了覆盖索引。比扫描数据行会好很多。
• range：范围扫描就是一个有限的索引扫描。range开始于索引中的某一点。比全索引扫描好一些。此种一般对应带有BETWEEN或者在WHERE子句中带有>的查询。IN()和OR也会显示为range,但是与BETWEEN是有本质区别的，IN()和OR属于多个等值查询，他们与BETWEEN的效率是不同的。且BETWEEN无法再使用范围列后面的索引列了。IN()和OR则没这个限制。
• ref：索引查找，返回匹配的值。这种可能会找到多个符合条件的行。此类型意味着使用了非唯一性索引或者唯一性索引的非唯一性前缀。叫做ref是因为索引要与一个参考值相比较。ref_or_null是ref的一个变体。意味着MySQL需要在除此查找的结果里进行二次查找以找出NULL条目。
• eq_ref：索引查找，最多返回一行记录。此类型意味着使用了主键索引或者唯一性索引。对于这种访问类型，MySQL可以在找到匹配行时立即返回，而不用全索引查找。
• const,system：MySQL能对查询的某部分进行优化并将其转换成一个常量时，就是这个访问类型。比如 select id from myTable where id = ‘1’;。此时MySQL将这个查询转换为一个常量，从而高效的将表从联接执行中移除。
• NULL：意味着SQL能在优化阶段分解查询语句。执行阶段甚至无需访问表或索引。如：查找一个索引列的最小值，此时无需访问表就能完成。

possible_keys列

这个列显示可以使用的索引。注意是可以使用的，而不是肯定使用的。这个信息根据查询访问的列和使用的比较操作符来判断，这个列表是在优化过程的早期创建的。因此有些罗列出来的索引可能在后续的优化过程中是用不到的。

key列

这个列显示MySQL最终决定使用的索引。如果这个所以没出现在possible_keys列，那么说明MySQL出于一些其他原因选择了另外的索引，比如选择了一个覆盖索引。

key_len列

该列显示了MySQL在索引里使用的字节数。通过这个值结合最左前缀可以算出具体使用了索引的哪些列。如定义一个索引（a,b,c） a为char(3), b为int(11), c为char(1).key列标识最后使用了这个索引。 key_len列为13。 那么a列3个字符，utf-8下每个最多3字节(计算长度时间要考虑字符集的影响)，b列为一个4字节整型。 3*3+4=13.即这个索引实际上只使用了a,b两列。 MySQL并不会显示索引实际使用的多少，比如使用前缀模式LIKE查询，他会显示整个列的宽带。计算也是索引字段你的最大长度，而不是实际使用字节数。比如char(3),就按照3算，即使表中都没有长度为3的字符值。

ref列

此列展示了与key列比较的列或者常量。假设key列为 c_a.如果是 where c_a = 't' 那么这个列就是const，意思是和一个常量比较。如果是 on t.c_a = e.c_b,那么这个列就是e.c_b,意思是c_a列和 e表的c_b列进行的比较。

rows列

此列展示MySQL预估为了找到所需要的行而要读取的行数。注意是MySQL认为他要检查的行数，而不是结果集的行数。这个值是一个估算值，可能很不精确。对于不要求那么精准的行数查询，可以用这个结果代替select count(*)的结果,因为EXPLAIN不必真的执行，会高效很多。

filtered列

使用EXPLAIN EXTENDED时出现。显示的是返回的行数占据扫描行数的悲观百分比估算。假设rows列为1000，filtered列为10.0，那么就是执行计划估算要扫描的1000行里只有10%会作为结果返回。这个一个悲观估算，一般情况下实际返回结果都会大于这个比率。 这个值越大代表着无用扫描越少。用主键查询时就显示的100.0，即估算扫描的行数都会作为结果返回，不包含无用扫描。

Extra列

这一列为扩展列，用来展示不适合在其他列显示的额外信息。常见的重要值如下：

• Using index:此值表示MySQL将使用覆盖索引，以避免访问表。注意哦：这个是Extra的Using index, 不是type列的index。别弄混了。
• Using where:意味着MySQL服务器将在存储引擎检索行后再进行过滤。换句话说就是：仅靠索引无法精准定位数据行，需要将数据行读取出来然后再进行比较过滤。
• Using temporary：表示使用了临时表
• Using filesort：对查询结果使用一个外部索引排序。即无法利用索引来完成排序，需要用文件排序的方式完成排序。
• Range check for each record：联表查询时没有合适的索引使用，需要对每一行进行检查。

用EXPLAN仅仅可以获得SQL的执行策略，如果需要进一步探查SQL的执行情况，则需要使用profile来完成了。

PS：
【JAVA核心知识】系列导航 [持续更新中…]
关联导航：MySQL架构基础
关联导航：MySQL数据类型选择与设计
关联导航：创建高性能的索引
关联导航：查询性能优化
关联导航：使用profile进一步分析SQL资源消耗
欢迎关注…