Greenplum之explain生成执行计划和阅读执行计划

0    82    2

Tags:

👉 本文共约35196个字,系统预计阅读时间或需133分钟。

目录

查询分析

GP 是基于 pgsql 开发的,其执行计划大多是跟 pgsql 一样的,但由于 gp 是分布式并行数据库,在 sql 执行上有很多 MPP 的痕迹,因此在理解 gp 的执行计划时,一定要将其分布式框架熟读在心,从而能够通过调整执行计划给 sql 带来很大的性能提升。

检查性能不好的查询的查询计划,来确定可能的性能调优机会。

Greenplum数据库为每个查询设计一个 查询计划 。选择正确的查询计划来匹配查询和数据结构对好的性能是必要的。 一个查询计划定义Greenplum数据库将如何在并行执行环境中运行查询。

查询优化器使用数据库维护的数据统计信息来选择具有最低可能代价的查询计划。代价以磁盘I/O来度量,磁盘I/O用取得的磁盘页面为单位。目标是最小化计划的总执行代价。

可以用EXPLAIN命令查看一个给定查询的计划。EXPLAIN展示查询规划器对该查询计划估计的代价。例如:

EXPLAIN ANALYZE不仅运行该语句,还会显示它的计划。这有助于判断优化器的估计与现实有多接近。例如:

Note: 在Greenplum数据库中,默认的GPORCA优化器与传统查询优化器共存。GPORCA生成的EXPLAIN输出与传统查询优化器生成的输出不同。

默认情况下,Greenplum数据库会在可能时使用GPORCA来为查询生成执行计划。

当EXPLAIN ANALYZE命令使用GPORCA时,EXPLAIN计划只显示被排除的分区数。被扫描的分区不会被显示。要在segment实例 日志中显示被扫描分区的名称,可以把服务器配置参数gp_log_dynamic_partition_pruning设置为on。这个SET命令的例子启用了该参数。

有关GPORCA的信息,请见查询数据

执行计划入门

什么是执行计划

执行计划就是数据库运行 sql 的步骤,相当算法,读懂 gp 的执行计划,对理解 sql 的正确性即性能有很大的帮助。执行计划是数据库使用者了解数据库内部结构的一个重要途径。

查看执行计划

跟 pgsql 一样,gp 通过 explain 命令来查看执行计划。具体语法如下:

各个参数的含义如下:

  • ANALYZE:执行命令并显示实际运行时间。
  • VERBOSE:显示规划树完整的内部表现形式,而不仅是一个摘要。通常,这个选项只是在特殊的调试过程中有用,VERBOSE 输出是否打印工整的,具体取决于配置参数 explain_pretty_print 的值。
  • statement:查询执行计划的 SQL 语句,可以是任何 select、insert、update、delete、values、execute、declare 语句。

分布式执行计划概述

架构

Greenplum之explain生成执行计划和阅读执行计划

图5-1

图5-1 很好地说明了 ShareNothing 的特点:

  • 底层的数据完全部共享。
  • 每个 Segment 只有一部分数据。
  • 每一个节点都通过网络连接在一起。

重分布与广播

关联数据在不同节点上,对于普通关系型数据库来说,是无法进行连接的。关联的数据需要通过网络流入到一个节点中进行计算,这样就需要发生数据迁移。数据迁移有广播和重分布两种。

Greenplum之explain生成执行计划和阅读执行计划

图5-2

图5-2 展示了 gp 中重分布数据的实现。

在图5-2中,两个 Segment 分别进行计算,但由于其中一张表的关联键与分布键不一致,需要关联的数据不在同一个节点上,所以在 SLICE1 上需要将其中一个表进行重分布,可理解为在每个节点之间互相交换数据。

关于广播与重分布,gp 有一个很重要的概念:Slice(切片)。每一个广播或 重分布会产生一个切片,每一个切片在每个数据节点上都会对应发起一个进行来处理该 Slice 负责的数据,上一层负责该 Slice 的进程会读取下级 Slice 广播或重分布的数据,然后进行相应的计算。

由于在每个 Segment 上每一个 Slice 都会发起一个进程来处理,所以在 sql 中要严格控制切片的个数,如果重分布或者广播太多,应适当将 sql 拆分,避免由于进程太多给数据库或者是机器带来太多的负担。进程太多也比较容易导致 sql 失败

Greenplum之explain生成执行计划和阅读执行计划

图5-3

Slice 之间如何交互可以从图5-3中看出。

下面通过一个实际的数据形象地介绍数据在 Segment 中的切分。比方说,对一个成绩表来说,分布键是学号(sno),我们现在要按照成绩(score)来执行 group by,那么就需要将数据按照 score 字段进行重分布,重分布前会对每个 Segment 的数据进行局部汇总,重分布后,同一个 score 的数据都在同一个 Segment 上,再进行一次汇总即可,数据的具体情况如图5-4所示。

Greenplum之explain生成执行计划和阅读执行计划

图5-4

Greenplum Master 的工作

