Greenplum企业应用实战（笔记）：第五章执行计划详解

2018-06-11 本文已影响28人凉秋_不见春暖

第五章执行计划详解

[TOC]

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

5.1 执行计划入门

5.1.1 什么是执行计划

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

5.1.2 查看执行计划

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

各个参数的含义如下：

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

5.2 分布式执行计划概述

5.2.1 架构

图5-1

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

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

5.2.2 重分布于广播

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

图5-2

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

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

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

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

图5-3

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

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

图5-4

5.2.3 Greenplum Master 的工作

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

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

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

图5-5

5.3 Greenplum 执行计划中的术语

5.3.1 数据扫描方式

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 来找数据，会有如下的提示：

Select * from test1 where ctid='(1,1)';
NOTICE: SELECT uses system-definedd column "test1.ctid" without the necessary companion column "test1.gp_segment_id"
HINT: TO uniquely identify a row within a distributer table, use the "gp_segment_id" column together with the "ctid" column.

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

select * from test1 where ctid='(1,1)' and gp_segment_id=1;

（5）、Subquery Scan '*SELECT*'：子查询扫描

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

（6）、Function Scan：函数扫描

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

explain select * from generate_series(1,10);

5.3.2 分布式执行

（1） Gather Motion(N:1)

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

（2） Broadcast Motion(N:N)

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

（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 关键字的地方就是广播跟重分布，如下：

Broadcast Motion 6:6 (slice1)
Gather Motion 6:1 (slice1)

5.3.3 两种聚合方式

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

（1） HashAggregate

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

（2） GroupAggregate

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

5.3.4 关联

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

testDB=# create table test1 (id int,values varchar(256)) distributed by (id);
CREATE TABLE
testDB=# create table test2 (id int,values varchar(256)) distributed by (id);
CREATE TABLE

1. Hash Join

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

下面是一个 Hash Join 的例子：

testDB=# explain select * from test1 a,test2 b where a.id=b.id;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.01..0.05 row=3 width=300)
    -> Hash Join (cost=0.01..0.05 rows=3 width=300)
        Hash Cond: a.id=b.id
        -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        -> Hash (cost=0.00..0.00 rows=1 width=150)
            -> Seq Scan on test2 b (cost=0.00..0.00 rows=1 width=150)
(6 rows)

2. Hash Left Join

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

testDB=# explain select * from test1 a left join testb on a.id=b.id;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.01..0.05 row=3 width=300)
    -> Hash Left Join (cost=0.01..0.05 rows=3 width=300)
        Hash Cond: a.id=b.id
        -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        -> Hash (cost=0.00..0.00 rows=1 width=150)
            -> Seq Scan on test2 b (cost=0.00..0.00 rows=1 width=150)
(6 rows)

3. NestedLoop

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

testDB=# explain select * from test1, test2;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.08..0.20 row=3 width=300)
    -> Nested Loop (cost=0..08..0.20 rows=3 width=300)
        -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        -> Materialize (cost=0.08..0.14 rows=6 width=150)
            -> Broadcast Motion 6:6 (slicel) (cost=0.00..0.07 rows=6 width=150)
                -> Seq Scan on test2 b (cost=0.00..0.00 rows=1 width=150)
Settings: enable_seqscan=off
(7 rows)

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

4. Merge Join 和 Merge Left Join

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

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

testDB=# set enable_hashjoin =off;
SET
testDB=# set enable_mergejion =on;
SET
testDB=# explain select * from test1 a join test2 b on a.id=b.id;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.02..0.05 rows=3 width=300)
    -> Merge Join (cosr=0.02..0.05 rows=3 with=300)
        Merge Cond: a.id=b.id
        -> Sort (cost=0.01..0.02 rows=1 width=150)
            Sort Key: a.id
            -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        ->Sort (cost=0.01..0.02 rows=1 width=150)
            Sort Key: b.id
            -> Seq Sacn on test2 b (cost=0.00..0.00 rows=1 width=150)
Settings: enable_hashjoin=off; enable_mergejoin=on; enable_seqscan=off
(10 rows)

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

