架构

MySQL是一种开源关系型数据库管理系统。与其他关系型数据库一样，MySQL将数据存储在由行和列组成的表中。用户可以使用结构化查询语言（通常称为SQL）定义、操作、控制和查询数据。它的架构大致如下图所示 Mysql的架构可以分为两层：

Server层：主要负责接收用户请求，对SQL进行处理和优化，再生成执行计划并调用存储引擎接口去执行
- 连接器：负责客户端连接管理和权限管理。由于长连接会导致内存占用，故长时间的未活跃连接会自动断开
  - 查询缓存：用于缓存查询请求。对于同样的查询语句，如果缓存中有结果就可以直接返回
- SQL解析器：对查询语句进行语法解析，根据规则判断是否满足条件，不满足则报错
- 优化器：在语句执行之前，根据索引情况、表结构等信息选择最优执行计划
- 执行器：调用存储引擎接口，真正完成数据的读写操作
存储引擎层：主要负责具体的数据存储和访问

在连接管理方面，还需要注意连接模式带来的影响。例如短连接模式下，每次请求都建立和关闭连接，在高并发场景下容易导致连接数暴涨，甚至超过max_connections限制。而长连接虽然减少了连接开销，但会导致连接对象长期占用内存资源。因此在实践中，通常需要结合连接池或主动释放连接资源（例如通过mysql_reset_connection）来平衡性能和资源使用。

核心概念

库和表

在Mysql中，“库”这个概念其实容易被误解。很多人会下意识认为数据库本身对应某种复杂的数据结构（比如B+树），但实际上，库更多是一个逻辑划分单位，而不是一个真正参与数据组织的结构。

从存储角度来看，库只是文件系统中的一层目录或者命名空间，用来组织表和相关对象。真正与数据结构（例如B+树）直接关联的，其实是“表”，更准确地说，是表对应的存储引擎实现。

以InnoDB为例，它才是负责数据存储和索引组织的核心组件。根据配置不同，InnoDB的表空间管理方式也有所不同：

在共享表空间模式（innodb_file_per_table=OFF）下，所有表的数据都会存储在统一的ibdata文件中，库只是逻辑上的划分
在独立表空间模式（innodb_file_per_table=ON）下，每张表都会对应一个独立的.ibd文件，此时库可以理解为这些文件所在的目录容器

无论哪种模式，库本身都不直接参与索引结构，也不需要手动扩容，表空间文件会根据数据增长自动扩展。

真正与B+树相关的是表中的索引结构。

索引

Mysql中的索引可以分为两类：聚簇索引和非聚簇索引。

聚簇索引

在InnoDB中，每张表本质上都会对应一棵（或多棵）B+树，其中最重要的是聚簇索引。如果表定义了主键，那么这棵B+树就是基于主键构建的；如果没有主键，InnoDB会自动选择一个唯一非空索引，或者生成一个隐藏的rowid来作为聚簇索引。

聚簇索引的特点在于：数据和索引是"聚"在一起的。也就是说，B+树的叶子节点中存储的不只是索引键，而是整行数据。因此，通过主键查询时，可以直接定位到数据所在页，这是最优路径。

非聚簇索引

而对于非主键字段建立的索引，则属于非聚簇索引（也叫二级索引或辅助索引）。每个辅助索引同样是一棵独立的B+树，但它的叶子节点只存储(secondary key, primary key)，并不包含整行数据。因此，当通过辅助索引查询时，往往需要先找到主键，再回到聚簇索引中“回表”获取完整数据，这也是为什么某些查询会涉及多次IO。

在具体实现上，InnoDB的B+树并没有一个固定的“阶数”，它的结构是由页大小动态决定的。InnoDB以“页”作为最小的存储和IO单位，默认大小为16KB。无论是索引还是数据，都是按页组织的，每一个B+树节点本质上就是一个页。这样一来，一棵高度为3的B+树，通常只需要3次磁盘IO就可以定位到一条记录，这也是数据库查询高效的关键原因之一。

不过，这种结构也带来一些副作用。例如InnoDB是按照主键顺序组织数据的，如果主键是随机的（比如UUID），新数据就可能被插入到任意位置，从而频繁触发页分裂，导致额外的随机IO开销。因此在实际设计中，通常建议使用自增主键，以减少这种开销。

另外，为了减少页分裂带来的性能影响，InnoDB在页中会预留一定的空间，用于后续插入。这种设计本质上是一种“空间换时间”的策略，也可以用RUM模型来理解：通过牺牲部分存储空间，换取更稳定的读写性能。

日志