Master 在 sql 的执行过程中承担着很多重要的工作,主要如下:

  • 执行计划解析即分发。
  • 将子节点的数据汇集在一起。
  • 将所有 Segment 的有序数据进行归并操作(归并排序)。
  • 聚合函数在 Master 上进行最后的计算。
  • 需要有唯一的序列的功能(如开窗函数不带 partition by 字句)。

举个简单的例子,在计算学生的平均分数时,在每个节点上先计算好 sum 和 count 值,然后再由 Master 汇总,再次进行少量计算,算出平均值,如图5-5所示。

Greenplum之explain生成执行计划和阅读执行计划

图5-5

Greenplum 执行计划中的术语

数据扫描方式

gp 扫描数据的方式有很多种,每一种扫描方式都有其特点:

(1)Seq Scan:顺序扫描

顺序扫描在数据库中是最常见,也是最简单的一种方式,就是讲一个数据文件从头到尾读取一次,这种方式非常符合磁盘的读写特性,顺序读写,吞吐很高。对于分析性的语句,顺序扫描基本上是对全表的所有数据进行分析计算,因此这一个方式非常有效。在数据仓库中,绝大部分都是这种扫描方式,在 gp 中结合压缩表一起使用,可以减少磁盘 IO 的损耗。

(2)Index Scan:索引扫描

索引扫描是通过索引来定位数据的,一般对数据进行特定的筛选,筛选后的数据量比较小(对于整个表而言)。使用索引进行筛选,必须事先在筛选的字段上建立索引,查询时先通过索引文件定位到实际数据在数据文件中的位置,再返回数据。对于磁盘而言,索引扫描都是随机 IO,对于查询小数据量而言,速度很快。

(3)Bitmap Heap Scan:位图堆表扫描

当索引定位到的数据在整表中占比较大的时候,通过索引定位到的数据会使用位图的方式对索引字段进行位图堆表扫描,以确定结果数据的准确。对于数据仓库应用而言,很少用这种扫描方式。

(4)Tid Scan:通过隐藏字段 ctid 扫描

ctid 是pgsql 中标记数据位置的字段,通过这个字段来查找数据,速度非常快,类似于 oracle 的 rowid。gp 是 一个分布式数据库,每一个子节点都是一个pgsql 数据库,每一个子节点都单独维护自己的一套 ctid 字段。

如果在 gp 中通过 ctid 来找数据,会有如下的提示:

就是说,如果想确定到具体一行数据,还必须通过制定另外一个隐藏字段(gp_segment_id)来确定取哪一个数据库的 ctid 值。

(5)Subquery Scan '*SELECT*':子查询扫描

只要 sql 中有子查询,需要对子查询的结果做顺序扫描,就会进行子查询扫描。

(6)Function Scan:函数扫描

数据库中有一些函数的返回值是一个结果集,数据库从这个结果集中取出数据的时候,就会用到这个 Function Scan,顺序获取函数返回的结果集(这是函数扫描方式,不属于表扫描方式),如:

分布式执行

(1) Gather Motion(N:1)

聚合操作,在 Master 上讲子节点所有的数据聚合起来。一般的聚合规则是:哪一个子节点的数据线返回到 Master 上就将该节点的数据先放在 Master 上。

(2) Broadcast Motion(N:N)

广播,将每个 Segment 上某一个表的数据全部发送给所有 Segment。这样每一个 Segment 都相当于有一份全量数据,广播基本只会出现在两边关联的时候,相关内容再选择广播或者重分布。

(3) Redistribute Motion(N:N)

当需要做跨库关联或者聚合的时候,当数据不能满足广播的条件,或者广播的消耗过大时,gp 就会选择重分布数据,即数据按照新的分布键(关联键)重新打散到每个 Segment 上,重分布一般在以下三种情况下回发生:

  • 关联:将每个 Segment 的数据根据关联键重新计算 hash 值,并根据 gp 的路由算法路由到目标子节点中,使关联时属于同一个关联键的数据都在同一个 Segment 上。
  • group by :当表需要 group by ,但是 group by 的字段不是分布键时,为了使 group by 的字段在同一个库中,gp 会分两个 group by 操作来执行,首先,在单库上执行一个 group by 操作,从而减少需要重分布的数据量;然后将结果数据按照 group by 字段重分布,之后在做啊聚合获得最终结果。
  • 开窗函数:跟group by 类似,开窗函数(Window Function)的实现也需要将数据重分布到每个节点上进行计算,不过其实现比 group by 更复杂一些。

(4) 切片(Slice)

gp 在实现分布式执行计划的时候,需要将 sql 拆分成多个切片(Slice),每一个 Slice 其实是单库执行的一部分 sql,上面描述的每一个 motion 都会导致 gp 多一个 Slice 操作,而每一个 Slice 操作子节点都会发起一个进程来处理数据。

所以应该尽量控制 Slice 的个数,将太复杂的 sql 拆分,减少进程数,在执行计划中,最常见的 Slice 关键字的地方就是广播跟重分布,如下:

两种聚合方式

HashAggregate 和 GroupAggregate 这两种聚合方式在 5.7 介绍执行原理时会给出详细的讲解,这里主要从占用内存方面简单介绍:

