腾讯技术面：聊聊MySQL五大核心模块

腾讯云开发者

发布于 2025-12-25 13:02:42

120

本文系统剖析了MySQL的核心技术架构，重点聚焦于分层逻辑结构、InnoDB存储引擎设计、事务机制与并发控制、主从复制原理及分区策略五大模块。文章旨在帮助开发者深入理解MySQL的运行机制与性能优化要点，内容涵盖B+树索引原理、MVCC实现、两阶段提交等关键技术细节，并解答了单表2000万数据量限制的经典问题。

01、前言

MySQL是一种开源的关系型数据库管理系统（RDBMS），具备高性能、高可靠、跨平台、社区活跃等特性，是构建各种规模应用程序的首选之一。为了在项目中更好地使用MySQL，我们有必要探索MySQL的核心知识。

分层逻辑架构
InnoDB存储引擎
事务机制与并发控制
主从复制原理
分区策略与应用场景
SQL鱼骨图

02、分层逻辑架构

首先我们来了解下MySQL的整体架构：

MySQL 采用分层逻辑架构，包括：连接层 (Connection Layer)、服务层 (Server Layer / SQL Layer) 和存储引擎层 (Storage Engine Layer)。这种分层设计使得 MySQL 功能强大、灵活、可扩展。

2.1 连接层

负责处理客户端的网络连接请求和身份验证，高效、安全地建立和维护客户端与服务器之间的通信通道。主要组件与功能：

连接池 (Connection Pool)： 管理网络连接（TCP/IP Socket、命名管道、共享内存等），连接复用避免频繁创建销毁的开销，显著提高性能。
连接管理 (Connection Handling)： 为每个客户端连接分配一个线程来处理该连接上的所有请求。
身份认证 (Authentication)： 验证客户端的用户名、密码、主机名等信息。
安全 (Security)： 支持 SSL/TLS 加密连接，确保数据传输的安全性。

2.2 服务层

接收来自连接层的 SQL 语句，进行解析、优化，并最终确定如何高效地访问数据。它还负责内置函数的实现、跨存储引擎的功能（如视图、存储过程、触发器等）。主要组件及其功能：

查询缓存 (Query Cache) (注：MySQL 8.0 中已移除)： (在 <8.0 版本存在) 缓存 SELECT 语句及其结果集。如果后续收到完全相同的 SELECT 查询，并且数据未被修改，则直接从缓存中返回结果（省去解析、优化、执行步骤）。因其设计缺陷（如全局锁、易失效）在实际应用中效果不佳，最终被移除。
解析器 (Parser)：
- 词法分析 (Lexical Analysis)： 将 SQL 文本分解成一系列有意义的“词”(Tokens)（如关键字、标识符、操作符）。
- 语法分析 (Syntax Analysis)： 检查 SQL 语法是否符合规则，生成一颗代表 SQL 语句结构的“解析树”(Parse Tree)。
预处理器 (Preprocessor)： 对解析树进行进一步的语义检查。检查表和列是否存在、名称是否有歧义、用户是否有权限等。如果查询涉及视图，则进行视图展开。
查询优化器 (Optimizer)： 根据解析树、表结构信息（元数据）、索引统计信息等，评估执行查询的各种可能方案。计算不同执行计划的预估成本（主要考虑 IO 和 CPU 开销）。选择它认为成本最低的“执行计划”(Query Execution Plan), 决定使用哪些索引、表连接的顺序（JOIN Order）、是否使用临时表等策略。优化器的决策对性能影响巨大。
查询执行引擎 (Query Execution Engine)： 负责执行优化器生成的执行计划。调用存储引擎层提供的 API（Handler API）来执行底层的数据读写操作。
内置函数 (Built-in Functions)： 实现 SQL 标准函数（如 COUNT(), SUM(), MAX(), NOW(), CONCAT()）以及一些扩展函数。
跨引擎功能 (Cross-Engine Features)： 管理存储过程、自定义函数、触发器（虽然触发器逻辑定义在服务层，但其执行会作用于具体的存储引擎表）、视图（作为虚拟表处理，最终需转换为对底层表的查询）、备份恢复等服务器端功能的核心逻辑。

2.3 引擎层

负责数据的实际读写操作，支持可插拔存储引擎，不同的存储引擎就是不同的“插件”，可以在启动时或运行时（对某些引擎）加载或更改。常见存储引擎简要对比。

2.4 SQL查询处理流程

在上述逻辑架构下，一条SQL 查询处理流程如下：建立连接、查询缓存（8.0后删除）、SQL 语句解析、查询优化、执行查询和返回结果。

03

InnoDB存储引擎

