MySQL：索引、事务与锁MySQL：索引、事务与锁

专题导读

MySQL 面试不要把索引、事务和锁拆成三套孤立知识。一次 InnoDB 查询从 B+ 树定位记录 开始；需要修改时先拿到合适的锁；提交时依靠 redo log 保证崩溃恢复，并通过 binlog 复制到从库；并发读取则通过 MVCC + undo log 读取历史版本。

建议沿着这条链路回答：数据如何存 → SQL 如何找 → 并发时读到什么 → 修改时锁住什么 → 宕机后如何恢复。下面默认讨论 MySQL 8.x 的 InnoDB；MyISAM 没有事务、行锁和 MVCC，不能把它的行为套进来。

学习目标

• 解释聚簇索引、二级索引、回表与覆盖索引的实际数据路径
• 从最左前缀、选择性和排序需求设计联合索引，而不是机械背规则
• 能读懂 EXPLAIN 的关键列，并通过慢查询定位索引与 SQL 问题
• 解释 RC / RR 下 MVCC、Read View、快照读与当前读的差异
• 说清 Record Lock、Gap Lock、Next-Key Lock 的作用与死锁处理方式
• 区分 redo log、undo log、binlog 的职责，并说明两阶段提交的必要性

从表到 B+ 树

聚簇索引决定数据行在哪里

InnoDB 的表数据本身就存放在主键 B+ 树的叶子节点中，这棵树称为聚簇索引。叶子节点不仅保存主键，还保存整行记录；按主键范围扫描时，叶子页之间有双向链表，适合范围查询。

若表没有显式主键，InnoDB 会优先选择第一个所有列都非空的唯一索引；仍没有时，才生成一个隐藏的 6 字节 row id。工程上应显式定义稳定、较短、递增趋势好的主键：它会被复制到每个二级索引的叶子节点中，主键过宽会放大所有索引和二级索引回表成本。

不要把“主键一定要自增”当成绝对规则。 核心是减少随机写和控制索引宽度；时间有序 ID 在分布式系统中同样常见。

二级索引、回表与覆盖索引

普通索引、唯一索引都属于二级索引。其叶子节点保存“二级索引列 + 主键值”，不保存完整行。因此：

SELECT name FROM user WHERE email = 'dev@example.com';

若 email 上有二级索引，InnoDB 先在 email 树找到主键，再回到主键树找 name，这一步叫回表。如果建立 (email, name) 联合索引，name 已在叶子节点内，查询不必回主键树，称为覆盖索引。

-- 索引：KEY idx_status_created_id (status, created_at, id)
SELECT id, created_at
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-08-01'
ORDER BY created_at
LIMIT 50;

这条 SQL 可以利用索引完成筛选、排序和返回列；EXPLAIN 的 Extra 常出现 Using index。但覆盖索引不是越多越好：索引越宽，页能容纳的条目越少，写入、缓存和维护成本越高。应针对高频读路径设计，而不是为所有 SELECT * 补索引。

为什么选择 B+ 树而不是 Hash 或 B 树

B+ 树非叶子节点只存键和子页指针，单页能容纳更多索引项，树高通常只有 3～4 层；一次查找只需少量页访问。所有数据在叶子节点且叶子节点相连，范围查询可以顺序扫描。

Hash 查等值很快，但不维护顺序，无法有效支持 BETWEEN、前缀匹配和 ORDER BY；普通 B 树的数据散落在非叶子和叶子节点，范围遍历不如 B+ 树稳定。InnoDB 的自适应哈希索引是内部优化，不能代替业务索引设计。

索引设计与执行计划

联合索引：等值在前，范围靠后，排序随后验证

联合索引 (a, b, c) 按字典序组织：先按 a，a 相同再按 b，再按 c。最左前缀不是“必须从 a 开始写 SQL”的死规则，而是优化器能否把条件转成连续的索引范围。