(1) HashAggregate

对于 Hash 聚合来说,数据库会根据 group by 字段后面的值计算 hash 值,并根据前面使用是的聚合函数在内存中维护对应的列表,然后数据库会通过这个列表来实现聚合操作,效率相对较高。

(2) GroupAggregate

对于普通聚合函数,使用 group 聚合,其原理是先将表中的数据按照 group by 的字段排序,这样同一个 group by 的值就在一起,只需要对排好序的数据进行一次全扫描就可以得到聚合的结果。

关联方式

gp 中的关联的实现比较多,有 Hash Join、NestLoop、Merge Join,实现方式跟普通的 pgsql 数据库方式一样。由于 gp 是分布式的,所以关联可能会涉及表的广播或重分布。下面通过实际的执行计划来分析这 3 中关联在 gp 上的简单实现,首先建立两张表以方便我们查看后面的执行计划:

1. Hash Join

Hash Join(Hash 关联) 是一种很搞笑的关联方式,简单地说,其实现原理就是讲一张关联表按照关联键在内存中建立哈希表,在关联的时候通过哈希的方式来处理。

下面是一个 Hash Join 的例子:

2. Hash Left Join

通过 Hash Join 的方式来实现左连接,在执行计划中的体现就是 Hash Left Join:

3. NestedLoop

NestedLoop 关联是最简单,也是最低效的关联方式,但是在有些情况下,不得不使用 NestedLoop,例如笛卡尔积:

由于是笛卡尔积,因此 sql 一定是采取 NestedLoop 关联。在 gp 中,如果采取 NestedLoop,关联的两张表中有一张表必须广播,否则无法关联,一般是数据量比较小的表会广播。

4. Merge Join 和 Merge Left Join

Merge Join 也是量表关联中比较常见的关联方式,这种关联方式需要将两张表按照关联键进行排序,然后按照归并排序的方式将数据进行关联,效率比 Hash Join 差。

下面的例子先通过设置两个参数来强制执行计划,采取的是 Merge Join 方式:

伴随 Merge Join 的肯定是两张表关联键的排序。

5. Merge Full Join

如果关联使用的是 full outer join,则执行计划使用的是 Merge Full Join。在 gp 中其他的关联方式都无法进行全关联。

在 oracle 10g 中,a full outer join b 的实现方式是对 a 和 b 做一个左外关联,然后对 b 和 a 做一个反连接(在关联时,匹配的剔除,不匹配的保留),再对两个结果直接进行 union all 操作。但是在 gp 中没有执行这个优化,所有只能采取 Merge Join。Nest咯哦片只能用于内连接,对外连接无能为力。

6. Hash EXISTS Join

关联子查询 exist 之类的 sql 会被改写成 inner join,如果 sql 被改写了,则会出现 Hash EXISTS Join。

SQL 消耗术语

在每个 sql 的执行计划中,每一步都会有(cost=0.01..0.05 rows=3 width=150)折3项表示 sql 的消耗,这三个字段的含义:

(1) Cost

以数据库自定义的消耗单位,通过统计信息来估计 sql 的消耗。具体消耗的单位可以参考 pgsql 的官方文档: http://www.pgsqldb.org/pgsqldoc-8.1c/runtime-config-query.html

(2) Rows

根据统计信息估计 sql 返回结果集的行数。

(3) Width

返回结果集每一行的长度,这个长度值是根据 pg_statistic 表中的统计信息来计算的。

其他术语

(1) Filter 过滤

where 条件中的筛选条件,在执行计划中就是 Filter 关键字。

(2)Index Cond

如果在查询的表中 where 筛选的字段中有阿银,那么执行计划会通过索引定位,提高查询的效率。Index Cond 就是定位索引的条件。

(3)Recheck Cond

在使用位图扫描索引的时候, 由于 pgsql 里面使用的是 MVCC 协议,为了保证结果的正确性,要重新检查一下过滤条件。

(4) Hash Cond

执行 Hash Join 的时候的关联条件:

(5)Merge

在执行排序操作时数据会在子节点上各自排好序然后在 Master 上做一个归并操作:

(6)Hash Key

在数据重分布时候指定的重算 hash 值的分布键:

(7)Materialize

将数据保存在内存中,避免多次扫描磁盘带来的开销。这个要重点注意,由于将数据保存在内存中,会占用很大的内存,而执行计划时按照统计信息来计算的,如果统计信息丢失或者错误,有可能会将一张很大的表保存在内存中,直接导致内存不足,进而导致 sql 执行失败:

(8)Join Filter

对数据关联后再进行筛选,如:

(9)Sort,Sort Key

如果执行计划中出现了 Sort 关键字,则说明有排序的操作,排序的字段为: Sort Key。

(10)Window,Partition By,Order by

这个出使用开窗函数(Window Function)时,执行计划显示了使用分析函数的标识:

(11)Limit

当在 sql 只取前几行时,就使用 Limit 语句:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Greenplum之explain生成执行计划和阅读执行计划后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部