MySQL支持可插拔存储引擎，其中最重要也最常见的存储引擎为：InnoDB 。它凭借先进的设计理念，在事务支持、数据安全、并发控制、索引优化等方面都具备显著优势。下面我们来看下其核心知识：

高性能索引结构：B+树
聚簇索引
- 为什么常说单表存储数据上限为2000W 条数据？
二级索引
索引优化

3.1 高性能索引结构：B+树

InnoDB使用B+树作为索引结构，这使其在进行数据检索时效率极高。那么B+树相比于其他数据结构有什么独特之处呢？ B+树结构示意如下：

B+树 vs. 二叉搜索树（BST）或平衡二叉搜索树（如 AVL 树、红黑树）：B+树的高度更矮，查询效率更高

B+树：一种多路平衡搜索树，每个节点可以包含多个键值和指针，通常一个节点能存储成百上千个数据项，因此树的高度非常低O(logmn)（m成百上千，通常是 3~4 层就能存储千万级数据）。B+ 树通过多路分支大幅降低树高，减少磁盘 I/O 次数，提高查询效率。
二叉搜索树（尤其是非平衡的）在最坏情况下可能退化成链表，查询时间复杂度为 O(n)。
平衡二叉搜索树（如红黑树），树高为 O(log₂n)，对于磁盘存储来说，树高仍然过高，导致多次 I/O 操作。

B+ 树 vs. 哈希表：B+支持范围查找、索引内存占用少，支持更大数量级

哈希表： 查找基于哈希函数直接定位，时间复杂度接近 O(1)，但是，哈希表通常需要 将所有数据（或至少所有索引+数据）加载进内存才能发挥最佳性能，内存消耗随数据量线性增长，且难以扩展。哈希表完全不支持范围查询（如 WHERE id > 100 AND id < 200），也不支持排序操作，无法按照键的大小顺序遍历。
B+ 树： 通过 多路分支和树形层级结构，可以用 较小的内存开销管理大规模数据，尤其适合磁盘存储环境。B+ 树是天然有序的，叶子节点形成有序链表，非常适合范围查询和排序操作。

B+ 树 vs. B 树

B+ 树其实是 B 树的一种变体，二者都是多路平衡树，但 B+ 树在数据库索引中更具优势，B+ 树通过将数据集中到叶子节点，并建立叶子节点间的链表，极大地优化了范围查询和全表扫描的效率。更多区别如下：

3.2 聚簇索引

聚簇索引最大的特点是索引叶节点包含完整的数据记录，索引即数据。
数据按照聚簇索引的顺序进行存储，范围查询（如 WHERE id > 100）效率极高。
每个 InnoDB 表有且仅有一个聚簇索引，如果定义了主键，主键就是聚簇索引，所以主键查询非常快（O(log n)），直接定位到数据行。如果没有主键，但有唯一且非空的索引，会选择它作为聚簇索引。如果既没有主键，也没有合适的唯一索引，InnoDB 会 隐式生成一个 6 字节的 rowid 作为聚簇索引（不可见，用户无法控制）。
聚簇索引查找流程：查询主键为18的数据，在聚簇索引 B+Tree 中定位到 id=18，叶子节点直接返回完整行数据，无回表。

为什么常说单表存储数据上限为2000W 条数据？

关于“单表存储数据上限为2000万条数据”的说法，其实并不是一个绝对的技术限制。2000万这个数字，并不是 MySQL 或任何数据库系统强制规定的硬性上限，而是一个在很多实际业务场景中，当单表数据量达到这个级别时，如果不做优化，性能可能会明显下降的经验阈值，尤其是查询性能。

2000W 条数据是如何估算的？

背景知识：

磁盘扇区：磁盘上存储数据最小单元是扇区，一个扇区的大小是 512 字节；
块（block）：文件系统（例如EXT4）最小单元是块（block），一个block 块的大小是 4KB；
页（Page）：Innodb 存储引擎的最小储存单元——页（Page），由若干磁盘块组成。

InnoD的每一张表在磁盘上会对应一个.ibd文件，叫作表空间。在表空间中，数据是以数据页的形式存储的，每页的大小默认为16K（可以使用 innodb_page_size 参数在创建实例时指定页面大小，该参数支持4KB、8KB、16KB和32KB。选择合适的页大小需要考虑工作负载和硬件性能，大的页可以减少I/O操作但占用更多空间，小的页则反之。）

2000W 条数据计算

设：非叶子结点存放的指向其他数据页的指针数量为 X ；叶子节点存放的行数据数量为 Y ；B+ 树的层数（高）为 Z 。则：B+树能存放的总行数 = (X ^ (Z-1)) * Y。

