本文是在阅读JavaGuide关于MySQL相关内容时的个人整理；

请移步：https://javaguide.cn

三大日志

MySQL 里常说的“三大日志”是 redo log、binlog 和 undo log。把它们放到一次更新里看，会更容易理清关系：

  1. 先把旧值相关信息写入 undo log，为回滚做准备，此时 undo log的 redo log 会写入 redo log buffer；
  2. 再修改内存中的数据页，也就是 Buffer Pool 里的页，这时会产生脏页；
  3. 同时把这次物理修改写入 redo log buffer；
  4. 事务提交时，再把 binlog 写好，并完成 redo log 的提交流程；
  5. 脏页本身不一定在提交时立刻刷回数据文件，而是由后台线程择机刷盘。

所以可以把三者粗略理解成：

  • redo log：负责把“已经做过、但还没落到数据文件里的修改”补回来，核心对应持久性；
  • undo log：负责把“不该保留的修改”撤销掉，核心对应原子性；
  • binlog：负责记录逻辑变更，用于主从复制，也参与判断事务是否真正提交成功。

更新时的日志和数据页

MySQL 查询数据时，会先把磁盘上的数据页读入内存中的Buffer Pool。更新操作也不是直接改磁盘，而是先改内存页，所以会产生脏页。

  1. redo log 有自己的缓冲区，事务执行过程中先写入 redo log buffer，后台线程可能会提前刷盘；而事务提交时，一般会强制把相关 redo log 刷到磁盘，典型配置是 innodb_flush_log_at_trx_commit=1。
  2. 脏页也可能在事务提交之前或之后被提前刷盘，但刷脏页之前，相关 redo log 必须已经落盘。
  3. undo log 也有缓冲区。它自己的刷盘时机和脏页刷盘没有简单的一一对应关系，但 undo log 对应的 redo log 一定会在脏页刷盘前落盘，因为这些修改在线性的 redo log 里位置更靠前。

这也是为什么 InnoDB 可以采用“先改内存、后刷磁盘”的方式：真正保证崩溃恢复能力的，不是脏页有没有立刻落盘，而是 redo log 有没有先落盘。

重做日志（redo log）

redo log 是 InnoDB 存储引擎独有 的物理日志，记录的是“某个数据页做了什么修改”。它的核心作用是：MySQL 宕机后，把那些已经提交、但还没来得及写回数据文件的修改重新做一遍。

它之所以重要，是因为：

  1. 更新先发生在 Buffer Pool，脏页刷盘通常是延迟的；
  2. 直接把脏页刷回磁盘偏向随机写，成本高；
  3. 而 redo log 是顺序写，性能更好，所以更适合作为“先持久化修改痕迹”的介质。

redo log 的落盘和恢复还要结合两个点理解：

  1. 磁盘上的 redo log 不是一个单文件，而是一个日志文件组，各文件大小相同，循环写入。
  2. InnoDB 用 checkpoint 控制哪些 redo log 可以被覆盖；如果 write pos 追上 checkpoint，说明可覆盖空间不够了，就必须优先推进刷盘，否则新的更新就无法继续写入 redo log。

redo log 为什么必须早于脏页落盘

这是通过 page_lsn 和 redo log 的 LSN 来保证的。

如果某个数据页的 page_lsn=1100，那这个页要刷盘时，对应的 redo log LSN 至少也要推进到 1100。也就是说，脏页落盘前，描述这次修改的 redo log 一定已经先安全落盘了。注意，这里说的是 redo log 已经从 redo log buffer 持久化到磁盘，并不是说它已经被 checkpoint 覆盖。

宕机恢复时，InnoDB 会从 checkpoint 之后顺序扫描 redo log 做恢复。之所以可能出现 page_lsn > redo_lsn，是因为磁盘上的这个页在宕机前就已经被刷成了一个更晚版本，而恢复程序当前才扫描到一条更早的 redo log。这说明该页已经包含了当前这条日志所描述的修改，甚至还包含了更晚的修改，所以这条 redo log 会被直接跳过，不会重复应用。也正因为如此，checkpoint 前进的含义是“更早的 redo log 已经不再是恢复所必需的了”；它通常是被脏页刷盘推动前进的，而不是脏页刷盘要等到 redo log 被 checkpoint 覆盖之后才能发生。

