开发规范

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

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

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

概述

简介

尽管ISO针对SQL已经发布SQL-92、SQL:1999、SQL:2006等标准，但由于不同数据库自身的特性，使得同样功能在各自产品的实现上不尽相同，这也使得相关的语法规则各有千秋。因此，在制定具体开发规范的时候，需要针对不同数据库来编写相应的规范。

本规范强调实用性、可操作性，根据开发人员在编码过程中常见的问题和易犯的错误,对代码编写的各方面均进行了详细明确的规范和约束。主要包括下列内容：

• 命名规范

• 设计规范

• 语法规范

• 优化相关

• PG兼容性

• 常用函数

除此之外,对规范的每条细则均给出具体的范例。

适用范围

本规范适用于MogDB/openGauss 1.0.0及以上版本。

命名规范

对象命名统一规范

数据库对象，如database, schema, table, column, view, index, constraint,sequence, function, trigger等命名统一标准如下：

• 建议使用小写字母、数字、下划线的组合

• 命名尽量采用富有意义英文词汇

• 建议不使用双引号即"包围，除非必须包含大写字母或空格等特殊字符

• 长度不能超过63个字符

• 不建议以PG、GS开头（避免与系统DB object混淆），不建议以数字开头

• 禁止使用保留字，保留关键字参考官方文档。

• table能包含的column数目,根据字段类型的不同，数目在 250 到 1600 之间

临时及备份对象命名

• 临时或备份的数据库对象名，如table,建议添加日期, 如dba.trade_record_2020_12_08 (其中dba 为DBA专用schema，trade_record为表名，2020_12_08为备份日期)。

表空间命名

• 数据库的用户表空间用ts_<表空间名>来表现，其中，表空间名分为：

  1. 数据空间：对于用户的缺省表空间，用default来表现。对于其他的表空间，根据寄存在表空间上的表的类别来表现。如放代码的表，用code来表现。放客户资料的表，用customer来表现。尽量用一个表空间来寄存该类的表。如果某表特殊大，可考虑单独使用一个表空间。
  2. 索引空间：在相应的数据表空间的名字前加idx_。如对用户缺省表空间的索引空间，用ts_idx_default来表现。对代码表的索引表空间，用ts_idx_code来表现。

• 表空间名禁止以"PG_"开头。

索引命名

• Index对象命名规则为: 表名_列名_idx,如student_name_idx,该索引命名方式为MogDB/openGauss数据库在创建索引时没有明确指定索引名称时的默认命名方式。

  因此建议创建索引时，不显式给出index name,使用DBMS默认值。

create unique index on departments(department_id);

CREATE INDEX

 \di

+----------+-------------------------------+--------+---------+

| Schema  | Name              | Type  | Owner  |

|----------+-------------------------------+--------+---------|

| mogdb   | departments_department_id_idx | index  | mogdb  |

+----------+-------------------------------+--------+---------+

SELECT 1

变量命名

• 命名应该使用英文单词,避免使用拼音,特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。

• 如果不涉及复杂运算,一律用number定义计数等简单应用。

分区表命名

• 分区表的表名遵循普通表的正常命名规则。

• 按时间范围分区（每月一个分区）,分区名字为PART_YYYYMM。

  举例： PART_201901、PART_201902

函数命名

• 名称应与其实际功能保持一致。导致发生某动作应以动词为前缀命令。

示例：以下命名符合规范：

func_addgroups(增加多个群组)
func_addgroup(增加一个群组)

设计规范

database设计

• 数据库优先使用兼容PG类型的方式来创建。

• 数据库编码只能用utf8。

tablespace设计

• 一般较大的表或索引单独分配一个tablespace。

• 高频率insert的对象分成一组，存在对应的tablespace中。

• 增、删、改的对象分成一组，存在对应的tablespace中。

• 表和索引分别存于不同的tablespace。

• 原则上每个schema对应一个表空间和一个相应索引表空间；每个schema下的大表对应一个单独的表空间和索引表空间。

table设计

• 设计表结构时，应该规划好，避免经常添加字段，或者修改字段类型或长度。

• 必须为表添加注释信息，表名与注释信息相匹配。

• 禁止使用unlogged关键字新建表，默认创建非压缩行表。

• 每个表在创建时候,必须指定所在的表空间,不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较繁忙的数据表,必须存放在专用表空间中。

• 作为表间连接关系的字段，数据类型必须保持严格一致，避免索引无法正常使用。

• 禁止使用VARCHAR或其他字符类型来存储日期值，如果使用，则不能在此字段上做运算，需要在数据规范中严格定义。

