第 56 期：EXPLAIN ANALYZE 怎么用？

我们接着聊执行计划（EXPLAIN）的相关话题。

我们已经把 MySQL 执行计划不同格式（EXPLAIN
、EXPLAIN FORMAT=JSON
、EXPLAIN FORMAT=TREE
）的输出结果做了详细介绍，来回顾一下 EXPLAIN
 语句能做哪些事情？

1. 根据 SQL 语句执行计划结果，来分析 SQL 是否用了合适索引、合适的表联结顺序、是否有临时表生成与销毁的过程等提示信息；
2. 查看 SQL 语句的每个执行步骤的预估成本对比、预估扫描行数对比、预估 CPU/IO 的具体成本对比等等；
3. 查看 SQL 语句经过 MySQL 内部规则转换后的真实语句。

虽然 EXPLAIN
 语句的输出结果是根据 MySQL 的表、索引等统计信息计算而来的，但是这些统计信息一般都是基于特定比例的行来进行计算的，并非全部真实的数据。某些场景下，需要完整执行一遍 SQL 语句后，对比其真实的扫描行数、执行成本等数据来进一步比对进而优化。

本文将通过三个例子，介绍 EXPLAIN ANALYZE 语句是如何解析查询结果的。

第一个例子

表 y1
 要做全表查询，对比 EXPLAIN
 和 EXPLAIN ANALYZE
 的效果差异。

EXPLAIN

仅显示总成本与扫描行数。

mysql:ytt>explain format=tree select * from y1\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on y1  (cost=100553 rows=1e+6)

1 row in set (0.00 sec)

EXPLAIN ANALYZE

除显示总成本和扫描行数外，还显示实际执行的数据。

(mysql:ytt)>explain analyze select * from y1\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on y1  (cost=100553 rows=1e+6) (actual time=0.0339..145 rows=1e+6 loops=1)

1 row in set (0.20 sec)

其中 (actual time=0.0339..145 rows=1e+6 loops=1)
 这条就代表实际执行数据。

• actual time=0.0339..145：代表实际执行时间（单位毫秒），扫描一行记录需要 0.0339 毫秒，扫描完所有记录行需要 145 毫秒。
• rows=1e+6：实际扫描的记录数，与预估的有很大偏差。
• loops=1：循环次数。

第二个例子

第一次执行

对表 t1
、t2
 做内连，求满足条件的总记录数，连接 KEY 为 ID。执行计划表示先嵌套循环连接后，再做 COUNT
 聚合计算。

mysql:ytt>explain analyze select count(*) from t1 a join t2 b using(id)\G
*************************** 1.row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=6856.58 rows=10169) (actual time=28.230..28.230rows=1 loops=1)
    -> Nested loop inner join  (cost=5839.68 rows=10169) (actual time=0.057..27.721 rows=10000 loops=1)
        -> Index scanon b using idx_log_date  (cost=1049.90 rows=10169) (actual time=0.044..2.579 rows=10000 loops=1)
        -> Single-row index lookup on a using PRIMARY (id=b.id)  (cost=0.37 rows=1) (actual time=0.002..0.002 rows=1 loops=10000)

1 row in set (0.03 sec)

结果说明

最终聚合部分：Aggregate: count(0)  (cost=6856.58 rows=10169) (actual time=28.230..28.230 rows=1 loops=1)
 ，预估扫描行数为 10169，实际扫描行数为 1 行，实际执行时间为 28 毫秒。

嵌套循环内联部分：Nested loop inner join  (cost=5839.68 rows=10169) (actual time=0.057..27.721 rows=10000 loops=1)
，预估扫描行数为 10169，实际扫描行数为 10000 行，实际总执行时间为 27 毫秒。

实际的表扫描

• 表 b
   的扫描基于索引 idx_log_date
  。实际执行时间为 2.5 毫秒，扫描行数 1 万，循环 1 次即可；
• 表 a
   的扫描基于主键，过滤条件依赖上层查询得到的连接 KEY，每次扫 1 行，执行时间 0.002 毫秒（可以忽略不计），循环 1 万次。

第二次执行

尝试增加过滤条件 a.r1=10
，执行结果多了一条过滤器的内容。