代入计算：一个数据页 16KB 。假设主键 ID 为 bigint 类型 8 字节，指针大小在 InnoDB 源码中设置为6 字节，一条数据是 14 字节左右，一个数据页中能存放 X = 16*1024/14 ≈ 1170 条数据，指向 1170 个新的数据页。Y 是数据页中能容纳的最大行记录数量，所以与实际存储的行记录的大小有关，假设一条行记录占用的空间大小为1KB，则：Y = 16。

假设B+树是 2 层，那 Z = 2，则总行数 = (1170 ^ (2-1)) * 16 = 18720（约 2 W）。
假设B+树是 3 层，那 Z = 3，则总行数 = (1170 ^ (3-1)) * 16 = 21902400（约 2 KW）。
假设B+树是 4 层，那 Z = 3，则总行数 = (1170 ^ (4-1)) * 16 ≈ 2 百亿。

在 InnoDB 中 B+ 树高度一般为1-3层，它就能满足千万级的数据存储。查询数据时，每加载一页（page）代表一次IO，所以通过主键索引查询通常只需要1~3次 I/O 操作即可查找到数据。MySQL 都有缓存， B+ 树高度为 3 时，第 1 层和第 2 层的数据都在缓存中，查询只需要一次 I/O 操作，速度很快，但是当数据超出 2KW 时， B+ 树层高会增加，需要再多一次 I/O 操作，查询效率就急速下降了。所以MySQL数据库单表建议最大2KW数据。

3.3 二级索引

聚簇索引之外的所有索引都称为 二级索引 或 辅助索引，比如普通索引、唯一索引、组合索引等
在 InnoDB 中，二级索引的叶子节点并不存储行数据本身，而是存储对应的主键值。
当你使用 二级索引查询数据时，1. 先在二级索引中找到对应的 主键值，然后根据这个主键值，再到聚簇索引中查找真正的数据行 —— 这个过程叫 回表（Bookmark Lookup / 回表查询）。
二级索引查找流程：二级索引查询name为alice的人，在 idx_name B+Tree 找到 'Alice' → 获得主键值（如 id=18），主键值到聚簇索引查询完整数据 → 二次磁盘 I/O（回表）。

注意：覆盖索引优化二级索引查询效率，例如查询SELECT id, name FROM users WHERE name = 'Alice'; 若 name为二级索引，则无需回表。

3.4 索引优化

索引覆盖

定义：当查询所需的所有列都包含在索引中时，数据库可以直接从索引中获取数据而无需回表查询原始数据行，这种索引被称为"覆盖索引"。覆盖索引可以显著提高检索性能。

示例：

-- 假设在users表的(name, age)上建立了联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 这个查询可以使用索引覆盖，因为只查询了索引包含的列
SELECT name, age FROM users WHERE name = '张三';

优势：避免了回表操作，减少I/O操作（不需要读取数据页），减少CPU消耗（不需要处理数据行）。

索引下推

定义：MySQL 5.6引入的特性，将WHERE条件中索引相关部分"下推"到存储引擎层进行过滤，而不是在服务器层过滤。

特点：

在存储引擎层就过滤掉不符合条件的记录
减少回表操作和服务器层处理的记录数
特别适用于联合索引的部分列查询

示例：

sql复制-- 假设在users表的(name, age)上建立了联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- MySQL 5.6之前：存储引擎只根据name='张三'查找索引，然后返回所有匹配的记录到服务器层，由服务器层过滤age>20的记录
-- MySQL 5.6及之后：存储引擎会在索引层面就同时检查name='张三'和age>20，只返回同时满足两个条件的记录
SELECT * FROM users WHERE name = '张三' AND age > 20;

优势：减少存储引擎返回给服务器层的数据量，减少回表操作（对于二级索引），提高查询效率，特别是对于范围查询后的过滤条件。

04

事务机制与并发控制

4.1 事务概述

事务是InnoDB存储引擎支持的最大的一个特性。事务是指数据库的一组操作，要么全部执行成功，要么全部失败回滚。事务具有四大核心特性ACID：

原子性（Atomicity）：事务中的所有操作要么全部提交成功，要么全部失败回滚。由 Undo Log 保证。
持久性（Durability）：事务一旦提交，其对数据的改变就是永久性的，即使系统故障也不会丢失。由 Redo Log 保证。
隔离性（Isolation）：多个并发事务之间互不干扰，一个事务的内部操作对其他隔离的事务是不可见的。由 锁机制 和 MVCC 保证。
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。由ADI特性保证。

4.2 Undo Log

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，记录的是 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。并且，undo-log 本身是会被删除清理的，例如 INSERT 操作，在事务提交之后就可以清除掉了；UPDATE/DELETE 操作在事务提交不会立即删除，会加入 history list，由后台线程 purge 进行清理。

