OpenGaussDB开发规范 | AyeeNotion’s Blog

参考

开发设计规范 | openGauss文档 | openGauss社区

数据库对象命名

数据库对象命名需要满足约束：

标识符非时序表长度不超过63个字节，时序表（当前特性是实验室特性，使用时请联系华为工程师提供技术支持）长度不超过53个字符。

标识符以字母或下划线开头，中间字符可以是字母、数字、下划线、$、#。

若标识符被双引号（""）包含，则可以使用合法字符的任意组合，如"123gs_column"。

标识符不区分大小写，只有被双引号包含才区分大小写。

【建议】避免使用保留或者非保留关键字命名数据库对象。

[!info] 可以使用select * from pg_get_keywords()查询openGauss的关键字，或者在关键字章节中查看。

【建议】避免使用双引号括起来的字符串来定义数据库对象名称，除非需要限制数据库对象名称的大小写。数据库对象名称大小写敏感会使定位问题难度增加。

【建议】数据库对象命名风格务必保持统一。

增量开发的业务系统或进行业务迁移的系统，建议遵守历史的命名风格。
建议使用多个单词组成，以下划线分割。
数据库对象名称建议能够望文知意，尽量避免使用自定义缩写（可以使用通用的术语缩写进行命名）。例如，在命名中可以使用具有实际业务含义的英文词汇或汉语拼音，但规则应该在数据库实例范围内保持一致。
变量名的关键是要具有描述性，即变量名称要有一定的意义，变量名要有前缀标明该变量的类型。

【建议】表对象的命名应该可以表征该表的重要特征。例如，在表对象命名时区分该表是普通表、临时表还是非日志表：

普通表名按照数据集的业务含义命名。
临时表以“tmp_+后缀”命名。
非日志表以“ul_+后缀”命名。
外表以“f_+后缀”命名。
不创建以redis_为前缀的数据库对象。
不创建以mlog_和以matviewmap_为前缀的数据库对象。

【建议】非时序表对象命名建议不要超过63字节。如果过该长度内核会对表名进行截断，从而造成和设置值不一致的现象。且在不同字符集下，可能造成字符被截断，出现预期外的字符。

Database和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兼容模式。

[!tips] create database db_oracle with ENCODING='utf-8' DBCOMPATIBILITY ='A'; create database db_mysql with ENCODING='utf-8' DBCOMPATIBILITY ='B'; create database db_td with ENCODING='utf-8' DBCOMPATIBILITY ='C'; create database db_pg with ENCODING='utf-8' DBCOMPATIBILITY ='PG';

【关注】Database的owner默认拥有该Database下所有对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。

Schema设计建议

【关注】如果该用户不具有==sysadmin==权限或者不是该Schema的owner，要访问Schema下的对象，需要同时给用户赋予Schema的usage权限和对象的相应权限。

【关注】如果要在Schema下创建对象，需要授予操作用户该Schema的create权限。

【关注】Schema的owner默认拥有该Schema下对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。

表设计

总体上讲，良好的表设计需要遵循以下原则：

==减少需要扫描的数据量==。通过分区表的剪枝机制可以大幅减少数据的扫描量。

尽量减少==随机==I/O。通过聚簇可以实现热数据的连续存储，将随机I/O转换为连续I/O，从而减少扫描的I/O代价。

选择分区方案

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

使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。

分区名称应当体现分区的数据特征。例如，关键字+区间特征。

将分区上边界的分区值定义为MAXVALUE，以防可能出现的数据溢出。

表 1 表的分区方式及使用场景

分区方式	描述
Range	表数据通过范围进行分区。
Interval	表数据通过范围进行分区，超出范围的会自动根据间隔创建新的分区。
List	表数据通过指定列按照具体值进行分区。
Hash	表数据通过Hash散列方式进行分区。

典型的分区表定义如下：

更多的表分区语法信息请参见CREATE TABLE PARTITION。

规划存储模型

openGauss支持行列混合存储。行、列存储模型各有优劣，建议根据实际情况选择。通常openGauss用于TP场景的数据库，默认使用==行存储==，仅对执行复杂查询且数据量大的AP场景时，才使用==列存储==。

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。行存储和列存储的差异请参见图1。

图 1 行存储和列存储的差异

上图中，左上为行存表，右上为行存表在硬盘上的存储方式。左下为列存表，右下为列存表在硬盘上的存储方式。

行、列存储有如下优缺点：

存储模型	优点	缺点
行存	数据被保存在一起。INSERT/UPDATE容易。	选择（Selection）时即使只涉及某几列，所有数据也都会被读取。
列存	- 查询时只有涉及到的列会被读取。<br>- 投影（Projection）很高效。<br>- 任何列都能作为索引。	- 选择完成时，被选择的列要重新组装。<br>- INSERT/UPDATE比较麻烦。

