Mysql设计规范

1、范式

1.1 简介

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结 构需要满足的某种设计标准的 级别 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

  1.2 范式包括哪些

目前常见的数据库范式常见有六种：从低到高分别是：第一范式（1NF）、第二范式 （2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美 范式）。

这张图告诉我们，范式从低到高是一种包含关系：即高阶范式一定满足低阶范式的约束。比如，第三范式一定满足第一范式和第二范式。

1.3 键和相关属性介绍

在了解范式之前，首先我们要知道以下概念

• 超键：在关系中能唯一标识元组的属性集合 称为关系模式的超键
• 候选键：最小超键，一个或多个属性的组合，能够唯一确定一个实体类的实例
• 主键：从候选键中，选出用来作为唯一标识的属性或属性组，称为
• 外键：如果一个实体的某个字段指向另一个实体的主键，就称之为外键
• 主属性、非主属性： 在一个关系中，如果一个属性是构成另一个候选关键字的属性集合中的一个属性，则称之为主属性。不包含在任何一个候选码中的属性称之为非主属性。

# 键、码、关键字 三者称呼可以互换，等价

上面这段概念很抽象，不好理解，我们看一个实际的例子：

学生信息表

学号

姓名

年龄

班级编号

身份证号

班级信息表

班级编号

班主任

所在教学楼位置

这两张表 分别构成了 学生信息、班级信息的关系，我们通过这两张表，理解上面的概念：

【超键】首先我们要明确的是 超键是一个集合

 学生信息表里 ，我们发现 学号和 身份证号都是全局唯一的(不可能有重复)，所以

（学号，姓名），（身份证号，班级编号，年龄）（学号），都可以是超键。

总结：对于学生信息表来说，超键是 包括 学号 或 身份证号的 任意组合。

【候选键】最小的超键 ，对于学生信息·表来说，超键是 (学号) 或者（身份证号）。

【主键】我们自己选定的候选键，比如(学号) 

##这里补充一句，

主键设计的一般规则，是要符合 ：全局唯一 和单调递增两个约束的

以往，我们不建议使用UUID作为主键，原因是 UUID作为字符串存储虽然能保证全局唯一，但是由于其时间戳字符串的顺序，并不是按照 年月日时分秒设计的，不能保证单调递增，

我们可以利用sql函数，重新设计UUID 使其变成单调递增。

UUID作为字符串存储是 36字节，作为二进制存储是16字节。

像一般的订单·表主键设计。我们可以 拼接一个主键比如：

       时间戳+全局唯一ID+用户账户ID片段

【外键】学生信息表的外键：班级编号

【主属性】：（学号） (身份证号)

【非主属性】：除了主属性全是

1.4   第一范式

表中的属性必须是原子性的

所谓原子性，就是一个属性不可以再分割为其他子属性： 比如：

用户编号	年龄	账户信息(用户名/密码)
0001	31	Alex 12345
0002	32	Cherry 67891

这张表里的用户信息 显然是可以再分割为 用户名 和 密码 两个字段的

用户编号	年龄	用户名	密码
0001	31	Alex	12345
0002	32	Cherry	67891

这里补充一句：属性的原子性，是比较主观的，是否必要分割，取决于业务需求，就像雇佣关系表中，很多老外 有 firstname 和 lastname， 我们可以用 fullname 来完整表示，是否有必要，讲名字分开，取决于业务需不需要获取具体的firstname 和 lastname。

1.5 第二范式

表中 不可以出现部分依赖

首先我们看看什么是部份依赖和完全依赖：

学号	课程号	姓名	成绩	任课老师
0001	AA	Alex	99	王大锤
0001	BB	Alex	95	张二棒子
0002	AA	Cherry	97	王大锤
0002	CC	Cherry	94	猪刚鬣

这张表中，我们发现 单单有学号，或只由课程号，是无法得到学生的成绩的，

常识上： 0001号学生的 数据结构成绩 是99分。成绩是由两个定语决定的，一个是学生，一个是课程。

所以，这张成绩表的主键，应该是联合主键：（学号，课程号）

此时(学号，课程号) -> (成绩) 这就是一个完全依赖。

但是，我们发现 学生姓名只依赖于 学号，换言之，知道了学号，我们就知道了学生姓名

(学号)->(姓名) 这就是一个部分依赖。

（课程号）->(任课老师)也是一个部份依赖

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？

• 数据冗余 ：如果一个学生可以考m门课程，那么，学生的姓名就重复了 m-1次，一个课程有n个学生参加，那么任课老师就重复了n-1次。
• 插入异常 ：如果我们想要添加一门新课程时，不知道学号，就无法插入(主键不能为NULL)
• 删除异常 ：如果我要删除某个学生，如果没有单独保存课程信息，就会同时删除了课程信息(比如表例里，删除cherry ，会把CC课程也删了)。
• 更新异常：如果我们更换了某课程的任课老师，就需要把此表的每一个记录的任课老师字段都进行更换，否则就会出现异常。

拆分如下：

课程信息表：

课程编号	任课老师
AA	王大锤
BB	张二棒子
CC	猪刚鬣

成绩表：

学号	课程号	成绩
0001	AA	99
0001	BB	95
0002	AA	97
0002	CC	94

学生信息表：

学号	姓名
0001	Alex
0002	Cherry

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只 表达一个意思。

1.6 第三范式

消除表中的非主属性的传递依赖。

这里又是一个新名词，传递依赖，我们来看看

