Mysql基础看这篇就够了

发布于: 2023-06-18
更新于: 2023-07-09

文章目录

Mysql基础

主要是讲mysql的基础构成还有一些sql的原理，本文中也有对部分网上文章、课程的总结回顾，在末尾处会贴出链接，也会有些自己总结的流程图归纳，欢迎交流讨论

基本架构

查询缓存

对于写压力大的业务，频繁更新缓存压力大，且带来收益不大，每次对表的更新，都会触发表的查询缓存全部清空

8.0中移除了这个模块

优化器

生成执行计划时，会决定sql的执行方式，比如常见的当查询条件涉及两个索引or两张表时，优先从哪个索引、哪张表上进行查询，都会在这步进行生成

执行器

权限校验
调用存储引擎获取数据，如果遇到符合条件的加入结果集，直到满足查询条件后，将结果集返回给client

单表数据量上限

MySQL 单表能放多少数据，不能简单背成“多少行”。对 InnoDB 来说，更准确的说法是：单表上限主要受表空间大小、页大小、文件系统、行大小、索引数量共同影响。

理论上限

InnoDB 最大表空间大小取决于 innodb_page_size，表空间最大值也就是单表最大值：

InnoDB page size	最大表空间/单表大小
4KB	16TB
8KB	32TB
16KB	64TB
32KB	128TB
64KB	256TB

默认页大小通常是 16KB，所以常见说法是 InnoDB 单表理论最大约 64TB。这个值还会受文件系统限制影响，例如文件系统单文件最大值小于 InnoDB 内部上限时，实际单表上限以文件系统为准。

如果问“最多多少行”，就要看每行平均占用空间：

单表最大行数 ≈ 表空间上限 / 单行平均大小

这里的单行平均大小不只包含数据列，还要考虑聚簇索引记录头、隐藏列、变长字段、页目录、页内碎片、二级索引等额外空间。比如每行平均 1KB，64TB 理论上可以容纳约 640 亿行；但如果每行平均 10KB，则理论行数会下降到约 64 亿行。

实际建议

真实业务里通常不会把单表逼近理论上限，原因有几个：

B+ 树层级变高后，范围查询、回表、分页、统计都会更慢。
二级索引越多，写入、更新、删除的维护成本越高。
DDL、备份、恢复、校验、归档、迁移都会变重。
大事务或长事务会放大 undo、redo、binlog、复制延迟问题。

所以单表容量更常见的判断方式是：结合 QPS、索引设计、冷热数据比例、归档策略、DDL 时间窗口来定。当单表进入千万级、亿级以后，就应该开始关注慢查询、索引膨胀、备份恢复耗时和 Online DDL 风险；如果继续增长，可以考虑归档、分区、分库分表或按业务维度拆表。

Online DDL

Online DDL 指的是在执行 ALTER TABLE 这类表结构变更时，尽量减少对线上读写的阻塞。它不是“完全无锁”，而是通过不同算法降低复制数据、重建表和持锁时间。

MySQL 8.x 常见 DDL 算法：

ALGORITHM=INSTANT：只改数据字典元数据，不重建表，也不改已有行数据，速度最快。MySQL 8.4 中部分加列、删列、修改默认值、重命名列等操作可以走 instant。
ALGORITHM=INPLACE：尽量在原表空间内完成，可能仍然需要重建表，但比 COPY 少一些额外开销。比如添加二级索引、部分修改列属性、重建主键相关操作。
ALGORITHM=COPY：创建临时新表，把原表数据复制过去，再替换原表。这个方式最重，对大表风险最高。

LOCK 用来约束 DDL 期间允许的并发访问：

LOCK=NONE：尽量允许并发读写。
LOCK=SHARED：允许读，不允许写。
LOCK=EXCLUSIVE：读写都阻塞。
LOCK=DEFAULT：交给 MySQL 自动选择。

Online DDL 大致流程：

常见例子：

-- 只改默认值，通常是元数据操作
ALTER TABLE user ALTER COLUMN status SET DEFAULT 1, ALGORITHM=INSTANT;

-- 添加普通二级索引，通常可以 online，但仍然会扫描表并构建索引
ALTER TABLE user ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