5. Merge Full Join

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

testDB=# explain select * from test1 a full outer join test2 b on a.id=b.id;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.02..0.05 rows=3 width=300)
    -> Merge Full Join (cost=0.02..0.05 rows=3 width=300)
        Merge Cond: a.id=b.id
        -> Sort (cost=0.01..0.02 rows=1 width=150)
            Sort Key: a.id
            -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        -> Sort (cost=0.01..0.02 rows=1 width=150)
            Sort Key: b.id
            -> Seq Scan on test2 b (cost=0.00..0.00 rows=1 width=150)
Settings: enable_seqscan=off
(1o rows)

在 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。

testDB=# explain select * from test1 a where exists(select 1 from test2 b where a.id=b.id);
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.02..0.05 rows=3 width=300)
    -> Hash EXISTS Join (cost=0.01..0.05 rows=3 width=150)
        Hash Cond: a.id = b.id
        -> Seq Scan on test1 a (cost=0.00..0.00 rows=1 width=150)
        -> Hash (cost=0.00..0.00 rows=1 width=4)
            -> Seq Scan on test2 b (cost=0.00..0.00 rows=1 width=4)
(6 rows)

5.3.5 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 表中的统计信息来计算的。

5.3.6 其他术语

（1） Filter 过滤

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

Filter: relfilenode = 1249::oid

（2）Index Cond

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

Index Scan using pg_class_oid_index on pg_class(cost=0.00..200.27 rows=1 width=205)
        Index Cond: oid = 1259::oid

（3）Recheck Cond

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

Bitmap Heap Scan on test1 (cost=100.37..103.48 rows=7 width=12)
    Recheck Cond: a >= 1 AND a <= 50
    -> Bitmap Index Scan on dix_test1 (cost=0.00..100.37 rows=7 width=0)
        Index Cond: a>=1 AND a<=50

（4） Hash Cond

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

-> Hash Join (cost=40.87..119.60 rows=1683 width=24)
    Hash Cond: y.b = x.a

（5）Merge

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

Gather Motion 6:1 (slicel) (cost=0.01..0.02 rows=1 width=150)
    Merge Key: id
    -> Sort (cost=0.01..0.02 rows=1 width=150)

（6）Hash Key

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

-> Redistribute Motion 6:6 (slicel) (cost=0.00..53.49 rows=1683 width=12)
    Hash Key: y.b
        -> Seq Scan on test2 y (cost=0.00..19.83 rows=1683 width=12)

（7）Materialize

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

-> Materialize (cost=147.74..248.72 rows=10098 width=12)
    -> Broadcast Motion 6:6 (slicel) (cost=0.00..137.64 rows=1098 width=12)
        -> Seq Scan on test2 y (cost=0.00..19.83 rows=1683 width=12)

（8）Join Filter

对数据关联后再进行筛选，如：

-> Nested Loop (cost=147.74..467528.25 rows=314721 width=24)
    Join Filter: x.a < y.a AND x.a > (y.a + 1)

（9）Sort，Sort Key

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

-> Sort (cost=0.01..0.02 rows=1 width=150)
    Sort Key: id
    -> Seq Scan on test1 (cost=0.00..0.00 rows=1 width=150)

（10）Window，Partition By，Order by

这个出使用开窗函数（Window Function）时，执行计划显示了使用分析函数的标识：

testDB=# explain select * from ( select row_number() over (partition by id order by values) rn from test1) t where rn=1;
                    QUERY PLAN
-------------------------------------------------------------------
Gather Motion 6:1 (slicel) (cost=0.01..0.03 rows=1 width=8)
    -> Subquery Scan t (cost=0.01..0.03 rows=1 width=8)
        FIlter: rn = 1
        -> Window (cost=0.01..0.02 rows=1 width=150)
            Partition By: id
            Order By: "values"
            -> Sort (cost=0.01..0.02 rows=1 width=150)
                Sort Key: id, "values"
                -> Seq Scan on test1 (cosr=0.00..0.00 rows=1 width=150)
