MySQL基础（十四）：函数依赖、三范式

下面是小凰凰的简介，看下吧！ 💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活 💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中) 💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！ 有相关技能问题可以写在下方评论区，我们一起学习，一起进步。 后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

文章目录

一、各种名词解析1、超键、候选键、主键2、主属性与非主属性3、外键 二、函数依赖1、完全函数依赖分析2、部分函数依赖分析3、传递函数依赖分析4、图形分析 三、三范式四、"反范式"数据库设计---之数据冗余架构设计与细节1、订单业务举例2、冗余表（1）服务同步冗余（2）服务异步冗余（3）线下异步冗余 3、如何保证最终一致性？（1）全量数据扫描（2）增量日志扫描（3）实时消息对检测

一、各种名词解析

1、超键、候选键、主键

超键：能够唯一标识一条记录的字段或字段集（字段也可以称之为属性）。 候选键：能够唯一标识一条记录的最小字段集 主键：某个能够唯一标识一条记录的最小字段集

1. 超键 只要你能唯一标识一条记录，那么你就一个超键！ 假如我现在创建一个表： create table user_info( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') default 'male' )engine='innodb'; 这里面的超键有(注意超键是个集合)：{id}、{id,name}、{id、sex}、{id、name、sex} 候选键(注意后选键是个集合)：{id} 主键(不是集合)：id 2. 主键 create table user_info( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') default 'male' id_card_num int not null unique, )engine='innodb'; 主键是id 候选键有：{id}、{id_card_num} 超键太多不列举了！只要有id或id_card_num存在的字段集合就是超键！ 3. 候选键 create table service( ip varchar(15), port char(5), service_id int not null unique, service_name varchar(10) not null, primary key(ip,port) ); 主键：ip+port 候选键：{ip,port}、{service_id} 超键太多不列举！只要有ip+port或者service_id的字段集合就是超键

总结：

1. 主键只有唯一一个，且不是一个字段集合，主键一定来自候选键 2. 一个个侯选键是一个个的字段集合 3. 只要有候选键存在的字段集合超键

2、主属性与非主属性

在一个关系中,如一个属性是构成某一个候选键的属性集中的一个属性,则称它为主属性。 '比如上面的ip、port、service_id这三个都分别是一个主属性;' 不包含在任何一个候选码中的属性称为非主属性 '比如上面的service_name'

3、外键

create table dpm( # 创建部门表 dpm_id int primary key auto_increment, dpm_name varchar(20) not null unique ); create table stuff( # 创建员工表 stuff_id int primary key auto_increment, stuff_name varchar(20) not null unique, stuff_age int not null, stuff_gender enum('male','female'), dpm_id int not null, foreign key(dpm_id) references dpm(dpm_id) on delete cascade on update cascade );

和其他表建立关系的字段就叫外键！比如这里stuff表的dpm_id

二、函数依赖

注意，我们对函数依赖的分析，分析的是非主属性对主键的依赖关系，而不是对主属性的依赖关系，也可以说是对主键中的主属性的依赖关系！

假设存在关系: R(学号，姓名，性别，班级，班主任，课程号，课程名，学时数，成绩) 主键：学号+课程号（这是一个复合主键） 候选键：{学号,课程号} 主属性：学号、课程号 //只是这里刚好主键中的主属性和主属性一样了，主属性中还有其他候选键中的属性，只是这里候选键内容等于主键！ 非主属性有：{姓名，性别，班级，班主任，课程名，学时数，成绩}

1、完全函数依赖分析

成绩依赖于学号和课程号两个字段的组合；但只知道学号无法确定成绩，同理只知道课程号也无法确定成绩； 只有学号和课程号组合在一起才能标识哪个学生哪门课程的成绩； 因此（学号，课程号）---->成绩 是“完全函数依赖”。

2、部分函数依赖分析

1. 由于姓名、性别和班级三个属性只依赖于主键中的学号，与主键中的'课程号'无关。 因此（学号，课程号）---->姓名是“部分函数依赖” （学号，课程号）---->性别是“部分函数依赖” （学号，课程号）----->班级是“部分函数依赖” 2. 由于课程名和学时数只依赖于课程号，与主键中的'学号'无关 因此（学号，课程号）----->课程名（或学时数）是“部分函数依赖”

3、传递函数依赖分析

班主任依赖于班级，与学号无关，与课程号也无关 又因班级依赖于学号所以班主任间接依赖于学号 因此，（学号，课程号）----->班主任是'传递函数依赖'

4、图形分析

三、三范式

1NF:无重复的列（数据库表中的每一列都是不可分割的基本数据项） 考虑这样一个表：【联系人】（姓名，性别，电话） 。如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和3NF 就容易搞混淆。 // 2NF与3NF之间的区别 2NF，满足1NF且非主键列都完全函数依赖于主键，消除了部分依赖（即没有非主键列依赖于主键的一部分这种情况）。 3NF，满足2NF且非主键列都不传递依赖于主键。即非主键列是直接依赖于主键，而不是直接依赖于非主键列。 //修正的第三范式（BCNF） BCNF：满足3NF且不允许主键的一部分被另一部分或其它部分所决定（即满足3范式，并且主键的主属性之间没有依赖关系）。

四、"反范式"数据库设计—之数据冗余架构设计与细节

1、订单业务举例

（1）订单业务 Order(oid,info_detail) T(buyer_id,seller_id,oid) （2）数据量大怎么办 水平切分 （3）如何水平切分？如何满足查询？ Order_id -> oid T -> buyer_id,seller_id?

在数据量很大的情况下，我们需要分库分表，水平切分提高数据库的性能，怎么做？

假设根据'buyer_id进行分库分表'，那么查询卖家id的时候需要遍历多库、多表，影响性能 反之对'seller_id分库分表'，那么buyer_id会遍历多库多表，怎么整？

2、冗余表

解决什么问题？

1. 数据量大 2. 需要水平切分 3. 一个schema上有多个字段的查询需求 怎么解决？ Order(oid, info_detail) T1(buyer_id,seller_id,oid) T2(seller_id,buyer_id,oid)

当数据变更时，怎么保证T1、T2的一致性？

（1）服务同步冗余

（2）服务异步冗余

esb是企业服务总线，里面有jms，jms里面又有mq。这里我们主要利用它的mq，所以你把它理解成mq就行了！

data_center就是一个消息消费方！

（3）线下异步冗余

有人说为什么不用主从复制呢？主从复制的主库、从库都是一样的，这里的是分库分表！

我们都发现上面三个方法，是无法保证T1和T2绝对的一致性。

延伸：对于一个不能保证一致性的操作，"那个任务先做"，"那个任务后做" ？ 方法论：如果原子性被破坏，不一致出现，谁先做对业务的影响比较小，就谁先执行

显然我们用户体验最重要，因此我们一定要让用户能及时查到数据，因此T1需要先做，T2可以后做！

扩展：分布式事务如何保证一致性？

难以保证，方法论：最终一致性

3、如何保证最终一致性？

（1）全量数据扫描

（2）增量日志扫描

（3）实时消息对检测