GreenPlum的SQL优化方案

1、优化Greenpllum n的SQL语句查询预先准备资料库查询1.VACUUMVacuum仅回收空间，使其可以重复使用，并允许在不请求锁定的情况下从表中读取和写入Vacuum full执行更广泛的过程，包括在块之间移动行，以将表压缩到最小磁盘块数存储。vacuum相对较慢，将请求锁定。定期执行：在日常维护中，必须定期对数据字典执行vacuum，并且可以在数据库空闲时每天执行。然后每两三个月对系统表运行vacuum full。此操作需要停机、耗时，而且在大型表中可能需要几个小时。Reindex:建议在运行vacuum后重建表的索引2.ANALYZE命令：analyze talbe (column

2、，收集有关表内容的统计信息以优化执行计划。如果在创建索引后执行此命令，则立即使用索引进行查询。自动统计信息收集Postgresql.conf包含控制自动收集的参数gp_autostats_mode设置，gp_autostats_mode包含none、no_change和on _ no _ statsO none:禁止收集统计信息O on change:执行DML后，如果受影响的资料列数大于gp _ auto stats _ on _ change _ threshold参数指定的值，则在执行此DML后，会自动执行收集表格统计资料的analyze工作。O no_no_stats:使用create

3、 talbe as select，insert，copy时，如果未从目标表收集统计信息，则analyze将自动运行以收集有关此表的信息。Gp默认使用on_no_stats，数据库消耗更少，但是对于持续更改的表，首次收集统计信息后将不再收集数据库。必须定期执行Analyze。如果执行时间少于一分钟的SQL较多，则可能会发现收集统计信息需要很长时间。要减少此部分的消耗，您可以指定不收集某些列的统计信息，如下所示：1.create table test (id int，name text，note text)；表格资料栏note不会显示在join资料栏或where叙述句的筛选条件中，因为您可以将它设

4、为不收集统计资料。1.alter table test alter note set statistics 0；3.EXPLAIN执行计划显示计划人员为给定语句创建的执行计划。Cost:返回第一行记录之前的开始时间，以及返回所有记录的总时间(基于磁盘页访问)测量单位)Rows:根据统计信息估计SQL返回结果集的行数Width:根据pg_statistic表中的统计信息返回的结果集的每行长度可以计算。4.两种聚合方法杂凑aggregate如果根据Group by字段后的值计算hash值，并根据以前使用的聚集函数在内存中维护该列表，则多个聚集函数将有多个数组。对于相同的数据量，聚合字段的迭代率越小

5、，使用的内存越大。群组aggregate首先按group by字段对表中的数据进行排序，完整扫描排序后的数据，然后计算聚集函数。内存消耗基本上是恒定的。选择如果SQL具有大量聚集函数，并且group by的字段迭代值很小，则应使用groupaggregate5.关联Hash join、nestloop join和merge join。这三种类型的前提是SQL执行正确，规划者优先采用hash join。Hash join:首先计算其中一个关联表的哈希值，将其从内存保存为一个方差列表，对另一个表执行全表扫描，然后将每行与此方差列表相关联。与Nestedloop:关联的两个表中的数据量比较小的表广播

6、，例如笛卡尔产品：select * fromtest1，test2Merge join:按照关联键对两个表进行排序，然后按照合并顺序比散列关联更有效地关联数据。整个outer join只能通过merge join进行。连接的广播和重新分配分析P133由一般计划员自动选择最佳执行计划。有时，重新部署和广播会导致耗时的任务6.再分配网络传输，有些SQL查询要求根据磁盘I/O从每个节点重新分配数据。关联密钥强制类型转换通常，表使用指定的分布密钥作为散列部分。如果两个表格是根据id:intege，id:numericr分布的，则关联需要表格id才能进行必需的类型转换。由于散列值的类型不同，数据重新分配

7、。关联键与部门键不匹配Group by、window函数和grouping sets导致重新分配查询优化通过Explain观察执行计划，以确定是否优化了SQL。1.explain参数显示计划人员为给定语句创建的执行计划。Cost:返回第一行记录之前的开始时间和返回所有记录的总时间(以磁盘页访问单位度量)Rows:根据统计信息估计SQL返回结果集的行数Width:返回的结果集中每个行的长度。此长度值根据pg_statistic表中的统计信息计算。2.选择相应的分发密钥选择分布关键点不正确可能导致重新分布、数据分布不均，数据分布不均可能会导致SQL集中在一个segment节点运行中，从而限制整个g