Settings: enable_seqscan=off
(10 rows)

（11）Limit

当在 sql 只取前几行时，就使用 Limit 语句：

Limit (cost=0.00..0.85 rows=1 width=205)

（12）Append

将结果直接汇总起来：

-> Append (cost=0.00..2.02 rows=1 width=13998)
    ->Append-only Scan on offer_1_prt_p20100801 offer
    ->Append-only Scan on offer_1_prt_p20100802 offer
    ->Append-only Scan on offer_1_prt_p20100803 offer

5.4 数据库统计信息收集

gp 与 oracle 等数据库一样，都是根据 CBO 优化器来选择一个好的执行计划的，尤其是在识别广播或者重分布的时候，统计信息十分重要，其准确与否直接决定了执行计划的好坏。

5.4.1 Analyze 分析

统计信息的命令如下：

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

如果没有参数，ANALYZE 检查当前数据库中所有表。如果有参数，则只检查参数指定的那个表。还可以给出一列字段名字，则只收集给出的字段的统计信息。

ANALYZE收集表内容的统计信息，表级别的信息（表的数据量及表大小）保存在 pg_class 中的 reltuples 和 relpages 字段中，然后把字段级别的结果保存在系统表 pg_statistic 中，字段级信息通常包括每个字段最常用数值的列表以及显示每个字段中数据近似分布的包线图。

在GP中会对表自动进行统计信息收集。控制自动收集的参数是 gp_autostats_mode，这个参数有三个值：none、on_change、on_no_stats。

这个参数在Master上修改，然后通过 gpstop -u 重新加载 postgresql.conf 这个配置文件即可。默认on_no_stats。

5.4.2 固定执行计划

greenplum是通过统计信息来生成执行计划的。

一般对执行计划影响最大的是 pg_class 的 relpages 和 reltuples 这两个字段，reltuples是表的数据量，relpages则表示表大小除以32k，即：

select pg_relation_size(tablename)/32/1024;

5.5 控制执行计划的参数介绍

在gp 中，控制执行计划的参数都是在会话级别，对于同一会话的所有sql生效。

这些配置参数提供了查询优化器选择查询规划的原始方法。如果优化器为特定的查询选择的默认规划并不是最优，那么我们就可以通过使用这些配置参数强制优化器选择一个更好的规划来临时解决这个问题。这些参数一般是在某个会话级别对某个sql进行设置的，不建议在全局中修改，会影响其他正常sql的执行计划。

5.6 规划期开销的计算方法

在选择合理的执行计划的时候，gp 会遍历所有的执行计划，计算其开销，即cost值，并选择最小的执行路径执行sql。

一般，gp/pgsql 以抓取顺序页的开销作为基准单位，也就是说将 seq_page_cost 设为1.0，同时其他开销参数对照它来设置。

表5-3 是gp中衡量数据库消耗的各个变量及默认参数。

减小 random_page_cost 值（相对于 seq_page_cost）将导致更倾向于使用索引扫描，而增加这个值导致更倾向于使用顺序扫描。可以通过同时增加或减少这两个值来调整磁盘 I/O 相对于 CPU的开销。

5.7 各种执行计划原理分析

5.7.1 详解关联的广播与重分布

分布式的关联有两种：

单库关联：关联键与分布键一致，只需要在单个库关联后得到结果即可
跨库关联：关联键与分布键不一致，数据需要重新分布，转换成单库关联，从而实现表的关联。

1、内连接

情况1：

select * from A,B where A.id=B.id;

分布键与关联键相同，属于单库关联，不会造成广播或者重分布。

情况2：

select * from  A,B where A.id=B.id2;

表A的关联键是分布键，表B的关联键不是分布键，那么可以通过两种方法来实现表的关联：

将表B按照id2字段将数据重分布到每一个节点上，然后再与表A进行关联。重分布的数据量是N
将表A广播，每一个节点都放一份全量数据，然后再与表B关联得到结果。广播的数据量是 M * 节点数

所以当N>M * 节点数的时候，选择表A广播，否则选择表B重分布。

