1. 数据库设计的原则

1.1. 表设计原则

规范化与反规范化

规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度，而且范式越高性能就会越差。出于性能和方便管理的考虑，原则上表设计应满足第三范式。有时为了提高某些查询或应用的性能而可以破坏规范规则，即反规范化。

数据应当按两种类别进行组织：频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据，内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。比较复杂的方法是将规范化的表作为逻辑数据库设计的基础，然后再根据整个应用系统的需要，物理地非规范化数据。

尽量做到冷热数据分离，减小表的宽度。对于影响核心业务的表，如果超过30列，需要将列按使用频率和重要性分为两个表（如果表更大，可以酌情考虑再细分），一个表存放高使用率的数据，一个存放低使用率的数据。
数据表分类说明

根据应用的实际需要和特点，可以将数据表进行如下分类：

基本数据表：描述业务实体的基本信息。例如，人员基本信息、单位基本信息等
标准编码表：描述属性的列表值。例如，职称、民族、状态等
业务数据表：记录业务发生的过程和结果。例如，人员调动登记、变更通知单等
系统信息表：存放与系统操作、业务控制有关的参数。例如，用户信息、权限、用户配置信息等
统计数据表：存放业务数据统计值。例如，通知单统计、人员类别统计等
临时处理表：存放业务处理过程中的中间结果
其他类型表：存放应用层的日志、消息记录等

1.2. 字段设计原则

一般来说，应该使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
选择更简单的数据类型。例如，比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。
一个表中的字段不要太多，不要超过80个。
数据库中所有布尔型中数值0表示为假；数值1表示为真。
字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值有两种情况，
如果是状态字段，默认值可以设置为极值，例如99999，或者-99999，字段备注中需要标注清楚极值的含义。
如果是金额、数量、面积等，默认值可以设置为0，避免汇总合计影响数据准确性。
如果存储空间紧张，考虑使用int unsigned存储IPV4。
一些常见字段的命名必须统一：
[DataStatus] [smallint] 数据状态，默认为1，0:为删除
[STAT] [smallint] 单据状态
[AddTime] [datetime] 添加时间
[AddUser] [varchar](20) 添加人
[AddUserName] [varchar](20) 添加人姓名
[AddIP] [varchar](20) 添加人IP
[UpdateTime] [datetime] 修改时间
[UpdateUser] [varchar](20) 修改人
[UpdateUserName] [varchar](20) 修改人姓名
[UpdateIP] [varchar](20) 修改人IP
小数类型使用decimal，禁止使用float和double。
如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：不是频繁修改的字段；不是varchar超长字段，更不能是text字段。
禁止使用二进制字段类型text, ntext, Image， varchar(max),nvarchar(max)也禁止使用，只能使用varchar(8000),nvarchar(8000)代替。
所有存储相同数据的列名和列类型必须一致（关联列）。
禁止在表中建立预留字段。
保障数据可追溯
对于状态数据，应当设计相应状态的字段来保存该数据的最后状态，同时记录下来该数据的初始创建人，时间以及该数据的最后修改人和修改时间。
针对需要跟踪每次修改的数据，需要在数据发生变化的时候记录一张日志表，用于记录该数据发生变化的全生命周期。针对只需要关注关键字段变化的情况，则日志表中只需要记录关键字段变化即可，但操作人，操作类型，时间应当准确记录，日志表数据一旦生成不允许进行修改。
数据表数据只允许逻辑删除，不允许物理删除，逻辑删除状态统一记录在DataStatus字段当中。

1.3. 键设计原则

禁止使用外键与级联，一切外键概念必须在应用层解决。
所有的键都必须唯一，用于唯一标识表的数据。
尽量避免使用复合键。
外键（应用层）总是关联唯一的键字段。
尽量使用系统生成（如自增列产生）的主键。
一个表中组合主键的字段个数尽可能少，最多不要超过5个字段。

1.4. 索引设计原则

如果一列出现在表达式或函数中，不会使用该列上的索引。
要索引外键（应用层）。
HASH索引只适用于相等比较。
禁止使用大型字段作为索引（建议超过100个字符的字段就不要建索引）。
禁止对常用的小型表创建索引（数据行数不超过1000行的表）。
区分度最高的列放在联合索引的最左侧，尽量把字段长度小的列放在联合索引的最左侧，使用最频繁的列放在联合索引的最左侧。
限制每张表上的索引数量，建议单张表索引不超过5个。
禁止给表中的每一列都建立单独的索引。
常见索引列建议：（在哪些列上建立索引）：
Select,update,delete语句的where从句中的列
包含在order by,group by,distinct中的字段（联合索引）
多表join的关联列
禁止建立冗余索引和重复索引。