一般情况下，如果表的字段比较多（大宽表），查询中涉及到的列不多的情况下，适合列存储。如果表的字段个数比较少，查询大部分字段，那么选择行存储比较好。

存储类型	适用场景
行存	- 点查询（返回记录少，基于索引的简单查询）。<br>- 增、删、改操作较多的场景。
列存	- 统计分析类查询（关联、分组操作较多的场景）。<br>- 即席查询（查询条件不确定，行存表扫描难以使用索引）。

行存表

默认创建表的类型。数据按行进行存储，即一行数据是连续存储。适用于对数据需要经常更新的场景。

列存表

数据按列进行存储，即==一列所有数据是连续存储的==。单列查询IO小，比行存表占用更少的存储空间。适合数据批量插入、更新较少和以查询为主统计分析类的场景。列存表不适合点查询。

行存表和列存表的选择

更新频繁程度数据如果频繁更新，选择行存表。

插入频繁程度频繁的少量插入，选择行存表。一次插入大批量数据，选择列存表。

表的列数表的列数很多，选择列存表。

查询的列数如果每次查询时，只涉及了表的少数（<50%总列数）几个列，选择列存表。

压缩率列存表比行存表压缩率高。但高压缩率会消耗更多的CPU资源。

字段设计

选择数据类型

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

【建议】尽量使用高效数据类型。

选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数、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字节

约束设计

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==。

案例：使用partial cluster key

唯一约束

【关注】行存表、列存表均支持唯一约束。

【建议】从命名上明确标识唯一约束，例如，命名为“UNI+构成字段”。

主键约束

【关注】行存表、列存表均支持主键约束。

【建议】从命名上明确标识主键约束，例如，将主键约束命名为 “PK+字段名”。

外键约束

【关注】行存表支持外键约束，列存表不支持外键约束。

【建议】从命名上明确标识外键约束，例如，将外键约束命名为 “FK+字段名”。

检查约束

【关注】行存表支持检查约束，而列存表不支持。

【建议】从命名上明确标识检查约束，例如，将检查约束命名为 “CK+字段名”。

视图和关联表设计

视图设计

【建议】除非视图之间存在强依赖关系，否则==不建议视图嵌套==。

【建议】视图定义中尽量避免排序操作。

关联表设计

【建议】表之间的关联字段应该尽量少。

【建议】关联字段的数据类型应该保持一致。

【建议】关联字段在命名上，应该可以明显体现出关联关系。例如，采用同样名称来命名。

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;<br>date<br>\------------<br>2018-02-02<br>(1 row)
CURRENT_TIME	获取当前时间，不包含年月日。	openGauss=# select CURRENT_TIME;<br>timetz<br>\--------------------<br>00:39:34.633938+08<br>(1 row)
CURRENT_TIMESTAMP(n)	获取当前日期和时间，包含年月日时分秒。<br><br>说明：<br><br>n表示存储的毫秒位数。	openGauss=# select CURRENT_TIMESTAMP(6);<br>timestamptz<br>\-------------------------------<br>2018-02-02 00:39:55.231689+08<br>(1 row)

【建议】尽量==避免标量子查询语句的出现==。标量子查询是出现在select语句输出列表中的子查询，在下面例子中，下划线部分即为一个标量子查询语句：

【建议】在where子句中，应当对过滤条件进行排序，把选择读较小（筛选出的记录数较少）的条件排在前面。

【建议】where子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如col op expression，其中col为表的一个列，op为‘=’、‘>’的等比较操作符，expression为不含列名的表达式。例如，

【建议】==尽量避免不必要的排序操作==。排序需要耗费大量的内存及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语句经常无法优化，导致执行速度慢。例如，将下面语句

【建议】当in(val1, val2, val3…)表达式中字段较多时，建议使用==in (values (val1), (val2),(val3)…)==语句进行替换。优化器会自动把in约束转换为非关联子查询，从而提升查询性能。

【建议】在关联字段不存在NULL值的情况下，使用==(not) exist代替(not) in==。例如，在下面查询语句中，当T1.C1列不存在NULL值时，可以先为T1.C1字段添加NOT NULL约束，再进行如下改写。

可以改写为：

[!info]
如果不能保证T1.C1列的值为NOT NULL的情况下，就不能进行上述改写。
如果T1.C1为子查询的输出，要根据业务逻辑确认其输出是否为NOT NULL。

【建议】通过游标进行翻页查询，而不是使用LIMIT OFFSET语法，避免多次执行带来的资源开销。游标必须在事务中使用，执行完后务必关闭游标并提交事务。

开发规范

如果用户在APP的开发中，使用了连接池机制，那么需要遵循如下规范：

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

如果使用了临时表，那么在将连接归还连接池之前，必须将临时表删除。

否则，连接池里面的连接就是有状态的，会对用户后续使用连接池进行操作的正确性带来影响。

兼容性原则：

新驱动前向兼容数据库，若需使用驱动与数据库同步增加的新特性，必须升级数据库。