MySQL进阶设计优化 范式设计

一、基本介绍

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。一般说来，数据库只需满足第三范式(3NF)就行了。

范式的包含关系。一个数据库设计如果符合第二范式，一定也符合第一范式。如果符合第三范式，一定也符合第二范式…

数据库基本概念

要理解范式，首先必须对知道什么是关系数据库，简单的说：关系数据库就是用二维表来保存数据。表和表之间可以……（省略10W字），如果对数据库很熟悉，可以不用理会下面的概念。

实体：现实世界中客观存在并可以被区别的事物。比如“一个学生”、“一本书”、“一门课”等等。值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”，它也可以是虚拟的，不如说“老师与学校的关系”。

属性：教科书上解释为：“实体所具有的某一特性”，由此可见，属性一开始是个逻辑概念，比如说，“性别”是“人”的一个属性。在关系数据库中，属性又是个物理概念，属性可以看作是“表的一列”。

元组：表中的一行就是一个元组。

分量：元组的某个属性值。在一个关系数据库中，它是一个操作原子，即关系数据库在做任何操作的时候，属性是“不可分的”。否则就不是关系数据库了。

码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。

全码：如果一个码包含了所有的属性，这个码就是全码。

主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。

非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。

外码：一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。

候选码： 若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何真子集都不能再标识，则称该属性组为（超级码）候选码。

二、6种范式

前面说到，范式越高，数据的冗余度越小。其实没有冗余的数据库设计是可以做到的。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。（最典型的就是在一些数据表中不仅存作为外键的user_id,同样存user_name,这样虽然违反数据库范式增加了user_name字段，但是却提高了效率，减少了获取user_id后再去user表中获取user name的操作）

所以实际中，我们只需要考虑数据库满足第三范式就可以了,下面以最通俗的方式来解释数据库的范式。

第一范式（1NF）：属性不可分

1NF的定义为：符合1NF的关系中的每个属性都不可再分

例子一:

如上图所示的情况，就不符合1NF的要求。

实际上，1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在RDBMS中已经存在的数据表，一定是符合1NF的。如果我们要在RDBMS中表现表中的数据，就得设计为如下图的形式：

例子二:

如上图所示的情况，就不符合1NF的要求。

如果我们要在RDBMS中表现表中的数据，就得设计为如下图的形式：

第二范式（2NF）：符合1NF，并且非主属性完全依赖于码。

• 满足1NF，非主属性完全函数依赖于候选码

一个候选码中的主属性也可能是好几个。如果一个主属性，它不能单独做为一个候选码，那么它也不能确定任何一个非主属性。

给一个反例：我们考虑一个小学的教务管理系统，学生上课指定一个老师，一本教材，一个教室，一个时间，大家都上课去吧，没有问题。那么数据库怎么设计？(学生上课表)

一个学生上一门课，一定在特定某个教室。所以有（学生，课程）－>教室

一个学生上一门课，一定是特定某个老师教。所以有（学生，课程）－>老师

一个学生上一门课，他老师的职称可以确定。所以有（学生，课程）－>老师职称

一个学生上一门课，一定是特定某个教材。所以有（学生，课程）－>教材

一个学生上一门课，一定在特定时间。所以有（学生，课程）－>上课时间

因此（学生，课程）是一个码。

然而，一个课程，一定指定了某个教材，一年级语文肯定用的是《小学语文1》，那么就有课程－>教材。（学生，课程）是个码，

(学生，课程)－>教材 有 课程－>教材 存在非主属性 对码(学生，课程)s的部分函数依赖。

课程却决定了教材，这就叫做不完全依赖，或者说部分依赖。出现这样的情况，就不满足第二范式 ！有什么不好吗？你可以想想：

• 校长要新增加一门课程叫“微积分”，教材是《大学数学》，怎么办？学生还没选课，而学生又是主属性，主属性不能空，课程怎么记录呢，教材记到哪呢? ……郁闷了吧?(插入异常)

