MySQL 开发篇

MySQL 开发篇技术参考图把 SQL、索引、事务、锁、MVCC、执行计划、慢 SQL、分页、连接池和容量治理串成一条可验证的开发闭环。高清原图

本文范围

这篇只讲后端开发怎么用好 MySQL，不讲 MySQL 怎么安装、怎么搭主从、怎么备份恢复，也不展开部署巡检。你可以把它当成一份数据库开发带练：先把表和 SQL 写稳，再学会看执行计划，然后处理事务、锁等待、分页、慢 SQL 和连接池，最后把这些动作沉淀成团队可复用的治理模板。

本文覆盖这些内容：

• SQL 写法：查询、更新、批量、聚合、排序、分页、幂等写入。
• 表设计：主键、字段类型、唯一约束、状态字段、时间字段、软删除、审计字段。
• 索引：B+Tree、组合索引、覆盖索引、索引失效、索引治理。
• 执行计划：EXPLAIN、EXPLAIN ANALYZE、扫描行数、回表、排序、临时表。
• 事务和锁：事务边界、MVCC、快照读、当前读、行锁、间隙锁、死锁。
• 慢 SQL 闭环：发现、复现、解释、优化、验证、回归。
• 落地工程深水区：索引治理、事务边界、锁等待、连接池、容量增长、慢 SQL 闭环。

本文不覆盖这些内容：

• MySQL 安装、目录规划、端口、防火墙、主从复制、备份恢复和高可用。
• 分库分表平台、读写分离代理、数据库迁移平台的搭建。
• DBA 级参数调优大清单。开发侧会讲需要理解的配置边界，但不会把本文写成运维手册。

版本基线

截至 2026-07-10，MySQL 官方文档和 EOL 公告已经把 9.7 列为新的 LTS 线，8.4 仍是很多团队迁移时更稳妥的长期支持基线。MySQL 8.0 在 2026 年 4 月随 8.0.46 进入 EOL，存量系统要安排升级评估，不要把 8.0 行为继续当成新项目默认标准。本文示例以 MySQL 8.4+ 的能力为基线，重点使用 InnoDB、EXPLAIN、EXPLAIN ANALYZE、performance_schema 等开发排查能力。

前置条件

先准备一个测试库。这里不是教安装，只是假设你已经能连接到一个测试 MySQL 实例：

CREATE DATABASE IF NOT EXISTS shop_demo
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE shop_demo;

后面所有 SQL 都围绕订单场景来讲。不要小看这个例子，真实项目里大多数问题就是列表页、详情页、状态流转、批量任务和报表查询互相挤在一张表上。

先建一套能反复实验的表

先把表建出来。这里故意不追求复杂，而是让你能在一套结构里观察索引、事务、分页和慢 SQL 的表现。

DROP TABLE IF EXISTS order_item;
DROP TABLE IF EXISTS user_order;
DROP TABLE IF EXISTS user_account;

CREATE TABLE user_account (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '内部主键',
  user_no VARCHAR(32) NOT NULL COMMENT '业务用户编号',
  mobile VARCHAR(32) NOT NULL COMMENT '手机号，示例中不放真实号码',
  nickname VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1正常 2冻结',
  version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_no (user_no),
  UNIQUE KEY uk_mobile (mobile)
) ENGINE=InnoDB COMMENT='用户账户';

CREATE TABLE user_order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '内部主键',
  order_no VARCHAR(40) NOT NULL COMMENT '业务订单号',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  status TINYINT UNSIGNED NOT NULL COMMENT '10待支付 20已支付 30已取消',
  total_amount DECIMAL(18,2) NOT NULL COMMENT '订单金额',
  pay_at DATETIME(3) NULL COMMENT '支付时间',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0未删除 1已删除',
  version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status_created (user_id, status, created_at DESC, id DESC),
  KEY idx_status_created (status, created_at DESC, id DESC)
) ENGINE=InnoDB COMMENT='用户订单';

CREATE TABLE order_item (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  sku_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  sale_price DECIMAL(18,2) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_sku (order_id, sku_id),
  KEY idx_sku_order (sku_id, order_id)
) ENGINE=InnoDB COMMENT='订单明细';

验证表结构时，不要只看建表有没有成功，要看字符集、主键、唯一键和组合索引是否符合预期：

SHOW CREATE TABLE user_order\G
SHOW INDEX FROM user_order;

如果 SHOW INDEX 里看不到 idx_user_status_created，后面的列表页查询就没有正确的访问路径。生产里很多慢查询不是因为开发不会 SQL，而是建表阶段没有把“这个表主要怎么被访问”讲清楚。

从 SQL 到 InnoDB：先把完整链路看清楚

后面会讲很多具体做法，但你要先把一条 SQL 在 MySQL 里的行走路线记住。开发侧排查慢、锁、事务和索引问题，本质上都是在这条链路上找证据。

这里有几个关键判断：

• EXPLAIN 看到的是优化器选择的访问路径，不是 InnoDB 页内真实记录长什么样。
• 二级索引只保存二级索引列和主键值；如果查询列不在索引里，就要回到聚簇索引取完整行。
• 普通一致性读要看 MVCC 可见性；UPDATE、DELETE、SELECT ... FOR UPDATE 这类当前读还会涉及锁。
• 写入不是只改内存里的行，还要生成 undo、写 redo，并在提交链路里和 binlog 配合保证崩溃恢复与复制一致性。

最小验证入口可以这样准备：

EXPLAIN FORMAT=TREE
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

SHOW VARIABLES LIKE 'optimizer_switch';
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW ENGINE INNODB STATUS\G

optimizer_switch 能帮助你确认 ICP、MRR 等优化是否默认开启；transaction_isolation 决定当前会话默认隔离级别；SHOW ENGINE INNODB STATUS 则是排查锁、事务和最近死锁时绕不开的入口。不要把这些命令留到事故现场才第一次用。

InnoDB 页、Buffer Pool、脏页和检查点

场景说明：开发排查慢 SQL 时经常只看索引，却忽略了“读到页”和“刷回页”的成本。InnoDB 不是一行一行从磁盘读，它以页为基本单位组织数据；常规数据页通常是 16KB。B+Tree 的叶子页保存记录或索引条目，Buffer Pool 缓存这些页，写入先改内存页并产生 redo，之后再由后台刷脏页。

