笔记——MySQL数据库系统原理

事务与ACID

事务指的是满足 ACID 特性的一组操作。可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

• 原子性（Atomicity）
  事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
  回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

• 一致性（Consistency）
  数据库在事务执行前后都保持一致性状态。指系统从一个正确的状态，迁移到另一个正确的状态。

• 隔离性（Isolation）
  一个事务所做的修改在最终提交以前，对其它事务是不可见的。

• 持久性（Durability）
  一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

并发一致性问题

• 脏写：两事务先后对数据修改未提交，后修改会覆盖先修改。
• 脏读：A事务修改数据未提交，B事务读，然后A事务回滚，导致B事务使用了错误数据。
• 不可重复读：B事务读一数据，A事务update/delete未提交，B事务之后又读一次，发现前后读到的不一致。
• 幻读：A事务调用count(*)，B事务insert，A事务又调用count(*)，发现多了几行数据。

隔离级别

• 未提交读：事务可读取未提交的事务的修改。
• 提交读：事务只可读取已提交的事务的修改。
• 可重复度：保证同一事务多次读取同一数据的结果一致。
• 可串行化：事务串行执行，互不干扰，永不出现并发一致性问题。

脏读：未提交读。
不可重复读：未提交读、提交读。
幻读：未提交读、提交读、可重复读。

封锁

封锁粒度：行锁、表锁。
封锁类型：读写锁、意向锁。

读锁（S）、写锁（X）：给单位加读锁，不影响其他事务的加读锁，但不让加写锁；给单位加写锁，则不让其他事务加读写锁。

意向锁（IS、IX）：事务想给表加写锁，就需要检测所有行是否有加写锁，这无疑很耗时。所以加入意向锁，当给行加写锁，需要给表加IX锁；给行加读锁，需要给表加IS锁

封锁协议：

一级：事务要修改必须加X锁，事务结束才可释放。解决脏写。
二级：事务要读取必须加S锁，读取完立马释放。解决脏读。
三级：事务要读取必须加S锁，事务结束才可释放。解决不可重复读。

多版本并发控制

实际场景中读操作往往多于写操作，而读写冲突会导致读等待。

MVCC利用多版本的思想，使写操作更新最新的版本快照，而读操作去读取旧版本快照，使互斥关系消失。

MVCC 规定只能读取已经提交的快照。

有快照生成，就代表有修改；读操作不会触发MVCC的任何改变。

当前读与快照读

当前读，像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)，它读取的是记录的最新版本，会对读取的记录进行加锁。

快照读，不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

隐式字段

系统版本号SYS_ID是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。

每一行数据，都会多出三列(其中一列是无主键时自动生成的DB_ROW_ID)：

• 事务版本号 DB_TRX_ID ：事务开始时的系统版本号。

• 回滚指针 DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本（存储于rollback segment里）。

Undo日志

MVCC 的多版本快照存储在 Undo 日志中。

Undo日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

Undo日志记录了记录事务版本号 TRX_ID 和数据，还记录了一个DEL 字段，用于标记是否被删除。

Undo日志有两种：

• insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。

• update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被统一清除。

ReadView

未提交读、可串行化这两个隔离级别，读取版本链中最新版本的记录即可，提交读、可重复读，就需要遍历所有快照，判断某个版本的记录是否对当前事务可见。InnoDB通过ReadView实现了这个功能。

ReadView存储所有未提交即活跃事务的版本号 TRX_IDs，和TRX_ID_MIN、TRX_ID_MAX。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。READ COMMITTED在每次读取数据前都会生成一个ReadView，这样就能保证每次都能读到其它事务已提交的数据。REPEATABLE READ 只在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果完全一致。

在select时，先从最新版本快照开始，读取数据行快照的 TRX_ID并判断：

• TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
• TRX_ID > TRX_ID_MAX，表示生成该快照的事务在执行select的事务之后进行修改，因此不可使用，然后根据回滚指针到上一个版本继续判断。
• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，如果在TRX_IDs列表里，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

范式

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

第一范式：属性不可拆分。
第二范式：每个非主属性完全函数依赖于键码。（“完全”指当键码为多个组合的，不能让组合之一去函数决定一个非主属性，只能整个组合函数决定。可通过拆分解决）
第三范式：非主属性不传递函数依赖于键码。（只能由键码来决定所有的非主属性）

ER图

Entity-Relationship，有三个组成部分：实体、属性、联系。

实体的三种联系包含一对一，一对多，多对多三种。

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

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

表示出现多次的关系：一个实体在联系出现几次，就要用几条线连接。

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

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

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