1.5. 禁用项

禁止在程序中使用游标
禁止使用外键约束

2. 完整性设计

采用数据库系统实现数据的完整性。这不但包括通过标准化实现的完整性而且还包括数据的功能性。

2.1. 主键约束

每个表要求必须有主健，主健字段必须满足非空属性和唯一性要求。
主键设计必须采用不更新列。
尽量使用自增数字列作为主键。
尽量使用单字段主键。
禁止使用UUID，MD5，HASH，字符串列作为主键（不能保证递增），选择使用自增ID值。

2.2. 外键约束

禁止使用外键与级联。

2.3. NULL值

由于NULL值在参加任何运算时，结果均为NULL，所以必须利用ISNULL()函数把可能为NULL值得字段或变量转换为非NULL的默认值。

新加的表，所有字段禁止NULL。
旧表新加字段，需要允许为NULL，避免全表数据更新，长期持锁导致阻塞。

2.4. CHECK条件

对于字段有检查性约束，必须指定CHECK规则。

2.5. 触发器

尽量避免使用触发器，因为会带来维护和性能的问题，对必须使用触发器的使用原则需要列明清楚，并写在触发器的代码中。

2.6. 视图

为了在数据库和应用程序之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问表。这样做还在处理数据库变更时提供了更多的自由。视图是虚拟的数据库表，在使用时要遵循以下原则：

为简化查询，将复杂的检索或子查询通过视图实现。
提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员。
视图中禁止嵌套视图，视图只允许使用物理表进行处理。
由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的表，不宜使用视图，可以采用索引视图代替。
除特殊需要，避免类似SELECT * FROM [TableName] 而没有检索条件的视图。
视图中尽量避免出现数据排序的SQL语句。

3. 命名规范

3.1. 总则

所有命名采用26个英文大小写字母和0－9这十个自然数，加上下划线_组成。不能出现其他字符（注释除外）。
对象名尽量短，长度不能超过30个字符。
实际名字尽量描述实体的内容，由英文单词、单词组合或单词缩写组成，不能以数字和_开头。
命名中禁止使用SQL关键字。
禁止出现中文，拼音。
除去前缀，不能超过三个单词。
必须要有扩展属性，增加注释。

3.2. 库

备份库，备份表必须以bak_为前缀并以日期为后缀。

3.3. 表

表以单数形式名词或名词短语命名。
如果表名仅有一个单词，那么建议不使用缩写，而是用完整的单词。
表名全部小写

数据表 t_inf_<系统标识>_<表标识>

编码表 t_cod_<系统标识>_<表标识>

系统表 t_sys_<系统标识>_<表标识>

统计表 t_sta_<系统标识>_<表标识>

临时表 t_tmp_<系统标识>_<表标识>

日志表 t_log_<系统标识>_<表标识>

3.4. 字段

采用有意义的字段名，应该是易于理解，能表达字段功能的英文单词或单词缩写，一般不超过三个英文单词。
字段名全部小写
系统中所有属于内码的字段（仅用于表示唯一性和程序内部用到的标识性字段），名称取为：ID。
系统中属于是业务范围内的编号的字段，其代表一定的业务信息，这样的字段建议命名为CODE，其数据类型为VARCHAR，该字段需加唯一索引。
字段名禁止与表名重复
禁止在列的名称中包含数据类型。
必须要有扩展属性，增加注释

3.5. 主键

pk_<表名>

3.6. 唯一索引

uk_<表名>_<构成索引的字段名>

3.7. 普通索引

idx_<表名>_<构成索引的字段名>

如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。

3.8. 视图

v_<系统标识>_<视图标识>

3.9. 存储过程（禁用)

proc_<系统标识>_<存储过程标识>

存储过程标识最好能直接表达存储过程内容，例如proc_sys_InsertUser

3.10. 函数

f_<系统标识>_<函数标识>

函数标识最好能直接表达函数内容，例如fn_oms_GetOrdePrice

3.11. 触发器（禁用)

tr_<表名>_<i、u、d的任意组合>

触发顺序：after、before

