Deep Dive 1：锁、gap/next-key 与"无辜"死锁
默认 REPEATABLE READ 下，InnoDB 用 next-key lock 防幻读。生产中最反直觉的是：两个事务插入不同键也可能死锁——因为插入要检查插入位置的 gap 是否有锁、唯一性检查要加 record lock，而两个事务加锁顺序相反即成环。监控上 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段 + performance_schema.data_locks/data_lock_waits 是标准诊断组合。应用层铁律：捕获 1213 后重试整个事务，而不是重试单条语句。降到 READ COMMITTED 能砍掉大部分 gap 锁，但唯一键冲突、外键检查、加锁顺序反转照样死锁——隔离级别不是银弹。
来源：planetscale/database-skills（deadlocks / row-locking-gotchas）社区共识；villagesql-docs 社区共识。观察版本：8.0/8.4。
Deep Dive 2：长事务、undo/purge 与大事务的"机制税"
InnoDB 的旧版本存在 undo log（8.0 起独立 undo 表空间文件），后台 purge 线程在确认无事务再可见旧版本后清理。一旦有长查询、长事务或 idle in transaction 持有老 Read View，purge 被堵住：History List Length 飙涨、undo 空间膨胀、版本链越拉越长导致读变慢、旧页挤压 buffer pool。MySQL Bug #117448 的生产报告里 HLL 达数亿，释放老事务后 purge coordinator 回读冷 undo 反成瓶颈，云盘上更严重。这是 MySQL 版的"VACUUM 阻塞"——PG 的税交在表膨胀上，MySQL 的税交在 undo/purge 上，只是位置不同。大事务另有三宗罪：超大 binlog 事件、复制延迟放大、回滚耗时与锁持有时间拉长。
来源：bugs.mysql.com #117448 社区实测；discuss.google.dev purge 讨论 社区共识。观察版本：8.0。
Deep Dive 3：异步复制延迟、GTID 切主与 read-after-write
默认异步复制下，主库提交不等待从库——这是 RPO>0 的根。GTID + 自动定位让"从库跟谁"不再靠文件名位点，但切主前必须比对 Executed_Gtid_Set，否则切过去才发现事务没到。半同步 AFTER_SYNC 用约一次 RTT 换"至少一个从库收到"的提交保证，是延迟与持久性的经典 trade-off。大事务、COPY 算法 DDL、慢查询都会把延迟峰值拉高；Seconds_Behind_Source 在从库空闲时会归零、有时钟/单线程应用盲区，严肃的延迟监控要用 heartbeat 表或 GTID 位置差。Read-after-write 的三条路：读主（主库扛不住）、sticky session（路由复杂）、WAIT_FOR_EXECUTED_GTID_SET（读延迟换一致性）——没有免费选项。
来源：percona-lab mysql-replication-ha SKILL 社区共识；planetscale replication-lag 社区共识。观察版本：8.0/8.4。
Deep Dive 4：Online DDL 与 MDL——"在线"二字的有效期
ALTER TABLE 三档算法：INSTANT（只改元数据，近乎即时）、INPLACE（后台重建，多数场景允许 DML）、COPY（全表复制，长阻塞写）。但三档在开始/提交阶段都要拿 metadata lock：一个长事务就能卡住 DDL，而排队中的 DDL 又会挡住后面所有请求——MDL 队头阻塞是生产 DDL 事故的头号形态。改列类型、字符集转换常被迫走 COPY。铁律：显式指定 ALGORITHM=INSTANT/INPLACE + LOCK=NONE，让不满足条件时失败而不是悄悄降级为阻塞。大热表用 gh-ost（依赖 binlog）或 pt-osc（依赖触发器），两者都吃 IO/复制容量，且最后的 swap 照样要 MDL。复制环境里 COPY DDL 还会堵住从库 relay 应用，造成延迟尖峰。
来源：nuggocto/dotfiles online-ddl 社区共识；villagesql zero-downtime-schema-changes 社区共识。观察版本：8.0/8.4。
Deep Dive 5：分库分表困境——MySQL 扩写的真正天花板
MySQL 原生无分片，扩写靠 Vitess/ShardingSphere/中间件。Vitess 的真实约束（多份 2026 年 skill 文档一致）：
- 跨分片 JOIN 支持但昂贵（scatter-gather / nested loop），聚合在 VTGate 内存里归并，大结果集慢；
- 事务三档：
SINGLE（拒绝跨分片）、MULTI（默认，尽力而为，顺序提交，可能部分提交）、TWOPC（原子但不隔离——隔离级别只在分片本地有效）；
- VTGate 不支持存储过程/触发器/事件、
LOCK TABLES/GET_LOCK；外键支持有限，官方建议应用层保证引用完整性；
- ID 要用 Vitess Sequence 或应用生成（UUID/雪花），否则跨分片冲突。
更深的是运维与语义双重成本：选错 shard key 是经典灾难（hash(key)%N 从 4 分片扩到 5 要搬 80% 数据，一致性哈希才只搬 1/N）；reshard 是在线 VReplication 流程但依然是重操作；相关子查询跨分片可能直接失败。结论：分片把"数据库的问题"变成了"分布式系统的问题"，而 MySQL 内核对此不提供任何原生帮助——这是它与 CockroachDB/TiDB 系最根本的分水岭。
来源：planetscale/database-skills vitess query-serving 社区共识；dbengines planetscale 社区共识；forge-orm SHARDING 社区共识。观察版本：Vitess v21–v24（2026）。
Deep Dive 6：可观测性的税（Performance Schema）
performance_schema + sys 是 MySQL 内建可观测的核心，默认 instrument 集生产可用。但"全开"的代价是真实的：LINE 旧实测在特定 sysbench 下全开 instrument 降 TPS 约 15%（旧版特定负载，不可推广）；Percona 旧材料也有类似量级结论。生产事故时正确的节奏是平时选择性启用、救火时临时放大。另两个暗坑：慢日志阈值过低本身就是 IO 负载；EXPLAIN ANALYZE 真实执行语句，对写语句用它做诊断等于又写了一遍。
来源：LINE engineering blog 社区实测；Percona webinar 材料 社区实测。观察版本：5.6/5.7 时代实测，机制在 8.x 延续。
Deep Dive 7：TDE、密钥管理与社区/企业版边界
机制本身是扎实的：两级密钥、主密钥轮换、覆盖表空间/redo/undo/doublewrite。但生产落地的三个真相：① 社区版只有本地文件 keyring——key 文件和数据的备份必须分离存放、异地容灾，否则"加密了但 key 和数据一起丢"；② binlog/relay log/审计日志加密是分别配置的，漏配等于链条断裂；③ RDS/Aurora 走云盘 KMS 而非 MySQL 原生 TDE，自建与云托管的加密责任模型不同，迁移时要重新对。TDE 防的是"硬盘被偷"，不防 SQL 注入、不防删库、不防内存 dump——威胁模型要先对齐。
来源：Oracle 官方文档 innodb-data-encryption 官方文档；dev.mysql.com TDE 博客 官方文档。观察版本：8.0/8.4。
Deep Dive 8：版本线、升级与"8.0.38 式惊魂"
LTS/Innovation 双线是好设计（8.4 首个 LTS，9.7 第二个），但 8.0→8.4 升级有真实断裂点：mysql_native_password 默认禁用、expire_logs_days 等变量移除（写进配置则拒绝启动）、mysqlpump 消失、binlog 仅 ROW、TLS 1.0/1.1 移除。升级铁律：先跑 util.checkForServerUpgrade()、先升从库再升主库、升级前 innodb_fast_shutdown=0、in-place 大版本不可逆（回退靠全备或克隆 dry-run）。历史教训：2024 年 8.0.38/8.4.1/9.0.0 出现"表超 1 万张则重启后 crash 且无法再启动"的严重 bug（#115517），Percona 公开建议暂缓升级——提醒我们：LTS 不等于每个小版本都可上生产，patch 版本也要看社区反馈再跟进。
来源：villagesql mysql-80-eol-upgrade 社区共识；aws-samples RDS 升级 playbook 社区共识；thenewstack 报道 Percona advisory 社区实测。观察版本：8.0.38/8.4.1/9.0.0（2024）。
Deep Dive 9：Group Replication / InnoDB Cluster——官方 HA 的适用半径
Group Replication 的认证机制看不到 gap 锁（gap 锁信息出不了 InnoDB），多主模式官方推荐 READ COMMITTED 以对齐本地与分布式冲突检测；认证也不考虑表锁与命名锁（GET_LOCK）；有事务大小上限；表必须有主键（无主键表在组内只读）。InnoDB Cluster（Shell AdminAPI + Router）官方明确：只适合局域网，跨 WAN 部署写性能明显受损；多数据中心正确姿势是 InnoDB ClusterSet（每 DC 一个 Cluster，DC 间异步复制）。手动配的异步复制通道 Cluster 不管，可能脑裂。生产经验（MySQL User Camp 分享）：长事务在集群里是性能杀手，重 DDL 会打断并行应用把从节点打进 RECOVERY。
来源：Oracle 官方 Group Replication Limitations / InnoDB Cluster Limitations 官方文档；slideshare InnoDB Cluster Experience 社区实测。观察版本：8.0/8.4。
Deep Dive 1: Locks, gap/next-key, and "innocent" deadlocks
Under the default REPEATABLE READ, InnoDB uses next-key locks to prevent phantom reads. The most counterintuitive production reality: two transactions inserting different keys can still deadlock — because an insert must check whether the target gap is locked and uniqueness checks take record locks, and reversed lock acquisition order between two transactions forms a cycle. For monitoring, the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS combined with performance_schema.data_locks / data_lock_waits is the standard diagnostic pair. The iron rule at the application layer: after catching error 1213, retry the entire transaction, not the single statement. Dropping to READ COMMITTED removes most gap locks, but deadlocks from unique-key conflicts, foreign-key checks, and reversed lock order remain — isolation level is not a silver bullet.
Sources: planetscale/database-skills (deadlocks / row-locking-gotchas) 社区共识; villagesql-docs 社区共识. Observed versions: 8.0/8.4.
Deep Dive 2: Long transactions, undo/purge, and the "mechanism tax" of large transactions
InnoDB keeps old row versions in the undo log (separate undo tablespace files since 8.0), and a background purge thread cleans them up once no transaction can still see them. As soon as a long query, long transaction, or idle in transaction session holds an old read view, purge stalls: History List Length skyrockets, undo space swells, lengthening version chains slow reads, and old pages squeeze the buffer pool. In the production report of MySQL Bug #117448, HLL reached the hundreds of millions; after releasing the old transactions, the purge coordinator reading back cold undo became the bottleneck itself, worse on cloud disks. This is MySQL's version of "blocked VACUUM" — in PG the tax is paid in table bloat, in MySQL it's paid in undo/purge; only the location differs. Large transactions have three additional sins: oversized binlog events, amplified replication lag, and rollback time plus lock-hold duration stretching out.
Sources: bugs.mysql.com #117448 社区实测; discuss.google.dev purge discussion 社区共识. Observed version: 8.0.
Deep Dive 3: Async replication lag, GTID failover, and read-after-write
Under default async replication, the primary commits without waiting for replicas — that is the root of RPO>0. GTID + auto-positioning means "which primary to follow" no longer depends on file-name positions, but you must compare Executed_Gtid_Set before failover, or you'll discover missing transactions only after the cutover. Semi-sync AFTER_SYNC trades roughly one RTT for a commit guarantee of "at least one replica has received it" — the classic latency-vs-durability trade-off. Large transactions, COPY-algorithm DDL, and slow queries all push lag spikes higher; Seconds_Behind_Source resets to zero when the replica is idle and has clock/single-threaded-apply blind spots, so serious lag monitoring should use heartbeat tables or GTID position deltas. Three options for read-after-write: read from primary (the primary can't take it), sticky sessions (complex routing), WAIT_FOR_EXECUTED_GTID_SET (trading read latency for consistency) — there is no free option.
Sources: percona-lab mysql-replication-ha SKILL 社区共识; planetscale replication-lag 社区共识. Observed versions: 8.0/8.4.
Deep Dive 4: Online DDL and MDL — the expiration date on the word "online"
ALTER TABLE has three algorithm tiers: INSTANT (metadata-only, near-instant), INPLACE (background rebuild, DML allowed in most cases), COPY (full table copy, long write blocking). But all three tiers must take a metadata lock at the start/commit phases: a single long transaction can stall the DDL, and the queued DDL then blocks everything behind it — MDL head-of-line blocking is the #1 shape of production DDL incidents. Changing column types or character sets is often forced into COPY. The iron rule: explicitly specify ALGORITHM=INSTANT/INPLACE + LOCK=NONE so the operation fails when conditions aren't met instead of silently degrading into blocking. For hot large tables, use gh-ost (depends on binlog) or pt-osc (depends on triggers) — both consume IO/replication capacity, and the final swap still needs the MDL. In replication setups, COPY DDL also blocks relay application on replicas, causing lag spikes.
Sources: nuggocto/dotfiles online-ddl 社区共识; villagesql zero-downtime-schema-changes 社区共识. Observed versions: 8.0/8.4.
Deep Dive 5: The sharding dilemma — MySQL's true write-scaling ceiling
MySQL has no native sharding; write scale-out relies on Vitess/ShardingSphere/middleware. Vitess's real constraints (consistent across multiple 2026 skill docs):
- Cross-shard JOINs are supported but expensive (scatter-gather / nested loop), with aggregation merged in VTGate memory — slow for large result sets;
- Three transaction modes:
SINGLE (rejects cross-shard), MULTI (default, best-effort, sequential commits, possible partial commits), TWOPC (atomic but not isolated — isolation is only effective within a single shard);
- VTGate does not support stored procedures/triggers/events,
LOCK TABLES / GET_LOCK; foreign key support is limited, and the official guidance is to enforce referential integrity at the application layer;
- IDs must use Vitess Sequences or be application-generated (UUID/snowflake), otherwise cross-shard conflicts occur.
Deeper still is the dual cost of operations and semantics: picking the wrong shard key is the classic disaster (hash(key)%N moving from 4 to 5 shards relocates 80% of the data; consistent hashing only moves 1/N); resharding is an online VReplication process but still a heavy operation; correlated subqueries across shards may fail outright. Conclusion: sharding turns "database problems" into "distributed systems problems", and the MySQL kernel offers no native help with any of it — this is the most fundamental watershed between MySQL and the CockroachDB/TiDB family.
Sources: planetscale/database-skills vitess query-serving 社区共识; dbengines planetscale 社区共识; forge-orm SHARDING 社区共识. Observed versions: Vitess v21–v24 (2026).
Deep Dive 6: The tax on observability (Performance Schema)
performance_schema + sys is the core of MySQL's built-in observability, and the default instrument set is production-safe. But the cost of "enabling everything" is real: LINE's old benchmark saw roughly a 15% TPS drop with all instruments enabled under a specific sysbench workload (old version, specific workload — not generalizable); old Percona materials reached similar-magnitude conclusions. The correct rhythm in production is selective enablement normally, temporarily widened collection during incidents. Two more hidden pitfalls: a slow-log threshold set too low is itself an IO load; EXPLAIN ANALYZE actually executes the statement — using it to diagnose a write is equivalent to writing it again.
Sources: LINE engineering blog 社区实测; Percona webinar materials 社区实测. Observed versions: 5.6/5.7-era benchmarks; the mechanism carries over into 8.x.
Deep Dive 7: TDE, key management, and the Community/Enterprise boundary
The mechanism itself is solid: two-level keys, master key rotation, coverage of tablespaces/redo/undo/doublewrite. But three truths of production deployment: ① Community Edition only has the local file keyring — key files and data backups must be stored separately with off-site disaster recovery, otherwise you get "encrypted, but the key and the data were lost together"; ② binlog/relay log/audit log encryption is configured separately — a missed configuration breaks the chain; ③ RDS/Aurora uses cloud-disk KMS rather than MySQL-native TDE, so self-hosted and cloud-managed deployments have different encryption responsibility models, which must be re-aligned during migration. TDE defends against "the disk gets stolen" — it does not defend against SQL injection, accidental deletion, or memory dumps; align the threat model first.
Sources: Oracle official docs innodb-data-encryption 官方文档; dev.mysql.com TDE blog 官方文档. Observed versions: 8.0/8.4.
Deep Dive 8: Release lines, upgrades, and the "8.0.38 scare"
The LTS/Innovation dual track is a good design (8.4 the first LTS, 9.7 the second), but the 8.0→8.4 upgrade has real breaking points: mysql_native_password disabled by default, removed variables like expire_logs_days (server refuses to start if they're in the config), mysqlpump gone, binlog ROW-only, TLS 1.0/1.1 removed. The iron rules of upgrading: run util.checkForServerUpgrade() first, upgrade replicas before the primary, innodb_fast_shutdown=0 before upgrading, and in-place major upgrades are irreversible (roll back via full backup or cloned dry-runs). Historical lesson: in 2024, 8.0.38/8.4.1/9.0.0 shipped a severe bug where instances with more than ~10,000 tables would crash on restart and could never start again (#115517), and Percona publicly advised holding off the upgrade — a reminder that LTS does not mean every patch release is production-ready; patch versions also deserve a look at community feedback before adoption.
Sources: villagesql mysql-80-eol-upgrade 社区共识; aws-samples RDS upgrade playbook 社区共识; thenewstack coverage of the Percona advisory 社区实测. Observed versions: 8.0.38/8.4.1/9.0.0 (2024).
Deep Dive 9: Group Replication / InnoDB Cluster — the applicable radius of official HA
Group Replication's certification mechanism cannot see gap locks (gap-lock information never leaves InnoDB); multi-primary mode officially recommends READ COMMITTED to align local and distributed conflict detection; certification also ignores table locks and named locks (GET_LOCK); there are transaction size limits; tables must have primary keys (tables without PKs are read-only inside a group). InnoDB Cluster (Shell AdminAPI + Router) is officially LAN-only — cross-WAN deployments suffer noticeably degraded write performance; the correct multi-datacenter posture is InnoDB ClusterSet (one Cluster per DC, async replication between DCs). Manually configured async replication channels are not managed by the Cluster and can split-brain. Production experience (MySQL User Camp talks): long transactions are performance killers inside a cluster, and heavy DDL interrupts parallel apply and drives replica nodes into RECOVERY.
Sources: Oracle official Group Replication Limitations / InnoDB Cluster Limitations 官方文档; slideshare InnoDB Cluster Experience 社区实测. Observed versions: 8.0/8.4.