情况3：

select * from A,B where A.id2=B.id2;

对于这种情况，两个表的关联键及分布键都不一样，那么还有两种做法：

将表A与表B都按照id2字段，将数据重分布到每个节点，重分布的代价是M+N
将其中一个表广播后再关联，当然选取小表广播，代价小，广播的代价是 min(M,N) * 节点数

所以，当 N + M > min(M,N) * 节点数的时候，选择小表广播，否则选择两个表都重分布。

2、左连接：

情况1:

select * from A left join B on A.id=B.id;

单库关联，不涉及数据跨库关联

情况2：

select * from A left join B on A.id=B.id2;

由于左表的分布键是关联键，鉴于左连接的性质，无论表B数据量多大，都必须将表B按照字段id2重分布数据。

情况3：

select * from A left join B on A.id2=B.id;

左表的关联键不是分布键，由于左连接A表肯定是不能被广播的，所以有两种方式：

将表A按照id2重分布数据，转换成情况A，代价为M
将表B广播，代价为 N * 节点数

情况4：

select * from A left join B on A.id2=B.id2;

将表A与表B都哦据考id2字段将数据重分布一遍，转换成情况1，代价是M+N
表A不能被广播，只能将表B广播，代价是 N * 节点数

对于有多种情况，gp总是选择代价小的方式来执行sql

3、全连接：

情况1：

select * from A full outer join B on A.id=B.id;

对于关联键都是分布键的情况，在gp中全连接只能采用Merger Join来实现

情况2：

select * from A full outer join B on A.id = B.id2;

将不是关联键不是分布键的才重分布数据，转换成情况1来解决。无论A、B大小分别为多少，为了实现全连接，不能将表广播，只能是重分布。

情况3：

select * from A full outer join B on A.id2=B.id2;

将两张表都重分布，转换成情况1进行处理。

5.7.2 HashAggregate 与 GroupAggregate

在pgsql/gp 数据库中，聚合函数有两种实现方式：HashAggregate 与 GroupAggregate。

案例：

select count(1) from pg_class group by oid;

1、两种实现算法的比较：

（1）HashAggregate

对于hash聚合来说，数据库会根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维对应的列表。如果select后面有两种聚合函数，那么在内存中就会维护两个对应的数据。同样的，有n个聚合函数就会维护n个同样的数组。对于hash聚合来说，数组的长度肯定是大于group by 的字段的distinct值的个数的，且与这个值应该呈线性关系，group by 后面的字段重复值越少，使用的内存也就越大。

执行计划如下：

（2）GroupAggregate

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

从上面两个执行计划的消耗来说，GroupAggregate由于需要排序，效率很差，消耗是HashAggregate的7倍，所以在gp中，对于聚合函数的使用，采用的都是HashAggregate。

2、两种实现的内存消耗

结论：

HashAggregate 在少数聚合函数时表现优异，但是对于很多聚合函数的情况，性能和消耗的内存差异很明显。尤其是受group by 字段唯一性的影响，字段count（district）值越大，HashAggregate消耗的内存越多，性能下降越明显。

所以在SQL中有大量聚合函数group by的字段重复值比较少的时候，应该用GroupAggregate，而不能用HashAggregate。

5.7.3 Nestloop Join、Hash Join与Merge Join

（1）Nestloop Join：笛卡尔积

尽量杜绝Nesloop

（2）Hash Join

这是在关联时候采用的一种很高效的方法，它先对其中一张关联的表计算Hash值，在内存中用一个散列表保存，然后对另外一张表进行全表扫描，之后将每一行与这个散列表进行关联。对于散列表来说，在理想情况下，每一行的关联都只有 O(1) 常数的消耗，从而使得表关联达到很高的性能。在一般情况，gp都是使用这个关联方式进行等值连接的。

（3）Merge Join

这种方法是对两张表都按照关联字段进行排序，然后按照排序好的内容顺序遍历一遍，将相同的值连接起来，从而实现了连接。使用这种方法，最大的消耗是对两张表进行排序，快速排序至少也要 O(nlogn) 的时间复杂度。gp默认将 MergeJoin 给关闭掉了。