Mysql的可靠性和一致性实现依赖于三种日志：binlog、redolog和undolog。

redolog

在Mysql中，如果每一次数据更新都直接落盘，会带来非常高的IO成本：不仅要写磁盘，还需要先定位数据页，再进行修改。在高并发场景下，这种“随机IO + 查找”的方式是不可接受的。

为了解决这个问题，InnoDB引入了WAL机制，也就是也就是“先写日志，再写数据”。当一条记录需要更新时，并不会立即修改磁盘中的数据页，而是先将修改操作按照顺序写入到redolog中，同时更新内存中的数据页。只要redolog写入成功，这次更新就可以认为已经完成。至于真正的数据页刷盘，则会在之后由后台线程在合适的时机（例如系统空闲时）完成。这种设计的核心价值在于：把随机写变成顺序写，大幅提升磁盘性能。

redolog是一个固定大小的循环日志，它在写满之后会从头开始覆盖。它的运行依赖两个关键指针:

write pos：当前写入位置（不断向前推进）
checkpoint：可以安全覆盖的位置（表示之前的数据已经刷盘）

这两个指针之间的空间，就是当前可用的日志区域。如果write pos追上了checkpoint，说明日志已经写满，这时系统必须暂停新的写入，先把内存中的数据页刷到磁盘，然后再推进checkpoint，释放空间。在数据库异常重启时，InnoDB只需要从checkpoint位置开始，重放后续的redolog，就可以恢复未落盘的数据，从而保证已提交事务不会丢失。

binlog

binlog是Server层的日志，适用于所有底层的存储引擎。它是一种逻辑日志，记录的是执行语句的原始逻辑。换句话说，如果你执行了一条更新语句，例如“将ID=2这一行的某个字段加1”，redolog记录的是“某个数据页的某个位置被改成了什么值”，而binlog记录的是“执行了这样一条SQL”。正因为如此，binlog并不是用来做崩溃恢复的，而是主要用于主从复制、数据备份以及基于时间点恢复。

在存储方式上，binlog采用的是追加写，文件大小不固定。当一个binlog文件写满后，会自动切换到下一个文件，而不会覆盖旧日志，这也使得它天然适合做归档和历史追溯。为了避免日志无限增长，可以通过binlog_expire_logs_seconds和max_binlog_size参数配置自动轮转和清理策略。

binlog支持三种记录格式，这也是实际生产中一个非常重要的选择点：

statement：语句模式。直接记录原始SQL语句。优点是日志体积小、写入快，非常适合批量操作；但缺点也很明显，在从库执行时依赖当前数据状态和执行环境，可能出现主从不一致（例如函数、随机数等非确定性语句）
row：行模式。会记录每一行的变更前/变更后数据。优点是结果绝对一致，不受执行环境影响，支持并行复制；缺点是日志体积大，尤其在批量更新时
mixed：混合模式。自动在statement和row之间切换。安全的语句用statement，有风险的语句用row。理论上兼顾性能和安全，但在实践中，可维护性差、行为不透明、问题排查困难

另外，在某些特殊的情况下，例如“执行更新但值没变”。在statement模式下一定会记录binlog。但row模式可能不会，因为它只有数据真正变化时才记录，除非表中存在自动更新的TIMESTAMP字段。

两阶段提交

想象一下，当更新一条数据时，如果binlog和redolog的写入不是同步的，会发生什么：

先写redolog，后写binlog：redolog写完后宕机，binlog此时还没写。重启后，主库通过redolog恢复了数据，但从库因为没有收到binlog，导致主从数据不一致
先写binlog，后写redolog：binlog写完后宕机。重启后，主库发现redolog没写，认为事务失败，回滚了数据。但从库收到binlog却执行了修改，导致主从数据不一致

在MySQL中，一个事务的提交并不是简单地“写完就结束”，而是通过两阶段提交机制来保证数据一致性。其中，Server层作为协调者，负责在两个日志之间建立严格的顺序关系。以一条更新语句为例，整个执行过程可以这样理解：

当SQL执行时，执行器首先会根据主键定位到对应的数据行。如果该数据页已经在内存中，就直接使用；否则需要先从磁盘加载到内存中
随后，InnoDB会先生成一条undolog，用于记录修改前的数据，以便在事务回滚时恢复
接下来，执行器调用存储引擎接口对数据进行修改。InnoDB会先在内存中更新这条记录，同时把这次修改操作写入redolog buffer，并将这条redolog标记为prepare状态
然后，流程进入Server层。执行器会生成对应的binlog，并将其写入磁盘
当binlog写入成功后，执行器会再次调用存储引擎的提交接口。此时，InnoDB会把之前处于prepare状态的redolog标记为commit状态，整个事务才算真正完成

