MySQL数据库设计规范（一）：表结构设计

发表于 2020-11-01 分类于项目管理

本文为我们项目中所采用的MySQL数据库设计规范。

计划分为三篇文章，第一篇为数据库的表结构设计，第二篇为SQL语句的书写，第三篇为PDM文档规范。

数据库设计原则

表设计原则

使用InnoDB引擎。如使用其他引擎，应经过评估。
字符集采用utf8mb4。
表结构设计原则上应满足第三范式，但可以为了提高性能而破坏此规则，例如在频繁访问而不频繁修改的表中，为了避免在查询时多表JOIN，设置一些冗余字段。另外，冗余字段应为经常被查询的字段，不应是频繁修改的字段，也不应是太占用空间的字段（例如长度太长的VARCHAR）。
避免使用分区表。
表结构设计文档应包含注释，解释表与字段主要用途、取值范围、使用注意事项、关联关系等。
表结构设计文档中，为便于查找排序，表的中文名称应采用统一规则。例如B-SYS-01-系统基础-用户信息，规定其中的B为数据域编码，SYS为功能模块编码，01为流水号，系统基础为功能模块名称，而用户信息才是实际的表名称。
单表数据量应控制在一千万以内。如预计会超过一千万，应提前考虑分库分表。

字段设计原则

使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。
选择更简单的数据类型，例如能使用TINYINT则不使用CHAR或VARCHAR。
布尔值使用TINYINT，数值0表示为假，数值1表示为真。
避免使用FLOAT或DOUBLE存储浮点数。如有需要，使用DECIMAL类型。
将字段定义为NOT NULL。如确实需要NULL，应在模型设计与SQL建表脚本中明确指明。
应尽量设置默认值。字符型为空字符串，数值型则为0。
避免存储大文件和BLOB。如有需要，应部署专门的文件服务器，将文件存放于文件服务器，在数据库中只存储其路径。
只允许使用以下数据类型：数值系列（TINYINT到BIGINT）、DECIMAL、CHAR、VARCHAR、TINYTEXT至LONGTEXT、TINYBLOB至LONGBLOB、DATETIME、TIMESTAMP。
除非为纯查询用表，否则应包含以下五个字段：

字段	字段英文名	类型（仅供参考）	说明
版本	version	INT	用于解决并发问题
创建时间	created_at	TIMESTAMP
创建人	created_by	INT	类型与用户ID保持一致
创建时间	updated_at	TIMESTAMP
创建人	updated_by	INT	类型与用户ID保持一致

该五个字段均为NOT NULL，且不应包含任何额外业务逻辑。在执行UPDATE时，必须对“修改时间”与“修改人”字段进行更新。

键设计原则

禁止在生产环境使用外键。
可在开发环境利用外键来辅助开发人员，但无论是否有外键，约束均应由程序控制。
必须设置主键。
主键字段的值必须唯一。
不使用频繁更新的列作为主键。
尽量使用系统生成的值作为主键，建议使用自增值。
不使用UUID、hash等作为主键。

索引设计原则

区分度最大的字段放在前面。
避免冗余和重复索引。
控制索引数量。单张表不要超过5个索引，单个索引字段数不要超过5个。
不要索引大型字段（有很多字符的字段），如有需要，建议考虑前缀索引，例如只索引前8个字符。
不要索引常用的小型表。
不要索引区分度不大的字段，例如“性别”。
不要对可以为NULL的字段建立索引。如需建立索引，应先将字段修改为NOT NULL。
不要对主键建立索引，主键本身自带索引。

命名规范

命名时使用尽量描述实体的内容。使用英文单词、单词组合或单词缩写组成，禁止使用汉语拼音或汉语拼音缩写。
命名采用小写字母、数字或下划线组成，不使用其他字符，长度不超过30字符。
同一词语在数据库中只能具有一个语义，例如，已经用area表示面积，那么就不能再用这个词语表示地区。
同一个语义应该用同一个词语来表示，例如是否有效如果已经用is_enabled表示，那么不要再使用is_valid表示。
不能使用系统关键字命名。考虑到系统扩展需要，应避免和各种主流数据库软件的关键字冲突。
表名统一采用模块名_表名，例如sys_user。
字段名统一采用普通英文单词，不同单词之间用下划线分割。对于布尔类型，则为is_形容词，例如is_enabled。
主键为pk_表名
索引为idx_表名_字段名，如长度太长，可适当缩减表名或字段名，但同一表的表名应保持一致。
临时用数据库、临时用表命名应以tmp_为前缀，以日期为后缀，用完之后应及时清理。
备份用数据库、备份用表命名应以bak_为前缀，以日期为后缀，且不应与生产用表混放。
不要使用驼峰命名法与匈牙利命名法（例如strName）。

其他规则

