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 表的分布方式及使用场景

选择分区方案

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

• 【建议】使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。
• 【建议】分区名称应当体现分区的数据特征。例如，关键字 + 区间特征。
• 【建议】将分区上边界的分区值定义为 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 常用字符串类型

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。例如：NULLNULL、NULL=NULL 和 NULL1 返回结果都是 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 时间相关的宏

• 复制代码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://my.oschina.net/gaussdb/blog/5486884

“做程序员，圈子和学习最重要”因为有有了圈子可以让你少走弯路，扩宽人脉，扩展思路，学习他人的一些经验及学习方法！同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家！需要资料的朋友私信我扣【06】