5.7.4 分析函数：开窗函数和grouping sets

1、开窗函数

对于如下的sql：

explain select
row_number()over(partition by offer_type order by join_from),
row_number()over(partition by member_id order by gmt_create)
from offer;

执行计划的图示如图5-6

这段sql代码中有两个开窗函数。开窗函数的实现与group by 相似，需要把分组（partition by）的字段分不到一个节点上计算，这个表的分布键是offer_id，而offer_id不是开窗函数的分区字段，故都要将数据进行重分布才能计算，步骤如下：

顺序扫描appenonly的offer表
按照扫描member_id字段进行重分布
对数据重分布之后按照member_id和gmt_create对其进行排序，然后将排好顺序的数据进行编号，即完成这个row_number的开窗函数
再按照offer_type对数据进行重分布，用同样的方法计算另外一个开窗函数的值

由于分区字段不是分布键，所以数据全部都要重分布一遍，如果开窗函数太多，会导致数据重分布的次数非常多，每一次重分布每一个Segment都要发起一个进程来处理，这会给操作系统和网络都带来一定的压力，所以开窗函数尽量少用或者用分区键作为分布键，这样也可以减少数据库的消耗。

如果开窗函数是对整个数据进行排序，没有partition字段，那么为了维护一个全局的序列，所有数据都必须汇总到Master上进行计算，然后再重新分发到每一个节点上，这个性能瓶颈会出现在Master上，效率会很差。

2、grouping sets

使用分析函数grouping sets、cube、rollup可进行多维度分析，如下：

explain select a,b,count(1) from cxfa group by grouping sets((a),(b),(a,b));

其执行计划图如图5-7：

grouping sets的执行步骤如下：

顺序扫描cxfa表，然后将其保存在内存中，之后分两个分支进行。
分支1：读取在内存中的数据，按照a执行GroupAggregate，计算出a字段汇总结果（a是分布键）
分支2：读取内存中的数据，按照a、b执行GroupAggregate，计算出这两个字段的汇总结果，然后按照b字段重分布再计算出b字段的汇总结果。
将分支1与分支2的结果都进行重分布，然后分别执行HashAggregate1
将结果在Master上汇总起来。

5.8 案例

5.8.1 关联键强制类型转换，导致重分布

量表的关联键id的类型都是一样的，都是integer类型。如果强制将两个integer类型转换成其他类型，会导致两个表都要重分布。

正常关联的执行计划如下：

强制将两个表的执行计划转换成numeric之后的执行计划：

可以看出，由于两个表刚开始的时候都是按照integer的类型进行分布的，但是关联的时候强制将类型转换成numeric类型，由于integer与numeric的hash值是不一样的，所以数据需要重分布到新的节点进行关联。

5.8.2 统计信息过期

当统计信息过期的时候，会导致执行计划出错。选择一个糟糕的执行计划，会导致很大的数据库开销。

一般的解决办法就是将表重新使用analyze分析一下，重新收集统计信息。或者使用 vacuum full analyze 对表中的空洞进行回收，从而提高性能。

5.8.3 执行计划出错

有时统计信息是正确的，但是由于信息不够全面，或者执行的优化器还不够精准，可能会是对结果集大小的估计有很大的偏差。

例如，在sql中加入一个无用的条件：id::integer&0=0;

以上两个sql的数据量都是一样的，但是执行计划看起来有很大的区别：

...

对于这种执行计划出错，没有很好的办法，只能将sql拆分，物化一张新的表来实现。

create table offer_tmp as select * from offer_2 where id::integer&0=0;

通过物化，让gp重新收集offer_tmp的信息，然后再与member表进行关联，才能得到正确的执行计划。

5.8.4 分布键选择不恰当

分布键选择不当一般有两种情况：

随便选择一个字段作为分布键，导致关联的时候需要重分布一个表关联
分布键导致数据分布不均，sql都卡在一个Segment上进行计算

