循序渐进丨基于 MogDB 制定一套数据库规范

关于MogDB

MogDB 是云和恩墨基于 openGauss 内核进行增强提升，推出的一款安稳易用的企业级关系型数据库。该产品具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力，能够满足从核心交易到复杂计算的企业级业务需求。（点击文末“阅读原文”或复制官网链接：mogdb.io至浏览器了解MogDB更多内容。）

在实际工作场景中，为了确保团队内以及团队之间可以快速顺利地进行工作交接，在最小的成本下理解已经存在的数据库对象，需要制定一些数据库规范，并应关注以下特点：

• 规范需要持续维护，不断改善来适应自身业务的发展。

• 标准的规范可以减少出错的概率，保证程序安全稳定运行。

• 可以快速定位问题原因并有效解决。

• 标准的规范可以提升开发效率，也可自动化管理。

本文就基于 MogDB 制定一套数据库规范，算是使用数据库时应该注意的一些事项，也给使用其他数据库的朋友一些参考。

一、数据库范式

为了更好地制定数据库规范，我们需要知道如何去设计数据模型。关系数据库的数据模型设计有六种范式（Normal Form，简称NF），各范式呈递次规范，越高的范式数据库冗余越小，一般情况下数据库满足第三范式就可以了。

1. 第一范式（1NF）

每一域（列）都是不可分割的原子数据项，不能是集合，数组，记录等非原子数据项，简而言之，第一范式就是无重复的域。

2. 第二范式（2NF）

在满足1NF的基础上，非码属性必须完全依赖于候选码。简而言之，一个表中必须有唯一键。

• 候选码：某一属性组的值能唯一地标识一个元组，则称该属性组为候选码。若有多个候选码，则选定其中一个为主码。

• 主属性：所有候选码的属性称为主属性，不包含在任何候选码中的属性称为非主属性或非码属性。

• 函数依赖：属性集关系模式R(U)中有x,y两个子集，若由x决定y，则说y函数依赖x， 如x(工号)-> y(评分)。

• 部分函数依赖：若x决定y，x的子集x1也可以决定y，则说y部分函数依赖x，如x(工号，姓名) -> y(评分)，x1(工号) -> y(评分)。

• 完全函数依赖：若x决定y，x的任何子集都不能决定y，则说y全部函数依赖x，如x(工号，绩效) -> y(评分)。

• 传递函数依赖：若x决定y，y决定z，但是z不属于y，y也不决定x，则说z传递依赖x，如x(工号) -> y(部门) ->z(部门信息)。

  

3. 第三范式（3NF）

在满足2NF的基础上，非主属性之间没有依赖关系，即消除2NF中的传递函数依赖。

巴斯-科德范式（BCNF）

在满足3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖），巴斯-科德范式被认为没有新的设计规范加入，只是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说它是对第三范式的修正，使数据库冗余度更小，这也是BCNF不被称为第四范式的原因。

4. 第四范式（4NF）

在满足BCNF的基础上，属性之间不允许有非平凡且非函数依赖的多值依赖，即同一个表中没有多对多关系。

• 平凡多值依赖：全集U=K+A，一个K可以对应于多个A，即K→→A，此时整个表就是一组一对多关系。

• 非平凡多值依赖：全集U=K+A+B，一个K可以对应于多个A，也可以对应于多个B，A与B互相独立，即K→→A，K→→B。整个表有多组一对多关系，且有：“一”部分是相同的属性集合，“多”部分是互相独立的属性集合。
  

  

5. 第五范式（5NF，又称完美范式）

在满足4NF的基础上，消除不是由候选码所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选码所隐含，则称此关系模式符合第五范式。

第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。

函数依赖是多值依赖的一种特殊的情况，而多值依赖实际上是连接依赖的一种特殊情况。

二、数据库命名规范

数据库对象（database、schema、table、column、view、index、function、 trigger等）命名标准：

• 长度不超过63个字符

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

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

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

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

• 禁止使用保留字，保留关键字参考官方文档或通过 pg_get_keywords() 函数查看

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

• 分区表命令规则与普通表保持一致

• 临时或备份的数据库对象名，如 table，建议添加日期, 如 dba.trade_record_2100_01_07

• 索引显示命名规则为：表名_列名_idx，与缺省默认给出的索引名保持一致

