对应 小林 Coding 中：

  • MySQL Server 层架构

  • MySQL 三大日志：undo log、redo log、binlog

  • MySQL 锁机制

MySQL 对数据最基本的功能就是增删改查，即 SELECT、UPDATE、INSERT、DELETE。

在功能上可以分为两类：

  • 对数据进行读操作的 SELECT

  • 对数据需要进行读写操作的 UPDATE、INSERT、DELETE

关于读操作，需要建立连接、解析 SQL、选择索引、针对存储引擎执行查询；

关于写操作，由于 MySQL 是多线程的，需要三种日志分别满足事务的原子性（事务回滚与MVCC）、持久性（掉电后故障恢复）、以及备份与主从复制等。

以上所有读写操作的原理分为 Server 层和 InnoDB 存储引擎两方面来实现。

  • Server 层的功能范围：

    • 主要以 数据库实例为单位，负责全局配置、跨数据库操作、SQL 解析和优化等。

    • 这些功能可能涉及多个数据库和多个表。

  • 执行引擎的功能范围：

    • 主要以 表为单位，负责具体表的数据存储、索引、锁机制、事务管理等。

    • 这些功能通常作用于单个表，但也可以扩展到多个表（例如跨表的事务）。

1. Server 层架构与机制

Server 层架构如下：

1.1 连接器：维护连接与权限

客户端需要和 MySQL 服务进行 TCP 三次握手，若 TCP 连接建立，则验证用户名密码，若正确，则连接器获取用户权限并保存。

以下是与 MySQL 连接有关的几个问题：

1.1.1 连接后权限不会动态影响

如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。

所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

1.1.2 默认使用长连接

MySQL 默认使用的是长连接，所以我们可以用命令查看它被多少个客户端连接了：

MySQL 的连接数是有限制的，由 max_connections 参数控制。若超过，则系统会拒绝接下来的连接请求。

上图中有两个 root 用户连接 MySQL 服务，其中 id 为 6 用户的 Command 列为 Sleep，即该用户连接完 MySQL 服务之后没执行过任何命令，即一个空闲的连接，空闲市场 736 秒。

空闲连接是有超时机制的，由 wait_timeout 参数控制，默认 8 小时。如果超时，客户端在发起下一个请求时会收到报错。

使用长连接有优缺点：

  • 优点：减少建立连接和断开连接的过程

  • 缺点：长连接需要用内存管理内存对象，占用内存增多。解决方案：

    • 定期断开长连接

    • 客户端会主动重置连接

1.1.3 不要与连接池混淆

  • MySQL 连接器：是 MySQL Server 的一部分，用于建立和管理客户端与MySQL服务器之间连接的组件。

  • MySQL 连接池：是应用程序客户端管理数据库连接的技术，旨在复用连接以减少开销。连接池使用连接器建立连接，并在应用程序请求时提供这些连接，使用完毕后回收连接以供复用。常见 JDBC连接池：如HikariCP、Apache DBCP，使用JDBC连接器创建和管理连接。

1.2 Server 层缓存

针对 SELECT 语句，会去 Server 层缓存查询是否命中，若命中直接返回 value 给客户端，否则继续查询。

由于命中率较低，MySQL 8.0 已移除。

注意它是 Server 层的缓存，不是存储引擎 Innodb 的 buffer pool 。

1.3 解析器：解析 SQL

这一层会对 SQL 语句这个字符串做词法分析和语法分析，如果解析失败了会给用户返回：

You have an error ...

注意：表不存在或者字段不存在，并不是在解析器里做的。

如果成功，接下来就做执行 SQL 的操作了。

1.4 预处理器与优化器：处理查询方案

预处理器做了两件事：

  • 检查 SQL 查询语句中的表或者字段是否存在；（MySQL 5.7 是在 预处理之前做的）

  • 将 select * 中的 * 符号，扩展为表上的所有列；

而优化器决定了 SQL 查询语句的执行方案，即决定使用一个查询效率最优的索引。

我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引。（如 PRIMARY 表示使用主键索引，null 表示全表扫描，或者具体创建的索引名称）

