Greenplum数据库设计开发规范

G r e e n p l u m数据库设计开发规范集团企业公司编码：（LL3698-KKI1269-TM2483-LUI12689-ITT289-目录第一章前言1.1文档目的随着Greenplum数据库的正式上线使用。

为了保证Greenplum 数据仓库系统平台的平稳运行，保证系统的可靠性、稳定性、可维护性和高性能。

特制定本开发规范，以规范基于Greenplum数据库平台的相关应用开发，提高开发质量。

1.2预期读者Greenplum数据仓库平台应用的设计与开发人员；Greenplum 数据仓库平台的系统管理人员和数据库管理员；Greenplum 数据仓库平台的运行维护人员；1.3参考资料参考Greenplum4.3.x版本官方指引：《GPDB43AdminGuide.pdf》《GPDB43RefGuide.pdf》《GPDB43UtilityGuide.pdf》第二章设计规范2.1数据库对象数量数据库对象类型包括数据表、视图、函数、序列、索引等等，在Greenplum数据库中，系统元数据同时保存在Master 服务器和Segment 服务器上，过多的数据库对象会造成系统元数据的膨胀，而过多的系统元数据造成系统运行逐步变慢；同时，类似数据库的备份、恢复、扩容等较大型的操作都导致效率变慢。

因此，依据GreenplumDB产品的最佳时间，单个数据库的对象数量，应控制在10万以内。

GP数据库的对象包括：表、视图、索引、分区子表、外部表等。

如果数据表的数量太多，建议按应用域进行分库，尽量将单个数据库的表数量控制在10万以内，可以在一个集群中创建多个数据库。

【备注】：在Greenplum数据库中，一张分区表，在数据库中存储为一张父表、每张分区子表都是一张独立的库表；例如：一张按月进行分区的存储一年数据的表，如果含默认分区，共14张表。

2.2表创建规范为了避免数据库表数量太多，避免单个数据表的数据量过大，给系统的运行和使用带来困难，在Greenplum数据库中需遵循如下的表创建规范：1、GP系统表中保存的表名称都是以小写保存。

通常SQL语句中表名对大小写不敏感。

但不允许在建表语句中使用双引号（“”）包括表名，这样会影响系统表中存储的名称，使得表名存在大小写或特殊字符。

表命名也不允许出现中文字。

2、单个数据库的数据表数量建议不要超过10万张；3、禁止使用二级分区表，因为二级分区表会造成表对象数量的急剧膨胀；4、由于过多的数据文件会导致操作系统对文件的操作效率降低，直接影响到数据库的管理效率。

如果数据文件数量过多，建议增加多个表空间，把数据表均匀分布到不同的表空间。

每个表空间目录下的数据文件数量，应控制在80万以内。

文件数统计可以直接到某个Segment实例目录下指定的表空间目录下统计。

5、创建数据表（DDL）的时候（不含临时表和程序中使用的中间表），必须使用tablespace 子句指定用于存储的表空间，而不是把所有表都存储在默认表空间；例如：6、对于数据量超过1TB的大表,需从应用设计方面，考虑对大表进行优化，例如是否可划分为历史数据表和当前数据表，并分开存放；是否应采用压缩存储节省空间；是否合理分区；是否应定期清理数据等等。

2.3表结构设计2.3.1字段命名表字段的命名，与表名类似。

在GP系统表中保存的表名称都是以小写保存。

通常SQL语句中字段名称对大小写不敏感。

但不允许在建表语句中使用双引号（“”）包括字段名，这样会影响系统表中存储的名称，使得表名存在大小写或特殊字符。

字段命名也不允许出现中文字。

2.3.2数据类型数据类型的定义与相关数据的加载和使用紧密相关，数据类型的定义决定了数据所占用的空间大小，因此，必须慎重设计GP数据仓库数据表的字段类型。

数据仓库的数据来自于多个异构的业务应用系统，通常情况下，业务应用系统的字段类型选择较为随意，不同的业务系统数据类型定义存在多样化，彼此之间差异较大；因此，在数据仓库中，需在参考源系统字段类型定义的情况下，结合Greenplum 数据仓库平台的特点和要求，对字段数据类型进行设计。

Greenplum数据库的数据类型定义需遵循以下原则：1、在满足业务需求的条件下，尽可能选择空间占用最小的数据类型；以节省数据存储空间；2、在GP系统中，CHAR、VARCHAR和TEXT之间不存在性能差异，在其他的DB系统中，可能CHAR会表现出最好的性能，但在GPDB中是不存在这种性能优势的。

