MySQL 优化经常被总结成一些口诀：联合索引遵守最左前缀、不要在索引列上使用函数、事务越短越好。这些口诀有用，但只背口诀很容易误判。

真正值得长期记住的是一套分析方法：SQL 扫描了多少记录、访问了多少页、持有了哪些锁、占用了多久连接，以及失败后如何恢复。

本文以 MySQL 8 和 InnoDB 为主，整理表设计、索引、执行计划、事务、锁、连接池、大表变更和排障经验。示例只表达通用模式，不代表所有业务都应该照抄。

核心结论

  1. 索引不是越多越好。 每个索引都会消耗磁盘、Buffer Pool、写放大和维护成本。
  2. 不存在可靠的“索引失效清单”。 IS NULL、BETWEEN、OR、!= 都可能使用索引，最终由查询形状、数据分布、统计信息和成本模型决定。
  3. 联合索引应该围绕完整查询设计。 同时考虑过滤、排序、分页、回表和其他查询的复用，不要只看单列选择性。
  4. 普通 SELECT 在常见隔离级别下通常是快照读，不加记录锁。 SELECT ... FOR UPDATE、SELECT ... FOR SHARE、UPDATE 和 DELETE 才是典型锁定读或写操作。
  5. InnoDB 锁的是扫描到的索引记录和范围，不是 SQL 文本里最终返回的几行。 索引不合适，锁范围就可能远大于结果集。
  6. 死锁是并发数据库的正常现象。 应减少发生概率，并让应用可以安全地重试整个事务。
  7. 连接池不是越大越好。 它只是在应用侧排队，不能让数据库凭空增加 CPU、I/O 和锁处理能力。
  8. Online DDL 不等于零影响。 即使允许并发 DML，仍可能争用元数据锁、I/O、临时空间，并增加 Checkpoint 压力和复制延迟。
  9. 单表行数没有统一红线。 查询模式、行宽、索引、冷热分布、DDL 窗口和恢复时间比一个固定数字更重要。
  10. 优化必须建立在测量上。 先看慢日志、执行计划和实际行数，再修改 SQL 或索引。

InnoDB 的存储模型

为什么是 B+ Tree

InnoDB 以页为基本单位组织数据，索引通常使用 B+ Tree。B+ Tree 适合数据库，不只是因为“减少磁盘 I/O”，还因为它同时满足：

  • 非叶子节点主要保存键和子节点指针，扇出较大，树高较低；
  • 叶子节点按键有序，适合范围扫描和排序；
  • 从根到叶子的路径长度稳定；
  • 页可以被 Buffer Pool 缓存，热点查询经常不需要真实磁盘读取；
  • 节点分裂和合并的成本可以被增量写入摊销。

SSD 已经改变了随机 I/O 成本，但没有改变“少访问页通常更快”这件事。索引优化的本质仍然是用更小、更有序的数据结构缩小扫描范围。

聚簇索引

每个 InnoDB 表都有一个聚簇索引，叶子节点直接保存整行数据：

  1. 显式定义主键时，主键就是聚簇索引；
  2. 没有主键时，InnoDB 选择第一个所有列均为 NOT NULL 的唯一索引；
  3. 两者都没有时，InnoDB 生成隐藏的行 ID。

生产表应显式定义主键。依赖隐藏主键会让数据迁移、复制、排障和二级索引成本都变得不透明。

二级索引与回表

二级索引叶子节点保存“二级索引列 + 主键值”。通过二级索引查询非索引列时，InnoDB 先找到主键，再回到聚簇索引读取整行，这就是回表。

因此，主键会被复制到每一个二级索引中。过长的字符串主键或随机 UUID 不只影响主键索引，也会放大所有二级索引、缓存和写入成本。

主键通常应满足：

  • 短；
  • 唯一；
  • 不可变；
  • 写入顺序尽量稳定；
  • 不包含业务上可能变化的含义。

自增 ID 写入局部性好，但分布式生成和数据合并需要额外设计；随机 ID 易于跨节点生成，却可能导致页分裂和更差的缓存局部性。没有一种方案适合所有系统，关键是知道代价在哪里。

表结构设计

下面是一张用于示例的通用任务表：