-- 修改字段类型，很多场景需要 COPY，属于高风险大表 DDL
ALTER TABLE user MODIFY COLUMN name VARCHAR(512), ALGORITHM=COPY;

需要注意：

Online DDL 仍然会申请 MDL，长事务、慢查询、未提交事务都可能阻塞 DDL；DDL 等锁时也可能反过来阻塞后续新查询。
INSTANT 不是所有操作都支持，MySQL 8.4 中 instant 加/删列会产生 row version，达到版本数量上限后需要用 COPY 或 INPLACE 重建表。
INPLACE 不等于不重建表，例如添加主键、修改部分列属性仍然可能重建表。
大表 DDL 前要先在从库或影子表验证耗时、磁盘空间、复制延迟和回滚方案。

常见索引查询优化

索引优化的核心目标不是“让 SQL 一定走索引”，而是减少扫描行数、减少回表次数、避免额外排序、让结果尽早变小。

假设有一张订单表：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  remark VARCHAR(255),
  KEY idx_user_status_time (user_id, status, created_at),
  KEY idx_status_time (status, created_at)
) ENGINE = InnoDB;

回表

InnoDB 中主键索引是聚簇索引，叶子节点存的是整行数据；普通二级索引的叶子节点存的是索引列和主键值。

所以通过二级索引查询时，如果要返回的列不在二级索引里，就需要先在二级索引中找到主键，再拿主键去聚簇索引查整行，这一步就叫回表。

SELECT id, user_id, status, created_at, remark
FROM orders
WHERE user_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

这个查询可以走 idx_user_status_time 找到满足条件的记录，但 remark 不在索引里，因此需要对命中的每一行回表读取完整记录。

回表本身不是绝对坏事。如果命中行很少，回表成本可以接受；如果命中很多行，比如扫描几十万行再回表，就会非常慢。

覆盖索引

覆盖索引指的是查询需要的字段都能从某个索引中拿到，不需要回表。比如上面的查询如果不返回 remark：

SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

idx_user_status_time(user_id, status, created_at) 加上二级索引叶子节点自带的主键 id，已经覆盖了所有返回列，因此可以避免回表。EXPLAIN 的 Extra 中常见 Using index，通常就表示使用了覆盖索引。

设计覆盖索引时要克制：

适合高频、返回列少、延迟敏感的查询。
不适合为了覆盖偶发查询，把很多大字段都塞进索引。
VARCHAR 大字段、文本字段、低选择性字段过多会让索引变胖，拖慢写入和缓存命中。

最左前缀和联合索引顺序

联合索引遵循最左前缀原则。以 idx_user_status_time(user_id, status, created_at) 为例：

-- 可以较好使用 user_id + status + created_at
WHERE user_id = 1001 AND status = 1 AND created_at >= '2026-01-01'

-- 可以使用 user_id + status
WHERE user_id = 1001 AND status = 1

-- 不能直接高效使用 status + created_at 这一段
WHERE status = 1 AND created_at >= '2026-01-01'

联合索引常见排序思路：

等值过滤字段放前面，比如 user_id、status。
范围过滤字段放后面，比如 created_at >= ...。
如果需要排序，尽量让 ORDER BY 字段接在等值字段后面，减少额外排序。
高选择性字段通常更适合放前面，但要结合查询模式，不能只看区分度。

文件排序 filesort

filesort 是 MySQL 的额外排序算法名，不一定真的落磁盘文件；数据量小可能在内存里排，数据量大或内存不够时才可能借助磁盘临时文件。

当 ORDER BY 不能直接利用索引顺序时，EXPLAIN 的 Extra 里可能出现 Using filesort。

SELECT id, user_id, status, created_at
FROM orders
WHERE status = 1
ORDER BY amount DESC
LIMIT 20;

如果只有 idx_status_time(status, created_at)，这个 SQL 虽然可以用 status 过滤，但索引顺序不是 amount，所以需要对候选结果按 amount 重新排序。

常见优化方式：

建立匹配过滤和排序的联合索引，比如 (status, amount)。
让 WHERE 中的等值条件在索引前缀，ORDER BY 字段紧跟其后。
避免对排序字段使用函数或表达式，否则索引顺序很难直接利用。
控制返回列和候选行数，减少排序的数据量。