对于第一种情况，可以通过查询执行计划来得知。当执行计划出现Redistribute Motion或Broadcast Motion时，就知道重新分布了数据，这个时候就要留意分布键选择是否有误，进而导致多余的重分布，比如一个表用了字段id来分布，另外一个表通过id和name两个字段来分布，然后通过id来进行关联，测过时候也会导致数据重分布。

第二种情况，因为在执行计划中，看不出sql有什么问题，往往要到sql执行非常慢的时候才意识到有问题。在数据分布不均中，有一个特例，就是空值，这是一个比较常见的问题（在第10章中详细介绍如何排除这种问题）。

下面介绍几个方法判断表是否分布不均：

1、gp_segment_id

每个表都有一个隐藏字段gp_segment_id，表示数据是在哪个Segment上的，我们可以对这个字段进行group by 来查看每个节点的数据量

select gp_segment_id ,count(1) from test01 group by 1 order by 1;

gp_segment_id | count
------------------------
0 | 3948
1 | 3576
2 | 5448

2、get_ao_distribution

对于appendonly表，还可以通过get_ao_distribution函数来获取数据分布的信息

select * from get_ao_distribution('test01') order by 1;

segmentid | tupcount
---------------------
0 | 3948
1 | 3576
2 | 5448

3、all_seg_sql

通过这个是同，可以查看子节点上正在运行的所有sql。

如果在数据库中发现一条sql执行了很长时间，但是在执行计划中看不出有什么问题，这时可以查看这条sql的sess_id，卷号通过这个sess_id，用下面的sql查询所有节点sql的运行情况。如果只发现其中小部分节点还在运行，则表示大多数都是数据分布不均导致的。

select * from all_seg_sql where sess_id=xxxx;

还有很多中数据分布不均的情况很难发现，如果sql比较复杂，可以查询表是否分布均匀，但是由于有重分布，而对于gp来说，重分布并不会考虑数据是否均衡，因此会导致原表可能是分布均匀的，中间却发生了重分布（关联或者是聚合引起的）。这样就更难定位到问题了，如果通过all_seg_sql观察到有数据不均，那就要根据sql业务逻辑的理解或者将sql拆分成小的sql来进行分析，看看到底是哪一步导致的数据分布不均的。

5.8.5 计算distinct

在sql中使用distinct一般有两种办法。

1、将全部数据按照使用distinct那个字段排序，然后执行一个unique操作去掉重复的数据，这样的效率是比较差的

2、按照使用distinct哪个字段来计算hash值，然后放到一个hash数组中，同样的值会得到相同的hash值，从而实现去重的功能。

从开销来看，只使用了不到第一种执行计划1/10的开销。

5.8.6. union 与union all

如果使用union，会进行去重。在gp中，如果不是分布键，去重的就要涉及数据的重分布，而在gp中则更加特殊，因为这个去重是以郑航数据为分布键的，这样分布键很长，一般union的结果会插入到另外一张表中，又会造成一次数据重分布，效率会较差。

从执行计划可以看到，gp会按照所有的字段作为key去重分布数据，然后按照全部的字段去排序，再去重，从而实现 union 的操作。

使用 union all 可能会造成不必要的数据重分布。在使用union all时，可以将前后查询的数据都插入到一个临时表中，以避免不必要的数据重分布。

5.8.7 子查询 not in

not in在执行计划中都会使用笛卡尔积来执行，效率极差，为了避免这种极差的执行计划，只能改写sql来实现这种not in 的语法——使用 left join 去重后的表关联来实现一样的效果

5.8.8 聚合函数太多导致内存不足

在gp 4.1 数据库中，sql进行很多的聚合运算时，有时候会报如下的错误：

Error 7 (ERROR: Unexpected internal error:Segment process received signal SIGSEGV (postgre.c:3360) (seg43 slicel sdw19-4:30003 pid=26345) (cdbdisp.c:1457))

这段sql其实就是占用内存太多，进程被操作系统发出信号干扰导致的报错。