• 字段必须添加能够清楚表示其含义的注释，状态类字段的注释中必须明确列出各状态值的说明。

• 对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。

• 表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。

  说明：比如A表user_id字段数据类型定义为varchar，但是SQL语句查询为 where user_id=1234；

partition table设计

• MogDB/openGauss数据库支持的分区表为范围分区表。

• 分区表的个数不建议超过1000个。

• 主键或唯一索引必须要包含分区键。

• 对于数据量比较大的表,根据表数据的属性进行分区,以得到较好的性能。

• 普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

• 建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。

• 在分区表中不建议使用全局索引，因为做分区维护操作时可能会导致全局索引失效，造成难以维护。

分区表的使用

按照以下方式对范围分区表进行操作。

• 创建表空间

mogdb=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
mogdb=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
mogdb=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
mogdb=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

当结果显示为如下信息，则表示创建成功。

CREATE TABLESPACE

• 创建分区表

mogdb=# CREATE TABLE mogdb_usr.customer_address
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1

PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;

当结果显示为如下信息，则表示创建成功。

CREATE TABLE

创建列存分区表的数量建议不超过1000个。

• 插入数据

将表mogdb_usr.customer_address的数据插入到表mogdb_usr.customer_address_bak中。例如在数据库中创建了一个表mogdb_usr.customer_address的备份表mogdb_usr.customer_address_bak，现在需要将表mogdb_usr.customer_address中的数据插入到表mogdb_usr.customer_address_bak中，则可以执行如下命令。

