数据库开发设计规范（通用）

为了在软件生命周期内规范数据库相关的需求分析、设计、开发、测试、运维工作，便于不同团队之间的沟通协调，以及在相关规范上达成共识，提升相关环节的工作效率和系统的可维护性。同时好的规范，在执行的时候可以培养出好的习惯，好的习惯是软件质量的保证。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。

  从数据库的设计原则、命名规范等方面论述数据库设计的规范思想及命名规则。参照以下原则进行数据库设计：

（1）方便业务功能实现、业务功能扩展

（2）方便设计开发、增强系统的稳定性和可维护性;

（3）保证数据完整性和准确性

（4）提高数据存储效率，在满足业务需求的前提下，使时间开销和空间开销达到优化平衡。

1. 数据表的个数越少越好。

2. 数据表中的字段个数越少越好。

3. 数据表中联合主键的字段个数越少越好。

4. 使用主键越多、外键越少越好。

5. 数据库单表数据量不超过2000万行。若单表超过2000万行，则考虑分库分表架构（针对MySQL）或者分区表（针对Oracle）。

6. 存储引擎非特殊情况使用InnoDB（MySQL），使用其他存储引擎需申请。

7. 对于图像类、文档类存储，建议在MySQL或者Oracle数据库之外进行存储，比如使用文档服务器或者文档数据库。

注意：这个原则并不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率。

（一）数据库对象设计规范-库

1、【强制】库的名称必须控制在30个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。

2.【强制】库名中英文一律小写，不同单词采用下划线分割。须见名知意。

3.【强制】库的名称格式：业务系统名称_子系统名。

4.【强制】库名禁止使用关键字（如type,order等）。

5.【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。

创建数据库SQL举例（MySQL）：CREATE DATABASE crm_fundDEFAULT CHARACTER SET 'utf8' ;

6.【建议】对于程序连接数据库账号，遵循权限最小原则

使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号原则上不准有drop权限 。

7.【建议】临时库以 tmp_ 为前缀，并以日期为后缀；备份库以bak_为前缀，并以日期为后缀。

（二）数据库对象设计规范-表、列

1. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以英文字母开头。

2. 【强制】 表名、列名一律小写，不同单词采用下划线分割。须见名知意。

3. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用统一前缀。比如：crm_fund_item。

4. 【强制】创建表时必须显式指定字符集为utf8或utf8mb4。

5. 【强制】表名、列名禁止使用关键字（如type,order等）。

6. 【强制】创建表时必须显式指定表存储引擎类型。如无特殊需求，一律为InnoDB。

7. 【强制】建表必须有注释comment。

8. 【强制】禁止在数据库中存储明文密码。

8. 【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写。如：公司ID，不要使用corporation_id, 而用corp_id 即可。

9. 【强制】布尔值类型的字段命名为is_描述。如member表上表示是否为enabled的会员的字段命名为is_enabled。

10. 【强制】禁止在数据库中存储图片、文件等大的二进制数据。

通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。

11. 【建议】建表时关于主键：表必须有主键(1)强制要求主键为id，类型为int或bigint，且为auto_increment 建议使用unsigned无符号型。 (2)标识表里每一行主体的字段不要设为主键，建议

设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。

12. 【建议】核心表（如用户表）必须有行数据的 创建时间字段（create_time）和最后更新时间字段（update_time），便于查问题。

13. 【建议】表中所有字段尽量都是NOT NULL属性，业务可以根据需要定义DEFAUL值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

14. 【建议】所有存储相同数据的列名和列类型必须一致，避免隐式转换（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。

15. 【建议】中间表（或临时表）用于保留中间结果集，名称以tmp_开头。备份表用于备份或抓取源表快照，名称以bak_开头。中间表和备份表定期清理。

16.  【建议】表要有预留字段。可评估预留1-3个字段，以防后期表扩容变更。