二进制日志（binlog）

binlog 是 MySQL Server 层的逻辑日志，按顺序记录所有涉及数据更新的逻辑操作。它最重要的用途不是页恢复，而是：

  1. 主从/主备复制；
  2. 数据恢复与归档；
  3. 配合 redo log 一起判断事务提交状态。

binlog 常见有三种记录格式：

  1. statement：记录 SQL 原文；
  2. row：记录具体改了哪些行，像 now() 这种函数会先求值再记录结果；
  3. mixed：由 MySQL 在 statement 和 row 之间自动选择。

它的写入过程也比较好记：每个事务（线程）都有独立的 binlog cache，事务执行时先写缓存，提交时再统一写入 binlog 文件。

两阶段提交

问题出在：redo log 和 binlog 不是同一套日志。

如果一个事务已经把 redo log 写好了，但 binlog 最终没写成功，那么 InnoDB 看起来像是“事务完成了”，而复制系统（主从复制）看到的却是“事务没发生”，这就会造成不一致。

为了解决这个问题，提交时会使用两阶段提交：

  1. 先把 redo log 写成 prepare 状态；
  2. 再写 binlog；
  3. 最后把 redo log 标记为 commit 状态。

这样宕机恢复时，就可以结合 redo log 的状态和对应 binlog 是否存在，来判断事务到底算不算真正提交：

  1. 如果 redo log 已经是 commit 状态，说明事务已经完成，需要前滚；
  2. 如果 redo log 还不是 commit，但能找到完整的 binlog，也说明事务已经完成，仍然需要前滚；
  3. 只有既不是 commit 状态、又找不到对应 binlog 的事务，才会被判定为未完成，需要回滚。

注意：这里的“回滚”靠的是 undo log，不是 redo log。

两阶段提交

图源：JavaGuide

回滚日志（undo log）

undo log 的核心作用是事务回滚，也就是保证原子性。一个事务执行过程中，如果后续失败了，或者在崩溃恢复时被判定为“未提交”，就需要依赖 undo log 把已经做过的修改撤销掉。

它还要注意两个点：

  1. undo log 自己的持久性并不是单独保证的，它也要靠 redo log 来保护；
  2. 事务提交后，undo log 也不是立刻全部删除，什么时候清理、能不能清理，要看 undo log 的类型，也和后面的 MVCC 有关。

所以 ACID 可以先粗记成：

  • 原子性（Atomicity）：主要靠 undo log；
  • 持久性（Durability）：主要靠 redo log；
  • 隔离性（Isolation）：主要靠锁和 MVCC；
  • 一致性（Consistency）：是事务机制、约束、日志和恢复共同作用后的结果。

宕机恢复时的协同工作

系统宕机重启后，InnoDB 会从 checkpoint 之后的 redo log 开始恢复，整体过程可以理解成两步：

  1. 先前滚：把已经记录在 redo log 里、但还没真正反映到数据文件里的修改补做出来；
  2. 再判断是否回滚：结合两阶段提交的状态，确认哪些事务已经提交，哪些事务没有提交。

如果某个事务最终被判定为未提交，就要依赖 undo log 做回滚。

这里还有一个容易忽略但很关键的点：即使 undo log 本身在宕机前还没真正刷到磁盘，也没关系。因为 undo log 的修改过程本身也会写入 redo log，所以重启后可以先通过对应的 redo log 把 undo log 恢复出来，再利用它去回滚未提交事务。

双写缓冲区

可以参考这篇文章：https://cloud.tencent.com/developer/article/2339261

