基础知识

设计数据库的步骤

数据库三大范式

MySQL 的存储引擎对比

MySQL 的数据类型

一条查询语句的执行过程

查询优化相关

Buffer Pool

事务相关

事务的概念：事务是一系列操作组成的工作单元，该工作单元内的操作是不可分割的，即：要么所有操作都做，要么所有操作都不做。

事务的作用：事务的产生是为了当应用程序访问数据库的时候，事务能够简化我们的编程模型，不需要我们去考虑各种各样的潜在错误和并发问题。

MySQL 中只有 InnoDB 存储引擎支持事务，MyISAM、Memory、Merge 存储引擎都不支持事务。

事务的 ACID

一般来说，事务必须满足四个条件，分别是：原子性 (Atomicity)、一致性 (Consistency)、隔离性 (Isolation)、持久性 (Durability)。

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会出现只完成了一半的情况。
如果一个事务在执行的过程中发生了错误，则数据会被回滚到该事务开始前的状态。

MySQL 的回滚是用撤销日志 (Undo Log) 实现的，Undo Log 中存储着 回滚需要的所有数据， 当需要进行 回滚操作时，只需反向执行操作即可。

隔离性：数据库允许多个并发事务对数据进行操作，那么就会产生一系列并发问题。
针对数据资源的并发访问，规定了各个事务之间相互影响的程度。

事务隔离分为不同的级别，包括：读未提交 (Read uncommitted)、读已提交 (read committed)、可重复读 (repeatable read) 和串行化 (Serializable)。

不同的隔离级别在应对数据资源并发访问可能出现的问题时的要求不一样。
数据资源并发访问时可能出现的问题，按严重性排列依次为：脏写 (Dirty Write)、脏读 (Dirty Read)、不可重复读 (Non-Repeatable Read)、幻读 (Phantom)。
脏写的问题过于严重，不论是哪种隔离级别都不能发生 脏写 的问题。

  • 读未提交：可能发生 脏读、不可重复读、幻读 的问题
  • 读已提交：解决了 脏读的问题，可能发生 不可重复读、幻读 的问题
  • 可重复读：解决了 脏读、不可重复读的问题，可能发生 幻读 的问题
  • 串行化：解决了 脏读、不可重复读、幻读 的问题

可重复读：一个事务启动的时候，能够看到所有已经提交的事务结果。但是，这个事务执行期间，其他事务的更新对该事务不可见。

MySQL 默认的隔离级别为：可重复读。

一致性：数据库从一个正确的状态，变为另一个正确的状态。
什么叫正确的状态呢?
正确的状态是指：当前的状态满足预定的约束。
比如：1、转账的前后，两人的账户总额不变；2、账户的金额必须为 正数

对于一致性的理解，网上也存在争议
知乎：如何理解数据库事务中的一致性

原子性、隔离性、持久性都是数据库的特征，也就是说这三个依赖数据库的具体实现。

而 一致性 实际上依赖于应用层，也就是说数据库本身能为我们保证一部分一致性需求。
比如：为表建立主键、唯一索引、外键、声明某个列为 not null 来拒绝 null 值的插入、check 语法。
但是更多的一致性需求需要程序员写的业务代码来保证。
比如：执行一些操作后，检查发现我们的约束被破坏了，那么程序就手动进行事务的回滚。

事务具备的一致性是说：通过事务的原子性、隔离性、持久性来保证我们的一致性。

持久性：一旦事务提交，该事务做的修改就会永久保存到数据库中。即使系统故障，修改的数据也不会丢失。

我们对数据库修改后，所做的修改并没有立刻写入磁盘，而是先修改缓冲池 (Buffer Pool) 中的数据，并产生相应的 Redo 日志。
在事务提交后，即使系统发生了故障，我们也能通过 Redo 日志，把没有写入磁盘的数据恢复。

事务并发执行遇到的问题

总结：

  • 脏写是：修改了未提交但被修改过的数据
  • 脏读是：读到了未提交但被修改过的数据
  • 不可重复读是：读到了已提交的数据，但提交后读 和 提交前读到的不一致
  • 幻读：读到了已提交的新插入的数据

