【SQL性能优化】【范式设计与反范式设计】

范式设计

数据库的设计范式

关系型数据库总共有6种范式，按照范式级别从低到高有：

1. 第一范式（1NF）
2. 第二范式（2NF）
3. 第三范式（3NF）
4. 巴斯-科德范式（BCNF）
5. 第四范式（4NF）
6. 第五范式（5NF, 完美范式）

特点：高阶范式一定符合低阶范式的要求。越高阶，冗余度越低

设计数据表时，通常尽量满足3NF足矣，甚至有时我们会为了提高一些查询性能，选择破坏范式规则，这叫反规范化。

数据表的键

范式的使用主要来源于键，主键、候选键、超键等等，键是由一个或多个属性组成

我们从上图理解这些键的意思，

• 超键：包括唯一标识的属性集，如（ID,NAME）(S_ID,AGE)
• 候选键：最小超键，球员表就是(ID)和（S_ID），SID是身份证，ID是球员编码
• 主键：我们自己从候选键选一个
• 外键：球员表中的TEAM_ID
• 主属性：就是候选键，其他属性例如姓名年龄就是非主属性

1NF\2NF\3NF介绍

1. 1NF指数据表中任何属性都是不可拆分的，原子性的
2. 2NF指数据表里非主属性都要和这个数据表的候选键有完全依赖关系
3. 3NF指满足2NF下，对任何非主属性都不传递依赖于候选键

1NF：

这个很好理解，就是字段不可以拆分，字段X无法拆分成X1，X2这样，基本都会满足这要求

2NF：

意思是非主属性是完全依赖候选键，是通过候选键来查找的，不能仅仅依赖一部分

实例解释：球员比赛表

球员编号	姓名	年龄	比赛编号	比赛时间	比赛场地
1	qaq	15	01	4.9	洛杉矶

可以看出这个表格的候选键有“球员编号”与“比赛编号”，

得到关系：(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地)

但是数据表中存在以下对应关系：
(球员编号) → (姓名，年龄)
(比赛编号) → (比赛时间, 比赛场地)

就是说有一些字段只是依赖了部分的候选键，没有完全依赖所有的候选键，这样不符合2NF。

$这种情况会导致什么问题呢？$

1. 数据冗余
   一个球员参加m场比赛，球员姓名＆年龄重复m-1次
   一个比赛有n个球员参加，时间和地点重复n-1次

2. 插入异常
   添加新的比赛，但不知道有哪些球员，无法插入

3. 删除异常
   删除某个球员编号，如果没有单独的比赛表，会同时把比赛信息删掉

4. 更新异常
   若调整某个比赛时间，那么关于这个比赛的所有时间都需要更新，否则会出现一场比赛时间不同的情况

$2NF核心思想：一张表是一个独立对象，只表达一个意思$

3NF：

存在一张这样的表：

球员编号	姓名	球队名称	球队教练
1	qaq	abc	哈皮

球员编号决定了球队名称，但是球队名称也能决定球队教练，说明球队教练就会传递依赖于球员编号.
3nf的关键是看非主键列是直接依赖于主键不？

总结

其实上面的名词解释有点过多，我觉得不太好理解，我们应该用简单的话去描述会更加容易记忆。

1NF：把字段分开，姓名和年龄分开
2NF：加个主键，如id，所有列完全依赖这个id
3NF：加个外键，建个新表。因为有些属性不是完全依赖主键

缺点：范式越高，会设计越多数据表出来，那么查询时就要关联多张表，影响查询效率

额外题目练习

有一张学生选课表，包含的属性有学号、姓名、课程名称、分数、系别和系主任，如果要改成符合 3NF 要求的设计，需要怎么修改呢？
$答：$ 学号、课程名称、系别是候选键。分数不是完全依赖于学号，违反2NF。系主任违反3NF，不是直接依赖于学号主键。

拆分成4张表：
学生表：

学号	姓名	系别id

课程表：

课程id	课程名

成绩表：

学号	课程id	分数

院系表：

系别id	系别名称	系主任

反范式设计

BCNF（巴斯范式）

所有非主属性对于每一个候选键都是完全依赖，所有主属于对于每一个不包含它的候选键也是完全依赖。

反范式设计的场景

数据表越多，数据冗余度越低，但是为了性能和读取效率，有时候违反原则，允许少量的冗余，通过空间来换时间。

• 简单实例：有一个商品评论表，一个用户表，查询评论者、评论内容和评论时间
  查询评论者要去用户表获取用户名，查询时要关联两张表，进行聚集索引扫描再嵌套循环，这一操作会耗费较多时间，如果数据量不大，那么还好；如果是百万级别的数据，会响应很慢。
  如果直接在商品评论表添加用户名字段，那么就可以做到快速查询，只需做一次聚集索引

反范式设计可能存在的问题

1. 数据量小时，反映不出性能的优势，可能会导致数据库设计更加复杂，增加维护成本
2. 用户昵称频繁更新，需要执行存储过程来更新，会消耗系统资源

反范式设计适用场景

当冗余信息有价值或能大幅度提高查询效率，可以采用反范式去优化，例如订单中的各种用户信息。

数据仓库也常用这种设计，因为仓库通常是存储历史数据，不需要实时性很强的增删改，适当允许数据冗余度，方便进行数据分析