理解完三大日志后，再看双写缓冲区(doublewrite buffer)就很自然了：它不是用来替代 redo log 或 undo log 的，而是专门解决脏页刷盘时的部分写问题（torn page）。原因是：

  1. 一个 InnoDB 页通常是 16KB；
  2. 操作系统页通常是 4KB；
  3. 所以一次刷页往往会拆成多次写入，这个过程不是原子的。

如果写到一半宕机，就可能出现“页只写了一半”的损坏页。这种情况下，单靠 redo log 不能直接修复，因为页本身已经坏了。

所以 InnoDB 会先把完整页顺序写到双写缓冲区（磁盘）里，再把这个页写回真正的数据文件：

  1. 如果是在写双写缓冲区时宕机，通常还能用旧页配合 redo log 重新恢复；
  2. 如果是在把页写回数据文件时只写了一半就宕机，就可以把双写缓冲区里那份完整页重新拷回去，再继续恢复。

事务隔离级别

4种级别和导致的问题

  1. READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  2. READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  3. REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  4. SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

RC 和 RR 都会用到 MVCC，但它们生成 Read View 的时机不同。RC 会在每次 select 前都生成一个新的 Read View，而 RR 只会在事务开始后的第一次 select 前生成 Read View，所以 RR 能实现可重复读。

读未提交会出现脏读，读已提交会出现不可重复读。InnoDB 默认的可重复读可以避免前两者，但幻读还要结合 MVCC 和临键锁来理解。

  1. 脏读(读未提交) => 读已提交

脏读

图源：JavaGuide

  1. 不可重复读(一个事务中读一个数据多次，结果不一样) => 可重复读

不可重复读

图源：JavaGuide

  1. 幻读

例子：SQL 脚本 1 第一次查询工资为 500 的记录时只有一条，SQL 脚本 2 插入了一条工资为 500 的记录并提交；SQL 脚本 1 在同一个事务中再次使用当前读查询，发现出现了两条工资为 500 的记录，这就是幻读。

幻读

图源：JavaGuide

注：图中的 select ... for update 会把查到的记录按当前读的方式加锁，用于后续更新。

解决思路其实就是：一个事务在操作某张表数据的时候，另外一个事务不要再往这个范围里新增或删除数据。

  1. 将事务隔离级别调整为 SERIALIZABLE ；
  2. 在可重复读的事务级别下，给事务操作的这张表添加表锁；
  3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock(Record Lock+Gap Lock)；

多版本并发控制(MVCC)

MVCC(Multi-Version Concurrency Control)即多版本并发控制，通过维护在每个数据行上的多个版本来实现；这些版本会定期被回收

  1. 修改：即写操作，会生成一个新的数据版本，带有当前事务的版本号，从而使得其他事务能够读取相应版本的数据
  2. 读取：使用快照读取，选择符合其事务开始时间的数据版本进行读取
  3. 事务提交后，其版本会成为数据库的最新版本，并对其他事务可见

快照读和当前读

MySQL 的 RR 隔离级别之所以能在很大程度上处理并发读问题，关键就在于要区分快照读和当前读。

  1. 快照读（一致性非锁定读）：读取历史快照的数据，属于 MVCC 的范畴。

  2. 锁定读（当前读）：执行 select ... lock in share mode、select ... for update、insert、update、delete 这些语句时，就不能再用快照了，而是会读取最新版本，并且对读到的记录加锁。

InnoDB对MVCC的实现

前面的MVCC只是一种理论，具体的实现方式要看不同的引擎，比如说快照读，不可能真的存一个快照在本地，太浪费空间了。

当一个事务读取某一行时，会结合这个事务的 Read View 和该行的隐藏字段 DB_TRX_ID（最后一次插入或更新该行的事务 id）来判断最新版本是否可见；如果不可见，就顺着另一个隐藏字段 DB_ROLL_PTR 回滚到上一个版本，直到找到可见版本，或者回滚指针不存在为止。