避免使用函数、存储过程与触发器。
禁止在数据库存储明文密码，必须进行加密后再存储。避免使用MD5、SHA1等安全性较低的加密方式。
设计IP字段时应考虑IPv6接入。IPv6地址最长长度为39个字符。
考虑到扩展性，如系统未来可能会改为Oracle或其他数据库部署，在设计字段时应考虑数据类型的兼容性。
应用服务器、数据库服务器操作系统时区统一用Asia/Shanghai（东八区）。

数据库管理流程规范

数据库文档管理

软件开发人员应及时维护以下资料：

数据库物理模型设计文档
基线版本开始到当前版本，所有表结构变更SQL文件（或脚本）
数据库软件配置文件

这些资料应纳入配置管理，而数据库物理模型设计文档应在设计完成后形成基线，后续进行变更管理流程。

数据库表结构版本

数据库表结构应设置版本号，并采用表结构设计变更时间作为版本的编号，其格式为数据库名称-YYYYMMDD-两位流水号，例如helloworld-20201101-01。

在数据库中设立数据库版本表db_version，表结构如下：

字段	字段英文名	类型	主键	非空	其他要求
序号	id	INT	√	√	AUTO_INCREMENT
版本号	version	VARCHAR(50)		√
操作时间	operate_time	TIMESTAMP		√	DEFAULT CURRENT_TIMESTAMP
责任人	operate_name	VARCHAR(50)		√

表结构变更SQL文件

表结构变更SQL文件应遵循以下规则：

文件名为版本号-变更简要说明.sql，例如helloworld-20201101-01-新增业务表.sql。
第一个版本文件名为版本号-初始化.sql，例如helloworld-20201101-01-初始化.sql，其中包含全量的建表语句，而后续所有版本均应为前一版本基础上的增量变更语句。
文件内文中应以注释形式加入应用升级或表结构变更说明，以便事后追溯。
除非明确要求执行删除（DROP、TRUNCATE等）操作，变更应使用ALTER修改，不得先DROP后CREATE。
SQL文件不包含CREATE DATABASE、USE 数据库名等涉及数据库实例的语句，也不指定数据库实例（例如helloworld.dict_dict）。创建与切换数据库操作总是应当确认后手工执行。
在空白数据库中，按SQL文件顺序逐个执行以后，形成的表结构应与当前版本的物理模型文档完全一致，且过程中产生的数据也得到了妥善处理。
文件应包含更新数据库版本表的语句：

INSERT INTO db_version (version, operate_name) VALUES ('版本号', '责任人姓名');

表结构变更

禁止未经评审确认私自修改表结构，包括文档以及开发、测试与生产数据库。
建新表、增加修改删除字段均应先进行评审确认，评审通过后再修改文档与实际的数据库。
所有建表操作应同时确定索引，并经过评审确认。
评审确认后应及时更新文档。
上线特殊活动（例如集中申报、抢号、抢票）之前，应提前请DBA进行评估。

数据库账号

在生产环境中，应向应用程序分配最小权限访问用户，严禁应用程序使用高权限账号（root）连接数据库。

严格管理连接生产数据库与生产服务器的软件，严禁使用非正规手段连接，严禁使用盗版软件建立连接。

部署升级操作

在生产环境，涉及表结构变更时应提前整理升级材料，并征得运维管理人员许可，然后方可进行操作。报备内容包含：

涉及的表结构变更内容
涉及的变更SQL（或脚本），其中需包含表结构的变更、索引的设置等
如需调整数据库软件配置，需报备修改的参数

未经许可，禁止私自进行以下行为：

增加或删除生产用表。
修改现有生产用表的表结构，包含增、删、改字段，以及修改字段名称、类型、长度、非空变可空。
在业务高峰期，人工进行大量更新、大量查询、大表数据统计、批量导入导出等资源消耗较大的操作。
对服务器内统一部署的监控与数据传输等软件进行启停或配置调整。

在进行大规模或大批量操作时，需DBA介入观察。

数据修改

禁止私自修改生产环境业务数据。一旦东窗事发，轻则被公司开除，重则遭遇牢狱之灾。

修改生产环境业务数据应按以下流程操作：

要求修改人提供具有效力的依据，例如签字盖章的传真。如无正式文件可拒绝修改请求。如事关紧急可酌情（例如领导电话确认）考虑先行修改，但仍应要求事后补好手续。
对修改凭据进行编号和归档管理。
修改数据，修改时应记录修改语句以及修改前后的数据。
在专门的数据修改记录表（纸质材料、电子表格文件或数据库表）记录修改凭据编号、修改涉及的表和字段、修改语句以及修改前后的数据。

因程序bug导致的数据错误也需要进行记录，凭据则为所处理的bug内容。

另外，如系统采用主从库部署，禁止修改从库数据。