实用起点是：高选择性等值条件 → 常用范围条件 → 排序或覆盖列，再用真实执行计划验证。

-- 常见订单列表：租户固定，状态等值，按时间倒序翻页
CREATE INDEX idx_orders_tenant_status_created
    ON orders (tenant_id, status, created_at DESC, id DESC);

SELECT id, amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = 'PAID'
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

tenant_id 放在前面能隔离多租户数据；status 是等值过滤；时间和 id 同时构成稳定的 seek pagination 游标。若 status 只有两三个取值且查询没有 tenant 条件，它单独做第一列往往选择性不足，优化器可能宁愿扫描主键。

常见索引失效，不是“用了函数就必失效”

优化器会基于统计信息估算成本，所谓“失效”通常是无法构造有效索引范围或估算后全表扫描更便宜。重点如下：

不要相信“范围条件之后的列完全不能用”。范围列之后的列通常不能继续缩小连续查找范围，但在部分计划中仍可能用于覆盖、ICP（Index Condition Pushdown）或排序优化；结论以 EXPLAIN ANALYZE 为准。

用 EXPLAIN 定位，而不是猜测

EXPLAIN ANALYZE
SELECT id, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

优先看这些字段：

• key：实际选择的索引；为 NULL 不等于一定有问题，但要确认扫描量。
• type：const / ref / range 往往优于 ALL；它不是性能排序的绝对榜单。
• rows 与 actual rows：估算与真实行数差距大时，检查统计信息、数据倾斜和条件写法。
• Extra：Using filesort 不必然慢，LIMIT 20 的小结果集可能完全可接受；大量 Using temporary 才应重点审视。
• EXPLAIN ANALYZE：显示实际循环、耗时和行数，生产环境需控制频率，避免对重 SQL 额外施压。

优化顺序： 先确认慢 SQL 与调用频率，再看执行计划和数据量，最后修改索引或 SQL。不要为了消灭 filesort 盲目增加宽索引。

事务、MVCC 与隔离级别

ACID 在 InnoDB 中如何落地

• Atomicity（原子性）：未提交修改可借助 undo log 回滚。
• Consistency（一致性）：应用约束、数据库约束、事务与恢复机制共同保证；它不是某一份日志单独实现的。
• Isolation（隔离性）：MVCC 和锁隔离并发事务。
• Durability（持久性）：提交相关 redo log 按策略刷盘，宕机恢复后可重放。

MySQL 默认 REPEATABLE READ（RR）。隔离级别越高不代表一定更安全或更慢；要看业务能接受何种读现象，以及事务持锁时间。

MVCC：读历史版本，不是“不加锁”

每条 InnoDB 聚簇索引记录隐含事务 id 和回滚指针。更新时，新版本覆盖当前记录，旧值经 undo log 串成版本链。普通 SELECT 通过 Read View 判断版本对当前事务是否可见；若当前版本不可见，沿 undo 链找到最近可见版本。这就是快照读。

Read View 可以理解为创建时活跃事务 id 的边界集合：比最小活跃 id 更早提交的版本可见；比下一个待分配 id 更晚的版本不可见；中间部分还要检查事务是否在活跃列表。不要在面试中死背字段名，关键是说明：读的是事务开始时（RR）或本次查询时（RC）已提交的版本。

-- 快照读：通常不加记录锁，按 Read View 读取可见版本
SELECT balance FROM account WHERE id = 1;

-- 当前读：读取最新版本并加锁；用于“读后修改”的正确性
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
UPDATE account SET balance = balance - 100 WHERE id = 1;

INSERT、UPDATE、DELETE 与 SELECT ... FOR UPDATE / LOCK IN SHARE MODE 都是当前读。MVCC 解决的是读写并发中的版本可见性，不会自动解决“先查余额再扣款”的竞态；涉及业务不变量时，应使用条件更新、悲观锁或乐观锁。

