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

作者: database 发布时间: 2023-08-18 浏览: 997 次 编辑

一、数据库的设计规范

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. 第五范式

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