数据行隐藏字段

  1. DB_TRX_ID(重要)：表示最后一次插入或更新该行的事务 id，用来判断该数据行对某个快照（Read View）的可见性。

  2. DB_ROLL_PTR(重要)：回滚指针，指向该行对应的 undo log，用来回滚到上一个版本。

  3. DB_ROW_ID：如果没有设置主键且该表也没有唯一非空索引，InnoDB 会用这个字段来生成聚簇索引。

undo-log 除了用于事务回滚时恢复数据，还用在MVCC中：当读取某个行记录，发现该记录的最新版本对当前事务并不可见，则需要通过 undo log，在内存中算出之前的版本数据，从而实现非锁定读。这里可以先按常见场景粗分为三类：

  1. insert undo log：由 insert 操作产生。它的作用是在事务回滚时把新插入的记录删除掉。因为新插入的记录在事务提交前本来就不会对其他事务可见，所以这类 undo log 一般不需要长期为 MVCC 保留，事务提交后通常可以较早清理。顺着这个思路，如果某数据行对当前事务不可见，且它的 DB_ROLL_PTR 为空，往往说明这是一条由 insert 产生、但当前还不可见的新版本记录。

  2. update undo log：由 update 操作产生，核心是保存被修改前的旧值，方便事务回滚，也方便 MVCC 沿着版本链找到更早的可见版本。它不能在事务提交时立刻删除，因为可能还有快照读在使用这些旧版本，所以通常要等不再被任何 Read View 需要后，才能由 purge 线程最终清理。不同事务或者相同事务对同一行的连续修改，会让这条记录的多个 undo log 串成版本链，链首是最新版本，链尾是最早的旧版本。

  3. delete undo log：可以把它理解成 delete 场景下的一类 update undo log。因为 InnoDB 删除一行时，通常不是立刻把记录物理移除，而是先做“删除标记”，并把相关信息写入 undo log。这样做有两个好处：一是如果事务回滚，可以根据这条 undo log 撤销删除；二是如果有更早创建的 Read View 还需要看到这行数据，也仍然可以沿着版本链读到它。等到事务已经提交、并且确认没有任何快照还需要这条旧记录后，purge 线程才会把它真正物理删除，并清理对应的 undo log。

数据行对Read View的可见性

Read View创建时会有：

  1. 当前活跃事务列表 m_ids：不一定连续，因为后创建的事务可能先结束；在这个列表里的事务，在 Read View 创建时都还没提交。
  2. 当前最大的和最小的事务id，即m_up_limit_id 和 m_low_limit_id：它们和 m_ids 一起，用来划分哪些事务版本肯定可见，哪些肯定不可见。

判断某行数据版本trx_id是否对当前的Read View可见，依次校验下面规则，源码在此：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
bool changes_visible(trx_id_t id, ...) const {
    // 1. 如果记录的事务ID早于 Read View 的可见边界，可见
    if (id < m_up_limit_id) { 
        return(true); 
    }
    // 2. 如果记录的事务ID等于当前事务自己，可见
    if (id == m_creator_trx_id) {
        return(true);
    }
    // 3. 如果记录的事务ID落在另一侧边界之外，说明是 Read View 之后产生的，不可见
    if (id >= m_low_limit_id) { 
        return(false); 
    }
    // 4. 如果在活跃事务列表中（m_ids），说明ReadView创建时它还没提交，不可见
    if (m_ids.binary_search(id)) {
        return(false);
    }
    // 5. 否则可见
    return(true);
}

MySQL锁

表级锁和行级锁一般只在当前读和增删改的情况下出现，普通快照读通常不加锁。常见情况可以先粗记成下面这样：

  • 行级排他锁：INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE
  • 行级共享锁：SELECT ... LOCK IN SHARE MODE / SELECT ... FOR SHARE
  • 表级排他锁：ALTER TABLE；如果 SELECT ... FOR UPDATE 没有命中索引，也可能退化成很重的锁
  • 表级共享锁：LOCK TABLES

表级锁和行级锁

MyISAM（读音 Mai-ai-sam）只支持表级锁，InnoDB 还支持行级锁。

