数据库核心知识笔记

Categories: Database / MySQL 2024-12-03

Buffer Pool

Change Buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

页面管理

frame 满了怎么办：驱逐一个页，驱逐算法，lru，lru-k。可以被驱逐的页都是 pin = 0 的。在选驱逐页时判断是否是还在 pin 的，pin-count > 0 的页是不能被驱逐的。

unpin 和 pin，如果没有 unpin 会怎么样。

flush 刷脏页，为啥在有日志（write ahead logging）的情况下，还要定期刷脏页？

还维护一个 flush 链表，定期刷脏，好处是恢复的时候可以减少操作量。只依赖 WAL 而不定期刷脏页，一旦系统崩溃，恢复时需要从日志重做的操作可能非常多，导致恢复时间较长。定期刷脏页可以减少系统恢复时需要重做的操作数量，从而显著缩短恢复时间。

WAL 日志

WAL 机制确保在数据页更新之前，先将这些修改记录（日志）写入磁盘。这样即使数据库崩溃，在恢复时也可以根据日志重做未完成的操作，确保数据一致性。

日志的作用：recover 的时候根据日志恢复成 crash 之前的样子。

并发控制

协议

2PL 协议

2PL 协议是一个悲观的并发控制协议，它使用锁来确定是否允许事务动态访问数据库中的对象。协议不需要提前知道事务将执行的所有查询。

GROWING 阶段：每个事务都会向 DBMS 的锁管理器请求所需的锁。锁管理器授予/拒绝这些锁请求。

SHRINKING 阶段：事务在释放第一个锁后立即进入收缩阶段。在收缩阶段，只允许事务释放锁。他们不允许购买新的。

对于每种隔离级别有所不同：

2PL 足以保证冲突可串行化。它生成优先级图是无环的调度。但它很容易受到级联中止的影响，即当一个事务中止时，现在必须回滚另一个事务，这会导致工作浪费。2PL 仍然可能存在脏读，并且还可能导致死锁。还有一些潜在的计划是可序列化的，但 2PL 不允许（锁定会限制并发性）。

级联中止：在两阶段锁协议（2PL）中，事务可能会读取其他事务尚未提交的数据（这取决于隔离级别），这种读取行为会形成依赖关系。当一个事务中止时，所有依赖于该事务读取未提交数据的事务也必须中止，这会导致工作浪费和系统性能下降。

假设有两个事务 T1 和 T2，T1 先执行，T2 依赖于 T1 的数据进行操作：

T1：开始事务，写入数据 A。
T2：开始事务，读取数据 A（此时 A 尚未提交）。
T1：由于某种原因中止（回滚）。

在这种情况下，因为 T2 读取了 T1 的未提交数据，如果 T1 中止，T2 也必须中止，以确保数据的一致性。

如果一个事务写入的任何值在这个事务提交之前不会被另一个事务读取或覆盖，则调度是严格的。严格两阶段锁协议（Strict 2PL）：事务仅在提交时释放锁。这种方法的优点是可以防止其他事务读取未提交的数据，不会导致级联中止。DBMS 还可以通过恢复已修改元组的原始值来反转中止事务的更改。严格的 2PL 会生成更加谨慎/悲观的计划，从而限制并发性。

可恢复调度：确保所有读取未提交数据的事务在其依赖事务提交之后再提交，从而防止级联中止，但影响并发性能。

处理 2PL 中的死锁有两种方法：检测和预防

1. 死锁检测

为了检测死锁，DBMS 创建一个等待图，其中事务是节点，如果事务 Ti 正在等待事务 Tj 释放锁，则存在从 Ti 到 Tj 的有向边。系统将定期检查等待图中的环（通常使用后台线程），然后决定如何打破它。

当 DBMS 检测到死锁时，它将选择一个”受害者”事务来中止以打破循环。受害者事务将重新启动或中止，具体取决于应用程序调用它的方式。

死锁了怎么办，如何检测死锁，构建 wait-for 依赖图，对于一个资源，阻塞的事务等待已经获得该资源锁的事务，判断是否有环，dfs 或者拓扑排序判断

2. 死锁预防

死锁预防不是让事务尝试获取所需的任何锁，然后再处理死锁，而是在事务发生之前阻止事务导致死锁。当一个事务试图获取另一个事务持有的锁时（这可能会导致死锁），DBMS 会杀死其中一个事务。