触发动作：i、u、d的任意组合

示例：tr_t_inf_goods_after_i

3.12. 变量

@<变量标识>

3.13. 游标（禁用）

局部游标：l_cur_<变量标识>

全局游标：g_cur_<变量标识>

3.14. 存储过程或函数定义中的参数

@<变量标识>

4. 行为规范

禁止在生产库做数据库压力测试。
DBA在执行checkdb等高IO检查操作时应于业务闲时操作，检查前应评估被检查数据库所在硬盘其他数据库的业务影响。
禁止从开发环境，测试环境直连生产环境数据库。
批量导入、导出数据必须提前通知DBA协助观察。
禁止生产库上执行后台管理和统计类查询。
产品出现非数据库导致的故障时及时通知DBA协助排查。
推广活动或上线新功能必须提前通知DBA进行流量评估。
数据库数据丢失，及时联系DBA进行恢复。
对单表的多次alter操作必须合并为一次操作。
重大项目的数据库方案选型和设计必须提前通知DBA参与。
对特别重要的库表，提前与DBA沟通确定维护和备份优先级。
禁止在业务高峰期批量更新、查询数据库。
所有关于表结构变更升级事项，设计阶段必须要有数据部门人员参与，实施过程及结果必须要知会数据部门相关人员。
提交生产库建表改表需求，必须详细注明所有相关SQL语句，且必须经过测试库严格测试，并附相关测试结果，且需有回滚计划，防止失败后无法恢复，通过数据部门确认后交由相关管理人员进行操作。

5. 可用性设计

5.1. 备份策略

因为数据库备份是一个I/O密集型操作，因此自动备份时间必须安排在业务不繁忙时进行操作。
一般备份计划：每天一个完全备份 + 每六个小时一个差异备份 + 每小时一个日志备份。（具体计划可视业务及设备情况而定）

5.2. 定期备份还原演练

每月做一次还原演练，保证备份文件有效性。

根据公司安全要求，搭建灾备环境，制定灾备演练计划，并定期演练，保证遇到不可抗力灾难可保证系统在较短时间恢复。
定期对数据库所有脚本备份逻辑结构备份。
数据与日志分离。
对于大数据量的表,选用合适字段进行分区。
根据数据的使用频繁度，对大表定期分库归档，分离冷热数据，主库/归档库物理分离；归档计划及脚本必须经过数据部门审核，并做好备份还原计划，实施过程及结果需要知会数据部门相关人员。非特殊情况下热、温、冷数据的定义（具体根据业务实际情况进行定义区分）：

3个月内业务数据定义为热数据
3到18个月的业务数据定义为温数据
18月之前的业务数据定义为冷数据

单表行数超过1000万行或者单表容量超过5GB，才推荐进行分库分表。（说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。）
当 Transact-SQL 语句在单个表或索引的引用上获取至少 5,000 个锁时，或在表已分区的情况下，在单个表分区或索引分区的引用上获取至少 5,000 个锁时，会触发锁升级，因此建议在一个事务操作中尽量不要超过5000行的数据修改或删除操作。
对于核心业务大表需单独建立文件组，并存放不同磁盘阵列，避免I/O争用。
新购买的数据库服务器，要对其进行全面的压力测试，收集相关性能数据。
其他高可用技术根据实际情况进行选择（例如AlwaysOn等）。
规范数据字典的编写

所有新增或修改字段必须维护到数据字典中
一个完整的数据字典至少应当包括以下内容：

数据集（系统）名称

数据库名称

表名称

字段

数据类型

数据说明

数据存储长度

数据的测量单位

代码说明

精密度

准确度

数据的下限

数据的上限

获得数据的手段

时间和/或环境

备注

6. SQL语句编写

SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字符串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用TRIM(),LOWER()等函数格式化匹配条件。
对于非常复杂的SQL（特别是有多层嵌套，带子句或相关子查询的），应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。
使用 IN 或 NOT IN 子句时，特别是当子句中有多个值且表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。（这里指出：in和or都是效率较低的运算，但是in的效率：O(logn)仍然比or的效率：O(n)高的多，尤其当运算列不是索引的时候尤为明显）。
避免使用SELECT * 语句，如果不必要取出所有数据，不要用 * 来代替，应给出字段列表。
避免不必要的排序，不必要的数据排序大大的降低系统性能。
使用INSERT语句一定要给出插入值的字段列表，这样即使表加了字段也不会影响现有系统的运行。
做多表操作时，应该给每个表取一个别名，每个表字段都应该标明其所属哪个表。
尽量避免where中包含子查询。
where条件中，过滤量最大的条件放在where子句之后。
where条件中，尽可能让字段顺序与索引顺序一致
Where从句中禁止对列进行函数转换和计算。
用Where子句替换HAVING子句。
采用绑定变量有助于提高效率。
在索引列上使用计算、改变索引列的类型、在索引列上使用!=将放弃索引。
运算符效率：exists高于in高于or，（not exists高于not in）。
避免在索引列上使用is null和is not null。
使用索引的第一个列。
用union-all替代union。
like ‘text%’使用索引，但like ‘%text’不使用索引。
超过五个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。
如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
利用覆盖索引来进行查询操作，避免回表。
利用延迟关联（即先将子查询进行临时表存储）或者子查询优化大查询场景。
防止因字段类型不同造成的隐式转换，导致索引失效。
避免数据类型的隐式转换。
禁止使用不含字段列表的insert语句。
拆分复杂的大SQL为多个小SQL。
超100万行的批量写操作，要分批多次进行操作。
关闭影响的行计数信息返回。
除非必要SELECT语句都必须加上NOLOCK。
查询大量数据使用分页或TOP。
Schema解耦，禁止跨库JOIN。
尽量使用EXISTS代替 SELECT COUNT(1)判断是否存在记录，COUNT函数只有在统计表中所有行数时使用，而且COUNT(1)比COUNT(*) 更有效率
尽量避免使用游标，如果一定要做循环，使用While循环来代替
避免使用不必要的DISTINCT,ORDER BY ,GROUP BY
存储过程、函数中的编写

头部注明创建人、创建时间、功能描述。如有修改注明修改人、修改时间、修改内容及原因。
过程如有修改，原代码注释，做好说明，注明修改人、修改时间及修改原因
传入参数有注释说明
传入参数做合法性检查
业务逻辑合法性检查
涉及更新、删除、插入要有异常捕获机制
过程涉及多个表更新、删除、插入要在一个事务内完成
有数据更新过程，过程要有返回值

注释示例如下：

/*

目的：

作者：

创建日期：

*/

/*

修改顺序号：

修改者：

修改日期：

修改原因：（具体原因详细描述）

*/

7. 建模管理方法

7.1. 建模工具

统一使用PDMAAS(协同版B/S)和PDManer(客户端C/S) 数据库建模软件。

7.2. 建模步骤

逻辑建模

根据数据库设计和命名规范先在PDMAAS和PDManer(客户端C/S) 中建立逻辑模型文件。要求每个表和字段都要有注释说明；Check Model不能出现错误。

根据逻辑模型文件创建对应数据库的物理模型文件。
生成数据库结构及其相应的SQL DDL脚本。

7.3. 模型维护

所有关于数据库的表、字段及关系、说明等均以物理模型文件为准。
由开发人员将变更需求提交项目负责人审批。
项目负责人同意变更后由相应开发人员负责编写变更脚本提交DBA。
DBA更新数据库及其相关文档，并维护所有部分的一致性。

8. 其他设计技巧

8.1. 保存常用信息

让一个表专门存放一般数据库信息非常有用。在这个表里存放数据库当前版本、最近检查/修复、关联设计文档的名称、客户等信息。这样可以实现一种简单机制跟踪数据库。

8.2. 包含版本机制

在数据库中引入版本控制机制来确定使用中的数据库的版本。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。把版本信息直接存放到数据库中更为方便。

8.3. 编制文档

对所有的命名规范、限制、数据字典、存储过程、函数都要编制文档。采用给表、列、触发器等加注释的数据库工具。对开发、支持和跟踪修改非常有用。对数据库文档化也会大大减少犯错的机会。

8.4. 测试、测试、反复测试

建立或者修订数据库之后，必须用用户新输入的数据测试修改的字段。最重要的是，让用户进行测试并且同用户一起保证选择的数据类型满足要求。测试需要在把新数据库投入实际服务之前完成。

8.5. 检查设计

在开发期间检查数据库设计的常用技术是通过其所支持的应用程序原型检查数据库。换句话说，针对每一种最终表达数据的原型应用，保证检查了数据模型并且查看如何取出数据。

空间快捷链接

页面树结构

0904-数据库开发管理规范