《Greenplum5.0 最佳实践》SQL 转换_columnar scan

改变 SQL 查询

Greenplum 数据库是基于代价的查询优化，查询优化器会选择代价最小的作为执行计划。
像其他的 RDBMS 优化器一样， Greenplum的查询优化器也会考虑如下因素，例如做连接操作涉及的记录数量，索引是否可用，
访问数据的字段基数。查询优化器还要考虑数据的具体位置，尽可能的在当前段内执行更多的操作，然后在进行段之间的通信操作
，因为在实际生产中，频繁的段间数据交换会产生集群的网络瓶颈。那么会降低集群的性能。

当查询执行要比我们想象的要慢时，我们需要去检查查询计划，这也就意味着，我们需要知道查询时如何执行的，如何判断该具体去
优化那些操作，从而提升效率。在确保查询吮吸进行，我们需要及时更细数据库的统计信息。

怎样生成查询计划

生产查询的计划，需要在查询之前加上关键字 EXPLAIN 或者 EXPLAIN ANALYZE , 这两者是存在区别的，对于 EXPLAIN 关
键字，实际上整个查询并不会运行，记者就意味着其不会对表中的数据做出修改。而关键字 EXPLAIN ANALYZE 却会对表中的数
据做出修改，这一点需要注意。当然如果我们将 EXPLAIN ANALYZE 放到一个事务内，那么，同样不会对表中数据进行修改。
(BEGIN; EXPLAIN ANALZE ...; ROLLBACK)

使用 EXPLAIN ANALYZE 也会多现实很多信息(用其检查计算倾斜)，如下

整个查询的运行时间单位毫秒
一个查询计划节点涉及到了多少个段数据库(工作节点)
那个段处理的记录数量最多，平均每个段处理了多少数据
每个操作的内存使用情况
启动时间(查询获得第一条记录的时间)，全部时间(查询获得全部记录的时间) 单位是毫秒

怎样阅读查询计划

查询计划的详细信息显示了，查询计划将会按着何种顺序去执行，这里需要知道的是，查询计划是树形结构，我们在阅读查询计划的
时候需要从底层往上层阅读。每一个树节点产生的结果都会直接给其上层节点使用。每一个树节点都表示着查询计划的一步，一条线
就代表着一个操作的执行。

从下面的这个简单的查询计划，我们开始分析

gpacmin=# EXPLAIN SELECT gp_segment_id, count(*)


              FROM contributions 
              GROUP BY gp_segment_id;
                             QUERY PLAN

Gather Motion 2:1 (slice2; segments: 2) (cost=0.00..4.44 rows=4 width=16) -> HashAggregate (cost=0.00..3.38 rows=4 width=16)


     Group By: contributions.gp_segment_id
     ->  Redistribute Motion 2:2  (slice1; segments: 2)  
             (cost=0.00..2.12 rows=4 width=8)
           Hash Key: contributions.gp_segment_id
           ->  Sequence  (cost=0.00..1.09 rows=4 width=8)
                 ->  Result  (cost=10.00..100.00 rows=50 width=4)
                       ->  Function Scan on gp_partition_expansion  
                               (cost=10.00..100.00 rows=50 width=4)
                 ->  Dynamic Table Scan on contributions (partIndex: 0)
                         (cost=0.00..0.03 rows=4 width=8)

Settings: optimizer=on (10 rows)

这个查询计划包含7个节点- Dynamic Table Scan, Function Scan, Result, Sequence, Redistribute Motion, HashAggregate,
Gather Motition. 每一个节点包含三个信息， cost (代价估算,顺序扫描一页的代价)，记录的数量，记录的宽度

cost - - 有两部分组成，一部分是启动代价(获得第一条记录的代价)，一部分是返回全部记录的代价,这里需要知道的是顺序扫描一个
磁盘页的代价是 1

节点返回的记录数量，这里需要知道的返回的记录数量可能要比实际处理的记录数要小很多，这就取决于 WHERE 子句。也需要
知道 LIMIT 的作用，该关键字的作用是，当查询返回记录的数量满足 limit, 就会停止返回。其耗时会明显减少很多。

记录的宽度，单位是字节。这里需要知道的是查询节点返回的全部字段数量。

每一个节点的代价是会包含其所有子节点的代价的，所以，查询计划树最上层的节点就是我们需要关注的代价。

扫描操作，包含如下：

Seq Scan on heap tables - - 扫描堆表的全部记录