比如：

ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);

SELECT id, status, amount
FROM orders
WHERE status = 1
ORDER BY amount DESC
LIMIT 20;

这个查询就更有机会按 idx_status_amount 的索引顺序读取，避免大范围 filesort。

延迟关联

延迟关联常用于深分页优化。普通深分页的问题是：MySQL 需要先找到并丢弃大量 offset 之前的数据，如果还要回表读取很多列，成本会更高。

-- 深分页，offset 越大越慢
SELECT id, user_id, status, amount, created_at, remark
FROM orders
WHERE status = 1
ORDER BY created_at DESC
LIMIT 100000, 20;

延迟关联的思路是：第一步只用覆盖索引拿到目标页的主键；第二步再拿这 20 个主键回表查完整数据。

SELECT o.id, o.user_id, o.status, o.amount, o.created_at, o.remark
FROM orders o
JOIN (
  SELECT id
  FROM orders
  WHERE status = 1
  ORDER BY created_at DESC
  LIMIT 100000, 20
) t ON o.id = t.id
ORDER BY o.created_at DESC;

这个写法的收益在于：前面的深分页扫描尽量只扫二级索引，不读取整行；真正回表只发生在最后 20 行。

延迟关联适合：

返回列比较多，无法覆盖索引。
offset 很大，直接查会产生大量回表。
排序字段能被索引利用，例如 (status, created_at)。

如果业务允许，更推荐把深分页改成基于游标的翻页：

SELECT id, user_id, status, amount, created_at
FROM orders
WHERE status = 1
  AND created_at < '2026-09-12 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

这种方式避免了大 offset，通常比延迟关联更稳定。

常见判断方式

看索引是否有效，优先看 EXPLAIN：

type：至少希望常见查询达到 ref、range，最好是 const、eq_ref；如果是 ALL，通常代表全表扫描。
key：实际使用的索引。
rows：优化器预估扫描行数，越小越好。
Extra：重点关注 Using index、Using where、Using filesort、Using temporary。

常见优化套路：

只查需要的列，减少 SELECT *。
为高频查询设计联合索引，而不是给每个字段单独建索引。
让等值条件、范围条件、排序条件尽量符合联合索引顺序。
用覆盖索引减少回表，但不要为了覆盖把索引做得过宽。
看到 Using filesort 不一定立刻建索引，要先判断候选行数和排序成本。
深分页优先考虑游标分页，其次考虑延迟关联。

MySQL 锁机制

MySQL 的锁可以按层次理解：server 层负责保护元数据和显式表锁，InnoDB 层负责事务、表级意向锁和行级锁。一次 SQL 执行时，通常不是只上一把锁，而是从外到内逐层加锁。

常见锁类型

锁类型	所属层	作用	释放时机
全局读锁	server 层	`FLUSH TABLES WITH READ LOCK`，让整个实例进入全局只读，常用于文件系统快照备份	`UNLOCK TABLES`、连接断开
元数据锁 MDL	server 层	保护表结构不被并发 DDL 修改	autocommit 下语句结束；显式事务中通常到事务结束
显式表锁	server 层	`LOCK TABLES READ/WRITE`，手动锁表	`UNLOCK TABLES`、连接断开、再次 `LOCK TABLES`、部分事务操作
意向锁 IS/IX	InnoDB 表级锁	表示事务准备在某些行上加共享锁或排他锁，用于协调表锁和行锁	事务结束
共享锁 S	InnoDB 行锁	允许读，不允许其他事务修改该行	事务结束
排他锁 X	InnoDB 行锁	用于更新或删除，阻止其他事务读写加锁版本	事务结束
记录锁 Record Lock	InnoDB 行锁	锁住索引上的某条记录	事务结束
间隙锁 Gap Lock	InnoDB 行锁	锁住两个索引记录之间的间隙，阻止插入	事务结束
临键锁 Next-Key Lock	InnoDB 行锁	记录锁 + 前面的间隙锁，解决幻读问题	事务结束
插入意向锁 Insert Intention Lock	InnoDB 行锁	插入前声明要插入某个 gap；不同位置的插入可以并发	事务结束
自增锁 AUTO-INC Lock	InnoDB 表级机制	分配自增 ID，保证自增值生成规则	取决于 `innodb_autoinc_lock_mode` 和语句类型

