数据库设计规范

目录

1 目的

2 适用范围

3 数据库命名规范

4 数据库设计规范

5 索引设计规范

6 数据库字段设计规范

7 数据库SQL开发规范

8 操作行为规范

1. 目的

规范数据库的开发工作。

加强开发人员在开发过程对数据库的合理使用，提高数据库的使用效率和规范数据库的操作。

1. 适用范围

本规范适用于本公司的信息化系统运维、技术人员和系统供应商。

1. 数据库命名规范

库名、表名、字段等数据库对象名称：

1. 必须使用小写字母，并且使用下划线分割。
2. 命名要能做到见名知意, 建议不超过32个字符。
3. 禁止使用MySQL等数据库保留关键字。
4. 临时表必须以tmp为前缀并以日期为后缀。
5. 备份库，备份表必须以bak为前缀并以日期为后缀。
6. 所有存储相同数据的列名和列数据类型必须一致。

表名、视图、序号、索引、触发器命名要求：

序号	类型	命名规则	举例/说明
1	表	模块简称_功能简称	po_headers、po_lines、ap_invoices
2	多语言表	模块简称_功能简称_tl	po_headers_tl、mtl_system_items_tl
3	视图	模块简称_功能简称_v	ap_invoices_v
4	序列	模块简称_功能简称_s	po_headers_s
5	索引	模块简称_功能简称_n/u(1..99)	n代表Normal索引，u代表Unique索引
6	触发器	模块简称_功能简称_操作简称_trg	po_headers_i_trg、po_headers_u_trg（其中i代表插入，u代表更新数据时）

表字段定义要求：

字段名称	类型	必须/ 建议	说明
created_by	数字	必须	创建该表中记录的登录用户id
creation_date	日期	必须	创建该表中记录的时间
last_updated_by	数字	必须	修改该表中记录的登录用户id
last_update_date	日期	必须	修改该表中记录的时间
deleted_falg	字符串	必须	是否删除表示，默认为N，删除后表示为Y。防止硬删除
主键id	字符串	必须	主键id（定义为唯一性索引）
attribute1	字符串	建议	备用字段
attribute2	字符串	建议	备用字段
attribute3	字符串	建议	备用字段
attribute4	字符串	建议	备用字段
attribute5	字符串	建议	备用字段
object_version_number	数字	建议	insert记录时默认为1，每次update后加1，用于记录锁定及更改次数记录
status_code	字符串	可选（非空）	业务数据，处理状态字段，其值例如：ENTERED、APPROVED、PROCESSED等
enable_flag	字符串	可选(非空)	基础数据，是否启用状态字段，Y/N
active_start_date	日期	可选	启用开始日期
active_end_date	日期	可选	启用结束日期

1. 数据库设计规范

1. 数据库和表的字符集统一使用UTF8，避免由于字符集转换产生的乱码。
2. 所有的表和字段都需要添加注释。
3. 尽量控制单表数据量的大小，建议控制在500万以内。
4. 谨慎使用MySQL分区表，建议采用物理分表方式管理大数据。
5. 尽量做到冷热数据分离，减小表的宽度；经常一起使用的列放到一个表中。
6. 禁止在表中创建预留列。
7. 禁止在数据库中存储图片，文件等二进制数据。
8. 禁止在生产数据库在线做压力测试。
9. 禁止从开发环境，测试环境直接连生产环境数据库。

1. 索引设计规范

1. 限制单表的索引数量，建议单表的索引数量不超过5个;禁止给表中的每一列都建立单独的索引。
2. 每个表必须有一个主键：

不使用更新频繁的列作为主键，不使用多列主键；

不使用UUID,MD5,HASH，字符串列作为主键；

主键建议选择使用自增ID值

1. 常见索引列建议：

SELECT UPDATE.DELETE语句，WHERE从句的列；

包含在OEDER BY , GROUP BY , DISTINCT中的字段；

多表JOIN的关键列

1. 索引命名规范

非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名

唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名

1. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
2. 索引列的顺序建议

索引列的顺序，区分度最高的列放在联合索引的最左侧；

尽量把字段长度小的列放在联合索引的最左侧；

使用最频繁的列放到联合索引的左侧

1. 避免建立冗余索引和重复索引。
2. 对于频繁的查询，优先考虑使用覆盖索引。
3. 尽量避免使用外键。建议不使用外键约束，但是在表与表之间的关联键上建立索引。

1. 数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型

无符号相对于有符号可以省一倍的存储空间。

对于非负数采用无符号整型(UNSIGNED)进行存储。

VARCHAR(N)中的N代表的是字符数，而不是字节数，根据实际的宽度来选择N，尽量用小的N。使用UTF8存储汉字 varchar(255)=765字节，过大的长度会消耗更多的内存。

1. 避免使用TEXT,BLOB数据类型。

建议把BLOB 或TEXT列 分离到单独的扩展表中。

TEXT,BLOB数据类型只能使用前缀索引。

1. 避免使用ENUM数据类型，使用TINYINT来代替。
2. 尽可能把所有的列定义为NOT NULL。
3. 不要使用字符串存储日期型的数据。
4. 使用TIMESTAMP或DATETIME存储时间, TIMESTAMP使用4字节，DATETIME使用8个字节。
5. 存储精确浮点数，必须使用decimal类型(比如:财务相关的金额类数据)。

1. 数据库SQL开发规范

1. 建议使用预编译语句进行数据库操作,可以提高性能并且避免SQL注入。
2. WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。
3. 避免使用双%号的查询条件，不建议使用%前缀模糊查询，例如LIKE “%TEST”。
4. 一个SQL只能利用到复合索引中的一列进行范围查询。

1. 合理利用存在的索引，而不是盲目增加索引。
2. 避免使用JOIN, 使用left join或not exists来优化join in操作。
3. 程序连接不同的数据库使用不同的账号，禁止跨库查询。
4. SELECT语句只获取需要的字段：

必须使用SELECT <字段列表>；

禁止使用"SELECT *"。

1. 禁止使用不含字段列表的INSERT语句,必须显式的指明字段名称。
2. 避免使用子查询，可以优化为join操作。
3. 避免使用JOIN关联太多的表，建议不超过5个。
4. 使用合理的SQL语句,减少同数据库交互次数，进行批量操作。
5. 使用IN代替OR，IN包含的值不应过多,建议不要超过500个。
6. 禁止使用ORDER BY RAND()进行随机排序，使用其他方法替换。
7. WHERE 从句中，禁止对列进行函数转换和计算。
8. 在明显不会有重复值时使用UNION ALL而不是UNION。
9. 拆分复杂的大SQL为多个小SQL。
10. 禁止在从库上执行后台管理和统计类型功能的QUERY。
11. 建议使用合理的分页方式以提高分页的效率。
12. INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不应过多。
13. 统计表中记录数时使用COUNT(*)，而不是 COUNT(primary_key)和COUNT(1)。

1. 操作行为规范

1. 超过100万行的批量写操作，要分批多次进行操作。
2. 谨慎修改数据库的表结构。
3. 对于大表使用pt-onine-schema-change，修改表结构。
4. 禁止为程序使用账号赋予super权限。
5. 对于程序连接数据库账号，遵循权限最小原则。
6. 程序使用的账号只能在一个DB下使用，不能跨库。
7. 程序使用的账号原则上不能有drop权限。