一、范式概述1.1 什么是范式范式Normal FormNF是关系数据库设计中的一组规范规则。遵从不同级别的规范可以设计出冗余度更低、异常更少的关系型数据库表结构。范式的核心目标是减少数据冗余、消除插入 / 删除 / 更新异常。1.2 六种范式关系数据库共有六种范式级别依次升高范式全称核心要求1NF第一范式列不可再分原子性2NF第二范式消除非主属性对候选键的部分函数依赖3NF第三范式消除非主属性对候选键的传递函数依赖BCNF巴斯 - 科德范式消除主属性对候选键的部分 / 传递依赖每个决定因素都是候选键4NF第四范式消除非平凡多值依赖5NF第五范式完美范式消除连接依赖范式越高数据冗余越小但表拆分越多查询时可能需要更多 JOIN。1.3 范式与性能的权衡范式高冗余少、一致性好、写入异常少但查询可能需要多表 JOIN增加查询复杂度和 IO范式低反范式冗余多、查询快单表即可但写入时需要维护多处数据的一致性存在更新异常风险。实际应用中通常满足 3NF 即可在高并发、读多写少的互联网场景有时会故意反范式增加冗余字段来减少 JOIN、提升查询性能。二、核心概念铺垫在讲解范式之前必须先理解几个关键概念。2.1 函数依赖函数依赖是指在一张表中如果属性字段X 的值确定后属性 Y 的值也唯一确定就称Y 函数依赖于 X记作X → Y。例如学生表中学号确定后姓名就唯一确定 →学号 → 姓名。三种函数依赖类型定义示例完全函数依赖X→Y且 X 的任何真子集都不能决定 Y(学号课程号) → 成绩缺了哪个都不行部分函数依赖X→Y但 X 的某个真子集就能决定 Y(学号课程号) → 姓名其实学号 alone 就能决定姓名传递函数依赖X→YY→Z且 Y 不能反过来决定 X则 X→Z 是传递依赖学号→班级号班级号→班主任姓名 → 学号→班主任姓名传递2.2 候选键、主键、主属性、非主属性概念定义候选键Candidate Key能唯一标识表中一行记录的最小属性集合。可以是单个字段也可以是多个字段的组合。一张表可以有多个候选键。主键Primary Key从候选键中选一个作为行的唯一标识一张表只能有一个主键。主属性Prime Attribute出现在任何一个候选键中的属性。非主属性Non-prime Attribute不出现在任何候选键中的属性。示例学生表学号身份证号姓名年龄候选键有两个{学号}和{身份证号}都能唯一标识一个学生选{学号}作为主键主属性学号、身份证号出现在候选键中非主属性姓名、年龄。三、第一范式1NF3.1 定义数据库表中的每一列都是不可再分的原子数据项不能是集合、数组、对象等非原子数据。换句话说每一行的每一列只存一个值且这个值不能再拆分成更小的有意义的数据。3.2 反例与正例反例不满足 1NF学号姓名联系方式001张三138xxxx, 025-xxxx联系方式 一列存了手机号和座机号两个值可以再拆分 → 不满足 1NF。正例满足 1NF学号姓名手机号座机号001张三138xxxx025-xxxx拆成两列每列只存一个原子值 → 满足 1NF。3.3 关于 SET 类型MySQL 的SET类型可以在一列中存储多个值如阅读,运动,音乐这天然违反 1NF。但这不代表绝对不能用 SET如果业务场景中这个字段就是作为一个整体使用、从不单独查询其中某个值且能在冗余和效率之间做出权衡SET 仍可使用。四、第二范式2NF4.1 定义在满足 1NF 的前提下消除非主属性对候选键的部分函数依赖。换句话说表中每一个非主属性都必须完全函数依赖于整个候选键而不能只依赖候选键的一部分。如果主键是单一字段非复合主键则天然满足 2NF—— 因为单一字段不存在 部分依赖 的问题。4.2 部分函数依赖的判定假设复合主键是(A, B)如果某个非主属性 C只靠 A 就能确定不需要 B那 C 就部分依赖于主键 → 不满足 2NF如果 C 必须同时靠 A 和 B 才能确定那 C 完全依赖于主键 → 满足 2NF。4.3 反例分析样例 将所有字段填入一个表用(学生id, 班级名称)作为复合主键。学生 id班级名称学生姓名学生性别班级教室班主任分析各字段的依赖关系学生姓名、学生性别只靠学生id就能确定 →部分依赖不需要班级名称班级教室、班主任只靠班级名称就能确定 →部分依赖不需要学生 id没有任何非主属性是完全依赖于整个复合主键的。→不满足 2NF。4.4 不满足 2NF 导致的异常异常类型定义说明示例插入异常本该可以正常录入的数据由于缺少主键的组成部分违反主键非空约束导致无法插入想插入一个还没有学生的班级无法插入因为学生 id 是主键的一部分不能为空新建了一个班级但还没招生无法录入班级信息删除异常删除某一条数据时连带删除了本不该删除的其他实体信息造成非预期的数据丢失删除某班所有学生时班级信息也一起被删掉了某届学生全部毕业后删除学生记录班级信息也没了更新异常修改某一个属性值时需要同时修改多行数据只要有一行漏改就会产生数据不一致修改班级信息时需要修改所有该班学生的行漏改一处就不一致班级换教室需要更新该班几十个学生的行数据冗余同一个班级的信息在每个学生行中重复存储50 个学生的班级班级名称 / 教室 / 班主任重复存 50 遍核心本质不满足第二范式引发的插入、删除、更新异常根源是将本应独立的多个实体强行揉合进同一张关系表破坏了 “一表一主题” 的设计原则使不同实体的数据与操作产生了不必要的强制绑定。往往是m:n关系的实体范式逻辑对应 不满足 2NF 的核心特征是非主属性对复合候选键存在部分函数依赖非主属性的实际决定因素只是候选键中的一部分主属性和候选键里的另一部分主属性并无依赖关系但在存储层面该非主属性必须与完整的候选键绑定共同构成一行数据。五、第三范式3NF5.1 定义在满足 2NF 的前提下消除非主属性对候选键的传递函数依赖。换句话说非主属性必须直接依赖于候选键不能通过其他非主属性间接依赖传递依赖。5.2 传递函数依赖的判定如果存在候选键 → A → B且 A 不能反过来决定候选键表明A不是候选键则 B 传递依赖于候选键 → 不满足 3NF。往往是将两个1n关系的实体揉合在一张表中5.3 反例分析样例 如果只用学生id作为单一主键学生 id学生姓名所属班级班级名称班级教室班主任主键是单一字段学生id→ 天然满足 2NF但依赖关系学生id → 所属班级所属班级 → 班级名称/教室/班主任所以班级名称/教室/班主任通过所属班级传递依赖于学生id→不满足 3NF。5.4 满足 3NF 的设计拆成两张表班级表主表班级 id主键班级名称班级教室班主任学生表从表学生 id主键学生姓名学生性别所属班级 id外键班级表中所有字段都直接依赖于班级id学生表中所有字段都直接依赖于学生id所属班级 id 是外键直接依赖学生 id消除了传递依赖 →满足 3NF。满足 3NF 后插入异常、删除异常、更新异常和数据冗余都得到了显著改善。六、BCNF巴斯 - 科德范式6.1 定义BCNF 是 3NF 的增强版。在满足 3NF 的前提下对于任何非平凡的函数依赖 X→YX 必须是候选键。换句话说每个决定因素能决定其他属性的属性都必须是候选键不允许非候选键的属性去决定其他属性。6.2 3NF 与 BCNF 的区别3NF 允许主属性对候选键的部分依赖或传递依赖BCNF 连主属性的部分 / 传递依赖也消除了。简单记3NF 是 非主属性不传递依赖于候选键BCNF 是 所有属性包括主属性都不传递 / 部分依赖于候选键。6.3 示例1. 关系模式与业务规则关系表STJ(学生编号 S, 教师编号 T, 课程编号 J)业务规则每位教师只讲授一门固定课程一门课程可以由多位不同的教师讲授每个学生选修某一门课程时对应一位固定的授课教师学生可以选修多门课程。2. 函数依赖根据业务规则推导T → J教师编号可以唯一确定课程一个老师只教一门课(S, J) → T学生 课程的组合可以唯一确定授课教师(S, T) → J学生 教师的组合可以唯一确定对应课程3. 候选键与主属性候选键 1(S, J)—— 能唯一标识一行记录且去掉任意一个字段都失去标识能力候选键 2(S, T)—— 能唯一标识一行记录且去掉任意一个字段都失去标识能力主属性S、T、J三个属性都出现在候选键中全都是主属性非主属性无4. 范式判定满足 3NF3NF 的约束是「消除非主属性对候选键的部分 / 传递依赖」。本例中没有非主属性天然满足 3NF 的全部要求。违反 BCNFBCNF 的核心规则是「关系中所有决定因素都必须是候选键」。 本例中存在函数依赖T → J决定因素是T但T 单独不是候选键T 只能确定课程 J无法确定学生 S不能唯一标识整行记录。 本质是存在「主属性 J 对 候选键 (S,T) 的部分函数依赖」因此违反 BCNF。5. 违反 BCNF 带来的异常插入异常新增一位教师并确定其授课课程时只要还没有学生选修这门课就无法插入数据S 是主属性不能为空。删除异常删除某一名学生的选课记录会连带删除掉「该教师教授这门课」的关联信息。更新异常某教师更换授课课程所有选该教师课程的学生记录都需要同步修改漏改就会数据不一致。6. 修正为 BCNF 的方式将原表拆分为两张表消除不合理的依赖教师课程表(T, J)主键T学生选课表(S, T)主键(S, T) 拆分后两张表的所有决定因素都是候选键均满足 BCNF。七、反范式设计7.1 什么时候需要反范式范式越高表拆得越细查询时 JOIN 越多。在以下场景有时会故意违反范式、增加冗余场景反范式做法目的读多写少、查询频繁在订单表中冗余存储用户名、商品名查询订单时不用 JOIN 用户表、商品表统计报表预先计算好统计值存入汇总表避免每次查询都做复杂聚合历史数据不可变订单快照保存下单时的商品价格商品价格变动不影响历史订单7.2 反范式的代价数据一致性维护成本高修改冗余字段时需要同时更新所有存储该字段的表写入性能下降一次写入可能涉及多张表的更新存在不一致风险如果更新时漏了某处就会出现数据不一致。反范式是用空间和一致性维护成本换查询性能需要根据业务场景权衡不能盲目反范式。八、E-R 图8.1 什么是 E-R 图E-R 图Entity-Relationship Diagram实体 - 关系图是一种用于描述数据模型的概念图主要用于数据库设计阶段帮助理清实体、属性和实体之间的关系。8.2 基本组成元素表示含义类比实体Entity矩形框数据对象如学生、班级、课程类似表属性Attribute椭圆形 / 圆角矩形实体的特性如学生的姓名、年龄类似字段关系Relationship菱形框实体之间的联系并标注关系类型类似外键 / 中间表8.3 关系类型1. 一对一1:1一个实体 A 的实例对应另一个实体 B 的一个实例反之亦然。示例用户 和 账户用户属性昵称、头像、手机号、地址……账户属性登录用户名、密码设计方式可以在任一实体中保存关联另一实体的字段外键。通常把不常用的字段拆分到另一张表减少主表的宽度。2. 一对多1:N/ 多对一N:1一个实体 A 的实例对应多个实体 B 的实例但一个实体 B 的实例只对应一个实体 A 的实例。示例班级 和 学生一个班级有多个学生一个学生只属于一个班级。设计方式在多的一方学生表保存外键关联一的一方班级表的主键。3. 多对多M:N一个实体 A 的实例对应多个实体 B 的实例反之亦然。示例学生 和 课程一个学生选多门课一门课被多个学生选。设计方式必须引入中间表也叫连接表 / 关联表将多对多拆成两个一对多。8.4 多对多的中间表设计以学生选课为例引入成绩表作为中间表一个学生有多个成绩 → 学生成绩 1:N一个课程有多个成绩 → 课程成绩 1:N成绩表字段字段说明id主键自增student_id外键关联学生表course_id外键关联课程表score成绩中间表的主键可以是自增 id也可以用(student_id, course_id)作为复合主键确保一个学生一门课只有一个成绩。8.5 EE-R图: 从 SQL 反推 E-R 图也可以通过已有的建表 SQL 反向生成 E-R 图逆向工程。注意工具是通过外键约束来识别表之间的关系的如果建表时没有建立外键只是逻辑上有关联工具不会自动连线因此逆向生成的 E-R 图不一定完全符合设计意图需要人工核对。工具推荐MySQL Workbench免费官方工具支持逆向工程Navicat付费也有 E-R 图功能draw.io / diagrams.net免费手动绘制。