MySQL InnoDB、查询优化与事务锁：从慢查询现场到内核治理

MySQL InnoDB 查询、事务与锁工程参考图沿 B+Tree、执行计划、ReadView、锁等待与 redo/binlog 提交链建立从 SQL 现象到内核证据、变更回滚和容量治理的完整诊断路径。高清原图

从一次“加了索引，接口反而更慢”开始

订单列表接口在发布前平均 18 ms，发布后逐渐升到 900 ms。值班同学看到慢日志里的 Using filesort，立即给 status 加了单列索引；几分钟后 CPU 更高、写入延迟也开始抖动。另一个同学为了止血，把查询强制成 FORCE INDEX。当天流量恢复后，计划仍被提示固定在错误索引上，批量更新又因为锁范围扩大把结算任务堵住。

这里至少混在了一起五类问题：

status 只有少量取值，单列索引未必有足够选择性。列表查询真正需要的是“用户 + 状态 + 时间顺序”，不是孤立的状态过滤。新索引改变了优化器的候选计划，也增加了每次写入维护 B+Tree 的成本。

线上只看 EXPLAIN 的估算，没有用可控数据执行 EXPLAIN ANALYZE 对照实际行数和循环次数。更新语句与列表查询共用索引范围，事务持续时间一长，性能问题就会升级为锁等待问题。

架构师处理这类现场，不能止于“再加一个索引”。正确顺序是固定业务查询和数据分布，取得执行证据，理解记录如何落在页和索引中，再决定统计信息、索引、SQL、事务边界或数据架构中的哪一层需要改变。

先搭一个可销毁实验台

以下实验固定使用 MySQL 8.4 LTS 镜像，映射到宿主机 3317，只监听回环地址。镜像小版本会随 8.4 标签更新；需要复现实验报告时，应记录实际镜像摘要，而不是把浮动标签当成永久基线。

先确认本机没有占用端口：

docker version
docker ps --format "table {{.Names}}\t{{.Ports}}"

PowerShell 可以再检查一次：

Get-NetTCPConnection -LocalPort 3317 -ErrorAction SilentlyContinue

创建隔离网络、数据卷和实例。密码仅用于可删除的本地实验；团队环境应由密钥系统注入，不能写进 Compose、Shell 历史或仓库：

docker network create mysql-innodb-lab-net
docker volume create mysql-innodb-lab-data

docker run -d \
  --name mysql-innodb-lab \
  --network mysql-innodb-lab-net \
  -p 127.0.0.1:3317:3306 \
  -e MYSQL_ROOT_PASSWORD='<LOCAL_ROOT_PASSWORD>' \
  -v mysql-innodb-lab-data:/var/lib/mysql \
  mysql:8.4 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_0900_ai_ci \
  --performance-schema=ON

等待 root 账号能够完成一次真实查询：

until docker exec mysql-innodb-lab \
  mysql -uroot -p'<LOCAL_ROOT_PASSWORD>' \
  --connect-timeout=2 -Nse "SELECT 1" >/dev/null 2>&1; do
  sleep 2
done

docker exec mysql-innodb-lab \
  mysql -uroot -p'<LOCAL_ROOT_PASSWORD>' \
  -e "SELECT VERSION(), @@hostname, @@port, @@transaction_isolation;"

预期能看到 8.4.x、容器主机名、容器内端口 3306 和默认隔离级别。mysqladmin ping 即使遇到认证失败也可能只把“服务正在响应”判为成功，因此这里直接执行 SELECT 1；业务账号、权限和数据结构仍要继续逐层验证。

创建独立实验库和低权限账号：

CREATE DATABASE innodb_lab
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'lab_app'@'%' IDENTIFIED BY '<LOCAL_APP_PASSWORD>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON innodb_lab.* TO 'lab_app'@'%';

应用账号不需要 PROCESS、SYSTEM_VARIABLES_ADMIN、REPLICATION CLIENT 或全局 SELECT。诊断能力应交给单独、可审计的只读运维角色；否则一次普通连接泄漏就可能变成全实例配置事故。

准备带倾斜分布的订单表

进入容器客户端：

docker exec -it mysql-innodb-lab \
  mysql -ulab_app -p'<LOCAL_APP_PASSWORD>' innodb_lab

创建表：

CREATE TABLE order_lab (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(40) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  remark VARCHAR(200) NULL,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_lab_order_no (order_no),
  KEY idx_order_lab_user_created (user_id, created_at)
) ENGINE=InnoDB;

用五个十进制位交叉生成 100000 行，不依赖递归深度：

SET @data_anchor = TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY);