这条链路可以这样理解：

最小取证入口：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW ENGINE INNODB STATUS\G

怎么读结果：

• Innodb_buffer_pool_reads 增长快，说明很多页没有命中缓存，需要读盘。
• Innodb_buffer_pool_pages_dirty 长时间偏高，写入压力或刷盘能力可能跟不上。
• SHOW ENGINE INNODB STATUS 里的 log sequence、flushed up to、last checkpoint 可以帮助判断 checkpoint 是否被写入压力拖住。

生产建议：开发侧不要直接替 DBA 调参数，但要知道哪些现象不是“再加个索引”能解决的。大批量写入、归档、修复脚本会制造脏页和 redo 压力；如果这些任务和在线接口共享实例，就要用批次、限速、维护窗口和回滚点治理。

SQL 开发先守住几条底线

先说一个常见现场：接口写完以后，测试环境数据少，所有 SQL 都很快；上线后数据过百万，列表页开始慢，导出任务拖住连接池，更新语句偶尔锁一片。这个时候再补索引，往往已经是救火。

开发 SQL 时先守住几条底线。

查询只拿需要的列

不要在核心接口里写 SELECT *。字段越多，网络传输、回表、对象映射和序列化成本越高，组合索引也更难覆盖。

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

验证方式：

EXPLAIN
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

重点看 key 是否命中 idx_user_status_created，rows 是否明显小于表总行数，Extra 是否没有出现明显的 Using filesort。如果数据量很少，rows 的参考价值会下降，所以核心 SQL 要在接近生产的数据分布下验证。

WHERE 条件要让索引能工作

索引列上做函数、隐式类型转换、左模糊，常常会破坏访问路径。

不推荐：

SELECT id, order_no
FROM user_order
WHERE DATE(created_at) = '2026-07-09';

推荐：

SELECT id, order_no
FROM user_order
WHERE created_at >= '2026-07-09 00:00:00'
  AND created_at <  '2026-07-10 00:00:00';

这里不是为了写法好看，而是让 B+Tree 能按有序范围扫描。生产建议是：时间查询统一用半开区间，前端传日期时由服务端转换成开始和结束时间，禁止把函数包在索引列外面。

更新语句必须带上业务约束

只按 id 更新状态，很容易把旧请求覆盖到新状态上。订单从待支付变成已支付时，应该带上当前状态：

UPDATE user_order
SET status = 20,
    pay_at = NOW(3),
    version = version + 1
WHERE order_no = 'ORD202607090001'
  AND status = 10;

验证方式：

SELECT ROW_COUNT() AS affected_rows;

如果 affected_rows = 1，说明状态流转成功。如果是 0，不是数据库异常，而是业务状态已经不满足条件。接口应该返回“订单状态已变化”或进入幂等查询，而不是盲目重试同一条更新。

批量写入要控制批次

批量任务最容易把数据库当成本地集合用。一次事务写几万行、每行先查再写、失败后全部回滚，这些都很危险。

推荐把批量导入拆成小批次：

INSERT INTO order_item (order_id, sku_id, quantity, sale_price)
VALUES
  (100001, 200001, 1, 99.00),
  (100001, 200002, 2, 19.90)
ON DUPLICATE KEY UPDATE
  quantity = VALUES(quantity),
  sale_price = VALUES(sale_price);

常见坑是以为 ON DUPLICATE KEY UPDATE 天然幂等。它只是在唯一键冲突时执行更新，业务是否允许覆盖，要看你的业务规则。比如订单明细已经出库后，继续覆盖数量就是事故。

生产建议：批量任务要有批次号、执行进度、失败明细、重试策略和限流参数。批次大小不要写死，先从 200 到 1000 行一批压测，再按连接池、锁等待和 redo 压力调整。

表设计：把业务事实和查询路径提前说清楚

表设计不是字段清单。你要先回答：这张表表达什么业务事实，唯一性是什么，谁会查它，谁会改它，数据会长到多大，历史数据怎么处理。

主键和业务唯一键分开

内部主键用来支撑 InnoDB 聚簇索引和表内关联，业务唯一键用来表达业务事实。不要把可变业务字段直接当主键。

推荐：

PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no)

这样做的好处是：

• id 短、稳定，适合做聚簇索引。
• order_no 能防重复提交，适合做幂等查询。
• 业务编号规则变化时，不影响内部关联结构。

常见坑是只靠应用层先查再插防重复。并发下两个请求同时查不到，然后同时插入，就会出现重复数据。最终兜底必须靠唯一约束。

字段类型要服务比较、排序和存储成本

几个高频规则要固定下来：

场景	推荐	不推荐	原因
金额	DECIMAL(18,2)	DOUBLE	避免浮点误差
状态	TINYINT UNSIGNED	随意字符串	节省空间，便于索引和约束
创建时间	DATETIME(3)	字符串时间	支持范围查询和排序
逻辑删除	TINYINT + 查询约束	只靠物理删除	便于恢复和审计，但要控制索引代价
业务编号	VARCHAR(32/40)	超长无边界	控制索引长度

验证方式不是看 DDL 是否能执行，而是看典型查询是否可用索引：

EXPLAIN
SELECT id, order_no
FROM user_order
WHERE order_no = 'ORD202607090001';

如果 order_no 是字符串，应用传参也必须是字符串。字段类型和参数类型不一致时，可能发生隐式转换，让索引使用效果变差。

NULL 要有明确语义

NULL 不是“空字符串”，也不是“默认值”。它表示未知或不适用。比如 pay_at 在未支付时可以是 NULL，但 nickname 这类展示字段就不建议让它到处传播 NULL。

推荐：

pay_at DATETIME(3) NULL COMMENT '支付时间',
nickname VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称'

常见坑：

• 业务代码把 NULL、空串、0 混着用，查询条件越来越复杂。
• 唯一索引字段允许 NULL，导致唯一性语义和业务预期不一致。
• 报表统计时忘记 NULL 的三值逻辑，结果不准。

生产建议：核心业务唯一键尽量 NOT NULL。确实允许未知值时，接口、数据库字段和统计 SQL 要统一语义。

索引设计：先定查询路径，再定列顺序

