0


数据库设计建议

1.概述

本开发设计建议约定数据库建模和数据库应用程序开发过程中,应当遵守的设计规范。依据这些规范进行建模,能够更好的契合openGauss的分布式处理架构,输出更高效的业务SQL代码。

本开发设计建议中所陈述的“建议”和“关注”含义如下:

  • 建议:用户应当遵守的设计规则。遵守这些规则,能够保证业务的高效运行;违反这些规则,将导致业务性能的大幅下降或某些业务逻辑错误。
  • 关注:在业务开发过程中客户需要注意的细则。用于标识容易导致客户理解错误的知识点(实际上遵守SQL标准的SQL行为),或者程序中潜在的客户不易感知的默认行为。

2.数据库对象命名

数据库对象命名需要满足约束:非时序表长度不超过63个字节,时序表长度不超过53个字符,以字母或下划线开头,中间字符可以是字母、数字、下划线、$、#。

  • 【建议】避免使用保留或者非保留关键字命名数据库对象。> > > 说明: 可以使用select * from pg_get_keywords()查询openGauss的关键字,或者在关键字章节中查看。
  • 【建议】避免使用双引号括起来的字符串来定义数据库对象名称,除非需要限制数据库对象名称的大小写。数据库对象名称大小写敏感会使定位问题难度增加。
  • 【建议】数据库对象命名风格务必保持统一。- 增量开发的业务系统或进行业务迁移的系统,建议遵守历史的命名风格。- 建议使用多个单词组成,以下划线分割。- 数据库对象名称建议能够望文知意,尽量避免使用自定义缩写(可以使用通用的术语缩写进行命名)。例如,在命名中可以使用具有实际业务含义的英文词汇或汉语拼音,但规则应该在数据库实例范围内保持一致。- 变量名的关键是要具有描述性,即变量名称要有一定的意义,变量名要有前缀标明该变量的类型。
  • 【建议】表对象的命名应该可以表征该表的重要特征。例如,在表对象命名时区分该表是普通表、临时表还是非日志表:- 普通表名按照数据集的业务含义命名。- 临时表以“tmp_+后缀”命名。- 非日志表以“ul_+后缀”命名。- 外表以“f_+后缀”命名。- 不创建以redis_为前缀的数据库对象。
  • 不创建以mlog_和以matviewmap_为前缀的数据库对象。
  • 【建议】非时序表对象命名建议不要超过63字节。如果过该长度内核会对表名进行截断,从而造成和设置值不一致的现象。且在不同字符集下,可能造成字符被截断,出现预期外的字符。

3.数据库对象设计

3.1Database和Schema设计

openGauss中可以使用Database和Schema实现业务的隔离,区别在于Database的隔离更加彻底,各个Database之间共享资源极少,可实现连接隔离、权限隔离等,Database之间无法直接互访。Schema隔离的方式共用资源较多,可以通过grant与revoke语法便捷地控制不同用户对各Schema及其下属对象的权限。

  • 从便捷性和资源共享效率上考虑,推荐使用Schema进行业务隔离。
  • 建议系统管理员创建Schema和Database,再赋予相关用户对应的权限。

Database设计建议

  • 【规则】在实际业务中,根据需要创建新的Database,不建议直接使用数据库实例默认的postgres数据库。
  • 【建议】一个数据库实例内,用户自定义的Database数量建议不超过3个。
  • 【建议】为了适应全球化的需求,使数据库编码能够存储与表示绝大多数的字符,建议创建Database的时候使用UTF-8编码。
  • 【关注】创建Database时,需要重点关注字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)两个配置项。openGauss支持A、B、C和PG四种兼容模式,分别表示兼容O语法、MY语法、TD语法和POSTGRES语法,不同兼容模式下的语法行为存在一定差异,默认为A兼容模式。
  • 【关注】Database的owner默认拥有该Database下所有对象的所有权限,包括删除权限。删除权限影响较大,请谨慎使用。