MDL 元数据锁

MDL 用来保护“表结构”这个元数据。只要 SQL 访问一张表，MySQL 就会给这张表加 MDL。普通查询和 DML 通常拿共享类 MDL，DDL 需要排他 MDL。

-- 会持有 users 的 MDL 读锁
START TRANSACTION;
SELECT * FROM users WHERE id = 1;

-- 另一个会话执行这个 DDL，会等待上面的事务结束
ALTER TABLE users ADD COLUMN age INT;

在 autocommit 模式下，一个语句就是一个事务，MDL 通常语句结束就释放；在显式事务里，访问过的表的 MDL 会延迟到 COMMIT 或 ROLLBACK 时释放。因此线上 DDL 经常卡在 Waiting for table metadata lock，根因通常是有长事务、慢查询或未提交事务占着表。

InnoDB 意向锁

意向锁是 InnoDB 的表级锁，但它不是为了直接锁整张表，而是为了告诉外部：“我接下来要在表里的某些行上加锁”。

IS：Intention Shared，准备给某些行加共享锁。
IX：Intention Exclusive，准备给某些行加排他锁。

比如执行：

SELECT * FROM users WHERE id = 1 FOR SHARE;

InnoDB 会先在表上加 IS，再在命中的行上加 S 锁。

再比如执行：

UPDATE users SET name = 'Tom' WHERE id = 1;

InnoDB 会先在表上加 IX，再在命中的行上加 X 锁。

意向锁的价值是快速判断表锁是否能加。如果一个事务已经在很多行上加了 X 锁，另一个事务想 LOCK TABLES users WRITE，MySQL 不需要扫描所有行锁，只要看到表上有冲突的意向锁，就知道不能立刻加表锁。

行锁、间隙锁、临键锁

InnoDB 的行锁本质上锁的是“索引记录”。即使表没有显式索引，InnoDB 也会用隐藏聚簇索引来加锁。

常见几种：

Record Lock：锁住某条索引记录，比如主键 id = 10。
Gap Lock：锁住两个索引记录之间的间隙，比如 (10, 20)，防止其他事务插入新记录。
Next-Key Lock：锁住一个左开右闭区间，比如 (10, 20]，等于 gap lock + record lock。

例如索引里有 10、20、30，范围更新：

UPDATE users SET status = 1 WHERE id BETWEEN 10 AND 20;

在可重复读隔离级别下，InnoDB 可能会锁住扫描到的记录以及相关间隙，防止其他事务在范围里插入新行造成幻读。

如果是唯一索引等值查询，并且能确定只命中一条记录：

UPDATE users SET status = 1 WHERE id = 10;

通常只需要对 id = 10 这条索引记录加记录锁，不需要锁前后的 gap。

SQL 执行时怎么加锁

以一条更新语句为例：

UPDATE orders
SET status = 2
WHERE user_id = 1001
  AND status = 1;

大致加锁流程如下：

关键点：

UPDATE、DELETE、锁定读通常会锁“扫描到的索引记录”，不只是最终满足 WHERE 的行。执行计划走错索引时，锁范围可能被放大。
如果通过二级索引加排他锁，InnoDB 还会回到聚簇索引记录上加锁。
没有合适索引时，范围会退化成大量扫描，可能锁住很多记录，甚至看起来像“锁表”。
普通 SELECT 在 InnoDB 下通常是快照读，不加行锁；SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 是当前读，会加锁。

常见 SQL 会加什么锁

-- 普通快照读：不加行锁，依赖 ReadView
SELECT * FROM orders WHERE id = 1;

-- 锁定读：加共享锁，其他事务可以读，但不能改
SELECT * FROM orders WHERE id = 1 FOR SHARE;

-- 锁定读：加排他锁，常用于先查再改
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- 主键等值更新：通常是主键记录 X 锁
UPDATE orders SET status = 2 WHERE id = 1;

-- 范围更新：可能是 next-key 锁，锁记录和间隙
UPDATE orders SET status = 2 WHERE created_at >= '2026-01-01';