“双一”配置

在理解了两阶段提交后，一个更关键的问题是：redolog和binlog什么时候真正落盘？

首先，binlog的刷盘策略是由sync_binlog参数控制的，有以下几个选项：

=0：将binlog写到page cache中，刷盘由操作系统决定，通常几秒一次
=1：每次事务提交都执行刷盘操作，确保日志真正落盘。最安全，但IO压力最大
=N：先将N个事务写到page cache中，再统一进行一次刷盘

我们再看看redolog的刷盘策略，它由innodb_flush_log_at_trx_commit参数控制：

=0：每次事务提交只把redolog写到redolog buffer中，由后台线程每隔1s刷盘
=1：每次事务提交都执行刷盘操作
=2：每次事务提交都将redolog写入redolog buffer，然后立即调用write写入操作系统的page cache，但不会执行刷盘。真正的刷盘由后台线程每隔1秒统一完成

由上面的两个参数可知，将它们都设置为1是最安全的配置，但这也会增加IO压力。在使用时，可以根据实际情况进行调整。

无论是redolog还是binlog，在很多情况下都会经过page cache。page cache是操作系统的内存，它不受Mysql内存参数限制，而是受系统总内存限制，并有可能和Mysql的buffer pool竞争资源。但它可以在mysql重启时，保存数据信息，用来恢复数据。除非机器断电宕机了。

那为什么redolog用了buffer缓冲，而binlog没有呢？这是因为redolog是高频写入，每个数据页修改都要记录，所以使用了buffer来实现“持续积累 + 批量刷盘”。而binlog是事务级写入，一次事务一次写，事务结束即释放。

redolog重放机制

在InnoDB中，每一条redolog都对应一个唯一的LSN。它是一个单调递增的值，可以理解为redolog的“时间轴”或“偏移量”。每当有新的redolog写入时，LSN就会按照写入的字节长度递增。例如写入一段长度为length的日志，LSN就增加length。这样一来，整个redolog就形成了一条连续的、有序的日志流。

更重要的是，LSN不仅存在于redolog中，也会写入数据页中。这样在崩溃恢复时，InnoDB可以通过对比数据页上的LSN和日志中的LSN，判断某一条日志是否已经应用过，从而避免重复执行。这也是redolog能够安全“重放”的关键基础。

组提交

理论上，一个事务提交至少需要两次刷盘：一次redolog，一次binlog。如果系统TPS达到每秒1万，那似乎意味着每秒需要2万次磁盘写入。但在实际运行中，磁盘IO并不会这么高，这就是因为MySQL使用了组提交机制。其核心思想是：在并发场景下，如果多个事务几乎同时提交，它们的redolog往往对应连续的LSN。这些事务就可以被归为同一个“组”，统一进行一次刷盘操作。组内事务越多，节省的IO成本就越明显。

redolog的组提交是InnoDB天然支持的机制，不需要额外配置，而且它只是延迟合并刷盘，并不会影响崩溃恢复的正确性。相比之下binlog也支持组提交，但效果通常不如redolog。这是因为binlog的write和fsync之间时间窗口较短，能够聚合在一起的事务较少。如果希望增强binlog的组提交效果，可以通过以下参数扩大提交窗口：

binlog_group_commit_sync_delay：延迟多少微秒再执行fsync
binlog_group_commit_sync_no_delay_count：累计多少事务后执行fsync

但需要注意，通过延迟fsync来提升性能，可能会在宕机时丢失binlog，从而影响主从复制和数据恢复一致性。

undolog

undolog是InnoDB在事务执行过程中生成的日志，它记录的是“数据修改之前的值”。当事务需要回滚时，就可以利用undolog把数据恢复到原来的状态。

除了用于回滚，undolog还是MVCC的基础。通过保留历史版本，MySQL可以在不同事务之间实现“读写不冲突”。

事务隔离级别与并发异常

在数据库系统中，当多个事务并发执行时，如果没有合理的控制机制，就可能出现各种数据不一致的问题。为了解决这些问题，数据库引入了事务隔离级别的概念。

其中，理想状态是序列化隔离级别。它要求所有并发事务的执行结果，必须等价于某种串行执行顺序。这意味着事务之间完全隔离，能够保证最强的一致性。然而，序列化虽然正确性最高，但在实现上难度大、性能开销高。因此，数据库系统通常会在一致性与性能之间做出权衡，这些权衡带来的问题统称为读写异常。

