【MySQL札记】SQL优化利器 - explain命令的输出格式详解
【MySQL笔记】SQL优化利器 - explain命令的输出格式详解
explain命令的语法说明见官网文档，这里略过。
该命令主要作用是输出MySQL的optimizer对SQL的执行计划，也即，MySQL会解释如何处理输入的SQL（是否使用索引，使用哪个索引，多表以什么顺序及什么关联字段做join）。
explain的提示可以帮助大家意识到哪些字段应该建索引，也可以帮大家确认SQL执行时optimizer是否会以合理的顺序来join多张表。比如若有类似这样的SQL语句：
select t1.id, t2.link, t3.detail from t1, t2, t3 where t1.id < 100 and t1.id = t2.base_id and t3.link_sign = t2.sign;
该语句执行时，optimizer不一定会以from列出的表顺序来join这3张表，而表的join顺序很可能会影响SQL性能。
这种场景下，如果想让optimizer以from语句列出的表顺序做join，有2种方法：
1) 在select关键字后添加STRAIGHT_JOIN来提示optimizer按from列出的表顺序来join，具体语法见SELECT文档
2) 调整sql where条件中各表关联字段在等号前后的位置
explain命令会为SQL中出现的每张表返回一行信息来说明optimizer将会如何操作这张表，其输出中列出的表次序也是MySQL实际执行SQL时对各表的处理顺序。
MySQL以nested-loop算法处理所有的join操作，算法原理说明在这里，对认识join的行为有帮助，建议理解。
explain针对每张表输出的每行记录均包含下面几个字段：
下面分别进行说明。
1) id
该字段标识select语句id，若SQL中只有1个select语句（即使是多表关联查询），则该值为1，否则依次递增；若SQL是union的结果，则该值为NULL。
2) select_type
该字段说明select语句的类型，其可能的取值如下图（来自官网文档）：
其中，simple是最常见的类型，表明SQL只包含1个select语句；derived表明该行代表的数据表（derived table）其实是from子句中包含的子查询的输出结果；其余类型较易理解，阅读官方文档即可，这里不赘述。
3) table
该字段表明explain输出的每行所代表的数据集来自哪张表，其值通常是具体的表名，当数据集是union的结果时，其值可能
是<unionM,N>，当数据集来自derived table时，其值可能是<derivedN>。这里提到的M或N均是id字段的值。
4) type
该字段表明各表是如何被join的，其取值比较复杂，详细可参考官网文档。这里只列出最常见的几种取值。
a. system/const
const表明上述"table"字段代表的数据集中，最多只有1行记录命中本步执行计划的查询条件，例如这步执行计划的sql的where
子句以某张表的primary key或unique index与常数做比较时，该执行计划对应的type字段取值就是const。
system只是const值的一个特例，它表示本步执行计划要操作的数据集中只有1行记录。
它们只可能出现在单表查询SQL的type字段取值中。
b. eq_ref
该值表明本步执行计划操作的数据集中关联字段是索引字段且只有1条记录符合上步执行计划操作的数据集的关联条件。这
是对多表做关联查询时，可能得到的最优的join类型（因为它通常表明关联的字段是本步执行计划要操作的表的primary key或unique index）。
c. ref
该值表明本步执行计划操作的数据集中关联字段是索引字段但不只有1条记录符合上步执行计划操作的数据集的关联条件。
符合关联条件的记录不只1条表明关联字段非primary key或unique index，当符合关联条件的记录数比较少时，这种join_type='ref'的场景还是比较合理的，但它显然不如join_type='eq_ref'高效。
d. ref_or_null
该join type类型与ref的场景类似，但它表明MySQL会对包含NULL值的字段做额外搜索。例如下面SQL的join type就是ref_or_null：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
e. index_merge
该值表明MySQL会对本步执行计划进行index merge优化，触发index merge的SQL通常包含'or'操作，常见实例如下：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
f. range
该值表明本步执行计划只操作单表且符合查询条件的记录不只1条，可能出现在有in或between操作的SQL中。
只限于单表操作场景也是其与前面提到的join_type取值为'ref'场景的区别，因为ref可能出现在单表/多表join操作场景下。
g. ALL
该值表明本步执行计划会对数据集做全表扫描，这是必须做优化的场景。通常可以通过对某些字段合理建索引来避免全表扫描。
h. index
该值表明MySQL执行本步计划时扫描的是index tree，而ALL则是扫全表。它可能在两种场景下出现：
case1. 本步计划查询数据集时select语句需要返回的字段是该数据集索引字段的最左前缀匹配集。如table A中已建立含2个字段的联合索引(f1, f2)，则select f1 from A where f2 = 'yyy'可能会触发MySQL扫描index tree，这种情况下，执行计划的Extra字段会包含"Using index"来表明它扫描的是index tree，因为f1是(f1, f2)最终前缀匹配集中的1个元素；而select f2 from A where f2 = 'yyy'则会扫描全表。
case2. 本步计划按照索引顺序进行全表扫描来查找符合条件的数据。这种情况下，执行计划的Extra字段不会包含"Using index"，这种全表扫描也是必须优化的场景。
5) possible_keys
该字段的值是可能被MySQL用作索引的字段，若值为NULL，则没有字段会被用作索引，因此查询效率不会高，这种情况下，需要优化数据表的索引结构。
6) key
该字段的值是MySQL真正用到的索引。
值得注意的是：该字段的值有可能不是possible_keys列出的候选索引字段，例如，当前查询SQL要返回的字段是数据表某索引字段的最左前缀匹配字段，但SQL的where条件中没有使用数据表的索引字段，则此时possible_keys可能为NULL，而key字段的值可能是那个能cover住待查询字段的数据表索引字段，此时，MySQL会扫描索引树，虽然低效，但比起扫描全表还是要快。这种场景也正是本文前面解释join_type='index'时提到的case1。
此外，在select语句中借助"force index或"use index"可以强制MySQL使用possible_keys中列出的候选索引字段。
7) key_len
该字段的值表明上述key字段的length，当MySQL将某联合索引字段作为SQL执行时用到的索引时，key_len字段可以暗示MySQL真正在什么程度上（多长的最左前缀匹配字段）使用了该联合索引。若key字段的值为NULL，则key_len字段值也为NULL。
8) ref
该字段的值表明数据表中的哪列或哪个constants会被用于与key字段指定的索引做比较。
9) rows
该字段的值表明MySQL执行该步计划对应的query时必须扫描的行数。这个值对于SQL优化非常具有参考意义，通常情况下，该值越小查询效率越高。
10) Extra
该字段的值包含了MySQL执行query时的其它额外信息。该字段可能的取值情况较多，详细情况可参考官网文档的说明。
如果理解了explain输出结果中每个字段背后的含义，则据此优化SQL性能会变得高效且有依据。
在工程实践中利用explain来trouble shoot低效SQL的思路，跟工程师的经验和能力有关，这里推荐几篇技术资料来抛砖引玉。
1) Oreilly官网中的一篇PDF分享：Explain Demystified
2) slideshare上的一篇分享文档：Mysql Explain Explained，该文档循序渐进地解释了MySQL explain的输出及据此优化SQL的典型思路，值得一读。
3) 美团官方技术博客的一篇文章：MySQL索引原理及慢查询优化，文中介绍了B+Tree的原理，并给出了几个利用explain来优化SQL的工程场景，也值得精读。
有MySQL使用经验的同学在实际项目中可能会遇到SQL慢查询的场景，有些场景很容易定位问题所在（如单表操作有慢查询SQL时，仔细check SQL语句通常很容易定位索引问题），而有些复杂业务场景下（如多表联合查询几十个字段并做group或sort等操作），人工check SQL语句通常很难发现SQL瓶颈根源。这个时候，MySQL提供的explain命令就派上用场了。
本笔记主要对explain的输出结果做说明，并给出根据explain输出对SQL做优化的思路。
1. EXPLAIN语法及用途explain命令的语法说明见官网文档，这里略过。
该命令主要作用是输出MySQL的optimizer对SQL的执行计划，也即，MySQL会解释如何处理输入的SQL（是否使用索引，使用哪个索引，多表以什么顺序及什么关联字段做join）。
explain的提示可以帮助大家意识到哪些字段应该建索引，也可以帮大家确认SQL执行时optimizer是否会以合理的顺序来join多张表。比如若有类似这样的SQL语句：
select t1.id, t2.link, t3.detail from t1, t2, t3 where t1.id < 100 and t1.id = t2.base_id and t3.link_sign = t2.sign;
该语句执行时，optimizer不一定会以from列出的表顺序来join这3张表，而表的join顺序很可能会影响SQL性能。
这种场景下，如果想让optimizer以from语句列出的表顺序做join，有2种方法：
1) 在select关键字后添加STRAIGHT_JOIN来提示optimizer按from列出的表顺序来join，具体语法见SELECT文档
2) 调整sql where条件中各表关联字段在等号前后的位置
本文下面的内容会说明如何通过explain输出来确定多表join时optimizer对各表的执行次序，以及如何调整SQL来影响optimizer的执行计划。
2. EXPLAIN输出格式说明explain命令会为SQL中出现的每张表返回一行信息来说明optimizer将会如何操作这张表，其输出中列出的表次序也是MySQL实际执行SQL时对各表的处理顺序。
MySQL以nested-loop算法处理所有的join操作，算法原理说明在这里，对认识join的行为有帮助，建议理解。
explain针对每张表输出的每行记录均包含下面几个字段：
下面分别进行说明。
1) id
该字段标识select语句id，若SQL中只有1个select语句（即使是多表关联查询），则该值为1，否则依次递增；若SQL是union的结果，则该值为NULL。
2) select_type
该字段说明select语句的类型，其可能的取值如下图（来自官网文档）：
其中，simple是最常见的类型，表明SQL只包含1个select语句；derived表明该行代表的数据表（derived table）其实是from子句中包含的子查询的输出结果；其余类型较易理解，阅读官方文档即可，这里不赘述。
3) table
该字段表明explain输出的每行所代表的数据集来自哪张表，其值通常是具体的表名，当数据集是union的结果时，其值可能
是<unionM,N>，当数据集来自derived table时，其值可能是<derivedN>。这里提到的M或N均是id字段的值。
4) type
该字段表明各表是如何被join的，其取值比较复杂，详细可参考官网文档。这里只列出最常见的几种取值。
a. system/const
const表明上述"table"字段代表的数据集中，最多只有1行记录命中本步执行计划的查询条件，例如这步执行计划的sql的where
子句以某张表的primary key或unique index与常数做比较时，该执行计划对应的type字段取值就是const。
system只是const值的一个特例，它表示本步执行计划要操作的数据集中只有1行记录。
它们只可能出现在单表查询SQL的type字段取值中。
b. eq_ref
该值表明本步执行计划操作的数据集中关联字段是索引字段且只有1条记录符合上步执行计划操作的数据集的关联条件。这
是对多表做关联查询时，可能得到的最优的join类型（因为它通常表明关联的字段是本步执行计划要操作的表的primary key或unique index）。
c. ref
该值表明本步执行计划操作的数据集中关联字段是索引字段但不只有1条记录符合上步执行计划操作的数据集的关联条件。
符合关联条件的记录不只1条表明关联字段非primary key或unique index，当符合关联条件的记录数比较少时，这种join_type='ref'的场景还是比较合理的，但它显然不如join_type='eq_ref'高效。
d. ref_or_null
该join type类型与ref的场景类似，但它表明MySQL会对包含NULL值的字段做额外搜索。例如下面SQL的join type就是ref_or_null：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
e. index_merge
该值表明MySQL会对本步执行计划进行index merge优化，触发index merge的SQL通常包含'or'操作，常见实例如下：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
f. range
该值表明本步执行计划只操作单表且符合查询条件的记录不只1条，可能出现在有in或between操作的SQL中。
只限于单表操作场景也是其与前面提到的join_type取值为'ref'场景的区别，因为ref可能出现在单表/多表join操作场景下。
g. ALL
该值表明本步执行计划会对数据集做全表扫描，这是必须做优化的场景。通常可以通过对某些字段合理建索引来避免全表扫描。
h. index
该值表明MySQL执行本步计划时扫描的是index tree，而ALL则是扫全表。它可能在两种场景下出现：
case1. 本步计划查询数据集时select语句需要返回的字段是该数据集索引字段的最左前缀匹配集。如table A中已建立含2个字段的联合索引(f1, f2)，则select f1 from A where f2 = 'yyy'可能会触发MySQL扫描index tree，这种情况下，执行计划的Extra字段会包含"Using index"来表明它扫描的是index tree，因为f1是(f1, f2)最终前缀匹配集中的1个元素；而select f2 from A where f2 = 'yyy'则会扫描全表。
case2. 本步计划按照索引顺序进行全表扫描来查找符合条件的数据。这种情况下，执行计划的Extra字段不会包含"Using index"，这种全表扫描也是必须优化的场景。
5) possible_keys
该字段的值是可能被MySQL用作索引的字段，若值为NULL，则没有字段会被用作索引，因此查询效率不会高，这种情况下，需要优化数据表的索引结构。
6) key
该字段的值是MySQL真正用到的索引。
值得注意的是：该字段的值有可能不是possible_keys列出的候选索引字段，例如，当前查询SQL要返回的字段是数据表某索引字段的最左前缀匹配字段，但SQL的where条件中没有使用数据表的索引字段，则此时possible_keys可能为NULL，而key字段的值可能是那个能cover住待查询字段的数据表索引字段，此时，MySQL会扫描索引树，虽然低效，但比起扫描全表还是要快。这种场景也正是本文前面解释join_type='index'时提到的case1。
此外，在select语句中借助"force index或"use index"可以强制MySQL使用possible_keys中列出的候选索引字段。
7) key_len
该字段的值表明上述key字段的length，当MySQL将某联合索引字段作为SQL执行时用到的索引时，key_len字段可以暗示MySQL真正在什么程度上（多长的最左前缀匹配字段）使用了该联合索引。若key字段的值为NULL，则key_len字段值也为NULL。
8) ref
该字段的值表明数据表中的哪列或哪个constants会被用于与key字段指定的索引做比较。
9) rows
该字段的值表明MySQL执行该步计划对应的query时必须扫描的行数。这个值对于SQL优化非常具有参考意义，通常情况下，该值越小查询效率越高。
10) Extra
该字段的值包含了MySQL执行query时的其它额外信息。该字段可能的取值情况较多，详细情况可参考官网文档的说明。
除explain外，MySQL还支持explain extended命令来分析optimizer的执行计划，后者在输出结果中多1个filtered字段，且可以用show warnings语句来分析输出的extra信息。
3. 如何根据explain的输出优化SQL如果理解了explain输出结果中每个字段背后的含义，则据此优化SQL性能会变得高效且有依据。
在工程实践中利用explain来trouble shoot低效SQL的思路，跟工程师的经验和能力有关，这里推荐几篇技术资料来抛砖引玉。
1) Oreilly官网中的一篇PDF分享：Explain Demystified
2) slideshare上的一篇分享文档：Mysql Explain Explained，该文档循序渐进地解释了MySQL explain的输出及据此优化SQL的典型思路，值得一读。
3) 美团官方技术博客的一篇文章：MySQL索引原理及慢查询优化，文中介绍了B+Tree的原理，并给出了几个利用explain来优化SQL的工程场景，也值得精读。
【参考资料】
1. MySQL Doc:
EXPLAIN Syntax
2. MySQL Doc: EXPLAIN Output Format
3. MySQL Doc:
SELECT Syntax about STRAIGHT_JOIN
4. MySQL Doc:
Optimizing Queries with EXPLAIN
5. MySQL Doc:
Nested-Loop Join Algorithms
6. PDF Presentation from Oreilly.com:
Explain Demystified
7. slideshare:
Mysql Explain Explained
8.
MySQL索引原理及慢查询优化