Greenplum数据库设计开发规范.docx
《Greenplum数据库设计开发规范.docx》由会员分享,可在线阅读,更多相关《Greenplum数据库设计开发规范.docx(19页珍藏版)》请在冰点文库上搜索。
Greenplum数据库设计开发规范
第一章前言2
1.1文档目的2
1.2预期读者2
1.3参考资料2
第二章设计规范3
2.1数据库对象数量3
2.2表创建规范3
2.3表结构设计4
2.3.1字段命名4
2.3.2数据类型4
2.3.3数据分布5
2.3.4分区7
2.3.5压缩存储8
2.3.6索引设计9
2.4其他数据库对象设计10
2.4.1schema10
2.4.2视图11
2.4.3临时表和中间表11
第三章SQL开发规范12
3.1基本要求12
3.2WHERE条件12
3.3分区字段使用13
3.4表关联13
3.5排序语句16
3.6嵌套子查询16
3.7UNION/UNIONALL16
3.8高效SQL写法的建议18
第一章前言
一.1文档目的
随着Greenplum数据库的正式上线使用。
为了保证Greenplum数据仓库系统平台的平稳运行,保证系统的可靠性、稳定性、可维护性和高性能。
特制定本开发规范,以规范基于Greenplum数据库平台的相关应用开发,提高开发质量。
一.2预期读者
Greenplum数据仓库平台应用的设计与开发人员;
Greenplum数据仓库平台的系统管理人员和数据库管理员;
Greenplum数据仓库平台的运行维护人员;
一.3参考资料
参考Greenplum4.3.x版本官方指引:
《GPDB43AdminGuide.pdf》
《GPDB43RefGuide.pdf》
《GPDB43UtilityGuide.pdf》
第二章设计规范
二.1数据库对象数量
数据库对象类型包括数据表、视图、函数、序列、索引等等,在Greenplum数据库中,系统元数据同时保存在Master服务器和Segment服务器上,过多的数据库对象会造成系统元数据的膨胀,而过多的系统元数据造成系统运行逐步变慢;同时,类似数据库的备份、恢复、扩容等较大型的操作都导致效率变慢。
因此,依据GreenplumDB产品的最佳时间,单个数据库的对象数量,应控制在10万以内。
GP数据库的对象包括:
表、视图、索引、分区子表、外部表等。
如果数据表的数量太多,建议按应用域进行分库,尽量将单个数据库的表数量控制在10万以内,可以在一个集群中创建多个数据库。
【备注】:
在Greenplum数据库中,一张分区表,在数据库中存储为一张父表、每张分区子表都是一张独立的库表;例如:
一张按月进行分区的存储一年数据的表,如果含默认分区,共14张表。
二.2表创建规范
为了避免数据库表数量太多,避免单个数据表的数据量过大,给系统的运行和使用带来困难,在Greenplum数据库中需遵循如下的表创建规范:
1、GP系统表中保存的表名称都是以小写保存。
通常SQL语句中表名对大小写不敏感。
但不允许在建表语句中使用双引号(“”)包括表名,这样会影响系统表中存储的名称,使得表名存在大小写或特殊字符。
表命名也不允许出现中文字。
2、单个数据库的数据表数量建议不要超过10万张;
3、禁止使用二级分区表,因为二级分区表会造成表对象数量的急剧膨胀;
4、由于过多的数据文件会导致操作系统对文件的操作效率降低,直接影响到数据库的管理效率。
如果数据文件数量过多,建议增加多个表空间,把数据表均匀分布到不同的表空间。
每个表空间目录下的数据文件数量,应控制在80万以内。
文件数统计可以直接到某个Segment实例目录下指定的表空间目录下统计。
5、创建数据表(DDL)的时候(不含临时表和程序中使用的中间表),必须使用tablespace子句指定用于存储的表空间,而不是把所有表都存储在默认表空间;例如:
Createtableemployee(idint,namevarchar)
TABLESPACEtpc_data_01distributedby(id);
6、对于数据量超过1TB的大表,需从应用设计方面,考虑对大表进行优化,例如是否可划分为历史数据表和当前数据表,并分开存放;是否应采用压缩存储节省空间;是否合理分区;是否应定期清理数据等等。
二.3表结构设计
二.3.1字段命名
表字段的命名,与表名类似。
在GP系统表中保存的表名称都是以小写保存。
通常SQL语句中字段名称对大小写不敏感。
但不允许在建表语句中使用双引号(“”)包括字段名,这样会影响系统表中存储的名称,使得表名存在大小写或特殊字符。
字段命名也不允许出现中文字。
二.3.2数据类型
数据类型的定义与相关数据的加载和使用紧密相关,数据类型的定义决定了数据所占用的空间大小,因此,必须慎重设计GP数据仓库数据表的字段类型。
数据仓库的数据来自于多个异构的业务应用系统,通常情况下,业务应用系统的字段类型选择较为随意,不同的业务系统数据类型定义存在多样化,彼此之间差异较大;因此,在数据仓库中,需在参考源系统字段类型定义的情况下,结合Greenplum数据仓库平台的特点和要求,对字段数据类型进行设计。
Greenplum数据库的数据类型定义需遵循以下原则:
1、在满足业务需求的条件下,尽可能选择空间占用最小的数据类型;以节省数据存储空间;
2、在GP系统中,CHAR、VARCHAR和TEXT之间不存在性能差异,在其他的DB系统中,可能CHAR会表现出最好的性能,但在GPDB中是不存在这种性能优势的。
在多数情况下,应该选择使用VARCHAR而不是CHAR;
3、定长字符串类型使用varchar,而不使用char.
4、对于数值类型来说,应该尽量选择更小的数据类型来适应数据;比如,选择BIGINT类型来存储SMALLINT类型范围内的数值,会造成空间的大量浪费。
5、用来做TableJoin的Column来说,应该考虑选择相同的数据类型。
如果做Join的Column具有相同的数据类型(比如主键PrimaryKey与外键ForeignKey),其工作效率会更高。
6、一般情况下,应尽量使用上述规范数据类型,避免出现诸如:
Address,INET,ARRAY等特殊类型字段。
二.3.3数据分布
基于Greenplum数据仓库平台的特点,每张数据表都必须指定分布键DK,Greenplum数据库根据数据分布键(DistributedKey,简称DK,后同)值来决定记录存储在哪一个segment上,DK不仅决定了数据在集群节点上的分布,还严重影响数据查询和处理操作的执行效率,需要非常慎重的选择数据表的分布键。
对于Greenplum数据仓库平台,DK的选择需要遵循以下原则:
1、数据均匀分布原则
为了尽可能达到最好的性能,所有的Instance应该尽量储存等量的数据。
若数据的分布不平衡或倾斜,那些储存了较多数据的Instance在处理自己那部分数据时将需要耗费更多的工作量。
为了实现数据的平坦分布,可以考虑选择具有唯一性的DK,如主键。
2、本地操作原则
在处理查询时,很多处理如关联、排序、聚合等若能够在Instance本地完成,其效率将远高于跨越系统级别(需在Instance之间交叉传输数据)的操作。
当不同的Table使用相同的DK时,在DK上的关联或者排序操作将会以最高效的方式把绝大部分工作在Instance本地完成。
3、均衡的查询负载原则
在一个查询正被处理时,我们希望所有的Instance都能够处理等量的工作负载,从而尽可能达到最好的性能。
通过合理的DK设计,尽量使得查询处理的负载均匀分布在每个节点上,并且尽量保证where条件产生的结果集在各个节点上也是均匀的。
4、关联一致原则
当表于表之间存在关联时,各表应选择相同字段作为DK,并且做关联查询时,使用DK作为连接字段,尽可能使连接包含全部DK字段;
5、DK一致原则
总分父子表的DK应保持一致;中间过程表、临时表的DK应尽可能保持和源表的DK一致;
6、DK精简原则
DK字段不宜过多,DK字段越少越好。
基于以上原则,Greenplum数据仓库平台的数据表DK设计规范如下:
✓每个数据表必须通过Distribiuted子句显式指定分布键,不允许使用默认DK的方式创建数据表;
✓分布键字段原则上为1个,应尽量不要超过3个;
✓分区的父子表的分布键应完全一致;
✓中间过程表、临时表、派生表的DK应尽可能保持和源表一致;
✓具有关联关系的数据表,应尽可能使用关联字段作为分布键;
✓分布键字段不可执行Update操作;
✓为了保证数据分布均匀,在没有合适字段作为分布键的情况下,应选择数据表的主键作为分布键;
✓对于没有逻辑主键,又没有其他合适字段作为分布键的数据表,才建议设置其分布策略为DistributedRandomly,这只应该为最后的选择;
✓随机分布的适合使用场景:
查询时不需要和其它表关联、或只与小表关联的数据表,使用随机分布策略。
二.3.4分区
表分区用以解决特别大的表的问题,分区表在执行给定的查询语句时,扫描相关的部分数据而不是全表的数据从而提高查询性能。
分区表对于数据库的管理也有帮助。
并不是任何数据表都适合做分区,应从如下几个方面判断是否应进行分区:
1、表是否足够大
只有非常大的事实表才适合做表分区。
若在一张表中有数亿条记录,从逻辑上把表分成较小的分区将可以改善性能。
而对于只有数万条或者更少记录的表,对分区预先进行的管理开销将远大于可以获得的性能改善。
2、对目前的性能不满意
作为一种调优方案,应该在查询性能低于预期时再考虑表分区。
3、查询条件是否能匹配分区条件
检查查询语句的WHERE条件是否与考虑分区的COLUMN一致。
例如,如果大部分的查询使用日期条件,那么按照月或者周的日期分区设计也许很有用,而如果查询条件更多的是使用地区条件,可以考虑使用地区将表做列表类型的分区。
4、按照某个规则数据是否可以被均匀的分拆
应该选择尽量把数据均匀分拆的规则。
若每个分区储存的数据量相当,那么查询性能的改善将与分区的数量相关。
例如,把一张表分为10个分区,命中单个分区条件的查询扫表性能将比未分区的情况下高10倍。
如果以上几个方面的回答都是Yes,这样的表可以通过分区策略来提高查询性能。
如上面章节所述,在Greenplum中,每个分区子表都对应一张独立的数据表,系统通过父子表之间的继承关系来维护分区定义信息。
如果过多的数据表进行了分区,会造成表对象数量过多,系统元数据急剧膨胀,给系统的运行和维护带来很大负担。
因此,还要综合考虑系统的表数据量情况,才可决定是否对数据表进行分区。
基于以上原则,Greenplum数据库数据分区的使用规范如下:
✓在性能可以满足的情况下,尽量不使用数据分区;
✓因会造成表对象数量过多,增加执行计划生成的复杂性,禁止使用二级分区;
✓数据量在亿级别以下,建议不要使用分区;
✓表的数据在单个实例的数据量在100万级别以下,不需要分区;
✓分区字段不可以UPDATE,需要用delete+insert或者truncate+insert替代实现。
二.3.5压缩存储
Greenplum数据表分两种类型:
heap表和AO表(Append-optimized)。
在Greenplum数据库中,需要对数据进行压缩,数据表则需要设置为AO表。
对数据表进行压缩,可以减少磁盘占用空间,同时也减少了对IO资源的开销(以CPU资源换IO资源)。
特别是在目前IO资源不足的硬件环境下,数据库设计应该尽可能多的使用AO表。
建议在选择压缩储存模式时,最好根据比较测试的结果来确定。
综合以上考虑,数据表压缩的设计规范如下:
✓数据量在百万级以下的小表,不建议使用压缩存储;
✓不要在压缩文件系统使用压缩存储;
✓压缩表建议统一使用zlib压缩算法,压缩级别为6(appendonly=true,compresstype=zlib,compresslevel=6);,此压缩设置满足大多数的使用场景。
✓建议对数据仓库中的记录数超过1亿的事实表、历史数据表采用压缩存储;
✓所有历史数据表、备份表、归档表统一使用压缩存储;
二.3.6索引设计
在分布式数据库GPDB中,应尽量避免使用索引。
GPDB中大部分应用场景是使用顺序扫描。
与传统的OLTP数据库不同的是,Greenplum中数据表的数据是分布在多个节点上的。
这意味着每个节点都扫描全部数据的一小部分来查找结果。
如果使用了表分区,扫描的数据可能更少。
通常,这种情况下使用索引未必能提升性能。
索引更易于改善OLTP类型的工作负载,因其返回很少量的数据,当情况合适时查询优化器会把索引作为获取数据的选择,而不是一味的全表扫描。
添加索引会带来一些数据库开销,其必定占用相当的存储空间,并且表更新时需维护索引。
需确保索引的创建在查询工作负载中真正被使用到。
同时,需要检查索引的确对于查询性能有显着的改善(与顺序扫描的性能相比)。
Greenplum支持B-tree索引和位图(Bitmap)索引。
因此,使用索引时,需要综合考虑以下问题:
1、查询工作负载类型:
索引更适合于OLTP类型的工作负载,其返回很少量的数据,对于OLAP类型的查询负载,在GPDB中索引通常作用不大;
2、压缩表:
在查询少量数据的情况下,索引能够改善AO表上的查询性能,当情况合适时查询优化器会把索引作为获取数据的选择,而不是一味的全表扫描。
对于压缩数据来说,索引访问数据的方法是解压需要的记录而不是全部解压;
3、避免在频繁更新的列上使用索引。
在频繁更新的列上创建索引,当该列被更新时,需要消耗大量的写磁盘资源和CPU计算资源;
4、在高选择性的列适合使用B-tree索引,选择性指的是列中DISTINCT值的数量除以表中的记录.例如,如果一张表中有1000行记录且有800个DISTINCT值,选择性指数为0.8,这被认为是良好的。
唯一索引总是具备1.0的选择比,这是最好的情况;
5、低选择性的列适合使用bitmap索引;
6、索引列用于关联。
经常关联(JOIN)的COLUMN(比如外键)上建立索引或许可以改善JOIN的性能,因为其可以帮助查询规划器使用其他的关联方法;
7、索引列经常用在查询条件中。
对于大表来说,查询语句WHERE条件中经常用到的列,可以考虑使用索引。
综合以上情况,结合Greenplum平台的特点,索引设计的规范如下:
✓原则上,数据仓库中的数据表不建立索引。
只有提供给外部用户访问的表,才考虑按用户访问特性,针对常用查询字段建立索引;
✓对于跑批的中间表和临时表,不允许创建索引;
✓对于记录数在百万级别以下的小表,建议不使用索引;
✓创建组合索引时,必须将经常作为查询条件且可选择性最大的列设置为索引的首列;
✓不允许创建冗余索引;
✓对于区别度高的索引,应使用B-tree索引,例如账号、合同号等等;对于区别度低的索引,应使用Bitmap索引,例如机构、产品类型等等;
✓创建组合索引时,建议列数不要超过5列;
✓每张数据表的索引数,建议不超过5个;
✓在创建和更新索引后,必须执行Analyze操作,更新索引的统计信息;
✓在对大表进行数据加载的时候,如果存在索引,建议先删除索引,待数据加载完成,再重新创建索引;
✓对频繁更新的数据表,应定期对其执行reindex操作,以重建索引;
✓如果在分区表中使用了索引,不允许在子表上单独创建和修改索引;通常,删除顶级分区的索引,系统会自动删除相关子表的索引,但如果子表的索引有缺失,将不能自动删除子表的索引,需要一一手动删除。
✓不再使用的索引必须删除;
二.4其他数据库对象设计
二.4.1schema
模式(Schema)是在DB内组织对象的一种逻辑结构。
模式可以允许用户在一个DB内不同的模式之间使用相同Name的对象(比如Table)。
Schema命名不允许出现中文字。
Schema的规划与创建建议由系统管理员或应用设计人员统一规划和设计。
不允许在系统的Schema下创建用户表;Greenplum的系统Schema如下:
序号
Schema名称
说明
1.
gp_toolkit
提供系统管理方面的视图
2.
Information_schema
提供元数据信息的视图
3.
pg_catalog
系统对象元数据表
4.
pg_aoseg
Appendonly表的辅助元数据表
5.
pg_toast
大对象存储
6.
pg_bitmapindex
位图索引对象存储
二.4.2视图
视图的设计规范建议如下:
✓视图命名不允许使用双引号包括视图名,视图名称不允许出现中文字;
✓在视图中,不允许使用ORDERBY语句;
✓对频繁访问,具有多个大表关联,并含有复杂计算或排序的视图,建议修改为物理表;
二.4.3临时表和中间表
临时表使用规范如下:
✓对于每天定期执行的后台数据处理作业,建议不要使用临时表,因为使用临时表,会造成每天都进行大量的数据表的创建和删除,引起系统元数据表的急剧膨胀,导致需要频繁的进行系统表的Vacuum操作,从而影响系统的使用和稳定性。
✓临时表和中间表定义时必须显示指定分布键。
✓临时表和中间表,评估表数据量,建议大表统一采用压缩表。
第三章SQL开发规范
三.1基本要求
1、代码行清晰、整齐、层次分明、结构性强,易于阅读;
2、代码中应具备必要的注释以增强代码的可读性和可维护性;
3、代码应充分考虑执行效率,保证代码的高效性;
三.2WHERE条件
1、在Where条件过滤中,应尽量将函数处理放在等式的右边,以提高查询性能;
2、对于日期(date、timestamp等)类型的字段判断,条件值可直接使用字符串,GP会自动进行转换。
无需过多的使用类型转换函数,如:
to_date
使用:
WHEREcall_dt='2015-01-01';
不需要写成:
WHEREcall_dt=to_date('2015-01-01','YYYY-MM-DD');
3、在条件过滤中使用函数,不需要写select关键字。
否则会影响执行计划的准确性:
错误示例:
WHEREt.z_day=
(selectto_char(current_timestamp-interval'1minute','dd'))
andt.z_hours=
(selectto_char(current_timestamp-interval'1minute','HH24'))
4、系统中很多采用日期分区的表,分区字段类型为数值型(integer)。
等式的左边不要使用数值运算,否则会影响执行计划对分区使用的准确性。
问题示例:
WHEREstatis_date/100
可改写为:
;
5、在WHERE条件中错误的添加1<>1的判断,会导致执行计划混乱。
问题语句:
SELECT
B.DVLPER_CODE,
A.CNTY_ID,SUM(A.CALL_DUR)/60.0ASCALL_DUR
FROMmasamk.LS_GSM_TOL_DA,masamk.IU_USR_DB
WHERE1<>1
GROUPBYB.DVLPER_CODE,A.CNTY_ID
三.3分区字段使用
如上述章节提到的分区表的使用原则,使用分期表是为了降低每次表扫描涉及的数据量,已达到提升SQL处理效率的目的。
如果SQL语句中没有准确的使用分区字段就会导致遍历所有分区,导致SQL执行效率低下。
特别在多个分区表关联时,每个分区表都需要制定分区字段的条件。
除非业务上有特殊要求必须要遍历所有的(或大部分的)子分区。
三.4表关联
1、表连接中的每个表应指定缩写的别名,别名的命名尽量清晰可辨别;
2、多表关联的时候,建议所有的关联写成JOIN的形式,例如:
而不允许写成如下形式:
3、建议一个SQL语句中多表关联的关联表不要超过10张表;
4、几个大小差不多的表做关联时,过滤性较强的优先做aJOIN;
5、在大/大/小三个表内关联时,避免先把两个大表进行JOIN,除非过滤性非常强;例如:
pg_namespace为小表,其他2个表为大表
6、在大/小/小三个表内联时,优先把两个小表进行JOIN:
SELECT*
FROM(smalltableAASAINNERJOINsmalltableBASBONA.key=B.key)
INNERJOINbigtableASCONC.key=A.key
7、在关联大表的时候,左右两个连接表的关联字段不能同时存在高重复值的情况,以免因重复记录关联产生巨大的中间结果,造成磁盘占用比例的大幅增长;例如:
如果一个100万的重复记录表和一个1万的重复记录表关联,结果会高达100万*1万=100亿条记录;
8、在使用小表LEFTJOIN超大表(记录数过亿)时,强烈建议把LEFTJOIN修改为先INNERJOIN,再LEFTJION的方式实现。
这样既可以提高性能,也能避免Greenplum产生大量的临时文件;因为在Greenplum数据库中,对于LEFTJOIN语句,服务器会固定使用右表的记录,构造Hash表,然后用HashJoin的方式实现关联;如果右表非常大,会导致Hash表需要占用大量的内存,如果内存超出限制,系统会把Hash表的内容,写入到文件系统的临时文件中,如果右表是一个超大表,可能在执行此语句的时候,系统会写入大量临时文件,造成系统占用空间大幅增加;
如果是INNERJOIN语句,系统会自动选择用小表建立Hash表。
例如:
如下LEFTJOIN语句:
其执行计划如下:
从执行计划可以看出,系统会扫描右表 aoddc_cicifci0_h,对其所有数据建立一个Hash表;
如果aoddc_cicifci0_h是一个超大表,那么LEFTJOIN可以改写如下:
9、表通过分布键关联时,不要使用表达式字段的方式进行关联,否则会导致数据重分布,举例如下:
--错误的关联方式,导致数据重分布
Select*frombase_fs.aoddc_ciccrcc0_hASA
LEFTJOINtemp_resultASBONtrim(A.ci_cust_no)=B.ci_cust_no
--正确的关联方式
Select*frombase_fs.aoddc_ciccrcc0_hASA
LEFTJOINtemp_resultASBONA.ci_cust_no=B.ci_cust_no
三.5排序语句
1、不要在视图中使用OrderBy排序语句,在视图中,排序语句会被忽略;
2、ORDERBY语句执行成本很高,建议尽量避免使用;
3、不要在大的数据结果集上执行排序操作;
4、PartitionBy、Union内部实现需要对数据排序,在数据量在千万级别下,差别不大,但如果数据量在亿级别上,建议尽量使用groupby实现,尽量避免orderby操作,举例如下:
Selectcust_no,cust_namefromBigTableA
Union
Selectcust_no,cust_namefromBigTableB
建议改为groupby实现:
Selectcust_