mogdb=# CREATE TABLE mogdb_usr.customer_address_bak
(
  ca_address_sk    integer          NOT NULL  ,
  ca_address_id    character(16)       NOT NULL  ,
  ca_street_number   character(10)            ,
  ca_street_name    character varying(60)        ,
  ca_street_type    character(15)            ,
  ca_suite_number   character(10)            ,
  ca_city       character varying(60)        ,
  ca_county      character varying(30)        ,
  ca_state       character(2)             ,
  ca_zip        character(10)            ,
  ca_country      character varying(20)        ,
  ca_gmt_offset    numeric(5,2)             ,
  ca_location_type   character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
CREATE TABLE
mogdb=# INSERT INTO mogdb_usr.customer_address_bak SELECT * FROM mogdb_usr.customer_address;
INSERT 0 0

• 修改分区表行迁移属性

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak DISABLE ROW MOVEMENT;
ALTER TABLE

• 删除分区

删除分区P8。

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak DROP PARTITION P8;
ALTER TABLE

• 增加分区

增加分区P8，范围为 40000<= P8<=MAXVALUE。

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE

• 重命名分区

重命名分区P8为P_9。

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION P8 TO P_9;
ALTER TABLE

重命名分区P_9为P8。

mogdb=# ALTER TABLE mogdb_usr.customer_address_bak RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE

• 修改分区的表空间

修改分区P6的表空间为example3。

mogdb=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P6 TABLESPACE example3;
ALTER TABLE

修改分区P4的表空间为example4。

mogdb=#  ALTER TABLE mogdb_usr.customer_address_bak MOVE PARTITION P4 TABLESPACE example4;
ALTER TABLE

• 查询分区

查询分区P6。

mogdb=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION (P6);
mogdb=# SELECT * FROM mogdb_usr.customer_address_bak PARTITION FOR (35888);

• 删除分区表和表空间

mogdb=# DROP TABLE mogdb_usr.customer_address_bak;
DROP TABLE
mogdb=# DROP TABLESPACE example1;
mogdb=# DROP TABLESPACE example2;
mogdb=# DROP TABLESPACE example3;
mogdb=# DROP TABLESPACE example4;
DROP TABLESPACE

column设计

• 建议可以采用数值类型的场合，则避免采用字符类型。

• 建议可以采用varchar(N) 就避免采用char(N), 可以采用varchar(N) 就避免采用text,varchar。

• 只允许用char(N)、varchar(N)及text字符类型。

• MogDB/openGauss新建数据库默认兼容oracle,not null 约束不允许传入空字符串，空字符串默认会转换为null，兼容PG模式的数据库不会有这个问题。

• 建议使用timestamp with time zone(timestamptz),而不用timestamp without time zone。

• 建议使用NUMERIC(precision, scale)来存储货币金额和其它要求精确计算的数值, 而不建议使用real, double precision。

序列设计

• 禁止手动添加与表相关的序列。

• 建表时指定列的serial或bigserial类型的方式来创建序列。

• 序列应与代码中变量定义类型及范围一致，防止无法插入数据。

constraint设计

主键约束

• 每个table必须包含主键。

• 建议不要用有业务含义的名称作为主键,比如身份证或者国家名称,尽管其是unique的。

• 建议主键的一步到位的写法:id serial primary key 或id bigserial primary key。

• 建议内容系统中size较大的table主键的等效写法如下,便于后续维护。

create table test(id serial not null );
create unique index CONCURRENTLY ON test (id);

唯一约束

除主键外，需存在唯一性约束的，可通过创建以“uk_”为前缀的唯一索引实现。

外键约束

• 存在外键关系的表上尽量创建外键约束。

• 使用外键时，一定要设置fk的action，例如cascade，set null，set default。

非空列

• 所有非空列须在建表之初明确标识“NOT NULL”,上线之后，不再变更。同时需注意 NULL 与空字符‘’的查询结果差别：null 值会转换为 NULL, 而空值无字符显示。

检查约束

• 对于字段有检查性约束,一般要求指定check规则。例如：性别、状态等字段。

index设计

• MogDB/openGauss 提供的index类型: 行存表支持的索引类型：btree（行存表缺省值）、gin、gist。列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。

• 建议create 或 drop index 时,加 CONCURRENTLY参数,这是个好习惯，达到与写入数据并发的效果，列存表、分区表和临时表不支持CONCURRENTLY方式创建索引。

• 建议对于频繁update, delete的包含于index 定义中的column的table, 用create index CONCURRENTLY , drop index CONCURRENTLY的方式进行维护其对应index。

• 建议用unique index 代替unique constraints便于后续维护。

• 建议对where 中带多个字段and条件的高频 query，参考数据分布情况，建多个字段的联合index。

• 每个表的index数量不能超过5个。

• 复合索引的建立需要进行仔细分析：

  • 正确选择复合索引中的第一个字段,一般是选择性较好的且在where子句中常用的字段上；
  • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是,则可以建立复合索引；否则考虑单字段索引；
  • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引；
  • 如果既有单字段索引，又有以这个字段为首列的复合索引,一般可考虑删除单字段索引；
  • 复合索引第一个字段一般不使用时间字段，因为时间字段多用于范围扫描，而前面的字段使用范围扫描后，后续字段无法用于索引过滤。
  • 复合索引字段个数不能超过4个。

• 频繁DML（写次数明显超过读次数）的表,不要建立太多的索引。

• 无用的索引以及重复索引应删除，避免对执行计划及数据库性能造成负面影响。

view设计

• 尽量使用简单视图，尽可能少使用复杂视图。

  简单视图定义：数据来自单个表,且无分组（DISTINCT/GROUP BY）、无函数。

  复杂视图定义：数据来自多个表,或有分组,有函数，表的个数不能超过3个。

• 尽量不要使用嵌套视图，如果必须使用，不能超过2层嵌套。

function设计

• 函数必须检索数据库表记录或数据库其他对象,甚至修改（执行Insert、Delete、Update、Drop、Create等操作）数据库信息。

• 如果某项功能不需要和数据库打交道,则不得通过数据库函数的方式实现。

• 在函数中避免采用DML或DDL语句。

语法规范

关于NULL

• 说明：NULL 的判断：IS NULL ，IS NOT NULL。

• 说明：注意boolean 类型取值 true，false，NULL。

• 说明：小心NOT IN 集合中带有NULL元素。

mydb=# SELECT * FROM (VALUES(1),(2)) v(a) ;  a

\---

 1

 2

(2 rows)

mydb=# select 1 NOT IN (1,NULL);

?column?

\---------

f

(1 row)

mydb=# select 2 NOT IN (1,NULL);

?column?

\---------

(1 row)

mydb=# SELECT * FROM (VALUES(1),(2)) v(a) WHERE a NOT IN (1, NULL);  a

\---

(0 rows)

• 建议：使用count(1) 或count(*) 来统计行数，而不建议使用count(col) 来统计行数，因为NULL值不会计入。

• 规则：count(多列列名)时，多列列名必须使用括号，例如count( (col1,col2,col3) )。

• 注意：多列的count，即使所有列都为NULL，该行也被计数，所以效果与count(*) 一致。

• 注意：count(distinct col) 计算某列的非NULL不重复数量，NULL不被计数

count(distinct (col1,col2,...) ) 计算多列的唯一值时，NULL会被计数，同时NULL与NULL会被认为是相同的。

• 注意：NULL 的count与sum

select count(1), count(a), sum(a)  from (SELECT * FROM (VALUES (NULL), (2) ) v(a)) as foo where a is NULL;

count | count | sum

-------+-------+-----

   1 |   0 |

(1 row)

• 判断两个值是否相同（将NULL视为相同的值）

select null is distinct from null;

?column?

\---------

f

(1 row)

select null is distinct from 1;

?column?

\---------

t

(1 row)

select null is not distinct from null;

?column?

\---------

t

(1 row)

select null is not distinct from 1;

?column?

\---------

f

(1 row)

关于索引失效

• 在书写SQL语句时经常会在查询中使用函数及表达式，建议尽量不要在条件列上使用函数及表达式。在条件列上使用函数或者表达式的时候会导致使用不上该条件列上的索引，从而影响SQL的查询效率。尽量把函数或者表达式用在条件值上，避免使用在条件列上。示例：

  select name from tab where id+100>1000;

  可以改写为如下形式：

  select name from tab where id>1000-100;

• 查询语句中尽量不要使用左模糊查询。示例：

  select id from tab where name like '%ly';

• 查询中尽量不要使用负向查询，如not in/like，示例：

  select id from tab where name not in ('ly','ty');

确保使用到所有变量和参数

• 声明变量也会产生一定的系统开销,并会显得代码不够严谨,在编译时未使用的变量会有告警,需修改以确保没有任何告警。

Query操作

DDL操作

• DB object 尤其是COLUMN 加COMMENT，便于后续新人了解业务及维护

• 发给DBA 发布的DDL，附带常用SQL: SELECT, INSERT ,DELETE, UPDATE，便于DBA给出create index CONCURRENTLY等其他优化建议

• 向大size的table中add column时，将 alter table t add column col datatype not null default xxx；分解为如下，避免填充default值导致的过长时间锁表

alter table t add column col datatype ；

alter table t alter column col set default xxx；

update table t  set column= DEFAULT where id in ( select id from t where column is null limit

1000 ) ; \watch 3

alter table t alter column col set not null；

DML操作

• update 时做 <> 判断,比如update table_a set column_b = c where column_b <> c，禁止出现where 1=1

• 单条DML语句操作数据量不超过10万

• 建议清空表时,使用truncate,不建议使用delete

DQL操作

• 非必须时禁止使用select *，只取所需字段，以减少包括不限于网络带宽消耗，避免表结构变更对程序的影响（比如某些prepare query）

• 对报表类的或生成基础数据的查询，使用物化视图(MATERIALIZED VIEW)定期固化数据快照，避免对多表（尤其多写频繁的表）重复跑相同的查询

• 复杂的统计查询可以尝试窗口函数 Window Functions

• 避免关联字段数据类型不一致，禁止使用隐式类型转换

• 不同字段的or语句使用union代替

数据导入

• 建议大批量的数据入库时，使用copy，不建议使用insert，以提高写入速度

• 导入数据前需要先删除相关索引，导入完成后重建，提高数据导入速度

事务操作

• 事务中的sql逻辑尽可能的简单，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock 、deadlock的产生，事务执行完及时提交

• 执行CRAETE、DROP、ALTER等DDL操作, 尤其多条，不要显式的开transaction, 因为加lock的mode非常高,极易产生deadlock

• state 为 idle in transaction 的连接，如果出现在Master, 会无谓的lock住相应的资源, 可导致后续产生lock,甚至deadlock; 出现在Slave,可导致卡住主从同步

其他

• 建议运行在SSD上的实例, random_page_cost (默认值为4) 设置为1.0~2.0之间, 使查询规划器更倾向于使用索引扫描

• 建议在需要使用explain analyze 查看实际真正执行计划与时间时，如果是写入 query，强烈建议先开启事务， 然后回滚。

• 对于频繁更新，膨胀率较高的表，应找窗口期执行表重组，降低高水位

PostgreSQL兼容性

建库规范

MogDB/openGauss创建数据库使用PG兼容模式如下：

create database dbnam DBCOMPATIBILITY='PG' encoding=’utf8’;

数据类型

数值类型

在开发使用中，MogDB/openGauss只允许使用smallint、integer、bigint、numeric[(p[,s])]、serial、bigserial 这几种类型。

字符类型

在开发使用中，MogDB/openGauss只允许使用char(n)、varchar(n)、text字符类型。

时间类型

在开发使用中，MogDB/openGauss只允许使用timestamp[(p )][with time zone]、date日期类型。

json类型

MogDB/openGauss只允许使用json 类型。

关键字

PostgreSQL与openGauss数据库中关键字差异化说明，保留是指数据库保留关键字，不允许自定义使用；非保留或空是指可以自定义使用，MogDB/openGauss保留字详情，请参考附件。

隐式转换对应表