为了实现这一点，根据时间戳为事务分配优先级（较旧的事务具有更高的优先级）。这些方案保证不会出现死锁，因为等待锁时只允许一种方向。当事务重新启动时，DBMS 会重用相同的时间戳。在死锁预防下，有两种方法可以终止事务：

• wait-die（“Old Waits for Young”）：如果请求事务的优先级高于持有事务，则它会等待。否则，它将中止。

• Wound-Wait（“Young Waits for Old”）：如果请求事务的优先级高于持有事务，则持有事务将中止并释放锁。否则，请求事务将等待。

时间戳排序协议

基本时间戳排序协议 (BASIC T/O) 允许在不使用锁的情况下读取和写入数据库对象（乐观的）。每个数据库对象 X 都标有在该对象上成功执行读取（表示为 R-TS(X)）或写入（表示为 W-TS(X)）的最后一个事务的时间戳。DBMS 检查每个操作的这些时间戳。如果事务尝试以违反时间戳顺序的方式访问对象，则事务将中止并重新启动。

读操作：如果 TS(Ti) < W-TS(X)，则 Ti 被终止并使用重新的时间戳启动。如果 TS(Ti) >= W-TS(X)，读取有效并且允许 Ti 读取 X。然后 DBMS 将 R-TS(X) 更新为 R-TS(X) 和 TS(Ti) 中的最大值。它还必须在私有工作区中制作 X 的本地副本，以确保 Ti 的可重复读取。

写操作：如果 TS(Ti) < R-TS(X) 或 TS(Ti) < W-TS(X)，则必须重新启动 Ti。否则，DBMS 允许 Ti 写入 X 并更新 W-TS(X)。同样，它需要制作 X 的本地副本以确保 Ti 的可重复读取。

优化：Thomas 写入规则：写入的优化是，如果 TS(Ti) < W-TS(X)，则 DBMS 可以忽略写入并允许事务继续，而不是中止并重新启动它。这称为托马斯写入规则。请注意，这违反了 Ti 的时间戳顺序，但这没关系，因为没有其他事务会读取 Ti 对对象 X 的写入，因为读的事务的 TS>=W-TS(X)。如果不使用 Thomas Write 规则，基本 T/O 协议会生成一个可冲突序列化的调度。它不会出现死锁，因为没有事务会等待。然而，如果短事务持续引起冲突，则长事务可能会出现饥饿。

潜在问题：

• 将数据复制到事务工作区以及更新时间戳会产生较高的开销。

• 长时间运行的事务可能会陷入饥饿状态。事务从较新的事务中读取某些内容的可能性会增加。

• 在高并发系统上遇到时间戳分配瓶颈。

OCC 协议

乐观并发控制（OCC）是另一种乐观并发控制协议，它也使用时间戳来验证事务。当冲突数量较少时，OCC 效果最佳。这是当所有事务都是只读的或者当事务访问不相交的数据子集时。如果数据库很大并且工作负载不偏斜，那么发生冲突的可能性就很低，这使得 OCC 成为一个不错的选择。**在 OCC 中，DBMS 为每个事务创建一个私有工作区。事务的所有修改都会应用于此工作区。读取的任何对象都会复制到工作区，写入的任何对象都会复制到工作区并在其中进行修改。任何其他事务都无法读取另一个事务在其私有工作区中所做的更改。**当事务提交时，DBMS 会比较该事务的工作区写入集，以查看它是否与其他事务冲突。如果不存在冲突，则写入集将安装到”全局”数据库中。

OCC 包含三个阶段：

读取阶段：在此阶段，DBMS 跟踪事务的读/写集并将其写入存储在事务的私有工作区中。
验证阶段：当事务提交时，DBMS 检查它是否与其他事务冲突。
写入阶段：如果验证成功，DBMS 将私有工作区更改应用到数据库。否则，它将中止并重新启动事务。

当事务进入验证阶段时，DBMS 会为其分配时间戳。为了确保只允许可序列化的调度，DBMS 将 Ti 与其他事务检查 RW 和 WW 冲突，并确保所有冲突都是单向的。

后向验证：在事务提交前检查它与之前已经验证通过的事务是否存在冲突。在后向验证中，事务只需与已经提交的事务进行验证。