undo log 是采用 segment（段）的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment（undo 日志段），undo log segment 包含在 rollback segment（回滚段）中。事务开始时，需要为其分配一个 rollback segment。每个 rollback segment 有 1024 个 undo log segment，这有助于管理多个并发事务的回滚需求。

通常情况下， rollback segment header（通常在回滚段的第一个页）负责管理 rollback segment。rollback segment header 是 rollback segment 的一部分，通常在回滚段的第一个页。history list 是 rollback segment header 的一部分，它的主要作用是记录所有已经提交但还没有被清理（purge）的事务的 undo log。这个列表使得 purge 线程能够找到并清理那些不再需要的 undo log 记录。

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

场景设定：

表结构： users (id INT PRIMARY KEY, name VARCHAR(20))
初始数据： 表中有一行数据： id = 1, name = 'Alice'
事务： 事务 ID (Trx ID) = 100
执行语句： UPDATE users SET name = 'Bob' WHERE id = 1;
假设： 这行数据存储在 Page 123 上。该页在 Buffer Pool 中已被修改（成为脏页），但尚未写入磁盘数据文件 (.ibd)。

逻辑日志

逻辑日志体现： 它记录了 “事务 100 在 users 表上更新了主键 id=1 的那行数据，把 name 字段从 ‘Alice’ 改成了新值（新值是什么 undo log 不关心）”。它关注的是行和字段的逻辑概念以及旧值。
存储位置： 这条 undo log 记录本身会被写入一个特殊的 undo 段 (undo segment) 页中（比如 Page 500）。注意： 对这个 undo 段页 (Page 500) 的修改本身也会产生 redo log！因为 undo log 也需要持久化。
回滚操作： 如果事务 100 回滚，InnoDB 会根据这条 undo log 执行：UPDATE users SET name = 'Alice' WHERE id = 1;。这是一个逻辑操作。

4.3 Redo Log

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复未落盘的数据。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

刷盘时机

InnoDB 将 redo log 刷到磁盘上有几种情况：

事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘（可以通过innodb_flush_log_at_trx_commit参数控制，后文会提到）。
log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。
正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

总之，InnoDB 在多种情况下会刷新重做日志，以保证数据的持久性和一致性。

我们要注意设置正确的刷盘策略innodb_flush_log_at_trx_commit 。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。 innodb_flush_log_at_trx_commit 的值有 3 种，也就是共有 3 种刷盘策略：

0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

刷盘策略innodb_flush_log_at_trx_commit 的默认值为 1，设置为 1 的时候才不会丢失任何数据。为了保证事务的持久性，我们必须将其设置为 1。另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

为什么呢？ 因为在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。下面是不同刷盘策略的流程图。

为0时，如果 MySQL 挂了或宕机可能会有1秒数据的丢失。

为1时，只要事务提交成功，redo log 记录就一定在硬盘里，不会有任何数据丢失。如果事务执行期间 MySQL 挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。

为2时，只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。如果仅仅只是 MySQL 挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在这个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

write pos 是当前记录的位置，一边写一边后移
checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。 write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

Redo Log使用WAL的策略顺序写，所以刷盘速度很快。

4.4 MVCC

MVCC Multi-version Concurrency Control，即多版本并发控制，用于提高数据库的并发访问性能。核心思想：不加锁，为每次数据修改创建一个“版本”（Version）。

为什么需要 MVCC？解决什么问题？

在没有 MVCC 的数据库中（或使用简单的锁机制），为了保证数据一致性，通常会采用强锁：

写操作会阻塞其他事务的读和写。
读操作可能会阻塞其他事务的写（取决于隔离级别，如可重复读需要通过加锁避免幻读）。

这会导致严重的性能瓶颈，尤其是在读多写少的应用场景中。 MVCC 主要解决了以下问题：

读写冲突：允许读事务和写事务并发执行，互不阻塞。
提升并发性能：通过避免不必要的锁等待，大幅提高系统吞吐量。

MVCC 实现核心组件

1、事务 ID（Transaction ID）

每个事务在开始时都会被分配一个唯一且单调递增的 ID。
用于标识数据版本的创建者和可见性。

2、数据行的隐藏字段

DB_TRX_ID（6字节）：最后修改该行数据的事务ID。当一个事务修改某行时，会将自己的事务ID记录于此。
DB_ROLL_PTR（7字节）：回滚指针。指向该行数据的前一个版本（存储在 Undo Log 中），形成一条版本链。
DB_ROW_ID（6字节）：行ID（隐式主键）。如果表没有主键，InnoDB 会用它生成聚簇索引。

3、版本链

通过 DB_ROLL_PTR 指针，将同一个数据行的多个版本连接成一个链表。链表的头部是当前最新的数据版本。