• 数据库的用户表空间用 ts_<表空间名> 来表现 ,数据表空间和索引表空间可以分开，如 ts_data_xxx 和 ts_idx_xxx

三、对象设计规范

1. Tablespace 设计

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

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

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

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

• 也可以用默认表空间 pg_default，即 base 目录

2. Database 设计

• 建议以业务功能来命名数据库，简单直观

• 推荐使用兼容PG模式创建数据库

• 数据库编码建议使用 UTF8

3. Schema 设计

• 在一个数据库下执行创建用户时，默认会在该数据库下创建一个同名 schema

• 不建议在默认 public schema 下创建数据库对象

• 创建一个与用户名不同的 schema 给业务使用

4. Table 设计

• 规划好表结构设计，避免添加字段、修改字段类型或长度

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

• 必须为表添加 comment 注释信息

• 表间关联字段数据类型要保持一致，避免索引无法使用

• 对于频繁更新的astore表，需要指定填充因子 fillfactor=85，给 HOT 预留空间

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

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

5. Cloumn 设计

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

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

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

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

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

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

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

6. Sequence 设计

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

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

• 不建议设置 minvalue 和 maxvalue

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

• 禁止开启 cycle

7. Index 设计

• 频繁 DML 操作的表索引数量不建议超过5个

• create/drop index 时添加 concurrently 参数

• 真正创建索引前可以使用虚拟索引确定索引的有效性

• 经常出现在关键字 order by、group by、distinct 后面的字段，建立索引

• 经常用作查询选择的字段，建立索引

• 经常用作表连接的属性上，建立索引

• 复合索引的字段数不建议超过3个

• 复合索引得一个字段是常用检索条件

• 复合索引第一个字段不应存在单字段索引

• 用 unique index 替换 unique constraints

• 对数据很少被更新的表，经常只查询其中的几个字段，考虑使用索引覆盖

• 不要在有大量相同取值的字段上建立索引

8. Partition Table 设计

• 范围分区表数量不建议超过1000个

• 分区表可以按使用频度选择表空间

• 分区键必须要有索引，不建议使用全局索引

• 定期清理历史分区表

9. View 设计

• 尽量使用简单视图，减少复杂视图（数据来自多个表，表的数量不建议超过3个）

• 避免嵌套视图，如必须使用，不能嵌套2层

10. Constraint 设计

• 每张表必须要有主键

• 不要用有业务含义的字段做主键，尽管其唯一

• 建议使用 id serial/bigserial primary key 的方式创建主键

• 大表添加主键可以使用 unique + not null 的方式替代

• 建议使用唯一索引替换唯一约束

• 所有非空列必须要添加 not null 约束

• 存在外键关系的表尽量添加外键约束

• 性能要求高而安全性自己控制的系统不建议使用外键

四、数据库操作规范

这里数据库操作指：DDL、DML、DQL

1. DDL 操作

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

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

• 大表添加带默认值的字段时，应拆分为：添加字段、填补默认值及添加非空约束三部分

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

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

2. DML 操作

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

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

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

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

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

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

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

3. DQL 操作

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

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

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

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

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

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

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

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

• 对于 or 运算，应该使用 union all 或 union 替换

关于作者

高云龙

云和恩墨服务总监，PostgreSQL专家，多年PostgreSQL及相关生态数据库运维经验，曾任职去哪儿网及某金融公司的核心数据库架构建设，目前主要深耕openGauss和MogDB数据库。

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，以“数据驱动，成就未来”为使命，是智能的数据技术提供商。我们致力于将数据技术带给每个行业、每个组织，构建数据驱动的智能未来。

云和恩墨在数据承载（分布式存储、数据持续保护）、管理（数据库软件、数据库云管平台、数据技术服务）、加工（应用开发质量管控、数据模型管控、数字化转型咨询）和应用（数据服务化管理平台、数据智能、隐私计算数据联邦平台）等领域为各个组织提供可信赖的产品、服务和解决方案，围绕用户需求，持续为客户创造价值，激发数据潜能，为成就未来敏捷高效的数字世界而不懈努力。

目前，云和恩墨的700多名员工分布在国内外的34个地区，已累计直接服务8大关键行业（金融、通信、能源、政务、制造、交通、医疗、商贸）的1,000多个组织，50,000多套业务系统，300,000多名行业从业者。