概要

本文仅针对设计数据库时要考虑的事项提出最基本建议和要求，但不提供数据库的运维操作，如查询分析，故障诊断等内容。

数据库是有其特定的文件组织逻辑和操作逻辑，作为一个物理系统，任何数据库都有其局限性。在设计数据库的时候，必须要考虑到这些问题，充分合理地利用数据库的物理特性，避免在业务增长时，遇到难以解决的问题。

范式与外键

现代软件工程已不提倡使用外键约束，这是为了容忍一些不规范的操作，因为没有仔细计划的外键级联操作可能会产生一些预期之外的问题，最常见的就是误删除。比如删除主数据后，与之级联的所有附加数据和历史数据都会被自动删除，这样十分危险。

另一种情况是如果级联设为必须先保证删除相关数据，那么数据库会拒绝主数据的删除操作，导致操作失败，这会让用户沮丧，甚至认为软件发生了BUG。

那么从业务上最快速的操作，就是把主数据标记删除（标记删除也并非是一种好的做法，后面专门介绍），或者只删除主数据，而保留其他数据。

虽然这样的操作需求并不符合关系数据库的设计思想，但是软件工程的 ”先做了再说“ 的思想高于数据库的设计优先级，数据库只好做出让步。

那就是取消外键约束，消除数据之间的硬性依赖，自由地操作数据。

这会带来各种问题：数据混乱，缺失，或者是多余，错误的数据就像定时炸弹，随时会让系统产生故障，到时候不得不人工处理数据，造成 ”人与数据的耦合”。

表分类

类别表/配置表：数据量有限，一般不会超过100条，在初次录入数据后，整个项目周期一般不会有数据更改；

实体表：数据规模容易预测；增长有限；

流水表：会无限增长，直到数据库无法承载；

报表：一般情况下增长有限。

以上，设计一个表时，应确认它的类别，数据模型，使用何种ID。

表前缀

如无必要，不用前缀。

表前缀在两种情况下有用：一是一个数据库中部署多套一样的系统，使用前缀来区分不同的系统。比如 a_user, b_user ，表示 a 系统中的 用户表 和 b 系统中的用户表；

另一种情况是起到让表聚集在一起的作用，如果数据库使用无意义的前缀，像 bp ds tb_ 这种莫名所以，在查询表的时候，就会极大降低效率，因为你记得表的名字，但不知道它的前缀是多少，只好把每种前缀都尝试一遍。

综上，如果不是要在一个数据库中部署一个系统的多个实例，则不必对所有表使用相同的前缀。如果某些表具有相信的属性需要聚集在一起方便查询，则可以使用有意义的前缀，如 wx_ 代表与微信有关的表。

主键设计

默认使用BigInt （8个字节），如果想要节约，可以使用Int（4个字节）；

Saas系统（多租户系统，需要用户之间数据隔离）的物理实体表可以使用字符串，但8个字符长度足够用了。

无限增长的流水表，宜使用 BigInt 而不是字符串。

一般设计主键为 Int ，是4个字节32位，可以容纳 21 亿数据，若设为无符号类型，则容量可以达到 42 亿，这对于一般的系统，足够使用了。但实际操作中一般使用 BigInt，对应编程语言中的长整型，可以容纳 9*10^18条数据 ，足以应对任何场景。

有些系统会将ID设计为 GUID 形式，spring 的 自动生成GUID 大概是有序的，但实际是并无法严格保证其有序性；主键天然有索引，当使用 GUID 当索引时，理论上会影响到其插入和索引性能。

在Saas系统中，如果ID暴露在外，且系统不想让用户看到ID，因为自增ID容易让人猜到其增长规律，这种情况下，对于总量有限的数据，可以使用简化的字符串ID，如人员，组织，企业，门店等实体；考虑数据规模，依次使用4位，8位，16位，32位；以每个字符62种表达，容量分别如下：

4位：（26*2+10）^4 = 14776336

8位：218 3401 0558 4896 （218万亿）

16位：47,672,401,706,823,533,450,263,330,816 （无限大）

再考虑到这种情况下的可读性，只使用车牌号系统的34进制，可能的容量如下：

4位：1,336,336

8位：1 7857 9390 4896 （1.78万亿）

16位：3,189,059,870,763,703,892,770,816 （无限大）

对于无限增长的流水型数据，使用流水号/序列号作为其唯一索引，序列号一般包含创建时间，及其他信息。比如订单表，一般使用流水号/序列号作为业务上的 ”订单ID” ，但实际上这只是一个唯一索引，其真正的ID是BigInt，而且在业务中，这个真正的ID是不对外暴露的。

枚举类型

禁止使用魔法数字，而是用有意义的枚举。

