调优SQL查询

调优SQL查询

Greenplum数据库的基于代价的优化器会为执行一个查询计算很多策略并且选择代价最低的方法。和其他RDBMS的优化器类似，在计算可选执行计划的代价时，Greenplum的优化器会考虑诸如要连接的表中的行数、索引的可用性以及列数据的基数等因素。规划器还会考虑数据的位置、倾向于在Segment上做尽可能多的工作以及最小化完成查询必须在Segment之间传输的数据量。

当查询运行得比预期慢时，用户可以查看优化器选择的计划以及它为计划的每一步计算出的代价。这将帮助用户确定哪些步骤消耗了最多的资源，然后修改查询或者模式来为优化器提供更加有效的可选方法。用户可以使用SQL语句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 ID）
  • 操作所使用的内存
  • 从产生最多行的Segment中检索到第一行所需的时间（以毫秒计），以及从该Segment中检索所有行花费的总时间。

如何阅读解释计划

解释计划是一份报告，它详细描述了Greenplum数据库优化器确定的执行查询要遵循的步骤。计划是一棵节点构成的树，应该从底向上阅读，每一个节点都会将其结果传递给其直接上层节点。每个节点表示计划中的一个步骤，每个节点对应的那一行标识了在该步骤中执行的操作——例如扫描、连接、聚集或者排序操作。节点还标识了用于执行该操作的方法。例如，扫描操作的方法可能是顺序扫描或者索引扫描。而连接操作可以执行哈希连接或者嵌套循环连接。

下面是一个简单查询的解释计划。该查询在存储于每一Segment中的分布表中查找行数。

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)

这个计划有七个节点——Dynamic Table Scan、Function Scan、Result、Sequence、Redistribute Motion、HashAggregate和最后的Gather Motion。每一个节点包含三个代价估计：代价cost（读取的顺序页面）、行数rows以及行宽度width。

代价cost由两部分构成。1.0的代价等于一次顺序磁盘页面读取。估计的第一部分是启动代价，它是得到第一行的代价。第二个不急是总代价，它是得到所有行的代价。

行数rows估计是由计划节点输出的行数。这个数字可能会小于计划节点实际处理或者扫描的行数，它反映了WHERE子句条件的选择度估计。总代价假设所有的行将被检索出来，但并非总是这样（例如，如果用户使用LIMIT子句）。

宽度width估计是计划节点输出的所有列的以字节计的总宽度。

节点中的代价估计包括了其所有子节点的代价，因此计划中最顶层节点（通常是一个Gather Motion）具有对计划总体执行代价的估计。这就是查询规划器想要最小化的数字。

扫描操作符扫描表中的行以寻找一个行的集合。对于不同种类的存储有不同的扫描操作符。它们包括：

  • 对表上的Seq Scan — 扫描表中的所有行。
  • Append-only Scan — 扫描行存追加优化表。
  • Append-only Columnar Scan — 扫描列存追加优化表中的行。
  • Index Scan — 遍历一个B-树索引以从表中取得行。
  • Bitmap Append-only Row-oriented Scan — 从索引中收集仅追加表中行的指针并且按照磁盘上的位置进行排序。
  • Dynamic Table Scan — 使用一个分区选择函数来选择分区。Function Scan节点包含分区选择函数的名称，可以是下列之一：
    • gp_partition_expansion — 选择表中的所有分区。不会有分区被消除。
    • gp_partition_selection — 基于一个等值表达式选择一个分区。
    • gp_partition_inversion — 基于一个范围表达式选择分区。
    Function Scan节点将动态选择的分区列表传递给Result节点，该节点又会被传递给Sequence节点。

Join操作符包括下列：

  • Hash Join – 从较小的表构建一个哈希表，用连接列作为哈希键。然后扫描较大的表，为连接列计算哈希键并且探索哈希表寻找具有相同哈希键的行。哈希连接通常是Greenplum数据库中最快的连接。解释计划中的Hash Cond标识要被连接的列。
  • Nested Loop – 在较大数据集的行上迭代，在每次迭代时于较小的数据集中扫描行。嵌套循环连接要求广播其中的一个表，这样一个表中的所有行才能与其他表中的所有行进行比较。它在较小的表或者通过使用索引约束的表上执行得不错。它还被用于笛卡尔积和范围连接。在使用Nested Loop连接大型表时会有性能影响。对于包含Nested Loop连接操作符的计划节点，应该验证SQL并且确保结果是想要的结果。设置服务器配置参数enable_nestloop为OFF（默认）能够让优化器更偏爱Hash Join。
  • Merge Join – 排序两个数据集并且将它们合并起来。归并连接对预排序好的数据很快，但是在现实世界中很少见。为了更偏爱Merge Join，可把系统配置参数enable_mergejoin设置为ON。

