[MySQL]范式和事务01

范式

在进入正文之前先复习几个重要范式

1范式(1 NF)

要求:表的每个属性必须具有原子值

需要说明的是,属性的原子性是主观的,比如说表中假设有地址这个属性:那么是否要认为广东省广州市三元里78号满足原子性取决于主观,如果认为这样已经可以满足需求,就不要进行拆分,但是如果认为不满足需求,就要进一步拆分成更小粒度的字段.

2范式

要求:非主属性要完全依赖主键

举例1:

• 成绩表 （学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以“（学号，课程号）→成绩”就是 完全依赖关系 。

举例2:

• 比赛表 player_game ，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键（或主键）来决定如下的关系：
  • (球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)
• 但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：
  • (球员编号) → (姓名，年龄)
  • (比赛编号) → (比赛时间, 比赛场地)

对于非主属性来说，并非完全依赖主键。为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

3范式

要求:每个非主属性完全依赖主键,而且除了主键外别无依赖.

举例1

商品类别名称依赖于商品类别编号，不符合第三范式。修改后:

BCNF

它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

有一个 学生导师表 ，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

这个表的设计满足三范式，但是这里存在另一个依赖关系，“专业”依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。所以这个表的部分主键Major依赖于非主键属性Advisor。不满足BCNF。更新后:

导师表:

事务

事务概述

基本概念

**事务:**一组逻辑操作单元，使数据从一种状态变换到另一种状态。

**事务处理的原则：**保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。

事务的ACID特性

• 原子性
  原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
• 一致性
  根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是语义上的而不是语法上的，跟具体的业务有关。
  那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。
• 隔离性
  事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对 并发 的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性
  持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。
  持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。

事务隔离级别

数据并发问题

脏写(Dirty Write)

对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写

脏读(Dirty Read)

对于两个事务 Session A, Session B, Session A 读取 了已经被 Session B更新但还 没有被提交 的字段。之后若 Session B 回滚，Session A 读取 的内容就是 临时且无效 的,这种现象就称之为 脏读 。

不可重复读(Non-Repeatable Read)

对于两个事务Session A, Session B, Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。

幻读(Phantom)

对于两个事务Session A, Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中 插入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。

SQL中的四种隔离级别

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
  - SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

脏写 怎么没涉及到？因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

MySQL的默认隔离级别为REPEATABLE READ

MySQL事务日志

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

• 事务的隔离性由 锁机制 实现。
• 而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
  • REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

redo日志(持久性)

为什么需要REDO日志

InnoDB存储引擎是以 页为单位 来管理存储空间的。在真正访问页面之前，需要把在 磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须 先更新缓冲池,中的数据，然后缓冲池中的 脏页 会以一定的频率被刷入磁盘(checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

另一方面，事务包含 持久性 的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。

那么如何保证这个持久性呢？ 一个简单的做法 ：在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘，但是这个简单粗暴的做法有些问题:

• 修改量与刷新磁盘工作量严重不成比例
  有时候我们仅仅修改了某个页面中的一个字节，但是我们知道在InnoDB中是以页为单位来进行磁盘IO的，也就是说我们在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，我们又知道一个页面默认是16KB大小，只修改一个字节就要刷新16KB的数据到磁盘上显然是太小题大做了
• 随机IO刷新较慢
  一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，假如该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的Bufer Pool中的页面 刷新到磁盘 时，需要进行很多的 随机IO，随机IO比顺序IO要慢，尤其对于传统的机械硬盘来说。

另一个解决的思路 ：我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西 记录一下 就好。比如，某个事务将系统表空间中 第10号 页面中偏移量为 100 处的那个字节的值 1 改成 2 。我们只需要记录一下：将第0号表空间的10号页面的偏移量为100处的值更新为 2 。

通过上面的思路就得到了REDO日志.

REDO日志的好处和特点

1.好处

• redo日志降低了刷盘效率
• redo日志占用的空间非常小
  2.特点
• redo日志是顺序写入磁盘的
• 事务执行过程中,redo log不断记录

redo的组成

redo log可以简单分为以下两个部分:

• 重做日志的缓冲(redo log buffer),保存在内存中,是易失去的.
• 重做日志文件(redo log file,保存在磁盘中,是永久的.

redo的整体流程

redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以 一定的频率 刷入到真正的redo log file 中。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。

针对这种情况，InnoDB给出innodb_flush_log_at_trx_commit参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

• 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）
• 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
• 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

UNDO日志(原子性,一致性)

什么是undo日志?

事务需要保证 原子性 ，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

• 事务执行过程中可能遇到各种错误，比如 服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导致的错误。
• 程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为 回滚 ，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合 原子性 要求。

undo日志的作用

• 作用1:回滚数据
  • 用户对undo日志可能 有误解:undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是 逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
  • 这是因为在多用户并发系统中，可能会有数十、数百其至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。
• 作用2:MVCC
  • undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息工以此实现非锁定读取。

undo的类型

在InnoDB存储引擎中,undo log分为

• insert undo log
• update undo log

小结

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。

redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程。