查询计划

HashData 数据仓库采用基于成本的优化器来评估执行查询的不同策略，并选择成本最低的方法。和其他关系数据库系统的优化器相似，在计算不同执行计划的成本时，HashData 数据仓库的优化器会考虑诸如关联表的行数、是否有索引、字段数据的基数等因素。还会考虑到数据的位置，尽可能在段数据库 (Segment) 上完成任务，降低在不同 Segments 间传输的数据量。

在一个查询运行速度比预期慢时，可以查看优选器生成的查询计划以及执行每一步的代价。这有助于确定哪一步最耗资源，进而可以修改查询或者模式，以便生成更好的计划。查看查询计划使用 EXPLAIN 语句。

优化器基于每个表的统计信息生成计划，所以准确的统计信息对生成最有计划至关重要。关于更新统计信息，请参看本文档的 ”使用 ANALYZE 更新统计信息” 一节。

生成查询计划

EXPLAIN 和 EXPLAIN ANALYZE 语句有助于改进查询性能。EXPLAIN 显示一个查询的执行计划以及估算的代价，但是不执行该查询。EXPLAIN ANALYZE 除了显示执行计划外还会执行查询。EXPLAIN ANALYZE 会丢弃 SELECT 语句的输出，其他操作会被执行（例如 INSERT，UPDATE 和 DELETE）。若需对 DML 语句使用 EXPLAIN ANALYZE 而不影响数据，可置 EXPLAIN ANALYZE 于事务中 （BEGIN; EXPLAIN ANALYZE …; ROLLBACK;）。

EXPLAIN ANALYZE 除了执行查询、显示查询计划外，还显示如下信息：

  • 执行查询的总时间（以毫秒为单位）；
  • 查询节点需要的工作进程个数；
  • 每个操作中处理最多行的 Segment 处理的最大行数以及 Segment 的序号；
  • 内存使用量；
  • 从处理最多行的 Segment 上获得第一行花费的时间（单位是毫秒）及从该Segment获得所有行花费的时间。

理解查询计划

解释计划详细描述了 HashData 执行查询的步骤。查询计划是颗节点树，从下向上读数据，每个节点将它的执行结果数据传递给其上的节点。每个节点表示查询计划的一个步骤，每个节点都有一行信息描述了该步骤执行的操作 – 例如扫描、关联、聚合或者排序操作等，此外还有显示执行该操作的具体方法。例如，扫描操作可能是顺序扫描或者索引扫描，关联操作可能是哈希关联或者嵌套循环关联。

下面是一个简单查询的解释计划，这个查询的结果应为每个 Segment 上 contributions 表的行数。

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 Motion。每个节点含有三个估计代价：代价（顺序读页数），行数和行的长度。

代价本身包含 2 个估计值。一部分是启动代价估计，即得到第一行的代价，第二部分是总代价估计，即处理完所有行的代价。代价为 1.0 意味着顺序读一个磁盘页。

行数是查询节点输出行数的估计。考虑到 WHERE 子句条件的选择性，可能比实际处理或者扫描的行数小。总代价假设处理所有行，而有些时候可能不需要（例如 LIMIT 子句）。

长度是查询节点输出的所有字段的总长度，单位是字节。

节点的估计代价包括所有子节点的代价，所以计划的最顶层节点（通常是 Gather Motion）包含执行计划的总代价。优化器试图降低的也正是这个数字。

扫描操作符扫描数据库表以找到期望的数据行。不同的存储类型有不同的扫描操作：

  • 堆表顺序扫描 - 扫描表的所有行

  • AO 扫描 - 扫描面向行的AO表

  • AO 列扫描 - 扫描面向列的AO表

  • 索引扫描 - 遍历B树索引，从表中获取期望的行

  • Bitmap AO 行扫描 - 从索引中获得 AO 表行的指针，并根据磁盘位置排序

  • 动态扫描 - 使用分区选择函数选择待扫描的分区。Function Scan 节点包含分区选择函数的名字：

    1. gp_partition_expansion - 选择表的所有分区，不会裁剪分区
    2. gp_partition_selection - 根据等价表达式选择分区
    3. gp_partition_inversion - 根据范围表达式选择分区

Function Scan 节点将动态选择的分区传递个 Result 节点，进而传递给 Sequence 节点。

关联操作符：

  • 哈希关联 - 用关联字段做哈希键，对小表建立哈希表。然后扫描大表，计算大表每行关联字段的哈希键，从哈希表中查找哈希值相同的行。通常哈希关联是速度最快的关联方式。查询计划中的 Hash Cond 是关联字段。
  • 嵌套循环 - 遍历大数据集，对其每一行，扫描小数据集，并找到匹配的行。嵌套循环关联需要广播一个表的数据，以便另一个表的数据可以和该表的每一行进行比较。对于小表或者使用索引的表性能良好。也用于笛卡尔关联和范围关联。对大表使用嵌套关联性能不佳。如果查询节点使用嵌套循环关联操作符，则检查 SQL ，确保结果是期望的。设置配置参数 enable_nestloop 为 OFF （默认）以优先使用哈希关联。
  • 合并关联 - 对两个数据集排序，然后合并。合并关联对已经排序的数据性能很好，但是较少使用。如要使用合并关联，设置 enable_mergejoin 为 ON。