行级锁本质上是加在索引记录上的；如果索引失效导致全表扫描，锁的范围就会变得很大。

针对唯一索引/非唯一索引的等值查询/范围查询，加锁规则可能都不一样，注意区分以下三种可能加的锁：

  1. 记录锁（Record Lock）：锁定索引中的单一行记录（无论主键还是其他索引），阻止其他事务修改或删除。

    一般出现在精确匹配某条记录时；锁的类型可以是共享锁，也可以是排他锁。

  2. 间隙锁（Gap Lock）：锁定索引记录之间的“间隙”，防止新数据插入导致幻读，它不会锁定某一条已有数据。

    例如日志表的时间索引字段 log_time，执行 SELECT * FROM logs WHERE log_time BETWEEN '2025-03-01' AND '2025-03-02' FOR UPDATE，就会锁定这个时间范围内的间隙，防止新日志插入。

  3. 临键锁（Next-Key Lock）：锁定一个范围，并且包含记录本身，可以理解成记录锁和间隙锁的叠加。

共享锁和排他锁

由于 MVCC，一般的 select 是快照读，不会加锁；如果是当前读，则会加共享锁或排他锁；如果是修改操作，则通常拿的是排他锁。

  1. 共享锁又称读锁，事务在读取记录时获取共享锁，允许多个事务同时获取（锁兼容）。
  2. 排他锁又称写锁/独占锁，事务在修改记录时获取排他锁，不允许多个事务同时获取。如果一条记录已经被加了排他锁，那其他事务不能再对它加任何类型的锁（锁不兼容）。

意向锁

意向锁本质上是协调行级锁和表级锁的一种机制：当事务准备给某些行加锁时，会先在表级别拿一个意向锁（IS 或 IX），这样别的事务申请表锁时就能快速判断是否冲突。

  1. 意向共享锁(IS)和意向排他锁(IX)都是兼容的，即如果不涉及表级锁，IS和IX都发挥不了作用；

  2. 表级S锁和IX锁互斥，即如果要对表格整体读取，则不能有行正在修改；

  3. 表级X锁和IS, IX都互斥，即如果要对表格整体改动，行级读锁和写锁都不能有。

悲观锁和乐观锁

MySQL的锁都是悲观锁，乐观锁需要自己添加版本号或时间戳来自定义逻辑。
一般情况下，就是先获取对象实体（包含版本号），更新时把“版本号一致”作为条件；如果更新成功，再把版本号加 1。

1
2
UPDATE products SET stock=stock-1, update_time=NOW() 
WHERE id=1 AND update_time=旧时间戳;

死锁

通过 SHOW ENGINE INNODB STATUS 可以查看最近一次死锁的详细信息。死锁指的是：多个事务都已经持有一部分锁，同时又在等待其他事务手里的锁，最后谁都无法继续推进。InnoDB 检测到死锁后，会主动回滚其中一个事务，让另一个事务继续执行。

从条件上看，死锁通常需要同时满足下面四点：

  1. 互斥条件：资源一次只能被一个事务占用。例如某一行已经被事务 A 加了排他锁，事务 B 就不能再修改这行。
  2. 请求并保持条件：事务已经持有一部分锁，同时还在继续申请新的锁；在申请成功之前，已持有的锁又不会释放。
  3. 不可剥夺条件：事务已经拿到的锁，不能被别的事务强行抢走，只能等它自己提交或回滚后释放。
  4. 循环等待条件：事务之间形成等待环。比如事务 A 等事务 B 的锁，事务 B 又在等事务 A 的锁。

最常见的死锁场景，其实就是“加锁顺序不一致”。例如有两条账户记录 id=1 和 id=2：

  1. 事务 A 先更新 id=1，拿到 id=1 的排他锁。
  2. 事务 B 先更新 id=2，拿到 id=2 的排他锁。
  3. 事务 A 再去更新 id=2，发现这把锁被事务 B 占着，于是等待。
  4. 事务 B 再去更新 id=1，发现这把锁被事务 A 占着，也开始等待。