4、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。
可见性判断： 一个事务在对一行数据做读取操作的时候，会从undo log历史版本链中从最新版本开始往前比对，通过一系列的规则，根据快照版本中的trx_id字段和read view来确定该版本对于当前事务是否可见，如果当前比对版本不可见，那么就通过roll_pointer找到上一个版本进行比对，直到找到可见版本或找不到任何一个可见版本。这些规则定义如下：
- 如果 trx_id < min_trx_id，则说明该版本对于当前事务(read view)来说，是已提交事务生成的，那么对于当前事务可见。
- 如果trx_id >= max_trx_id：则说明该版本对于当前事务(read view)来说，是"将来"的事务生成的，那么对于当前事务不可见。
- 如果min_trx_id <= trx_id < max_trx_id：如果trx_id在read view的活跃事务id列表中，则说明该版本对于当前事务(read view)来说，是已开始但未提交的事务生成的，那么对于当前事务不可见。如果trx_id不在read view的活跃事务id列表中，则说明该版本对于当前事务(read view)来说，是已提交的事务生成的，那么对于当前事务可见。注：当前事务id(current_trx_id)也会在活跃事务id列表中，如果undo log是由当前事务生成的，也就是trx_id == current_trx_id，那么此版本对于当前事务来说当然可见

5、Undo Log（回滚日志）

用于存储数据被修改前的旧版本。当某行数据被更新时，旧版本的数据会被复制到 Undo Log 中，并通过 DB_ROLL_PTR 指针与新版本连接起来。
作用：
- 提供数据的历史版本：用于实现一致性读。
- 事务回滚：如果事务需要回滚，可以用 Undo Log 中的旧版本数据恢复。
- 系统崩溃恢复。

MVCC 的工作流程：以 SELECT 和 UPDATE 为例

假设我们有一行数据 name = ‘Alice’，事务 ID 为 100 的事务将其修改为 ’Bob‘。一个 SELECT 查询（事务 ID=200）在事务 100 提交前后执行，它应该看到什么？

1、UPDATE 操作（事务 Trx100）：

不是直接覆盖 ’Alice‘，而是先将 ’Alice‘ 这行数据拷贝到 Undo Log。
然后在当前数据页中修改数据为 ’Bob‘，并更新 DB_TRX_ID = 100，DB_ROLL_PTR 指向 Undo Log 中的 ’Alice‘ 版本。
此时，版本链是：当前页 (Bob, Trx100) -> Undo Log (Alice, 某个旧TrxID)。

2、SELECT 操作（事务 Trx200）：事务 100 还未提交

事务 200 开始，执行 SELECT，生成自己的 Read View。假设此时事务 100 还未提交，则 m_ids 中包含 [100]。
事务 200 读取这行数据，发现最新版本的 DB_TRX_ID=100。
可见性判断规则（简化）：
- 如果 DB_TRX_ID < min_trx_id，说明该版本在 Read View 创建前已提交，可见。
- 如果 DB_TRX_ID >= max_trx_id，说明该版本在 Read View 创建后才开启，不可见。
- 如果 DB_TRX_ID 在 m_ids 中，说明创建该版本的事务在生成 Read View 时还活跃（未提交），不可见。
- 如果 DB_TRX_ID 是创建者自己的ID，可见。
在本例中，Trx100 在 m_ids 中，所以最新版本 ’Bob‘ 对 Trx200 不可见。
事务 200 沿着 DB_ROLL_PTR 找到旧版本 ’Alice‘，判断其 DB_TRX_ID 小于 min_trx_id（已提交），因此 ’Alice‘ 对 Trx200 可见。所以事务 200 读到的结果是 ’Alice‘。

3、SELECT 操作（事务 Trx200）：事务 100 提交后：

另一个事务 Trx300 开始 SELECT，生成新的 Read View。此时事务 100 已提交，不在活跃事务列表 m_ids 中。
Trx300 读取数据，发现最新版本的 DB_TRX_ID=100。
判断规则：Trx100 不在 m_ids 中，且 Trx100 < Trx300，说明该版本已提交，可见。
所以 Trx300 读到的结果是 ’Bob‘。

MVCC 与事务隔离级别

MVCC 是实现高级别事务隔离（如 读已提交 - RC 和 可重复读 - RR）的基础。

读已提交（Read Committed）：
- 每次执行 SELECT 语句时都会生成一个新的 Read View。
- 所以每次读都能看到最新已提交的数据。
可重复读（Repeatable Read）：
- 只在事务中第一次执行 SELECT 时生成一个 Read View，后续的 SELECT 都复用这个 View。
- 所以在整个事务中，每次读到的都是一致的数据快照，避免了不可重复读和幻读（在 InnoDB 中，通过 Next-Key Lock 和 MVCC 结合基本解决了幻读）。

MVCC 的优缺点

优点：