INSERT INTO order_lab
  (order_no, user_id, status, amount, remark, created_at, updated_at)
SELECT
  CONCAT('ORD', LPAD(n, 12, '0')),
  10000 + MOD(n, 1000),
  CASE
    WHEN MOD(n, 100) < 90 THEN 'PAID'
    WHEN MOD(n, 100) < 99 THEN 'CANCELLED'
    ELSE 'REFUND'
  END,
  10 + MOD(n, 50000) / 100,
  RPAD(CONCAT('lab-', n), 80, 'x'),
  @data_anchor + INTERVAL n SECOND,
  @data_anchor + INTERVAL n SECOND
FROM (
  SELECT
    a.n + b.n * 10 + c.n * 100 + d.n * 1000 + e.n * 10000 + 1 AS n
  FROM
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
  CROSS JOIN
    (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e
) seed;

验证数据分布：

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT user_id) AS users_total
FROM order_lab;

SELECT status, COUNT(*) AS rows_total
FROM order_lab
GROUP BY status
ORDER BY rows_total DESC;

预期总行数为 100000，状态大致为 PAID=90000、CANCELLED=9000、REFUND=1000。这份倾斜数据会贯穿索引、统计信息和计划实验。

B+Tree 决定的不只是“能不能查到”

InnoDB 默认按页管理数据。B+Tree 的非叶子页保存导航键和子页指针，叶子页保存有序记录；树高较低时，少量页访问就能定位很大的数据集。真正影响工程设计的是叶子记录放了什么，以及每次写入会改动多少棵树。

聚簇索引叶子保存完整行

PRIMARY KEY (id) 同时定义了行的物理组织顺序。按主键范围扫描通常连续；随机 UUID 作为主键则更容易在不同叶子页插入，增加页分裂、缓存离散度和二级索引体积。

如果没有显式主键，InnoDB 会尝试选择第一个所有列均为 NOT NULL 的唯一索引；仍找不到时，会生成隐藏聚簇索引。隐藏主键不能被业务稳定引用，也让二级索引和排障更难解释，因此业务表应显式设计短而稳定的主键。

页分裂不是“用了 UUID 就一定发生事故”，判断要看：

插入键是否大体递增。每行和主键有多宽。写入并发和 Buffer Pool 能否承受离散页访问。

二级索引数量是否把主键副本放大多次。数据保留周期和页空间回收策略。

二级索引叶子携带主键

idx_order_lab_user_created (user_id, created_at) 的叶子记录包含二级键和主键 id。查询只返回 id、user_id、created_at 时，可能直接由二级索引覆盖；再读取 remark 时，就需要拿 id 回到聚簇索引取完整行。

对照两条查询：

EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM order_lab
WHERE user_id = 10042
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN ANALYZE
SELECT id, user_id, created_at, remark
FROM order_lab
WHERE user_id = 10042
ORDER BY created_at DESC
LIMIT 20;

预期两条都使用 idx_order_lab_user_created。第一条更容易出现 covering index scan；第二条需要回表。不要把输出耗时写成团队阈值：容器资源、缓存冷热和数据量都会改变绝对数字，真正应比较的是计划节点、实际行数、循环次数和相同环境下的相对变化。

主键越宽，每个二级索引叶子携带的主键副本也越宽。一个 36 字符 UUID 在六个二级索引中被重复保存，成本不只是一列字符串，而是 Buffer Pool、磁盘、备份、复制和写放大的联动成本。

从 SQL 到实际执行计划

优化器不会“读懂业务意图”。它根据可用索引、统计信息、成本模型、连接顺序和谓词估算选择计划，然后由执行器逐步取行。慢查询诊断要把三类证据分开：

证据	回答的问题	不能单独证明
EXPLAIN	优化器计划怎么执行	实际运行了多久、每个节点实际处理多少行
EXPLAIN ANALYZE	实际执行时间、行数和循环与估算偏差多大	生产全流量下的尾延迟和资源争用
statement digest / APM	一段时间内频率、总耗时、平均或分位延迟	单条计划为何如此选择

MySQL 的 EXPLAIN ANALYZE 会真实执行语句。先在脱敏副本或受控只读查询上使用；不要把可能修改数据的语句直接带到生产验证。

先制造一个错误写法

下面的函数包裹让现有 (user_id, created_at) 索引难以用于时间范围定位：

SET @day_start = DATE(@data_anchor);
SET @next_day = @day_start + INTERVAL 1 DAY;

EXPLAIN ANALYZE
SELECT id, order_no, created_at
FROM order_lab
WHERE user_id = 10042
  AND DATE(created_at) = @day_start
ORDER BY created_at DESC;

