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

2022年5月23日13:05:28

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

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.数据库的范式

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

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

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

范式：

  • 第一范式（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;

结果：

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

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

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

示例：

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

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

外连接（Outer Join）

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

    示例：

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

    结果：

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

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

    示例：

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

    结果：

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

  • 全外连接（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;

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

交叉连接（Cross Join）：返回两表的笛卡尔积（对于所含数据分别为m、n的表，返回m*n的结果）。

示例：

select * from student,grade;

结果：

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

  • 作者：投三分的金闪闪
  • 原文链接：https://www.cnblogs.com/ranger30/p/16121713.html
    更新时间：2022年5月23日13:05:28 ，共 3006 字。