Schema设计建议

  • 【关注】如果该用户不具有sysadmin权限或者不是该Schema的owner,要访问Schema下的对象,需要同时给用户赋予Schema的usage权限和对象的相应权限。
  • 【关注】如果要在Schema下创建对象,需要授予操作用户该Schema的create权限。
  • 【关注】Schema的owner默认拥有该Schema下对象的所有权限,包括删除权限。删除权限影响较大,请谨慎使用

3.2表设计

openGauss是分布式架构。数据分布在各个DN上。总体上讲,良好的表设计需要遵循以下原则:

  • 【关注】将表数据均匀分布在各个DN上。数据均匀分布,可以防止数据在部分DN上集中分布,从而导致因存储倾斜造成数据库实例有效容量下降。通过选择合适的分布列,可以避免数据倾斜。
  • 【关注】将表的扫描压力均匀分散在各个DN上。避免扫描压力集中在部分DN上,而导致性能瓶颈。例如,在事实表上使用等值过滤条件时,将会导致扫描压力不均匀。
  • 【关注】减少需要扫描的数据量。通过分区表的剪枝机制可以大幅减少数据的扫描量。
  • 【关注】尽量减少随机I/O。通过聚簇/局部聚簇可以实现热数据的连续存储,将随机I/O转换为连续I/O,从而减少扫描的I/O代价。
  • 【关注】尽量避免数据shuffle。shuffle,是指在物理上,数据从一个节点,传输到另一个节点。shuffle占用了大量宝贵的网络资源,减小不必要的数据shuffle,可以减少网络压力,使数据的处理本地化,提高数据库实例的性能和可支持的并发度。通过对关联条件和分组条件的仔细设计,能够尽可能地减少不必要的数据shuffle。

选择存储方案

【建议】表的存储类型是表定义设计的第一步,客户业务类型是决定表的存储类型的主要因素,表存储类型的选择依据请参考表1。

表 1 表的存储类型及场景

存储类型

适用场景

行存

  • 点查询(返回记录少,基于索引的简单查询)。
  • 增、删、改操作较多的场景。

列存

  • 统计分析类查询(关联、分组操作较多的场景)。
  • 即席查询(查询条件不确定,行存表扫描难以使用索引)。

选择分布方案

【建议】表的分布方式的选择一般遵循以下原则:

表 2 表的分布方式及使用场景

分布方式

描述

适用场景

Hash

表数据通过Hash方式散列到数据库实例中的所有DN上。

数据量较大的事实表。

Replication

数据库实例中每一个DN都有一份全量表数据。

维度表、数据量较小的事实表。

Range

表数据对指定列按照范围进行映射,分布到对应DN。

用户需要自定义分布规则的场景。

List

表数据对指定列按照具体值进行映射,分布到对应DN。

用户需要自定义分布规则的场景。

选择分区方案

当表中的数据量很大时,应当对表进行分区,一般需要遵循以下原则:

  • 【建议】使用具有明显区间性的字段进行分区,比如日期、区域等字段上建立分区。
  • 【建议】分区名称应当体现分区的数据特征。例如,关键字+区间特征。
  • 【建议】将分区上边界的分区值定义为MAXVALUE,以防止可能出现的数据溢出。

典型的分区表定义如下:

复制代码CREATE TABLE staffS_p1
(
  staff_ID       NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID  VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(4,2),
  MANAGER_ID     NUMBER(6),
  section_ID     NUMBER(4)
)
PARTITION BY RANGE (HIRE_DATE)
( 
   PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'),
   PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'),
   PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE)
);

选择分布键

Hash表的分布键选取至关重要,如果分布键选择不当,可能会导致数据倾斜,从而导致查询时,I/O负载集中在部分DN上,影响整体查询性能。因此,在确定Hash表的分布策略之后,需要对表数据进行倾斜性检查,以确保数据的均匀分布。分布键的选择一般需要遵循以下原则:

  • 【建议】选作分布键的字段取值应该比较离散,以便数据能在各个DN上均匀分布。当单个字段无法满足离散条件时,可以考虑使用多个字段一起作为分布键。一般情况下,可以考虑选择表的主键作为分布键。例如,在人员信息表中选择证件号码作为分布键。
  • 【建议】在满足第一条原则的情况下,尽量不要选取在查询中存在常量过滤条件的字段作为分布键。例如,在表dwcjk相关的查询中,字段zqdh存在常量过滤条件“zqdh='000001'”,那么就应当尽量不选择zqdh字段做为分布键。
  • 【建议】在满足前两条原则的情况,尽量选择查询中的关联条件为分布键。当关联条件作为分布键时,join任务的相关数据都分布在DN本地,将极大减少DN之间的数据流动代价。