在多数情况下，应该选择使用VARCHAR而不是CHAR；3、定长字符串类型使用varchar,而不使用char.4、对于数值类型来说，应该尽量选择更小的数据类型来适应数据；比如，选择BIGINT类型来存储SMALLINT类型范围内的数值，会造成空间的大量浪费。

5、用来做Table Join的Column来说，应该考虑选择相同的数据类型。

如果做Join的Column具有相同的数据类型(比如主键PrimaryKey 与外键ForeignKey)，其工作效率会更高。

6、一般情况下，应尽量使用上述规范数据类型，避免出现诸如：Address，INET，ARRAY等特殊类型字段。

2.3.3数据分布基于Greenplum 数据仓库平台的特点，每张数据表都必须指定分布键DK，Greenplum 数据库根据数据分布键（Distributed Key,简称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操作；为了保证数据分布均匀，在没有合适字段作为分布键的情况下，应选择数据表的主键作为分布键；对于没有逻辑主键，又没有其他合适字段作为分布键的数据表，才建议设置其分布策略为Distributed Randomly，这只应该为最后的选择；随机分布的适合使用场景：查询时不需要和其它表关联、或只与小表关联的数据表，使用随机分布策略。

2.3.4分区表分区用以解决特别大的表的问题，分区表在执行给定的查询语句时，扫描相关的部分数据而不是全表的数据从而提高查询性能。

分区表对于数据库的管理也有帮助。

并不是任何数据表都适合做分区，应从如下几个方面判断是否应进行分区：1、表是否足够大只有非常大的事实表才适合做表分区。

若在一张表中有数亿条记录，从逻辑上把表分成较小的分区将可以改善性能。

而对于只有数万条或者更少记录的表，对分区预先进行的管理开销将远大于可以获得的性能改善。

2、对目前的性能不满意作为一种调优方案，应该在查询性能低于预期时再考虑表分区。

3、查询条件是否能匹配分区条件检查查询语句的WHERE条件是否与考虑分区的COLUMN一致。

例如，如果大部分的查询使用日期条件，那么按照月或者周的日期分区设计也许很有用，而如果查询条件更多的是使用地区条件，可以考虑使用地区将表做列表类型的分区。

4、按照某个规则数据是否可以被均匀的分拆应该选择尽量把数据均匀分拆的规则。

若每个分区储存的数据量相当，那么查询性能的改善将与分区的数量相关。

例如，把一张表分为10个分区，命中单个分区条件的查询扫表性能将比未分区的情况下高10倍。

如果以上几个方面的回答都是Yes，这样的表可以通过分区策略来提高查询性能。

如上面章节所述，在Greenplum 中，每个分区子表都对应一张独立的数据表，系统通过父子表之间的继承关系来维护分区定义信息。

如果过多的数据表进行了分区，会造成表对象数量过多，系统元数据急剧膨胀，给系统的运行和维护带来很大负担。

因此，还要综合考虑系统的表数据量情况，才可决定是否对数据表进行分区。

基于以上原则，Greenplum 数据库数据分区的使用规范如下：在性能可以满足的情况下，尽量不使用数据分区；因会造成表对象数量过多，增加执行计划生成的复杂性，禁止使用二级分区；数据量在亿级别以下，建议不要使用分区；表的数据在单个实例的数据量在100万级别以下，不需要分区；分区字段不可以UPDATE，需要用delete + insert或者truncate + insert替代实现。

2.3.5压缩存储Greenplum 数据表分两种类型：heap表和AO表（Append-optimized）。

在Greenplum 数据库中，需要对数据进行压缩，数据表则需要设置为AO表。

对数据表进行压缩，可以减少磁盘占用空间，同时也减少了对IO资源的开销（以CPU资源换IO资源）。

特别是在目前IO资源不足的硬件环境下，数据库设计应该尽可能多的使用AO表。

建议在选择压缩储存模式时，最好根据比较测试的结果来确定。

综合以上考虑，数据表压缩的设计规范如下：数据量在百万级以下的小表，不建议使用压缩存储；不要在压缩文件系统使用压缩存储；压缩表建议统一使用zlib压缩算法，压缩级别为 6(appendonly=true, compresstype=zlib, compresslevel=6);，此压缩设置满足大多数的使用场景。