mysql:ytt>explain analyze select count(*) from t1 a join t2 b using(id) where a.r1 = 10\G
*************************** 1.row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=5952.35 rows=508) (actual time=20.465..20.465rows=1 loops=1)
    -> Nested loop inner join  (cost=5901.50 rows=508) (actual time=0.264..20.447 rows=118 loops=1)
        -> Index scanon b using idx_log_date  (cost=1049.13 rows=10169) (actual time=0.070..2.242 rows=10000 loops=1)
        -> Filter: (a.r1 = 10)  (cost=0.38 rows=0) (actual time=0.002..0.002 rows=0 loops=10000)
            -> Single-row index lookup on a using PRIMARY (id=b.id)  (cost=0.38 rows=1) (actual time=0.001..0.002 rows=1 loops=10000)

1 row in set (0.02 sec)

结果说明

Filter: (a.r1 = 10)  (cost=0.38 rows=0) (actual time=0.002..0.002 rows=0 loops=10000)
，实际执行时间为 0.002 毫秒，扫描记录数为 0，循环 1 万次。

也就是说，增加的过滤条件不用扫表，基于主键扫描后再二次过滤即可。

第三个例子

再来看一个派生表过滤的例子。

mysql:ytt>desc analyze select * from (select * from t1 where 1 order by r1 desc limit 1000) T where r2 < 1000\G
*************************** 1.row ***************************
EXPLAIN: -> Filter: (T.r2 < 1000)  (cost=102.91..115.00 rows=333) (actual time=5.524..5.695 rows=1000 loops=1)
    -> Table scanon T  (cost=0.01..15.00 rows=1000) (actual time=0.002..0.041 rows=1000 loops=1)
        -> Materialize  (cost=102.89..117.87 rows=1000) (actual time=5.521..5.623 rows=1000 loops=1)
            -> Limit: 1000 row(s)  (cost=2.87 rows=1000) (actual time=0.552..5.197 rows=1000 loops=1)
                -> Index scanon t1 using idx_r1 (reverse)  (cost=2.87 rows=1000) (actual time=0.551..5.132 rows=1000 loops=1)

1 row in set (0.00 sec)

按照从下到上的顺序解释：

1. 派生表先执行，基于索引 idx_r1
    反向扫描。由于有 LIMIT
    条件，只需实际扫描 1000 行，执行时间为 5.132 毫秒，循环 1 次即可。
2. 物化派生表，执行时间 5.623 毫秒，实际扫描行数 1000 行。
3. 全表扫派生表，执行时间为 0.041 毫秒，实际扫描行数 1000 行。

最终派生表过滤：Filter: (T.r2 < 1000)  (cost=102.91..115.00 rows=333) (actual time=5.524..5.695 rows=1000 loops=1)
。这里预估行数和实际扫描有很大差异，预估行数为 333 行，实际扫描行数为 1000 行，实际执行时间为 5.695 毫秒。

使用之前

EXPLAIN ANALYZE 由于会实际执行 SQL 语句，当需要分析一条非常耗资源的 SQL 时，需要很长时间才能出结果，不推荐使用。

比如前篇提到过的对 MySQL 非常不优好的多次嵌套语句：select count(*) from t1 where r1 in (select r1 from t1 where r1 in (select r1 from t1))
，就属于这种情况。

数据类型 | 大对象字段 | 列非空与自增 | 外键 | 字符集1 | 字符集2 | 字符集3 | 字符集4 | 表空间| 压缩表1 | 压缩表2 | 表统计 | 页合并 | B+树 | 索引结构 | 主键设计 | 哈希表1 | 哈希表2 | 前缀索引 | 函数索引 | 组合索引1 | 组合索引2 | 多值索引 | 索引基数 | 索引下推 | 全文索引1 | 全文索引2 | 全文索引3 | 全文索引4 | 索引数量 | 索引设计 | 表标准化设计 | 表冗余设计 | 垂直拆分 | 水平分表 | 原生表分区 | 时间分区 | 分区案例 | 哈希分区 | 多列分区 | 多表关联分区 | 无主键分区 | SQL 优化思路 | 执行计划1 | 执行计划2 | 执行计划3 | 执行计划4 | 执行计划5 | 执行计划6 | 执行计划 7 | 执行计划 8 | 执行计划 9 | EXPLAIN 10 更新中……