一文讲透 MySQL 锁、事务日志与索引优化：从 ACID 到两阶段提交

在 MySQL 面试中，锁、事务日志、ACID 和索引优化经常被分开提问，但它们本质上共同解决两个问题：并发访问时如何保证数据正确，以及数据库发生故障后如何恢复数据。本文以 InnoDB 为主线，将这些高频知识串联起来。

一、MySQL 的 ACID 特性

事务具有四个核心特性：

• 原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败。InnoDB 主要通过 undo log 实现回滚。

• 一致性（Consistency）：事务执行前后，数据必须满足约束和业务规则。一致性是最终目标，由原子性、隔离性、持久性以及业务约束共同保证。

• 隔离性（Isolation）：并发事务之间尽量互不干扰。InnoDB 通过锁和 MVCC 解决脏读、不可重复读、幻读等问题。

• 持久性（Durability）：事务提交后，即使数据库宕机，结果也不能丢失，主要依赖 redo log。

可以简记为：undo log 保证能回滚，锁与 MVCC 保证并发隔离，redo log 保证提交后能恢复。

二、InnoDB 中常见的锁

1. 共享锁与排他锁

**共享锁（S Lock）**也叫读锁。多个事务可以同时对同一条记录加共享锁，但此时其他事务不能获得该记录的排他锁。

SELECT * FROM account WHERE id = 1 FOR SHARE;

**排他锁（X Lock）**也叫写锁。一个事务获得排他锁后，其他事务不能再对相同记录获得共享锁或排他锁。

SELECT * FROM account WHERE id = 1 FOR UPDATE;
UPDATE account SET balance = balance – 100 WHERE id = 1;

普通 SELECT 通常属于快照读，依靠 MVCC 获取一致性视图，并不会直接加记录锁。SELECT … FOR SHARE、SELECT … FOR UPDATE、UPDATE 和 DELETE 等才属于需要重点关注的加锁操作。

2. 意向锁

意向锁是表级锁，用于表示事务准备或已经对表中的某些行加锁：

• 意向共享锁（IS）：准备对部分行加共享锁；

• 意向排他锁（IX）：准备对部分行加排他锁。

它的主要作用不是阻塞行级操作，而是让 MySQL 在申请表锁时，不必逐行检查是否存在记录锁。例如，事务要修改某行时，会先获得表的 IX 锁，再获得该行的 X 锁。

3. 表锁什么时候出现

表锁会锁住整张表，并发粒度较粗。常见情况包括：

• 显式执行 LOCK TABLES；

• 使用以表锁为主的 MyISAM 存储引擎；

• 某些 DDL 操作通过元数据锁阻止冲突访问；

• InnoDB 的意向锁也属于表级锁，但它与传统“锁住整张表的数据锁”作用不同。

需要注意：InnoDB 执行没有命中索引的更新时，通常不是直接升级为传统表锁，而是扫描并锁住大量索引记录，效果上可能接近“锁表”。因此，更新和删除条件能否使用合适索引非常重要。

4. 临键锁什么时候出现

临键锁（Next-Key Lock）可以理解为：

临键锁 = 记录锁 + 间隙锁

它不仅锁住已存在的索引记录，还锁住记录前方的索引间隙，从而阻止其他事务向该范围插入新记录，主要用于防止幻读。

在 InnoDB 默认的 REPEATABLE READ 隔离级别下，加锁读、UPDATE、DELETE 等执行范围扫描时，可能产生临键锁。例如：

SELECT * FROM user
WHERE age >= 20 AND age < 30
FOR UPDATE;

如果 age 上有普通索引，该语句可能锁定扫描到的记录及相关间隙。需要记住三个边界：

三、undo log、redo log 与 binlog

1. undo log：解决事务回滚与版本读取

undo log 记录数据修改前的逻辑信息。例如，把余额从 1000 修改为 900，需要保留能够恢复旧值的信息。当事务失败或主动执行 ROLLBACK 时，InnoDB 可以利用它撤销修改。

此外，MVCC 还会通过 undo log 形成历史版本链，让不同事务根据一致性视图读取合适的数据版本。因此它主要解决：

• 事务回滚，保证原子性；

• 保存历史版本，支持 MVCC。

2. redo log：解决宕机后的数据恢复