脏写：事务 A 修改了另一个未提交的事务 B 修改过的数据，但是事务 B 在 事务 A 提交后回滚了。

时间顺序 Session A Session B
1 begin; begin;
2 update t1 set c1 = ‘B’ where id = 1
3 update t1 set c1 = ‘A’ where id = 1
4 commit;
5 rollback;

Session A 和 Session B 各开启了一个事务， 我们下面把他们分别称为 事务 A 和 事务 B。
事务 A 和事务 B 在更新同一条数据，事务 B 先把它更新为 B 值，还没有 提交 / 回滚，事务 A 把它更新为 A 值。
更新之后，事务 A 和 事务 B 都会记录一条 Undo 日志。

因为事务 B 是先更新的，它在更新之前，这行数据的值为 v。
所以事务 B 的 Undo 日志中记录的旧值为 v。
如果此时事务 A 更新完数据的值后，事务 B 回滚了，那么这行数据就会回滚为 事务 B 的 Undo 日志中记录的旧值 v。
对于事务 A 来说，它修改了 这行记录的值为 A，也提交了该事务，但是该行记录的值仍为 v。
相当于：事务 A 的修改未生效。

脏读：事务 A 读到了另一个未提交的事务 B 修改过的数据，但是事务 B 回滚了。

时间顺序 Session A Session B
1 begin; begin;
2 update t1 set c1 = ‘B’ where id = 1
3 select * from t1 where id = 1
4 commit;
5 rollback;

相当于：事务 A 读到了一个不存在的数据。

不可重复读：事务 A 能读到另一个已经提交的 事务 B 修改过的数据。但是 事务 A 在事务 B 「提交前读出的数据」和 「提交后读出的数据」不一致，并且每次记录更新，事务 A 都能读到该记录的新值。

时间顺序 Session A Session B
1 begin; 该事务设置自动提交
2 select * from t1 where id = 1（此时读到 A）
3 update t1 set c1 = ‘B’ where id = 1
4 select * from t1 where id = 1（此时读到 B）
update t1 set c1 = ‘C’ where id = 1
5 select * from t1 where id = 1（此时读到 C）

幻读：事务 A 根据某个查询条件读到一些记录，之后事务 B 向该表中插入了一些符合那个查询条件的记录，
之后 事务 A 再次按照原查询条件查询，能读到 事务 B 新插入的记录。

时间顺序 Session A Session B
1 begin; 该事务设置自动提交
2 select * from t1 where id > 0
3 insert into t1 values(B)
4 select * from t1 where id > 0（能读到 B）

对 MVCC 的理解

MVCC (Multiversion Concurrency Control) 是多版本并发控制技术。

MySQL 的 InnoDB 存储引擎用 MVCC 来实现 读已提交 和 可重复读 这两种隔离级别
InnoDB 存储引擎支持 MVCC，其他的存储引擎不支持事务，故没有用到 MVCC

它通过由数据行的 Undo 日志组成的版本链，来实现对数据库事务的并发控制。

MVCC 的原理：版本链 + ReadView
对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列

  • transaction_id：每次事务对某条记录进行修改时，都会把该事务的 id 赋值给该隐藏列
  • roll_pointer：每次事务对某条记录进行修改时，该隐藏列都会指向该操作对应的 Undo 日志，Undo 日志中存储了修改前的信息

Undo 日志被存储到页面类型为 fil_page_undo_log 的页面中， 页面内的 Undo 日志形成链表，且 transaction_id值大的在前，该链表即版本链，版本链的头节点就是当前记录的最新值。
图片.png

对于隔离级别为 读未提交 的事务来说，直接读取记录的最新版本即可。
对于隔离级别为 串行化 的事务来说，InnoDB 存储引擎使用加锁的方式来访问记录。
对于隔离级别为 读已提交 和 可重复读 的事务来说，都必须保证只能读到已经提交的事务修改过的数据，不能读到未提交的事务修改过的数据，所以 InnoDB 存储引擎用 ReadView 来判断版本链中的哪个较新的版本对当前事务是可见的。