很多业务中，喜欢使用数字表达业务状态，比如用 1表示下单待支付，2表示已支付 3 表示退款等。这种表达会造成额外的认知负荷，需要对数字进行一次翻译。这种情况宜使用枚举，如enum(\'TOPAY\', \'PAIED\', \'WITHDRAW\') 。

在任何编程语言中，枚举都是被当作数字来处理的，MySql也是如此，正是考虑到人力翻译的认知负荷。

另外在线上高压的环境中，如果处理一个陌生的问题，使用人力翻译数字，无疑会加剧这种精神压力，尤其是在半夜，很多人同时给你打电话催你，或者业务高峰其系统宕机，想想这时候的压力吧。你在分析问题，还有人在催你，你在连续多天熬夜之后，精力极度匮乏，还不敢出任何错误，每一次思考都要反复确认，就像行走在钢丝上，这时候你会发现，这些魔法数让你抓狂。

字符串类型

如果无法避免使用字符串作为主键，其数据类型不必设为 utf8mb4，甚至于 utf8mb4_0900_ai_ci 等这些奇奇怪怪的类型也不要考虑。

utf8mb4 的唯一用处，是用来显示昵称中的特殊符号，为了防止遗忘而在线上产生问题，有时干脆设置整个数据库的默认字符串格式为 utf8mb4 ，虽然这并不必要。

如果设置整个数据库的默认格式为 utf8mb4，编码集有很多奇怪的设置，这些设置全都是关于排序规则的设定比如有音调标号，重音排序，多语言排序之类。对于中文，常用 utf8mb4_general_ci 就足够用了，其余的完全没有必要。

以下是建议：

在一般情况下，出于简化管理的需要，设置数据库的默认字符集是 utf8mb4，默认排序方式是 utf8mb4_general_ci（mysql5.7），其他常用的是(mysql8)：utf8mb4_bin ， 和 utf8mb4_unicode_ci 。

虽然本人不太喜欢 utf8mb4，但这已成行业惯例，所以不得不这样设置。但对于数据量大的表，必须要谨慎再谨慎，检查再检查。

在其他情况下，如需要精确控制，比如减少存储占用，增加排序效率，可对单独的表与列使用 utf8 甚至 ASCII 。

字段设计

字段总宽度不宜超出屏幕宽度。

在线上定位故障时，需要快速访问线上数据，如果查询的字段在屏幕之外，则需要额外的操作，浪费宝贵的时间。对于业务上确实需要设计宽表的，可以使用 Json 列将某些可以归类的、不需要索引的字段聚集起来。

业务最常用、访问频繁的字段靠前展示。

同时，长文字如描述，备注等业务不敏感的字段靠后展示。也是为了提高人工排障时的操作效率。

代码模板和数据库冗余

上线后，要清理掉无用的表和代码。

有的项目使用代码模板，或直接复制其他项目的代码，在线上产生了很多不必要的数据表和字段，需要清理掉。

对于代码模板，其中的业务机制，是否符合需求，是非常明确的，清理决策容易做出。

而对于复制类似项目的代码，有些业务代码是否有用需要仔细甄别。

权限控制与管理

线上业务账号不得拥有DDL操作；

普通业务账号只授权与其业务相关的表相关访问权限；

人员登录维护，各人使用各自账号；一是防止误操作，二是在发生问题时能够快速溯源，起到审计作用。

使用IP白名单，禁止非法登录，确保数据安全。

标记删除

标记删除是把某条数据标记为已删除，从而在查询中避开它。

这样做会有以下问题：

1 所有的查询都不得不带上标记字段： and del_flag = 0 这样相当繁琐和难受。

2 只有删除操作而没有恢复操作，当有数据恢复的需求时，只能手工处理。

3 存在潜在的逻辑问题：如果标记删除后，又新建了数据，而且新数据也产生了历史记录，那么，是否需要合并这些数据。

总之，标记删除易于操作，但隐患相当的大。

如果数据需要删除，就应当考虑，这条数据是否需要完全删除。如果确定数据可以直接删除，而不必恢复，那么就从数据库中直接删掉。

如果担心误删除，并做好了人工恢复的预期，可以做一个删除区(以 特定前缀开头，如recycled_employee)，类似于回收站，将删除的数据从业务表，移动到删除区的中，同时所有相关历史数据都移进去。

把删除区可以通过触发器来实现，这样可以实现对业务透明。

如果是出于审计的目的，那么审计本身就是业务需求的一种，此时的需求是对所有的数据与操作进行溯源，恢复数据也是审计的附带需求。

那此时不但要有操作记录，还需要有回收站机制。

综上所述，标记删除在逻辑上的执行不够闭环，仅仅是一个成本最低的选项，而它所带来的隐患，要高于它节约的成本。

本人认为，在任何情况下，都不要使用逻辑删除。

数据库的坏味道/反面案例

本节列举一些反而案例

1 很多表/几乎所有表具有相同的基础结构

这些基础结构如，id, create_time, owner_id, modify_time, parent_id 这些，除了 id 和 create_time 两个比较常用外，其余的字段在业务中并不常用，也意味着不必每个表都需要它们。这说明设计数据库的人放弃了对数据库结构的优化。

2 某表中有许多字段的值为空值，如 null 或空字符串（稀疏表）

而且这些字段并非是前一条提到的基础字段，而是与业务相关的。这说明，要么这些字段是多余的，要么是业务逻辑是多余的。此时可以顺着这些字段去代码中查询，应该能看到，它们只有插入，没有读取，甚至连插入都没有，完全是只有定义没有使用。

是觉得有用，但实际上根本没有用到。

这种情况下，应当仔细分析业务逻辑，其中必定有不合理的地方。