8、p的速度。均匀分布数据的所有节点数据，以便充分利用多个系统查询。如果可能，Join、窗口函数等将使用分布关键点作为关联关键点、分区关键点。特别是，join、窗口函数根据关联的键、区域键重新分布或广播，因此，如果分布键和关联键不匹配，无论如何修改分布键，都必须重新分布。确保由Where条件生成的结果集的存储也尽可能均匀。确保Select gp _ segment _ id、count(*)from fact _ table group by gp _ segment _ id表分布不均在Segment级别，select gp _ segment _ id、count(*)from fact _

9、table group by gp _ segment _ id可确保每个表中的数据统一存档您可以在系统级别使用df -h或du -h直接验证磁盘或目录数据是否统一查看数据库中倾斜了数据的表首先，将数据倾斜定义为最大子节点数据量/平均节点数据量。将较小的值与平均节点数据量相加，以使整个表中的数据量为空时对结果的影响很小，SQL为：SELECT tabname、max(size)/(avg(size)0.001)as max _ div _ avg、Sum(SIZE) total_size自。(SELECT gp_segment_id、Oid:regclass tabname、Pg_relati

10、on_size(oid) SIZEFROM gp_dist_random(pg_class)WHERE relkind=rAND relstorage IN (a，h)tGROUP BY tabnameORDER BY 2 DESC3.分区表根据字段进行分区不会影响数据节点上的数据分布，但仅在单个数据节点上对数据进行分区和存储。您可以加快分割栏位的查询速度。4.压缩表您还可以对大型AO表和分区表使用压缩，以节省存储空间、提高系统I/O或在字段级别配置压缩。应用场景：不需要对表格执行更新和删除操作访问表时，默认情况下是全表扫描，无需创建索引不能经常向表中添加字段或修改字段类型5.分组扩展Gree

11、nplum数据库中的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，c

12、2，C3)计算聚合。(c1，c2，C3)(c1，C2)(c2，C3)(c1，C3)(C1)(C2)(C3)（）GROUPING SETS指定计算字段的聚集，与ROLLUP和CUBE相比，这些字段可以更精确地控制拆分条件。6.窗口函数窗口函数可以实现结果集的分组子集的聚合或排名函数，如population(sum)over(partition by city)。窗口功能强大，性能卓越。在数据库内部执行计算，从而防止数据传输。窗口函数row_number()从分组的子集计算行号，例如row_number() over (order by id)。如果查询计划显示表已扫描多次，则可以通过窗口函数减少

13、扫描次数。窗口函数通常可以避免使用自关联。7.保存列和保存行列存储，即同一列中的数据连续保存在一个物理文件中，压缩率高，适用于从分段表中过滤某些字段的场景。如果集群中有很多节点，并且表列很多，请注意，在每个节点的每个列中至少创建一个文件，从而全局创建更多文件，并且对表执行DDL操作的速度较慢。使用和分区表会生成更多文件，并且可能超出Linux的文件句柄限制。请特别小心。行存储：如果记录需要update/delete，则只能选择未压缩行存储方法。对于查询，如果经常选择30列以上，还必须选择如何保存行。储存资料栏：如果选取的资料栏数目非常有限，而且您想要高压缩率的资料栏以换取大量资料查询的IO效能

14、，则应选取资料栏储存模式。列存储分区表，其中每个分区中的每个列都有对应的物理文件。因此，文件过多可能会降低在Linux中同时打开的最大文件数以及超过DDL命令的效率。8.函数和存储过程支持光标，但如果可能，应全局操作数据，而不是使用光标处理数据。9.使用索引如果非常大的结果集返回非常小的结果集(不超过5%)，建议使用非结构化数据仓库任务(Bt REE index)表记录的存储顺序与索引最匹配，可以进一步减少良好的索引群集(IO)Where条件中的列是以join方式联接的，可以考虑使用索引如果关键点值重复很多，则最好使用bitmap索引有关索引用法的测试列在GP索引调整测试-基本和GP索引调整测

15、试-排序部分中。10.NOT IN使用Hash left anti semi join在gp4.3中进行了优化。以下仅适用于gp4.1及更早版本具有O not in的SQL使用笛卡尔乘积运行，使用nested join效率非常低使用left join而不是O not in=实现后表关联o是o select * from test1 where col 1 not in(select col 2 from test1)进行以下更改select * from test1 a left join(select col 2 from test1 group by col 2)b on a . col 1=b . col 2 where b . col 2 is null运行时间从30秒提高到92毫秒。11.聚集函数过多一个SQL中的聚集函数过多，统计信息不详细，或者SQL太负责，可能会错误地选择hashaggregate，从而导致内存不足。解决方法：o分割为多个SQL执行，减少hashaggregate使