这时候就形成了一个最典型的循环等待，死锁出现。

所以可以把死锁理解成一句话：不是“锁多”就一定会死锁，而是“多个事务以不同顺序去拿同一批资源”时，才容易形成死锁。

实践里要减少死锁，常见做法有四个：

  1. 尽量让事务按相同顺序访问和加锁。
  2. 缩短事务时间，不要持有锁太久。
  3. 给查询条件建立合适索引，避免锁到过多记录或范围。
  4. 业务代码里对死锁报错做好重试，因为死锁在高并发下通常只能“降低概率”，很难彻底消灭。

数据表索引

数据结构

哈希表

  1. （怎么建和怎么发挥作用）key 是建立索引的字段值，value 是该行所在的地址（或 index）。

  2. （哈希冲突）拉链法和红黑树 or B+Tree。

  3. （缺点）无序，所以不支持顺序和范围查询；例如找 id 小于 500，需要对 1 和 499 都单独定位。

红黑树

待研究

B树

多叉平衡搜索树，参考https://www.bilibili.com/video/BV1tJ4m1w7yR，下图亦是出自于此

比红黑树好——因为大量数据需要对磁盘进行IO，内存放不下

而B+树每个节点存储的数据多，硬盘读取1次节点耗时是类似的，无论节点的元素有多少

访问节点的操作在硬盘上进行，访问节点内的元素是在内存上进行的

  1. 整体概述：m 阶 B 树的结点，最多有 m 个分支和 m-1 个元素，最少有 ceil(m/2) 个分支和 ceil(m/2)-1 个元素。

image-20241227164429874

  1. 插入：只可能上溢出，溢出后需要分裂和调整。

image-20241227164935458

  1. 删除：只可能下溢出；如果删的是非叶节点，一般会先转成删除它的前驱或后继，再转成删除叶节点。

B+树

  1. 分支数目 = 节点中的元素数；每个元素最终都会出现在叶节点，中间节点里的元素更多是为了导航。

  2. 叶节点存储数据所在地址，可以针对一张表中的多个字段建立 B+ 树索引。

  3. 它是一个多级索引结构，可以理解成树中的第 i 层在给第 i+1 层做索引。

image-20241227171213564

索引介绍

  1. 主键索引

    每个表都会有主键并建立索引。InnoDB 会先检查有没有唯一且非空的字段可以拿来当主键；如果没有，就会自动生成一个隐藏主键。

    InnoDB 默认用 B+ 树存储主键索引，叶节点上就是对应的行数据。

  2. 二级索引

    二级索引相对于主键索引来说，叶节点存储的是索引列的值和对应的主键值；查到主键后，往往还要再回主键索引树里找一次完整行数据。

  3. 聚簇索引和非聚簇索引

    InnoDB 使用聚簇索引，MyISAM 使用非聚簇索引。前者使得表中数据和主键索引的顺序放在一起，所以基于主键的查询效率很高；后者通常需要多一次定位数据的过程。

    数据行和叶子节点可以顺序访问，这也是 B+ 树很适合范围查询的重要原因。每张表只能有一个聚簇索引，因为数据行本身只能按一种索引顺序组织。

  4. 覆盖索引：索引里已经包含了查询所需要的全部字段。一般来说，非主键索引查询时会先拿到主键值，再去找完整行数据，这叫回表；如果要查的字段刚好都在索引里，就可以直接返回结果，不需要回表。

  5. 联合索引：使用表中的多个字段一起建索引，核心还是最左匹配原则。一般会把区分度高的字段放在更靠左的位置，以便更早过滤数据。索引失效问题，对于联合索引，只要不包含最左列，就不会走索引

    假设有一个联合索引(column1, column2, column3)，其从左到右的所有前缀为(column1)、(column1, column2)、(column1, column2, column3)。只有命中这些前缀的查询，才更容易用到这个联合索引。

  6. 前缀索引：只针对字符串类型，对前几个字符创建索引，这样索引会更小。