-- 推荐：单条原子条件更新，受影响行数就是扣款是否成功的事实来源
UPDATE account
SET balance = balance - ?
WHERE id = ? AND balance >= ?;

int changed = jdbcTemplate.update(
    "UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?",
    amount, accountId, amount
);
if (changed != 1) {
    throw new IllegalStateException("余额不足或账户不存在");
}

这比“先 SELECT 再 UPDATE”少一次网络往返，并让数据库在同一条语句中维护 balance >= amount 的不变量。

锁、死锁与事务边界

Record Lock、Gap Lock、Next-Key Lock

InnoDB 的行锁本质上是索引记录上的锁，不是抽象的“表中某行”。没有合适索引的更新需要扫描并锁更多索引记录，表现上接近锁表，也更容易阻塞。

• Record Lock：锁定一个已有索引记录。
• Gap Lock：锁定两个索引记录之间的间隙，阻止插入，不锁已有记录本身。
• Next-Key Lock：Record Lock + 前面的 Gap Lock；RR 下的锁定范围查询常用它防止幻读。
• 意向锁：表级标记，表明事务准备在行上持有共享/排他锁；不等同于“锁住整张表”。

例如 RR 下：

-- idx_orders_tenant_status_created (tenant_id, status, created_at)
SELECT id FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= '2026-08-01'
FOR UPDATE;

这不是只锁已返回的 id。为了防止另一个事务在这个索引范围插入满足条件的新记录，InnoDB 可能锁定范围内的 next-key。具体范围取决于索引、谓词、隔离级别与执行计划，排障时以 performance_schema.data_locks 和死锁日志为准，不能仅凭 SQL 文本推断。

死锁是正常并发控制的一部分

两个事务形成等待环时，InnoDB 会检测死锁，回滚代价较小的一个并返回 Deadlock found when trying to get lock。这不表示数据库故障；应用必须识别可重试的瞬时失败。

降低死锁概率的实践：

1. 同一业务操作以固定顺序访问资源，例如总按较小 account id 先锁。
2. 事务只包含必要 SQL；不要在事务中 RPC、发 Kafka、调用第三方或等待用户输入。
3. 给更新条件建立恰当索引，缩小扫描和锁定范围。
4. 批量更新拆小批次，控制单事务修改行数。
5. 对死锁与 lock wait timeout 做有限次数、带退避的重试；写操作必须有幂等键。

public final class DeadlockRetry {
    private static final int MAX_ATTEMPTS = 3;

    public static <T> T execute(TransactionalOperation<T> operation) {
        for (int attempt = 1; attempt <= MAX_ATTEMPTS; attempt++) {
            try {
                return operation.run();
            } catch (TransientDatabaseException exception) {
                if (attempt == MAX_ATTEMPTS) {
                    throw exception;
                }
                try {
                    Thread.sleep(20L * attempt);
                } catch (InterruptedException interrupted) {
                    Thread.currentThread().interrupt();
                    throw new IllegalStateException("retry interrupted", interrupted);
                }
            }
        }
        throw new AssertionError("unreachable");
    }

    @FunctionalInterface
    public interface TransactionalOperation<T> {
        T run();
    }
}

真实 Spring 项目中应让每次重试重新开启事务，不能在已经标记 rollback-only 的同一事务里重试。异常分类也必须基于实际 JDBC / Spring 错误码，不能把所有 SQL 异常都重试。

日志、崩溃恢复与复制

redo log、undo log、binlog 各自负责什么

WAL（Write-Ahead Logging）要求数据页落盘前，相关 redo 先持久化。宕机后，InnoDB 用 redo 重放已提交但尚未写入数据页的修改；未提交事务再通过 undo 回滚。undo 不是永久历史库：长事务持有旧 Read View 会阻止 purge 清理版本链，导致 undo 表空间膨胀和查询变慢。

两阶段提交避免主从不一致

