数据库基础知识详解三:MVCC、范式以及表连接方式

写在文章前:本系列文章用于博主自己归纳复习一些基础知识,同时也分享给可能需要的人,因为水平有限,肯定存在诸多不足以及技术性错误,请大佬们及时指正。

8.MVCC

多版本并发控制(Multi-Version Concurrency Control, MVCC),MVCC在数据表中每行记录后面都保存有两个隐藏的列,用来存储更新信息的事务号:DB_TRX_ID和一个回滚指针:DB_ROLL_PTR(指向该行数据上一次修改前的数据,存储在undo log中)。

系统版本号:每开始一个新的事务,系统版本号就会自动递增)。(更新包括增删改)

更新事务号:更新一个数据行时的事务版本号(事务版本号:事务开始时的系统版本号。)

各种操作具体实现

  • 插入操作时,记录创建版本号。
  • 删除操作时,记录删除版本号。
  • 更新操作时,先记录删除版本号,再新增一行记录创建版本号。
  • 查询操作时,要符合以下条件才能被查询出来:删除的版本号未定义或大于当前事务版本号(删除操作是在当前事务启动之后做的)。创建的版本号小于或等于当前事务版本号(创建操作是事务完成或者在事务启动之前完成)

通过版本号减少了锁的争用,提高了系统性能。可以实现提交读和可重复读两种隔离级别,未提交读级别无需使用MVCC。

快照读:使用MVCC读取的是快照中的数据,这样可以减少加锁带来的开销。

当前读:读取的是最新的数据,需要加锁。

问题:MVCC不是有类似生成快照的机制吗,为什么不能解决幻读?

我们设计一个实际案例:现在假设事务A的版本号为200:

select * from user,
-- 其他操作
update user set level=1 where age>0,
select * from user

在事务A执行第一次select的语句时,假设查询出了三个用户。然后在事务A执行中间的其他操作时,事务B插入了一条新的用户数据,因为事务B的版本号为300,所以假设此时事务A查询,因为该行数据创建的版本号大于自己的版本号,所以不会被查询出。

但是由于此时事务A刚好执行了下一条更新语句,而且恰好新插入的那行数据满足更新条件,它的更新版本号被修改为事务A的版本号,这导致事务A的第二次查询操作会查询出这条别的事务新插入的数据,这就造成了幻读的问题。

MySQL是使用MVCC+Next Key Lock来解决幻读问题的,关于Next-Key Lock可以看博主数据库基础知识一的介绍。

9.数据库的范式

讲解数据库的范式之前,补充一下数据库中的基本概念:

  • 主键:关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键(一张表只有一个,不允许重复,不允许为空)。
  • 外键:外键用于与另一张表的关联。是能确定另一张表记录的字段,用于保持数据的一致性。成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键(一张表可以有多个,可以有重复的,可以是空值)。
  • 元组:可以理解为数据表的某一行属性:可以理解为数据表的某一列,属性名就是列的字段。
  • 候选码:某一属性组能唯一标识一个元组而其子集不能,则称该属性组为候选码。若有多个候选码,选择其中一个为主码。
  • 主属性:候选码包含的属性(一个或多个)。
  • 非主属性:顾名思义,就是候选码不包括的属性。

范式

  • 第一范式(1NF,Normal Form):属性不应该是可分的。举例:如果将“电话”作为一个属性(即数据表中的一列),是不符合1NF的,因为电话这个属性可以分解为家庭电话和移动电话。如果将“移动电话”作为一个属性,就符合1NF。

  • 第二范式(2NF):每个非主属性完全依赖于主属性集(候选键集)。B完全依赖于A,就是说A中的所有属性唯一决定B,属性少了就不能唯一决定,属性多了则有冗余(叫依赖不叫完全依赖)。

    举例:(学号,课程名)这个主属性集可以唯一决定成绩,但是对于学生姓名这个属性,(学号,课程名)这个属性集就是冗余的,所以学生姓名不完全依赖于(学号,课程名)这一属性集。

    问题:那如何使其满足2NF?

    可以通过分解来满足 2NF:将(学号,课程名,成绩)做成一张表;(学号,学生姓名)做成另一张表,避免大量的数据冗余; 满足1NF后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系,也就是说一个表只描述一件事情。

  • 第三范式(3NF):在 2NF 的基础上,非主属性不传递依赖于主属性

    传递依赖:如果C依赖于B,B依赖于A,那么C传递依赖于A。3NF在2NF的基础上,消除了非主属性之间的依赖。

    比如一个表中,主属性有(学号),非主属性有(姓名,院系,院长名),可以看到院长名这个非主属性依赖于院系,传递依赖于学号。要求:表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键)。

    使一个2NF变成3NF的方法同样是分解,方法类似1NF变为2NF,这里不再赘述。

不符合范式会出现哪些异常?

  • 冗余数据:某些同样的数据多次出现(如学生姓名)。
  • 修改异常:修改了一个记录中的信息,另一个记录中相同的信息却没有修改。
  • 删除异常:删除一个信息,那么也会丢失其它信息(删除一个课程,丢失了一个学生的信息)。
  • 插入异常:无法插入(插入一个还没有课程信息的学生)。

10.表连接方式

先创建两张简单的数据表以作后续的演示:

学生表 在这里插入图片描述
成绩表 在这里插入图片描述

内连接(Inner Join):仅将两个表中满足连接条件的行组合起来作为结果集

  • 自然连接:只考虑属性相同的元组对。

示例:

select * from student natural join grade;

结果:

在这里插入图片描述

没有给任何的条件,数据库自动把两张数据表各行有相同属性的行(元组)连接在了一起。

  • 等值/连接:给定条件进行查询。

示例:

select * from student,grade
where student.sno=grade.sno;

结果:
在这里插入图片描述

外连接(Outer Join)

  • 左连接:左边表的所有数据都有显示出来,右边的表数据只显示共同有的那部分(就比如说成绩表和课程表连接,只显示两边有学号相等的,如果某一边的学号另一边没出现,那就不显示),没有对应的部分补NULL。

    示例:

    select * from student
    left outer join grade
    on student.sno=grade.sno;
    

    结果:

在这里插入图片描述

  • 右连接:和左连接相反。

    示例:

    select * from student
    left outer join grade
    on student.sno=grade.sno;
    

    结果:

在这里插入图片描述

  • 全外连接(Full Outer Join):查询出左表和右表所有数据,但是去除两表的重复数据。

    示例:

    原本SQL语句只应该需要类似:

    select * from student
    full outer join grade 
    on student.sno=grade.sno;
    

    但因为MySQL不支持这样的全外连接,所以我们使用UNION来达到全外连接的效果:

    select * from student
    left join grade on student.sno=grade.sno
    union
    select * from student
    right join grade on student.sno=grade.sno;
    

    结果:
    在这里插入图片描述

交叉连接(Cross Join):返回两表的笛卡尔积(对于所含数据分别为m、n的表,返回m*n的结果)。

示例:

select * from student,grade;

结果:

 2 total views,  1 views today

页面下部广告