CREATE TABLE task_record (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(64) NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    amount DECIMAL(30, 8) NOT NULL DEFAULT 0,
    version INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME(3) NOT NULL,
    updated_at DATETIME(3) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_tenant_request (tenant_id, request_id),
    KEY idx_tenant_status_created_id
        (tenant_id, status, created_at DESC, id DESC)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_bin;

数据类型

  • 整数选择能覆盖未来容量的最小类型，不要为了省几个字节留下很快溢出的字段。
  • 金额使用 DECIMAL 或定义清楚比例的整数；Java 侧使用 BigDecimal，不要使用 float 或 double。
  • VARCHAR 长度表达业务上限，而不是习惯性全部写成最大值。
  • TEXT、BLOB 和大 JSON 不应塞进高频列表的热行；必要时拆到扩展表或对象存储。
  • 状态字段要定义稳定编码。Java 枚举的 ordinal() 会随声明顺序变化，不适合作为持久化协议。
  • 时间需要明确时区、精度和语义。created_at、updated_at 是事件时间还是数据库写入时间，不能含糊。

NULL 不是索引失效

InnoDB 的 B-Tree 索引可以保存和检索 NULL，IS NULL 也可以使用索引。真正需要考虑的是字段语义：

  • NULL 表示未知、缺失或不适用，不应和空字符串、零混用；
  • 可为空字段会增加应用分支和三值逻辑；
  • 唯一索引通常允许出现多个 NULL，不要误以为它能约束“只能有一个空值”；
  • 当字段业务上一定存在时，优先使用 NOT NULL 和合理默认值。

字符集与排序规则

统一使用 utf8mb4。排序规则决定比较、排序、唯一性和索引行为：是否区分大小写、重音和尾部空格，都可能影响业务结果。

跨表 JOIN 的字符集或排序规则不一致，会导致隐式转换、索引利用困难甚至直接报错。选择排序规则时先明确业务语义，不要因为某个模板默认这样写就永久继承。

JSON 的边界

JSON 适合变化快、读取整体为主的扩展属性，不适合替代关系模型。频繁作为过滤、排序、JOIN 或唯一约束的字段，应该成为普通列。

必须查询 JSON 内部字段时，可以使用生成列或函数索引，但仍要验证表达式完全匹配、统计信息可靠，并为 Schema 演进准备迁移方案。

索引设计

从查询开始，不从字段开始

索引服务于查询，而不是字段。设计前先写清楚：

SELECT id, request_id, status, created_at
FROM task_record
WHERE tenant_id = ?
  AND status = ?
  AND created_at < ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

这条查询的主要访问路径是：租户等值过滤、状态等值过滤、时间范围、稳定倒序和小批量返回。因此索引 (tenant_id, status, created_at DESC, id DESC) 比四个单列索引更贴合查询。

最后的 id 很重要：不同记录可能有相同的 created_at，没有唯一 Tie-breaker，翻页时可能重复或遗漏。

最左前缀

联合索引 (a, b, c) 可以直接支持以 (a)、(a, b)、(a, b, c) 为查找前缀的查询。只有 b 或 (b, c) 时，通常不能像完整左前缀那样定位范围。

但“违反最左前缀就绝对不用索引”也不准确。优化器可能选择全索引扫描、Skip Scan 或其他访问方式。关键不是执行计划里是否出现索引名字，而是扫描量和总代价是否合理。

联合索引列顺序

一个实用顺序是：

  1. 高频、稳定的等值过滤列；
  2. 范围过滤或排序列；
  3. 保证排序稳定的唯一列；
  4. 少量、窄且高频读取的投影列，用于覆盖索引。

“选择性最高的列永远放最前面”是不完整的规则。联合索引还要服务租户隔离、排序、分页和多条查询。多个等值条件之间的顺序，常常应由完整工作负载决定。

范围条件之后的列，通常不能继续缩小同一个连续查找区间，但仍可能用于 Index Condition Pushdown、排序或覆盖。不要只凭口诀判断，要看执行计划。

覆盖索引

如果查询需要的列全部可以从某个索引得到，就能避免回表。覆盖索引对高频小查询很有效，但不应该把大量大字段都塞进索引：

  • 索引越宽，单页容纳的记录越少；
  • Buffer Pool 命中率下降；
  • 写入、更新和页分裂成本增加；
  • 同一查询的收益可能伤害全表的写性能。

覆盖索引是空间换时间，而且这个空间会持续产生写放大。

唯一索引也是并发控制

“先查询是否存在，再插入”存在竞态。两个事务可以同时查到不存在，然后都执行插入。数据库唯一约束才是最终防线。

典型做法是让 (tenant_id, request_id) 成为唯一键，应用捕获重复键并返回已有结果。Redis 幂等 Key 或分布式锁可以减少冲突，但不能替代唯一约束。

不要重复建索引

存在 (a, b, c) 后，单列索引 (a) 经常是冗余的；但 (b) 并不冗余，因为它不是左前缀。是否删除还要考虑：

  • 更短索引是否被高频覆盖查询使用；
  • 唯一性是否不同；
  • 排序方向和前缀长度是否不同；
  • 外键或执行计划是否依赖；
  • 删除后写性能能改善多少。

MySQL 8 的 Invisible Index 可以先隐藏索引、观察执行计划与性能，再决定是否删除。不过隐藏并不消除维护成本，它只是降低试错风险。

索引的常见代价

  • 每次 INSERT 都要写所有相关索引；
  • 更新索引列相当于删除旧项并插入新项；
  • 删除记录后还需要 Purge 回收；
  • 索引过多会增加优化器选择成本；
  • 低价值索引占用 Buffer Pool，挤出真正的热点页；
  • 大索引会拉长备份、恢复、复制和 DDL 时间。

“索引失效”应该怎样理解

更准确的问题不是“索引有没有失效”，而是：

  1. 优化器能否使用某个索引定位范围？
  2. 使用索引是否比全表扫描便宜？
  3. 索引只是用于扫描，还是也消除了排序、回表或临时表？
  4. 估算行数与实际行数是否接近？
条件 可能的行为 真正要检查的内容
col IS NULL 可以使用索引 NULL 比例与扫描行数
col BETWEEN ? AND ? 可以做范围扫描 范围宽度与回表数量
col != ?、NOT IN (...) 可能做范围或索引扫描 命中比例是否太高
a = ? OR b = ? 可能使用 Index Merge 联合查询是否值得专用索引
name LIKE 'abc%' 通常可以做前缀范围扫描 排序规则与参数类型
name LIKE '%abc' 普通 B-Tree 难以定位 是否应使用全文检索或反向索引
DATE(created_at) = ? 普通列索引难以直接定位 改写范围或使用函数索引
字符串列和数字比较 可能发生隐式转换 参数类型、转换方向、执行计划
低选择性状态列 可能全表扫描 是否与租户、时间组合
ORDER BY 可能利用索引，也可能排序 过滤、方向和索引顺序是否匹配

把函数放到常量一侧

下面的写法对列逐行计算，普通 created_at 索引难以直接用于定位：

WHERE DATE(created_at) = '2026-09-08'

通常应改写为半开区间：

WHERE created_at >= '2026-09-08 00:00:00'
  AND created_at <  '2026-09-09 00:00:00'

半开区间不会在毫秒、微秒精度变化时漏掉边界数据。

隐式类型转换

如果 request_id 是字符串，应用却绑定成数字，MySQL 可能需要转换列值后比较，导致索引访问路径恶化。JOIN 两侧字段的类型、长度、Unsigned 属性、字符集和排序规则也应一致。

最稳妥的做法不是背转换规则，而是让参数类型从 API、Java、JDBC 到数据库列保持一致，并用真实参数查看执行计划。

优化器主动不用索引

当查询需要读取表中很大比例的数据时，顺序扫描可能比“扫描二级索引 + 大量随机回表”更便宜。这个比例没有固定阈值，会随着行宽、索引宽度、缓存命中、磁盘、统计信息和查询列变化。

FORCE INDEX 可能暂时绕过错误计划，但它会把今天的数据分布固化成明天的假设。优先修复统计信息、SQL 或索引设计；确实使用 Hint 时，要有注释、监控和撤销条件。

用 EXPLAIN 分析，而不是猜

EXPLAIN 与 EXPLAIN ANALYZE

EXPLAIN FORMAT=TREE
SELECT id, request_id, status, created_at
FROM task_record
WHERE tenant_id = 42
  AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN 展示优化器计划和估算。EXPLAIN ANALYZE 会真正执行语句，并补充实际耗时、返回行数和循环次数：

EXPLAIN ANALYZE
SELECT id, request_id, status, created_at
FROM task_record
WHERE tenant_id = 42
  AND status = 1
ORDER BY created_at DESC, id DESC
LIMIT 50;

因为 EXPLAIN ANALYZE 会执行 SQL，生产环境只应对确认安全、影响可控的只读查询使用，并遵守超时与权限限制。

重点看什么

  • 访问方式是常量、点查、范围、索引扫描还是全表扫描；
  • 候选索引有哪些、最终选择了哪个；必要时用 Optimizer Trace 分析未选择原因；
  • 估算 rows 与实际行数相差多少；
  • filtered 后还剩多少记录；
  • 每个节点的 actual time、rows 和 loops；
  • 是否发生额外排序、临时表、物化或重复回表；
  • LIMIT 50 之前实际扫描了多少行；
  • 最慢的是扫描、JOIN、排序还是聚合。

执行计划出现 Using index 不代表查询一定快，全表覆盖索引扫描仍可能很贵；出现 Using filesort 也不代表一定慢，对几十行内存排序可能比维护一个宽索引更划算。

统计信息

优化器依赖统计信息估算基数。数据分布剧烈变化、批量导入或删除后，估算可能偏离实际。可以检查持久化统计、直方图和 ANALYZE TABLE，但不要把刷新统计当作固定运维仪式。

优化必须使用接近生产的数据量和分布。空测试库里“走索引”，不能证明亿级表上会选择同一计划。

查询、分页与批处理

不要习惯性 SELECT *

只读取需要的列可以减少网络、反序列化、内存和回表成本，也让覆盖索引成为可能。大字段应在详情接口按需读取，不要进入高频列表。

避免 N+1

先查 100 条记录，再循环执行 100 次明细查询，是最常见的数据库放大器之一。优先批量查询：

  1. 一次取得主记录；
  2. 提取关联 ID 并去重；
  3. 使用有界 IN 批量查询；
  4. 在内存中按 ID 分组组装。

IN 也不是越长越好。过大的 SQL 会增加解析、网络、优化和执行成本，应根据数据量分批，必要时使用临时表或数据导入方案。

游标分页

深分页：

SELECT id, request_id, created_at
FROM task_record
WHERE tenant_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 100000, 50;

即使使用索引，MySQL 仍要跳过前面大量记录。更稳定的是 Keyset Pagination：

SELECT id, request_id, created_at
FROM task_record
WHERE tenant_id = ?
  AND (
      created_at < ?
      OR (created_at = ? AND id < ?)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

对应索引至少需要 (tenant_id, created_at DESC, id DESC)。游标携带完整排序键，才能在时间重复时保持稳定。

Keyset Pagination 不适合直接跳到任意页，但多数无限滚动、后台扫描和批量修复并不需要这个能力。

COUNT(*) 不是免费的

InnoDB 不维护可以直接返回的精确总行数。精确 COUNT(*) 仍要扫描满足条件的记录或索引。列表页如果每次都计算一个昂贵总数，分页查询本身再快也没用。

可以根据产品语义选择：

  • 小范围索引计数；
  • 只返回 hasMore；
  • 缓存或异步计算近似值；
  • 离线汇总；
  • 明确告诉用户数字是估算。

批量写入

  • 批量大小要有上限，避免超大事务、SQL 包、Redo 和 Binlog；
  • 大范围更新先按主键游标选出一小批，再精确更新；
  • 每批提交后记录检查点，失败可以恢复；
  • 回填任务应限速，并监控主库负载和复制延迟；
  • 不要用 OFFSET 扫描持续变化的大表，主键游标更可靠；
  • 需要重复执行时，写入必须幂等。

事务与 MVCC

四种隔离级别

隔离级别 一致性读特点 常见代价
READ UNCOMMITTED 可能读到未提交数据 很少用于业务系统
READ COMMITTED 每次一致性读创建新快照 同一事务两次读取可能不同
REPEATABLE READ 通常复用首次一致性读建立的快照 Undo 保留更久，锁定读仍看当前版本
SERIALIZABLE 关闭 autocommit 时，普通读取会转为共享锁定读；开启 autocommit 时可以保持一致性非锁定读 并发能力通常最低

MySQL InnoDB 默认是 REPEATABLE READ。默认值不等于所有系统的最佳选择：READ COMMITTED 可以减少部分范围锁影响，REPEATABLE READ 则提供更稳定的事务内快照。应根据业务语义和锁冲突实测选择。

快照读与当前读

普通 SELECT 在 READ COMMITTED 和 REPEATABLE READ 下通常使用 MVCC 快照，不给读取记录加锁。它通过 Undo 版本链重建事务可见的数据。

下面这些属于当前读，会读取较新的可用版本并参与锁定：

  • SELECT ... FOR UPDATE；
  • SELECT ... FOR SHARE；
  • UPDATE；
  • DELETE；
  • 某些约束检查和特殊语句。

同一事务里先做快照读、再做锁定读，可能看到不同版本。不要把两种读取方式混在一起后，仍假设整个事务只存在一张静态快照。

Redo、Undo 与 Binlog

  • Redo Log 记录页修改，用于崩溃恢复和持久性；
  • Undo Log 保存旧版本，用于事务回滚与 MVCC；
  • Binlog 位于 Server 层，用于复制、增量恢复和数据订阅。

长时间保持旧快照的事务会迫使 InnoDB 保留更多 Undo、阻碍 Purge；包含大量写入的长事务还会增加回滚、故障恢复和复制应用成本。即使事务没有持有很多行锁，也可能给系统留下很长的历史版本链。

事务边界

事务应只覆盖必须原子完成的数据库操作：

  • 不在事务内等待用户输入；
  • 不在事务内调用不可控的外部 HTTP/RPC；
  • 不在事务内做大文件处理或长时间计算；
  • 不把整个 Service 类默认包进事务；
  • 写方法显式声明事务，查询按一致性需要选择；
  • 明确异常是否触发回滚；
  • 保证注解调用真正经过 Spring 代理，避免同类内部调用失效。

本地数据库事务无法自动覆盖消息队列和第三方服务。需要“写库 + 发消息”时，可以使用 Transactional Outbox：业务数据和 Outbox 记录在同一事务提交，再异步发布并由幂等消费者处理。

InnoDB 锁与 Metadata Lock

常见锁类型

  • Record Lock：锁定索引记录；
  • Gap Lock：锁定索引记录之间、之前或之后的间隙，主要阻止插入；
  • Next-Key Lock：Record Lock 与前方 Gap Lock 的组合；
  • Intention Lock：表级意向锁，协调表锁与行锁；
  • Auto-Increment Lock：与自增值分配有关；
  • Metadata Lock：由 MySQL Server 层管理，用于保护表定义；普通查询也会持有，DDL 需要与它协调。

意向锁通常由 InnoDB 自动管理，业务无需手工申请。线上更常见的问题是范围锁、缺少索引导致的大范围扫描，以及长事务阻塞 DDL。

锁加在索引访问路径上

锁定读、UPDATE 和 DELETE 通常会锁住执行过程中扫描到的索引记录。WHERE 条件最终过滤掉的行，也可能已经在扫描阶段被锁定。

如果没有合适索引而不得不扫描全表，几乎所有记录都可能被锁住，效果接近表级阻塞。此时“SQL 最终只更新一行”并不能说明锁范围只有一行。

使用二级索引做排他锁定时，InnoDB 还需要锁定对应的聚簇索引记录。因此分析锁冲突时，要同时看二级索引和主键访问。

唯一等值查询与范围查询

使用完整唯一索引精确找到一条已有记录时，通常只需要 Record Lock。非唯一索引、只使用唯一索引的一部分，或范围条件，可能产生 Gap Lock 或 Next-Key Lock。

在 REPEATABLE READ 下，下面的锁定读可能阻止其他事务向目标范围插入：

START TRANSACTION;

SELECT id
FROM task_record
WHERE tenant_id = 42
  AND status = 0
  AND created_at < '2026-09-08 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 10
FOR UPDATE;

具体锁范围取决于所选索引、边界值、数据是否存在和隔离级别。不要只看 WHERE 文本推断，应结合执行计划和锁监控验证。

READ COMMITTED 会减少许多普通范围搜索中的 Gap Lock，但唯一性检查、外键检查等场景仍可能使用间隙相关锁。切换隔离级别不是“一键消灭死锁”。

FOR UPDATE、FOR SHARE、NOWAIT 与 SKIP LOCKED

  • FOR UPDATE 用于即将修改的记录；
  • FOR SHARE 用于需要阻止并发修改、但允许其他共享读取的场景；
  • NOWAIT 无法立即取得锁时直接失败；
  • SKIP LOCKED 跳过已锁记录，适合多个 Worker 竞争领取独立任务。

SKIP LOCKED 返回的是一个不完整视图，不能用于要求精确一致结果的普通业务查询。它适合工作队列，是因为“暂时跳过这条任务”本身就是允许的语义。

Metadata Lock

一个很短的 ALTER TABLE 也可能长时间等待，因为前面有未提交事务持有 Metadata Lock。更糟糕的是，等待中的 DDL 还可能阻塞后续新查询，形成队列。

执行 DDL 前应检查长事务、空闲事务和当前锁等待。应用要设置合理超时，避免连接开启事务后长时间闲置。

Metadata Lock 不会出现在 InnoDB 数据锁表中，应通过专用表查看：

SELECT * FROM performance_schema.metadata_locks;

死锁与锁等待

死锁并不等于数据库坏了

典型死锁：

Transaction A: locks row 1 → waits for row 2
Transaction B: locks row 2 → waits for row 1

InnoDB 检测到环后会选择一个事务回滚，让其他事务继续。应用必须能够识别死锁错误，并安全地重试整个事务，不能只重试最后一条 SQL。

死锁重试需要：

  • 操作具备幂等性；
  • 次数有上限；
  • 使用短暂退避和随机抖动；
  • 每次从事务开始重新读取状态；
  • 超过上限后记录上下文并告警。

减少死锁

  • 所有代码按一致顺序更新表和记录；
  • 事务尽量短，及时提交；
  • 锁定查询使用正确索引，缩小扫描范围；
  • 大批量更新拆成小批次；
  • 避免事务中等待外部依赖；
  • 热点记录考虑条件更新、队列化或拆分；
  • 删除不用的索引，减少单行写入需要修改的索引记录；
  • 不要把提高锁等待超时当成根因修复。

降低隔离级别可能减少部分锁冲突，但写操作仍然会死锁。死锁的核心是资源获取顺序和锁范围，不只是隔离级别。

如何排查

最近一次 InnoDB 死锁可以从下面的命令查看：

SHOW ENGINE INNODB STATUS;

当前 InnoDB 数据锁和等待关系可以从 Performance Schema 或 sys Schema 查看：

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

排查时记录：

  • 两个事务各自执行的完整 SQL 和参数；
  • 已持有和正在等待的索引锁；
  • 事务开始时间与最后一条语句；
  • 执行计划和扫描行数；
  • 是否总以相同顺序访问记录；
  • 是否存在批量操作、外部调用或人为停顿。

锁等待超时和死锁不同：死锁形成环，通常能被立即检测；锁等待可能只是单向等待，直到持锁事务提交或达到超时。

常用并发写模式

条件更新

不要先查询剩余额度，再在应用中计算后更新。可以让数据库原子判断：

UPDATE resource_quota
SET remaining = remaining - ?,
    version = version + 1
WHERE id = ?
  AND remaining >= ?;

检查受影响行数：1 表示成功，0 表示条件不满足或记录不存在。它通常比“查询 + 锁定 + 更新”更短，也更不容易发生丢失更新。

乐观锁

UPDATE task_record
SET status = ?,
    version = version + 1,
    updated_at = ?
WHERE id = ?
  AND version = ?;

乐观锁适合冲突较少、失败可以重新读取和重试的场景。热点记录冲突很高时，乐观锁会造成大量失败和重试，可能不如串行队列或重新拆分数据模型。

悲观锁与状态机

需要读取当前状态后执行复杂校验时，可以使用 SELECT ... FOR UPDATE，但应：

  1. 用主键或完整唯一键精确定位；
  2. 加锁后再次校验状态；
  3. 在同一个短事务内完成更新；
  4. 远程副作用放到提交之后；
  5. 用唯一键或状态流转规则防止重复执行。

幂等插入

请求 ID、事件 ID 或业务唯一键应落到唯一索引。冲突时应区分：

  • 同一个请求的安全重放：返回原结果；
  • 不同请求误用了同一业务键：拒绝并告警；
  • 历史失败是否允许恢复：由状态机决定。

不要无条件使用 INSERT IGNORE 吞掉所有错误，它还可能忽略本应暴露的数据问题。ON DUPLICATE KEY UPDATE 也要确认不会误触发其他唯一键。

连接池

HikariCP 等连接池解决的是复用连接和应用侧排队，不是数据库扩容。

关键参数

  • maximumPoolSize：单实例最大连接数；
  • connectionTimeout：获取连接最长等待时间；
  • idleTimeout：空闲连接回收时间；
  • maxLifetime：连接最大生命周期；
  • keepaliveTime：必要时保持连接活性；
  • validationTimeout：验证连接的时间上限。

不要配置无限连接等待。下游已经拥塞时，让请求无限堆积只会耗尽 Web 线程并扩大故障。

maxLifetime 应短于数据库、代理或网络设备回收连接的时间，并加入适当抖动，避免所有实例同时重建连接。

连接池大小

估算时至少考虑：

总数据库连接 = 实例数 × 每实例连接池上限 + 管理与后台连接

这个总数必须小于数据库可承受的有效并发，而不只是小于 max_connections。大量正在等待锁或争用 CPU 的连接不会提高吞吐。

监控连接池的活跃数、空闲数、等待线程、获取耗时、超时和连接使用时长。连接泄漏通常表现为活跃连接持续不归还，而不是突然出现一个明显异常。

连接归还池之前，事务状态、隔离级别、只读标志和 Session 变量必须被正确恢复。不要假设每次拿到的都是一条“全新连接”。

主从复制与读写分离

读写分离提高读取能力，也引入复制延迟。下面这些查询通常需要读主库或使用会话级读写一致性策略：

  • 写入后的立即确认；
  • 幂等判断；
  • 状态机下一步决策；
  • 安全与权限变更后的校验；
  • 依赖最新余额、库存或配额的操作。

历史列表、报表和允许短暂陈旧的页面更适合读从库。

不要把从库“查不到”立即解释成数据不存在，它也可能还没复制过来。重试从库不一定有用，因为每次都可能落到同样落后的副本。

复杂报表放到从库也不是没有成本。大查询会占用 CPU、I/O 和 Buffer Pool，导致复制线程追赶变慢。在线只读副本和分析副本最好按负载隔离。

故障切换后要验证事务是否真正提交、客户端是否刷新拓扑、旧主是否发生脑裂，以及只读实例是否错误承接写流量。

DDL 与 Schema 迁移

Online DDL 仍然需要敬畏

MySQL 8 的 DDL 算法主要包括：

  • INSTANT：主要修改元数据，通常最快；
  • INPLACE：尽量避免完整拷贝，但可能重建数据；
  • COPY：创建新表并复制，代价通常最大。

不同版本、字段类型、索引和表结构支持程度不同。关键变更可以显式指定期望算法，让不满足条件时直接失败，而不是悄悄退化为更重操作：

ALTER TABLE task_record
    ADD COLUMN priority TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ALGORITHM = INSTANT;

创建普通二级索引通常可以使用 In-place 并允许并发 DML，但仍需依据当前版本与表定义验证：

ALTER TABLE task_record
    ADD INDEX idx_tenant_priority (tenant_id, priority),
    ALGORITHM = INPLACE,
    LOCK = NONE;

“允许并发写”不代表没有风险。排序建索引期间会读取大量数据、写临时排序文件、产生脏页并触发刷盘和 Checkpoint；并发 DML 还会写入 Online DDL 临时日志，并照常生成 Redo。整个过程可能增加响应延迟、磁盘使用和复制延迟；开始与结束阶段还要取得 Metadata Lock。

Expand and Contract

兼容迁移的一般流程：

  1. Expand：先添加新字段、表或索引，旧版本仍可运行；
  2. 兼容写入：新代码双写或写入兼容格式；
  3. 回填：按主键小批量推进，可暂停、可恢复；
  4. 切换读取：用开关灰度切到新结构；
  5. 停止旧写：确认没有旧实例和旧消费者；
  6. Contract：最后删除旧字段、索引和兼容代码。

数据库回滚往往比代码回滚困难，因此 Schema 默认应该向前、向后都兼容一段时间。

大表变更检查

  • 表与索引大小；
  • 峰值写入量和 DDL 预计时长；
  • 临时空间与磁盘余量；
  • 长事务和 Metadata Lock；
  • Redo、Undo 和复制延迟；
  • 是否重建聚簇索引；
  • 失败后的清理与重试；
  • 监控、停止条件和回滚路径；
  • 备份是否真实可恢复。

列在表里的物理顺序通常没有业务价值，不要为了“看起来整齐”触发更重的 DDL。一次合并多个 ALTER 可以减少重复扫描，也可能让原本轻量的操作被迫采用更重算法，需要按实际版本验证，不能机械套规则。

极大表可能需要 gh-ost、pt-online-schema-change 或数据库平台提供的在线变更能力，但要先理解它们对触发器、外键、Binlog、复制拓扑和故障切换的要求。

大表、分区与分库分表

单表多少行需要拆

没有统一答案。判断指标应该包括：

  • 核心查询的 P95/P99 延迟和扫描量；
  • 索引是否还能装下热点工作集；
  • 写入与页分裂压力；
  • DDL、备份和恢复窗口；
  • 历史数据是否能够归档；
  • 是否存在明确的数据生命周期；
  • 单机容量与未来增长速度。

几亿行表的主键点查仍可能很快，一张几百万行但缺少正确索引的表也可能很慢。先优化访问路径、归档冷数据和拆分大字段，再考虑物理分片。

分区表

分区适合按时间管理生命周期、快速删除旧分区，以及查询能稳定携带分区键的场景。它不是自动性能优化：

  • 没有分区裁剪时仍会访问多个分区；
  • 每个分区仍需要正确索引；
  • 唯一键通常必须包含分区列；
  • 分区过多会增加元数据和优化成本；
  • 只按主键扫描但不带分区条件，可能无法裁剪历史分区。

先通过执行计划确认 partitions 和实际扫描范围，再判断分区是否带来收益。

分库分表

分库分表是容量与吞吐手段，不是普通 SQL 优化工具。启用前先回答：

  • 分片键是否出现在绝大多数核心查询中；
  • 热点用户或租户会不会让某个分片过热；
  • 跨分片排序、分页、聚合和 JOIN 如何处理；
  • 全局唯一 ID 如何生成；
  • 扩容重分片如何双写、校验和切换；
  • 后台任务与数据修复如何精确路由；
  • 事务和唯一约束的边界变成了什么。

无法携带分片键的查询会扇出到所有节点。随着分片数量增长，它会从一次查询变成一次小型分布式任务。

Java、MyBatis 与数据库边界

分层

建议保持：

Controller → Service → Repository → Mapper → MySQL

Service 表达业务和事务语义，Repository 统一数据访问，Mapper 负责 SQL 映射。Service 直接散落 Mapper 调用，后续很难统一读写路由、批量访问、缓存失效和审计。

参数化查询

MyBatis 的值参数使用 #{}，由 PreparedStatement 绑定。${} 是文本替换，不能接收原始用户输入。

表名、列名和排序方向不能通过 ? 占位符绑定。确实需要动态标识符时，在 Java 代码中把用户选项映射到固定白名单，再传给 SQL。不要把“只允许字母”当成足够的授权与语义校验。

ORM 不会自动优化 SQL

MyBatis-Plus 的 Wrapper 可以减少 CRUD 样板代码，但同样可能产生：

  • 没有索引的过滤；
  • 无界列表；
  • N+1；
  • 过宽的 SELECT；
  • 条件顺序看似漂亮、访问路径却很差的 SQL。

简单操作优先使用框架已有方法；复杂 JOIN、批处理和需要 Hint 的查询显式写 SQL，并说明索引假设。

数据库测试

自定义 SQL、事务、锁、JSON、排序规则和分片逻辑应使用真实 MySQL 版本做集成测试。H2 适合快速测试简单 CRUD，但它的方言、锁和执行计划不能证明 MySQL 行为。

Testcontainers 可以为测试启动一次性 MySQL。测试要自己准备数据，覆盖：

  • 空数据与边界值；
  • 重复键和并发写；
  • 事务回滚；
  • 时间与小数精度；
  • 分页稳定性；
  • 实际查询计划依赖的索引；
  • Schema 迁移从旧版本升级。

监控与容量

数据库指标

至少监控：

  • QPS、提交、回滚与错误率；
  • 查询 P50、P95、P99；
  • 活跃连接、运行线程和连接拒绝；
  • Buffer Pool 命中、脏页与刷新压力；
  • Redo 生成与 Checkpoint 压力；
  • 行锁等待时间、锁等待数量和死锁；
  • Undo History List 与长事务；
  • 磁盘 IOPS、吞吐、延迟和剩余空间；
  • 主从复制延迟与复制错误；
  • 临时表、排序和全表扫描趋势。

单个指标很少能说明问题。例如 Buffer Pool 命中率很高，仍可能有一条热点 SQL 扫描大量缓存页，把 CPU 打满。

应用指标

  • 连接池获取等待和超时；
  • 按 SQL Digest 聚合的调用量、错误和耗时；
  • 每次请求的数据库调用次数；
  • 批处理扫描、更新、跳过和失败数量；
  • 死锁重试与最终失败；
  • 读主库、读从库的比例；
  • 关键查询返回的最老数据年龄。

指标标签不要放用户 ID、请求 ID 或完整 SQL。使用有限的查询名、结果和错误类别，详细参数留在经过脱敏的日志或 Trace 中。

慢查询治理

慢日志和 Performance Schema 应按 SQL Digest 聚合，先处理“总耗时最大”的查询，不要只盯单次最慢：

总数据库成本 ≈ 单次耗时 × 调用次数

一条 20 毫秒、每秒执行数千次的 SQL，通常比一天一次的 10 秒报表更值得优先优化。

线上排障流程

1. 先确认现象

  • 是单条 SQL 变慢，还是所有数据库请求都变慢；
  • 是读取、写入、建连还是提交慢；
  • 是平均延迟上升，还是 P99 尾延迟；
  • 是否与发布、DDL、流量、备份或故障切换同时发生；
  • 主库、从库和某个分片是否表现一致。

2. 找到真实 SQL 与参数

同一个 SQL 模板在不同参数下可能有完全不同的选择性。排障需要 SQL Digest，也需要经过脱敏的代表性参数、返回行数和调用频率。

3. 查看执行计划

比较估算与实际：

  • 是否换了索引；
  • 是否统计信息失真；
  • 数据分布是否倾斜；
  • 是否扫描、回表或排序过多；
  • 是否因为返回列变多失去覆盖索引；
  • 是否因为参数类型变化发生隐式转换。

4. 检查等待

查询“正在做什么”还不够，还要看“正在等什么”：锁、Metadata Lock、连接、磁盘、Redo、CPU、网络还是复制。

5. 选择最小风险处置

  • 先限流或关闭非关键入口；
  • 暂停批处理、报表、回填或 DDL；
  • 必要时终止明确的阻塞会话，但先确认事务影响；
  • 回滚有问题的 SQL 或路由；
  • 再考虑加索引、改 Schema 或扩容。

不要在故障中直接执行未经验证的大表索引创建。一个为了救火启动的 DDL，可能成为新的故障源。

6. 修复后防复发

  • 添加查询、锁等待和容量监控；
  • 保存故障时的执行计划与数据分布；
  • 为关键 SQL 增加集成或性能回归测试；
  • 给 DDL、回填和故障处置补充 Runbook；
  • 记录触发条件、停止条件和恢复步骤。

安全与数据保护

  • 数据库账号采用最小权限，应用账号不应拥有任意 DDL 或管理权限；
  • 读写账号分离，报表和在线流量按资源隔离；
  • JDBC 使用 TLS 并校验证书；
  • 凭据来自 Secret Manager、KMS 或 Vault，不写进代码和镜像；
  • 值参数全部使用预编译绑定；
  • 动态表名、列名和排序方向采用服务端白名单；
  • 密码使用 Argon2 或 BCrypt 哈希，不能可逆加密；
  • 必须检索的敏感字段设计 Tokenization、Hash 辅助列或受控索引；
  • 备份、快照和 Binlog 同样属于敏感数据，需要加密、访问控制和保留策略；
  • 日志和慢查询样本必须脱敏，不记录 Token、密钥和完整个人数据；
  • 生产数据不能随意复制到本地或共享测试环境；
  • 定期验证备份恢复，而不是只检查备份任务显示成功。

字段加密会影响等值查询、排序、范围查询和唯一约束，应在建模阶段设计，不能上线前简单“给字段套一层加密”。

常见误区

  • “NULL 不进入索引”——错误，IS NULL 可以使用 B-Tree 索引。
  • “BETWEEN 一定使索引失效”——错误，它通常可以形成范围扫描。
  • “看到 key 不为空就说明 SQL 很快”——错误，还要看实际扫描、回表、排序和循环次数。
  • “联合索引永远按选择性从高到低排列”——不完整，还要考虑查询前缀、排序和分页。
  • “普通 SELECT 会加 Next-Key Lock”——在常见隔离级别下，普通一致性读通常不加记录锁。
  • “行锁只锁最终返回的行”——错误，锁范围取决于访问路径中扫描到的索引记录。
  • “改成 READ COMMITTED 就没有死锁”——错误，写操作和锁顺序仍会产生死锁。
  • “连接池调大可以提高吞吐”——只有数据库还有余量时才可能成立。
  • “Online DDL 对线上没有影响”——它仍可能消耗大量资源并等待 Metadata Lock。
  • “表超过两千万行必须分表”——没有统一阈值，应由容量和访问模式决定。
  • “分区表自动让查询变快”——只有发生有效裁剪并有正确索引时才可能受益。
  • “加缓存可以解决所有慢 SQL”——缓存会引入一致性和失效问题，根因仍可能存在。

上线前检查清单

表结构

  • 有短、稳定、显式的主键
  • 字段类型、Unsigned、字符集和排序规则语义一致
  • 金额和时间的精度、时区、舍入规则明确
  • 热行中没有不必要的大字段
  • 唯一性由数据库唯一约束兜底

查询与索引

  • 索引围绕完整查询设计，不是给每列分别加索引
  • 核心 SQL 使用代表性数据执行过 EXPLAIN ANALYZE
  • 估算行数与实际行数没有严重偏差
  • 没有 N+1、无界列表、深 OFFSET 和不必要的 SELECT *
  • 动态排序字段经过白名单映射
  • 冗余索引和写放大已经评估

事务与锁

  • 事务范围短，不包含远程调用和长计算
  • 并发写使用条件更新、版本号、唯一键或精确锁定
  • 多表、多行更新顺序一致
  • 死锁能够重试整个事务且有次数上限
  • 锁定查询使用正确索引并验证过锁范围
  • 应用不存在长期空闲未提交事务

连接与复制

  • 连接获取、查询和事务都有有限超时
  • 全集群连接总量不超过数据库有效承载能力
  • 连接池等待、泄漏和超时有监控
  • 写后读、幂等和状态决策明确走主库
  • 复制延迟、错误和故障切换经过演练

DDL 与恢复

  • 明确 DDL 算法、锁级别、耗时和磁盘需求
  • 检查长事务与 Metadata Lock
  • 大表回填可限速、暂停、恢复和重复执行
  • Schema 变更遵循 Expand and Contract
  • 发布、验证、停止和回滚步骤写入 Runbook
  • 备份完成过真实恢复验证

参考资料

最后

MySQL 使用经验最终可以浓缩成四句话：

  1. 用索引减少需要扫描的数据；
  2. 用短事务减少持锁时间；
  3. 用边界和监控控制资源；
  4. 用可恢复的迁移与任务应对变化。

不要迷信某个固定行数、某条索引口诀或某个神奇参数。把 SQL、数据分布、执行计划、锁和资源等待放在一起看，数据库问题通常会从“玄学”变成可以验证的工程问题。