与此同时，优化器也判定了索引下推的情况。

1.5 执行器：与存储引擎交互

执行器就干两件事：

  • 通过 read_first_record 和 read_record 函数，从执行引擎那里要一条记录；

  • 判断这条记录是否符合查询条件，如果符合则将这条记录返回给客户端。（客户端收到所有记录后才会展示）

执行器的执行流程如下：

  1. 调用 read_first_record 函数，然后判断条件；

  2. 进入一个 while 循环，不断调用 read_record 函数，直到返回 -1，停止查询。

而反过来说，一个字段（条件）是在哪里进行判断的呢？

  • 在执行引擎：执行引擎走了这个字段的索引，如 InnoDB 根据这个索引的 B+ 树来找到记录（或主键）；除此之外还有另一种索引下推的特殊情况。

  • 在Server层的执行器：该字段不是索引字段，没有被执行引擎利用，是一种“过滤”条件。

下面以 InnoDB 执行引擎为例，用三种方式来分别讲解执行器的工作过程。

1.5.1 主键索引查询

  • SQL 语句： SELECT * FROM product WHERE id = 1;

  • 访问类型：const

  • read_first_record 函数指针：指向为 InnoDB 引擎索引查询的接口，让存储引擎定位符合条件的第一条记录。

  • read_record 函数指针：指向一个永远返回 -1 的函数。

1.5.2 全表扫描

  • SQL 语句： select * from product where name = 'iphone';

  • 访问类型：ALL

  • read_first_record 函数指针：指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；

  • read_record 函数指针：指向的还是 InnoDB 引擎全扫描的接口，继续读刚才那条记录的下一条记录，直到存储引擎把表中的所有记录读完。

1.5.3 索引下推

索引下推是由 MySQL Server 层的优化器实现的，不过并非所有执行引擎都支持。但它并不是 InnoDB 存储引擎的内部机制。

索引下推通常存在于联合索引里，能够减少非聚簇索引（二级索引）在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

假如有一张表 t_user 主键是 id ，有联合索引 (age, reward) 。

有如下 SQL 语句：

select * from t_user where age > 20 and reward = 100000;

由于联合索引遇到范围查询会停止匹配，即 age 字段可以用到索引，但 reward 无法利用到索引。

如果没有索引下推，age 字段在存储引擎层进行匹配，而 reward 字段在 Server 层进行匹配。

age 字段在存储引擎层匹配时，由于 age 是非聚簇索引，需要根据查出来的 id 回表查询整条记录再返回给 Sever 层，供 Server 层查询；Server 层再根据其他条件筛选。

而索引下推则是在 age 字段在存储引擎层进行匹配后，先判断该索引中包含的 reward 列条件是否成立。若条件不成立则直接跳过这个 age 索引，若成立再回表查询，将完整记录返回给 Server 层。

1.6 二进制日志 binlog

“三大日志” 中的 binlog 是 MySQL 完成一条更新操作后，在 Server 层生成的一个文件，用于数据备份和主从复制。

MySQL 在完成一条更新操作后，Server 层会生成一条 binlog （写到 binlog cache），事务提交后会把事务执行过程中产生的所有 binlog 统一写入 binlog 文件。

它记录了数据库中所有修改数据的语句（如 INSERT、UPDATE、DELETE）以及数据定义语言（DDL）语句（如 CREATE TABLE、ALTER TABLE 等），但不包括 SELECT 和 SHOW 这类语句。

1.6.1 Binlog 的格式

MySQL 提供了三种 Binlog 格式，分别是 STATEMENT、ROW 和 MIXED。不同的格式对性能和兼容性有不同的影响：

  • STATEMENT 格式：

    • 记录的是 SQL 语句的文本形式。

    • 优点是日志量小，因为只记录了语句本身。

    • 缺点是某些语句（如包含非确定性函数的语句）可能无法在从服务器上正确执行。

  • ROW 格式：

    • 记录的是每一行数据的变化，而不是 SQL 语句。

    • 优点是精确记录数据的变化，避免了非确定性语句的问题。

    • 缺点是日志量较大，尤其是当修改大量数据时。

  • MIXED 格式：

    • 是 STATEMENT 和 ROW 的混合模式。

    • MySQL 会根据语句的特性自动选择使用 STATEMENT 格式还是 ROW 格式。

    • 通常在不确定语句是否会产生非确定性结果时，MySQL 会自动切换到 ROW 格式。