3.3字段设计

选择数据类型

字段设计时,基于查询效率的考虑,一般遵循以下原则:

  • 【建议】尽量使用高效数据类型。选择数值类型时,在满足业务精度的情况下,选择数据类型的优先级从高到低依次为整数、浮点数、NUMERIC。
  • 【建议】当多个表存在逻辑关系时,表示同一含义的字段应该使用相同的数据类型。
  • 【建议】对于字符串数据,建议使用变长字符串数据类型,并指定最大长度。请务必确保指定的最大长度大于需要存储的最大字符数,避免超出最大长度时出现字符截断现象。除非明确知道数据类型为固定长度字符串,否则,不建议使用CHAR(n)、BPCHAR(n)、NCHAR(n)、CHARACTER(n)。关于字符串类型的详细说明,请参见常用字符串类型介绍。

常用字符串类型介绍

在进行字段设计时,需要根据数据特征选择相应的数据类型。字符串类型在使用时比较容易混淆,下表列出了openGauss中常见的字符串类型:

表 1 常用字符串类型

名称

描述

最大存储空间

CHAR(n)

定长字符串,n描述了存储的字节长度,如果输入的字符串字节格式小于n,那么后面会自动用空字符补齐至n个字节。

10MB

CHARACTER(n)

定长字符串,n描述了存储的字节长度,如果输入的字符串字节格式小于n,那么后面会自动用空字符补齐至n个字节。

10MB

NCHAR(n)

定长字符串,n描述了存储的字节长度,如果输入的字符串字节格式小于n,那么后面会自动用空字符补齐至n个字节。

10MB

BPCHAR(n)

定长字符串,n描述了存储的字节长度,如果输入的字符串字节格式小于n,那么后面会自动用空字符补齐至n个字节。

10MB

VARCHAR(n)

变长字符串,n描述了可以存储的最大字节长度。

10MB

CHARACTER VARYING(n)

变长字符串,n描述了可以存储的最大字节长度;此数据类型和VARCHAR(n)是同一数据类型的不同表达形式。

10MB

VARCHAR2(n)

变长字符串,n描述了可以存储的最大字节长度,此数据类型是为兼容Oracle类型新增的,行为和VARCHAR(n)一致。

10MB

NVARCHAR2(n)

变长字符串,n描述了可以存储的最大字节长度。

10MB

TEXT

不限长度(不超过1GB-8203字节)变长字符串。

1GB-8203字节

3.4 约束设计

DEFAULT和NULL约束

  • 【建议】如果能够从业务层面补全字段值,那么,就不建议使用DEFAULT约束,避免数据加载时产生不符合预期的结果。
  • 【建议】给明确不存在NULL值的字段加上NOT NULL约束,优化器会在特定场景下对其进行自动优化。
  • 【建议】给可以显式命名的约束显式命名。除了NOT NULL和DEFAULT约束外,其他约束都可以显式命名。

局部聚簇

Partial Cluster Key(局部聚簇,简称PCK)是列存表的一种局部聚簇技术,在openGauss中,使用PCK可以通过min/max稀疏索引实现事实表快速过滤扫描。PCK的选取遵循以下原则:

  • 【关注】一张表上只能建立一个PCK,一个PCK可以包含多列,但是一般不建议超过2列。
  • 【建议】在查询中的简单表达式过滤条件上创建PCK。这种过滤条件一般形如col op const,其中col为列名,op为操作符 =、>、>=、<=、<,const为常量值。
  • 【建议】在满足上面条件的前提下,选择distinct值比较多的列上建PCK。

唯一约束

  • 【关注】行存表、列存表均支持唯一约束。
  • 【建议】从命名上明确标识唯一约束,例如,命名为“UNI+构成字段”。