某些查询计划节点是移动操作符 (Motion)。移动操作符负责在段数据库（Segment）间传输行数据。这种节点会标识执行移动操作的方法：

  • 广播：每个 Segment 发送其表数据给所有其他 Segments，这样每个 Segment 都有该表的完整本地拷贝。广播移动操作比充分发移动操作符效率低，所以优化器仅仅对小表使用广播操作，广播大表性能欠佳。
  • 重分发：每个 Segment 对数据关联字段计算哈希值，并发送到对应的 Segments。
  • 收集：所有 Segments 的结果数据发送给单个节点，这通常是大多数查询计划的最后一步。

其他操作符有：

  • 物化（Material） - 优化器物化子查询，避免多次使用时重复计算。
  • InitPlan - 仅仅需要执行一次的子查询，且对外围查询没有依赖。
  • 排序（Sort） - 对数据集排序，多用于为聚合或者合并关联准备数据。
  • 分组（Group By）- 根据一个或者多个字段对数据集分组。
  • 分组/哈希聚合（Aggregation）- 使用哈希对数据集进行聚合计算。
  • 追加（Append）- 合并数据集，例如当扫描分区表的多个分区时。
  • 过滤器 (Filter) - 根据 WHERE 子句条件选择匹配的数据行。
  • 限制（Limit）- 限制返回的行数。

优化查询

本节介绍某些情况下可以改善系统性能的数据库特性和编程实践。

分析查询计划时首先需找估计代价很高的操作。对比估算的行数及代价与操作实际需要处理的行数，以判断是否合理。

如果有分区表，判断分区裁剪是否有效。分区裁剪需要查询条件（ WHERE 子句）必须和分区条件一样。此外，WHERE 子句不能含有显式值，也不能包含子查询。

检查查询计划树的执行顺序，检查行数估计值。希望先处理小表，或者哈希关联的结果集，然后处理大表。理想情况是最后处理最大的表，以降低沿着查询树向上传递直到最顶层节点的行数。如果执行计划顺序不是最优的，检查数据库统计信息是否最新的。运行 ANALYZE 通常会解决这个问题，并生成最优计划。

注意计算倾斜。当执行诸如哈希聚合和哈希关联等操作符时，若不同 Segments 执行代价分布不均，则发生计算倾斜。由于某些 Segment 比其他 Segment 使用更多的 CPU 和内存，性能下降明显。原因可能是关联、排序、聚合字段基数低或者分布不均匀。通过 EXPLAIN ANALYZE 输出可以检测计算倾斜。每个节点都含有 Segment 处理的最多行数和所有 Segment 的平均行数。如果最大行数比均值大很多，那么至少有一个 Segment 需要处理更多的工作，因而有计算倾斜的可能性。

注意执行排序或者聚合操作的查询节点。聚合操作隐含排序操作。如果排序和聚合操作需要处理大量数据，则存在优化查询性能的机会。当需要对大量数据行进行排序和聚合时，优先使用 HashAggregate 操作符。通常情况下，不良 SQL 会造成优化器选择使用排序操作符。如果重写查询，大多数的排序操作可以被 HashAggregate 替代。设置 enable_groupagg 参数以优先使用 HashAggregate 而非排序聚合操作。

若查询计划显示对大量数据集使用了广播移动操作符，需要尝试避免使用广播操作符。一种方法是使用 gp_segments_for_planner 配置参数增加移动数据的估计代价。该变量告诉优化器在计算移动代价时使用多少个 segments。默认值是 0，意味着使用实际 Segment 个数。增大这个数字，移动的代价会跟着增大，优化器会优先使用重分发移动操作符。例如设置 gp_segments_for_planner=100000 告诉优化器有 100000 个 Segments。相反为了优先使用广播移动操作符，为该值设置一个小数字，例如 2。

分组扩展

HashData 数据仓库的 GROUP BY 扩展可以执行某些常用的计算，且比应用程序或者存储过程效率高。

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

ROLLUP 对分组字段（或者表达式）从最详细级别到最顶级别计算聚合计数。ROLLUP 的参数是一个有序分组字段列表，它计算从右向左各个级别的聚合。例如 ROLLUP(c1, c2, c3) 会为下列分组条件计算聚集：

  • (c1, c2, c3)
  • (c1, c2)
  • (c1)
  • ()

CUBE 为分组字段的所有组合计算聚合。例如 CUBE(c1, c2, c3) 会计算一下聚合：

  • (c1, c2, c3)
  • (c1, c2)
  • (c2, c3)
  • (c1, c3)
  • (c1)
  • (c2)
  • (c3)
  • ()

GROUPING SETS 指定对那些字段计算聚合，它可以比 ROLLUP 和 CUBE 更精确地控制分区条件。

更多细节请参见《HashData 数据仓库参考指南》。

窗口函数

窗口函数可以实现在结果集的分组子集上的聚合或者排名函数，例如 sum(population) over (partition by city)。窗口函数功能强大，性能优异。因为它在数据库内部进行计算，避免了数据传输。

  • 窗口函数 row_number() 计算一行在分组子集中的行号，例如 row_number() over (order by id)。
  • 如果查询计划显示某个表被扫描多次，那么通过窗口函数可能可以降低扫描次数。
  • 窗口函数通常可以避免使用自关联。

results matching ""

    No results matching ""