Append-only Scan 按照行访问的方式扫描AO表(相比于堆表行扫描非常耗时)
Append-only Columnar Scan 按照列的访问方式扫描AO表(非常高效)
Index Scan 遍历B-Tree 索引，然后按照索引条目去访问表
Bitmap Append-only Row-oriented Scan 收集指向记录的指针在AO表中来自表的索引和排序在磁盘上
Dynamic Table Scan 根据选择条件，选取分区表进行顺序扫描。这样可以根据分区需要，过滤掉很多不必要分文的分区表，这里需要知道的是 Function Scan 节点是包含分区扫描方法的
gp_partition_expansion 选择全部的分区表，没有一个分区表被忽略掉
gp_partition_selection 根据等值表达式，选择部分分区表
gp_partition_inversion 根据范围表达式，选择部分分区表

注：这里的等值表达式和范文表达式没有具体介绍，需要详细去查看参考手册

Join 操作包含如下
Hash Join - - 根绝小表的连接字段作为哈希值构建哈希表。然后扫描大表，然后计算连接字段的哈希值、探测哈希值相同的记录。Hash Join 是当前 Greenplum 中最快的连接操作。哈希条件在显示的执行计划中会显示的标记执行哈希连接的字段
Nested Loop -- 迭代一次访问大数据集的每一条记录，每次迭代都要扫描小数据集的全部记录。嵌套循环连接要求参与链接操作的记录需要执行广播操作。在小表间执行或者使用索引时，该操作的效果很明显。它也用于笛卡尔连接和范围连接。在大表上执行嵌套循环连接时，其效率会收到影响。对于执行的查询计划中包含嵌套循环连接操作，需要验证SQL确保结果集是正确的。设置参数 enable_nestloop = OFF 服务器参数, 将会执行 Hash Join
Merge Join -- 两个已经排好序的数据集然后合并在一起。一个合并操作在预先已经排好序的数据集上执行会非常的快，但是这在真实的生产环境中，将会非常耗时。设置服务参数 enable_mergejoin = ON 将会有利于执行合并连接操作

一些查询计划需要 Motion 操作。移动操作是在段数据库之间根据查询需要移动数据。这个节点用来标记着移动操作，移动操作包含：

Broadcast motion - 每一个段都发送其拥有的数据到其他的段上，这样每个段都会拥有表的全拷贝。一个广播移动操作可能没有重分布移动操作更佳，所以，优化器只选择小表作为用于广播操作。一个广播操作并不适合大表。当连接操作不是发生在分布键上的时候，一个动态的重分配需要执行在一个表到另一个段上的重新分配。
Redistribute motion - 每一个段数据库重新计划哈希值，然后根据哈希值将数据发送到相对应的段数据库上
Gather motion - - 来自全部的段数据计算的结果数据整合到单一的数据流中，这就是查询的最终的操作

其他操作包含如下：

Materialize - -执行计划器一次性实现子查询，所以它不必要为每一个顶级记录重复工作
InitPlkan -- 一个预查询，使用动态分区消除，当规划人员需要识别要扫描的分区值的值在执行时间之前是未知的
Sort -- 对行进行排序以准备其他需要有序操作。如聚合或者合并连接
Group By --- 根据一个字段或者多个字段分组
Group/Hash Aggregate --- 使用哈希聚集行
Append --- 连接数据集，当组合从分区表中的分区扫描记录时
Filter --- 使用 WHERE 子句选择行
Limit ---- 限制返回的记录数量

优化查询

正如上面描述的Greenplum数据库特性和实际的程序的使用。可以在一些场景下提升性能。

为了分析查询计划，首先要确定那些在查询计划中代价大的节点。确定代价估算的记录数量和成本与做成操作设计的记录数是否合理

如果使用到分区，验证是否实现了分区清楚。为了实现分区清除，查询语句中的谓词(WHERE 子句)必须与分区标准相同。所以，这个 WHERE 子句必须不能包含明确的值，和不能包含子查询。

查看查询计划树的执行顺序。查看代价估算记录的数量。你希望的执行顺序建立在较小的表上或者散列连接结果，并且在交大的表上进行探测。优化，大表被用来最终的连接或者探测来减少提价到顶层树的记录的数量。如果分析检测到的执行顺序不是最佳的，那么需要确保数据库的统计信息是最新的。请运行一下 ANALYZE 。

