GreenPlum的SQL优化⽅案
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(默认)
o none:禁⽌收集统计信息
o on change:当⼀条DML执⾏后影响的⾏数超过
gp_autostats_on_change_threshold参数指定的值时,会执⾏完这条DML后再
⾃动执⾏⼀个analyze 的操作来收集表的统计信息。
o no_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
sql语句优化方式
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的⽅式检查每张表的数据是否均匀存放?在系统级,可以直接⽤df -h 或du -h检查磁盘或者⽬录数据是否均匀
查看数据库中数据倾斜的表
⾸先定义数据倾斜率为:最⼤⼦节点数据量/平均节点数据量。为避免整张表的数据量为空,同时对结果的影响很⼩,在平均节点数据量基础上加上⼀个很⼩的值,SQL 如下:
SELECT tabname,
max(SIZE)/(avg(SIZE)+0.001) AS max_div_avg,
sum(SIZE) total_size
FROM
(SELECT gp_segment_id,
oid::regclass tabname,
pg_relation_size(oid) SIZE
FROM gp_dist_random('pg_class')
WHERE relkind='r'
AND relstorage IN ('a','h')) t
GROUP BY tabname
ORDER BY2DESC;
3. 分区表
按照某字段进⾏分区,不影响数据在数据节点上的分布,但是,仅在单个数据节点上,对数据进⾏分区存储。可以加快分区字段的查询速度。
4. 压缩表
对于⼤AO表和分区表使⽤压缩,以节省存储空间并提⾼系统I/O,也可以在字段级别配置压缩。应⽤场景:
不需要对表进⾏更新和删除操作
访问表的时候基本上是全表扫描,不需要建⽴索引
不能经常对表添加字段或者修改字段类型
5. 分组扩展
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的参数是⼀个有序分组字段列表,它计算从右向左各个级别的聚合。例如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更精确地控制分区条件。
6. 窗⼝函数
窗⼝函数可以实现在结果集的分组⼦集上的聚合或者排名函数,例如sum(population) over (partition by city)。窗⼝函数功能强⼤,性能优异。因为它在数据库内部进⾏计算,避免了数据传输。
窗⼝函数row_number()计算⼀⾏在分组⼦集中的⾏号,例如row_number() over (order by id)。
如果查询计划显⽰某个表被扫描多次,那么通过窗⼝函数可能可以降低扫描次数。
窗⼝函数通常可以避免使⽤⾃关联。
7. 列存储和⾏存储
列存储亦即同⼀列的数据都连续保存在⼀个物理⽂件中,有更⾼的压缩率,适合在款表中对部分字段进⾏筛选的场景。
需要注意的是:若集中节点较多,⽽且表的列也较多,每个节点的每⼀列将会⾄少产⽣⼀个⽂件,那么总体上将会产⽣⽐较多的⽂件,对表的DDL操作就会⽐较慢。在和分区表使⽤时,将会产⽣更多⽂件,甚⾄可能超过linux的⽂件句柄限制,要尤其注意。
⾏存储:如果记录需要update/delete,那么只能选择⾮压缩的⾏存⽅式。对于查询,如果选择的列的数量经常超过30个以上的列,那么也应该选择⾏存⽅式。
列存储:如果选择列的数量⾮常有限,并且希望通过较⾼的压缩⽐换取海量数据查询时的较好的IO性能,那么就应该选择列存模式。其中,列存分区表,每个分区的每个列都会有⼀个对应的物理⽂件,所以要注意避免⽂件过多,导致可能超越linux 上允许同时打开⽂件数量的上限以及DDL命令的效率很差。
8. 函数和存储过程
虽然⽀持游标但是,尽量不要使⽤游标⽅式处理数据,⽽是应该把数据作为⼀个整体进⾏操作。
9. 索引使⽤
如果是从超⼤结果集合中返回⾮常⼩的结果集(不超过5%),建议使⽤BTREE索引(⾮典型数据仓库操作)
表记录的存储顺序最好与索引⼀致,可以进⼀步减少IO(好的index cluster)
where条件中的列⽤or的⽅式进⾏join,可以考虑使⽤索引
键值⼤量重复时,⽐较适合使⽤bitmap索引
有关索引使⽤的测试见GP索引调优测试–基本篇和GP索引调优测试–排序篇。
10. NOT IN
在gp4.3中已经进⾏了优化,采⽤hash left anti semi join进⾏连接。
以下只针对gp4.1及之前
o有not in的SQL,都会采⽤笛卡尔积来执⾏,采⽤nested join,效率极差
o not in==》改⽤left join去重后的表关联来实现
o例⼦
o select * from test1 where col1 not in (select co l2 from test1)
改为
select * from test1 a left join (select col2 fro
m test1 group bycol2) b l2 where b.
col2 is null
运⾏时间由30多秒提升⾄92毫秒。
11. 聚合函数太多
⼀条SQL中聚合函数太多,⽽且可能由于统计信息不够详细或者SQL太负责,错选hashaggregate来执⾏,导致内存不⾜。解决⽅法:
o拆分成多个SQL来执⾏,减少hashaggregate使⽤的内存
o执⾏enable_hashagg=off,把hashaggregate参数关掉,强制不采⽤。将会采⽤groupaggregate,这样排序时间会长⼀些,但是内存可控,建议采⽤这种
⽅式⽐较简单。
12. 资源队列
数据写⼊、查询分别使⽤不同的⽤户,GP创建⽤户时为不同⽤户指定不同的资源队列。
13. 其它优化技巧
⽤group by对distinct改写,因为DISTINCT要进⾏排序操作
⽤UNION ALL加GROUP BY的⽅式对UNION改写
尽量使⽤GREENPLUM⾃⾝提供的聚合函数和窗⼝函数去完成⼀些复杂的分析

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。