mysql主键查询慢（mysql主外键查询）

摘要：本文主要分析了MySQL 8.0 EXPLAIN ANALYZE命令的使用，并结合源码介绍其实现思路，帮助数据库使用者和开发者更好的使用、理解该功能。

本文分享自华为云社区《【华为云MySQL技术专栏】MySQL 8.0 EXPLAIN ANALYZE 工具介绍》，作者：GaussDB 数据库。

MySQL 8.0.18 版本开始支持查询分析工具EXPLAIN ANALYZE，该工具不仅会实际执行SQL语句，还会展示SQL语句详细的执行信息，包含执行算子（Iterator）粒度的扫描行数、执行耗时、迭代次数等信息。

EXPLAIN ANALYZE工具是MySQL EXPLAIN FORMAT=TREE 功能的扩展，除了展示执行计划和代价估算之外，还提供了细粒度执行算子的耗时等信息。这使得DBA和开发人员能够基于代价估算和算子实际执行耗时信息，判断执行计划是否合理，并识别出后续的优化点。

以TPC-H基准测试中的Q14 查询为例，该SQL为两个表的连接及GROUP BY聚合操作，用于统计发货日志在1996年1月的促销商品收入占比。

通过EXPLAIN FORMAT=TREE语句，可以看出执行计划和代价估算信息：

通过EXPLAIN ANALYZE语句，可以看出每个算子详细的执行信息，如下:

相比EXPLAIN FORMAT=TREE，EXPLAIN ANALYZE会实际执行SQL语句，并统计每个算子的详细耗时信息，每个算子额外提供如下信息：

m_start: 该算子返回第一行数据的实际时间(毫秒)
m_end: 该算子返回所有数据的实际时间(毫秒)
m_rows: 该算子实际的返回行数
m_loops: 该算子实际的迭代次数

例如，Filter算子过滤lineitem表的L_SHIPDATE字段在 ['1996-01-01', '1996-02-01') 区间的数据。

优化器基于统计信息估算出的代价为 60883.30，预测返回行数为 66041；然而，实际执行后发现，真实的返回行数为7884。其中，Filter算子过滤掉了行 ( - 7884)。迭代次数为1（对应于Nested Loop Join中外表的扫描次数），返回给上层算子（Nested loop inner join）第一行数据的时间为 0.042 毫秒，返回给上层算子所有数据的时间为 183.957 毫秒。

例如，点查算子Single-row index lookup on part using PRIMARY，作为Nested loop inner join的内表，通过条件part.p_partkey = lineitem.l_partkey循环获取满足条件的行。

优化器估算出的代价为0.25，预测返回行数为 1；然而，实际执行后发现，真实的返回行数为1，但迭代次数为7884，与外表FILTER算子执行后的结果数据量相等，每次迭代只返回上层算子1行。因此，返回给上层算子（Nested loop inner join）第一行数据的时间和所有数据的时间相等，都是0.002毫秒，可以推算出内表点查的累计耗时为 15.768 毫秒(7884 * 0.002毫秒)。

基于以上分析，我们可以看出该SQL语句执行耗时约200 毫秒，lineitem表的全表扫描耗时约140 毫秒，Filter算子耗时约40 毫秒，part表循环点查约16 毫秒。

MySQL 8.0 使用火山执行引擎，火山模型是数据库系统中广泛使用的迭代模型。SQL语句经过查询解析生成抽象语法树（AST），然后经过查询优化，最终生成执行树，执行树的每个节点对应一个执行算子（Iterator）。每个算子提供了Init,Read,End接口，每个算子从子节点获取数据，执行该算子的相关工作，并返回结果给父节点。

以MySQL 8.0.22版本为例，它提供了37个执行算子来处理数据读取、多表连接、聚合操作、数据物化等多个操作场景，每个执行算子都继承自一个基类RowIterator。

例如， TableScanIterator（处理全表扫描）和 NestedLoopIterator（处理2表连接）的类图如图1所示：

图1 TableScanIterator 和 NestedLoopIterator 类图

EXPLAIN ANALYZE 工具的作用是展示SQL语句的执行计划以及详细记录各个算子的执行耗时。在MySQL 8.0中，这一功能的实现是通过新增一个接口模板类TimingIterator，将37个执行算子封装起来，以便统计各个执行算子的详细执行耗时信息。这样做的好处是实现简单，无需对所有算子单独适配，而且不影响非EXPLAIN ANALYZE语句的执行效率。

例如，全表扫描算子TableScanIterator 对应TimingIterator<TableScanIterator>，表连接算子 NestedLoopIterator 对应 TimingIterator<NestedLoopIterator>，其类图如图2所示：

图2 TimingIterator<TableScanIterator> 和 TimingIterator<NestedLoopIterator> 类图

数据库优化器在确定了最优的访问路径（AccessPath）之后，会通过函数 CreateIteratorFromAccessPath 生成执行树，该函数会依据算子类型，调用NewIterator函数生成对应的算子。如果是普通DQL（SELECT）语句，则生成对应的算子；如果是 EXPLAIN ANALYZE语句，则生成一个 TimingIterator< RealIterator> Wapper对象，其中，真实的执行算子被保存在 TimingIterator::m_iterator 中。

例如，EXPLAIN ANALYZE语句，TableScanIterator 会生成TimingIterator<TableScanIterator> 算子，NestedLoopIterator 会生成 TimingIterator<NestedLoopIterator> 算子，执行流程如图3所示。

图3 执行树生成流程

TimingIterator 模板类的主体实现如下表所示，执行的统计信息记录在几个私有成员变量中。

在SQL语句实际执行过程中，通过 Init 和 Read 函数的调度来记录详细执行信息，具体实现如下:

SQL语句执行结束后，会调用函数 TimingIterator<RealIterator>::TimingString 打印算子执行耗时信息，调用堆栈信息如下表所示：

TimingIterator<RealIterator>::TimingString 函数，会基于执行阶段的统计打印以下信息：

该算子返回第一行数据的实际时间(毫秒)
该算子返回所有数据的实际时间(毫秒)
该算子实际的返回行数
该算子实际的迭代次数

综上，我们分析了MySQL 8.0 EXPLAIN ANALYZE命令的使用，并结合源码介绍其实现思路，帮助数据库使用者和开发者更好的使用、理解该功能。

当遇到慢查询时，我们也可借助于EXPLAIN ANALYZE工具观察执行计划是否合理、分析SQL执行的主要耗时点，进而去优化SQL执行。

参考资料

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

https://dev.mysql.com/worklog/task/?id=4168

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

华为开发者空间，汇聚鸿蒙、昇腾、鲲鹏、GaussDB、欧拉等各项根技术的开发资源及工具，致力于为每位开发者提供一台云主机、一套开发工具及云上存储空间，让开发者基于华为根生态创新。点击链接，领取您的专属云主机

点击关注，第一时间了解华为云新鲜技术~

到此这篇mysql主键查询慢（mysql主外键查询）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！

上一篇： mysql主键自增原理（mysql自增主键实现原理）

下一篇： sql中循环更新（sql里面循环）

版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/sqlbc/79626.html

相关文章：