学号	姓名	年龄	选课编号	课程名称	任课老师
0001	Alex	31	AA	数据结构	王大锤
0002	Cherry	33	AA	数据结构	王大锤
0003	Jack	32	BB	算法原理	孔老六
0004	Tom	34	AA	数据结构	王大锤

此表的候选键是 （学号，选课编号） 或（学号，课程名称）//此时我们假设一个老师可以担任多个课程的老师。

这是一张学生选课信息表，主键是学号，但是我们发现，课程名称，任课老师 是依赖于选课编号的，每个学生都有自己的选课编号，这种依赖关系如下：

(学号)->(选课编号)->(课程名称，任课老师)， 这就是传递依赖，此表不符合第三范式，而且我们发现，课程名称，任课老师存在大量数据冗余，我们对其进行改造：

学号	姓名	年龄	选课编号
0001	Alex	31	AA
0002	Cherry	33	AA
0003	Jack	32	BB
0004	Tom	34	AA

课程信息表

课程名称	任课老师	课程名称
数据结构	王大锤	数据结构
数据结构	王大锤	数据结构
算法原理	孔老六	算法原理

这样，这些表的关系，就符合了第三范式。

1.7 巴斯-科德 范式

1、案例：

我们先分析如下表的范式情况：

在这个表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性 之间的依赖关系。

 仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个 属性。这样，我们就可以找到数据表的候选键。

候选键 ：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为 主键 ，比 如（仓库名，物品名）。

 主属性 ：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。

非主属性 ：数量这个属性。

2. 是否符合三范式

如何判断一张表的范式呢？

我们需要根据范式的等级，从低到高来进行判断。

 首先，数据表每个属性都是原子性的，符合 1NF 的要求；

 其次，数据表中非主属性”数量“都与候选键全部依赖，（仓库名，物品名）决定数量，（管理员，物品 名）决定数量。因此，数据表符合 2NF 的要求；

 最后，数据表中的非主属性，不传递依赖于候选键。因此符合 3NF 的要求。

3、存在的问题

既然数据表已经符合了 3NF 的要求，是不是就不存在问题了呢？我们来看下面的情况：

1 增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因 此会出现 插入异常 ；

2. 如果仓库更换了管理员，我们就可能会 修改数据表中的多条记录 ；

3. 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除

 你能看到，即便数据表符合 3NF 的要求，同样可能存在插入，更新和删除数据的异常情况。

4、问题解决

首先我们需要确认造成异常的原因：主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系， 这样就有可能导致上面的异常情况。

因此引入BCNF，它在 3NF 的基础上消除了主属性对候选键的部分依 赖或者传递依赖关系。

如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于 BCNF。

根据 BCNF 的要求，我们需要把仓库管理关系 把表拆分成下面这样：

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

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

这样就不存在主属性对于候选键的部分依赖或传递依赖，上面数据表的设计就符合 BCNF。

我们再看一个例子：

学号	课程号	课程名	成绩
0001	AA	数据结构	99

此表中，候选键（学号，课程号） 或 (学号，课程名)，我们可以任选其一作为主键

       学号，课程号，课程名 都是主属性，但是我们发现，主属性之间是有依赖关系的

(课程号)—>(课程名),为了消除这种主属性依赖，我们对表进行改造：

课程号	课程名
AA	数据结构

学号	课程号	成绩
0001	AA	99

1.8 第四范式

消除多值依赖问题

课程，教室，导师 三者是一个联合主键

课程	上课时间	导师
高数	上午	张三
高数	下午	张三
英语	下午	李四
高数	上午	王五

这张表是符合BC范式的，但是却存在着多值依赖问题，

学生选课的时候 高数 上午，还没有确定具体哪个老师来教，我们就无法维护课程与上课时间的关系，因为 高数 上午在此表中，至少有两条冗余数据

课程	上课时间
高数	上午
高数	下午
英语	下午

课程	导师
高数	张三
高数	王五
英语	李四

1.9 第五范式

由于第五范式几乎很少使用，不做详述。

2  反范式化

2.1 概述

我们知道，范式的存在，就是为了规范数据库表结构设计而出现的，那么不是越高越好吗，为什么要进行反范式化的操作？

首先，我们要知道，范式给我们带来什么样的好处：

范式化成都越高，表的数据冗余程度就越低，耦合度也降低。

但是一味的降低耦合度，却·忽略了数据库查询等操作的性能，是我们不能接收的。

为什么这么说？

有时候，我们可以一条语句就查询出结果，但是范式化成都过高，我们要将多个表在一起进行查询，才能得到最终结果，我们知道，多表查询，一般分为子查询，和关联查询，

这种语句的复杂度是要高于普通查询的，而且，很有肯能会造成索引失效等严重破坏查询效率的情况。

所以，在考虑性能为前提的情况下，我们是要在设计表的时候，进行一些取舍。

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要

2. 在数据规范化的同时 , 要综合考虑数据库的性能

 3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间

 4. 通过在给定的表中插入计算列，以方便查询

例子：

//TODO

2.2 反范式化带来的问题

1. 存储 空间变大 了

1. 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致

1. 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源

1. 在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

2.3 反范式化适用场景

当冗余信息有价值或者能 大幅度提高查询效率 的时候，我们才会采取反范式的优化。

在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每 次发生的 订单收货信息 都属于 历史快照 ，需要进行保存，但用户可以随时修改自己的信息，这时保存这 些冗余信息是非常有必要的。

 反范式优化也常用在 数据仓库 的设计中，因为数据仓库通常 存储历史数据 ，对增删改的实时性要求不 强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。