• 下学期没学生学一年级语文（上）了，学一年级语文（下）去了，那么表中将不存在一年级语文（上），也就没了《小学语文1》。这时候，校长问：一年级语文（上）用的什么教材啊？……郁闷了吧?(删除异常)

• 校长说：一年级语文（上）换教材，换成《大学语文》。有10000个学生选了这么课，改动好大啊！改累死了……郁闷了吧？(修改异常)

那应该怎么解决呢？们必须消除这些部分函数依赖，只有一个办法，就是将大数据表拆分成两个或者更多个更小的数据表，在拆分的过程中，要达到更高一级范式的要求，这个过程叫做”模式分解“。模式分解的方法不是唯一的，以下是其中一种方法：将一个表分解成两个或若干个表。

学生表

课程表

（这里还涉及到一个如何进行模式分解才是正确的知识点，先不介绍了）
现在我们来看一下，进行同样的操作，是否还存在着之前的那些问题？

1. 一年级语文（上）换教材，换成《大学语文》
   只需要修改一次一年级语文（上）对应的教材的值即可。——有改进
2. 下学期没学生学一年级语文（上）了，学一年级语文（下）去了，那么表中将不存在一年级语文（上），也就没了《小学语文1》。
   该教材尚在。——有改进
3. 删除老师中所有的学生记录
   该老师的信息仍然全部丢失。——无改进
4. 插入一个尚无学生的新老师的信息。
   因为学生表的码是学生和课程，不能为空，所以此操作不被允许。——无改进

所以说，仅仅符合2NF的要求，很多情况下还是不够的，而出现问题的原因，在于仍然存在非主属性系主任对于码学号的传递函数依赖。为了能进一步解决这些问题，我们还需要将符合2NF要求的数据表改进为符合3NF的要求。

第三范式（3NF）：符合2NF，并且消除了非主属性对于候选码的传递函数依赖。

• 如果存在非主属性对于码的传递函数依赖，则不符合3NF的要求。

接下来我们看看上面2NF图中的设计，是否符合3NF的要求。
对于课程表，主码为课程，主属性为课程，非主属性只有一个，为教材，不可能存在传递函数依赖，所以表2的设计，符合3NF的要求。
对于学生表，主码为(学生,课程)，主属性为学生和课程，非主属性为老师、老师职称和教室。
因为(学生,课程) → 老师，同时老师 → 老师职称，所以存在非主属性系老师对于码(学生,课程)的传递函数依赖，所以表1的设计，不符合3NF的要求。
为了让数据表设计达到3NF，我们必须进一步进行模式分解为以下形式：
学生表（学生，课程，老师，教室，上课时间）
课程表（课程，教材)
老师表（老师，老师职称）
对于课程表，符合3NF的要求，之前已经分析过了。
对于学生表，码为(学生，课程)，主属性为学生和课程，非主属性为老师、教室和上课时间，不可能存在非主属性对于码的传递函数依赖，所以符合3NF的要求。
对于老师表，码为老师，主属性老师，非主属性为老师职称，不可能存在非主属性对于码的传递函数依赖（至少要有三个属性才可能存在传递函数依赖关系），所以符合3NF的要求。

学生表

课程表

老师表

现在我们来看一下，进行同样的操作，是否还存在着之前的那些问题？

1. 删除某个老师中所有的学生记录
   该老师的信息不会丢失。——有改进
2. 插入一个尚无学生的新老师的信息。
   因为老师表与学生表目前是独立的两张表，所以不影响。——有改进

BCNF：符合3NF，并且消除主属性对于码的部分与传递函数依赖

• 满足3NF，消除每一属性对候选码的部分与传递依赖

举例一: 若一张表的数据包括：“书号、书名、作者”其中，书号是唯一的，书名允许相同，一个书号对应一本书。一本书的作者可以多个，但是同一个作者所参与编著的书名应该是不同，希望没有说晕，看图看图。

存在关系：

书号→书名

(书名、作者)→书号