索引不是“哪个字段常查就给哪个字段加一个”。真实项目里，索引要服务稳定的查询路径：过滤条件、排序方式、分页方式、返回列、写入成本要一起看。

B+Tree、聚簇索引和二级索引

InnoDB 表的数据不是散着放的。以默认行格式和常规 InnoDB 表为例，数据页通常按 16KB 组织，页里放记录，页之间通过 B+Tree 结构组织起来。开发不用背页头每个字段，但要知道两件事：

• 主键索引就是聚簇索引，叶子节点保存完整行数据。
• 二级索引叶子节点保存二级索引列和主键值，查到后可能还要按主键回表。

先把查询路径画出来，很多“为什么加了索引还慢”的问题就不会只停在 SQL 文本上：

设计评审时，这张图用来追问三个问题：列表页是否真的需要返回所有列，当前组合索引能不能覆盖高频查询，回表行数有没有被 WHERE 和 LIMIT 控住。排障时则先看 EXPLAIN 的 key、rows、Extra，再结合慢日志样本判断是“索引没走上”，还是“走了二级索引但回表太多”。

所以这条查询如果只返回索引里已有的列：

SELECT user_id, status, created_at, id
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

它更容易形成覆盖索引访问。验证时看 Extra 里是否出现 Using index：

EXPLAIN
SELECT user_id, status, created_at, id
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

如果你多查一个不在索引里的 total_amount，InnoDB 需要根据二级索引里的主键回到聚簇索引拿完整行。这不是一定不能接受，关键看回表次数。如果回表 20 次没问题，如果先扫 50 万条再回表筛选，接口就会慢得很扎实。

ICP 和 MRR：它们解决的不是同一个问题

ICP 是 Index Condition Pushdown，能把部分只依赖索引列的过滤条件下推到存储引擎层，减少回表。你可以先确认开关：

SHOW VARIABLES LIKE 'optimizer_switch';

如果 optimizer_switch 中 index_condition_pushdown=on，符合条件的查询可能在 EXPLAIN 的 Extra 中看到 Using index condition。它的意义是：先在二级索引层过滤掉一部分记录，少回聚簇索引。

MRR 是 Multi-Range Read，重点是减少二级索引回表时的随机访问成本。它会把满足条件的索引元组按数据行 ID 顺序组织，再批量回表，让访问更接近顺序读。它不保证每条 SQL 都更快，优化器会按成本判断。

开发侧要记住：覆盖索引、ICP、MRR 都是在降低访问成本，但它们不是业务建模的替代品。查询范围无限、排序方式混乱、返回列不断膨胀时，再多优化器技巧也只是延缓问题爆发。

列表页索引怎么设计

场景：用户订单列表，按用户、状态查询，按创建时间倒序分页。

SQL：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

索引：

CREATE INDEX idx_user_status_created
ON user_order (user_id, status, created_at DESC, id DESC);

为什么列顺序这样排：

• user_id 是强过滤主轴，先缩小到一个用户。
• status 是等值条件，继续缩小范围。
• created_at DESC, id DESC 同时服务排序和分页。
• id 放在末尾用于同一时间下的稳定排序，也方便游标分页。

验证：

EXPLAIN ANALYZE
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

EXPLAIN ANALYZE 会真实执行查询并输出执行耗时，适合在测试环境和压测环境使用。不要直接在生产高峰对重 SQL 随便跑 EXPLAIN ANALYZE，因为它不是只估算，它会执行。

组合索引的常见误区

第一个坑：给每个字段都加单列索引。

KEY idx_user_id (user_id),
KEY idx_status (status),
KEY idx_created_at (created_at)

这不等于能支撑 user_id + status + order by created_at 的查询路径。优化器可能只选择其中一个索引，然后再过滤和排序，扫描量仍然很大。

第二个坑：范围条件放得太靠前。

CREATE INDEX idx_created_user_status
ON user_order (created_at, user_id, status);

如果查询是 created_at >= ? AND user_id = ? AND status = ?，范围条件后面的列使用能力会受影响。不是所有场景都不能这样设计，而是你要知道自己是在服务“时间范围扫描”，不是服务“单用户列表”。

第三个坑：为了覆盖索引把字段塞得太多。

覆盖索引能减少回表，但组合索引越长，写入成本、空间成本和维护成本越高。高频、返回列稳定、读远大于写的场景可以考虑覆盖；字段变化频繁的业务列表，不要为了少一次回表把索引做成第二张表。

索引命中为什么会影响锁范围

很多人把索引只理解成“快不快”，这是不够的。对当前读和写入来说，索引还会影响 InnoDB 到底扫描哪些记录、在哪些索引区间上加锁。

看两个更新语句。

第一种，命中唯一键：

UPDATE user_order
SET status = 30
WHERE order_no = 'ORD202607090001'
  AND status = 10;

如果 order_no 命中唯一索引，InnoDB 可以快速定位到一条候选记录，锁范围通常很小。

第二种，条件没有合适索引：

UPDATE user_order
SET status = 30
WHERE status = 10
  AND created_at < '2026-07-01 00:00:00';

如果没有贴合 status + created_at 的访问路径，执行器可能扫描大量记录。扫描越多，当前读判断和加锁涉及的记录或间隙就越多，锁等待和死锁概率就会上升。

验证写入语句时也要看执行计划：

EXPLAIN
UPDATE user_order
SET status = 30
WHERE status = 10
  AND created_at < '2026-07-01 00:00:00';

生产建议：所有批量 UPDATE / DELETE 上线前，都要说明命中的索引、预估扫描行数、单批上限和回滚方式。不要只在 SELECT 上讲索引，写操作的索引路径更容易把线上锁住。

索引治理要有证据

新增索引前至少保留三类证据：

EXPLAIN FORMAT=TREE
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

SHOW INDEX FROM user_order;

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop_demo'
  AND TABLE_NAME = 'user_order'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

上线后要观察慢 SQL、写入耗时、锁等待和磁盘空间。索引不是加完就结束，后续还要确认优化器真的按预期使用。

MySQL 支持不可见索引，适合在测试或灰度阶段验证“删除某个索引会不会影响查询计划”：

ALTER TABLE user_order ALTER INDEX idx_status_created INVISIBLE;

EXPLAIN
SELECT id, order_no
FROM user_order
WHERE status = 20
ORDER BY created_at DESC
LIMIT 20;