1.6.2 主从复制的实现

主库会异步地把 binlog 的数据从主库传输到从库上。

在主从复制架构中，Binlog 是实现数据同步的关键：

  • 主服务器：开启 Binlog，记录所有数据变更操作。

  • 从服务器：通过 CHANGE MASTER TO 命令连接到主服务器，读取 Binlog 文件。

  • I/O 线程：从服务器的 I/O 线程从主服务器拉取 Binlog 并存储到本地的 Relay Log。

  • SQL 线程：从服务器的 SQL 线程解析 Relay Log 并应用到从服务器的数据库中。

1.6.3 Binlog 刷盘机制

MySQL 是多线程的，每个线程只能执行同时有一个事务在执行。而每个线程都有一个 binlog cache，用于缓冲 binlog 每一条记录。

刷盘机制如下图：

  • 写入到内核的 write 执行时机：线程事务提交时，执行器把 binlog cahce 里的完整事务执行 write 操作，并清空 binlog cache。

  • I/O到磁盘的 fsync 执行时机：

    • sync_binlog = 0，由操作系统决定刷盘时机；（默认）

    • sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；

    • sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

1.7 Server 层锁机制

Server层锁主要用于控制对数据库对象（如表、视图、存储过程等）的访问，这些锁通常用于全局性或元数据级别的操作。

Server层锁是与线程相关的，大多不是和事务相关。

1.7.1 全局锁

全局锁的作用范围是整个数据库实例，会阻止所有对数据库的写操作（包括 DML 和 DDL），但允许读操作。全局锁的作用范围是整个数据库实例，而不是某个特定的表或存储引擎。因此，它与存储引擎无关，而是由 MySQL Server 统一管理。

MySQL 提供了两种常见的全局锁机制：

  1. FLUSH TABLES WITH READ LOCK（FTWRL）：

    • 这是一个典型的全局锁命令，用于锁定整个数据库实例，防止任何写操作。

    • 它会将所有表设置为只读状态，直到锁被释放。这通常用于以下场景：

      • 备份操作：在进行全量备份时，需要确保数据一致性，FTWRL 可以防止备份期间的数据变更。

      • 维护任务：在执行某些需要独占访问的维护操作时，FTWRL 可以确保没有其他写操作干扰。

  2. mysqladmin flush-hosts 或 mysqladmin flush-tables：

    • 这些命令也会涉及到全局锁，但作用范围和持续时间可能更短，主要用于清理缓存或刷新表状态。

  • 全局锁可以通过显式命令释放（如 UNLOCK TABLES），也可以通过客户端断开连接自动释放。

1.7.2 表锁

表锁是 MySQL 中最粗粒度的锁机制，用于锁定整个表，防止其他事务或会话对该表进行并发访问。

需要显式地使用 LOCK TABLES 和 UNLOCK TABLES 语句。

表锁的类型

表锁分为两种主要类型：

  1. 共享锁（Shared Lock，S锁）：

    • 允许多个事务同时读取表，但不允许写操作。

    • 适用于只读操作，如 SELECT 查询。

  2. 排他锁（Exclusive Lock，X锁）：

    • 用于写操作，如 INSERT、UPDATE、DELETE。

    • 排他锁会阻止其他事务对该表的任何读写操作，确保独占访问。

表锁的工作原理

  • 表锁由 MySQL Server 层管理，与存储引擎无关。

  • 当一个线程（事务）获取了表锁后，其他线程（事务）必须等待，直到表锁被释放。

  • 表锁的粒度较大，因此并发性能较差，但在某些场景下（如批量操作）可以减少锁的开销。

表锁的使用场景

  1. 批量操作：

    LOCK TABLES my_table WRITE;