主键约束

  • 【关注】行存表、列存表均支持主键约束。
  • 【建议】从命名上明确标识主键约束,例如,将主键约束命名为 “PK+字段名”。

外键约束

  • 【关注】行存表支持外键约束,列存表不支持外键约束。
  • 【建议】从命名上明确标识外键约束,例如,将外键约束命名为 “FK+字段名”。

检查约束

  • 【关注】行存表支持检查约束,而列存表不支持。
  • 【建议】从命名上明确标识检查约束,例如,将检查约束命名为 “CK+字段名”。

3.5 视图和关联表设计

视图设计

  • 【建议】除非视图之间存在强依赖关系,否则不建议视图嵌套。
  • 【建议】视图定义中尽量避免排序操作。

关联表设计

  • 【建议】表之间的关联字段应该尽量少。
  • 【建议】关联字段的数据类型应该保持一致。
  • 【建议】关联字段在命名上,应该可以明显体现出关联关系。例如,采用同样名称来命名。

4.JDBC配置

目前,openGauss相关的第三方工具都是通过JDBC进行连接的,此部分将介绍工具配置时的注意事项。

连接参数

  • 【关注】第三方工具通过JDBC连接openGauss时,JDBC向openGauss发起连接请求,会默认添加以下配置参数,详见JDBC代码ConnectionFactoryImpl类的实现。复制代码params = {{ "user", user },{ "database", database },{ "client_encoding", "UTF8" },{ "DateStyle", "ISO" },{ "extra_float_digits", "2" },{ "TimeZone", createPostgresTimeZone() },};这些参数可能会导致JDBC客户端的行为与gsql客户端的行为不一致,例如,Date数据显示方式、浮点数精度表示、timezone显示。如果实际期望和这些配置不符,建议在java连接设置代码中显式设定这些参数。
  • 【建议】通过JDBC连接数据库时,应该保证下面三个时区设置一致:- JDBC客户端所在主机的时区。- openGauss数据库实例所在主机的时区。- openGauss数据库实例配置过程中时区。> > > 说明: 时区设置相关的操作,请参考《安装指南》中“企业版安装>安装准备>准备软硬件安装环境> 同步系统时间”章节内容。

fetchsize

【关注】在应用程序中,如果需要使用fetchsize,必须关闭autocommit。开启autocommit,会令fetchsize配置失效。

autocommit

【建议】在JDBC向openGauss申请连接的代码中,建议显式打开autocommit开关。如果基于性能或者其它方面考虑,需要关闭autocommit时,需要应用程序自己来保证事务的提交。例如,在指定的业务SQL执行完之后做显式提交,特别是客户端退出之前务必保证所有的事务已经提交。

释放连接

【建议】推荐使用连接池限制应用程序的连接数。每执行一条SQL就连接一次数据库,是一种不好的SQL编写习惯。

【建议】在应用程序完成作业任务之后,应当及时断开和openGauss的连接,释放资源。建议在任务中设置session超时时间参数。

【建议】使用JDBC连接池,在将连接释放给连接池前,需要执行以下操作,重置会话环境。否则,可能会产生因为历史会话信息导致的对象冲突。

  • 如果在连接中设置了GUC参数,那么在将连接归还连接池之前,必须使用“SET SESSION AUTHORIZATION DEFAULT;RESET ALL;”将连接的状态清空。
  • 如果使用了临时表,那么在将连接归还连接池之前,必须将临时表删除。

CopyManager

【建议】在不使用ETL工具,数据入库实时性要求又比较高的情况下,建议在开发应用程序时,使用openGauss JDBC驱动的copyManger接口进行微批导入。

5.SQL编写

DDL

  • 【建议】在openGauss中,建议DDL(建表、comments等)操作统一执行,在批处理作业中尽量避免DDL操作。避免大量并发事务对性能的影响。
  • 【建议】在非日志表(unlogged table)使用完后,立即执行数据清理(truncate)操作。因为在异常场景下,openGauss不保证非日志表(unlogged table)数据的安全性。
  • 【建议】临时表和非日志表的存储方式建议和基表相同。当基表为行存(列存)表时,临时表和非日志表也推荐创建为行存(列存)表,可以避免行列混合关联带来的高计算代价。
  • 【建议】索引字段的总长度不超过50字节。否则,索引大小会膨胀比较严重,带来较大的存储开销,同时索引性能也会下降。
  • 【建议】不要使用DROP…CASCADE方式删除对象,除非已经明确对象间的依赖关系,以免误删。

