【MySQL】explain analyze简介

一、阅读及执行顺序

1. 从右到左：没有遇到并列的迭代器之前，都是从右边开始执行；
2. 从上到下：遇到并列的迭代器，都是上边的先开始执行。

• 总结：自上而下，深度优先

二、基础关键词解释

2.1 表结构及sql

CREATE TABLE `demo1` (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓 
 名',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别 {男:1,女:2}',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
  `content` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '个人简介',
  `create_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC ;

EXPLAIN ANALYZE
SELECT *
FROM demo1

2.2 输出

-> Table scan on demo1  (cost=0.35 rows=1) (actual time=0.007..0.007 rows=0 loops=1)

第一部分，操作类型：Table scan on demo1，当前步骤为对表demo1的扫描

第二部分，预估成本：(cost=0.35 rows=1)，当前步骤预估共消耗0.35毫秒，读取一行

第三部分，实际成本：(actual time=0.007..0.007 rows=0 loops=1)，当前步骤实际读取第一行平均消耗0.007毫秒，读取所有行平均消耗0.007毫秒，读取0行，循环1次(即执行一次)

总结：explain analyze命令输出每一行将由【操作类型+预估成本+实际成本】三部分组成

三、部分场景示例

3.1 级联

3.1.1 表结构及sql

tips:表都没有数据

CREATE TABLE `demo1` (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别 {男:1,女:2}',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
  `content` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '个人简介',
  `create_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

CREATE TABLE `demo2` (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别 {男:1,女:2}',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
  `content` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '个人简介',
  `create_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

EXPLAIN ANALYZE
SELECT *
FROM demo1 as d1
	join demo2 as d2
		on d2.id = d1.id

3.1.2 输出

-> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.085..0.085 rows=0 loops=1)
    -> Table scan on d1  (cost=0.35 rows=1) (actual time=0.084..0.084 rows=0 loops=1)
    -> Single-row index lookup on d2 using PRIMARY (id=d1.id)  (cost=0.35 rows=1) (never executed)

 执行顺序，遵从【自上而下，深度优先】的原则：

第一步【表扫描】：Table scan on d1  (cost=0.35 rows=1) (actual time=0.084..0.084 rows=0 loops=1)

翻译：扫描d1全表，预计消耗0.35ms并返回1行，实际读取第一行平均花费0.084ms，返回所有行平均花费0.084ms，共循环调用该迭代器1次，返回0行

思考：因为没有数据，但是作为驱动表，所以也需要执行一次该迭代器内容，同时读取第一行和所有行消耗的时间一样

第二步【索引扫描】：Single-row index lookup on d2 using PRIMARY (id=d1.id)  (cost=0.35 rows=1) (never executed)

翻译：通过主键索引(聚簇索引)扫描表，仅进行单行扫描，预计消耗0.35ms并返回1行，实际读取第一行平均花费0.084ms，返回所有行平均花费0.084ms，共循环调用该迭代器1次

思考：因为没有数据，不需要执行当前迭代

第三步【嵌套连接】：Nested loop inner join  (cost=0.70 rows=1) (actual time=0.085..0.085 rows=0 loops=1)

翻译：进行嵌套表连接，预计消耗0.70ms并返回1行，实际读取第一行平均花费0.085ms，返回所有行平均花费0.085ms，共循环调用该迭代器1次，返回0行

思考：此处的最终执行时间为1*.0.085(即loops*0.085，此处应取单次循环时平均所有航读取时间——后一个0.085)=0.085ms，该执行时间为调用子节点所有迭代器总和，再加上本身执行的时间，所以当前步骤实际执行时间应为(actual time=0.001..0.001)

 3.1.3 扩展

因数据会影响最终的优化器结果，所以需要进行数据测试

3.1.3.1 相同数据

demo1和demo2表中写入一模一样的两条数据，执行当前示例相同sql，执行计划如下

-> Inner hash join (d2.id = d1.id)  (cost=1.11 rows=2) (actual time=0.172..0.176 rows=2 loops=1)
    -> Table scan on d2  (cost=0.18 rows=2) (actual time=0.026..0.029 rows=2 loops=1)
    -> Hash
        -> Table scan on d1  (cost=0.45 rows=2) (actual time=0.092..0.105 rows=2 loops=1)

执行顺序，遵从【自上而下，深度优先】的原则： 

第一步【表扫描】：Table scan on d2  (cost=0.18 rows=2) (actual time=0.026..0.029 rows=2 loops=1)

第二步【表扫描】：Table scan on d1  (cost=0.45 rows=2) (actual time=0.092..0.105 rows=2 loops=1)

第三步【哈希连接】：Inner hash join (d2.id = d1.id)  (cost=1.11 rows=2) (actual time=0.172..0.176 rows=2 loops=1)

 思考：由于表中多了数据，优化器在指定执行计划时，与无数据时有了不同的选择，本次选择了哈希连接，当然数据量不多，实际业务过程中的计划的评估与生成会更复杂

3.1.3.2 级联表数据一张表多，一张表少

demo1表写入两条，demo2表中除了与demo1中相同的两条外，在插入id不同的两条数据，执行当前示例相同sql，执行计划如下

-> Nested loop inner join  (cost=1.15 rows=2) (actual time=0.060..0.066 rows=2 loops=1)
    -> Table scan on d1  (cost=0.45 rows=2) (actual time=0.039..0.042 rows=2 loops=1)
    -> Single-row index lookup on d2 using PRIMARY (id=d1.id)  (cost=0.30 rows=1) (actual time=0.010..0.010 rows=1 loops=2)

执行顺序，遵从【自上而下，深度优先】的原则： 

第一步【表扫描】：Table scan on d1  (cost=0.45 rows=2) (actual time=0.039..0.042 rows=2 loops=1)

第二步【聚簇索引遍历查找】：Single-row index lookup on d2 using PRIMARY (id=d1.id)  (cost=0.30 rows=1) (actual time=0.010..0.010 rows=1 loops=2)

第三步【嵌套循环连接】：Nested loop inner join  (cost=1.15 rows=2) (actual time=0.060..0.066 rows=2 loops=1)

思考：

1、此处有一个细节，执行计划将数据量少的d1表当做了驱动表，符合【选择数据量少的表做驱动表】原则，而且【数据量少】这个判断依据并不是数据表中的实际数据量少，是经过where、on条件过滤后提取的数据量少的表。

2、另外，第二步的loops=2，理解为当前迭代器需要执行两次，因为驱动表中有2条符合条件的数据，被驱动表就需要读取两次，相应的第二步的时间应为(actual time=0.010..0.010)×2=(actual time=0.020..0.020)

.此处不再讨论大数据量下的计划理解，有此意者可私聊博主，互相交流与探讨

参考文章：

[译]MySQL EXPLAIN ANALYZE - 墨天轮 (modb.pro)

MySQL8.0：explain analyze分析SQL执行过程 - 简书 (jianshu.com)

MSQL系列(十) Mysql实战-Join驱动表和被驱动表如何区分_mysql驱动表和被驱动表-CSDN博客