写点什么

遇到慢查询怎么办?一文解读 MySQL 8.0 查询分析工具

  • 2024-10-31
    广东
  • 本文字数:4391 字

    阅读完需:约 14 分钟

遇到慢查询怎么办?一文解读MySQL 8.0查询分析工具

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

1. EXPLAIN ANALYZE 可以解决什么问题

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


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

2. EXPLAIN ANALYZE 如何使用

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

select	100.00 * sum(case		when p_type like 'PROMO%'			then l_extendedprice * (1 - l_discount)		else 0	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenuefrom	lineitem,	partwhere	l_partkey = p_partkey	and l_shipdate >= '1996-01-01'	and l_shipdate < date_add( '1996-01-01', interval '1' month);
复制代码

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

-> Aggregate: sum((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT))), sum((case when (part.P_TYPE like 'PROMO%') then (lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) else 0 end))  -> Nested loop inner join (cost=83997.65 rows=66041)      -> Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month)))) (cost=60883.30 rows=66041)          -> Table scan on lineitem (cost=60883.30 rows=594488)      -> Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY) (cost=0.25 rows=1)
复制代码

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

-> Aggregate: sum((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT))), sum((case when (part.P_TYPE like 'PROMO%') then (lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) else 0 end)) (actual time=203.753..203.753 rows=1 loops=1)  -> Nested loop inner join (cost=83997.65 rows=66041) (actual time=0.056..200.386 rows=7884 loops=1)      -> Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month)))) (cost=60883.30 rows=66041) (actual time=0.042..183.957 rows=7884 loops=1)          -> Table scan on lineitem (cost=60883.30 rows=594488) (actual time=0.039..140.993 rows=600572 loops=1)      -> Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=7884)
复制代码

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

(actual time=m_start..m_end rows=m_rows loops=m_loops)
复制代码
  • m_start: 该算子返回第一行数据的实际时间(毫秒)

  • m_end: 该算子返回所有数据的实际时间(毫秒)

  • m_rows: 该算子实际的返回行数

  • m_loops: 该算子实际的迭代次数

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

Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month))))(cost=60883.30 rows=66041)(actual time=0.042..183.957 rows=7884 loops=1)
复制代码

优化器基于统计信息估算出的代价为 60883.30,预测返回行数为 66041;然而,实际执行后发现,真实的返回行数为 7884。其中,Filter 算子过滤掉了 592688 行 (600572 - 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 循环获取满足条件的行。

Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY)(cost=0.25 rows=1)(actual time=0.002..0.002 rows=1 loops=7884)
复制代码

优化器估算出的代价为 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 毫秒。

3. EXPLAIN ANALYZE 源码实现

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> 类图

3.1 执行树生成

数据库优化器在确定了最优的访问路径(AccessPath)之后,会通过函数 CreateIteratorFromAccessPath 生成执行树,该函数会依据算子类型,调用 NewIterator 函数生成对应的算子。


如果是普通 DQL(SELECT)语句,则生成对应的算子;如果是 EXPLAIN ANALYZE 语句,则生成一个 TimingIterator<RealIterator>Wapper 对象,其真实执行算子被保存在 TimingIterator::m_iterator 中。


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

图3 执行树生成流程

3.2 统计算子执行耗时

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

template <class RealIterator>class TimingIterator final : public RowIterator { public:  bool Init() override;  int Read() override;  std::string TimingString() const override; // 打印函数,输出算子执行时间信息
private: uint64_t m_num_rows = 0; // 该算子累计处理的记录数 uint64_t m_num_init_calls = 0; // 调用 Init 函数的次数 // 返回第一行的执行时间 steady_clock::time_point::duration m_time_spent_in_first_row{0}; // 返回所有行的执行时间 steady_clock::time_point::duration m_time_spent_in_other_rows{0}; bool m_first_row; // 是否为第一行数据 RealIterator m_iterator; // 真实的执行算子};
复制代码

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

template <class RealIterator>bool TimingIterator<RealIterator>::Init() {  ++m_num_init_calls;  // Init 函数的调用次数递增  steady_clock::time_point start = now();  bool err = m_iterator.Init(); // 调用真实执行算子的Init函数  steady_clock::time_point end = now();  m_time_spent_in_first_row += end - start; // 累计获取第一行数据的时间  m_first_row = true;  return err;}
template <class RealIterator>int TimingIterator<RealIterator>::Read() { steady_clock::time_point start = now(); int err = m_iterator.Read(); // 调用真实执行算子的Read 函数 steady_clock::time_point end = now(); if (m_first_row) { m_time_spent_in_first_row += end - start; // 更新获取第一行数据的时间 m_first_row = false; // 获取第一行数据结束 } else { m_time_spent_in_other_rows += end - start; // 更新获取所有行数据的时间 } if (err == 0) { ++m_num_rows; // 刷新该算子累计处理的记录数 } return err;}
复制代码

3.3 打印算子执行耗时

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

dispatch_command  mysql_parse    mysql_execute_command      Sql_cmd_dml::execute        Sql_cmd_dml::execute_inner          explain_query            ExplainIterator              PrintQueryPlan                ExplainAccessPath                  TimingIterator<RealIterator>::TimingString
复制代码

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

  • 该算子返回第一行数据的实际时间(毫秒)

  • 该算子返回所有数据的实际时间(毫秒)

  • 该算子实际的返回行数

  • 该算子实际的迭代次数

4. 总结

综上,我们分析了 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、欧拉等各项根技术的开发资源及工具,致力于为每位开发者提供一台云主机、一套开发工具及云上存储空间,让开发者基于华为根生态创新。点击链接,免费领取您的专属云主机~


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


用户头像

提供全面深入的云计算技术干货 2020-07-14 加入

生于云,长于云,让开发者成为决定性力量

评论

发布
暂无评论
遇到慢查询怎么办?一文解读MySQL 8.0查询分析工具_MySQL_华为云开发者联盟_InfoQ写作社区