InnoDB 修改数据时，通常先修改内存中的数据页，不会要求每次事务提交都立即把所有数据页随机写回磁盘。它会先顺序写入 redo log，这就是 WAL（Write-Ahead Logging，预写日志）思想。

如果数据库宕机，已提交事务的数据页尚未落盘，InnoDB 可以在重启后重放 redo log 恢复修改。它是 InnoDB 存储引擎层的物理日志，主要保证持久性。

3. binlog：解决复制与数据恢复

binlog 属于 MySQL Server 层，记录数据库发生的逻辑变更，主要用于：

• 主从复制；

• 基于时间点的数据恢复；

• 数据订阅和增量同步。

三种日志可以这样区分：

四、为什么需要两阶段提交

一次事务提交时，redo log 和 binlog 都要记录同一项修改。如果简单地顺序写入两份日志，写完第一份后数据库宕机，就可能导致两者不一致。

例如，先写 redo log 再写 binlog：如果前者成功、后者失败，主库恢复后可能保留该事务，但从库收不到对应的 binlog。反过来，先写 binlog 再写 redo log，也可能出现从库执行了事务，而主库恢复后没有该事务的问题。

MySQL 因此通过内部 XA 两阶段提交协调二者：

崩溃恢复时：

• redo log 已是 commit，说明事务已经完成，可以恢复；

• redo log 处于 prepare，则检查是否存在完整的 binlog；

• 存在对应 binlog 就提交，否则回滚。

这样便能保证存储引擎的崩溃恢复结果与 Server 层的复制日志一致。

五、常见索引失效场景

所谓索引失效，一般是指优化器没有使用预期索引，或者只使用了联合索引的一部分。常见情况包括：

WHERE YEAR(create_time) = 2026
WHERE age + 1 = 20

WHERE phone = 13800138000

WHERE name LIKE '%mysql'

因此，“语法上可以使用索引”不等于“优化器一定使用索引”。应通过 EXPLAIN 查看 key、type、rows 和 Extra 等信息判断实际执行情况。

六、UNION、UNION ALL 与去重

UNION 用于合并多个查询结果，并对最终结果去重：

SELECT id FROM table_a
UNION
SELECT id FROM table_b;

UNION ALL 直接合并结果并保留重复数据，省去了去重过程，通常性能更好：

SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;

如果业务上确认结果不会重复，或者允许重复，应优先考虑 UNION ALL。

常见去重方式包括：

• DISTINCT：对查询结果的指定列组合去重；

• GROUP BY：按列分组，也能形成唯一分组，但主要用途是分组聚合；

• UNION：对多个查询合并后的结果去重；

• 窗口函数 ROW_NUMBER()：按业务规则为重复记录编号，再保留其中一条。

需要特别纠正：ORDER BY 只负责排序，不能去重。它可以与上述方法组合使用，但不是去重手段。

七、存储引擎是表级还是数据库级

存储引擎最终是表级属性。创建表时可以指定：

CREATE TABLE user (
id BIGINT PRIMARY KEY,
name VARCHAR(50)
) ENGINE = InnoDB;

也可以修改已有表：

ALTER TABLE user ENGINE = InnoDB;

MySQL 可以配置服务器的默认存储引擎，未显式指定时，新表使用默认值。但同一个数据库中的不同表仍然可以使用不同存储引擎。因此，“数据库使用 InnoDB”通常只是简化表达，准确说法应是“数据库中的表使用 InnoDB，或系统默认引擎为 InnoDB”。

八、总结

MySQL 的事务安全不是由某一个机制单独完成的：锁和 MVCC 控制并发访问，undo log 支持回滚与历史版本，redo log 保证崩溃恢复，binlog 支持复制和数据恢复，两阶段提交则保证两份关键日志的一致性。

在查询优化方面，需要同时理解索引使用条件、执行计划和数据分布，不能只靠背诵“索引失效规则”。对于结果集合并，UNION 会去重，UNION ALL 保留重复项；DISTINCT、GROUP BY 和窗口函数可以完成不同场景下的去重，而 ORDER BY 只负责排序。最后，存储引擎属于表级属性，同一数据库可以同时包含不同引擎的表。

把这些知识点联系起来，才能真正理解 MySQL 如何在并发、性能和可靠性之间取得平衡。