数据加载和卸载

  • 【建议】在insert语句中显式给出插入的字段列表。例如:复制代码INSERT INTO task(name,id,comment) VALUES ('task1','100','第100个任务');
  • 【建议】在批量数据入库之后,或者数据增量达到一定阈值后,建议对表进行analyze操作,防止统计信息不准确而导致的执行计划劣化。
  • 【建议】如果要清理表中的所有数据,建议使用truncate table方式,不要使用delete table方式。delete table方式删除性能差,且不会释放那些已经删除了的数据占用的磁盘空间。

类型转换

  • 【建议】在需要数据类型转换(不同数据类型进行比较或转换)时,使用强制类型转换,以防隐式类型转换结果与预期不符。
  • 【建议】在查询中,对常量要显式指定数据类型,不要试图依赖任何隐式的数据类型转换。
  • 【关注】若sql_compatibility参数设置为A,在导入数据时,空字符串会自动转化为NULL。如果需要保留空字符串需要sql_compatibility参数设置为C。

查询操作

  • 【建议】除ETL程序外,应该尽量避免向客户端返回大量结果集的操作。如果结果集过大,应考虑业务设计是否合理。
  • 【建议】使用事务方式执行DDL和DML操作。例如,truncate table、update table、delete table、drop table等操作,一旦执行提交就无法恢复。对于这类操作,建议使用事务进行封装,必要时可以进行回滚。
  • 【建议】在查询编写时,建议明确列出查询涉及的所有字段,不建议使用“SELECT *”这种写法。一方面基于性能考虑,尽量减少查询输出列;另一方面避免增删字段对前端业务兼容性的影响。
  • 【建议】在访问表对象时带上schema前缀,可以避免因schema切换导致访问到非预期的表。
  • 【建议】超过3张表或视图进行关联(特别是FULL JOIN)时,执行代价难以估算。建议使用WITH TABLE AS语句创建中间临时表的方式增加SQL语句的可读性。
  • 【建议】尽量避免使用笛卡尔积和FULL JOIN。这些操作会造成结果集的急剧膨胀,同时其执行性能也很低。
  • 【关注】NULL值的比较只能使用IS NULL或者IS NOT NULL的方式判断,其他任何形式的逻辑判断都返回NULL。例如:NULL<>NULL、NULL=NULL和NULL<>1返回结果都是NULL,而不是期望的布尔值。
  • 【关注】需要统计表中所有记录数时,不要使用count(col)来替代count()。count()会统计NULL值(真实行数),而count(col)不会统计。
  • 【关注】在执行count(col)时,将“值为NULL”的记录行计数为0。在执行sum(col)时,当所有记录都为NULL时,最终将返回NULL;当不全为NULL时,“值为NULL”的记录行将被计数为0。
  • 【关注】count(多个字段)时,多个字段名必须用圆括号括起来。例如,count( (col1,col2,col3) )。注意:通过多字段统计行数时,即使所选字段都为NULL,该行也被计数,效果与count(*)一致。
  • 【关注】count(distinct col)用来计算该列不重复的非NULL的数量,NULL将不被计数。
  • 【关注】count(distinct (col1,col2,…))用来统计多列的唯一值数量,当所有统计字段都为NULL时,也会被计数,同时这些记录被认为是相同的。
  • 【建议】使用连接操作符“||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推,导致查询性能严重劣化。
  • 【建议】使用下面时间相关的宏替换now函数来获取当前时间。因为now函数生成的执行计划无法下推,导致查询性能严重劣化。表 1 时间相关的宏宏名称描述示例CURRENT_DATE获取当前日期,不包含时分秒。复制代码openGauss=# select CURRENT_DATE;date-———–2018-02-02(1 row)CURRENT_TIME获取当前时间,不包含年月日。复制代码openGauss=# select CURRENT_TIME;timetz-——————-00:39:34.633938+08(1 row)CURRENT_TIMESTAMP(n)获取当前日期和时间,包含年月日时分秒。 说明: n表示存储的毫秒位数。复制代码openGauss=# select CURRENT_TIMESTAMP(6);timestamptz-——————————2018-02-02 00:39:55.231689+08(1 row)
  • 【建议】尽量避免标量子查询语句的出现。标量子查询是出现在select语句输出列表中的子查询,在下面例子中,下划线部分即为一个标量子查询语句:复制代码SELECT id, (SELECT COUNT(*) FROM films f WHERE f.did = s.id) FROM staffs_p1 s;标量子查询往往会导致查询性能急剧劣化,在应用开发过程中,应当根据业务逻辑,对标量子查询进行等价转换,将其写为表关联。
  • 【建议】在where子句中,应当对过滤条件进行排序,把选择读较小(筛选出的记录数较少)的条件排在前面。
  • 【建议】where子句中的过滤条件,尽量符合单边规则。即把字段名放在比较条件的一边,优化器在某些场景下会自动进行剪枝优化。形如col op expression,其中col为表的一个列,op为‘=’、‘>’的等比较操作符,expression为不含列名的表达式。例如,复制代码SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHERE current_timestamp(6) - time < '1 days'::interval;改写为:复制代码SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time > current_timestamp(6) - '1 days'::interval;
  • 【建议】尽量避免不必要的排序操作。排序需要耗费大量的内存及CPU,如果业务逻辑许可,可以组合使用ORDER BY和LIMIT,减小资源开销。openGauss默认按照ASC & NULL LAST进行排序。
  • 【建议】使用ORDER BY子句进行排序时,显式指定排序方式(ASC/DESC),NULL的排序方式(NULL FIRST/NULL LAST)。
  • 【建议】不要单独依赖limit子句返回特定顺序的结果集。如果部分特定结果集,可以将ORDER BY子句与Limit子句组合使用,必要时也可以使用OFFSET跳过特定结果。
  • 【建议】在保障业务逻辑准确的情况下,建议尽量使用UNION ALL来代替UNION。
  • 【建议】如果过滤条件只有OR表达式,可以将OR表达式转化为UNION ALL以提升性能。使用OR的SQL语句经常无法优化,导致执行速度慢。例如,将下面语句复制代码SELECT * FROM scdc.pub_menu WHERE (cdp= 300 AND inline=301) OR (cdp= 301 AND inline=302) OR (cdp= 302 ANDinline=301);转换为:复制代码SELECT * FROM scdc.pub_menu WHERE (cdp= 300 AND inline=301) union allSELECT * FROM scdc.pub_menu WHERE (cdp= 301 AND inline=302) union all SELECT * FROM tablename WHERE (cdp= 302 AND inline=301)
  • 【建议】当in(val1, val2, val3…)表达式中字段较多时,建议使用in (values (val1), (val2),(val3)…)语句进行替换。优化器会自动把in约束转换为非关联子查询,从而提升查询性能。
  • 【建议】在关联字段不存在NULL值的情况下,使用(not) exist代替(not) in。例如,在下面查询语句中,当T1.C1列不存在NULL值时,可以先为T1.C1字段添加NOT NULL约束,再进行如下改写。复制代码SELECT * FROM T1 WHERE T1.C1 NOT IN (SELECT T2.C2 FROM T2);

可以改写为:

复制代码  ​SELECT * FROM T1 WHERE NOT EXISTS (SELECT  * FROM T1,T2 WHERE T1.C1=T2.C2);

说明:

  • 如果不能保证T1.C1列的值为NOT NULL的情况下,就不能进行上述改写。
  • 如果T1.C1为子查询的输出,要根据业务逻辑确认其输出是否为NOT NULL。
  • 【建议】通过游标进行翻页查询,而不是使用LIMIT OFFSET语法,避免多次执行带来的资源开销。游标必须在事务中使用,执行完后务必关闭游标并提交事务。

本文转载自: https://blog.csdn.net/GaussDB/article/details/123471301
版权归原作者 Gauss松鼠会 所有, 如有侵权,请联系我们删除。

“数据库设计建议”的评论:

还没有评论