开发规范
数据库设计
对象命名规范
约束
数据库对象命名需要满足如下约束：
长度以不超过15个字符为宜(避免超过 20)。
以字母或下划线开头，中间字符可以是字母、数字、下划线。
建议
- 避免使用保留或者非保留关键字命名数据库对象。可以使用select * from pg_get_keywords()查询Vastbase G100的关键字。
- 避免使用双引号括起来的字符串来定义数据库对象名称，除非需要限制数据库对象名称的大小写。数据库对象名称大小写敏感会使定位问题难度增加。
- 数据库对象命名风格务必保持统一。
- 增量开发的业务系统或进行业务迁移的系统，建议遵守历史的命名风格。
- 建议使用多个单词组成，以下划线分割。
- 数据库对象名称建议能够望文知意，尽量避免使用自定义缩写（可以使用通用的术语缩写进行命名）。例如，在命名中可以使用具有实际业务含义的英文词汇或汉语拼音，但规则应该在数据库实例范围内保持一致。
- 变量名的关键是要具有描述性，即变量名称要有一定的意义，变量名要有前缀标明该变量的类型。
表对象的命名应该可以表征该表的重要特征。例如，在表对象命名时区分该表是普通表、临时表还是非日志表：
- 普通表名按照数据集的业务含义命名。
- 临时表以“tmp_+后缀”命名。
- 非日志表以“ul_+后缀”命名。
- 外表以“f_+后缀”命名。
- 不创建以redis_为前缀的数据库对象。
- 不创建以mlog_和以matviewmap_为前缀的数据库对象。
表对象的命名长度以不超过 15 个字符为宜(避免超过 20)。如果超过63字节，内核会对表名进行截断，从而造成和设置值不一致的现象。且在不同字符集下，可能造成字符被截断，出现预期外的字符。
创建数据库
规则
在实际业务中，根据需要创建新的Database，不建议直接使用数据库实例默认的vastbase数据库。
建议
- 一个数据库实例内，用户自定义的Database数量建议不超过3个。
- 为了适应全球化的需求，使数据库编码能够存储与表示绝大多数的字符，建议创建Database的时候使用UTF-8编码。
- 创建Database时，需要重点关注字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)两个配置项。Vastbase G100支持A、B、C和PG四种兼容模式，分别表示兼容O语法、MY语法、TD语法和POSTGRES语法，不同兼容模式下的语法行为存在一定差异，默认为A兼容模式。
Database的owner默认拥有该Database下所有对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。
创建用户
通过CREATE USER创建的用户，默认具有LOGIN权限。并且建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA。
系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。
用户名称取值范围：字符串，要符合标识符的命名规范。且最大长度不超过20个字符。
用户登录密码规则如下：
取值范围：字符串。
密码默认不少于8个字符。
不能与用户名及用户名倒序相同。
至少包含大写字母（A-Z），小写字母（a-z），数字（0-9），非字母数字字符（限定为~!@#$%^&*()-_=+|[{}];:,<.>/?）四类字符中的三类字符。
密码也可以是符合格式要求的密文字符串，这种情况主要用于用户数据导入场景，不推荐用户直接使用。如果直接使用密文密码，用户需要知道密文密码对应的明文，并且保证明文密码复杂度，数据库不会校验密文密码复杂度，直接使用密文密码的安全性由用户保证。
创建用户时，应当使用双引号或单引号将用户密码括起来。
权限相关
数据库对象创建后，进行对象创建的用户就是该对象的所有者。数据库安装后的默认情况下，未开启三权分立，数据库系统管理员具有与对象所有者相同的权限。也就是说对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和销毁对象，以及通过GRANT将对象的权限授予其他用户。
为使其他用户能够使用对象，必须向用户或包含该用户的角色授予必要的权限。Vastbase G100支持以下的权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE、USAGE、ALTER、DROP、COMMENT、INDEX和VACUUM。不同的权限与不同的对象类型关联。
要撤消已经授予的权限，可以使用REVOKE。对象所有者的权限（例如ALTER、 DROP、COMMENT、INDEX、VACUUM、GRANT和REVOKE）是隐式拥有的，即只要拥有对象就可以执行对象所有者的这些隐式权限。对象所有者可以撤消自己的普通权限，例如，使表对自己以及其他人只读，系统管理员用户除外。
系统表和系统视图要么只对系统管理员可见，要么对所有用户可见。标识了需要系统管理员权限的系统表和视图只有系统管理员可以查询。
示例
创建用户kfst，并对其授予schema为mytest下表t的访问权限：
create user kfst identified by ‘Kfst@2022’;
grant usage on mytest to kfst;
grant select on mytest.t to kfst
创建表
总体上讲，良好的表设计需要遵守的规则：
- 规划好表结构设计，避免添加字段、修改字段类型或长度，表字段命名不超过63个字符。
- 在设计时尽量包含两个日期字段:created(创建日期)，modified(修改日期)且非空,对表的记录进行更新的时候，必须包含对modified字段的更新。
- 尽可能使用简单数据类型，不要使用类似数组或者嵌套表这种复杂类型。
- 必须要有主键，且尽量不要使用有实际意义的字段做主键。
- 需要join的字段，数据类型保持绝对一致（否则无法使用索引）。
- 当字段的类型为枚举型或布尔型时，建议使用char(1)类型。
- 频繁更新使用的表应该单独放在存储性能好的表空间。
- 数据量超过亿级或占用磁盘超过10GB的表，建议考虑分区。
- 必须为表添加comment注释信息。
同一个模块的表尽可能使用相同的前缀，表名尽可能表达含义，临时或备份的数据库对象名，如table,建议添加日期。
减少需要扫描的数据量。通过分区表的剪枝机制可以大幅减少数据的扫描量。
尽量减少随机I/O。通过聚簇/局部聚簇可以实现热数据的连续存储，将随机I/O转换为连续I/O，从而减少扫描的I/O代价。
存储方案的选择
表的存储类型是表定义设计的第一步，客户业务类型是决定表的存储类型的主要因素，表存储类型的选择依据请参考下表：
| 存储类型 | 适用场景 |
|---|---|
| 行存 | |
| 列存 |
分区方案的选择
当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：
- 使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。
- 分区名称应当体现分区的数据特征。例如，关键字+区间特征。
- 将分区上边界的分区值定义为MAXVALUE，以防止可能出现的数据溢出。
表的分区方式及使用场景如下：
| 分区方式 | 描述 |
|---|---|
| range | 通过范围进行分区。 |
| list | 通过指定列，按照具体的值进行分区。 |
| interval | 通过范围进行分区，超出范围的会自动根据间隔创建新的分区。 |
| hash | 通过hash散列的方式进行分区。 |
典型的分区表定义如下：
创建range分区表。
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) );创建Interval分区表，初始两个分区，插入分区范围外的数据会自动新增分区。
CREATE TABLE sales (prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) INTERVAL('1 day') ( PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00'), PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00') );创建list分区表。
CREATE TABLE test_list (col1 int, col2 int) partition by list(col1) ( partition p1 values (2000), partition p2 values (3000), partition p3 values (4000), partition p4 values (5000) );创建Hash分区表。
CREATE TABLE test_hash (col1 int, col2 int) partition by hash(col1) ( partition p1, partition p2 );
创建索引
创建索引遵循如下规则：
- 频繁DML操作的表索引数量不建议超过5个。
- 复合索引的字段数不建议超过3个。
- 复合索引得一个字段是常用检索条件。
- 复合索引第一个字段不应存在单字段索引。
- 真正创建索引前可以使用虚拟索引确定索引的有效性。
- 分区表索引分为LOCAL索引与GLOBAL索引，一个LOCAL索引对应一个具体分区，而GLOBAL索引则对应整个分区表。
一般来说，应该在这些列上创建索引：
- 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
- 经常用作表连接的字段上，建立索引。
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
- 为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。
不应该创建索引的的这些列具有下列特点：
- 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
- 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
- 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
SQL开发规范
DDL规范
- 在代码中不允许出现任何 DDL 语句。
- DDL语句一律由开发DBA 编写并交由运维DBA统一执行，例：CREATE、ALTER、DROP。
- 所有生产DDL操作必须经过开发测试环境验证。
- 在Vastbase G100中，建议DDL（建表、comments等）操作统一执行，在批处理作业中尽量避免DDL操作。
- 避免大量并发事务对性能的影响。
- 索引字段的总长度不超过50字节。否则，索引大小会膨胀比较严重，带来较大的存储开销，同时索引性能也会下降。
DML规范
更新数据的SQL语句禁止出现where 1=1。
清空表中的数据时，应使用truncate。
对于风险性较高的操作，应该显示的开启事务，确认无误后在提交，例：
begin; update ... commit; end;事务中SQL逻辑尽量简单，操作执行完后要及时提交，避免idle in transaction状态。
DQL规范
1、不要使用select *这样的代码，指定需要的字段名。
错误写法：
select * from test a;正确写法：
select a.id,a.name from test a;
2、写SQL的时侯一定要使用绑定变量。对于极少数情况下不使用绑定变量提高性能，使用之前一定要和DBA沟通。
3、写SQL的时候一定要给每个字段指定表名做前缀。一来带来性能的提升，二来可以避免因schema切换导致访问到非预期的表。
错误写法：
select id,name from test a;正确写法：
select a.id,a.name from test a;
4、避免在where子句中对字段施加函数。
不允许在字段上添加函数或者表达式，这样将导致索引失效。
错误写法：
select * from iw_account_log where to_char(trans_dt, 'yyyy-mm-dd') = '2007-04-04'; select qty from product where p_id + 12 = 168;正确写法：
select * from iw_account_log where trans_dt>= to_date('2007-04-04', 'yyyy-mm-dd') and trans_dt<to_date('2007-04-05', 'yyyy-mm-dd'); select qty from product where p_id = 168 - 12;
如果是业务要求的除外，但需要在编写时咨询DBA。
当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数
错误的写法（a.id是number类型，而b.operator_number 是char类型）:
select count from adm_user a, adm_action_log b where a.id = b.operator_number and a.username = '小钗';正确写法：
select count from adm_usera, adm_action_logb where to_char(a.id) = b.operator_number and a.username = '小钗'; select count from adm_usera, adm_action_logb where a.id = to_number(b.operator_number) and a.username = '小钗';
上面两种写法哪个正确？遇到这种情况时必须咨询DBA。例如统信系统在Vastbase G100调用存储过程和单独调用SQL执行效率有差别。
使用存储过程结果。
circ=> select lf_circchnl_polno_num(86110020180120000820); lf_circchnl_polno_num ----------------------- 03 (1 row) Time: 3000.635 ms单独调用SQL结果：
Time: 3.795 ms
原因：数据类型有问题，函数的参数用的是character，数据库的字段是varchar(60)，类型不匹配，所以没法走索引。
5、限制join的数量，不建议超过3个。
超过3张表或视图进行关联（特别是FULL JOIN）时，执行代价难以估算。建议使用WITH AS语句创建中间临时表的方式增加SQL语句的可读性。例：
WITH t1 AS (
select ...
), t2 AS (
select ...
)
SELECT * FROM t1,t2 ...;
6、需要统计表中所有记录数时，不要使用count(col)来替代count()。count()会统计NULL值（真实行数），而count(col)不会统计，可能造成数据失真。
7、全模糊查询无法使用INDEX，应当尽可能避免。例如：
select * from table where name like '%jacky%';
8、严格要求使用正确类型的变量，杜绝数据库做隐式类型转换的情况推荐在sqlmap的变量中指定变量的数据类型，例如：
select * from iw_user where iw_user_id = #userid:VARCHAR#;
对于变量数据类型错误导致SQL严重性能问题的，按严重的编码错误Bug 处理。
应用程序开发规范
如果用户在APP的开发中，使用了连接池机制，那么需要遵循如下规范：
如果在连接中设置了GUC参数，那么在将连接归还连接池之前，必须使用如下命令，将连接的状态清空。
SET SESSION AUTHORIZATION DEFAULT; RESET ALL;如果使用了临时表，那么在将连接归还连接池之前，必须将临时表删除。否则，连接池里面的连接就是有状态的，会对用户后续使用连接池进行操作的正确性带来影响。