ReadView 中主要包含 4 个比较重要的内容

  • m_ids：表示在生成 ReadView 时，当前系统中活跃的读写事务的 事务id 列表
  • min_transaction_id：表示在生成 ReadView 时，m_ids 中的最小值
  • max_transaction_id：表示在生成 ReadView 时，系统应该分配给下一个事务的 id 值
  • creator_transaction_id：表示生成该 ReadView 的事务的 事务id

这样在访问某条记录时，用 ReadView 来判断版本链中的哪个较新的版本对当前事务是可见的。

  • 如果被访问版本的 transaction_id 属性值与 ReadView 中的 creator_trx_id 值相同，表明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的 transaction_id 属性值 小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交了，所以该版本可以被当前事务访问。
  • 如果被访问版本的 transaction_id 属性值 大于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的 transaction_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下transaction_id 属性值是不是在 m_ids 列表中：
    • 如果在，表明生成 ReadView 时，被访问版本的事务还是活跃的，该版本不可以被访问
    • 如果不在，表明生成 ReadView 时，被访问版本的事务已经被提交了，该版本可以被访问

读已提交 和 可重复读 实现的不同之处就在：ReadView 的生成时机不同

  • 读已提交：每次读取数据前，都生成一个 ReadView
  • 可重复读：在当前事务第一次读取该数据时，才生成一个 ReadView

    Redo Log

    Redo Log 是 InnoDB 存储引擎特有的日志。

Redo Log 让 MySQL 拥有了系统崩溃后的恢复能力，保证了数据的持久化。
当 MySQL 实例挂了 或 宕机了，InnoDB 存储引擎会使用 Redo Log 恢复未写入磁盘的数据。

实例是“内存”和“后台进程”的集合。数据库是数据的物理存储。 一个实例可以用于一个数据库，多个实例也可以同时用于一个数据库， 数据库和实例的关系是一对多的关系。 多个“实例”同时驱动一个“数据库”的架构叫“集群”

宕机，指：操作系统无法从一个严重系统错误中恢复过来，或系统硬件层面出问题，以致系统长时间无响应，而不得不重新启动计算机的现象。

MySQL 将数据划分为若干个页，以 页 作为磁盘和内存交互的基本单位。
即使我们只需要一条记录，MySQL 也会从磁盘 把该页的所有数据都加载到 Buffer Pool 中。
后续的查询都是：先去 Buffer Pool 中找，没有找到所需数据 再去磁盘加载数据到 Buffer Pool。
更新数据时，发现 Buffer Pool 中存储了要更新的数据，就直接更新 Buffer Pool 中的数据，将该数据页挂到 flush 链表上，在内存中记录“在某个数据页上做了什么修改”，等到一个 Mini-Transaction 结束时，将该过程中产生的一组 Redo 日志存储到 重做日志缓存 (Redo Log Buffer)，根据 MySQL 提供的刷盘策略将 重做日志缓存 (Redo Log Buffer) 存储到磁盘。

对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction
比如：修改一次 Max Row ID 的值就算是一个 Mini-Transaction 向索引对应的 B+ 树中插入一条记录的过程也算是一个 Mini-Transaction。
一个 Mini-Transaction 可以包含一组 redo 日志， 在进行奔溃恢复时，这一组 redo 日志作为一个不可分割的整体。

总结：一个事务可以包含若干条语句，每一条语句由若干个 Mini-Transaction 组成， 每一个 Mini-Transaction 包含若干条 redo 日志。

在 Redo Log Buffer 中的 Redo Log 的刷盘时机

  1. Redo Log Buffer 空间不足时（占用 1 / 2 容量左右）就会执行刷盘
  2. 一个事务提交时，执行刷盘操作（需要设置刷盘策略，默认刷盘策略）
  3. 后台线程不停的执行刷盘操作（默认频率为：每秒一次，有可以设置频率的参数）
  4. 正常关闭服务器时，执行刷盘操作
  5. 做 checkpoint 时，执行刷盘操作
  6. 等等

InnoDB 存储引擎为 Redo Log Buffer 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，
我们可以通过修改该参数的值，设置三种策略：

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
binlog 可以用来归档，也可以用来做主备同步

