详解Mysql:范式-mysql分表规则

一、数据库的设计规范

1. 范式

在关系型数据库中，关于数据库表设计的基本原则，规则被称为范式，范式的英文名称为Normal Form，简称NF。要想设计一个合理的关系型数据库，就需要满足一定的范式。

目前关系型数据库有六种常见范式，按照范式级别，从低到高有：第一范式（1NF），第二范式（2NF），第三范式（3NF），巴斯科德范式（BCNF），第四范式（4NF），第五范式（5NF，又称完美范式）。

数据库的范式设计得越高阶，冗余度就越低，同时高阶的范式一定满足低阶的范式的要求。

一般来说，在关系型数据库中，最高也就遵循到BCNF，普遍是3NF，但也不是绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

一些概念：

1. 超键：能唯一标识元组的属性集称为超键。
2. 候选键：如果超键中不包含多余的属性，那么这个超键就称为候选键。
3. 主键：用户可以从候选键中选择一个作为主键。
4. 外键：如果数据表R1中的某属性不是R1的主键，但是是数据表R2的主键，那么这个属性就是R1的外键。
5. 主属性：包含在任一候选键中的属性称为主属性。
6. 非主属性：指不包含在任何一个候选键中的属性。

通常我们会将候选键称为码，把主键称为主码。

1.1 第一范式

第一范式主要确保数据表中每个字段的值都具有原子性，也就是说表中每个字段不能再被拆分。这个范式一定需要遵守。

例如某个字段user_info，包含了家庭住址，邮箱，电话，这显然是不可以的，需要将user_info拆分成对应的三个字段。

但是原子性事实上是主观的，例如姓名name可能有firstName，lastName，那是否需要拆分，再例如是否需要将地址拆分成省份，区域等，这取决于应用程序是否需要查询到哪种粒度。

1.2 第二范式

在满足第一范式的基础上，还要满足数据库表中的每一条数据，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

举例1：

成绩表（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以（学号，课程号）和成绩就是完全依赖关系。

举例2：

比赛表，里面包含了球员编号，姓名，年龄，比赛编号，比赛时间，比赛场地等属性，由于单单球员编号和比赛编号都无法确定唯一一条数据，因此需要将球员编号和比赛编号联合作为主键：

(球员编号，比赛编号) --> (姓名，年龄，比赛时间，比赛场地，得分)

但是这个表并不满足第二范式，因为数据表中的字段并不满足完全依赖主键的条件：

(球员编号) --> (姓名，年龄)
(比赛编号) --> (比赛时间，比赛场地)

不满足第二范式的问题：

1. 数据冗余：如果一个球员参加了n场比赛，那么球员的姓名和年龄就重复了n次，一个比赛也有可能有m个球员参加，那比赛的时间和地点就重复了m次。
2. 插入异常：如果我们想要添加一场新的比赛，但是这时球员还没有确定，那么就无法插入。
3. 删除异常：如果我们想要删除某个球员编号，会将比赛信息删除掉。
4. 更新异常：如果我们想要调整某个比赛的时间，那么数据库表中所有关于这个比赛的时间都需要进行调整，否则会出现一场比赛时间不同的情况。

因此为了避免上面的问题，我们可以将上面球员比赛表设计成三张表，这样每张表都符合了第二范式

球员表：球员编号，姓名，年龄等
比赛表：比赛编号，比赛场地，比赛时间等
球员比赛关系表：球员编号，比赛编号，得分等

1.3 第三范式

在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段字段。（即不能存在非主属性A依赖非主属性B，非主属性B依赖于主键C的情况），通俗来说，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

举例：

员工信息表：员工编号，姓名，部门编号，部门名称。

上面的员工信息表是符合第二范式的，因为姓名，部门编号，部门名称都完全依赖员工编号这个主键，但是并不符合第三范式，因为有非主键字段 部门名称 依赖于非主键字段 部门编号。因此需要将部门编号，部门名称再抽取成一张表。

1.4 小结

• 第一范式：确保每列的原子性
• 第二范式：非主键列完全依赖着主键列
• 第三范式：非主键列之间不存在依赖关系

范式的目的是为了降低数据的冗余，缺点是可能会降低了查询效率，因为范式等级越高，设计出来的表就越多，越精细，进行查询时就可能需要关联多张表。

实际上设计数据库时，并非会完全遵守这些标准，经常会为了性能违反范式原则，通过增加冗余的数据来提高数据库的性能。

2. 反范式化

有时候为了性能，并不一定会完全遵守范式标准。

举例1：

有员工表employee，和部门表department，如果经常需要查询员工的部门名称，并且员工很多，那么可以考虑在员工表中添加部门名称 这一冗余字段：

select emp_id, dept_name
from employee e join department d
on e.dept_id = d.dept_id;

反范式的问题：

存储空间变大了

一个表中的字段做了修改，另一个表中的冗余字段也需要做相应的修改。

在数据量小的情况下，反范式反而不能体现性能上的优势，可能还会让数据库的设计变复杂。

反范式的使用场景：

当加上冗余信息后能够大幅度提高查询效率

这个冗余字段不需要经常修改

3. 巴斯范式

人们在3NF的基础上进行了改进，提出了巴斯范式（BCNF）。

若一个关系达到了第三范式，并且它只有一个候选键，或者它每个候选键都是单属性，则为巴斯范式。简单来说就是主属性和其他主属性存在依赖关系。

一般来说，数据库设计达到第三范式或巴斯范式就可以了。

案例分析：

在这个表中，一个仓库只有一个管理员，并且一个管理员只管理一个仓库。

进行分析可知，（仓库名，物品名）或者（管理员，物品名）可以决定数量，因此（仓库名，物品名）或者（管理员，物品名）就是这个表的候选键。

1. 符合第一范式：所有字段都是原子性的。
2. 符合第二范式：表中非主属性 “数量” 完全依赖两个候选键
3. 符合第三范式：并不存在非主属性依赖于非主属性。

存在的问题：

增加一个仓库，但是还没有存放物品，由于主键（仓库名，物品名）不能有空值存在，因此会插入异常。

如果仓库的物品全部卖完了，那么会导致仓库名和管理员名称也会随之删除。

如果仓库的管理员更换了，那么会有多条记录都需要更改。

因此，即便表符合了3NF，但是还是可能存在插入，删除，更新异常的问题。

问题出现的原因：

主属性仓库名对于候选键（管理员，物品名）有部分依赖，这样就导致有可能出现上面的问题，因此引入BCNF，它在3NF的基础上消除了主属性对候选键的部分依赖或者传递性依赖。

如何解决：

将仓库名和管理员拆分出来形成一张表，然后（仓库名，物品名，数量）形成一张库存表。

4. 第四范式

如果表中存在多个 1对多关系 时，需要进行拆分。

例如有职工表（职工编号，职工孩子，职工选修课程），在这张表中，每个职工可能有多个孩子，也有可能有多个选修课程，因此需要进行拆分成两张表：职工表一（职工编号，职工孩子），职工表二（职工编号，职工选修课程）。

5. 第五范式

。。。挖坑，不是很重点，有空再填。