-- 执行批量插入或更新操作
INSERT INTO my_table VALUES (...);
UPDATE my_table SET column = value WHERE condition;
UNLOCK TABLES;

  2. 显式锁定：

    LOCK TABLES my_table READ;
-- 执行只读操作
SELECT * FROM my_table;
UNLOCK TABLES;

表锁的缺点

  • 并发性能差：表锁会阻塞整个表的访问，导致其他事务等待，不适合高并发场景。

  • 锁粒度大：锁定整个表可能导致不必要的资源浪费。

1.7.3 元数据锁

元数据锁是 MySQL Server 层的一种轻量级锁机制，用于保护表或索引的元数据。

通常由 MySQL 自动管理，无需显式申请或释放。

元数据是指表的结构信息（如列定义、索引定义等）。MDL 的主要目的是确保在查询或修改数据时，表的结构不会被其他事务修改。

  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；

  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

不过 MDL 锁有个坑，如果某线程申请不到 MDL 写锁，那么后续的申请读锁的查询操作也会被阻塞。

2. InnoDB 引擎

2.1 数据存储

详见索引章节，学完了，略。（其实还有一篇文章没写完）

2.2 缓冲池 Buffer Pool

2.2.1 Buffer & Cache

在计算机科学中，buffer（缓冲区）和cache（缓存）都是用于临时存储数据的机制，但它们在设计目标、使用场景和行为上有显著的区别。以下是它们的主要不同点：

  • Buffer（缓冲区）

    • 主要设计目标是协调数据传输的速度差异，或者用于暂存数据以便后续处理。它通常用于解决生产者和消费者之间速度不匹配的问题，或者用于批量处理数据以提高效率。

    • 读操作：先读 Buffer，若 Buffer 中不存在再读取原始数据。

    • 写操作：先写 Buffer，再通过其他机制将 Buffer 内容同步到原始数据中。不会出现数据一致性问题。

  • Cache（缓存）

    • 主要设计目标是提高数据访问速度，通过存储最近或频繁访问的数据，减少对低速存储设备（如磁盘）的访问次数。缓存利用了数据访问的局部性原理（空间局部性和时间局部性），即最近访问过的数据或相邻的数据很可能会被再次访问。

    • 读操作：先读 Cache，若 Cahce 中不存在再读取原始数据。

    • 写操作：写原始数据，通过其他机制将数据同步到 Cache 中。可能会出现数据一致性问题。

Buffer pool 基本机制与我们所说的“缓存”有所不同：

  • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。

  • 当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

2.2.2 Buffer Pool 缓存的内容

Buffer Pool 缓存的内容是以页为单位的。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图：

注意 Buffer Pool 查询一条记录时，实际上也是查询该条记录所在的页面。

缓存页的状态有三种：

  • Free Page（空闲页），表示此页未被使用，位于 Free 链表；

  • Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。

  • Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

2.2.3 空闲页与脏页的管理

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

如果我们想找到第一个空闲页 or 第一个脏页，那么如果遍历这一块内存空间，复杂度为O(n)。

为此设计两个双向链表来使得这个查找工作变成 O(1)：

  • Free 链表：空闲页链表，每个节点就是空闲缓存页的控制块。

  • Flush 链表：脏页链表，每个节点就是脏页的控制块。

2.2.4 提高命中率：LRU 链表

LRU List 管理 脏页与干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；

InnoDB 对这个 LRU 列表做了两点优化：

  • 将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。（MySQL 预读了大量页，但是不一定会被用到）

  • 当「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。（类似 LFU）

2.3 事务持久性 Redo Log

2.3.1 WAL 技术与 Redo Log

Buffer Pool 提高了读写效率，但是它基于内存，如果断电重启，脏页数据会丢失。WAL 技术用于解决这个问题。

在更新数据页 和 后台线程定期将脏页刷新到磁盘 之间，多了一步：使用 Redo Log 记录某个数据页做了什么修改，并在事务提交时将 Redo Log 持久化到磁盘。

Redo Log 是物理日志，记录某个数据页做了什么修改，比如对 XXX 表空间中 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新，每当执行一个事务就会产生一条或多条物理日志。