ALTER TABLE user_order ALTER INDEX idx_status_created VISIBLE;

生产建议：不可见索引不是随手开关。核心表要先确认查询入口、压测样本和回滚方式，避免让低频但关键的任务突然退化成全表扫描。

执行计划：先看访问路径，再看 SQL 文本

很多慢 SQL 不是因为 SQL 长，而是访问路径错了。看执行计划时，先抓几个字段。

字段	你要看什么	常见问题
type	访问方式，是否全表扫描或大范围扫描	ALL、大范围 range
key	实际使用哪个索引	不是你预期的索引
rows	预估扫描多少行	预估远大于返回行
filtered	条件过滤比例	过滤比例低，说明索引不贴合
Extra	排序、临时表、覆盖索引等	Using filesort、Using temporary

优化器选计划靠成本估算，估算又依赖统计信息。数据量小、数据分布均匀时，很多 SQL 看起来都没问题；数据倾斜后，低区分度状态、热门用户、超大时间范围会让估算和真实扫描差距变大。

可以用下面几类命令补证据：

-- 查看索引基数估算
SHOW INDEX FROM user_order;

-- 更新表统计信息，适合测试或维护窗口验证计划变化
ANALYZE TABLE user_order;

-- 观察优化器开关
SHOW VARIABLES LIKE 'optimizer_switch';

统计信息漂移时，ANALYZE TABLE 只能刷新索引基数估算，不等于优化器一定会选你希望的索引。对于低区分度但分布很倾斜的列，比如订单状态、租户、渠道、地区，可以在测试环境评估直方图：

ANALYZE TABLE user_order
  UPDATE HISTOGRAM ON status, channel WITH 32 BUCKETS;

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'shop_demo'
  AND TABLE_NAME = 'user_order'\G

ANALYZE TABLE user_order
  DROP HISTOGRAM ON status, channel;

直方图不是“万能索引”。它帮助优化器理解非索引列或低区分度列的数据分布，但也会引入治理成本：数据分布变化后要刷新，测试库和生产库分布不一致时计划仍可能不同。核心 SQL 不要把稳定性全押在直方图上，优先把查询边界、组合索引和数据分层设计清楚。

如果怀疑优化器为什么选了某个索引，可以在测试环境打开 optimizer trace：

SET optimizer_trace = 'enabled=on';

SELECT id, order_no, total_amount
FROM user_order
WHERE status = 20
ORDER BY total_amount DESC
LIMIT 20;

SELECT TRACE
FROM information_schema.OPTIMIZER_TRACE\G

SET optimizer_trace = 'enabled=off';

optimizer_trace 输出很长，不适合当日常日志开着跑。它的价值是在疑难 SQL 上解释“优化器为什么这样选”，尤其是多个索引都看似可用、但计划不稳定的时候。

直接做一次对比。先写一个不太好的查询：

EXPLAIN
SELECT id, order_no, total_amount
FROM user_order
WHERE status = 20
ORDER BY total_amount DESC
LIMIT 20;

如果 status 过滤不强，total_amount 又没有合适排序索引，这条 SQL 很可能扫描大量数据再排序。优化不一定是加 (status, total_amount) 索引，因为你要先问：这个查询是不是核心路径？是否允许按金额全局排序？是否只查最近 30 天？是否应该走报表表？

更稳的查询边界：

EXPLAIN
SELECT id, order_no, total_amount
FROM user_order
WHERE status = 20
  AND created_at >= '2026-07-01 00:00:00'
  AND created_at <  '2026-08-01 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 20;

生产建议：核心接口上线前，要把核心 SQL 的执行计划作为评审材料。不要只在测试库几百行数据里看响应时间。

执行计划闭环图

这张图的重点是闭环。只截图一张 EXPLAIN 不叫优化，真正的优化要能说明：原来扫多少行，改完扫多少行，写入成本有没有增加，线上指标有没有变好，后续谁负责巡检。

分页：OFFSET 越深，代价越明显

列表页最容易从小问题长成大问题。第一页很快，不代表第 5000 页也快。

常见写法：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

这类深分页通常需要先跳过大量记录，再返回 20 条。即使有索引，也可能扫描很多行。

推荐使用游标分页，也叫 seek pagination。第一页：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

下一页使用上一页最后一条记录的 (created_at, id)：

SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
  AND (
    created_at < '2026-07-09 12:00:00.123'
    OR (created_at = '2026-07-09 12:00:00.123' AND id < 987654321)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

验证方式：

EXPLAIN
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
  AND (
    created_at < '2026-07-09 12:00:00.123'
    OR (created_at = '2026-07-09 12:00:00.123' AND id < 987654321)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

常见坑：

• 只按 created_at 排序，同一毫秒多条数据时翻页不稳定。
• 游标字段没有放进索引，分页还是慢。
• 产品要求跳到任意页，但数据量很大。这个时候要提前沟通边界，核心链路用游标分页，后台低频查询可以保留页码但限制最大页数。

生产建议：对核心列表写清分页契约。比如最大每页 100 条，最多允许查询最近 180 天，深分页只支持游标，不允许前端无边界传 pageSize=10000。

事务边界：事务不是越大越安全

事务的目标是让必须一起成功或失败的数据库操作成为一个原子单元。它不会自动理解业务语义，也不会帮你处理外部系统、缓存、消息和文件。

先看一个订单支付状态流转：

START TRANSACTION;

SELECT id, status, total_amount
FROM user_order
WHERE order_no = 'ORD202607090001'
FOR UPDATE;

UPDATE user_order
SET status = 20,
    pay_at = NOW(3),
    version = version + 1
WHERE order_no = 'ORD202607090001'
  AND status = 10;

COMMIT;

这里 FOR UPDATE 是当前读，会对读取到的记录加锁。它适合冲突代价高、必须串行的场景。问题是，事务内不能做慢操作。不要在 START TRANSACTION 和 COMMIT 中间调用支付网关、发短信、写文件、跑复杂计算。

更常见的写法是先在事务外准备好数据，事务内只做短写入：

START TRANSACTION;

UPDATE user_order
SET status = 20,
    pay_at = NOW(3),
    version = version + 1
WHERE order_no = 'ORD202607090001'
  AND status = 10;

INSERT INTO order_item (order_id, sku_id, quantity, sale_price)
VALUES (100001, 200001, 1, 99.00)
ON DUPLICATE KEY UPDATE
  quantity = VALUES(quantity),
  sale_price = VALUES(sale_price);

COMMIT;

验证方式：

SELECT ROW_COUNT();

如果状态更新影响行数为 0，要进入幂等分支，查询当前订单状态，而不是把整个事务重试一遍。

undo、redo、binlog 分别管什么

事务提交时，开发最容易混淆三类日志：

日志	主要作用	开发侧要关心什么
undo log	回滚和 MVCC 历史版本	长事务会拖住历史版本清理，导致 undo 压力上升
redo log	InnoDB 崩溃恢复	大事务会制造集中刷盘压力，提交延迟可能抖动
binlog	复制和基于日志的恢复	影响主从复制、CDC、审计和部分回放场景

你不需要在业务代码里操作这些日志，但要理解它们对写入路径的影响。一个大事务不是“只占一个连接”，它还会持有锁、保留 undo 版本、增加 redo 压力，并让 binlog 提交变成一个更大的原子单元。

最小观察入口：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';

这些参数属于数据库配置边界，本文不展开成 DBA 调参手册。开发侧要拿它们判断风险：如果批量任务让 Innodb_log_waits 上升，或者 Binlog_cache_disk_use 明显增加，说明事务大小、写入频率和提交节奏已经影响到底层日志链路。

提交链路可以按这几个阶段理解：先修改 Buffer Pool 中的数据页并写 undo，提交时 redo 进入 prepare 状态；随后 server 层写 binlog；最后 InnoDB redo commit，事务对外完成。这个链路保证崩溃恢复和复制口径能对齐，但也意味着一次“大事务提交”会同时压住 redo、binlog、锁持有时间和复制延迟。

排查提交抖动时，可以把这些证据放在一起看：

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';
SHOW GLOBAL STATUS LIKE 'Binlog_stmt_cache_disk_use';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';

生产建议：开发不要为了“看起来原子”把十万行修复放进一个事务。对在线库来说，更稳的方案是分批提交、每批有幂等标记、失败可重跑，并把每批影响行数和耗时落日志。

生产建议：核心在线事务尽量短，小批量提交；大导入、大修复、大归档走任务化治理，有批次、有暂停、有回滚点。不要把“一个事务包住全部”当成安全感。

乐观锁适合冲突低的更新

配置编辑、用户资料、低冲突状态更新，可以使用版本号：

UPDATE user_account
SET nickname = 'new-name',
    version = version + 1
WHERE user_no = 'U10001'
  AND version = 7;

验证：

SELECT ROW_COUNT() AS affected_rows;

0 表示版本已经变化。接口应该提示用户刷新或合并，而不是静默覆盖。

MVCC：快照读和当前读不是一回事

InnoDB 的普通一致性查询通常走快照读，UPDATE、DELETE、SELECT ... FOR UPDATE、SELECT ... FOR SHARE 这类操作是当前读。一个事务里混用两者时，可能看到不同时间点的数据。

你可以用两个会话观察：

会话 A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT status
FROM user_order
WHERE order_no = 'ORD202607090001';

会话 B：

UPDATE user_order
SET status = 30
WHERE order_no = 'ORD202607090001';
COMMIT;

会话 A 再查普通查询，可能仍然看到事务开始后的快照；但如果会话 A 执行当前读：

SELECT status
FROM user_order
WHERE order_no = 'ORD202607090001'
FOR UPDATE;

它读取的是当前版本并加锁。这个差异会影响业务判断，所以不要把“事务内查过一次”当成所有后续操作的绝对事实。

Read View 可以理解成一致性读的可见性快照。它会结合事务 ID 和 undo 版本链判断某行的哪个版本对当前查询可见。REPEATABLE READ 下，同一事务内普通一致性读通常复用事务级视图；READ COMMITTED 下，每条一致性读语句会看到更新的已提交版本。这个差异会影响“我刚才查过了，为什么后面又不一样”这类问题。

把 Read View 和 undo 版本链放到一起看，会更容易区分快照读和当前读：

这张图不能替代命令排查，因为 Read View 本身不是一个让开发直接查询的业务对象。它的用法是辅助复现实验和设计评审：遇到“事务里两次查询结果不一致”时，先确认隔离级别和语句类型；评审“先查后改”逻辑时，要求最终 UPDATE 带业务状态、版本号或唯一约束，不能只依赖前面那次快照读的判断。

隔离级别不要靠默认值猜，先查：

SELECT @@transaction_isolation;

需要临时验证时，可以只改当前会话：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 执行你的验证 SQL
COMMIT;

常见坑是把隔离级别当成应用配置随手改。比如从 REPEATABLE READ 切到 READ COMMITTED 后，间隙锁行为、binlog 行为、业务重复读预期都会跟着变化。生产变更必须和业务一致性、复制格式、测试用例一起评审。

生产建议：事务里判断状态再更新时，最终的 UPDATE 仍然要带上状态条件或版本条件。这样即使前面的读取和并发窗口有变化，最后写入也能兜住。

锁等待和死锁：先看索引，再看资源顺序

锁问题经常表现成接口慢、偶发失败、线程堆积，日志里可能是：

Lock wait timeout exceeded; try restarting transaction
Deadlock found when trying to get lock; try restarting transaction

先看是否有事务长时间不提交：

SELECT *
FROM information_schema.INNODB_TRX\G

再看锁等待关系：

SELECT *
FROM performance_schema.data_locks\G

SELECT *
FROM performance_schema.data_lock_waits\G

必要时看 InnoDB 最近一次死锁信息：

SHOW ENGINE INNODB STATUS\G

常见原因：

• 更新条件没命中索引，锁范围扩大。
• 事务里做远程调用或大循环，锁持有时间太长。
• 多条业务路径更新相同资源，但访问顺序不一致。
• 大批量更新一次锁太多行，和在线请求互相阻塞。

定位时不要只看“有锁等待”。要把等待关系拆成三步：

1. data_lock_waits 看哪个事务在等、被哪个事务挡住。
2. data_locks 看锁对象、锁模式、索引名和记录范围。
3. INNODB_TRX 看事务开始时间、状态和正在执行的语句。

可以把三张信息表联起来做一次粗排：

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_started AS waiting_started,
  b.trx_id AS blocking_trx_id,
  b.trx_started AS blocking_started,
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.INDEX_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r
  ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.INNODB_TRX b
  ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
JOIN performance_schema.data_locks dl
  ON w.REQUESTING_ENGINE_LOCK_ID = dl.ENGINE_LOCK_ID\G

字段名在不同版本上要以当前实例为准；如果联表不成功，就分开查，不要为了一个排查 SQL 卡住。关键是拿到“等待事务、阻塞事务、表、索引、锁模式、事务持续时间”这几个证据。

直接做法：

EXPLAIN
UPDATE user_order
SET status = 30
WHERE user_id = 10001
  AND status = 10
  AND created_at < '2026-07-01 00:00:00';

如果更新条件没有合适索引，先不要上线。更新也需要执行计划，尤其是批量更新。生产里建议把大更新拆成小批次：

UPDATE user_order
SET status = 30
WHERE status = 10
  AND created_at < '2026-07-01 00:00:00'
ORDER BY id
LIMIT 500;

执行后检查：

SELECT ROW_COUNT();

循环执行时要有间隔、最大次数和回滚策略。不要写一个无限循环脚本直接打生产库。

锁类型不要只背名字，要能从 SQL 形态判断风险：

SQL 形态	常见锁范围	典型风险	验证方式
唯一索引等值命中一行	主要是记录锁	并发更新同一行等待	data_locks 看唯一索引记录
普通索引范围查询并当前读	next-key lock，记录锁加间隙锁	阻止范围内插入，接口看起来像“插入也被查询挡住”	两会话做 FOR UPDATE 和 INSERT
条件没索引或索引选择差	扫描范围扩大，锁跟着扩大	小业务更新变成大范围阻塞	先 EXPLAIN，再看 LOCK_MODE 和 INDEX_NAME
向被 gap lock 覆盖的范围插入	insert intention lock 等待	新增请求卡住但阻塞者是范围查询或更新	SHOW ENGINE INNODB STATUS\G 看 insert intention waiting
READ COMMITTED 下范围更新	间隙锁通常减少，但并非所有场景都消失	幻读预期、复制口径和业务一致性变化	同一脚本分别在 RC/RR 跑

最小复现实验如下，先建索引：

ALTER TABLE user_order ADD INDEX idx_user_status_created (user_id, status, created_at);

会话 A：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT id
FROM user_order
WHERE user_id = 10001
  AND status = 10
  AND created_at BETWEEN '2026-07-01 00:00:00' AND '2026-07-31 23:59:59'
FOR UPDATE;

会话 B 尝试插入同一索引范围：

INSERT INTO user_order (
  order_no, user_id, status, total_amount, created_at, updated_at
) VALUES (
  'NO-LOCK-TEST-001', 10001, 10, 10.00,
  '2026-07-15 12:00:00', NOW()
);

如果 B 被阻塞，不要只看应用超时，马上在第三个会话查：

SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'user_order'\G

SELECT *
FROM performance_schema.data_lock_waits\G

然后把会话 A 改成 READ COMMITTED、把查询条件改成唯一索引等值、把索引删掉分别复现一次。你会看到锁范围随隔离级别、访问路径和条件形态变化。生产建议很简单：会加锁的读写必须先证明索引路径；需要范围锁时要把范围收窄；插入被阻塞时不要只盯 INSERT，要找前面哪个范围当前读拿住了 gap。

MDL 和 Online DDL：改表也会挡住业务

场景说明：有些事故不是 SQL 慢，而是一次看似“在线”的 ALTER TABLE 把业务挡住了。MySQL 执行 DDL 和 DML 都会涉及 metadata lock。一个未提交的长事务可能持有表的元数据读锁，导致 DDL 等待；DDL 一旦排队，又可能让后续普通查询或写入继续排队，形成连锁阻塞。

Online DDL 不等于没有 MDL。真正危险的是排队链路：前面一个长事务没提交，中间一个 DDL 等独占元数据锁，后面的普通请求也被堵在同一张表上。

这张图适合放进 DDL 变更评审：先证明没有长事务和长查询，再确认目标 DDL 的算法、锁级别和失败策略。线上排障时按图反推，不要只盯住正在执行的 ALTER TABLE，还要找最早持有 MDL 的会话；很多时候真正该处理的是前面的长事务，而不是后面已经排队的业务 SQL。

先用一个最小实验理解。

会话 A：

START TRANSACTION;
SELECT *
FROM user_order
WHERE id = 100001;
-- 故意不提交

会话 B：

ALTER TABLE user_order
  ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

如果会话 B 卡住，不要只看 SHOW PROCESSLIST。继续看 metadata lock：

SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop_demo'
  AND OBJECT_NAME = 'user_order'\G

生产里做 Online DDL 要先问四件事：

• 当前 MySQL 版本是否支持目标操作的 INSTANT、INPLACE 或 COPY 算法。
• LOCK=NONE 是否真的适用于这次操作，不支持时会失败还是退化。
• 是否存在长事务、长查询、备份、导出任务持有 MDL。
• 失败或耗时过长时是中断 DDL、等待窗口，还是走影子表、双写迁移或专用变更工具。

生产建议：核心表 DDL 不要只写一句 ALTER TABLE。要有预检查、执行窗口、锁等待监控、回滚方案和业务降级预案。能在低峰灰度的索引先灰度；字段类型、主键、字符集这类高风险 DDL 要单独评审。

慢 SQL：从样本到闭环，不是看到慢就加索引

慢 SQL 治理建议用固定模板，不要靠聊天记录和截图。先确认慢查询入口：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

开发侧通常不负责生产慢日志配置，但必须会读慢 SQL 样本，并能复现参数。一个慢 SQL 处理单至少包含：

项目	要写什么
SQL 样本	带脱敏参数的真实 SQL
调用入口	哪个接口、任务、页面、批处理
出现频率	高峰、低峰、固定时间、偶发
数据范围	租户、用户、时间、状态、返回行
执行计划	优化前后 EXPLAIN
等待信息	是否有锁等待、连接池排队
变更方案	改 SQL、加索引、改分页、改业务边界
验证结果	扫描行、耗时、写入影响、上线后指标

常见优化路径：

1. 缩小查询范围：加时间边界、租户边界、状态边界。
2. 调整索引：围绕过滤、排序、分页设计组合索引。
3. 改写 SQL：避免函数包裹索引列，减少返回列，拆复杂查询。
4. 改分页：深分页改游标分页。
5. 改业务：报表异步化，导出走任务，历史数据归档。

生产建议：慢 SQL 闭环必须和发布流程结合。新增核心接口时，至少提交核心 SQL、预估数据量、执行计划和分页边界。不要等慢日志报警后才发现没有索引。

连接池：不是越大越好

MySQL 慢不一定是 SQL 慢，也可能是应用连接池排队。后端开发至少要理解连接池几个参数。

以 HikariCP 为例：

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 10
      connection-timeout: 3000
      validation-timeout: 1000
      idle-timeout: 600000
      max-lifetime: 1700000

这里的数字不是通用答案。你要按服务实例数和数据库最大连接数计算：

总连接上限 = 单实例 maximum-pool-size * 应用实例数 + 任务实例连接数 + 管理连接预留

如果数据库允许 300 个连接，线上有 10 个应用实例，每个实例连接池开 50，理论上就可能打到 500 个连接，还没算批量任务。这个时候调大连接池不是解决问题，而是在制造更大的排队和数据库压力。

验证方式：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

应用侧要看连接池等待指标、活跃连接数、空闲连接数和超时次数。生产建议是：核心服务、后台任务、报表任务使用独立线程池和连接池配置，避免导出任务把在线接口连接耗尽。

容量增长：别等表大了再设计边界

容量问题往往在需求阶段就埋下了。一个列表接口如果允许任意时间范围、任意状态组合、任意排序、任意页码，数据增长后一定会出事。

开发时至少做一次容量估算：

日订单量：100 万
保留在线查询热数据：180 天
热表行数：约 1.8 亿
单用户最大订单：10 万
核心列表页：按 user_id + status + created_at 倒序
后台报表：按天汇总，不走在线明细表实时聚合

然后把边界写进接口契约：

• 普通用户列表只查当前用户，默认最近 90 天，最大 180 天。
• 管理端查询必须带时间范围，单次导出走异步任务。
• 报表统计走汇总表或离线任务，不在在线明细表上做无限聚合。
• 大批量更新按 id 范围分批，每批有上限和间隔。

常见坑是只讨论“当前数据量能不能跑”。架构师要问的是“按这个增长速度，半年后核心查询是否还能解释”。

落地工程深水区

这一节是给真正要把 MySQL 用进生产系统的同学看的。MySQL 开发侧最容易拖垮项目的，不是某个语法不会，而是这些治理点长期没人负责。

索引治理

现象：表上索引越来越多，写入越来越慢，优化器偶尔选择奇怪索引，没人敢删旧索引。

直接做法：

SHOW INDEX FROM user_order;

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop_demo'
  AND TABLE_NAME = 'user_order'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

治理要求：

• 每个核心索引必须写清服务哪些 SQL。
• 新增索引必须提供优化前后执行计划。
• 删除索引先用不可见索引或灰度验证。
• 定期清理重复索引、低价值索引和过度覆盖索引。

生产建议：索引评审不要只由 DBA 承担。开发要解释业务查询路径，DBA 或技术负责人再判断成本。

事务边界

现象：接口偶发慢、锁等待增加、死锁增多、事务回滚后外部系统已经执行。

直接做法：

• 事务内只放必须同成同败的数据库写入。
• 外部调用、文件处理、复杂计算、消息发送放到事务外或事务提交后。
• 状态更新带当前状态或版本号。
• 幂等靠唯一键、业务幂等号和冲突处理兜底。

验证：

SELECT *
FROM information_schema.INNODB_TRX\G

生产建议：核心写入路径要补并发测试，至少覆盖重复提交、状态抢占、唯一键冲突、死锁重试和事务回滚。

锁等待

现象：慢 SQL 日志里看到查询慢，但 SQL 本身执行计划不差，接口 P99 抖动。

直接做法：

SELECT *
FROM performance_schema.data_lock_waits\G

SHOW ENGINE INNODB STATUS\G

判断：

• 如果等待集中在某张表某个索引，先查更新条件是否命中索引。
• 如果长事务很多，查应用是否把远程调用放进事务。
• 如果死锁固定出现，统一资源访问顺序。

生产建议：不要把锁等待全部归因于数据库。很多锁问题是应用事务边界和索引路径共同造成的。

连接池

现象：数据库连接数不高但接口超时，或者连接数很高但吞吐没有增加。

直接做法：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

判断：

• Threads_connected 高，可能连接池总量过大或连接泄漏。
• Threads_running 高，可能真正执行压力大。
• 应用连接等待高，可能连接池过小、SQL 慢或后台任务占满连接。

生产建议：连接池大小要按实例数统一评审。后台任务不要和在线接口抢同一组连接资源。

容量增长

现象：前几个月一切正常，数据过千万后列表、导出、统计一起变慢。

直接做法：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop_demo'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

判断：

• 表行数增长是否超过设计预期。
• 索引空间是否接近或超过数据空间。
• 核心查询是否仍有稳定过滤条件。
• 报表和导出是否还在打在线表。

生产建议：容量边界要写进需求和接口契约。无限查询范围不是用户体验，是生产风险。

Buffer Pool 和写入抖动

现象：执行计划没有明显退化，但接口 P99 抖动，批量任务期间写入延迟明显升高。

直接做法：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW ENGINE INNODB STATUS\G

判断：

• 脏页持续偏高，说明后台刷盘或写入节奏可能跟不上。
• buffer pool 读盘增长快，说明热点页缓存不足或访问模式太散。
• redo 等待上升，说明提交链路已经受日志写入影响。

生产建议：大修复、大导入、大归档要和在线接口隔离窗口。开发侧要提供限速、暂停、续跑和回滚点，不能把刷盘压力全丢给数据库参数。

DDL 变更

现象：发布时只是加索引或加字段，业务接口却突然全部等待。

直接做法：

SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop_demo'
  AND OBJECT_NAME = 'user_order'\G

判断：

• 如果 DDL 在等 MDL，先找未提交长事务和长查询。
• 如果 DDL 算法退化成 copy，核心大表要暂停并改走变更方案。
• 如果后续 DML 被挡住，要评估是否中断 DDL 释放队列。

生产建议：DDL 必须有预检查。核心表变更前要确认长事务、备份、导出、报表任务和 Online DDL 算法，不要把“支持 Online DDL”理解成“任何时候都不会阻塞”。

慢 SQL 闭环

现象：慢 SQL 每周都有人处理，但类似问题反复出现。

直接做法：建立慢 SQL 处理模板，优化后必须更新查询契约、索引说明或代码规则。

验证：

EXPLAIN ANALYZE
SELECT id, order_no, status, total_amount, created_at
FROM user_order
WHERE user_id = 10001
  AND status = 20
ORDER BY created_at DESC, id DESC
LIMIT 20;

生产建议：慢 SQL 的最终产物不是“这条 SQL 变快了”，而是“同类查询以后不会再用错误路径写出来”。

排障手册

现象	先看什么	常见原因	修复方向	验证
有索引但仍然慢	EXPLAIN 的 key、rows、Extra	低选择性、范围太大、排序不贴合	调整组合索引或查询边界	对比扫描行和耗时
索引没命中	SQL 条件和字段类型	函数包裹、隐式转换、左模糊	改 SQL 或补生成列/专用索引	EXPLAIN
分页越往后越慢	LIMIT OFFSET	深分页跳过大量行	游标分页	对比第一页和深页执行计划
更新偶发超时	锁等待和事务	更新条件缺索引、事务太长	补索引、缩短事务、分批更新	data_lock_waits
死锁	SHOW ENGINE INNODB STATUS	资源访问顺序不一致	统一更新顺序，缩小锁范围	并发压测
连接池耗尽	应用连接池指标	慢 SQL、连接泄漏、后台任务占用	限流、拆连接池、优化 SQL	连接等待下降
报表拖慢在线库	慢日志和调用入口	在线表实时大聚合	汇总表、异步任务、只读隔离	核心接口 P95 恢复

命令速查

-- 看表结构和索引
SHOW CREATE TABLE user_order\G
SHOW INDEX FROM user_order;

-- 看执行计划
EXPLAIN SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;
EXPLAIN ANALYZE SELECT ...;
ANALYZE TABLE user_order;

-- 看统计信息和直方图
SHOW INDEX FROM user_order;
ANALYZE TABLE user_order UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
SELECT * FROM information_schema.COLUMN_STATISTICS\G

-- 临时观察优化器选择，测试环境使用
SET optimizer_trace = 'enabled=on';
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

-- 看慢查询相关变量
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

-- 看连接情况
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

-- 看事务和锁
SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G
SHOW ENGINE INNODB STATUS\G

-- 看事务日志相关压力
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';

-- 看 InnoDB 页缓存和脏页
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

-- 看 MDL 和 Online DDL 阻塞
SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'shop_demo'
  AND OBJECT_NAME = 'user_order'\G

-- 看表容量
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop_demo'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

上线前检查清单

• 核心表是否有明确主键、业务唯一键、状态字段和时间字段。
• 核心 SQL 是否提供了 EXPLAIN，并说明命中的索引和扫描行数。
• 核心 SQL 是否明确了优化器证据：统计信息、候选索引、是否需要 EXPLAIN ANALYZE 或 optimizer trace。
• 统计信息、直方图和数据分布是否和生产接近，是否有刷新和回滚方案。
• 列表页是否限制最大时间范围、最大页大小和深分页方式。
• 新增索引是否说明服务的查询路径、写入影响和回滚方式。
• DDL 是否评估 MDL、Online DDL 算法、锁级别、长事务和回滚窗口。
• 批量写入、批量更新是否评估了 redo、undo、binlog 和锁持有时间。
• 写入高峰是否观察 Buffer Pool 命中、脏页、checkpoint 和 redo 等待。
• 状态更新是否带当前状态或版本号。
• 事务内是否没有远程调用、文件处理、长循环和大批量操作。
• 隔离级别是否明确，是否评估了 REPEATABLE READ、READ COMMITTED 对快照读、当前读和间隙锁的影响。
• 批量任务是否有限流、批次大小、失败明细和重试策略。
• 连接池总量是否按应用实例数统一计算。
• 慢 SQL 是否有处理模板和上线后验证方式。
• 是否没有把安装、主从、备份恢复等部署运维内容混入开发方案。

落地模板

核心 SQL 评审模板

接口/任务：
SQL 类型：查询 / 更新 / 插入 / 批量 / 导出
业务频率：QPS / 每日次数 / 高峰时间
数据范围：租户 / 用户 / 时间 / 状态
预计数据量：当前 / 半年 / 一年
分页方式：页码 / 游标 / 不分页
命中索引：
EXPLAIN 结果：
写入影响：
异常处理：唯一冲突 / 乐观锁失败 / 死锁 / 超时
上线后观察：慢 SQL / 连接池 / 锁等待 / P95 / P99

索引说明模板

索引名：
表名：
列顺序：
服务 SQL：
过滤主轴：
排序/分页：
是否覆盖：
预期扫描范围：
写入成本：
是否允许删除：
验证 SQL：

官方文档入口

• MySQL 参考手册：版本与发布模型
• MySQL 8.0 Release Notes：8.0.46 EOL 提示
• MySQL 参考手册：EXPLAIN
• MySQL 参考手册：理解执行计划
• MySQL 参考手册：优化器与索引
• MySQL 参考手册：优化器统计信息
• MySQL 参考手册：Optimizer Trace
• MySQL 参考手册：Index Condition Pushdown
• MySQL 参考手册：Multi-Range Read
• MySQL 参考手册：InnoDB Buffer Pool
• MySQL 参考手册：InnoDB Multi-Versioning
• MySQL 参考手册：InnoDB Redo Log
• MySQL 参考手册：InnoDB 事务隔离级别
• MySQL 参考手册：InnoDB 锁
• MySQL 参考手册：不同 SQL 设置的 InnoDB 锁
• MySQL 参考手册：Metadata Locking
• MySQL 参考手册：Online DDL
• MySQL 参考手册：慢查询日志
• MySQL 参考手册：慢查询日志输出位置
• MySQL 参考手册：Performance Schema 锁等待表
• HikariCP 官方配置说明