查看计算倾斜。计算倾斜是在查询执行引起的，例如执行的操作为 Hash Aggregate 和 Hash Join 时，因为数据不均等而引起了计算倾斜。一个段数据库相比于其他段数据库使用了更多的内存和 CPU 资源，导致优化失败。导致计算倾斜的问题有很多，例如使用 join sort aggregation 等。我们可以使用 EXPLAIN ANALYZE 来探测计算倾斜。对于每一个节点上回显示各个段在处理查询时所处理的平均行数，以及那个段处理的最大行数。如果最大行数远远高于平均值，那么至少有一个段数据库执行了比其他段数据库更多的工作，这时候，就应该去怀疑是否有计算倾斜发生。

识别执行 SORT 或者 Aggregate 操作的执行计划节点，隐藏在 Aggregate 内部的操作是 sort 。如果这 Sort 或者 Aggregate 操作包含大数据量的记录，这里有一个机会用来提升查询的性能。一个 HashAggregate 操作是优先的 SORT 和
Aggregate 操作，当大数据量的记录需要被排序操作的。通常，一个排序操作江北选择在SQL构建的时候；这就是，由于书写
SQL。如果查询重写，大部分的排序操作可以使用 HashAggreagte 替代。为了确保 HashAggregate 操作可以替代 sort 和
aggregate, 我们需要修改系统参数为 enable_groupagg = ON

当查询计划显示的是对大数据的广播移动操作的时候，这就需要我们尝试去避免广播操作。一种方式是使用
gp_segment_for_planner=0 通过增加移动操作的代价来促使查询优化器去选择其他的替代操作。
参数 gp_segment_for_planner 将会告诉查询优化器多少个主段将会被用于计算。这个值默认是0，这就一位置告诉查询优化器选择
选择实际所拥有的段。增加这个参数的值可以增加移动数据到所有主段的数量，因此采用重分布移动代替广播移动。例如，在这里我
们设置的 gp_segments_for_planner = 100000, 这就意味着查询优化器有 100000 个主段，如果执行广播操作将会非常耗时，这就
回促使查询优化器选择重分布操作。相反，如果想把重分布操作转换为广播操作，那么就需要吧这个值设置的小一点，
gp_segments_for_planner = 2

Greenplum 分组扩展

Greenplum 对 GROUP BY 子句的数据聚合扩展，可以比在应用程序或者存储过程中实现代码(UDF)更高效, 下面我们查看这些常
用的计算。

GROUP BY ROLLUP (col1, col2, col3)
GROUP BY CUBE (col1, col2, col3)
GROUP BY GROUPING SETS ((col1, col2), (col1, col3))

ROLLUP 分组会创建分类聚合小计，从最细小的级别到总数，按照列表(或表达式)。 ROLLUP 采用分组列的有序列表，计算
在 GROUP BY 子句中指定的标准聚合值。然后逐渐创建更高级别的小计，从右向左移动列表。最后，它创建一个总计。

CUBE 分组会创建小计为给定的分组列表(或者表达式)。在多维分析情况下， CUBE 可以生成指定维度的数据立方体，计算所有
的小计。

GROUPING SETS 表达式，选择性的指定要创建的组的集合，这允许在多个维度进行精确的规格，而无需计算整个 ROLLUP 或 CUBE
参考

窗口函数

通过使用窗口函数，可以在当前记录行中访问到与其存在特定关系的其他记录行，相当于在每一行记录上都开了一个访问外部数据的
窗口，也就成为窗口函数。"窗口" 就是当前行可见的外部记录行的范围。 (摘自 PostgreSQL 即学即用)
书写窗口函数的样式可以是如下

agg() OVER() AS RE_NAME;在 OVER() 中，我们可以什么都不填写， 也可以使用 PARTITION BY （设置可见记录的范围）ORDER BY （对窗口中可见记录排序）两者可以联合使用SELECT tract_id, val, sum(val) OVER (PARTITION BY left(tract_id, 5) ORDER BY val ) as sum_country_ordered FROM census.facts WHERE fact_type_id =2 ORDER BY left(tract_id, 5) val;

row_number
rank
能够基于窗口区的数据实现对记录的复杂排序。

参考文献

《PostgreSQL 即学即用》
">https://gpdb.docs.pivotal.io/500/best_practices/tuning_queries.html>

https://promotion.aliyun.com/ntms/act/ambassador/sharetouser.html?userCode=n7gh3gne&utm_source=n7gh3gne

我有几张阿里云幸运券分享给你，用券购买或者升级阿里云相应产品会有特惠惊喜哦！把想要买的产品的幸运券都领走吧！快下手，马上就要抢光了。

《Greenplum5.0 最佳实践 》SQL 转换_columnar scan