17. 【建议】字符串存储选择。定长char，非定长varchar、text（上限65535，其中varchar还会消耗1-3字节记录长度，而text使用额外空间记录长度）。

18. 【建议】定长和非定长数据类型的选择。decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text。

19. 【建议】优先选择符合存储需要的最小的数据类型、避免使用ENUM类型。

20. 【建议】使用TIMESTAMP存储时间、DECIMAL代替FLOAT和DOUBLE存储精确浮点数、使用UNSIGNED存储非负整数、使用INT UNSIGNED存储IPV4。

21. 【建议】尽可能不使用TEXT、BLOB类型。对于报文之类的大文本，可以用TEXT、BLOB类型，建议将该列单独设计为一张表，并通过关联字段与主表关联进行查询或其他操作。

22. 【建议】特定字段类型的选择建议。

1）仅存储年使用YEAR类型，日期使用DATE类型。

2）时间类型使用datetime，不要使用timestmp。

3）钱币等精确浮点类型使用DECIMAL类型。

4）数值字段增长上限不大，不使用BIGINT。

23. 【示范】一个较为规范的建表语句：

CREATE TABLE user_info (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`user_id` bigint(11) NOT NULL COMMENT '用户id',

`username` varchar(45) NOT NULL COMMENT '真实姓名',

`email` varchar(30) NOT NULL COMMENT '用户邮箱',

`nickname` varchar(45) NOT NULL COMMENT '昵称',

`birthday` date NOT NULL COMMENT '生日',

`sex` tinyint(4) DEFAULT '0' COMMENT '性别',

`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',

`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',

`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP COMMENT '修改时间',

`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未 通过，4为还未提交审核',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_user_id` (`user_id`),

KEY `idx_username`(`username`),

KEY `idx_create_time_status`(`create_time`,`user_review_status`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'

24. 【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。实际上，我们通常很少自己写DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构的导出和导入。

（三）数据库对象设计规范-索引

1. 【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。

2. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE。

3. 【建议】主键的名称以pk_开头，唯一键以uni_或uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。

4. 【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:

sample 表 member_id 上的索引：idx_sample_mid。

5. 【建议】单个表上的索引个数不能超过6个。

6. 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。

7. 【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

8. 【建议】建表或加索引时，保证表里互相不存在冗余索引。比如：如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

9. 【建议】不使用更新频繁的列作为主键，如无特殊要求，使用自增id作为主键。对于并发插入量较大且需要物理主键的表，可以通过类似JAVA里的guid键值来代替。

10. 【建议】索引创建选择唯一性较强的字段。

11. 【建议】索引选择数据类型较短的字段。

12. 【建议】尽量避免使用外键，容易产生死锁，由上层应用程序保证约束。

13. 【建议】筛选text 或较长varchar类型字段，需使用全文索引。

14. 【建议】全文索引必须使用match函数， AGAINST函数，不支持％通配符匹配，例如：SELECT * FROM articles     WHERE MATCH (title,body)     AGAINST ('database' IN NATURAL LANGUAGE MODE)；

15. 【建议】重要的SQL语句必须被索引，例如：Update\delete语句的where条件列；order by \group by\distinct字段。

16. 【建议】索引列建议：

1) 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列；

2) 包含在ORDER BY、GROUP BY、DISTINCT中的字段；

3) 多表join的关联列；

注意：并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好。

17. 【建议】索引列顺序建议

1) 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；

2) 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；

3) 使用最频繁的列放到联合索引的左侧（这样可较少的建立一些索引）。

（四）数据库对象设计规范-视图

1. 【强制】数据库不能包含具有相同名称的表和视图。

2. 【强制】定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

3. 【强制】不能将触发程序与视图关联在一起。

4. 【建议】视图以v_name命名。

（五）数据库设计规范-SQL语句

1. 【强制】程序端SELECT语句必须指定具体字段名称，禁止写成*。

2. 【强制】禁止like “