其中，每一个属性都为主属性，但是上述关系存在传递依赖，不能是BCNF。即：

(书名、作者)→书号→书名

(书名、作者)→书名

即出现主属性书号对(书名,作者)码的传递依赖

我们可以通过分解为两张表，实现BCNF。

举例二:某公司有若干个仓库,每个仓库只能有一名管理员，一名管理员只能在一个仓库中工作；一个仓库中可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。那么关系模式 仓库（仓库名，管理员，物品名，数量） 属于哪一级范式？

答：已知函数依赖集：
仓库名 → 管理员，管理员 → 仓库名，（仓库名，物品名）→ 数量
码：（管理员，物品名），（仓库名，物品名）
主属性：仓库名、管理员、物品名非主属性：数量

∵ 不存在非主属性对码的部分函数依赖和传递函数依赖。
∴ 此关系模式属于3NF。
基于此关系模式的关系(具体的数据)可能如图所示：

好，既然此关系模式已经属于了 3NF，那么这个关系模式是否存在问题呢？我们来看以下几种操作：

先新增加一个仓库，但尚未存放任何物品，是否可以为该仓库指派管理员？——不可以，因为物品名也是主属性，根据实体完整性的要求，主属性不能为空。

某仓库被清空后，需要删除所有与这个仓库相关的物品存放记录，会带来什么问题？——仓库本身与管理员的信息也被随之删除了。

如果某仓库更换了管理员，会带来什么问题？——这个仓库有几条物品存放记录，就要修改多少次管理员信息。

从这里我们可以得出结论，在某些特殊情况下，即使关系模式符合 3NF 的要求，仍然存在着插入异常，修改异常与删除异常的问题，仍然不是 ”好“ 的设计。

造成此问题的原因：存在着主属性对于码的部分函数依赖与传递函数依赖。（在此例中就是存在主属性【仓库名】对于码【（管理员，物品名）】 的部分函数依赖。
解决办法就是要在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

仓库表（仓库名，管理员）

库存（仓库名，物品名，数量）

这样，之前的插入异常，修改异常与删除异常的问题就被解决了。
以上就是关于 BCNF 的解释。

第四范式（4NF）：要求把同一表内的多对多关系删除。

• 通俗地说，对于有三个属性的表，给定属性A一个值，剩余两个列之间不存在多对多的关系。例如，在下面的SPE表中，给定SNO=S1，PNO和ENO之间很明显存在多对多的关系，故上表是不属于4NF的。
• 非形式说：只要两个独立的1：N联系出现在一个关系中，那么就可能出现多只依赖。

举例一:假设每个供应商(SNO)可以生产多个零件(PNO)，可以供应给多个工程(ENO)，一个工程(ENO)需要多个零件(PNO)，但同一个工程(ENO)的同一个零件(PNO)必须来自同一个供应商。

那么关系SPE(SNO,PNO,ENO)对应的表数据可能是如下：

此时表SPE存在如下的函数依赖：
(PNO,ENO)→SNO

根据BCNF的定义，此时表SPE属于BCNF。但是这样的关系模式仍具有不好的地方：数据冗余度太大。假如供应商S3生产了n个零件，每个零件供应给m个工程，那么显然S3要在表中重复m*n次。

分解表

表1（SNO，PNO）

表2（PNO，ENO）

举例二: 一个表中存在三个数据：“课程、学生、先修课”。假设2017级的计算机专业学生想要学习JAVA课程，那么他们需要先学习VB、C#、BS三门课，才可以选择进行JAVA课程。存在关系：

课程→学生

课程→先修课

两个均是1：N的关系，当出现在一张表的时候，会出现大量的冗余。所以就我们需要分解它，减少冗余。(Ps：该例子主要是为了说明概念帮助理解，具体应用中不会只是这样的简单粗暴的。)

分解表

如果消除了4NF中的连接依赖，则达到了5NF的关系模式；5NF范式化已经很高了，实际工作中很少会遇到这么高的范式表，这里就不再叙述了。