一些查询计划节点指定移动操作。在处理查询需要时，移动操作在Segment之间移动行。该节点标识执行移动操作使用的方法。Motion操作符包括下列：

  • Broadcast motion – 每一个Segment将自己的行发送给所有其他Segment，这样每一个Segment实例都有表的一份完整的本地拷贝。Broadcast motion可能不如Redistribute motion那么好，因此优化器通常只为小型表选择Broadcast motion。对大型表来说，Broadcast motion是不可接受的。在数据没有按照连接键分布的情况下，将把一个表中所需的行动态重分布到另一个Segment。
  • Redistribute motion – 每一个Segment重新哈希数据并且把行发送到对应于哈希键的合适Segment上。
  • Gather motion – 来自所有Segment的结果数据被组装成一个单一的流。对大部分查询计划来说这是最后的操作。

查询计划中出现的其他操作符包括：

  • Materialize – 规划器将一个子查询物化一次，这样就不用为顶层行重复该工作。
  • InitPlan – 一个预查询，被用在动态分区消除中，当执行时还不知道规划器需要用来标识要扫描分区的值时，会执行这个预查询。
  • Sort – 为另一个要求排序数据的操作（例如Aggregation或者Merge Join）准备排序数据。
  • Group By – 通过一个或者更多列分组行。
  • Group/Hash Aggregate – 使用哈希聚集行。
  • Append – 串接数据集，例如在整合从分区表中各分区扫描的行时会用到。
  • Filter – 使用来自于一个WHERE子句的条件选择行。
  • Limit – 限制返回的行数。

优化Greenplum查询

这个主题描述可以用来在某些情况下提高系统性能的Greenplum数据库特性和编程实践。

为了分析查询计划，首先找出估计代价非常高的计划节点。判断估计的行数和代价是不是和该操作执行的行数相关。

如果使用分区，验证是否实现了分区消除。要实现分区消除，查询谓词（WHERE子句）必须与分区条件相同。还有，WHERE子句不能包含显式值且不能含有子查询。

审查查询计划树的执行顺序。审查估计的行数。用户想要执行顺序构建在较小的表或者哈希连接结果上并且用较大的表来探查。最优情况下，最大的表被用于最后的连接或者探查以减少传递到树最顶层计划节点的行数。如果分析结果显示构建或探查的执行顺序不是最优的，应确保数据库统计信息为最新。运行ANALYZE将能更新数据库统计信息，进而产生一个最优的查询计划。

查找计算性倾斜的迹象。当Hash Aggregate和Hash Join之类的操作符的执行导致Segment上的不平均执行时，查询执行中会发生计算性倾斜。在一些Segment上会使用比其他更多的CPU和内存，导致非最优的执行。原因可能是在具有低基数或者非一致分布的列上的连接、排序或者聚集。用户可以在查询的EXPLAIN ANALYZE语句中检测计算性倾斜。每个节点包括任一Segment所处理的最大行数以及所有Segment处理的平均行数。如果最大行数远大于平均数，那么至少有一个Segment执行了比其他更多的工作，因此应该怀疑该操作符出现了计算性倾斜。

确定执行Sort或者Aggregate操作的计划节点。Aggregate操作下隐藏的就是一个Sort。如果Sort或者Aggregate操作涉及到大量行，这就是改进查询性能的机会。在需要排序大量行时，HashAggregate操作是比Sor和Aggregate操作更好的操作。通常优化器会因为SQL结构（也就是由于编写SQL的方式）而选择Sort操作。在重写查询时，大部分的Sort操作可以用HashAggregate替换。要更偏爱HashAggregate操作而不是Sort和Aggregate，请确保服务器配置参数enable_groupagg被设置为ON。

当解释计划显示带有大量行的广播移动时，用户应该尝试消除广播移动。一种方法是使用服务器配置参数gp_segments_for_planner来增加这种移动的代价估计，这样优化器会偏向其他可替代的方案。gp_segments_for_planner变量告诉查询规划器在其计算中使用多少主Segment。默认值是零，这会告诉规划器在估算中使用实际的主Segment数量。增加主Segment的数量会增加移动的代价，因此会更加偏向重新分布移动。例如，设置gp_segments_for_planner = 100000会告诉规划器有100,000个Segment。反过来，要影响规划器广播表而不是重新分布它，可以把gp_segments_for_planner设置为一个较低的值，例如2。

Greenplum分组扩展

Greenplum数据库对GROUP BY子句的聚集扩展可以让一些常见计算在数据库中执行得比在应用或者过程代码中更加高效：
  • 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.

这些子句的细节请参考Greenplum数据库参考指南。

窗口函数

窗口函数在结果集的划分上应用聚集或者排名函数——例如，sum(population) over (partition by city)。窗口函数很强大，因为它们的所有工作都在数据库内完成，它们比通过从数据库中检索细节行并且预处理它们来产生类似结果的前端工具具有性能优势。

  • row_number()窗口函数为一个划分中的行产生行号，例如row_number() over (order by id)。
  • 当查询计划表明一个表被多个操作扫描时，用户可以使用窗口函数来降低扫描次数。
  • 经常可以通过使用窗口函数消除自连接。