假设有两个事务 Ti 和 Tj，且 Ti 的 CTS(提交时间戳) < Tj 的 CTS（Ti 在 Tj 之前进入验证阶段），那么 Ti 和 Tj 的验证条件如下：

Ti 的写集合与 Tj 的读集合是否有交集：
- 如果 Ti 的写集合与 Tj 的读集合有交集，说明 Tj 在读阶段读取的数据被 Ti 修改了，Tj 的结果可能无效，因此 Tj 需要回滚。
Ti 的写集合与 Tj 的写集合是否有交集：
- 如果 Ti 的写集合与 Tj 的写集合有交集，说明 Tj 在写入数据时可能会覆盖 Ti 的写入，导致数据不一致，因此 Tj 需要回滚。

前向验证：

DBMS 检查提交事务与所有其他正在运行的事务的时间戳顺序。尚未进入验证阶段的事务被分配时间戳为 ∞。

如果 TS(Ti) < TS(Tj)，则必须满足以下三个条件之一：

Ti 在 Tj 开始执行之前完成所有三个阶段（串行排序）。
Ti 在 Tj 开始写入阶段之前完成，并且 Ti 不会写入 Tj 读取的任何对象。 • 写入集(Ti) ∩ 读取集(Tj) = ∅。
Ti 在 Tj 完成其读取阶段之前完成其读取阶段，并且 Ti 不会写入由 Tj 读取或写入的任何对象。 • WriteSet(Ti) ∩ ReadSet(Tj) = ∅，并且 WriteSet(Ti) ∩ WriteSet(Tj) = ∅。

MVCC

当事务写入对象时，DBMS 会创建该对象的新版本。当事务读取对象时，它会读取事务启动时存在的最新版本。MVCC 的基本概念/好处是写入者不会阻止写入者，读取者不会阻止读取者。这意味着一个事务可以修改对象，而其他事务则读取旧版本。

有四个重要的 MVCC 设计决策：

并发控制协议
版本存储
垃圾收集
索引管理

并发协议的选择是（两阶段锁定、时间戳排序、乐观并发控制）。

锁粒度

数据库锁层次结构：

数据库级（很少见）
表级（很常见），表锁-S/X
页级（常见）
元组级（很常见）：行锁-S/X
属性级（很少见）

MDL 元数据锁

在 MySQL 中，当一个事务正在访问一个表时，它会持有一个元数据锁。**元数据锁的作用是确保在事务执行过程中，表的结构不会发生变化。**元数据锁有多种类型，其中最严格的是 MDL-X（Exclusive）锁，它用于阻止其他事务对表的结构进行修改。

当一个事务正在访问一个表时（例如执行 SELECT、INSERT、UPDATE 或 DELETE 操作），它通常会持有一个较低级别的 MDL 锁（如 MDL-S（Shared）锁）。在这种情况下，其他事务仍然可以访问该表（例如执行 SELECT 操作），但无法获得 MDL-X 锁。这意味着，如果一个事务试图执行 DDL 操作（如 ALTER TABLE、DROP TABLE 等），它将被阻塞，直到持有 MDL-S 锁的事务完成。

意向锁

意向锁允许以共享模式或独占模式锁定更高级别的节点，而无需检查所有后代节点。如果节点处于意图模式，则显式锁定将在树中的较低级别完成。

• 意向共享(IS)：表示事务打算在更细粒度（如行级别）上获取共享锁（S 锁）

可以在更高级别（如表级别）上与其他 IS 锁、IX 锁共存，但不能与 S 锁、X 锁、SIX 锁共存。

• 意向独占(IX)：示事务打算在更细粒度（如行级别）上获取排他锁（X 锁）。

以在更高级别（如表级别）上与其他 IS 锁、IX 锁共存，但不能与 S 锁、X 锁、SIX 锁共存。

• 共享+意图独占(SIX)：表示事务已经在更高级别（如表级别）上获取了共享锁（S 锁），并打算在更细粒度（如行级别）上获取排他锁（X 锁）。

可以在更高级别上与其他 IS 锁、IX 锁共存，但不能与 S 锁、X 锁共存。

隔离级别

可串行性允许程序员忽略并发问题，但强制执行它可能会导致并行性过低并限制性能。我们可能希望使用较弱的一致性级别来提高可扩展性。隔离级别控制事务暴露于其他并发事务的操作的程度。

异常：

• 脏读：读取未提交的数据。