-- 插入：先加插入意向锁，再给新插入的索引记录加 X 记录锁
INSERT INTO orders(id, user_id, status, amount, created_at)
VALUES(1, 1001, 1, 99.00, NOW());

锁什么时候释放

释放时机要分层看：

InnoDB 行锁、间隙锁、next-key 锁、意向锁：通常在事务结束时释放，也就是 COMMIT 或 ROLLBACK。
autocommit 模式：单条 DML 自己就是一个事务，语句执行成功后提交，锁也随之释放。
显式事务：BEGIN 后的锁会一直持有到 COMMIT 或 ROLLBACK，所以事务里不要夹杂慢操作、RPC、用户交互。
MDL：autocommit 下通常语句结束释放；显式事务中访问过的表会持有到事务结束。
LOCK TABLES 表锁：用 UNLOCK TABLES 显式释放，或者连接断开时释放；再次执行 LOCK TABLES 会先释放已有表锁。
FLUSH TABLES WITH READ LOCK 全局读锁：用 UNLOCK TABLES 释放，常用于备份场景。

例如：

BEGIN;
UPDATE orders SET status = 2 WHERE id = 1;
-- 这里行锁、IX 意向锁、MDL 仍然持有
COMMIT;
-- 提交后释放事务相关锁

如果事务长时间不提交，其他更新相同行或相同范围的事务就会等待，最后可能触发 innodb_lock_wait_timeout；如果出现环形等待，InnoDB 的死锁检测会选择一个事务回滚。

排查锁问题

常用排查入口：

-- 看当前 InnoDB 事务
SELECT * FROM information_schema.INNODB_TRX;

-- 看 data lock，MySQL 8.0+ 常用
SELECT * FROM performance_schema.data_locks;

-- 看锁等待关系
SELECT * FROM performance_schema.data_lock_waits;

-- 看 MDL
SELECT * FROM performance_schema.metadata_locks;

-- 看最近一次死锁
SHOW ENGINE INNODB STATUS;

优化建议：

让更新条件命中合适索引，减少扫描范围，也就是减少加锁范围。
事务尽量短，先算好数据再开启事务。
多表更新时，所有业务按同样顺序访问表和行，降低死锁概率。
分页批量更新时用小批次提交，避免一次锁太多行。
DDL 前先检查长事务，避免卡在 MDL。

更新实现方式

架构

SQL 提交流程

下面以 InnoDB 事务提交为例，梳理一条更新 SQL 从开启事务到提交结束的大致流程：

trx_id 数据结构和存储位置

trx_id 是 InnoDB 内部事务 ID，用来标识“哪个事务最后修改了这条记录”。它主要服务于 MVCC、回滚、锁等待诊断等 InnoDB 内部逻辑，和 binlog 里的 GTID、两阶段提交里的 xid 不是一个东西。

可以把 trx_id 理解成一个递增的事务编号：

InnoDB transaction
  ├── trx_id: InnoDB 内部事务 ID
  ├── state: RUNNING / LOCK WAIT / ROLLING BACK / COMMITTING
  ├── undo log 链表
  ├── 持有的锁信息
  └── read view / 隔离级别相关信息

几个关键点：

只有真正需要事务 ID 的事务才会分配 trx_id。普通一致性非锁定只读事务通常不会生成真实 trx_id，第一次写入、加锁读、修改事务状态时才更可能分配。
在数据页里，trx_id 存在于 InnoDB 聚簇索引记录的隐藏列 DB_TRX_ID 中，长度是 6 字节，表示最后一次插入或更新这行记录的事务 ID。
因为 DB_TRX_ID 是 6 字节，所以行记录里能表达的事务 ID 理论上限是 2^48 - 1，也就是 281474976710655。这个值非常大，正常业务很难耗尽，但它不是无限的。
聚簇索引记录里还会有隐藏列 DB_ROLL_PTR，长度是 7 字节，指向 undo log 里的回滚记录。通过 DB_TRX_ID + DB_ROLL_PTR，InnoDB 可以沿着 undo 链构造旧版本。
如果表没有显式主键，InnoDB 还会生成隐藏的 DB_ROW_ID，长度也是 6 字节，用来作为聚簇索引键。
二级索引记录通常不直接保存完整行版本的 DB_TRX_ID 和 DB_ROLL_PTR，需要时会回到聚簇索引记录判断版本可见性。

