开发规范

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

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

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

概述

简介

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

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

• 命名规范
• 设计规范
• 语法规范
• 优化相关
• PG兼容性

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

适用范围

本规范适用于MogDB 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_1970_01_01（其中dba 为DBA专用schema，trade_record为表名，1970_01_01为备份日期）。

表空间命名

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

索引命名

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

  因此建议创建索引时，不显式给出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设计

• 频繁使用的表和索引单独存放在一个表空间，此表空间应在性能好的磁盘上创建。

• 以历史数据为主，或活跃度较低的表和索引可以存放在磁盘性能较差的表空间。

• 表和索引可以单独存放在不同的表空间。

• 表空间也可以按数据库分、按schema分或按业务功能来分。

• 每个database/schema对应一个表空间和一个相应索引表空间。

schema设计

• 在一个数据库下执行创建用户时，默认会在该数据库下创建一个同名schema。
• 不建议在默认public schema下创建数据库对象。
• 创建一个与用户名不同的schema给业务使用。

table设计

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

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

• 禁止使用unlogged/ temp/temporary关键字创建业务表。

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

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

• 对于频繁更新的astore表，建议建表时指定表的fillfactor=85，给HOT预留空间。

• 频繁更新使用的表应该单独放在存储性能好的表空间。

• 数据量超过亿级或占用磁盘超过10GB的表，建议考虑分区。

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

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

partition table设计

• 分区表的个数不建议超过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设计

• 避免与系统表的列名重复。

• 字段含义及数据类型要与程序代码设计保持一致。

• 所有字段必须要添加comment注释信息。

• 能使用数值类型，就不要使用字符类型。

• 禁止用字符类型存储日期数据。

• 时间类型字段统一使用timestamptz。

• 字段尽量要求not null，为字段提供默认值。

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

序列设计

• 禁止手动创建与表相关的序列，应指定serial/bingserial类型方式创建。

• 序列的步长建议设置为1。

• 不建议设置minvalue和maxvalue。

• 不建议设置cache，设置cache后序列号不连续。

• 禁止开启cycle。

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

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约束

检查约束

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

index设计

• 频繁DML操作的表索引数量不建议超过5个。
• create/drop index时添加concurrently参数。
• 真正创建索引前可以使用虚拟索引确定索引的有效性。
• 经常出现在关键字order by、group by、distinct后面的字段，建立索引。
• 经常用作查询选择的字段，建立索引。
• 经常用作表连接的属性上，建立索引。
• 复合索引的字段数不建议超过3个。
• 复合索引得一个字段是常用检索条件。
• 复合索引第一个字段不应存在单字段索引。
• 对数据很少被更新的表，经常只查询其中的几个字段，考虑使用索引覆盖。
• 不要在有大量相同取值的字段上建立索引。
• 建议用unique index代替unique constraints便于后续维护。
• 建议对where中带多个字段and条件的高频query，参考数据分布情况，建多个字段的复合index。
• 无用的索引以及重复索引应删除，避免对执行计划及数据库性能造成负面影响。

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元素。

• 建议：使用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操作

• 业务高峰期禁止对已存在的表执行任何DDL操作

• 所有生产DDL操作必须经过开发测试环境验证

• 维护索引时应采用concurrently的方式

• 应该使用pg_repack替换vacuum full来重建表

• 大表添加带默认值的字段时，应拆分为：添加字段、填补默认值及添加非空约束三部分，如将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操作

• 更新数据的SQL语句禁止出现where 1=1

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

• 清空表中的数据时，应使用truncate

• 对于风险性较高的操作，应该显示的开启事务，确认无误后在提交

• 事务中SQL逻辑尽量简单，操作执行完后要及时提交，避免idle in transaction状态

• 大批数据入库时应使用copy替换insert

• 数据导入前考虑先删除索引，导入完成后重建

DQL操作

• 禁止使用select *，应用具体所需字段替换

• 禁止使用where 1=1，避免全表扫描或笛卡尔积

• 检索条件值应该与字段类型保持一致，防止不走索引

• 等号左边的字段应该与索引保持一致，尤其是条件索引或函数索引

• 关注慢SQL的执行计划，如与预期不一致，尽快修改

• 使用count(*)或count(1)来统计行数，count(column)不会统计null行

• 限制join的数量，不建议超过3个

• 递归查询需要做好限制，防止无限循环

• 对于or运算，应该使用union all或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创建数据库使用PG兼容模式方法如下：

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

数据类型

数值类型

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

字符类型

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

时间类型

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

json类型

MogDB只允许使用json类型。

关键字

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

隐式转换异同