改成半开时间区间：

EXPLAIN ANALYZE
SELECT id, order_no, created_at
FROM order_lab
WHERE user_id = 10042
  AND created_at >= @day_start
  AND created_at <  @next_day
ORDER BY created_at DESC;

预期第二条能同时利用联合索引的用户和时间范围，扫描行数更少。若两条都很快，说明实验数据仍小或已在缓存中；结论仍应来自访问路径和实际行数，而不是肉眼可感知的等待。

估算错了，先查统计信息

查看索引基数和持久统计配置：

SHOW INDEX FROM order_lab;

SELECT @@innodb_stats_persistent,
       @@innodb_stats_persistent_sample_pages,
       @@information_schema_stats_expiry;

大批量导入、归档或数据分布剧变后，可以在变更窗口执行：

ANALYZE TABLE order_lab;

ANALYZE TABLE 会更新键分布统计，并可能短暂取得读锁。它不是“慢了就跑”的无风险按钮；大表需要先在同结构副本测时长、观察元数据锁和复制影响。

直方图解决的是无索引列分布估算

status 高度倾斜。先看没有直方图时的估算：

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
FROM order_lab
WHERE status = 'REFUND';

由具备维护权限的诊断账号创建直方图：

ANALYZE TABLE order_lab
  UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

SELECT HISTOGRAM
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'innodb_lab'
  AND TABLE_NAME = 'order_lab'
  AND COLUMN_NAME = 'status'\G

再次执行 EXPLAIN FORMAT=TREE，比较估算行数。MySQL 的优化器统计说明：直方图按需创建，不会随着每次 DML 自动维护，因此数据分布持续变化时会逐渐过期；索引列已有 index dive 时，直方图也未必提供额外收益。

回滚直方图：

ANALYZE TABLE order_lab DROP HISTOGRAM ON status;

直方图不能把全表扫描变成索引定位。它只帮助优化器估算选择率，从而在连接顺序、访问方式等候选计划之间作出更可靠判断。

索引变更必须先证明可回滚

不可见索引：先模拟删除，再真正删除

先添加与列表模型匹配的索引，并明确要求在线 DML：