高并发：读写不相互阻塞，是读多写少应用的理想选择。
一致性读：保证了事务看到的数据是一致的。

缺点：

存储开销：需要存储数据的多个版本，会消耗更多的磁盘空间。
维护开销：需要管理版本链和清理过期数据（Purge 操作）。
版本清理：长时间未提交的事务会阻止 Undo Log 中旧数据版本的清理，可能导致 Undo Log 膨胀。

4.5 幻读

什么是幻读？

幻读（Phantom Read）：事务 A 两次读取同一条件的数据，在两次读取之间，事务 B 插入或删除了符合该条件的数据，导致事务 A 第二次读取时看到“凭空出现”或“消失”的行。
关键问题：仅锁定现有记录（行锁）无法防止其他事务插入新数据，因此需要锁定“可能插入新数据的区间”。

如何避免幻读？

在 MySQL 的 可重复读（Repeatable Read） 隔离级别下，通过锁定范围（间隙锁 + 记录锁）可以避免幻读。

记录锁（Row Lock）：锁定符合条件的现有行。
间隙锁（Gap Lock）：锁定索引记录之间的“间隙”，阻止其他事务在区间内插入新数据。

为什么仅锁定现有行（记录锁）无法避免幻读？

场景复现：仅锁定现有行导致幻读

假设有一张用户表 users，字段如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT,
    KEY idx_age (age)  -- 二级索引
);

表中现有数据：

事务 A（不加间隙锁）

事务 A（假设隔离级别为读已提交）
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;  -- 仅加记录锁
-- 第一次查询结果：id=2 和 id=3（age=25 的两条记录）

事务 B 插入新数据

-- 事务 B
BEGIN;
INSERT INTO users (id, age) VALUES (5, 25);  -- 插入新的 age=25 的记录
COMMIT;

事务 A 再次查询

sql复制-- 事务 A 再次执行查询
SELECT * FROM users WHERE age = 25 FOR UPDATE;
-- 第二次查询结果：id=2、id=3、id=5（多了一条记录！）
COMMIT;

结果分析

事务 A 第一次查询时，仅锁定了已有的 age=25 记录（id=2 和 id=3）。
事务 B 成功插入了一条新的 age=25 记录（id=5）。
事务 A 第二次查询时，看到了新插入的 id=5，导致幻读。 锁定读 & 非锁定读

为什么记录锁无法阻止幻读？

记录锁（Row Lock）会锁定满足条件的现有数据行（例如 id=2 和 id=3）。
但它不会锁定索引中的“间隙”（例如 (20, 25) 或 (25, 30)），因此其他事务可以自由插入新的 age=25 记录。

对比：如何通过锁定范围避免幻读？如果事务 A 在可重复读（Repeatable Read）隔离级别下执行，InnoDB 会默认使用 Next-Key Lock（记录锁 + 间隙锁）：

1、锁定范围：

记录锁：锁定现有的 age=25 行（id=2 和 id=3）。
间隙锁：锁定 (20, 25) 和 (25, 30) 的区间，阻止插入新的 age=25 数据。

2、事务 B 的插入操作会被阻塞：

事务 B 插入 id=5, age=25 时，会因间隙锁的存在而等待，直到事务 A 提交或超时。

3、事务 A 两次查询结果一致：

事务 A 第二次查询时，仍只能看到 id=2 和 id=3，避免了幻读。

间隙锁为什么可以阻止age=25的数据插入，间隙锁是开区间，不包含25，为什么可以阻止数据插入？

在 MySQL 的 InnoDB 引擎中，间隙锁（Gap Lock） 虽然本身是“开区间”，但它通过 Next-Key Lock 机制 和 索引结构的特性（顺序存储） 阻止特定值的插入。以下通过一个具体例子，结合索引结构和锁机制，解释为什么间隙锁可以阻止 age=25 的数据插入。 索引结构与插入逻辑

假设有一个二级索引 idx_age，现有数据如下：

age | 行位置（假设物理存储顺序）
----|-------------------
20  | 行1
25  | 行2
25  | 行3
30  | 行4

插入 age=25 的逻辑

当插入一个新的 age=25 时，InnoDB 会根据索引的 有序性，将新数据插入到所有 age=25 的现有记录之后、下一个不同值（30）之前。例如：

新插入的 age=25 会位于行3 和行4 之间的位置（物理存储层面）。

锁定范围的真正作用假设事务 A 执行以下操作：

BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;  -- 可重复读隔离级别下触发 Next-Key Lock

InnoDB 会为 age=25 加 Next-Key Lock，具体锁定范围包括：

记录锁（Row Lock）：锁定所有 age=25 的现有行（行2 和行3）。

间隙锁（Gap Lock）：- 左间隙：锁定 (20, 25)，阻止插入 20 < age <25 的数据。