Undo Log

在 MySQL 中，事务的回滚机制是通过 Undo Log 实现的。
事务对数据进行修改时，会先把回滚需要的数据存储到 Undo Log 中，然后再执行相关的操作。

当需要进行 回滚操作时，只需反向执行操作即可。
Undo Log 会先于数据持久化到磁盘上，这样就保证了即使遇到突发情况重启数据库服务器后，可以回滚之前未提交的事务。

不同的修改操作 (insert、update、delete) 会产生不同类型的 Undo Log，
Undo Log 存储在页面类型为 fil_page_undo_log 的页面中，
一个页面里存储的都是相同类型的 Undo Log，页面形成链表，分为： Insert Undo 链表、Update Undo 链表。
MySQL 规定对普通表 和 临时表 进行修改时，产生的 Undo Log 要分别记录，
所以在一个事务中最多有 4 个以 Undo 页面 为节点组成的链表。
图片.png

为事务分配页面类型为 fil_page_undo_log 的页面链表的过程

  • 事务在执行的过程中，对普通表的记录首次做改动之前，首先会到系统表空间的第 5 号页面中分配一个回滚段（其实就是：获取一个 Rollback Segment Header 页面的地址）。

一旦某个回滚段被分配给了这个事务，那么之后该事务再对普通表的记录做改动时,就不会重复分配回滚段了
分配回滚段的方式为：循环使用 (round - robin)，意思就是这些回滚段被轮着分配给不同的事务。

  • 在该事务分配到回滚段后，首先看这个回滚段的两个 cached链表（Insert Undo Cached 链表、Update Undo Cached 链表） 有没有已经缓存了的 Undo Slot，
    • 如果对应的链表中有缓存的 Undo Slot，那么就把缓存的 Undo Slot 分配给该事务
    • 如果对应的链表中没有缓存的 Undo Slot，那么就要到类型为 Rollback Segment Header 的页面找一个可用的 Undo Slot 分配给该事务
  • Undo Slot 分配给事务后，相当于该事务拥有了 Undo页面链表的第一个页面。

把 Undo Log 写入 Undo 页面即可。

系统表空间的第 5 号页面的页面类型为：Transction System 在系统表空间的第 5 号页面中存储了 128 个 Rollback Segment Header 页面地址， 每个 Rollback Segment Header 页面都对应着一个回滚段。（即：回滚段只有一个页面）

在 Rollback Segment Header 页面中，又包含 1024 个 Undo Slot ，每个 Undo Slot 都对应一个 Undo 页面 链表。 Undo Slot 就是 Undo页面 链表 的 First Undo Page 的页号。

这 128 个回滚段被从 0 ~ 127 编号，分为两大类

  • 第 0 号、第 33 ~ 127 号回滚段属于一类，如果一个事务在执行过程中由于对普通表的记录做了改动，需要分配 Undo页面 链表时，必须从这一类的段中分配相应的 undo slo 。
  • 第 1 ~32 号回滚段属于一类。如果一个事务在执行过程中由于对临时表的记录做了改动，需要分配 Undo页面 链表时，必须从这一类的段中分配相应的 undo slot。

针对普通表和临时表划分不同种类的 回滚段 的原因：在修改针对普通表的回滚段中的 Undo页面 时，需要记录对应的 Redo Log，以保证 Undo Log 的持久性。而修改针对临时表的回滚段中的 Undo页面 时，不需要记录对应的 Redo Log。

第 0 号回滚段必须在系统表空间中， 第 33 ~ 127 号回滚段既可以在系统表空间中，也可以在自己配置的 Undo 表空间中。 第 1～32 号回滚段必须在临时表空间（对应着数据目录中的 ibtmp1 文件）中。

锁相关

疯狂创客圈 - MySQL 中锁的面试题总结

MySQL 提供的锁介绍

MySQL 中的锁是实现数据库事务并发执行的重要手段。

MySQL 提供了：全局锁、表级锁、行级锁。

  • 全局锁：表示对整个数据库实例加锁。