ALTER TABLE order_lab
  ADD INDEX idx_order_lab_user_status_created
    (user_id, status, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

如果目标版本和操作不支持指定算法或锁级别，语句应失败，而不是静默降级为复制整表。失败本身是保护信号：回到同结构副本评估 INSTANT、INPLACE、COPY、额外空间、MDL 和变更窗口。

验证计划：

EXPLAIN ANALYZE
SELECT id, order_no, status, created_at
FROM order_lab
WHERE user_id = 10042
  AND status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

准备下线索引时，不要直接 DROP INDEX。先隐藏：

ALTER TABLE order_lab
  ALTER INDEX idx_order_lab_user_status_created INVISIBLE;

SHOW INDEX FROM order_lab
WHERE Key_name = 'idx_order_lab_user_status_created';

普通查询会忽略该索引。诊断时可以只对一条语句临时允许它参与计划：

EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
       id, order_no, status, created_at
FROM order_lab
WHERE user_id = 10042
  AND status = 'REFUND'
ORDER BY created_at DESC
LIMIT 20;

INVISIBLE INDEX 的价值是把“删除”拆成可逆和不可逆两个阶段。观察完整业务周期的慢日志、statement digest、报表任务和批处理后，再决定删除；发现退化则立即恢复：

ALTER TABLE order_lab
  ALTER INDEX idx_order_lab_user_status_created VISIBLE;

最终删除仍要单独审批：

ALTER TABLE order_lab
  DROP INDEX idx_order_lab_user_status_created,
  ALGORITHM=INPLACE,
  LOCK=NONE;

Online DDL 不等于无锁

LOCK=NONE 表示 DDL 主体允许并发 DML，不代表完全没有锁。执行开始和结束仍可能等待元数据锁；并发事务越长，DDL 越可能排队，排队中的 DDL 又可能阻塞后续请求。

上线前至少记录：

表行数、数据与索引大小、磁盘剩余空间。是否重建表，临时空间和 redo/binlog 增量。DDL 预计时长、复制延迟和业务低峰窗口。

performance_schema.metadata_locks 中是否已有阻塞者。中止方式、索引恢复方式和旧版本应用兼容性。

MySQL 的Online DDL 性能说明建议先克隆表结构、填充代表性数据并观察操作是否复制行。架构评审中，“支持 Online DDL”只能作为入口，不能代替实际数据量上的演练。

一次 UPDATE 的真实提交链

更新并不是“改页、写 redo、写 binlog、提交”四个串行动作。启用 binary log 时，Server 层与 InnoDB 需要保证 binlog 中可复制的事务和 InnoDB 中已提交的事务一致，因此提交路径包含 prepare 和 commit 两个阶段。

关键边界如下：

undo 保存回滚和旧版本重建所需的信息，长事务会让旧版本更久不能 purge。Buffer Pool 脏页 可以晚于事务提交落到数据文件；崩溃恢复依赖 redo 重放已提交修改。redo prepare 表示 InnoDB 已准备提交，但需要与 binlog 协调最终结果。

binlog 是复制和 PITR 的 Server 层变更记录，不是 InnoDB 数据页恢复日志。redo commit 让 prepared transaction 进入最终提交状态；恢复时可依据已持久化 binlog 的事务标识决定提交或回滚 prepared transaction。

MySQL 的二进制日志说明明确描述了 InnoDB 两阶段提交如何保持 binlog 与 InnoDB 数据文件同步。内部还会用 group commit 合并多个事务的刷盘工作，因此高并发下的吞吐不能用“每个事务固定两次独占 fsync”简单估算。

先读配置，不要在生产直接试丢数据

SELECT @@log_bin,
       @@binlog_format,
       @@sync_binlog,
       @@innodb_flush_log_at_trx_commit;

SHOW BINARY LOG STATUS;

复制场景追求最强持久性时，基线通常是：

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

sync_binlog控制 binlog 同步到磁盘的频率；innodb_flush_log_at_trx_commit 控制 redo 在提交时写入和刷盘的策略。放宽任一参数都可能提高吞吐，但会扩大进程崩溃、操作系统崩溃或断电场景下的丢失窗口。即使都为 1，磁盘控制器谎报 flush、无保护写缓存或虚拟化存储语义不可靠，仍会破坏持久性假设。

反向检查不是把参数改成 0 后拔电，而是先找出承诺是否自相矛盾：

业务声称 RPO=0，参数却允许批量刷盘。参数都是 1，云盘和宿主机却没有持久性保证。binlog 保留时间短于最大恢复窗口或副本最大延迟。

性能压测只测吞吐，没有注入实例异常并核对已确认事务。

任何参数变更都应先记录当前值、负载、提交延迟和恢复结果。回滚动作是恢复原值并重新做故障恢复验证，而不是只把配置文件改回去。

MVCC 与 ReadView：快照不是在 BEGIN 时凭空出现

InnoDB 记录包含事务标识和回滚指针。普通一致性读根据 ReadView 判断当前版本是否可见；不可见时沿 undo 版本链重建旧版本。MVCC 让读写并发，但旧版本并非免费：长事务会延长 undo 保留、增加版本链遍历和 purge 压力。

在默认 REPEATABLE READ 下，事务内第一次一致性读建立快照，后续一致性读复用该快照；普通 BEGIN 本身不必然建立 ReadView。START TRANSACTION WITH CONSISTENT SNAPSHOT 才显式请求一致性快照。在 READ COMMITTED 下，每次一致性读都会取得新的快照。一致性非锁定读还说明，同一事务自己的修改对自己可见，因此事务可能观察到一个并非数据库曾经整体存在过的混合状态。

RR 反向实验：BEGIN 之后、首次 SELECT 之前的提交可见

准备账户：

CREATE TABLE mvcc_account (
  id BIGINT PRIMARY KEY,
  balance INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO mvcc_account VALUES (1, 100), (2, 200);

Session A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 暂时不要 SELECT

Session B：

UPDATE mvcc_account SET balance = 110 WHERE id = 1;
COMMIT;

回到 Session A，执行第一次一致性读：

SELECT balance FROM mvcc_account WHERE id = 1;

预期结果是 110，证明“执行 BEGIN 就固定快照”是错误心智模型。此时快照才由第一次一致性读建立。

Session B 再更新：

UPDATE mvcc_account SET balance = 120 WHERE id = 1;
COMMIT;

Session A 再次普通读取：

SELECT balance FROM mvcc_account WHERE id = 1;

预期仍为 110。当前读则不同：

SELECT balance FROM mvcc_account WHERE id = 1 FOR UPDATE;

预期读取最新已提交版本 120 并取得锁。结束实验：

ROLLBACK;

RC 对照实验：每次一致性读都刷新快照

Session A：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM mvcc_account WHERE id = 1;

Session B：

UPDATE mvcc_account SET balance = 130 WHERE id = 1;
COMMIT;

Session A：

SELECT balance FROM mvcc_account WHERE id = 1;
ROLLBACK;

第二次读取预期为 130。RC 降低了部分间隙锁影响并让读更“新”，代价是同一事务内两次普通查询可能得到不同结果。选隔离级别要从业务不变量出发：报表展示、库存扣减、账务对账和后台批处理需要的读语义并不相同。

行锁、间隙锁与 next-key lock

InnoDB 所谓“行锁”实际加在索引记录上。唯一索引等值命中通常能把范围收得很小；非唯一索引、范围条件或缺失索引可能锁住更多记录和间隙。REPEATABLE READ 的 next-key locking 把记录锁与记录前间隙结合，用于阻止范围内出现幻影记录。

RR 负向实验：范围锁阻止区间插入

准备数据：

CREATE TABLE lock_range_lab (
  id INT PRIMARY KEY,
  note VARCHAR(40) NOT NULL
) ENGINE=InnoDB;

INSERT INTO lock_range_lab VALUES (10, 'left'), (20, 'right');

Session A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT *
FROM lock_range_lab
WHERE id BETWEEN 10 AND 20
FOR UPDATE;

Session B：

SET SESSION innodb_lock_wait_timeout = 3;
START TRANSACTION;
INSERT INTO lock_range_lab VALUES (15, 'middle');

Session B 预期等待后收到：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

不要继续复用 Session B 的事务。默认情况下，锁等待超时只回滚当前语句，不保证整个事务都回滚；显式清理两端：

-- Session B
ROLLBACK;

-- Session A
ROLLBACK;

RC 正向对照：减少普通范围的间隙锁

Session A：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT *
FROM lock_range_lab
WHERE id BETWEEN 10 AND 20
FOR UPDATE;

Session B：

SET SESSION innodb_lock_wait_timeout = 3;
START TRANSACTION;
INSERT INTO lock_range_lab VALUES (15, 'middle');
COMMIT;

在没有唯一性或外键检查等额外锁需求时，插入预期成功。Session A 最后 ROLLBACK，并删除插入行：

ROLLBACK;
DELETE FROM lock_range_lab WHERE id = 15;

这不意味着 RC 可以治愈所有锁问题。热点行更新、资源访问顺序不一致、无索引更新和长事务，在任何常用隔离级别下都可能制造阻塞或死锁。

锁等待必须留下可解释证据

仅看到应用超时，不能直接判断数据库锁。先制造一条明确等待链。

Session A：

START TRANSACTION;
UPDATE mvcc_account SET balance = balance + 1 WHERE id = 1;
-- 保持事务不提交

Session B：

SET SESSION innodb_lock_wait_timeout = 20;
START TRANSACTION;
UPDATE mvcc_account SET balance = balance + 10 WHERE id = 1;
-- 此语句等待

第三个诊断会话执行：

SELECT
  req.ENGINE_TRANSACTION_ID AS waiting_trx,
  blk.ENGINE_TRANSACTION_ID AS blocking_trx,
  req.OBJECT_SCHEMA,
  req.OBJECT_NAME,
  req.INDEX_NAME,
  req.LOCK_TYPE AS waiting_type,
  req.LOCK_MODE AS waiting_mode,
  req.LOCK_DATA AS waiting_data,
  blk.LOCK_MODE AS blocking_mode,
  blk.LOCK_DATA AS blocking_data
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks req
  ON req.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
 AND req.ENGINE = w.ENGINE
JOIN performance_schema.data_locks blk
  ON blk.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
 AND blk.ENGINE = w.ENGINE\G

预期能看到等待事务、阻塞事务、mvcc_account、PRIMARY 和记录锁信息。MySQL 8.4 的 Performance Schema 锁表分别提供已持有/请求的锁以及等待边关系，适合构建阻塞链，而不是只看一张进程列表猜测。

清理顺序：

-- Session A
ROLLBACK;

-- Session B 的 UPDATE 随后可能完成，不要误提交
ROLLBACK;

生产止血前先确认阻塞者是不是关键事务。直接 KILL 可能触发大事务回滚，回滚期间仍占资源和锁；更稳妥的顺序是限制新流量、识别事务所有者、评估回滚成本，再由有审计权限的运维角色执行。

死锁不是调大超时就能解决

创建两行资源：

UPDATE mvcc_account SET balance = 100 WHERE id = 1;
UPDATE mvcc_account SET balance = 200 WHERE id = 2;

按相反顺序加锁：

Session A：

START TRANSACTION;
UPDATE mvcc_account SET balance = balance - 10 WHERE id = 1;

Session B：

START TRANSACTION;
UPDATE mvcc_account SET balance = balance - 20 WHERE id = 2;

Session A 再执行，命令会等待：

UPDATE mvcc_account SET balance = balance + 10 WHERE id = 2;

趁 A 等待时，Session B 执行：

UPDATE mvcc_account SET balance = balance + 20 WHERE id = 1;

其中一个会话预期收到：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

诊断会话查看最近一次死锁：

SHOW ENGINE INNODB STATUS\G

重点读取 LATEST DETECTED DEADLOCK 中的事务、SQL、已持有锁、请求锁和被回滚事务。InnoDB 死锁说明要求应用始终准备重试事务；频繁死锁才说明事务结构、索引或资源顺序需要治理。

两个会话都执行 ROLLBACK，然后按统一顺序重试：

START TRANSACTION;
SELECT id
FROM mvcc_account
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE mvcc_account SET balance = balance - 10 WHERE id = 1;
UPDATE mvcc_account SET balance = balance + 10 WHERE id = 2;
COMMIT;

正向设计不是“永远没有死锁”，而是：

所有调用方按同一稳定顺序取得资源。事务足够短，不在持锁期间调用 RPC、等待人工输入或处理大文件。更新谓词有索引，锁范围可解释。

捕获 1213 后重试整个事务，设置次数上限和退避，并保留业务幂等键。死锁率、重试成功率和最终失败率进入监控，而不是被框架静默吞掉。

锁等待超时 1205 与死锁 1213 不同。默认 innodb_lock_wait_timeout 触发时只回滚当前语句；InnoDB 错误处理明确了这一语义。应用不显式回滚就继续执行，可能提交一个“前半段成功、超时语句失败、后半段又成功”的不完整事务。

从单条 SQL 建立性能基线

性能优化没有基线，就只剩感觉。基线至少固定六个维度：

维度	应记录的证据	常见误判
业务负载	查询模板、读写比、并发、数据分布	用一条主键查询代表全部业务
延迟	p50、p95、p99、超时率	只看平均值
吞吐	TPS/QPS、提交速率	TPS 上升就认为系统更健康
执行效率	rows examined / rows sent、临时表、排序、计划	“走了索引”就结束分析
InnoDB	Buffer Pool、redo、脏页、历史链、锁等待	命中率高就认为没有 IO 问题
资源与容量	CPU、IOPS、await、磁盘、连接、增长率	用测试机参数直接套生产

先记录版本、数据和配置指纹

SELECT VERSION(), @@hostname, @@port;

SELECT COUNT(*) AS rows_total,
       MIN(created_at) AS min_created_at,
       MAX(created_at) AS max_created_at
FROM order_lab;

SELECT @@innodb_buffer_pool_size,
       @@innodb_redo_log_capacity,
       @@innodb_flush_log_at_trx_commit,
       @@sync_binlog,
       @@transaction_isolation;

不记录数据量和分布，计划对比没有意义；不记录版本和参数，回归失败后无法复现。

用同一负载比较，不追求单次漂亮数字

安装 MySQL 客户端工具的机器可以用 mysqlslap 做轻量对照。它不是生产容量模型，只用于验证同一环境中索引或 SQL 改动的相对影响：

mysqlslap \
  --host=127.0.0.1 \
  --port=3317 \
  --user=lab_app \
  --password \
  --create-schema=innodb_lab \
  --concurrency=1,8,32 \
  --iterations=3 \
  --number-of-queries=3000 \
  --query="SELECT id, order_no, created_at FROM order_lab WHERE user_id=10042 ORDER BY created_at DESC LIMIT 20"

密码由交互提示输入。不要把密码拼在命令行参数、CI 日志或截图中。记录每个并发档的平均、最小、最大时间，同时从应用压测或 APM 取得 p95/p99；mysqlslap 的平均值不能替代尾延迟。

查看高成本 digest：

SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_CREATED_TMP_DISK_TABLES,
  SUM_SORT_ROWS
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'innodb_lab'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

对 Buffer Pool 用增量而不是启动以来的累计比率：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_os_log_written',
  'Innodb_history_list_length',
  'Innodb_row_lock_waits',
  'Innodb_row_lock_time'
);

压测前后各采一次，计算增量并与同一时间窗的吞吐、延迟和系统 IO 对齐。全局命中率可能长期很好，却掩盖某个新查询持续读取冷页；脏页比例正常，也不能证明存储没有周期性 flush 抖动。

变更验收要有反向指标和回滚门槛

索引或参数变更至少定义：

目标：例如相同数据和并发下，核心查询 p95 降低，超时率不升。护栏：写入 p99、redo 增量、磁盘空间、复制延迟和锁等待不得突破业务 SLO。观察窗：覆盖流量高峰、批处理和至少一个低频任务周期。

回滚门槛：计划退化、写入成本显著上升、复制延迟持续扩大或磁盘水位逼近容量线时立即回退。回滚动作：隐藏新索引、恢复原 SQL/参数、停止灰度、复核旧计划，而不是临时再加 hint。

70%、80% 这类值只能是团队根据增长速率、扩容交付周期和故障余量推导出的门槛，不能当成 MySQL 通用真理。容量决策应回答：按当前日增长多久到线，索引增加多少存储，备份与恢复窗口是否同步增长，扩容或归档需要多久。

从现象回到机制的诊断树

常见证据组合：

现象	需要同时看到的证据	优先动作
查询忽快忽慢	计划变化、估算偏差、缓存冷热、数据倾斜	固定查询和数据快照，再更新统计或调整索引
CPU 高、IO 不高	高频扫描、排序聚合、表达式计算、热点锁自旋	按 digest 总耗时排序，不先调 Buffer Pool
IO await 高	物理读增量、全表扫描、回表、刷脏或备份重叠	区分读 IO 与写回，避免“一律加内存”
UPDATE 卡住	等待链、索引、持锁事务、锁模式	先处理阻塞链，再缩短事务或修正定位索引
提交延迟抖动	redo/binlog 刷盘、存储延迟、group commit 批次	对齐提交延迟与磁盘，不轻易放宽持久性
purge 落后	长事务、history list、undo 空间	找最老事务并治理批处理边界

项目里的事务边界怎么落地

以转账或库存扣减为例，数据库事务只包围需要原子完成的本地写入：

请求校验与幂等检查
  → 开启数据库事务
  → 按稳定顺序锁定必要记录
  → 条件更新并检查影响行数
  → 写业务事件/outbox
  → 提交数据库事务
  → 事务外发送消息或调用远程服务

不要把 HTTP 调用、对象存储上传、消息确认、复杂 JSON 序列化或人工审批放在持锁事务里。远程调用失败要依赖幂等、outbox、补偿和重试，而不是无限延长数据库事务。

应用必须区分错误：

错误	处理原则
1213 死锁	整个事务回滚后有限重试，保持幂等
1205 锁等待超时	显式回滚整个业务事务；查阻塞链，不只重试单条语句
连接断开 / 提交结果未知	通过业务幂等键或事务状态查询确认，不能盲目重复扣款
唯一键冲突	区分幂等命中和真实业务冲突
乐观锁影响行数为 0	重新读取状态并按业务规则决策

连接池的 autoCommit、事务隔离、只读标记和超时必须显式配置并在归还连接时复位。否则某个请求留下的 session 状态会污染下一位借用者。数据库 innodb_lock_wait_timeout 也不能替代应用请求超时：应用先超时离开，而数据库语句仍在执行，会形成“客户端已经失败、数据库仍持锁”的幽灵负载。

权限、敏感数据与诊断成本

性能诊断本身有数据边界：

慢日志、general log、events_statements_* 和进程信息可能包含表名、租户标识、SQL 注释甚至业务参数。PROCESS、SELECT on performance_schema、错误日志和云监控权限应分角色授予并留审计。APM SQL 采集要归一化参数，禁止把身份证号、手机号、token 和密钥作为 SQL 注释或标签。

EXPLAIN ANALYZE 会执行查询并消耗真实资源；大扫描应在脱敏副本或限流会话运行。ANALYZE TABLE、索引构建和直方图更新都会消耗 CPU、IO 或锁资源，应纳入变更窗口。导出诊断证据前清理业务字面量、主机名、账号和拓扑；工单附件同样属于敏感数据载体。

一套可维护的角色模型通常至少分为：

角色	能力	明确禁止
应用读写账号	指定库表 DML	全局权限、DDL、诊断管理权限
migration 账号	受审批的 DDL 与版本迁移	常驻应用连接、跨库任意操作
诊断只读账号	必要的 Performance Schema、sys 与状态读取	修改全局参数、业务 DML
平台运维账号	参数、备份、恢复、故障处置	个人长期共享、绕过审计

生产连接配置还应强制 TLS、校验证书和主机身份。GUI 客户端保存密码虽方便，却会扩大终端失窃和误连风险；生产连接使用只读账号、醒目命名和独立颜色仍只是最后一道提示，不能替代网络隔离、审批和最小权限。

架构师如何做取舍

索引还是缓存

索引适合稳定、高频、强一致的定位和排序；缓存适合可定义失效语义、热点明显且允许额外一致性复杂度的读取。数据库计划错误不能靠缓存掩盖，否则缓存击穿时问题会集中爆发。

RC 还是 RR

需要事务内一致快照、可接受更多范围锁治理时，RR 更自然。更重视读到最新已提交版本、希望减少普通 gap locking 影响时，可评估 RC。无论选择哪个，都要用真实并发实验验证扣减、幂等、批处理和报表语义。

隔离级别是系统契约，不能由某个服务私自修改后不记录。

悲观锁还是乐观锁

冲突少、重试便宜：version 条件更新通常更经济。冲突高、必须串行决定：SELECT ... FOR UPDATE 更直接，但要缩短事务并稳定加锁顺序。热点计数器、余额和库存不能只争论锁类型，还要评估分片、排队、批量合并或专用服务。

继续优化单库还是改变数据架构

先证明瓶颈确实来自单实例写入、容量或维护窗口，而不是坏 SQL、冗余索引、长事务和归档缺失。分库分表、缓存和搜索引擎都会引入新的数据一致性、回滚、审计与成本问题。单库治理没做完就扩展，通常只是把不可解释问题复制到更多节点。

团队治理闭环

成熟团队不会依赖某位 DBA 临场救火，而是把证据和回滚固化到流程：

SQL 评审记录查询模型、数据量、选择性、预期返回行数和事务边界。核心 SQL 在代表性数据上保存 EXPLAIN ANALYZE，并把计划变化纳入版本回归。新索引说明服务哪些 digest、预计空间和写放大、观察周期与下线条件。

大表 DDL 在同结构副本演练，记录算法、锁、耗时、临时空间和复制影响。锁事故保存等待链、事务所有者和索引证据，复盘资源顺序与超时语义。参数变更绑定 SLO、负载、故障演练和恢复动作，不接受“网上推荐值”。

月度检查长事务、无效索引、全表扫描、慢 digest、容量增长和权限漂移。每次重大版本升级重跑计划、锁、DDL、备份恢复和驱动兼容实验。

团队职责也要清晰：研发负责 SQL、幂等和事务边界；DBA 或数据库平台负责实例参数、备份恢复和诊断能力；SRE 负责 SLO、告警与故障演练；安全团队负责权限、审计和敏感数据；架构师负责让这些约束在容量、成本和演进路径上不互相冲突。

实验清理与回滚

先在客户端删除实验对象和账号：

DROP DATABASE IF EXISTS innodb_lab;
DROP USER IF EXISTS 'lab_app'@'%';

退出客户端后删除容器、数据卷和网络：

docker rm -f mysql-innodb-lab
docker volume rm mysql-innodb-lab-data
docker network rm mysql-innodb-lab-net

检查没有残留：

docker ps -a --filter name=mysql-innodb-lab
docker volume ls --filter name=mysql-innodb-lab-data
docker network ls --filter name=mysql-innodb-lab-net

空输出或仅表头才表示清理完成。若数据卷删除失败，先确认是否仍被其他容器引用，不要用批量删除命令误伤其他项目。

查询与索引：

核心查询固定了数据分布、返回列、排序和分页方式。EXPLAIN ANALYZE 的估算与实际行数偏差已解释。联合索引服务明确查询模型，主键宽度和二级索引放大已评估。

统计信息和直方图有更新时机、权限和回退动作。删除索引前先经过不可见观察期，并覆盖低频任务周期。Online DDL 已在同结构副本验证算法、MDL、空间和复制影响。

事务与锁：

明确普通读、锁定读和写操作各自需要的数据新鲜度。RR 的快照建立时机与 RC 每次刷新语义已用双会话验证。更新谓词命中稳定索引，资源按统一顺序加锁。

应用区分 1205 与 1213，能回滚并有限重试完整事务。锁等待能从 data_lock_waits 追到阻塞事务、索引和业务所有者。事务内没有 RPC、人工等待、文件处理或无限批量操作。

持久性与性能：

redo prepare、binlog 持久化、redo commit 与脏页后台刷新边界已讲清。sync_binlog、innodb_flush_log_at_trx_commit 与业务 RPO 一致。基线固定版本、数据、并发、读写比和配置指纹。

同时观察 p95/p99、吞吐、扫描行、锁、redo、Buffer Pool、IO 和容量。任何调优都有护栏、观察窗、回滚门槛和可执行恢复动作。

权限与治理：

应用、migration、诊断和平台账号分离并定期复核。生产连接启用 TLS，凭证不进入命令行、日志、截图和仓库。慢日志、Performance Schema、APM 与工单附件完成敏感数据治理。

容量阈值由增长率、扩容周期和故障余量推导，而非照抄固定百分比。索引、SQL、参数和隔离级别变更都能找到责任人、证据和回滚记录。