【MySQL】explain analyze简介

【MySQL】explain analyze简介

码农世界 2024-06-14 后端 117 次浏览 0个评论

一、阅读及执行顺序

  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博客

转载请注明来自码农世界，本文标题：《【MySQL】explain analyze简介》

百度分享代码，如果开启HTTPS请参考李洋个人博客
37815篇文章 站点 微博
每一天，每一秒，你所做的决定都会改变你的人生！

发表评论

快捷回复：

评论列表 （暂无评论，117人围观）参与讨论

还没有评论，来说两句吧...

Top