Greenplum数据库设计开发规范参考

Greenplum 数据库设计开发运行规范 Greenplum 数据库设计开发规范数据库设计开发规范 参考文档参考文档 Greenplum 数据库设计开发运行规范 20162016 年年 7 7 月月 Greenplum 数据库设计开发运行规范 目目 录录 GREENPLUMGREENPLUM 数据库设计开发规范数据库设计开发规范 1 V1 5V1 5 1 1 1 前言前言 4 1 1 文档目的 4 1 2 文档范围 4 1 3 预期读者 4 1 4 参考资料 4 2 2 开发规范检查项开发规范检查项 4 3 3 GPGP 与与 TDTD 的差异关注点的差异关注点 6 4 4 系统级设计系统级设计 7 4 1 用户设计 7 4 1 1 超级用户 8 4 1 2 公共查询用户 8 4 1 3 公共数据区用户 8 4 1 4 集市系统用户 8 4 2 数据库表空间设计 9 4 3 资源队列设计 10 4 4 系统级的维护工作 11 4 4 1 系统表的维护工作 11 4 4 2 各种库表的维护工作 12 4 4 3 投产前统一收集统计信息 12 5 5 命名规范命名规范 12 6 6 数据库对象设计规范数据库对象设计规范 13 6 1 数据库对象数据量 13 6 2 表创建规范 13 Greenplum 数据库设计开发运行规范 6 3 表设计 14 6 3 1 数据类型 14 6 3 2 数据分布 16 6 3 3 数据分区 17 6 3 4 数据表压缩 21 6 3 5 数据表行列存储 22 6 4 索引设计 23 6 5 视图设计 25 6 6 SEQUENCE设计 26 6 7 其他数据库对象设计 26 6 7 1 Schema 26 6 7 2 函数 26 6 7 3 触发器 27 6 7 4 临时表 27 7 7 开发规范开发规范 27 7 1 GP 查询优化器 GPORCA 的使用 27 7 2 SQL 开发规范 28 7 2 1 基本要求 28 7 2 2 大小写 28 7 2 3 缩进和换行 28 7 2 4 WHERE 条件 29 7 2 5 表连接 30 7 2 6 排序语句 32 7 2 7 运算符前后间隔 33 7 2 8 CASE 语句编写 33 Greenplum 数据库设计开发运行规范 7 2 9 SQL 语句注释 34 7 2 10 嵌套子查询 36 7 2 11 高效的 SQL 语句写法 36 7 2 12 开发建议 41 7 3 存储函数开发规范 42 7 3 1 编码规则 42 7 3 2 大小写规则 44 7 3 3 缩进与换行 45 7 3 4 事务管理规范 46 7 4 应用开发规范 46 7 4 1 禁止在模板中嵌套子查询 47 7 4 2 两表关联执行 delete 语句 47 7 4 3 Window 函数注意问题 47 7 4 4 Window 函数和聚合函数使用原则 48 Greenplum 数据库设计开发运行规范 1 1 前言前言 1 11 1 文档目的文档目的 随着 Greenplum 数据库仓库平台应用逐步上线 为了保证 Greenplum 数据 仓库系统平台的平稳运行 保证系统的可靠性 稳定性 可维护性和高性能 特制定本开发规范 以规范基于 Greenplum 平台的应用开发 提高开发质量 1 21 2 文档范围文档范围 本规范主要包含 Greenplum 数据仓库平台应用开发的设计开发规范要求 适合于本行所有基于 Greenplum 数据仓库平台的应用开发 1 31 3 预期读者预期读者 Greenplum 数据仓库平台应用的设计与开发人员 Greenplum 数据仓库平台的系统管理人员和数据库管理员 Greenplum 数据仓库平台的运行维护人员 1 41 4 参考资料参考资料 GPDB43AdminGuide pdf GPDB43BestPractices pdf 2 2 开发规范检查项开发规范检查项 本规范主要用于指导 Greenplum 数据库平台的开发 通过规范要求提升开 Greenplum 数据库设计开发运行规范 发质量 本规范所提出的观点都是基于 Greenplum 数据库产品的最佳实践 同 样 作为系统或者项目的管理者 也可以通过该规范对开发质量进行审查和监 督 本章节的检查列表 是帮助系统管理人员审查开发质量 关注重点检查项 检查项目列表 序序 号号 分类分类检查项描述检查项描述 1 是否有按照开发规范创建数据库角色 1 创建子系统专用的用户 2 非超级用户 3 ETL 跑批用户与前端用户区分开 2 资源队列检查 数据库角色归属的资源队列是否符合规范 不允许使用 默认队列 pg default 3 tablespace 检查 1 是否安装规范要求创建独立的 tablespace 2 表是否按照要求创建到该 tablespace 中 3 检查相应的用户是否有配置默认 tablespace 4 系统级 表属主检查 检查表的属主 owner 是否按照规范 表属主都应该是 子系统的用户 一般属主应该是跑批用户 trans 属主不允许是超级用户 5 检查子系统的中表数量 6 库表设计 检查分区表设计是否符合规范 1 如果表太大需要按天划分分区 只在半年内保留内的 天分区 2 按月分区只在 5 年内保留月分区 3 五年前的历史分区都采用年分区 4 拉链表会有特殊的分区 如 p30001231 p00010101 5 单个分区表 子分区数量不要超过 300 个 6 检查是否有没用的分区 是否有没用的子分区则需要 结合具体的业务需求来定 Greenplum 数据库设计开发运行规范 7 检查是否需要设置为分区表 分区粒度是否合适 按照 生产环境判断分区粒度的规则 1 表的总记录数超过 3 亿 单表容量超过 50GB 需要把 表设计为分区表 2 该表在每个实例上记录数小于 50 万的表 无需进行 分区 根据生产环境上实例数计算表总记录数小于 XXX 条记录 不需要设置为分区表 3 单个子分区的记录数小于 1000 万 说明分区粒度太 细 8 检查默认分区是否有过多的数据记录 9 检查表压缩设计 统计各种压缩表的数量 如果表的记 录数小于 1000 万 该表不需要设计为压缩表 10 倾斜率检查 11 automation 中是否有配置子系统对应的 vacuum 和 analyze 任务 1 每日 analyze 任务 2 每周 analyze 任务 3 每周 vacuum 任务 12 ETL 任务 automation 中是否有配置相应的子系统的增加分区的操 作 3 3 GPGP 与与 TDTD 的差异关注点的差异关注点 经过信用卡集市的项目实施 总结的了一些编程和数据核对方面的 GP 数据 库产品与 TD 数据库产品的特性差异 1 中文字排序 TD 的中文字排序是按照汉语拼音的顺序进行排列 而 GP 数据库的中文字排 序由于其内码处理的关系 并不是按照中文拼音的顺序排列 如需要达到按照 拼音顺序排列的结果 需要对中文字符串进行处理 目前已经封装了一个函数 string convert 用于转换后进行排序 举例 Greenplum 数据库设计开发运行规范 select name string convert name from tableA order by 2 注意 string convert 函数的结果不能用于展示 只可用于排序 order by 2 char varchar 字段中汉字的长度差异 在目前 TD 库中 所有 char varchar 字段定义中都把字符集设置为了 LATIN LATIN 编码就代表在 TD 中是按照单字节的格式保存 而经测试 TD 中中 文字编码实际上是 GBK 这样一个汉字存储 TD 中长度为 2 在 GP 库中 所有数据存储编码统一为 UTF8 即使一个汉字实际有 3 个字节 在数据库存储中一个汉字长度为 1 3 char varchar 字段中字母忽略大小写的差异 在目前 TD 库中 所有 char 和 varchar 字段的定义中都有 NOT CASESPECIFIC 关键字 指明字符的处理会忽略大小写 在 GP 数据库中 所有存入数据库的都是统一按照原样大小写存储 并且处 理时不会改变其属性 也不会忽略大小写 如果需要忽略大小写 只能够采用 upper 或者 lower 等方法 注意 目前部分程序中已经采用 upper 操作实现忽略大小写的处理 但 upper 或者 lower 操作必须要在 GP 库内部处理 TDTD 数据是按照数据是按照 LATINLATIN 单字节存单字节存 储 不能在储 不能在 TDTD 输出时就操作输出时就操作 upperupper 或者或者 lowerlower 否则存储内容很可能会被破坏 否则存储内容很可能会被破坏 掉 掉 4 四舍五入算法的差异 GP 数据库所采用的四舍五入算法是传统的四舍五入算法 如 10 12345 10 1235 10 12355 10 1236 10 12346 10 1235 TD 数据库所采用的四舍五入的算法比较特殊 主要是遇到 5 进位时的差异 Greenplum 数据库设计开发运行规范 10 12345 10 1234 10 12355 10 1236 10 12365 10 1236 10 12375 10 1238 10 12346 10 1235 这个与 GP 相同 4 4 系统级设计系统级设计 系统级对象是指整个 GP 集群级别的对象 并不属于某个数据库 Database 包括 系统用户 Role 表空间 Tablespace 资源队列 Resource queue 等 4 14 1 用户设计用户设计 数据库中规划几类用户 DB Role 超级用户 公共查询用户 公共数据 区用户以及各个集市系统的用户 GP 数据库用户信息列举如下 序 号 归属区域 用户类 型 用户名 允许 登录 权限描述 1SYSgpadmin 是超级用户 用于运维 2 系统级 FRONTdwituser 是公共查询用户 3BATCHdwshdata trans 是公共数据区 sdata pdata ods 跑批用户 4 公共数据区 BACKENDdwshdata qry 否 公共数据层 sdata pdata ods 只读权限 用于其 他用户继承 5BATCHdwccd trans 是 ccard 子系统跑批用户 对象属主 公共数据 select 权限 6FRONTdwccarduser 是前端查询 拥有建表权限 公共数据 select 权限 7 信用卡集市 BATCHdwbip trans 是 bip 子系统跑批用户 对象属主 公共数据 Greenplum 数据库设计开发运行规范 select 权限 8FRONTdwbipuser 是前端查询 拥有建表权限 公共数据 select 权限 9BATCHdwcsp trans 是 csp 子系统跑批用户 对象属主 公共数据 select 权限 10FRONTdwcspuser 是前端查询 拥有建表权限 公共数据 select 权限 11BACKENDdwccard qry 否查询权限继承 grant dwccard qry to 12BATCHdwmrdm trans 是 mrdm 子系统跑批用户 ccard 属主 公共数据 select 权限 13 市场风险集 市 FRONTdwmrdmuser 是前端查询 拥有建表权限 公共数据 select 权限 XX 集市 每种用户详细描述参加如下章节 4 1 14 1 1 超级用户超级用户 GP 集群初始化好之后 默认的超级用户是 gpadmin gpadmin 用户既是操作 系统用户也是数据库的超级用户 应用于数据库的日常维护和故障处理 如 数据库启停 数据库状态监控 数据库恢复等操作 4 1 24 1 2 公共查询用户公共查询用户 GP 数据库中创建一个独立的公共查询用户 dwituser 该用于拥有数据库中 所有表的查询权限 用于前台业务查询应用 因此 所有表创建好后都需要给 dwituser 赋予 select 权限 4 1 34 1 3 公共数据区用户公共数据区用户 公共数据区 sdata pdata 主要用于接收从 TD 及其他系统导入的数据 并且对数据进行初步加工 该区域的数据是其他集市的基础数据源 数据会供 给上层所有集市系统 该区主要是后台跑批任务 创建一个独立的后台跑批用 Greenplum 数据库设计开发运行规范 户 dwshdata trans 该用户是公共数据区库表的数据 拥有所有权限 另外上 层集市系统的用户都拥有公共数据区的 select 权限 4 1 44 1 4 集市系统用户集市系统用户 对于每一个集市系统参考以前 TD 的用户管理模式 都创建 2 类用户 1 一类是后台跑批用户 命名为 trans 是归属子系统 schema 下 表的属主 即 owner 并且拥有其他子系统 schema 下表的查询权限 上线 投产操作时 使用该用户创建表和其他对象 并且负责给其他用户赋权 2 另一类是前台查询用户 命名为 user 拥有其归属子系统 schema 下表的增删改查权限 并且拥有其他子系统 schema 下表的查询 权限 以信用卡决策分析系统集市为例 其下有三个子系统 ccd bip csp 各 个子系统分别创建一个后台跑批用户 dwccd trans dwbip trans dwcsp trans 分别创建一个前台查询用户 dwccarduser dwbipuser dwcspuser 所有用户拥有公共数据区的 select 权 限 如果一个集市系统下面的子系统不止一个 为了方面于彼此之间授予 select 权限 可以创建一个专用于继承 select 权限的的用户 该用户不可登 陆数据库 如 信用卡集市 dwccard qry 用户 信用卡集市的所有表 公共数 据区的所有表都给该用户授予 select 权限 然后 dwccard qry 用户再把自身权 限授予集市的所有其他用户 其他用户都拥有了查询权限 以后 新增的库表 也只需要把 select 权限授予 dwccard qry 用户间授权方法 grant role dwccard qry to dwccd trans dwbip trans dwcsp trans dwccarduser dwbipuser dwcspuser 所有集市系统都参考信用卡集市系统的用户创建自己的用户 Greenplum 数据库设计开发运行规范 4 24 2 数据库表空间设计数据库表空间设计 表空间 tablespace 允许 DB 管理员使用多个文件系统来存储数据库对象 从而可以决定如何更好的利用他们的物理储存设备 使用表空间 允许用户使 用不同性能的磁盘来存储访问频度不同的数据库对象 例如 将经常使用的表 放在高性能磁盘的文件系统上 比如 SSD 固态盘 而将其他表放在普通硬盘的 文件系统上 在 Greenplum 数据库中 表空间必须建立在文件空间之上 在 GPDB 中 Master 和每个 Instance primary 和 mirror 都需要独立的存储位置 或者说目 录 所有这些文件系统目录构成了 GP 系统中所谓的文件空间 filespace 文 件空间定义之后 其可以被多个表空间使用 如果单个表空间下的文件数量过多 会给系统维护操作 例如备份 节点 恢复 扩容等操作带来不便 因此需控制表空间下的文件数量在一定范围 建 议每个表空间的文件对象数量不要超过 20 万 表空间下的文件数量与数据表的数量 列存储表的数量列存表的列数 分 区表的分区数量 索引数量等因素有关 可通过 Alter table 命令修改数据表 所在表空间 表空间的使用规范如下 1 建议控制每个表空间下的文件数不要超过 20 万 如果超过 20 万 请将部 分表迁移到其他表空间 2 系统默认的表空间为 pg default 建议给不同数据库设置其默认表空间 以 免太多的数据表都创建在 pg default 上 生产环境 GP 集群规划创建一个独立的 filespace 命名为 XXX fs 所有 的表空间都创建在该文件系统上 表空间规划建议如下 1 共享及公共数据层分配一个独立的初始表空间 ts shdata 01 2 为每一个系统分配独立的初始表空间 例如 信用卡决策支持系统表空 间为 ts ccard 01 每新增一个系统 需为其初始创建一个独立的表空间 3 公共数据区或者每个系统相应使用的用户 都需要设置用户的默认表空 Greenplum 数据库设计开发运行规范 间 如 dwshdata trans 用户的默认表空间为 ts shdata 01 4 如果当前使用的表空间中超过 15 万个 则需要新创建另一个表空间 如 ts shdata 02 修改相应用户的默认表空间 运维人员在日常巡检中 检查所有表空间对应的数据库目录下文件数超过 15 万以后 可以创建新的表空间 新建表空间名称需要符合命名规范 表空间 创建完成后 通过默认表空间参数 default tablespace 或表空间改名等方 法启用新的表空间 获取每个实例 每个表空间对应的目录的方法 1 查询系统初始默认表空间 pg default SELECT spcname as tblspc fsname as filespc fsedbid as seg dbid conf hostname as seghost fselocation base select oid from pg database where datname bigdata text as datadir FROM pg tablespace pgts pg filespace pgfs pg filespace entry pgfse gp segment configuration conf WHERE pgts spcfsoid pgfse fsefsoid AND pgfse fsefsoid pgfs oid AND pgfse fsefsoid 3052 AND conf dbid pgfse fsedbid and spcname pg default ORDER BY seg dbid tblspc 2 查询自定义的表空间 SELECT spcname as tblspc fsname as filespc fsedbid as seg dbid conf hostname as seghost Greenplum 数据库设计开发运行规范 fselocation pgts oid text select oid from pg database where datname bigdata text as datadir FROM pg tablespace pgts pg filespace pgfs pg filespace entry pgfse gp segment configuration conf WHERE pgts spcfsoid pgfse fsefsoid AND pgfse fsefsoid pgfs oid AND pgfse fsefsoid 3052 AND conf dbid pgfse fsedbid ORDER BY seg dbid tblspc 查询到对应的主机和路径之后 到相应的路径下 执行 find wc l 就 可以统计出数据文件的数量 4 34 3 资源队列设计资源队列设计 GPDB 需要规划资源队列 以限定不用类型的数据库操作的可用资源阈值 资源队列规划原则是按照数据库操作类型来规划 资源开销大响应时间短 资 源消耗大响应时间长 资源消耗小响应时间短 资源消耗小响应时间长 并发 度很高 对于生产环境 我们建议至少会规划两个队列 1 que batch 所有后台跑批用户 trans 归属该资源队列 应用特 点是资源消耗大响应时间长 create resource queue que batch with active statements 30 priority medium memory limit 16GB 2 que front 用于所有前端即时查询用户 user 的资源队列 应用 特点是随时可能运行一些资源消耗较高的 SQL 白天要求优先级较高 create resource queue que front with active statements 5 priority max memory limit 10GB max cost 400000 000 Greenplum 数据库设计开发运行规范 3 que front pri 用于普通查询用户 dwituser 的资源队列 应用 特点是前端用户可能较多 但要求优先级不高 create resource queue que front pri with active statements 10 priority medium max cost 400000000 关于资源队列的调整 由于 GP 系统白天和晚上所执行的任务特点有所不同 白天需要兼顾前端查询和后端批量任务 晚上则完全是批量任务优先 因此 通过两个脚本在一天的两个时间段按需调整白天和晚上的资源队列的相关配置 检查各个角色对应的资源队列的方法 select rolname rsqname from pg roles aa pg resqueue bb where aa rolresqueue bb oid order by 2 4 44 4 系统级的维护工作系统级的维护工作 系统级的维护工作主要是表的统计信息收集 analyze 以及表空间膨胀维 护 vacuum 4 4 14 4 1 系统表的维护工作系统表的维护工作 系统表的维护工作有 master 上的 vacuum analyze sh 脚本负责处理 存放 路径是两台 master 服务器的 home gpadmin gpshell 由自动化平台调用每天 执行 执行语句 sh home gpadmin gpshell vacuum analyze sh bigdata pg catalog Greenplum 数据库设计开发运行规范 4 4 24 4 2 各种库表的维护工作各种库表的维护工作 日常各种库表的维护工作有三类 1 每日 analyze 任务 针对非分区表 每个跑批日期对应的子分区表收集 统计信息 2 每周 analyze 任务 针对所有分区表的根分区进行统计信息收集 3 每周 vacuum 任务 针对膨胀超过 50 的表进行 vacuum 每个子系统都应该配置有上述三个维护操作的尾任务 4 4 34 4 3 投产前统一收集统计信息投产前统一收集统计信息 对于新投产的子系统 在初始化的数据后需要进行统一的 analyze 操作 可使用 master 服务器上的 home gpadmin gpdbanalyze 目录下的脚本进行操作 1 对整个 schema 进行 analyze 操作 可使用 analyze for schema pl 脚 本 举例 perl analyze for schema pl bigdata dwmart fis 12 2 如果针对某些表进行 analyze 操作 可以使用 gpdbanalyze sh 脚本 举例 先把待处理的表名放到一个文件中 如 tablelist txt 每一行一个表名 sh gpdbanalyze sh tablelist txt Greenplum 数据库设计开发运行规范 5 5 命名规范命名规范 1 对象名称只能使用字母 数字 下划线 不允许使用特殊字符 2 Schema 总长度不得超过 30 个字符 3 原则上表 视图名称长度不超过 30 个字符 外部表不超过 34 个字符 4 TableSpace 长度不超过 60 字符 5 关于表的子分区 partitionname 命名需要统一规范 按日期分区的 建议采用字母 p 加上日期的方式命名 如 p20151201 p201512 p2015 6 6 数据库对象设计规范数据库对象设计规范 6 16 1 数据库对象数据量数据库对象数据量 1 数据库对象类型包括数据表 视图 函数 序列 索引等等 在 Greenplum 数据库中 系统元数据保存在 Master 服务器上 过多的数据库对象会造成 系统元数据的膨胀 而过多的系统元数据造成系统运行偏慢 以及数据库 的备份 恢复 扩容等操作都造成困难 因此 单个数据库的对象数量 应控制在一定范围之内 应尽量减少数据库对象数量 单个数据库的对象 数量建议控制在 10 万以内 对象包括 表 视图 索引 分区子表 外部表 2 如果数据表的数量太多 建议按应用域进行分库 尽量将单个数据库的表 数量控制在 10 万以内 备注 在备注 在 GreenplumGreenplum 数据库中 一张分区表 在数数据库中 一张分区表 在数 据库中存储为一张父表 一张默认分区的子表 与分区数一样多的分区子据库中存储为一张父表 一张默认分区的子表 与分区数一样多的分区子 表 例如 一张按月进行分区的存储一年数据的表 在表 例如 一张按月进行分区的存储一年数据的表 在 GreenplumGreenplum 中为中为 1414 张表张表 可以在一个集群中创建多个数据库 6 26 2 表创建规范表创建规范 为了避免数据库表数量太多 避免单个数据表的数据量过大 给系统的运 行和使用带来困难 在 Greenplum 数据库中需遵循如下的表创建规范 Greenplum 数据库设计开发运行规范 1 在数据仓库模型设计时 通过允许一定数量的数据冗余 降低数据模型的 范式要求 对相关数据表进行合并 减少数据表数据量 2 单个数据库的数据表数量建议不要超过 10 万张 3 禁止使用二级分区表 因为二级分区表会造成表对象数量的急剧膨胀 4 每个表空间目录下的数据文件数量 不允许超过 20 万 因为过多的数据文 件会给系统维护带来困难 如果数据文件数量过多 建议增加多个表空间 把数据表均匀分布到不同的表空间 表空间数据文件数量估算方法为 行存表数量 行存表的分区子表数量 行存表的索引数量 行存表的分区子表 的索引数量 列存表的字段总数 列存表的分区子表的字段总数 列存表的索 引数量 列存表的分区子表的索引数量 举例说明 假设表空间下保存如下几张表 TableA 按行存储 无分区 索引数量为 5 TableB 按行存储 按机构分区 40 个子分区 索引数量为 5 TableC 按列存储 20 个字段 无分区 索引数量为 5 TableD 按列存储 20 个字段 按机构分区 40 个子分区 索引数量为 5 那么总的数据文件数量估算为 行存表数量 2 TableA TableB 行存表的分区子表数量 40 TableB 的子分区总数 行存表的索引数量 10 TableA 5 个索引 TableB 5 个索引 行存表的分区子表索引数量 40 5 200 40 个分区 每分区 5 个索引 列存表的字段总数 20 20 40 列存表的分区子表的字段总数 40 20 800 列存表的索引数量 5 5 10 列存表的分区子表的索引数量 40 5 200 所以 数据文件总数估计为 2 40 10 200 40 800 10 200 1302 Greenplum 数据库设计开发运行规范 5 创建数据表的时候 必须使用 tablespace 子句指定用于存储表的表空间 而不是把所有表都存储在默认表空间 例如 Create table employee id int name varchar TABLESPACETABLESPACE data ts01data ts01 distributed by id 6 对于数据量超过 10 亿的大表 需从应用设计方面 考虑对大表进行优化 例如是否可划分为历史数据表和当前数据表 并分开存放 是否应采用压 缩存储节省空间 是否合理分区 是否应定期清理数据等等 7 对于数据量超过 10 亿的大表 需在运行时检查是否使用了压缩 是否合理 分区 是否存在需清理数据 6 36 3 表设计表设计 6 3 16 3 1 数据类型数据类型 数据类型的定义与相关数据的加载和使用紧密相关 数据类型的定义决定 了数据所占用的空间大小 因此 必须慎重设计 GP 数据仓库数据表的字段类型 数据仓库的数据来自于多个异构的业务应用系统 通常情况下 业务应用 系统的字段类型选择较为随意 不同的业务系统数据类型定义存在多样化 彼 此之间差异较大 因此 在数据仓库中 需在参考源系统字段类型定义的情况 下 结合 Greenplum 数据仓库平台的特点和要求 对字段数据类型进行设计 Greenplum 数据仓库的数据类型定义需遵循以下原则 1 在满足业务需求的条件下 尽可能选择空间占用最小的数据类型 以节省数 据存储空间 Greenplum 数据库设计开发运行规范 2 在 GP 系统中 CHAR VARCHAR 和 TEXT 之间不存在性能差异 在其他的 DB 系统中 可能 CHAR 会表现出最好的性能 但在 GPDB 中是不存在这种性能优 势的 在多数情况下 应该选择使用 VARCHAR 而不是 CHAR 3 定长字符串类型使用 varchar 而不使用 char 4 对于 Numeric 类型来说 应该尽量选择更小的数据类型来适应数据 比如 选择 BIGINT 类型来存储 SMALLINT 类型范围内的数值 会造成空间的大量浪 费 5 用来做 Table Join 的 Column 来说 应该考虑选择相同的数据类型 如果做 Join 的 Column 具有相同的数据类型 比如主键 PrimaryKey 与外键 ForeignKey 其工作效率会更高 6 数据仓库在处理代码转换时 如遇到例外取值 采用的方式为在前面拼一个 特殊字符将其保留 以便后续知道这个例外取值时可将其恢复 因此 对于 此类代码字段 数据仓库的长度定义应比源系统字段的最大长度多一位 待 讨论 为了尽可能的保持数据仓库类型的一致性和规范性 以及为了在数据迁移 时 降低数据转换的复杂性 数据仓库中的数据类型定义不宜过于复杂 数据 仓库中的常用字段类型建议规范如下 序号数据类型选择该数据类型的字段 1 VARCHAR 2 标志字段 2 VARCHAR 4 代码字段 1 档 3 VARCHAR 6 代码字段 2 档 4 VARCHAR 8 代码字段 3 档 会计科目号 利率代码 5 VARCHAR 10 代码字段 4 档 Greenplum 数据库设计开发运行规范 6 VARCHAR 12 代码字段 5 档 7 VARCHAR 15 代码字段 6 档 8 VARCHAR 20 超长代码字段 9 VARCHAR 60 加载任务 10 VARCHAR 30 交易序号 流水号 顺序号 业务序号 凭 证编号 当事人编号 资产编号 事件编号 业务编号 电话号码 手机号码 传真号码 事由编号 地址编号 债券编号 证件号码 个人姓名 以及源系统过来的各种编号字段 11 VARCHAR 60 协议编号 项目编号 单位名称 申请编号 12 VARCHAR 100 地址 项目名称 摘要 13 VARCHAR 200 较长的说明描述字段 14 VARCHAR 500 较长的说明描述字段 15 VARCHAR 1000 较长的说明描述字段 16 VARCHAR 2000 较长的说明描述字段 17 VARCHAR 5000 较长的说明描述字段 18 TEXT 超大文本字段 19 DATE 日期 20 TIME 时间 21 TIMESTAMP 时间标签 22 BOOLEAN 布尔类型 23 SMALLINT 较小的整数 32768 32768 24 INTEGER 一般大小的整数 最大约 21 亿 25 BIGINT 超大整数 26 DECIMAL 18 2 金额 面积等 Greenplum 数据库设计开发运行规范 27 DECIMAL 18 4 评分 28 DECIMAL 18 6 久期 凸性 29 DECIMAL 18 9 汇率 30 DECIMAL 18 10 折算汇率 31 DECIMAL 9 2 比例 占比 抵押率 32 DECIMAL 9 6 利率 费率 税率 浮动率 久期 凸性 一般情况下 应尽量使用上述规范数据类型 避免出现诸如 Address INET ARRAY 等特殊类型字段 6 3 26 3 2 数据分布数据分布 基于 Greenplum 数据仓库平台的特点 每张数据表都必须指定分布键 DK Greenplum 数据库根据数据分布键 Distributed Key 简称 DK 后同 值 来决定记录存储在哪一个 segment 上 DK 不仅决定了数据在集群节点上的分布 还严重影响数据查询和处理操作的执行效率 需要非常慎重的选择数据表的分 布键 对于 Greenplum 数据仓库平台 DK 的选择需要遵循以下原则 1 数据均匀分布原则 为了尽可能达到最好的性能 所有的Instance应该尽量储存等量的数据 若数据的分布不平衡或倾斜 那些储存了较多数据的Instance在处理自己那 部分数据时将需要耗费更多的工作量 为了实现数据的平坦分布 可以考虑 选择具有唯一性的DK 如主键 2 本地操作原则 在处理查询时 很多处理如关联 排序 聚合等若能够在Instance本地 完成 其效率将远高于跨越系统级别 需在Instance之间交叉传输数据 的操 作 当不同的Table使用相同的DK时 在DK上的关联或者排序操作将会以最 高效的方式把绝大部分工作在Instance本地完成 Greenplum 数据库设计开发运行规范 3 均衡的查询负载原则 在一个查询正被处理时 我们希望所有的Instance都能够处理等量的工 作负载 从而尽可能达到最好的性能 通过合理的DK设计 尽量使得查询处 理的负载均匀分布在每个节点上 并且尽量保证where条件产生的结果集在 各个节点上也是均匀的 4 关联一致原则 当表于表之间存在关联时 各表应选择相同字段作为 DK 并且做关联 查询时 使用 DK 作为连接字段 尽可能使连接包含全部 DK 字段 5 DK 一致原则 总分父子表的 DK 应保持一致 中间过程表 临时表的 DK 应尽可能保持 和源表的 DK 一致 6 DK 精简原则 DK 字段不宜过多 DK 字段越少越好 基于以上原则 Greenplum 数据仓库平台的数据表 DK 设计规范如下 1 每个数据表必须通过 Distribiuted 子句显式指定分布键 DK 不允许使用 默认 DK 的方式创建数据表 2 对从 TD 系统过来的数据表 分布键应采用 TD 的 PI 字段 3 分布键字段原则上为 1 个 应尽量不要超过 3 个 4 父子表的分布键应完全一致 5 中间过程表 临时表 派生表的 DK 应尽可能保持和源表一致 6 具有关联关系的数据表 应尽可能使用关联字段作为分布键 7 对于维表 代码表 应选择其主键作为分布键 8 对于实体表 应选择其逻辑主键作为分布键 9 分布键字段不可执行 Update 操作 10 尽量避免使用随机分布策略 虽然其数据分布是均匀的 但随机分布策略 总会导致在查询执行时 数据在节点之间的交换和迁移 影响执行性能 11 为了保证数据分布均匀 在没有合适字段作为分布键的情况下 应选择数 据表的主键作为分布键 Greenplum 数据库设计开发运行规范 12 对于没有逻辑主键 又没有其他合适字段作为分布键的数据表 才建议设 置其分布策略为 Distributed Randomly 这只应该为最后的选择 13 随机分布的适合使用场景 查询时不需要和其它表关联 或只与小表关联 的数据表 使用随机分布策略 每张表数据分布检查 SQL select gp segment id count from tablename group by 1 6 3 36 3 3 数据分区数据分区 6 3 3 1 分区设计原则分区设计原则 表分区用以解决特别大的表的问题 比如事实表 解决办法就是将表分成 很多小且更容易管理的部分 分区表在执行给定的查询语句时 扫描相关的部 分数据而不是全表的数据从而提高查询性能 分区表对于数据库的管理也有帮 助 比如在数据仓库中滚动旧的数据 并不是任何数据表都适合做分区 应从如下几个方面判断是否应进行分区 1 表是否足够大 只有非常大的事实表才适合做表分区 若在一张表中有数亿条记录 从 逻辑上把表分成较小的分区将可以改善性能 而对于只有数万条或者更少记 录的表 对分区预先进行的管理开销将远大于可以获得的性能改善 2 对目前的性能不满意 作为一种调优方案 应该在查询性能低于预期时再考虑表分区 3 查询条件是否能匹配分区条件 检查查询语句的WHERE条件是否与考虑分区的COLUMN一致 例如 如果 大部分的查询使用日期条件 那么按照月或者周的日期分区设计也许很有用 而如果查询条件更多的是使用地区条件 可以考虑使用地区将表做列表类型 的分区 4 数据仓库是否需要滚动历史数据 Greenplum 数据库设计开发运行规范 历史数据的滚动需求也是分区设计的考虑因素 比如 数据仓库中仅需 要保留过去两个月的数据 如果数据按照月进行分区 将可以很容易的删除 掉两个月之前的数据 而最近的数据存入最近月份的分区即可 5 按照某个规则数据是否可以被均匀的分拆 应该选择尽量把数据均匀分拆的规则 若每个分区储存的数据量相当 那么查询性能的改善将与分区的数量相关 例如 把一张表分为10个分区 命中单个分区条件的查询扫表性能将比未分区的情况下高10倍 6 3 3 2 分区设计规范分区设计规范 如果以上几个方面的回答都是 Yes 这样的表可以通过分区策略来提高查 询性能 另外 在 Greenplum 中 每个分区子表都对应一张独立的数据表 系统通 过父子表之间的继承关系来维护分区定义信息 如果过多的数据表进行了分区 会造成表对象数量过多 系统元数据急剧膨胀 给系统的运行和维护带来很大 负担 因此 还要综合考虑系统的表数据量情况 才可决定是否对数据表进行 分区 基于以上原则 Greenplum 数据库数据分区通用的使用规范如下 1 在性能可以满足的情况下 尽量不使用数据分区 2 因会造成表对象数量过多 增加执行计划生成的复杂性 禁止使用二级分区 3 数据量在 3 亿以上 或者单表容量超过 50GB 建议设计为分区表 4 表的数据在单个实例的数据量在 50 万级别以下 不需要分区 5 单个子分区的记录数小于 1000 万 说明分区粒度太细 6 分区字段不可以 UPDATE 需要用 delete insert 替代实现 以信用卡决策支持系统为例讲解分区设计规则 以供其他集市开发参照 信用卡集市系统的数据会保留很久 从 2006 年到现在的数据都会一直保留 Greenplum 数据库设计开发运行规范 着 对于数据量超过 3 亿的表 或者单表容量超过 50GB 建议设计为分区表 可按照下面几个分区的规则进行设计 1 对于每日新增大量数据的表 如 流水表 最小分区粒度为天 规则如 下 1 5 年以前的数据 按照年划分区 2 5 年以内至半年以前的数据 按照月划分区 3 半年以内的数据 按照日划分区 4 日分区创建由 ETL 批量程序负责创建 5 日分区合并为月分区由统一的作业程序完成 定期操作 最小频率每 月操作一次 2 对于每日新增数据量有限 或者每月只保留月末一天的表 最小分区粒 度为月 规则如下 1 5 年以前的数据 按照年划分区 2 5 年以内的数据 都按照月划分区 3 月分区的创建 创建由 ETL 批量程序负责创建 4 月分区合并为年分区由统一的作业程序完成 定期操作 每年操作一 次 3 对于拉链表 主要分为开链和闭链两部分数据 因此分区也按照开链和 闭链分两块 1 开链数据统一存放在一个分区 2 闭链数据分区规则 参照上述第二点规则 5 年以前的数据 按照年 划分区 5 年以内的数据 都按照月划分区 子分区的命名 partitionname 规范 按日期分区的建议采用字母 p 加上日期的方式命名 如 p20151201 p201512 p2015 例如创建日分区 alter table tableA add partition p20160102 start 2016 01 02 data end 2016 01 03 date appendonly true orientation row Greenplum 数据库设计开发运行规范 compresstype zlib compresslevel 5 tablespace ts shdata 01 例如创建月分区 alter table tableA add partition p201601 start 2016 01 01 data end 2016 02 01 date appendonly true orientation row compresstype zlib compresslevel 5 tablespace ts shdata 01 6 3 3 3 分区维护规范分区维护规范 对于按照日期进行分区的表 为了有效控制每张分区表的自分区数量 便 于日常管理 推荐的分区维护规范如下 子分区创建 1 分区由 Automation 任务负责创建 在每个子系统中创建一个起始任务 负责创建需要的子分区 分区的创建尽量少由 DBA 人工干预 2 按月分区以及按日分区的表 都采用使用前检查分区是否存在 如果不 存在则创建的方式 无需 DBA 人工干预 3 每个子系统的每日任务 通过调用公共函数 table add partition 可 完成检查及创建分区的工作 4 建议针对日分区表 每日预创建 2 天后的分区 对于月分区表 每月月 底创建下月的分区 函数使用说明 table add partition v schema text v table text d day text 参数一 涉及表的 schema 参数二 涉及表的表名 参数三 当前任务的日期 格式为 YYYYMMDD 或者 YYYY MM DD 执行举例 select table add partition public to cdr partname 20160101 函数中会依据传入日期的上一个月的分区情况来确定所指定的表是月分区 表还是日分区表 如 传入日期为 20160406 程序需要判断 3 月份分区的情况 如果为 3 月为月分区则按照月分区规则操作 Greenplum 数据库设计开发运行规范 分区合并 为了进一步控制分区表的自分区数量 对于时间较久的数据采用分区合并 操作 按照上述分区设计规范 1 针对日分区表 每个月月初需要对 6 个月前的日分区合并为月分区 操 作公共函数为 alter partition day2mon 这个采用 automation 定时任务完 成 函数使用说明 alter parti