一行 InnoDB 聚簇索引记录可以粗略理解成：

clustered index record
  ├── record header
  ├── DB_ROW_ID    6 bytes，如果没有主键才需要
  ├── DB_TRX_ID    6 bytes，最后修改该行的事务 ID
  ├── DB_ROLL_PTR  7 bytes，指向 undo log 记录
  └── 用户定义列

MVCC 判断可见性时，会把记录上的 DB_TRX_ID 和当前事务的 ReadView 比较：

如果 DB_TRX_ID 小于 ReadView 的低水位，说明这个版本在快照创建前已经提交，通常可见。
如果 DB_TRX_ID 大于等于 ReadView 的高水位，说明这个版本在快照创建后才出现，通常不可见。
如果 DB_TRX_ID 落在活跃事务列表中，说明快照创建时该事务还没提交，不可见。
如果当前版本不可见，就根据 DB_ROLL_PTR 找到 undo log 中的旧版本，继续用旧版本的事务信息判断。

如果 trx_id 真的接近或超过上限，问题会很严重。MVCC 的可见性判断依赖事务 ID 大体单调递增，一旦回绕成较小的值，新事务写出的记录可能看起来像“很老的版本”，ReadView 就可能误判记录可见性，导致一致性读结果异常、undo purge 判断混乱，甚至让实例进入不可可靠服务的状态。

MySQL/InnoDB 不像 PostgreSQL 那样通过 vacuum freeze 主动冻结老事务 ID；在 MySQL 里，trx_id 耗尽属于极端异常场景。真实生产中如果监控发现事务 ID 水位异常接近上限，通常思路不是等待回绕，而是尽快停写、备份校验、迁移或重建实例，保证新的实例重新获得健康的事务 ID 空间。

information_schema.INNODB_TRX 中也能看到当前正在执行事务的 TRX_ID，但这是观测正在运行事务的诊断入口，不等于每条记录都能直接通过 SQL 查出隐藏列。

xid 介绍

xid 更容易混淆，因为它在不同语境里有两层含义。

第一层是普通事务提交时 binlog 里的 XID_EVENT。事务修改了支持 XA/两阶段提交的存储引擎表之后，MySQL 在 binlog 中写入 XID_EVENT 作为事务提交标记。这个 event 里携带一个 8 字节左右的内部 xid，可以理解成 server 层和 InnoDB 在两阶段提交中对齐同一个事务的提交编号。

普通事务两阶段提交
  ├── InnoDB redo log prepare，记录 xid
  ├── server 层写 binlog，binlog 中出现 XID_EVENT
  ├── binlog fsync 成功
  └── InnoDB redo log commit

崩溃恢复时，MySQL 会检查 redo log 中处于 prepare 状态的事务，再去 binlog 中查是否存在相同 xid 的提交记录：

如果 redo log 已 prepare，但 binlog 中找不到对应 xid，说明 binlog 没提交成功，事务需要回滚。
如果 redo log 已 prepare，并且 binlog 中能找到对应 xid，说明 binlog 已经提交成功，事务需要提交。

第二层是显式 XA 事务里的 xid。这种 xid 是 XA 规范里的事务标识，格式是：

xid = gtrid [, bqual [, formatID ]]

gtrid：global transaction id，全局事务 ID。
bqual：branch qualifier，分支事务标识。
formatID：格式编号。

例如：

XA START 'order-1001', 'mysql-branch', 1;
UPDATE account SET balance = balance - 100 WHERE id = 1;
XA END 'order-1001', 'mysql-branch', 1;
XA PREPARE 'order-1001', 'mysql-branch', 1;
XA COMMIT 'order-1001', 'mysql-branch', 1;

总结一下三者区别：

名称	所属层	主要用途	常见位置
`trx_id`	InnoDB	MVCC、undo、锁诊断	聚簇索引隐藏列 `DB_TRX_ID`、InnoDB 事务对象、`INNODB_TRX`
`xid`	server 层 + InnoDB 提交协调	两阶段提交、崩溃恢复时判断事务提交还是回滚	redo prepare 记录、binlog `XID_EVENT`
`GTID`	复制层	全局标识已提交事务，方便主从复制和故障切换	binlog `GTID_LOG_EVENT`、`gtid_executed`