查看执行计划，发现是HashAggregate搞的鬼。一般来说，数据库会根据统计信息来了选择HashAggregate或GroupAggregate，但是有可能统计信息不够详细或sql太复杂而选错执行计划。

一般遇到这种问题，有两种方法：

拆分成多个sql来执行，减少HashAggregate使用的内存
在执行sql之前，先执行 enable_hashagg=off，将HashAggregate参数关掉，强制不采用HashAggregate这种聚合方式，则数据库会采用GroupAggregate，虽然增加了排序的代价但是内存使用量是可控的，建议用这种方法，比较简单。

5.9 小结

对于所有数据库来说，学会阅读执行计划，可以让我们了解整个数据库的运行方式。对于sql调优来说，执行计划是一个强有力的利器。

阅读执行计划
统计信息对执行计划的影响
各种执行计划的原理
执行计划案例分析

gp与其他数据库在执行计划上的最大区别就是广播与重分布，而这两个过程又严重依赖于统计信息的完整性，对于因统计信息不完善而导致的执行计划出错，就需要将sql拆分来实现。gp的执行计划相对其他数据库的执行计划更容易出错，而且广播大表有可能耗尽数据库所有的资源，因此在分析执行时间过长的sql时，应当首先从执行计划入手。

Greenplum企业应用实战（笔记）：第五章执行计划详解

第五章执行计划详解

5.1 执行计划入门

5.1.1 什么是执行计划

5.1.2 查看执行计划

5.2 分布式执行计划概述

5.2.1 架构

5.2.2 重分布于广播

5.2.3 Greenplum Master 的工作

5.3 Greenplum 执行计划中的术语

5.3.1 数据扫描方式

5.3.2 分布式执行

5.3.3 两种聚合方式

5.3.4 关联

5.3.5 SQL 消耗

5.3.6 其他术语

5.4 数据库统计信息收集

5.4.1 Analyze 分析

5.4.2 固定执行计划

5.5 控制执行计划的参数介绍

5.6 规划期开销的计算方法

5.7 各种执行计划原理分析

5.7.1 详解关联的广播与重分布

5.7.2 HashAggregate 与 GroupAggregate

5.7.3 Nestloop Join、Hash Join与Merge Join

5.7.4 分析函数：开窗函数和grouping sets

5.8 案例

5.8.1 关联键强制类型转换，导致重分布

5.8.2 统计信息过期

5.8.3 执行计划出错

5.8.4 分布键选择不恰当

5.8.5 计算distinct

5.8.6. union 与union all

5.8.7 子查询 not in

5.8.8 聚合函数太多导致内存不足

5.9 小结

猜你喜欢

热点阅读

Greenplum企业应用实战（笔记）：第五章 执行计划详解

第五章 执行计划详解

5.1 执行计划入门

5.1.1 什么是执行计划

5.1.2 查看执行计划

5.2 分布式执行计划概述

5.2.1 架构

5.2.2 重分布于广播

5.2.3 Greenplum Master 的工作

5.3 Greenplum 执行计划中的术语

5.3.1 数据扫描方式

5.3.2 分布式执行

5.3.3 两种聚合方式

5.3.4 关联

5.3.5 SQL 消耗

5.3.6 其他术语

5.4 数据库统计信息收集

5.4.1 Analyze 分析

5.4.2 固定执行计划

5.5 控制执行计划的参数介绍

5.6 规划期开销的计算方法

5.7 各种执行计划原理分析

5.7.1 详解关联的广播与重分布

5.7.2 HashAggregate 与 GroupAggregate

5.7.3 Nestloop Join、Hash Join与Merge Join

5.7.4 分析函数：开窗函数和grouping sets

5.8 案例

5.8.1 关联键强制类型转换，导致重分布

5.8.2 统计信息过期

5.8.3 执行计划出错

5.8.4 分布键选择不恰当

5.8.5 计算distinct

5.8.6. union 与union all

5.8.7 子查询 not in

5.8.8 聚合函数太多导致内存不足

5.9 小结

猜你喜欢

热点阅读

Greenplum企业应用实战（笔记）：第五章执行计划详解

第五章执行计划详解