数据库开发设计规范（通用）
原创 我科绝伦 小周的数据库进阶之路 2024-05-17 15:46 黑龙江
一、编写目的
为了在软件生命周期内规范数据库相关的需求分析、设计、开发、测试、运维工作，便于不同团队之间的沟通协调，以及在相关规范上达成共识，提升相关环节的工作效率和系统的可维护性。同时好的规范，在执行的时候可以培养出好的习惯，好的习惯是软件质量的保证。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范，从而为公司业务系统稳定、健康地运行提供保障。
二、概述
从数据库的设计原则、命名规范等方面论述数据库设计的规范思想及命名规则。参照以下原则进行数据库设计：
（1）方便业务功能实现、业务功能扩展
（2）方便设计开发、增强系统的稳定性和可维护性;
（3）保证数据完整性和准确性
（4）提高数据存储效率，在满足业务需求的前提下，使时间开销和空间开销达到优化平衡。
三、数据库对象设计总原则
-
数据表的个数越少越好。
-
数据表中的字段个数越少越好。
-
数据表中联合主键的字段个数越少越好。
-
使用主键越多、外键越少越好。
-
数据库单表数据量不超过2000万行。若单表超过2000万行，则考虑分库分表架构（针对MySQL）或者分区表（针对Oracle）。
-
存储引擎非特殊情况使用InnoDB（MySQL），使用其他存储引擎需申请。
-
对于图像类、文档类存储，建议在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_为前缀，并以日期为后缀。
（二）数据库对象设计规范-表、列
-
【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以英文字母开头。
-
【强制】 表名、列名一律小写，不同单词采用下划线分割。须见名知意。
-
【强制】表名要求有模块名强相关，同一模块的表名尽量使用统一前缀。比如：crm_fund_item。
-
【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
-
【强制】表名、列名禁止使用关键字（如type,order等）。
-
【强制】创建表时必须显式指定表存储引擎类型。如无特殊需求，一律为InnoDB。
-
【强制】建表必须有注释comment。
-
【强制】禁止在数据库中存储明文密码。
-
【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写。如：公司ID，不要使用corporation_id, 而用corp_id 即可。
-
【强制】布尔值类型的字段命名为is_描述。如member表上表示是否为enabled的会员的字段命名为is_enabled。
-
【强制】禁止在数据库中存储图片、文件等大的二进制数据。
通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
- 【建议】建表时关于主键：表必须有主键(1)强制要求主键为id，类型为int或bigint，且为auto_increment 建议使用unsigned无符号型。 (2)标识表里每一行主体的字段不要设为主键，建议
设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
-
【建议】核心表（如用户表）必须有行数据的 创建时间字段（create_time）和最后更新时间字段（update_time），便于查问题。
-
【建议】表中所有字段尽量都是NOT NULL属性，业务可以根据需要定义DEFAUL值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
-
【建议】所有存储相同数据的列名和列类型必须一致，避免隐式转换（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
-
【建议】中间表（或临时表）用于保留中间结果集，名称以tmp_开头。备份表用于备份或抓取源表快照，名称以bak_开头。中间表和备份表定期清理。
-
【建议】表要有预留字段。可评估预留1-3个字段，以防后期表扩容变更。
-
【建议】字符串存储选择。定长char，非定长varchar、text（上限65535，其中varchar还会消耗1-3字节记录长度，而text使用额外空间记录长度）。
-
【建议】定长和非定长数据类型的选择。decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text。
-
【建议】优先选择符合存储需要的最小的数据类型、避免使用ENUM类型。
-
【建议】使用TIMESTAMP存储时间、DECIMAL代替FLOAT和DOUBLE存储精确浮点数、使用UNSIGNED存储非负整数、使用INT UNSIGNED存储IPV4。
-
【建议】尽可能不使用TEXT、BLOB类型。对于报文之类的大文本，可以用TEXT、BLOB类型，建议将该列单独设计为一张表，并通过关联字段与主表关联进行查询或其他操作。
-
【建议】特定字段类型的选择建议。
1）仅存储年使用YEAR类型，日期使用DATE类型。
2）时间类型使用datetime，不要使用timestmp。
3）钱币等精确浮点类型使用DECIMAL类型。
4）数值字段增长上限不大，不使用BIGINT。
- 【示范】一个较为规范的建表语句：
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='网站用户基本信息'
- 【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。实际上，我们通常很少自己写DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构的导出和导入。
（三）数据库对象设计规范-索引
-
【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。
-
【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE。
-
【建议】主键的名称以pk_开头，唯一键以uni_或uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。
-
【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:
sample 表 member_id 上的索引：idx_sample_mid。
-
【建议】单个表上的索引个数不能超过6个。
-
【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。
-
【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
-
【建议】建表或加索引时，保证表里互相不存在冗余索引。比如：如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。
-
【建议】不使用更新频繁的列作为主键，如无特殊要求，使用自增id作为主键。对于并发插入量较大且需要物理主键的表，可以通过类似JAVA里的guid键值来代替。
-
【建议】索引创建选择唯一性较强的字段。
-
【建议】索引选择数据类型较短的字段。
-
【建议】尽量避免使用外键，容易产生死锁，由上层应用程序保证约束。
-
【建议】筛选text 或较长varchar类型字段，需使用全文索引。
-
【建议】全文索引必须使用match函数， AGAINST函数，不支持％通配符匹配，例如：SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE)；
-
【建议】重要的SQL语句必须被索引，例如：Update\delete语句的where条件列；order by \group by\distinct字段。
-
【建议】索引列建议：
-
出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列；
-
包含在ORDER BY、GROUP BY、DISTINCT中的字段；
-
多表join的关联列；
注意：并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好。
- 【建议】索引列顺序建议
-
区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；
-
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；
-
使用最频繁的列放到联合索引的左侧（这样可较少的建立一些索引）。
（四）数据库对象设计规范-视图
-
【强制】数据库不能包含具有相同名称的表和视图。
-
【强制】定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。
-
【强制】不能将触发程序与视图关联在一起。
-
【建议】视图以v_name命名。
（五）数据库设计规范-SQL语句
-
【强制】程序端SELECT语句必须指定具体字段名称，禁止写成*。
-
【强制】禁止like “%abs”做where条件，会全表扫描且不能用索引。
-
【强制】Where条件里不要对列使用函数，不会引用索引，除非谓词列已有函数索引。
-
【强制】禁止单条语句同时更新多个表。
-
【强制】杜绝大事务，事务要尽量简单，整个事务的时间长度不要太长。
-
【建议】能确定返回结果只有一条时，使用limit 1（LIMIT分页注意效率，LIMIT越大，效率越低）。
-
【建议】少用子查询，改用JOIN（子查询要在内存里建临时表）。
-
【建议】多表JOIN的字段，区分度最大的字段放在前面。
-
【建议】只读查询语句不要显式开启事务，例如不要加begin或start transaction。
-
【建议】 多条INSERT语句使用bulk insert提交（INSERT INTO table VALUES(),(),()……）。
-
【建议】避免大表join。
-
【建议】SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'，其中id列为非字符类型。
-
【建议】除非必要，避免使用!=等非等值操作符，会导致用不到索引。
-
【建议】程序端insert语句指定具体字段名称，不建议写成INSERT INTO t1 VALUES(…)。
-
【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
-
【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很快，但会引起主从同步延迟。
-
【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
-
【建议】线上环境，多表JOIN不要超过5个表。
-
【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
-
【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
-
【建议】对单表的多次alter操作必须合并为一次
对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
-
【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
-
【建议】事务里包含SQL不超过5个。
因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
- 【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如
UPDATE… WHERE id=XX;
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='网站用户基本信息'
否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。
25.【建议】避免使用insert into …on duplicate key update…在高并发环境下，会造成主从不一致。
- 【建议】多表JOIN时需要注意以下：
1）区分度最大的字段放在前面；
2）核心SQL优先考虑覆盖索引；
3）避免冗余和重复索引；
4）索引要综合评估数据密度和分布以及考虑查询和更新比例。
-
【建议】在明显不会有重复值时使用UNION ALL而不是UNION。UNION会把两个结果集的所有数据放到临时表中后再进行去重操作，UNION ALL不会再对结果集进行去重操作。
-
【建议】超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作。
-
【建议】对于大表使用pt-online-schema-change修改表结构。可避免大表修改产生的主从延迟、在对表字段进行修改时进行锁表。
（六）数据库设计规范-存储过程
在MySQL数据库中尽量避免使用存储过程，Oracle数据库中午要求。
在存储过程中，MySQL禁止使用下述语句：
CHECK TABLES
LOCK TABLES, UNLOCK TABLES
LOAD DATA, LOAD TABLE
OPTIMIZE TABLE
SQL预处理语句（PREPARE、EXECUTE、DEALLOCATE PREPARE），目前仅适用于存储过程;不适用于存储函数和触发器;也不适用于在存储函数或者触发器里面调用含有预处理语句的存储过程。
创建存储过程以proc_开头命名，函数以 func_开头命名。
（七）数据库设计规范-触发器
建议禁止使用触发器，触发器可以理解为是一个隐藏的存储过程，它不需要调用，不需要显示调用，维护起来容易被忽略。可以使用存储过程来替代。
（八）数据库设计规范-安全规范
用户权限管理：按照最小权限原则分配用户权限，避免过多权限导致的安全风险。
加密敏感数据：对于敏感数据，建议进行加密处理。
防范SQL注入：使用预编译查询，并避免动态生成SQL语句。
审计和日志记录：记录关键操作日志，确保可追溯性。
四、数据库架构设计原则
（一）高可用架构选择
设计数据库时，考虑实现高可用性方案，如数据库镜像、复制和集群技术，避免单节点故障引起的业务影响。采用主从复制或集群技术，确保数据的高可用性和实时同步。配置自动故障切换机制，如主备切换，保证系统在故障发生时能迅速恢复。
（二）扩展性
对于系统来说扩展性很重要，尽量做到水平扩展。避免过度依赖纵向扩展，同时具备纵向，横向扩展的能力，例如无状态应用应该多套负载均衡多活部署，数据库分库架构。架构应该尽可能地分散负载，减少单点故障的风险。
（三）安全性策略
访问控制：严格控制数据库访问权限，遵循最小权限原则。
加密存储：采用合适的加密算法加密核心数据，防止黑客攻击和数据泄露。
日志审计：启用数据库日志，记录用户操作和系统事件，以便安全审计和问题追溯。
（四）数据完整性策略
必须遵循数据库设计的第二范式，根据业务需要尽量满足第三范式
数据完整性尽量通过业务逻辑实现，数据库设计应尽量避免使用大量的外键约束，避免使用触发器。
（五）规范化设计与性能之间的权衡策略
数据的标准化有助于消除数据库中的数据冗余。如果数据几余低数据的一致性容易得到保证，如无特殊理由，OLTP系统的设计应当遵循第三范式，对于 OLAP 系统，为了减少表间连接查询的操作，提高系统的响应时间，合理的数据几余是必要的。
（六）数据生命周期管理
数据归档：对历史数据和不常使用的数据进行归档处理，减少主库的负担。
数据清理：定期清理过期和无用的数据，保持数据库的整洁。
（七）数据备份策略
备份策略：制定完善的备份策略，包括全量、增量备份及异地备份。
文中的概念来源于网络，如有侵权，请联系我删除。
欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。欢迎觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。
我科绝伦
个人观点，仅供参考
阅读 1134
文章已于2024-05-17修改
小周的数据库进阶之路
写下你的留言
3条留言
Nginx
甘肃
5月17日
2
牛[得意]
置顶
作者赞过
亚星
广东
6月8日
1
绝大多数有用[强][强][强]
作者赞过
正常人
北京
6月2日
1
数据表的个数不是越少越好吧，这个要根据具体的业务情况来决定！
作者赞过
已无更多数据
人划线
标签：建议,通用,数据库,存储,索引,使用,设计规范,id From： https://www.cnblogs.com/huft/p/18249230