Redo Log 保证了事务四大特性的持久性。

Redo Log 是顺序写，而 Buffer Pool 写入磁盘是随机写。

2.3.2 Redo Log Buffer

Redo Log 也有一个在内存中的 Buffer，默认大小 16 MB。

持久化时机：

  • MySQL 正常关闭时。

  • 写入量大于 Buffer 内存空间一半时。

  • InnoDB 后台线程每隔 1 秒

  • 每次事务提交时，由参数决定：

    • 不刷盘

    • 调用 write()

    • 调用 fsync()

2.3.3 Redo Log 循环写

红色部分：即将记录新的更新操作。如果由新的更新操作，write pos 前进。

蓝色部分：待落盘的脏页记录。如果脏页落盘，check point 前进。

如果 write pos 追上了 check point，即 Redo Log 满了，MySQL 会阻塞更新操作，直到右脏页落盘，checkpoint 前进。

2.3.4 与 Binlog 的两阶段提交

MySQL InnoDB 在事务结束后，MySQL Server 还会将记录写入 bin log 用于主从复制。这里就涉及到了数据一致性的问题。

两阶段提交是以 binlog 写成功（里面右当前内部 XA 事务的 XID）为标识。

2.4 事务原子性 Undo Log

Undo Log 两个作用：

  • 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。

  • 实现 MVCC 关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读的时候，会根据事务的 Read View 信息，顺 undo log 的版本链找到满足可见性的记录。

2.5 事务隔离性 MVCC & Read View

2.5.1 事务隔离级别与问题

问题：

  • 脏读：一个事务读到了另一个未提交事务修改过的数据。

  • 不可重复读：一个事务多次读取同一个数据，前后两次读到的数据不同。

  • 幻读：一个事务内多次查询记录数量，前后两次查询到记录数量不一样。

隔离级别：

  • 读未提交：一个事务没提交时，变更就可以被其他事务看到。

  • 读提交：一个事务提交后，变更才可以被其他事务看到。

  • 可重复读：事务启动到结束，看到的数据是一致的。

  • 串行化：对记录加上读写锁，如果读写冲突直接等待。

读提交和可重复读这两种隔离级别通过 MVCC 实现。

生成 Read View 的时机：

  • 读提交：每个语句执行前

  • 可重复读：启动事务时

2.5.2 Read View 数据结构

每个事务在同一时刻都会只有一个 Read View：

  • 在读提交隔离级别下，每个事务中每执行一条快照读语句对应一个 Read View；

  • 在可重复读隔离级别下，每个事务对应一个 Read View。

Read View 的内容在创建时就会被确定，有四个字段：

  • m_ids：创建 Read View 时，当前数据库中活跃未提交的事务 id 列表。

  • min_trx_id：m_ids 的最小值

  • max_trx_id：创建 Read View 时，当前数据库应该给下一个事务的 id 值

  • creator_trx_id：创建该 Read View 的事务 id

对于被读取的数据，InnoDB 每个聚簇索引还包含两个隐藏列：

  • trx_id：改动这个聚簇索引记录的事务 id

  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中。该隐藏列是个指针，指向每一个旧版本的记录。

“已提交事务可见，未提交事务不可见”：

2.5.3 可重复读与读提交的 MVCC 机制

读提交：

可重复读：

2.6 InnoDB 锁机制

InnoDB 锁机制大多数都针对于记录，并且“锁”住的是事务的可访问性。

所以 InnoDB 所有行级锁都是在开启事务的条件下讨论的，事务提交时，该事务加的行锁就会被释放。

（MySQL 每个线程只能同时有一个事务）

2.6.1 行锁 Record Lock

普通的快照读 SELECT 语句不会加锁。如果在读内容时需要加锁，有两种方式：

//对读取的记录加共享锁 S锁
select ... lock in share mode; 

//对读取的记录加独占锁 X锁
select ... for update;

在对一个记录使用 UPDATE、DELETE 操作时，也会对这个记录加 X 锁。

比如一个事务执行以下语句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。当事务执行 commit 后，事务过程中生成的锁都会被释放。

