GreenPlumn的SQL语句查询优化
数据库查询预准备
1. VACUUM
∙vacuum只是简单的回收空间且令其可以再次使用，没有请求排它锁，仍旧可以对表读写
∙vacuum full执行更广泛的处理，包括跨块移动行，以便把表压缩至使用最少的磁盘块数目存储。相对vacuum要慢，而且会请求排它锁。 ∙定期执行：在日常维护中，需要对数据字典定期执行vacuum，可以每天在数据库空闲的时候进行。然后每隔一段较长时间（两三个月）对系统表执行一次vacuum full，这个操作需要停机，比较耗时，大表可能耗时几个小时。
面包炉
∙reindex:执行vacuum之后,最好对表上的索引进行重建
2. ANALYZE
∙命令：analyze [talbe [(column,..)]]
∙收集表内容的统计信息，以优化执行计划。如创建索引后，执行此命令，对于随即查询将会利用索引。 ∙自动统计信息收集
∙在f中有控制自动收集的参数gp_autostats_mode设置，gp_autostats_mode三个值：none、no_change、on_no_stats（默认）
onone：禁止收集统计信息
oon change：当一条DML执行后影响的行数超过gp_autostats_on_change_threshold参数指定的值时，会执行完这条DML后再自动执行一个analyze 的操作来收集表的统计信息。
ono_no_stats：当使用create talbe as select 、insert 、copy时，如果在目标表中没有收集过统计信息，那么会自动执行analyze 来收集这张表的信息。gp默认使用on_no_stats，对数据库的消耗比较小，但是对于不断变更的表，数据库在第一次收集统计信息之后就不会再收集了。需要人为定时执行analyze.
∙如果有大量的运行时间在1分钟以下的SQL，你会发现大量的时间消耗在收集统计信息上。为了降低这一部分的消耗，可以指定对某些列不收集统计信息，如下所示：
∙1. create table test(id int, name text,note text);
自制室内单杠
上面是已知道表列note不需出现在join列上，也不会出现在where语句的过滤条件下，因为可以把这个列设置为不收集统计信息：
1. alter table test alter note SET STATISTICS 0;
3. EXPLAIN执行计划
显示规划器为所提供的语句生成的执行规划。
∙cost：返回第一行记录前的启动时间， 和返回所有记录的总时间（以磁盘页面存取为
陶瓷保险丝单位计量）
∙rows：根据统计信息估计SQL返回结果集的行数
∙width：返回的结果集的每一行的长度，这个长度值是根据pg_statistic表中的统计信息
来计算的。
4. 两种聚合方式
∙hashaggregate
根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表，几个聚合函数就有几个数组。相同数据量的情况下，聚合字段的重复度越小，使用的内存越大。兑换券制作
∙groupaggregate 剪力墙加固
先将表中的数据按照group by的字段排序，在对排好序的数据进行全扫描，并进行聚合函数计算。消耗内存基本是恒定的。
∙选择
在SQL中有大量的聚合函数，group by的字段重复值比较少的时候，应该用groupaggregate
5. 关联
分为三类：hash join、nestloop join、merge join，在保证sql执行正确的前提下，规划器优先采用hash join。
∙hash join: 先对其中一张关联的表计算hash值，在内存中用一个散列表保存，然后对另外一张表进行全表扫描，之后将每一行与这个散列表进行关联。
∙nestedloop:关联的两张表中的数据量比较小的表进行广播，如笛卡尔积：select * fromtest1，test2
∙merge join:将两张表按照关联键进行排序，然后按照归并排序的方式将数据进行关联，效率比hash join差。full outer join只能采用merge join来实现。
∙关联的广播与重分布解析P133，一般规划器会自动选择最优执行计划。 ∙有时会导致重分布和广播，比较耗时的操作
6. 重分布
一些sql查询中，需要数据在各节点重新分布，受制于网络传输、磁盘I/O，重分布的速度比较慢。
∙关联键强制类型转换
一般，表按照指定的分布键作hash分部。如果两个表按照id:intege、id:numericr分布，关联时，需要有一个表id作强制类型转化，因为不同类型的hash值不一样，因而导致数据重分布。
∙关联键与分部键不一致
∙group by、开窗函数、grouping sets会引发重分布
查询优化
通过explain观察执行计划，从而确定如果优化SQL。
1. explain参数
显示规划器为所提供的语句生成的执行规划。
∙cost：返回第一行记录前的启动时间， 和返回所有记录的总时间（以磁盘页面存取为单位计量）
∙rows：根据统计信息估计SQL返回结果集的行数
∙打铃器width：返回的结果集的每一行的长度，这个长度值是根据pg_statistic表中的统计信息来计算的。
2. 选择合适分布键
分布键选择不当会导致重分布、数据分布不均等，而数据分布不均会使SQL集中在一个segment节点的执行，限制了gp整体的速度。
∙使所有节点数据存放是均匀的，数据分布均匀才能充分利用多台机器查询，发挥分布式的优势。
∙join、开窗函数等尽量以分布键作为关联键、分区键。尤其需要注意的是join、开窗函数会
依据关联键、分区键做重分布或者广播操作，因而若分布键和关联键不一致，不论如何修改分布键，也是需要再次重分布的。
∙尽量保证where条件产生的结果集的存储也尽量是均匀的。
∙查看某表是否分布不均： select gp_segment_id,count(*) from fact_tablegroup by gp_segment_id
∙在segment一级，可以通过select gp_segment_id,count(*) from fact_table group by gp_segment_id的方式检查每张表的数据是否均匀存放
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议