• 不可重复读取：重做读取会导致不同的结果。

• 幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。插入或删除会导致相同范围扫描查询的不同结果。

四种隔离级别（从最强到最弱）

可串行化：无幻读，所有读取均可重复，且无脏读。
可重复读取 RR：幻读可能会发生。
READ-COMMITTED：可能会发生幻读和不可重复读。
READ-UNCOMMITTED：所有异常情况都可能发生。

两阶段协议，为啥这样就能实现 RR 的隔离级别？

在 RR 中，在第一个锁释放后进入 shrinking 阶段，而 RR 中规定在 shrinking 阶段不能获取任何锁，只能释放锁，因此事务在释放了 S 锁后，其他事务可以获取 X 锁进行更新，但事务不会再获取该 S 锁了，进而保证不会读取到其他事务更新后的数据，确保了可重复读。

RU: 允许事务读取尚未提交的其他事务的修改，脏读。

RC：仅允许事务读取已经提交的其他事务的修改，不可重复读。

RR：确保在事务执行期间，每次读取的数据都是一致的，可能会幻读，其他事务在两次读取之前插入了数据。

解决幻读 -> 间隙锁（RR 隔离级别下）

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

注意：原文中引用了图片 image-20240901163235113.png，需要从原文件路径复制图片到博客的 public 目录

由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

这样对于 id=0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

SQL

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
 
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/
 
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。所以，id=0 这一行的问题解决了。

但是，id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经把所有的记录都上了锁，还是阻止不了 id=1 这一行的插入和更新呢？

因为在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录。

**产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的”间隙”。**因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)，锁的就是两个值之间的空隙。比如表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

**跟间隙锁存在冲突关系的，是”往这个间隙中插入一个记录”这个操作。**间隙锁之间都不存在冲突关系。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

这个 supremum 从哪儿来的呢？

这是因为 +∞ 是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的”都是前开后闭区间”

间隙锁也会引入一些死锁问题。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

注意：间隙锁是在可重复读隔离级别下才会生效的。所以，如果隔离级别设置为读提交的话，就没有间隙锁了。但同时，就需要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

加锁规则

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

注意：“加 next-key lock(5,10]“操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

事务 ACID

原子性：事务中的所有操作要么全部成功，要么全部失败
一致性：事务在开始和完成时，数据库必须从一个一致状态转换到另一个一致状态
隔离性：多个事务同时执行时，它们之间不会互相干扰
持久性：一旦事务提交，其结果必须被永久保存，即使系统发生崩溃、断电等故障，数据也不会丢失。

日志

Redo Log

Redo Log 的写入机制

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

redo log buffer 里面的内容，是不是每次生成后都要直接持久化到磁盘呢？不需要，如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它控制 redo log 的写入策略：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中；
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

**redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。**注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
**另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。**假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

MySQL 的”双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

组提交优化

为了节省 IO，采用组提交的方式，引入日志逻辑序列号（log sequence number，LSN）用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log，LSN 的值就会加上 length。

eg：3 个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。trx1 第一个 commit 要开始写盘的时候，trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。

两阶段提交

两阶段提交：prepare 阶段写入 redo log(write + fsync)，此时日志状态为”Prepare”，提交阶段写入 binlog(write + fsync)，在 binlog 成功写入后，将 Redo Log 的状态从 “Prepare” 更新为 “Commit”。此时，事务正式提交，数据变更对外可见。

两阶段提交的作用

确保一致性：通过先写 Redo Log 的 Prepare 状态，再写 Binlog，最后提交 Redo Log，可以确保在发生系统崩溃时，Redo Log 和 Binlog 是一致的。
崩溃恢复
- 如果在提交阶段之前发生崩溃（即 Redo Log 已写入 Prepare 状态，但 Binlog 尚未写入或未持久化），数据库重启时会检查 Redo Log 的状态，可以通过检查 Redo Log 的状态，决定是否回滚该事务。
  
  在 Binlog 中找到与 Redo Log “Prepare” 状态对应的事务（即 Binlog 记录存在并完整），MySQL 会提交该事务。它将 Redo Log 的状态从 “Prepare” 变为 “Commit”。
  
  如果 Binlog 记录不存在（例如 Binlog 未完全写入或未刷盘），MySQL 将回滚该事务。确保事务不会因为 Binlog 的缺失而导致数据不一致。

MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的时间拖到了 binlog 的 write 之后

WAL 机制主要得益于两个方面：

redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

Binlog

binlog 的特性确保了在备库执行相同的 binlog，可以得到与主库相同的状态。

Binlog 格式

statement

binlog 里面记录的就是 SQL 语句的原文

SQL

mysql> show binlog events in 'master.000001';

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。会出现主备数据不一致的情况

row

binlog 里面会记录整行的信息，记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。

可以通过 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容

BASH

mysqlbinlog -vv data/master.000001 --start-position=8900;

mixed

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，比如：恢复数据。

重放 binlog 数据的时候用 mysqlbinlog 解析出日志，然后把里面的 statement 语句直接拷贝出来执行。这个方法是有风险的。因为有些语句的执行结果是依赖于上下文命令的，直接执行的结果很可能是错误的。

eg: insert into t values(10,10, now()); binlog 设置为 mixed，binlog 存是 statement 格式。如果这个 binlog 过了 1 分钟才传给备库的话，那主备的数据不就不一致了吗？binlog 在记录 event 的时候，多记了一条命令：SET TIMESTAMP=1546103491。它用 SET TIMESTAMP 命令约定了接下来的 now() 函数的返回时间。这样就能确保主备一致性。

所以，用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：

BASH

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

双 M 结构

注意：原文中引用了图片 image-20240902005350270.png，需要从原文件路径复制图片到博客的 public 目录

节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

双 M 结构会存在 binlog 循环复制问题

client 在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。

如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？

MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id。因此，我们可以用下面的逻辑，来解决两个节点间的循环复制的问题：

规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；也就是说 A 节点传到节点 B 生成的 binlog 的 server id 也是 A 的 server id
每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

Binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入 write 到 binlog 中，并清空 binlog cache。

写入 binlog 中先写入 fs 的 page cache，并没有把数据持久化到磁盘，速度是比较快的。fsync，才是将数据持久化到磁盘的操作。一般情况下，只认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync，只写入 page cache 就行
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

缓冲池管理策略

DBMS 需要确保以下保证：

• 一旦 DBMS 告诉某人它已提交，任何事务的更改都是持久的。

• 如果事务中止，则任何部分更改都不会持久。

窃取策略规定 DBMS 是否允许未提交的事务覆盖非易失性存储中对象的最近提交的值（事务是否可以将属于不同事务的未提交的更改写入磁盘？）。

• STEAL：允许。

• NO-STEAL：不允许。

强制策略指示 DBMS 是否要求在允许事务提交（即，将提交消息返回给客户端）之前，事务所做的所有更新都反映在非易失性存储上。

• FORCE：需要

• NO-FORCE：不需要

强制写入可以更轻松地恢复，因为所有更改都会保留，但会导致运行时性能较差。

最容易实施的缓冲池管理策略称为 NO-STEAL + FORCE。在此策略中，DBMS 永远不必撤消中止事务的更改，因为更改不会写入磁盘。它也永远不需要重做已提交事务的更改，因为所有更改都保证在提交时写入磁盘。

WAL

通过预写日志记录，DBMS 在对磁盘页面进行更改之前，将对数据库所做的所有更改记录在日志文件（稳定存储上）中。该日志包含足够的信息来执行必要的撤消和重做操作，以在崩溃后恢复数据库。DBMS 必须先将与对数据库对象所做的更改相对应的日志文件记录写入磁盘，然后才能将该对象刷新到磁盘。

每个日志条目都包含回退或重播对单个对象的更改所需的信息：

• 事务 ID。

• 对象 ID。

• Before Value（用于 UNDO）。

• After Value（用于 REDO）。

索引

索引是一种数据结构，它通过为数据库表中的列创建额外的数据存储来加速数据的检索。

数据结构

为啥不用红黑树（二叉），树高太高，IO 次数多。

B-tree/B+tree

B+ 相比与 B 能存更多的索引，因为内部节点不存储数据，数据都存在叶子节点上，只存索引，这样就有更多的子节点（树分叉更多）。叶子节点还包含一个指针指向下一个 leaf page，方便做范围查询。

B+Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。一层一层的找下来，然后在索引值对应的叶子节点上找是否有相应的索引值。

Hash 索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值（一个较小的值），并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

索引分类

主键索引 vs 二级索引