2.6.2 间隙锁和 Next-Key 锁

只存在于可重复读隔离级别，是专门为解决幻读问题而提供的解决方案。

间隙锁：

  • 表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了

  • 间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系

next-key 锁：

  • 间隙锁与记录所的组合，锁定一个范围不被插入，并锁定记录本身不被修改或删除。

  • 表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

  • 如果一个事务获取 X 型 next-key lock，则另外一个事务在获取相同范围的 X 型 next-key lock 会被阻塞。

插入意向锁：

  • 如果一个事务在插入一条新记录时，发现插入位置被其他事务加了间隙锁，则会被阻塞，同时生成一个插入意向锁去所住这条记录，但是表明为等待状态。在间隙锁事务提交后，该插入意向锁状态为正常状态。

  • 注：只有该插入意向锁为正常状态时，我们才会说该事务获取到了这个插入意向锁。

那么锁的范围又是怎么决定的？

Next-Key 锁的范围由 索引扫描范围 决定：

  • Next-Key 锁 = 间隙锁 + 记录锁，锁定左开右闭区间（如 (3,5]）。

  • 索引扫描到哪些区间，就锁定哪些区间：

    • 若扫描 id BETWEEN 4 AND 6，且索引中有 id=3,5,7：

      • 锁定 (3,5]（覆盖 id=5）和 (5,7)（间隙锁）。

    • 若扫描 id > 3，锁定 (3,5]、(5,7] 和 (7, +∞)。

示例：

  • 执行 UPDATE t SET name = 'Bob' WHERE id > 3 AND id < 6：

    • 扫描到 id=5，锁定 (3,5]（Next-Key 锁）和 (5,7)（间隙锁）。

要素

对 Next-Key 锁的影响

WHERE 条件

决定索引扫描的起始和终止范围，直接影响锁定的区间。

索引类型

唯一索引可能减少锁范围，非唯一索引可能扩大锁范围。

数据分布

现有数据的间隙决定了锁的边界。

锁模式（X/S）

X 型锁（排他锁）会阻塞其他事务的写操作，S 型锁（共享锁）允许读但阻塞写。

隔离级别

可重复读级别强制使用 Next-Key 锁解决幻读；读已提交级别可能仅加记录锁，不锁间隙。

2.6.3 意向锁

意向锁是属于表级别的，目的是为了在加 Server 层的共享表锁 / 独占表锁时，快速判断表里是否有记录被加锁。

  • 如果 InnoDB 对表里某些记录要加共享锁，那么要先对表加上意向共享表锁。

  • 如果 InnoDB 对表里某些记录要加独占锁，那么要先对表加上意向独占表锁。

3. 其他对比问题

3.1 MyISAM 存储引擎

  • 不支持外键

  • MyISAM 的 B+ 树主键索引和二级索引的叶子节点都是数据文件的地址指针，即都是非聚簇索引。

  • 不支持事务，强调性能

  • 没有行级锁，数据更新时锁住的是整个表

  • 数据库在读写过程中相互阻塞

  • 读取性能好，写入性能差

3.2 MySQL 8.0

  • 权限认证支持了 Role （RBAC）

  • 支持了降序索引，更好支持文档型数据库与JSON

  • 废除了 Buffer Pool mutx 和 行缓存

  • 成本模型：InnoDB缓冲区可以估算缓存区中的有多少表和索引，这可以让优化器选择访问方式时知道数据是否可以存储在内存中还是必须存储到磁盘上。

3.3 MySQL 与其他关系型数据库

3.3.1 PostgreSQL

  • 以高级特性和数据完整性而著称。支持复杂的数据类型如数组、范围类型、几何类型、网络地址类型等；

  • 支持自定义函数、自定义数据类型和自定义操作符等高级特性；

  • 除了支持常见索引类型外，还提供了很多高级索引。

  • 只有一个存储引擎，通过 MVCC 机制处理复杂事务；

  • 提供流复制、逻辑复制和多主复制等高级特性。

  • 支持行级安全性，比MySQL权限颗粒度更细。

参考

小林 Coding