读写异常

在事务并发执行时，主要会出现以下几类异常

读异常
1. 脏读：一个事务读取到了另一个尚未提交事务的数据。如果后者回滚，就会导致前者读到“无效数据”
2. 不可重复读：同一个事务内，两次读取同一行数据，但由于另一个事务在中间修改并提交，导致两次结果不一致
3. 幻读：同一个事务内，两次执行相同条件的查询，但由于其他事务插入或删除了符合条件的记录，导致结果集发生变化
写异常
1. 丢失更新：两个事务同时修改同一条数据，后提交的事务覆盖了先提交的结果，导致前一个事务的修改丢失
2. 脏写：一个事务修改了另一个未提交事务已经修改过的数据
3. 写写冲突：多个事务同时写同一数据对象，最终可能导致覆盖或不一致结果
4. 写偏斜：两个事务基于同一个快照读取数据后，各自更新不同的记录，但最终组合结果违反业务约束

事务隔离级别

为了在一致性与性能之间折中，数据库定义了多种隔离级别：

RU：读未提交。是最低隔离级别，允许读取未提交数据，但可能出现脏读、不可重复读、幻读
RC：读已提交。事务只能读取已经提交的数据。能够避免脏读，但仍可能出现不可重复读、幻读
RR：可重复读。保证同一事务中多次读取同一行结果一致，能够防止不可重复读，但仍可能存在幻读
Serializable：序列化。是最强隔离级别，强制事务串行执行效果。通常通过锁机制保证，因此并发性能较差
SI：快照隔离。基于一致性快照实现，事务读取启动时的数据快照，读操作不阻塞写，仅在写写冲突时才回滚，仍可能发生写偏斜
SSI：序列化快照隔离。是在SI基础上增强，记录事务的读集与写集，在提交时检测冲突，若检测到潜在冲突则回滚事务

在Mysql中，通常通过MVCC实现隔离性。其核心思想是：为同一数据保留多个版本，通过“视图”决定事务能看到哪个版本。不同隔离级别下的“视图”机制如下：

RR：事务开始时生成一致性视图，整个事务期间使用同一视图，只能看到事务开始前已提交的数据
RC：每条SQL执行前生成新视图，每条语句看到的结果可能不同
RU：直接读取最新版本数据，不使用一致性视图
Serializable：不依赖MVCC快照，而是通过加锁控制并发执行

长事务问题

由上可知，在MVCC机制下，RR和RC都会生成试图，它们都会保留历史版本undolog。因此在这两种隔离级别下，如果存在长事务，就会导致undolog无法及时清理，占用大量存储空间，还会影响性能甚至导致数据库压力升高。因此，我们应该避免使用长事务。

间隙锁

在MySQL InnoDB存储引擎中，默认事务隔离级别是可重复读。在该隔离级别下，普通的快照读（普通SELECT）通常不会出现幻读，因为它依赖MVCC的一致性视图。但在一种特殊场景中幻读还是会发生，那就是当前读，例如：

SELECT ... FOR UPDATE或LOCK IN SHARE MODE 因为当前读需要读取最新数据，并加锁保证一致性。如果扫描过程中不对范围加锁，其他事务仍然可以插入新行，从而导致后续再次查询时结果集发生变化。就算对已扫描的行加锁，也无法阻止“新行插入”，因此仍然可能出现幻读。

为了解决“新行插入导致的幻读问题”，InnoDB引入了间隙锁。间隙锁锁定的是索引中两个已存在值之间的“空隙”。其特点是：不锁定实际存在的行，只锁定“插入空间”。

假设有一张表test_gap，主键id上有索引，且目前有三条数据：1、5、10

CREATE TABLE `test_gap` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `test_gap` VALUES (1), (5), (10);

当执行如下当前读时

SELECT * FROM t WHERE c = 7 FOR UPDATE;

由于7不存在，Mysql会在（5,10）上加间隙锁，阻止其他事务向该区间插入新记录，这样可以有效避免幻读。

Next-Key Lock

在InnoDB中，间隙锁通常与行锁组合使用，形成Next-Key Lock（临键锁）。临键锁的定义为：行锁 + 间隙锁，即锁定当前记录本身，以及它前后的间隙，形成一个“前开后闭区间”。InnoDB在索引末尾引入一个虚拟最大值：Supremum。例如：

(-∞, 1]
(1, 5]
(5, 10]
(10, +supremum]

间隙锁虽然解决了幻读，但容易造成“锁放大”。且间隙锁只在RR下生效