主键索引（Primary Key）：主键是表中的一个或多个字段，它的值唯一标识表中的每一行。主键的值不能重复，也不能为 NULL。在创建表时，可以通过 PRIMARY KEY 约束来定义主键。主键自动为表创建主键索引。

多字段主键：a 和 b，a 可以相同，但 a+b 不能相同。

二级索引：叶子节点只包含索引列和指向数据行的指针（主键），不包含其他列的数据

唯一索引（Unique Index）：唯一索引是不允许包含重复值的索引。如果某个字段的值必须唯一，但不是主键，那么可以为该字段创建唯一索引。可以通过 UNIQUE 约束或 CREATE UNIQUE INDEX 语句来创建唯一索引。

非唯一非主键索引（普通索引）：这种索引既不是主键索引，也不是唯一索引。它允许索引字段包含重复的值。这种索引主要用于提高查询性能，特别是当表中有大量数据，且经常需要基于某些字段进行查询时。

二级索引访问需要两次索引查找，而不是一次 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次 B-Tree 查找而不是一次。对于 InnoDB，自适应哈希索引能够减少这样的重复工作。

联合索引（多列索引）

为多个列创建的索引，而非在多个列上都单独创建一个索引，如 a，b，c 三列创建联合索引 abc。

前缀特性：该索引能够匹配：a|ab|abc，但不能匹配 b，bc

where a = x and b > y and c = z，c 不能用上索引，可以利用 ab 找到 a=x 里面 b>y 的部分，但是在 y>b 的部分里 c 是无序的。

聚簇索引 vs 非聚簇索引

— 并非索引类型，而是一种数据的存储方式。

聚簇：索引 kv 对，k 是索引字段值，v 是数据行，不用去回表。为了保证数据的一致性和节省空间大小，一个表只有一个聚簇索引。eg：Innodb 的主键索引

非聚簇：索引 kv 对，k 是索引字段值，v 是指向记录的指针（pageid + rowid），或主键值，需要再去查询表中获取数据。eg：二级索引，v 是主键值。

覆盖索引

使用索引来直接获取列的数据，这样就不再需要回到主键索引去（回表）读取数据行。如果索引的叶子节点中已经包含要查询的数据，就不用再回表查询。如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为”覆盖索引”。

eg: 查询 name，age，而 name，age 字段正好建立了联合索引

建立索引技巧

普通索引和唯一索引，应该怎么选择？

查询过程：

eg：select id from T where k=5。这个查询语句选择 k 这个索引，先是通过 B+ 树从树根开始，按层搜索到叶子节点，在叶子节点数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

因为 InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。对于普通索引来说，要多做的那一次”查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

更新过程：

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用

第一种情况是，这个记录要更新的目标页在内存中。

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中。

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

普通索引和唯一索引应该怎么选择：这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

分区表

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。

MySQL 实现分区表的方式—对底层表的封装—意味着索引也是按照分区的子表定义的，而没有全局索引。全局索引一般是一个优化点。

MySQL 在创建表时使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区—只需要查找包含需要数据的分区就可以了。explain 中 partition 列中表明要 scan 的分区。

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便

select：在查询分区表时，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引警接口访问各个分区的数据。

insert：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。delete 同理

update：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL 先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

这里的锁住所有的底层表并不是说在处理过程中是锁住全表的。

有些操作是支持过滤的。例如，当删除一条记录时，MySQL 需要先找到这条记录，如果 WHERE 条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对 UPDATE 语句同样有效。如果是 INSERT 操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL 先确定这条记录属于哪个分区，再将记录写人对应的底层分区表，无须对任何其他分区进行操作。

分区选择：根据范围，eg 年份，根据键值，hash，取模等等

使用分区表

对于访问分区表来说，很重要的一点是要在 WHERE 条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这些条件，MySQL 就需要让对应存储引警访问这个表的所有分区，如果表非常大的话就可能会非常慢。

此外，MySQL 只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。这就和查询中使用独立的列才能使用索引的道理是一样的。

优点

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。历史数据可以存储到 OSS 中
分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
可以使用分区表来避免某些特殊的瓶颈，例如 InnoDB 的单个索引的互斥访问、ext3 文件系统的 inode 锁竞争等

缺点

NULL 值会使分区过滤无效，NULL 值会存在第一个分区，可能第一个分区很大
选择分区的成本可能很高
打开并锁住所有底层表的成本可能很高