加上全局锁之后，整个数据库处于只读状态。
全局锁一般用于：给数据库做逻辑备份。
给数据库加全局锁的命令：flush tables with read lock;
释放锁的命令：unlock tables;（表级锁、行级锁释放也是这个命令）

  • 表级锁：表示对整张表加锁。

表级锁不会出现死锁，并发度低。
给数据库表加表级锁的命令：lock table 表名 read / write;

  • 行级锁：表示对某行记录加锁。

行级锁会出现死锁，并发度高。
给读到的记录加共享锁：select … lock in share mode;
给读到的记录加排他锁：select … for update;
表级锁 和 行级锁又分为 独占锁 和 共享锁，即存在四类：

  • 表级独占锁、表级共享锁、行级独占锁、行级共享锁

行级锁又细分为各种类型，不同类型的行级锁起的作用也不同，行级锁分为：

  • Record Lock：单个行记录的锁
  • Gap Lock：间隙锁，锁定一个范围，不锁定记录本身，Gap Lock 的作用仅是为了防止插入幻影记录
  • Next-Key Lock： 锁定一个范围 和 记录本身，Next-key Lock 用于解决幻读

共享锁 和 排他锁 的兼容关系

  • 一个事务对数据对象 A 加了 共享锁，可以对 A 进行读取操作，但是不能进行更新操作。

加锁期间其它事务能对 A 加 共享锁，但是不能加 排他锁。

  • 一个事务对数据对象 A 加了 排他锁，就可以对 A 进行读取和更新操作。

加锁期间其它事务不能对 A 加任何锁。

锁的类型 共享锁 排他锁
共享锁 √ ×
排他锁 × ×

所有存储引擎都支持全局锁。
InnoDB 存储引擎支持 表锁 和 行锁。
MyISAM、Memory、Merge 存储引擎只支持表锁，并且不支持事务。

封锁协议

封锁就是：事务 T 在对某个数据对象操作之前，先向系统发出请求对其加锁。

在运用 共享锁 和 排他锁对数据对象加锁时，还需要约定一些规则。 例如，申请什么锁、什么时候申请锁、持锁时间、什么时候释放锁等。这些规则称为封锁协议。

三级封锁协议

  1. 一级封锁协议：事务 T 修改数据 A 之前，必须先对其加 排他锁，直到事务 T 结果才能释放该 排他锁。

一级封锁协议解决了 脏写 的问题。

  1. 二级封锁协议：在一级封锁协议的基础上增加要求，事务 T 读数据 A 之前，必须先对其加 共享锁，读完马上释放该 共享锁。

二级封锁协议解决了 脏写、脏读 的问题。

  1. 三级封锁协议：在一级封锁协议的基础上增加要求，事务 T 读数据 A 之前，必须先对其加 共享锁，直到事务 T 结果才能释放该 共享锁。

三级封锁协议解决了 脏写、脏读、不可重复读 的问题。
图片.png图片.png图片.png

两段锁协议
MySQL 的 InnoDB 存储引擎采用两段锁协议，根据隔离级别在需要的时候自动封锁，在同一时刻释放所有锁。
两段锁协议是指：每个事务的执行必须分为两个阶段：加锁阶段 和 解锁阶段。
在加锁阶段只能进行加锁操作，在对数据进行读操作之前要申请并获得 共享锁，在进行写操作之前要申请并获得排他锁。如果加锁不成功，则事务进入等待状态，直到加锁成功才能继续执行。
在解锁阶段只能进行解锁操作，不能再进行加锁操作。

两段锁协议可以这样实现：事务开始后就处于加锁阶段，一直到执行 rollback 或 commit 之前都是加锁阶段。rollback 或 commit 使事务进入解锁阶段，即在 rollback 或 commit 后进行解锁操作。

事务遵循两段锁协议是保证可串行化调度的充分条件。
比如：下面操作满足两段锁协议，它是可串行化调度。
lock-x(A) … lock-s(B) … lock-s(C) … unlock(A) … unlock(C) … unlock(B)
但不是必要条件，比如：下面操作不满足两段锁协议，但它还是可串行化调度。
lock-x(A) … unlock(A) … lock-s(B) … unlock(B) … lock-s(C) … unlock(C)

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

切分相关

复制相关