InnoDB 的 redo 与 Server 层 binlog 是两套日志。提交时先写 redo 的 prepare，再写 binlog，最后写 redo commit；恢复时根据两者状态决定提交或回滚，避免“主库已经持久化但 binlog 没记录”或“binlog 记录了但引擎未提交”的不一致。

复制场景优先理解 row-based binlog：它记录受影响行，语义更确定；statement-based 记录 SQL，日志可能更小但会受非确定函数和执行环境影响。生产配置与复制延迟、GTID、半同步复制等问题需要结合具体版本和拓扑设计。

面试易错点与排障路径

大 OFFSET 深分页

-- 越往后扫描和丢弃的行越多
SELECT id, created_at FROM orders
WHERE tenant_id = 42
ORDER BY id
LIMIT 1000000, 20;

-- 使用上一页最后一条记录作为游标
SELECT id, created_at FROM orders
WHERE tenant_id = 42 AND id > ?
ORDER BY id
LIMIT 20;

游标分页要求排序键稳定、唯一或附带唯一 tiebreaker；它不适合“直接跳到第 N 页”的后台管理需求。后者可考虑限制页数、延迟关联、异步导出或专用搜索系统。

长事务为什么危险

长事务不只是“占连接”。它会长期持锁、阻塞写入；其 Read View 还会让旧版本不能 purge，导致 undo 膨胀；在主从复制中也可能扩大延迟。Spring 的 @Transactional 应只包围数据库原子操作，切勿包住 HTTP 调用、消息发送和大文件处理。

排查 SQL 慢或锁等待的顺序

1. 先确认慢的是哪类 SQL、频率多高、P95/P99 多大，而不是只看一次偶发日志。
2. 获取绑定真实参数后的 SQL，执行 EXPLAIN ANALYZE，核对扫描行数、索引与排序。
3. 检查数据分布与统计信息；低选择性条件、隐式转换和不匹配的联合索引很常见。
4. 锁等待时查看 performance_schema.data_locks、data_lock_waits 与 SHOW ENGINE INNODB STATUS，定位持锁 SQL 和事务年龄。
5. 改动后灰度验证，观察慢查询、错误率、锁等待、CPU 与复制延迟，保留索引回滚方案。

面试表达框架

Q1：MySQL 的一条查询如何执行？

先区分是否命中索引。InnoDB 通过 B+ 树从根页走到叶子页；若命中二级索引，叶子页先得到主键，再到聚簇索引取整行，这就是回表。若所需列都在二级索引中，可覆盖索引避免回表。优化时我会先用 EXPLAIN ANALYZE 看真实扫描量和计划，而不是只看是否出现 Using filesort。

Q2：MVCC 如何实现可重复读？

InnoDB 更新时用 undo log 维护旧版本链，普通 SELECT 根据 Read View 找到对当前事务可见的版本。RR 下同一事务通常复用首次一致性读建立的 Read View，所以同一行重复读取结果稳定；RC 则每次查询创建 Read View。MVCC 处理快照读，SELECT ... FOR UPDATE 等当前读仍需锁，业务不变量要靠原子条件更新或显式锁保证。

Q3：RR 已有 MVCC，为什么还要 Gap Lock？

MVCC 让普通快照读看见一致版本，但锁定读或更新需要防止另一个事务在查询范围插入新记录。RR 下 InnoDB 通过 next-key lock 锁住索引记录和部分间隙，避免范围条件对应的幻影行出现。锁的实际范围由索引和执行计划决定，因此索引设计同时影响性能与并发。

Q4：redo log 和 binlog 的区别与两阶段提交？

redo 是 InnoDB 的 WAL，用于崩溃恢复；binlog 是 Server 层逻辑变更日志，用于复制和增量恢复。两套日志独立写入时可能出现一套成功、一套失败，所以提交采用 redo prepare → binlog → redo commit 的协调流程，恢复时据此决定事务状态，降低主库数据与复制日志不一致的风险。