主从同步逻辑

MySQL 主从同步本质上是备库把主库的 binlog 拉取到本地 relay log，再由备库回放 relay log 中的事件，最终让数据追上主库。

存量库初始化同步

如果主库已经跑了一段时间，备库不能只从当前 binlog 开始追，因为历史存量数据还没有。完整搭建链路一般分成两步：先做一次全量备份恢复，让备库拥有某个时间点的数据快照；再从这个快照对应的 binlog 位点或 GTID 集合开始追增量。

两种全量备份方式的区别：

ibd 物理全量备份：复制 InnoDB 的物理数据页，恢复速度通常更快，更适合大库。它依赖一致性快照和崩溃恢复能力，不能只拷贝单个 .ibd 文件就认为完成备份，还要考虑表结构、数据字典、undo、redo、系统表空间等配套信息。
SQL 逻辑全量备份：把库表结构和数据导成 SQL，例如 CREATE TABLE 和 INSERT。优点是可读、可跨版本或跨环境恢复；缺点是大库导出和恢复速度较慢。

位点复制下，备份时要拿到类似 mysql-bin.000123 + 456789 的起点；备库恢复完成后，从这个位置继续拉 binlog。GTID 复制下，备份中要保留或记录 gtid_executed，备库恢复后使用 SOURCE_AUTO_POSITION = 1 让主库自动发送缺失事务。

拉取 binlog 流程

binlog event 数据结构

下面按 MySQL 8.4 LTS 这类高版本来理解。binlog 本身是二进制文件，逻辑上由一组连续的 event 组成；每个 event 都有自己的起始位置 Pos 和结束位置 End_log_pos，备库就是根据这些位置或 GTID 来持续拉取。

binlog 文件结构可以粗略理解成：

binlog file
  ├── magic number
  ├── FORMAT_DESCRIPTION_EVENT
  ├── PREVIOUS_GTIDS_LOG_EVENT
  ├── GTID_LOG_EVENT / ANONYMOUS_GTID_LOG_EVENT
  ├── QUERY_EVENT(BEGIN)
  ├── TABLE_MAP_EVENT
  ├── WRITE_ROWS_EVENT / UPDATE_ROWS_EVENT / DELETE_ROWS_EVENT
  ├── XID_EVENT(COMMIT)
  └── ...

单个 event 的结构可以粗略分成四部分：

common header：事件公共头，常见字段有 timestamp、event_type、server_id、event_size、log_pos、flags。其中 log_pos 指向当前 event 结束后的下一个位置，备库会用它更新复制位点。
post-header：当前 event 类型自己的固定头信息。比如 TABLE_MAP_EVENT 里会有 table_id 和 flags，row event 里会有 table_id、flags、额外信息长度等。
event body：真正的事件内容。statement 格式会记录原始 SQL；row 格式会记录表映射和变更前后的行数据。
footer：高版本一般会带 checksum，用于校验 event 内容是否完整。

几个常见 event 的作用：

FORMAT_DESCRIPTION_EVENT：说明当前 binlog 文件使用的事件格式版本、server 版本、header 长度等，解析后续 event 时会用到。
PREVIOUS_GTIDS_LOG_EVENT：记录这个 binlog 文件之前已经包含过的 GTID 集合，方便复制和恢复时判断事务连续性。
GTID_LOG_EVENT：标识接下来这个事务对应的 GTID。
QUERY_EVENT：statement 格式下记录 SQL；row 格式下也会用于记录 BEGIN、DDL、以及一些上下文信息。
TABLE_MAP_EVENT：row 格式下非常关键，用一个 table_id 映射到具体的库、表、列类型、列元数据、nullable 位图等。
WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT：记录实际行变更。UPDATE_ROWS_EVENT 通常会同时记录修改前的行镜像和修改后的行镜像。
XID_EVENT：事务提交事件，可以理解成这个事务在 binlog 里的 commit 标记。
ROTATE_EVENT：binlog 文件切换事件，告诉复制线程后面要读取哪个 binlog 文件。

比如有一张表：