右间隙：锁定 (25, 30)，阻止插入 25 < age <30 的数据。

为什么插入 age=25 会被阻塞？

新插入的 age=25 虽然值等于 25，但根据索引的有序性，它会被插入到行3（最后一个 age=25 的行）和行4（age=30）之间的位置，即 (25, 30) 的间隙。

事务 A 的间隙锁已经锁定了 (25, 30) 的区间，因此插入操作需要获取该区间的插入意向锁（Insert Intention Lock），与间隙锁冲突，导致阻塞。

图示说明

索引值：20    ←[间隙锁(20,25)]→   25（行2、行3）   ←[间隙锁(25,30)]→   30

插入 age=25 的新记录时，实际插入的位置在 25（行3）和 30 之间的间隙（即 (25, 30)）。
由于事务 A 锁定了 (25, 30) 的间隙，插入操作被阻塞。

关键点总结

间隙锁的“开区间”不包含边界值，但通过锁定相邻区间，间接影响新数据的插入位置。

索引的有序性 决定新数据的插入位置，即使值相同（如 age=25），也会被插入到最后一个相同值之后的间隙。

Next-Key Lock 的组合（记录锁 + 间隙锁）确保：- 现有行无法被修改或删除。

新数据无法插入到被锁定的间隙中。
间隙锁 + 顺序存储

对比：如果只有记录锁如果事务 A 仅加记录锁（如 读已提交 隔离级别）：

-- 事务 A（读已提交隔离级别）
BEGIN;
SELECT * FROM users WHERE age = 25 FOR UPDATE;  -- 仅加记录锁

事务 B 可以插入新的 age=25 到 (25, 30) 的间隙，导致事务 A 出现幻读。

索引有序性

主键索引有序性：数据按主键值排序，决定数据行的物理存储顺序。
二级索引有序性：数据按索引列排序，相同值时按主键排序，影响插入位置和间隙锁范围。
间隙锁的作用：即使间隙锁是开区间，但通过索引的有序性，新数据的插入位置会落入被锁定的间隙，从而避免幻读。

索引有序性其它作用？

精准锁定范围：索引的有序性使得 InnoDB 能明确锁定需要保护的区间（如 (20,25) 和 (25,30)）。
避免幻读：通过锁定间隙，阻止其他事务插入符合条件的新数据。
高效查询：有序性使得范围查询（如 WHERE age > 20）能快速定位数据，减少扫描行数。

05、主从复制原理

5.1 流程

主数据库接收到一个写操作（如 INSERT、UPDATE、DELETE）时，会将这个操作记录到二进制日志（Bin Log）中，将数据修改的操作按顺序记录下来。
从数据库 IO 线程会自动连接主服务，从二进制中读取同步数据，记录到中继日志（Relay Log）中。
从数据库的 SQL 线程会定期从中继日志中获取同步数据，写入到从数据库中。

5.2 BinLog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。那 binlog 到底是用来干嘛的？可以说 MySQL 数据库的数据备份、主备、主主、主从都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。

binlog 会记录所有涉及更新数据的逻辑操作，并且是顺序写。

Bin Log 日志格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

statement
row
mixed

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，记录的内容如下。

同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。为了解决这种问题，我们需要指定为row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据，记录内容如下。

row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。 update_time=now()变成了具体的时间update_time=1627112756247，条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值（假设这张表只有 3 个字段）。这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗 IO 资源，影响执行速度。所以就有了一种折中的方案，指定为mixed，记录的内容是前两者的混合。 MySQL 会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

binlog 的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到 binlog 文件中。因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。 binlog 日志刷盘流程如下

上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是1。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。

虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

在出现 IO 瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的 binlog 日志。

两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。 binlog（归档日志）保证了 MySQL 集群架构的数据一致性。虽然它们都属于持久化的保证，但是侧重点不同。在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

回到正题，redo log 与 binlog 两份日志之间的逻辑不一致，会出现什么问题？我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。假设执行过程中写完 redo log 日志后，binlog 日志写期间发生了异常，会出现什么情况呢？

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为 redo log 日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。原理很简单，将 redo log 的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于prepare阶段，并且没有对应 binlog 日志，就会回滚该事务。

再看一个场景，redo log 设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然 redo log 是处于prepare阶段，但是能通过事务id找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

5.3 同步机制分类

1、异步复制（Asynchronous Replication）

原理：
- 主库提交事务后，无需等待从库确认，直接响应客户端。从库通过 I/O 线程异步拉取 Binlog 进行同步。
优点：- 主库性能高（无等待延迟）。
- 对网络抖动容忍性强。
缺点：- 数据不一致风险：主库宕机时，未同步的数据可能丢失（数据延迟）。
适用场景： 对数据一致性要求不高的业务（如日志分析、读多写少的应用）。

