数据库原理

数据库原理

一、事务

概念

事务是满足ACID特性的一组操作，事务可以Commit提交，也可以Rollback回滚。

ACID

1、原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交要么全部提交，要么全部回滚。

2、一致性（Consistency）

数据库在事务执行前后保持一致性状态。在一致性状态下，所有事务对同一数据的读取结果都是相同的。

3、隔离性（Isolation）

一个事务在最终提交之前，对其他事务是不可见的

4、持久性（Durability）

一旦事务发生提交，其所做的将永远保存在数据库中，即使系统崩溃，事务执行的结果也不能丢失。

AUTOCOMMIT

MySQL默认使用自动提交模式，在不使用start transaction开启事务的时候，任何一个数据库操作都会被当成一个事务，在执行完成后自动提交

二、并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

丢失修改

丢失修改指的是一个事务的更新操作被另一个事务的更新操作覆盖

读脏数据

读脏数据是指在不同的事务下，一个事务读取到了另一个事务未提交的数据

不可重复读

不可重复读是指在同一事务中，对同一数据的多次读取结果不同。第一个事务读取一个数据后，第二个事务对该数据进行了修改，第一个事务再次读取该数据时与第一次读取的结果不同

幻影读

幻影读的本质也是不可重复读，T1读取某个范围内的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据时与第一读取的结果不同

产生并发不一致性的原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制通过封锁来实现，数据库管理系统提供了事务的隔离级别用来实现并发控制。

三、封锁

封锁粒度

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

封锁类型

1、读写锁

• 互斥锁（Exclusive），简称为X锁，又称写锁
• 共享锁（Shared），简称为S锁，又称读锁

有以下两个规定：

• 一个事务对数据对象A加X锁，就可以对A进行读取和更新操作。加锁期间任何事务不能对A加任何锁
• 一个事务对数据对象A加S锁，可以对A进行读取操作，但不能进行更新操作。加锁期间其他事务可以对A加S锁，但不能加X锁

2、意向锁

使用意向锁可以更容易地支持多粒度封锁

在存在行级锁和表级锁的情况下，事务T想对表A加X锁，就要检测其他事务是否对表A或者表A中的任意一行加锁，那么就需要对表A的每一行都进行检测，这个过程是非常耗时的。

意向锁在之前的X/S锁的基础上引入了lX/lS锁，IX/IS都是表锁，用来表示一个事务想对表中的某个数据行加X锁或者S锁，有以下两个约定：

• 一个事务想要获得某个数据行对象的S锁之前，需要先获得表的IS锁或更强的锁
• 一个事务想要获得某个数据行对象的X锁之前，需要先获得表的lX锁

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其他事务对表A添加X/IX/S/IS锁，如果加了就表示有其他事务正在对使用这个表或者表中某一行的锁，因此事务T对表A加锁失败。

各种锁的兼容关系如下：

解释如下：

• 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
• 这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

封锁协议

1、三级封锁协议

一级封锁协议

事务T想要修改数据A时，必须加X锁，直到T结束时才释放锁

可以解决丢失修改问题，因为不能有两个事务同时对同一数据进行修改，所有事务的修改不会被覆盖

二级封锁协议

在一级封锁的基础上，要求读取A数据时必须要加S锁，读取完马上释放S锁

可以解决读脏数据，因为如果一个事务在对数据A修改，按照一级封锁协议，必须加X锁，那么就不能加S锁，就不能读取数据。

三级封锁协议

在二级封锁的基础上，要求读取A数据时必须加S锁，直到事务结束才释放S锁

可以解决不可重复读问题，因为在读取A数据时，必须加S锁，那么就不能加X锁，从而避免了在读的期间数据A的改变

2、两段锁协议

加锁和解锁分为两个阶段执行

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同，串行执行的事务之间互不干扰，不会出现并发一致性问题

MySQL的隐式锁定与显示锁定

MySQL使用的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻释放的，这被称为隐式锁定

InnoDB也可以使用特定的语句进行显式锁定：

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

四、隔离级别

未提交读（READ UNCOMMITED）

事务中的修改即使未提交，对其他事务也是可见的

提交读（READ COMMITED）

只能读已提交的修改

可重复读（REPEATABLE READ）

保证在同一个事务中，多次访问同一数据的结果是相同的

可串行化（SERIALIZABLE)

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题

该隔离级别需要加锁完成，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行

五、多版本并发控制

多版本并发控制（Multi-Version Concurrency Control，MVCC）是MySQL的InnoDB存储引擎实现事务隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。未提交读总是读取最新的数据行，无需MVCC；可串行化需要对所有的数据行都进行加锁，单纯使用MVVC无法实现

基本思想

MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系

版本号

• 系统版本号SYS_ID：是一个递增的数据，每开启一个新的事务，系统版本号自动递增
• 事务版本号TRX_ID：事务开始时的系统版本号

Undo日志

MVCC的多版本指的是多个版本的快照，这些快照存储在Undo日志中，该日志通过回滚指针ROLL_PTR把一个数据行的所有快照连接起来。

ReadView

MVCC维护了一个ReadView结构，主要包含了当前未提交事务的列表TRX_IDs，和列表的最大值TRX_ID_MAX和最小值TRX_ID_MIN

快照读

MVCC进行SELECT操作时，读取的是快照中的数据，不需要进行加锁操作

当前读

MVCC进行INSERT、UPDATE、DELETE操作时，需要进行加锁操作，从而保证读取的是最新的数 据

六、Next-Key Locks

Next-Key Locks是MySQL的InnoDB引擎中的一种锁实现，是为了解决MVCC不能解决幻影读问题而存在的。

Record Locks

锁定一个记录上的索引，而不是记录本身

如果一个表没有设置索引，那么InnoDB会自动在表的主键上加上隐藏的聚簇索引，Record Locks依然可以实现。

Gap Locks

锁定索引之间的间隙，但是不包含索引本身，例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

七、关系数据库设计理论

函数依赖

记A-B表示A函数决定B，也可以说B函数依赖于A

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖。

异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

Sno	Sname	Sdept	Mname	Cname	Grade
1	学生-1	学院-1	院长-1	课程-1	90
2	学生-2	学院-2	院长-2	课程-2	80
2	学生-2	学院-2	院长-2	课程-1	100
3	学生-3	学院-2	院长-2	课程-2	95

不符合范式的关系，会产生很多异常，主要有以下四种异常：

• 冗余数据：例如 学生-2 出现了两次。
• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

范式

范式是为了解决异常而规定的，高级别范式依赖于低级别范式

第一范式（1NF）

属性不可分

第二范式（2NF）

每个非主属性函数完全依赖于键码

第三范式（3NF）

非主属性不传递函数依赖于键码

八、ER图

Entity—RelationShip，包含三个组成部分：实体、属性、联系

用来进行关系型数据库的概念设计

实体的三种关系

一对一、一对多、多对多三种

• 如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
• 如果是一对一，画两个带箭头的线段；
• 如果是多对多，画两个不带箭头的线段

下图的 Course 和 Student 是一对多的关系。

表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系。

联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

表示子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。