CREATE TABLE account (
  id BIGINT PRIMARY KEY,
  name VARCHAR(32) NOT NULL,
  balance DECIMAL(10,2) NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE = InnoDB;

执行一条事务更新：

START TRANSACTION;
UPDATE account
SET balance = balance - 100.00,
    updated_at = '2026-09-12 10:00:00'
WHERE id = 1;
COMMIT;

如果高版本 MySQL 使用常见的 binlog_format=ROW，那么它不会主要记录“原始 UPDATE 语句”，而是记录“哪张表的哪一行从什么值变成了什么值”。用 mysqlbinlog -vv --base64-output=DECODE-ROWS 查看时，大致会看到类似这样的结构：

# at 1234
# server id 1  end_log_pos 1310  GTID  last_committed=10 sequence_number=11
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1024'/*!*/;

# at 1310
# server id 1  end_log_pos 1388  Query  thread_id=28  exec_time=0  error_code=0
SET TIMESTAMP=1789188000/*!*/;
BEGIN

# at 1388
# server id 1  end_log_pos 1468  Table_map: `test`.`account` mapped to number 108

# at 1468
# server id 1  end_log_pos 1588  Update_rows: table id 108 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=1 /* BIGINT meta=0 nullable=0 is_null=0 */
###   @2='Alice' /* VARSTRING(32) nullable=0 is_null=0 */
###   @3=1000.00 /* DECIMAL(10,2) nullable=0 is_null=0 */
###   @4='2026-09-12 09:00:00' /* DATETIME nullable=0 is_null=0 */
### SET
###   @1=1
###   @2='Alice'
###   @3=900.00
###   @4='2026-09-12 10:00:00'

# at 1588
# server id 1  end_log_pos 1619  Xid = 80123
COMMIT/*!*/;

这个例子里可以看到几个重点：

GTID_LOG_EVENT：标识这个事务的全局事务 ID，备库可以据此判断自己是否已经执行过。
QUERY_EVENT(BEGIN)：标识事务开始。
TABLE_MAP_EVENT：告诉备库 table id 108 对应 test.account，并携带列类型等信息。
UPDATE_ROWS_EVENT：记录行级变更，WHERE 部分是变更前的行镜像，SET 部分是变更后的行镜像。
XID_EVENT：事务提交。备库回放到这里时，提交前面这组 row event。

如果是 statement 格式，binlog 更像是记录 SQL：

Query  thread_id=28  exec_time=0  error_code=0
use `test`;
UPDATE account
SET balance = balance - 100.00,
    updated_at = '2026-09-12 10:00:00'
WHERE id = 1;

实际生产更常见的是 row 格式，因为它更适合复制一致性。需要注意，高版本 MySQL 如果开启了 binlog 事务压缩，SHOW BINLOG EVENTS 里还可能先看到 Transaction_payload_event，它会把一个事务内的多个 event 压缩成一个载荷展示；解包后内部仍然是 GTID、BEGIN、TABLE_MAP、ROWS、XID 这一类事件。

备库回放流程

GTID 同步方式

GTID 是全局事务 ID，格式通常是 server_uuid:transaction_id。开启 GTID 后，复制不再强依赖手工指定 binlog 文件名和 position，而是围绕“哪些事务已经执行过、哪些事务还缺失”来同步。

GTID 同步的大致逻辑：

主库提交事务时，为事务生成 GTID，并把 GTID event 写入 binlog。
备库记录自己已经执行过的事务集合，也就是 gtid_executed。
建立复制连接时，备库把自己的 gtid_executed 集合发送给主库。
主库比较备库已执行集合和自己的 binlog，找出备库缺失的 GTID 事务。
dump thread 从缺失事务开始发送 binlog event。
备库写入 relay log，并由 SQL 线程或 applier worker 回放。
回放成功后，备库更新自己的 gtid_executed，下次断点续传时继续用这个集合定位。

GTID 的好处是切换主库或断点续传时更简单：备库不用人工判断从哪个 binlog 文件和 position 开始，只要告诉新主库自己已经执行过哪些 GTID，新主库就能继续发送缺失事务。需要注意的是，如果主库已经清理掉备库缺失 GTID 对应的 binlog，复制仍然会失败，需要重新搭建备库或补齐缺失日志。