vlambda博客
学习文章列表

    就我个人经历来看，解释清一个复杂概念最好的方式是使用简单的例子。所以本篇文章中， 为了讲清楚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` longtext CHARACTER SET utf8 NOT NULL,

   ...

   `OwnerUserId` int(11) DEFAULT NULL,

   ...

   `Title` varchar(250) CHARACTER SET utf8 DEFAULT NULL,

   `ViewCount` int(11) NOT NULL

   PRIMARY KEY (`Id`)

 ) ENGINE=InnoDB DEFAULT 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` datetime NOT NULL,

   PRIMARY KEY (`Id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

使用的SQL如下：

SELECT

  v.UserId,

  COUNT(*) AS FavoriteCount

FROM

  Votes v

  JOIN Posts p ON p.id = v.PostId

WHERE

  p.OwnerUserId = 12345678

  AND v.VoteTypeId = 5  -- (Favorites vote)

GROUP BY

  v.UserId

ORDER BY

  FavoriteCount DESC

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结果中， 我们看到了什么？ 

1. MySQL选Vote表开始。Explain结果显示，会遍历145,045,878（一亿四千多万）条记录。这个几乎是整个Vote表中全部记录。这个情况相当糟

2. Extra列表明MySQL使用Where语句来减少排查的记录行数，不过MySQL估算降到10%，这里10%的记录行数是一千四百万条。据这个线索，这里一个可能的结论是“v.VoteTypeId = 5”条件的区分度不够高，这才造成还有千万条的数据跟另一个表做关联操作。 

3. 观察Where条件，可以看到还有另一个条件，即p.OwnerUserId = 12345678。这个条件的区分度应该很高，也应该会极大地降低需要排查的记录条数。Post表中有四千万条记录，而满足p.OwnerUserId = 12345678条件的结果只有57行，这也表明这个条件有极强的区分度。这种情况下，如果MySQL先从这个表开始，并使用条件p.OwnerUserId = 12345678过滤的话，查询性能应该会有极大改善。本文后面， 我们可以看到做了什么调整后， 可以影响选表的顺序。 

4. 观察两个表的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的结果

    有了哪些变化呢？

1. 第一个变化是MySQL决定从Post表开始了（干得漂亮！）。这次使用新索引后，过滤掉绝大多数的行，并估算只有57行记录跟Vote表关联。

2. 第一个变化是， 通过观察Key列，我们发现在两个表的查找和过滤过程中，都有使用到索引。

3. 观察key_len列，我们可以看到Vote表的组合索引有100%的使用，即8bytes，这个值是VoteTypeId和PostId两个字段长度的总和。 

4. 最后一个重要的变化是，MySQL估算用来比对数据的行数。这里，MySQL估算只有57 * 2 = 114行记录用来比对，这个相当好，要知道优化前需要比对上百万条记录。

    有了上面的理论分析后， 再执行下同样的SQL， 我们看到耗时有了一个巨大的减少：从原来可能需要数分钟（甚至因超时不能返回结果）到现在只有63毫秒！！！

结论：

最后总结下，理解Explain结果并优化的几个重要方面：

1. 执行Explain命令，查看下SQL的执行计划。

2. 留意下MySQL执行时使用的表顺序。选用的表是否有意义？如果不可思议的话，自问下：是因为咱们没有告诉MySQL什么建议，最终MySQL选了这么差的顺序？

3. 仔细排查Where条件，看看哪些是区分度最高的，并在新建的索引中，包含进这些条件。可以这里（https://www.eversql.com/choosing-the-best-indexes-for-mysql-query-optimization/）找到这方面更多的建议。 

4. 留意MySQL在执行查找和过渡阶段没有使用到索引的地方，这些地方往往是性能优化的突破口。

5. 留意MySQL估算需要排查的行数。

本文编译自：https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/

---------

往期推荐

~~~~~~~~~~~~~

一起推进电商业务信息化