我刚入行那年第一次参加项目评审就见到一张让人血压升高的表。表名叫customer_info里面有个字段叫other_contact存的内容长这样“微信:xxx;QQ:12345;紧急联系人:张三(13800000000)”。旁边还并排开着phone1、phone2、phone3三个字段问业务方当初为什么这么设计答曰想到什么加什么图省事。后来这个系统的下场不难猜上线三个月数据乱得没法看。同一个客户的手机号在phone1和other_contact里存了两个版本客服改完phone1忘了改other_contact订单统计怎么都对不上最后不得不停掉业务专门花两周时间重做表结构。这件事给我的教训很深数据库设计这件事看似是教科书里的基础理论实际上直接决定了一个系统能活多久、改起来疼不疼。这篇文章我想用大白话把这些数据库设计的基础理论讲清楚不堆概念只讲逻辑再附上我这些年实际建表、拆表、改表时攒下的经验和教训。适合刚接触数据库的同学、写过 SQL 但没系统学过设计的开发以及被烂表折磨过、想回头补课的团队负责人。1. 不设计的代价冗余、异常与一张让人崩溃的表1.1 一个表结构翻车的实例很多人觉得建表就是“业务有什么字段就加什么字段”这句话本身没错错的是只想着加字段不想字段之间的关系。最典型的翻车现场就是我在评审会上看到的那种customer_info表。我拿一个简化版举例你先感受一下这种结构的杀伤力CREATE TABLE customer_info ( id INT PRIMARY KEY, name VARCHAR(50), phone1 VARCHAR(20), phone2 VARCHAR(20), phone3 VARCHAR(20), other_contact TEXT, order_detail VARCHAR(500) );这个表的问题一眼就能看出来phone1、phone2、phone3是明显的“字段拆数字”操作如果客户有第四个手机号呢改表加字段。other_contact更夸张把所有联系方式揉成一个字符串想按微信号查客户用LIKE %xxx%扫全表。真正致命的是order_detail里面塞的是“订单号-商品-金额”的拼接串。这种设计等于把订单数据“藏”在了客户表里后续所有跟订单相关的查询都变成字符串解析JOIN直接失去意义统计报表基本靠人工。1.2 更新与删除异常数据不一致的根源教科书里讲数据库设计时会提到三类异常更新异常、插入异常、删除异常。这三个词听着抽象其实用刚才的customer_info一解释就通了。先说更新异常。假设客户张三有两个订单每个订单的明细都写在order_detail里。某天商品改名了订单里却还留着旧名字你得先把订单字符串拆出来改完再拼回去。但凡有一个订单漏改同一个客户的订单历史就出现了两个版本的商品名。这种“改一处忘一处”的矛盾就是数据冗余导致的更新异常。插入异常更隐蔽。假设我们要记录一个刚注册但还没下过单的客户如果表结构把订单信息强行绑在客户行里那这个客户没有任何订单时订单字段只能留空想录入都挤不进去。反过来如果删掉某个订单连带客户的联系方式也被删了这就是删除异常。这三个异常的本质原因只有一个数据被放在了一个错误的位置。数据库设计的基础理论本质上就是研究怎么把数据放到正确的位置上去。2. 设计流程的整体框架四个阶段与每阶段的产出物2.1 需求分析先问清楚业务规则很多人跳过了需求分析上来就建表这是最贵的一个错误。需求分析阶段的核心任务不是列字段而是搞懂业务规则。你要问业务方的问题大致是这些一个客户可以下几个订单一个订单包含几种商品订单取消后记录是保留还是删掉客户联系方式允许多个吗这些问题听起来琐碎但它们的答案直接决定了表结构怎么设计。比如“客户联系方式允许多个”这条规则一旦答案是“允许”你的表结构就不可能再把phone放成单字段而是需要单独的联系方式表。我在实际项目里经常做的一件事就是把需求分析得到的业务规则写成清单建表之前逐条对照规则说不清楚的表结构方案一律不通过。2.2 概念设计画 ER 图时把业务装在图形里需求分析完成之后进入概念结构设计阶段。这个阶段最典型的产出是ER 模型也就是实体-联系图。为什么要用图形来表达因为图比文字更能暴露逻辑漏洞。比如“客户”和“订单”之间是什么关系如果业务规则是“一个客户可以下多个订单一个订单只属于一个客户”图上画出来的就是一对多关系。如果规则是“一个订单可以由多个客户共同拼单一个客户也有多个订单”那就是多对多关系。这个区分如果不在概念设计阶段想清楚到了逻辑设计阶段表结构会来回推翻好几遍。概念设计阶段不要急着写 SQL先用实体、属性、联系把业务模型画出来。画图的过程本身就是梳理业务的过程。2.3 逻辑设计把模型翻译成表和约束概念模型确定后逻辑设计阶段的任务是把 ER 图翻译成关系模式也就是具体的表结构。这一步要做的事包括确定每个表的主键把多对多关系拆成中间表把属性分配到正确的实体上定义外键约束。这个阶段要特别留意规范化。我见过很多团队在逻辑设计阶段直接照抄概念模型导致一张表里既有客户信息又有订单信息违背了基本的拆分原则。逻辑设计阶段输出的应该是一份完整的表结构清单而不是一张 ER 图画完就草草建表。2.4 物理设计索引、存储与查询路径逻辑设计决定“表长什么样”物理设计决定“数据怎么存、怎么查得快”。物理设计阶段要考虑的问题包括哪些字段需要建索引主键用自增还是 UUID数据量大不大、需不需要分区表的存储引擎怎么选。物理设计有一个重要原则先保证逻辑正确再优化物理性能。很多人一上来就纠结索引优化却忽略了表本身结构混乱索引加再多也白搭。四阶段的关系可以用下面这个表格概括阶段核心任务主要产出常见错误需求分析搞清业务规则与数据流业务规则清单跳过此阶段直接建表概念结构设计用 ER 模型表达业务概念模型ER 图把联系和实体混在一起逻辑结构设计转成关系模式并规范化表结构清单、约束定义忽视范式一张表塞所有字段物理结构设计决定存储、索引、分区DDL 脚本、索引策略在错误表结构上盲目加索引这个流程不是死板的瀑布式。业务变化快的项目里这四个阶段经常要反复迭代但有一点不会变逻辑设计必须先于物理设计需求分析必须先于模型设计。顺序一旦颠倒返工的代价是成倍的。3. 理论地基实体、属性、联系与关系模型里的关键概念3.1 实体与属性用对象视角看数据数据库设计的基础理论里实体和属性是一对最核心的概念。实体是现实世界中可以独立存在的对象比如某个客户、某张订单属性是这个实体的特征比如客户的姓名、订单的金额。用生活化一点的说法来理解实体是“名词”属性是“修饰名词的形容词”。你把现实中的对象识别成一个个实体再把实体的特征拆成属性这个过程本身就是建模。实体和属性的划分有一个很实用的判断标准如果一个“特征”本身还有特征那它更可能是实体而不是属性。比如“商品”有一个特征叫“分类”而“分类”下面还有“分类名称”“分类编码”“上级分类”这时候分类就应该单独做成实体而不是商品表里的一个字段。3.2 键与主键编号不是随便选的实体要有唯一标识这个唯一标识在数据库里叫主键也叫主码。主键的选择看似简单实际上坑很多。自增主键最简单性能好、占用空间小但它在分布式场景下有冲突风险。UUID 主键全局唯一适合分库分表但它无序且长度大会对 B 树索引的写入性能产生负面影响。业务主键比如身份证号最大的问题是“业务属性会变”而且主键一旦掺入业务语义后续改起来就非常痛苦。我的建议是绝大多数场景下用无业务含义的自增或 UUID 做主键把业务唯一标识做成独立字段并加唯一索引。这样既保证行的唯一性又保留业务查询的灵活性。3.3 联系的类型一对一、一对多与多对多实体之间不是孤立的联系描述的就是实体之间的关联方式常见三种一对一、一对多、多对多。一对一联系在数据库里相对少见但也不是没有。比如“用户”和“用户登录凭据”通常是一对一这种联系可以合在一张表里也可以拆开。一对多联系最常见。“一个客户有多个订单”就是典型的一对多体现方式是在“多”的那一方订单表加外键指向“一”的那一方客户表。多对多联系则要复杂一些。比如“一个学生可以选多门课程一门课程可以被多个学生选择”如果直接在学生表里加课程字段或者反过来都会造成大量冗余。正确的做法是拆出一张中间表选课表中间表里存学生主键和课程主键以及选课相关的属性比如成绩。三种联系对比一下联系类型例子表结构实现一对一用户与登录凭据可合并一张表或共享主键一对多客户与订单“多”方表加外键多对多学生与课程中间表关联两方主键3.4 完整性约束外键、非空、唯一是怎么保护数据的关系模型之所以能保证数据可靠靠的不是开发者的自觉而是数据库的完整性约束。主键约束保证行的唯一性唯一约束保证业务上不允许重复的值比如登录名不重复非空约束保证关键字段不会被漏填外键约束则保证引用关系的正确性。外键约束在真实项目里有个争议有人觉得外键影响插入性能干脆不用外键靠应用层保证。我的观点是小型项目必须用外键约束它是数据一致性的最后防线大型高并发项目如果因为性能考虑不用外键也一定要在代码规范和审核里明确引用关系不能两边都不管。4. 范式理论从第一范式到第三范式以及什么时候该打破4.1 第一范式字段原子性与“可再分”的边界范式理论是数据库设计基础理论里最硬核的部分也是面试常客。很多人背得下定义但用不上我要试着把它讲成能直接指导建表的工具。第一范式的核心要求是字段原子性也就是每个字段不能再分成更小的独立部分。拿前文的other_contact举例它存“微信:xxx;QQ:12345;紧急联系人:张三”这就在一个字段里塞了多个独立信息违反第一范式。但“可再分”要看业务视角。比如“地址”这个字段在只做标签展示的业务里可以当一个原子字段但在需要按省份、城市做统计的物流系统里就必须拆成省、市、区、详细地址等多字段。所以第一范式不是绝对的它取决于业务是否需要单独操作某个子信息。违反第一范式的典型后果是想查微信号得先截字符串想按城市统计得先做分隔解析字段一旦存入格式多样的内容查询和更新都会变得极其痛苦。4.2 第二范式部分函数依赖带来的麻烦第二范式建立在第一范式之上核心是消除非主属性对码的部分函数依赖。这句话读起来像天书我用选课的例子拆开讲。假设有一张选课表CREATE TABLE sc_info ( sid INT, -- 学生ID sname VARCHAR(50), -- 学生姓名 cid INT, -- 课程ID cname VARCHAR(50), -- 课程名称 score INT, -- 成绩 PRIMARY KEY (sid, cid) );这张表的主键是(sid, cid)联合主键。请问学生姓名的唯一决定因素是什么是sid不是cid。也就是说sname只要sid就能确定不需要整个主键。这种“只需要主键的一部分就能确定”的情况就是部分函数依赖它违反了第二范式。部分依赖带来了什么后果同一个学生在选 10 门课的情况下sname会重复存 10 次。如果他改名你要 UPDATE 10 行漏一行就是数据不一致。修正方法是拆表把学生基本信息拆到student表课程信息拆到course表选课关系单独留在sc表里CREATE TABLE student ( sid INT PRIMARY KEY, sname VARCHAR(50) ); CREATE TABLE course ( cid INT PRIMARY KEY, cname VARCHAR(50) ); CREATE TABLE sc ( sid INT, cid INT, score INT, PRIMARY KEY (sid, cid) );这个拆法就是前面提到的“多对多中间表”的理论依据。你看基础理论并不是考场里背完就扔的东西它真的能指导实际设计。4.3 第三范式传递依赖的隐形坑第三范式要求消除非主属性对码的传递函数依赖。继续用例子说。假设有一张订单表CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, customer_name VARCHAR(50), customer_address VARCHAR(100), order_time DATETIME, total_amount DECIMAL(10,2) );主键是order_id。customer_name和customer_address是谁决定的它们其实由customer_id决定。也就是说通过customer_id这个非主属性customer_name间接依赖于主键order_id这就是传递依赖。传递依赖的问题在于一个客户下了 10 个订单客户地址就存了 10 份。现在客户搬家了你需要更新这 10 行漏掉一行这个客户的地址在不同订单里就互相矛盾。这种“改了不知道改全了没有”的局面对运营系统来说是致命的。第三范式的修正方法是把客户独立拆成表订单表里只留customer_id外键CREATE TABLE customer ( customer_id INT PRIMARY KEY, customer_name VARCHAR(50), customer_address VARCHAR(100) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_time DATETIME, total_amount DECIMAL(10,2), FOREIGN KEY (customer_id) REFERENCES customer(customer_id) );为了让你快速对照我把三个范式的检查逻辑整理成了表格范式核心要求检查方法违反后果1NF属性原子性字段是否还能再分查询需字符串解析统计困难2NF消除部分依赖联合主键下非主属性是否只依赖部分主键数据重复存储改名要改多行3NF消除传递依赖非主属性是否通过其他非主属性间接依赖主键同一事实在多行重复更新易漏实际工作中我很少让人把范式背出来而是让他们管住两条直觉第一一张表只描述一个实体第二非主键字段只能依赖主键。这两条做到2NF 和 3NF 基本稳了。4.4 反范式是专业判断不是偷懒借口讲完三个范式必须泼一盆冷水现实系统不是范式越高越好。高范式意味着更多表连接查询而表连接是有代价的。在很多读多写少、查询路径固定的场景里合理的反范式反而更优。最典型的案例是订单明细里的商品信息快照。按三范式订单明细应该只存商品 ID商品名称和单价都去商品表 JOIN。但问题是商品改名、调价以后历史订单里应该保留的是下单当时的商品名和单价而不是现在商品表里的值。如果在订单明细里直接冗余一份product_name、unit_price这恰恰是数据准确性的保证而不是缺陷。所以判断一个冗余该不该留标准很简单它记录的是“这个字段当前的最新值”还是“这个业务事件发生时刻的历史事实”。前者是违反范式的危险冗余后者是合理的快照设计两者不要混为一谈。5. 设计成果怎么落地ER 图、数据字典与评审清单5.1 ER 图的绘制规范与检查概念设计阶段画 ER 图很多人画完就丢没有把画图的价值榨干。我建议画图时固定下来几个规范实体用矩形表示属性用椭圆表示联系用菱形表示一对多关系的“多”那一端一定要明确外键放在哪张表里。画完图还要做一轮检查重点问自己三个问题第一每个实体是否有主键第二每个联系是否有正确的基数一对一还是一对多第三有没有出现“一张表既当实体又当联系”的模糊设计。这些问题检查完ER 图才算真正能用。5.2 数据字典的字段信息设计建表之前我强烈建议先写数据字典。数据字典不只是一个字段清单而是每个字段的“身份证”。以订单表为例数据字典大致长这样字段名数据类型允许NULL默认值业务含义来源/备注order_idBIGINT否自增订单唯一标识系统生成customer_idBIGINT否无下单客户的ID引用 customer 表order_timeDATETIME否CURRENT_TIMESTAMP下单时间系统生成total_amountDECIMAL(12,2)否0订单应付总金额由明细汇总statusTINYINT否0订单状态0待支付 1已支付 2已取消业务方定义写数据字典最大的价值在于强迫设计者说清楚每个字段的业务含义。很多表结构设计的问题在写“业务含义”这一列时自己就能暴露出来比如一个status字段如果只能写“状态”说明业务规则根本没理清。5.3 上线前评审自问这七个问题表结构设计完我习惯在评审前过一遍检查清单这里分享出来每张表是否都有主键主键是否稳定且无业务语义是否存在字段能拆到另一张表里让本表更纯粹外键关系是否正确反映了业务规则历史事实和实时引用是否分清楚了字段类型是否合理金额是不是用了定点数而不是浮点是否有明显重复存储且不是业务快照的字段命名是否清晰能否只看表名和字段名就理解业务含义这七个问题我几乎每次评审都在用。很多烂表其实只需要在评审阶段被问一次“为什么”就能避免后面几个月的返工。6. 多年建表踩坑的真实教训主键、类型和扩展性6.1 主键三选一自增、UUID 还是业务键主键设计是很多项目埋雷最多的地方。自增主键最省心但做数据迁移、跨库合并时容易冲突UUID 分库分表友好但无序 UUID 会让主键索引频繁页分裂写入性能下降明显。如果一定要用 UUID建议用有序 UUID比如雪花算法生成的 64 位整数既保留全局唯一又对索引友好。业务键比如身份证号、手机号我很不推荐作为主键因为业务键会变一旦历史数据已经用它做了关联改起来就是大工程。业务唯一标识可以加唯一索引但不要动主键。6.2 字段类型与精度金额和时间的那些坑字段类型选错是最常见也最隐蔽的坑。金额字段必须用DECIMAL千万别用FLOAT或DOUBLE。浮点数是近似存储账算多了会出现“0.1 0.2 0.30000000000000004”的问题放到金额上就是实打实的资损事故。时间字段的坑在于时区。如果你用本地时间存业务表系统部署到其他区域后同一时刻的数据时间会对不上。我现在的习惯是数据库统一存 UTC 时间展示层再做时区转换这样不管服务器在哪时间语义都不会乱。字符串长度也别拍脑袋。太短会导致后期ALTER TABLE改长度锁表太长又浪费空间影响索引性能。建议根据业务真实边界预留 20% 余量。6.3 通用字段和扩展性习惯最后分享几个我建表时一定会带的通用字段created_at、updated_at、软删除标记比如deleted_at。这三个字段的成本很低但带来的收益很大。created_at和updated_at帮你定位数据问题的发生时间软删除标记用来替代物理删除至少保留一段时间的历史防止误删和审计需要。还要养成的习惯是建表时顺手写注释。表要有表的注释字段要有字段的注释。好的注释能解决“这个人离职了这张表没人敢动”的问题。还有一种更省心的做法建表时就把字段业务含义录入数据字典和后续的迭代记录放在一起这样设计意图就能长期保留下来。说回开头那张customer_info表项目重做的时候业务方问我要不要保留other_contact字段。我说字段本身可以留但它应该被拆成一张联系方式子表主表只留客户最核心的信息。业务方还觉得奇怪联系方式那么多拆表不会更麻烦吗后来用了大半年他们自己也承认拆完表之后查某个微信号、统计联系方式类型都变成一行 SQL 的事再也不用在字符串里大海捞针了。这就是数据库设计基础理论的价值它不是在纸上画模型给你看的而是让你在建表之前能多想几步把将来要付出的返工成本提前用思考去对冲掉。每次建新表之前你多问自己一句“这个字段放在这张表里真的对吗”数据库设计就已经在起作用了。