“'山穷水复疑无路,柳暗花明又一村'。在复杂的MySQL优化世界里， Explain是一个极佳的工具。”
就我个人经历来看，解释清一个复杂概念最好的方式是使用简单的例子。所以本篇文章中， 为了讲清楚MySQL Explain的含义，我基于StackOverflow公开的数据作为例子。
下面， 我们首先使用Explain分析下原始查询SQL，接下来基于分析结果优化查询， 随后再结合Explain命令看下具体优化了的地方和背后的原理。
查询语句和库表结构
例子中两个表结构的定义如下：
CREATE TABLE `posts` (
`Id`int(11)NOT NULL,
`AcceptedAnswerId`int(11)DEFAULT NULL,
`AnswerCount`int(11)DEFAULT NULL,
`Body` longtextCHARACTER SET utf8NOT NULL,
...
`OwnerUserId`int(11)DEFAULT NULL,
...
`Title`varchar(250)CHARACTER SET utf8DEFAULT NULL,
`ViewCount`int(11)NOT NULL
PRIMARY KEY (`Id`)
) ENGINE=InnoDBDEFAULT CHARSET=latin1
CREATE TABLE `votes` (
`Id`int(11)NOT NULL,
`PostId`int(11)NOT NULL,
`UserId`int(11)DEFAULT NULL,
`BountyAmount`int(11)DEFAULT NULL,
`VoteTypeId`int(11)NOT NULL,
`CreationDate` datetimeNOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDBDEFAULT CHARSET=latin1
使用的SQL如下：
SELECT
v.UserId,
COUNT(*)AS FavoriteCount
FROM
Votes v
JOIN Posts pON p.id = v.PostId
WHERE
p.OwnerUserId = 12345678
AND v.VoteTypeId = 5-- (Favorites vote)
GROUP BY
v.UserId
ORDER BY
FavoriteCountDESC
LIMIT
100;
这个SQL是为了找到那些把我提出StackOverflow问题加为收藏的用户信息。这里为了方便说明，我的UserId是12345678。
上面SQL执行时间很长，我没等执行完成，在运行了在一分多钟后就手动地终止了。
使用Explain查看执行计划
下图是上面SQL的执行计划。
先抑制住优化这个查询的冲动，我们仔细看下Explain的输出， 以全面了解执行计划。首先看到的是，这里有两行。我们分析的SQL执行过程中，会涉及到两个表，这两个表通过inner join关联起来。这里每一个表在执行计划输出中都有一行对应。
Explain结果解释如下：
id - 大多数情况下，这个id字段会是一串数字，每一个数字对应当前行所属的查询。当前这个查询没有子查询也没有unions， 两行的id值都是1， 表示只有一个查询。
select_type - Select的类型。当前例子中， 因为没有subqueries或unions， 这只是一个简单的查询。复杂的查询情况下，这个字段的值有：SUBQUERY(表示子查询)、UNION(第二个或后面的UNION语句)、DERIVED和其它一些值。可以从这里(https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_type)看到全面的解释。
table - 当前行对应的表名或别名。上面的截图中， 我们可以看到v和p，这两个分别是Vote和Post两个表的别名。
type - 表明对应表的访问(或联接)方式。最常见的Type值有， All，index, range, ref, eq_ref, const, system， 这里的顺序是从最坏到最好。Explain的结果所显，Vote表最先访问，使用的是All方式，这也就意味着MySQL会扫描全表，没有使用索引，这样会遍历1400万条记录。随后Post表使用eq_ref的访问方式。除了system和const方式外，eq_ref是最好的join方式。针对Post表中的记录，MySQL会检查本表中对应记录(如果存在的话)。
possible_keys - 这个字段指明了MySQL在查找表中数据行时可能会使用到的Key。由于MySQL选用执行方式的不同，这里列的索引可能跟实际不相关。一般来说，MySQL会使用索引来关联表。当然这么说，也不意味着会使用第一个表的联接列(use an index on the first table's join column)，这里MySQL会遍历所有行(除通过Where部分过滤掉的行之外)。
key - 这一字段表明MySQL最终决定使用的索引。这里注意下，并不意味着会使用到整个索引，也有可能使用部分内容，即通过最左优先的方式。
key_len - 这一列是Explain结果中最重要的一个。它表明MySQL使用Key的长度，单位是Bytes。上面所示的Explain结果中，MySQL使用了整个主键索引(4 bytes)。我们知道，主键索引中只有一列，即Id,它的类型是INT，长度是4 bytes。不过，我们也不能方便地知道MySQL究竟是使用了索引中的哪一部分的内容，咱们判断时，一般使用的方式是，加起来索引字段中对应列的类型的长度，再和这个key_len值比对。
rows - 表示MySQL判断后决定执行SQL中必须要遍历的行数。这只是一个估算值。通常情况下，这个值越高， 也就意味着查询语句还有很大的查询空间。
filtered - 表示Where条件中未过滤掉的行数。这些行将会跟Explain中下一行的表关联。和前面提到的一样，这也是一个估算值，MySQL在这个值上可能会出错。
extra - 包含了SQL执行中的另外一些信息，下面列举下，我们查询中，这一部分的内容：
using where - 表示使用Where条件来过滤出来Vote表中那些行要跟Post表关联。
using temporary - MySQL执行过程中， 可能会创建临时表。在大多数情况下，使用临时表会影响SQL执行效率。临时表也表示， 没有使用索引情况下，有ORDER BY或GROUP BY执行了。如果ORDER BY和GROUP BY使用了不一样的列(或不同的顺序)时，也会使用到临时表。
using filesort - 表明MySQL进行了另一个阶段，对查询结果排序。很多情况下，这也会带来性能上的损耗。
据Explain的结果优化SQL
从Explain结果中， 我们看到了什么？
MySQL选Vote表开始。Explain结果显示，会遍历145,045,878(一亿四千多万)条记录。这个几乎是整个Vote表中全部记录。这个情况相当糟
Extra列表明MySQL使用Where语句来减少排查的记录行数，不过MySQL估算降到10%，这里10%的记录行数是一千四百万条。据这个线索，这里一个可能的结论是“v.VoteTypeId = 5”条件的区分度不够高，这才造成还有千万条的数据跟另一个表做关联操作。
观察Where条件，可以看到还有另一个条件，即p.OwnerUserId = 12345678。这个条件的区分度应该很高，也应该会极大地降低需要排查的记录条数。Post表中有四千万条记录，而满足p.OwnerUserId = 12345678条件的结果只有57行，这也表明这个条件有极强的区分度。这种情况下，如果MySQL先从这个表开始，并使用条件p.OwnerUserId = 12345678过滤的话，查询性能应该会有极大改善。本文后面， 我们可以看到做了什么调整后， 可以影响选表的顺序。
观察两个表的possible_keys值，我们看到MySQL并没有使用到什么可选的索引。更准确地说，possible_keys中没有命中Where中指定的条件
基于上面的分析，我们需要添加下面这两个索引。每一个索引中分别以SQL中Where条件中字段开始，Vote表的索引中也包含了被关联的字段。
ALTER TABLE `Posts` ADD INDEX `posts_idx_owneruserid` (`OwnerUserId`);
ALTER TABLE `Votes` ADD INDEX `votes_idx_votetypeid_postid` (`VoteTypeId`,`PostId`);
加了索引后Explain结果
下图是添加了索引后，Explain的结果
有了哪些变化呢？
第一个变化是MySQL决定从Post表开始了(干得漂亮！)。这次使用新索引后，过滤掉绝大多数的行，并估算只有57行记录跟Vote表关联。
第一个变化是， 通过观察Key列，我们发现在两个表的查找和过滤过程中，都有使用到索引。
观察key_len列，我们可以看到Vote表的组合索引有100%的使用，即8bytes，这个值是VoteTypeId和PostId两个字段长度的总和。
最后一个重要的变化是，MySQL估算用来比对数据的行数。这里，MySQL估算只有57 * 2 = 114行记录用来比对，这个相当好，要知道优化前需要比对上百万条记录。
有了上面的理论分析后， 再执行下同样的SQL， 我们看到耗时有了一个巨大的减少：从原来可能需要数分钟(甚至因超时不能返回结果)到现在只有63毫秒！！！
结论：
最后总结下，理解Explain结果并优化的几个重要方面：
执行Explain命令，查看下SQL的执行计划。
留意下MySQL执行时使用的表顺序。选用的表是否有意义？如果不可思议的话，自问下：是因为咱们没有告诉MySQL什么建议，最终MySQL选了这么差的顺序？
仔细排查Where条件，看看哪些是区分度最高的，并在新建的索引中，包含进这些条件。可以这里(https://www.eversql.com/choosing-the-best-indexes-for-mysql-query-optimization/)找到这方面更多的建议。
留意MySQL在执行查找和过渡阶段没有使用到索引的地方，这些地方往往是性能优化的突破口。
留意MySQL估算需要排查的行数。
本文编译自：https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/
320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