2、半同步复制（Semisynchronous Replication）

原理： 主库提交事务后，至少等待一个从库接收并写入 Relay Log 后才响应客户端（MySQL 5.5+ 支持）。
优点：- 降低数据丢失风险（确保至少一个从库有最新数据）。
缺点：- 主库性能下降（需等待从库ACK）。
- 网络延迟或从库故障时，主库写入会被阻塞（可配置超时降级为异步）。
配置命令：

sql复制INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

3、组复制（Group Replication）

原理：基于 Paxos 协议实现多节点强一致性（MySQL InnoDB Cluster 的核心，MySQL 5.7+ 支持）。- 事务提交需由多数节点（N/2+1）确认。
- 自动故障切换，支持多主写入（冲突检测）。
优点：- 高可用与强一致性兼备。
- 自动脑裂防护。
缺点：- 部署复杂，网络要求高（低延迟）。
- 写入性能低于单主模式。
适用场景： 金融级高可用需求（如银行核心系统）。

5.4 拓扑结构

一主一从

作用：读写分离、冷备容灾。

一主多从

作用：读密集型业务负载均衡（如电商大促）

级联复制

优点： 减轻主库网络压力（Binlog 分发给中间层从库）。
缺点： 数据延迟层级叠加。

双主复制（Master-Master）

原理： 两个节点互为主从，可双向同步。
风险： 数据冲突（如同时修改同一行），需业务层规避。
适用场景： 跨地域双向同步（需配合分片或错峰写入）。

多源复制

用途： 从库聚合多个数据源（如分库数据汇总分析）。
要求： MySQL 5.7+ 支持多通道复制（CHANNEL）。

5.5 如何选择复制模式？

场景需求	推荐模式
读扩展	一主多从（异步复制）
数据高一致	半同步复制或组复制
跨地域容灾	级联复制 + 半同步
多数据中心双向同步	双主复制（业务层防冲突）
聚合多个数据源	多源复制
7x24 高可用	组复制（InnoDB Cluster）

06、分区策略与应用场景

6.1 类型详解

RANGE 分区

原理：根据列的取值范围将数据分布到不同分区。语法示例：

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

适用场景：

时间序列数据（如日志、交易记录）
数值范围数据（如价格区间、年龄分段）

优点：

便于按范围快速删除旧数据（直接 DROP PARTITION）
查询时可通过分区剪裁（Partition Pruning）提升性能

LIST 分区

原理：根据离散的值列表将数据分布到分区。

CREATE TABLE users (
    id INT,
    region_id INT,
    username VARCHAR(50)
) PARTITION BY LIST (region_id) (
    PARTITION p_east VALUES IN (1, 2, 3),
    PARTITION p_west VALUES IN (4, 5, 6),
    PARTITION p_south VALUES IN (7, 8, 9),
    PARTITION p_other VALUES IN (DEFAULT)
);

适用场景：

地域、类别等离散值数据
需要按特定值快速查询的场景

HASH 分区

原理：根据哈希函数将数据均匀分布到指定数量的分区。

-- 基于列值的HASH分区
CREATE TABLE orders (
    id INT,
    order_date DATE,
    customer_id INT
) PARTITION BY HASH(customer_id)
PARTITIONS 4;
-- 基于表达式的HASH分区（线性哈希）
CREATE TABLE logs (
    id INT,
    log_time DATETIME
) PARTITION BY LINEAR HASH(YEAR(log_time))
PARTITIONS 6;

适用场景：

需要数据均匀分布的通用场景
无明确范围或列表分类的数据

优点：

数据分布均匀，避免热点分区
线性哈希（LINEAR HASH）便于扩展分区数量

KEY 分区

原理：类似 HASH 分区，但使用 MySQL 内置的哈希函数，支持多列分区键。

CREATE TABLE events (
    id INT AUTO_INCREMENT,
    event_type VARCHAR(20),
    created_at TIMESTAMP,
    PRIMARY KEY (id, event_type)
) PARTITION BY KEY(event_type)
PARTITIONS 5;

适用场景：

主键或唯一键的分区
需要多列分区键的场景

复合分区（子分区）

CREATE TABLE sales_detail (
    id INT,
    sale_date DATE,
    region VARCHAR(10),
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH(region)
SUBPARTITIONS 4 (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

6.2 分区策略选择指南

业务场景	推荐分区策略	理由
时间序列数据（日志、监控）	RANGE + 按时间分区	便于按时间范围管理和清理数据
用户数据分片	HASH/KEY + 用户ID	均匀分布，避免热点
多租户SaaS系统	LIST + 租户ID	按租户隔离数据
地理分布数据	LIST + 地域编码	按地域管理查询