第 07 篇 · MySQL 深度原理

这道题考察对数据库索引底层存储结构选型动机的理解：为什么关系型数据库统一选择 B+树而非 B树、红黑树、跳表？本质是在磁盘 I/O 模型下，如何让一棵查找树以最少的磁盘页读取覆盖尽可能多的数据。B+树通过"非叶子节点只存索引键不存数据"换取极大的扇出（fan-out），使树高被压缩到 3-4 层即可索引千万级行数；再通过"叶子节点双向链表"使范围查询退化为顺序 I/O。理解这两点就能回答一切 B+树相关问题，包括聚簇索引、回表、页分裂、为什么 MongoDB 早期用 B树而 InnoDB 用 B+树。

二叉查找树 → 平衡二叉树（AVL/红黑树）：内存场景的查找结构，树高 O(logN)。问题在于磁盘场景下每个节点一次 I/O，百万数据需 20+ 次 I/O，完全不可接受。需要降低树高、增加每节点扇出。

B树（1970，Bayer & McCreight）：多路平衡查找树。每个节点既存键也存数据，m 阶 B树每个节点最多 m 个孩子、m-1 个键值对。树高显著降低（约 log_m N）。早期文件系统（如 ReiserFS）与 MongoDB（3.0 前 MMAPv1 引擎）采用 B树。痛点：范围查询要中序回溯遍历多层级，随机 I/O 多；节点存数据导致单页能放的键少、扇出受限。

B+树：B树变体——非叶子节点只存索引键（不存数据/卫星数据全部下沉到叶子），叶子节点存全部数据并按主键有序组织成双向链表。InnoDB、PostgreSQL、Oracle、SQL Server 全部采用 B+树。它解决了 B树范围查询低效与扇出受限两大痛点，成为 OLTP 数据库的事实标准。

LSM树（Log-Structured Merge Tree，1996 Patrick O'Neil）：为写密集场景设计的另一种范式——写操作先追加到内存 MemTable（+ WAL），达到阈值后刷盘成有序 SSTable，后台多层级 Compaction 合并。读需合并查找 MemTable + 多层 SSTable（用布隆过滤器加速）。Bigtable、HBase、LevelDB、RocksDB、Cassandra 采用 LSM。它牺牲读放大与空间放大换取极致写吞吐，与 B+树形成读优化 vs 写优化的根本对立。

趋势：B+树与 LSM 在融合——InnoDB 行格式 REDUNDANT/COMPACT（Antelope 格式，MySQL 5.x 即存在）与 DYNAMIC/COMPRESSED（Barracuda 格式，MySQL 5.5 引入）；8.0 的演进在于默认行格式统一为 DYNAMIC 与原子落盘/原子 DDL，而非首次引入这些行格式；RocksDB 的 LSM 普遍加 Bloom Filter + Partitioned Index 缩小读放大；新一代存储引擎（如 TiDB 的 TiKV 用 RocksDB、Spanner 用 B+树 + Paxos）按负载选型。NewSQL 倾向底层 LSM 存储 + 上层 SQL 接口 + 分布式一致性协议。

B+树 vs B树的核心差异：

InnoDB 的 B+树为何能 3 层索引 2000 万行。假设主键 BIGINT（8 字节）、指针 6 字节，InnoDB 页默认 16KB：
非叶子节点每页可放键值对数 = 16384 / (8+6) ≈ 1170 个；
叶子节点每页放行数据（假设单行 1KB）= 16 行；
三层 B+树可索引 = 1170 × 1170 × 16 ≈ 2189 万行，仅需 3 次磁盘 I/O（根节点与内部节点常驻 Buffer Pool 时实际仅 1 次）。

聚簇索引 vs 二级索引：InnoDB 的聚簇索引（Clustered Index）叶子节点存完整行数据，按主键组织——一张表只有一个。二级索引（Secondary Index）叶子节点存索引列值 + 主键值，查询需要的列不在二级索引时需回表（用主键再查一次聚簇索引）。这与 MyISAM 不同——MyISAM 索引与数据分离，所有索引都指向行的物理地址。

覆盖索引避免回表：若查询列全部包含在二级索引中，直接在二级索引叶子节点返回，无需回表聚簇索引。例如 SELECT id,name FROM t WHERE name='x'，在 (name,id) 索引上即覆盖（InnoDB 二级索引自动含主键）。EXPLAIN 中 Extra 显示 Using index 即覆盖索引。

页分裂与自增主键：B+树叶子节点按主键有序。随机主键（如 UUID）插入导致频繁页分裂、页填充率低、碎片多。推荐自增整型主键——顺序插入追加到叶子节点末尾，写放大最小。UUID 主键可改用雪花算法或存储为 BINARY(16) 并配 uuid_short()。

页大小调优：InnoDB 默认页 16KB。OLTP 高并发点查可用 8KB（减少单页竞争、提升 Buffer Pool 命中率）；OLAP 大范围扫描可用 32KB（MySQL 8.0 innodb_page_size 仅建实例时设定）。

追问 1：为什么不用红黑树或跳表？

红黑树/跳表是二叉/多层链表结构，扇出小（红黑树扇出 2），同等数据量树高远高于 B+树（百万数据红黑树高约 20，B+树高 2-3）。磁盘 I/O 次数 = 树高，红黑树在磁盘场景完全不可用。跳表多用于内存数据库（Redis Zset、LevelDB MemTable），其优势是实现简单、并发友好，但磁盘 I/O 模型下扇出仍不如 B+树。

追问 2：B+树与 LSM 树的本质权衡是什么？如何选型？

B+树是读优化（read-optimized）——数据原地更新，读一次 I/O 命中，但有写放大（随机写 + 页分裂）。LSM 是写优化（write-optimized）——写只追加，顺序 I/O 极快，但读要查多层（读放大）且需 Compaction（空间/写放大）。选型：OLTP 读多写少/要求低延迟读 → B+树（MySQL/PG）；写密集/时序/海量 KV → LSM（HBase/Cassandra/RocksDB）。NewSQL（TiDB）用 LSM 底层 + 分布式 SQL 折中。

这道题考察对 InnoDB 多版本并发控制（Multi-Version Concurrency Control）底层实现的深度理解：如何在不加读锁的情况下实现读写并发、又如何仅通过"ReadView 生成时机"的差异切换 RC（读已提交）与 RR（可重复读）。本质是用 undo log 版本链 + ReadView 可见性算法，让每个事务看到数据的一个一致性快照，读写互不阻塞。掌握版本链结构、ReadView 组成、可见性判断四步算法，就能解释一切 MVCC 现象。

SQL 标准的四个隔离级别（1992）：读未提交（RU）、读已提交（RC）、可重复读（RR）、串行化（Serializable）。标准基于锁实现——RC 用短读锁、RR 用长读锁、Serializable 全表锁。锁实现下读写互相阻塞，并发度低。

基于锁的困境：读阻塞写、写阻塞读，高并发 OLTP 下性能不可接受。PostgreSQL 率先在 1980s 后期引入 MVCC 思想——为每个事务保留数据的一个版本快照，读走快照、写生成新版本，读写分离。

InnoDB MVCC：InnoDB 在 RR 级别用 MVCC 实现快照读（普通 SELECT 不加锁），用 Next-Key Lock 解决当前读的幻读。关键设计：同一套 undo 版本链，仅靠 ReadView 生成时机的不同区分 RC 与 RR——RC 每条 SELECT 生成新 ReadView，RR 仅事务第一条 SELECT 生成一次并复用。这是 MySQL 最优雅的设计之一。

趋势：MVCC 已是关系型数据库标配（PostgreSQL/Oracle/SQL Server 均有）。演进方向是减少 undo 回滚段膨胀（长事务堆积 undo 导致性能下降）——MySQL 8.0 引入独立的 undo tablespace 与 truncate 回收；PostgreSQL 的 VACUUM 清理死元组。分布式数据库（TiDB）用 MVCC + 单 TSO 时间戳实现全局快照隔离。

三大组件：隐藏字段 + undo 版本链 + ReadView。

每行数据有两个隐藏字段：DB_TRX_ID（6 字节，最后修改该行的事务 ID）、DB_ROLL_PTR（7 字节，回滚指针，指向 undo log 中该行的上一版本）。每次 UPDATE 不是原地改，而是把旧版本写入 undo log，新版本写回数据页，roll_ptr 串联成版本链。

ReadView 结构（事务执行快照读时生成）：

• m_ids：生成 ReadView 时活跃（未提交）事务 ID 列表
• min_trx_id：m_ids 中最小值
• max_trx_id：下一个将分配的事务 ID（即系统最大事务 ID + 1）
• creator_trx_id：生成该 ReadView 的事务自身 ID

可见性判断四步算法（对版本链上每个版本的 trx_id 判断）：

if (trx_id == creator_trx_id)
    // 版本是自己改的，可见
    return VISIBLE;
else if (trx_id < min_trx_id)
    // 修改在 ReadView 生成前已提交，可见
    return VISIBLE;
else if (trx_id >= max_trx_id)
    // 修改在 ReadView 生成后才开启的事务，不可见
    return INVISIBLE;
else if (m_ids.contains(trx_id))
    // 修改事务在生成 ReadView 时仍活跃，不可见
    return INVISIBLE;
else
    // 修改事务在 ReadView 生成前已提交，可见
    return VISIBLE;

不可见时，沿 roll_ptr 找上一版本，重复判断，直到可见版本或链尾（NULL）。

RC 与 RR 的唯一区别：ReadView 生成时机

长事务是 MVCC 的头号敌人：长事务的 ReadView 长期存在，导致其生成时活跃事务产生的 undo 版本无法被 purge 线程回收，undo 表空间膨胀、history list 增长，影响所有事务性能。线上应监控 information_schema.innodb_trx 事务时长、SHOW ENGINE INNODB STATUS 的 history list length。

RC 还是 RR：阿里、美团等大厂很多业务默认用 RC而非 MySQL 默认的 RR——RC 下间隙锁更少（无 Next-Key Lock 的间隙锁部分）、死锁概率低、并发更好，且业务层用乐观锁/唯一索引保证一致性即可。RR 主要价值在 binlog statement 格式下的主从一致性，但 row 格式已无此约束。选 RC 需确保业务不依赖 RR 的防幻读。

快照读 vs 当前读混用陷阱：RR 下 SELECT 是快照读（看旧值），但 UPDATE 是当前读（看最新值）。先 SELECT 判断再 UPDATE 的逻辑在 RR 下可能基于过期快照决策，需用 SELECT ... FOR UPDATE 当前读加锁。

追问 1：RR 下 MVCC 完全解决幻读了吗？

没有完全解决。快照读由 MVCC 解决幻读（同一事务多次范围快照读结果一致）；但当前读（FOR UPDATE/UPDATE/DELETE）若不加间隙锁会读到新插入的行产生幻读。InnoDB 在 RR 下对当前读用 Next-Key Lock（临键锁 = 记录锁 + 间隙锁）锁住查询范围及间隙，阻止其他事务在间隙内插入，从而解决当前读幻读。但若事务中先快照读后当前读，仍可能"突然看到"新行——这是 MVCC 与锁边界处的已知行为。

追问 2：MVCC 的 undo 版本链何时清理？

InnoDB 后台 purge 线程负责清理。清理条件：某 undo 版本对所有活跃事务的 ReadView 都不可见（即没有任何活跃事务可能再读到它）。因此长事务会阻止 purge——长事务持有的 ReadView 越老，越多 undo 版本无法回收。8.0 起 undo tablespace 可独立 truncate 回收物理空间。

这道题考察 InnoDB 锁体系的精确理解：记录锁、间隙锁、临键锁三种行锁算法的加锁范围与作用，以及它们在不同隔离级别下的行为差异。本质是如何用最小粒度的锁保证事务隔离性，同时最大化并发。InnoDB 行锁锁的是索引而非数据行本身——这是理解所有锁行为的关键前提。掌握加锁规则就能预测任意 SQL 的加锁范围、定位死锁。

表锁时代（MyISAM/Memory）：写锁全表互斥，并发度极低，仅适合读多写少。

行锁引入（InnoDB）：锁粒度降到行（实际锁索引记录），不同行读写互不阻塞。但纯记录锁无法解决幻读——新插入的行不在已有记录上，记录锁锁不住。

间隙锁 + 临键锁：InnoDB 在 RR 级别引入 Gap Lock（锁记录间的间隙）与 Next-Key Lock（记录 + 其前间隙），锁住"范围"而非仅"点"，阻止间隙内的插入，从而在当前读场景解决幻读。这是 RR 比 RC 并发略低的根源。

趋势：锁实现持续优化——5.7.15 引入 innodb_deadlock_detect（可关闭以降高并发 CPU 开销），8.0 持续优化死锁检测算法、引入 CTS（Change Transaction System）等。NewSQL（TiDB）则用 MVCC + 单时间戳 TSO + 乐观/悲观事务模型规避复杂锁体系。

三种行锁算法：

核心加锁规则（RR 级别，基于唯一索引/非唯一索引区分）：

• 等值查询唯一索引，命中记录：Next-Key Lock 退化为 Record Lock（只锁记录，因唯一性保证无插入冲突）。
• 等值查询唯一索引，未命中：Next-Key Lock 退化为 Gap Lock（锁住目标值所在间隙，防插入）。
• 等值查询非唯一索引，命中：Next-Key Lock 不退化为 Record Lock（因非唯一，可能有重复值）。InnoDB 会对所有等值匹配记录加 Next-Key Lock，并继续向右扫描到第一条不满足条件的记录，对最后一条匹配记录与其后第一条不匹配记录之间的间隙加 Gap Lock——防止新插入等值记录。这是与唯一索引等值命中的关键区别。
• 范围查询：锁覆盖所有扫描到的记录及其前间隙，直到第一个不满足范围条件的记录（含其后间隙的 Gap Lock）。

意向锁（Intention Lock）：表级锁，事务加行锁前先加意向锁（IS/IX），作用是让"加表锁"操作能快速判断表内是否有行锁而不必逐行扫描。意向锁之间互不冲突，与行锁共存，对开发者透明。

RC vs RR 锁行为：RC 级别几乎不加 Gap Lock（外键/唯一性检查除外），只加 Record Lock——这是 RC 并发更高、但有幻读的根源；RR 默认 Next-Key Lock，防幻读但锁范围大、死锁概率高。

插入意向锁：INSERT 前若目标间隙已被 Gap Lock 占用，插入事务等待并持有插入意向锁（与 Gap Lock 冲突，与其他插入意向锁不冲突，允许同间隙不同位置并发插入）。

分析一条 SQL 加了什么锁：① 确认隔离级别（RR 还是 RC）；② 确认索引类型（唯一/非唯一/无索引）；③ 确认查询类型（等值/范围、命中/未命中）；④ 套用加锁规则。无索引的查询会锁全表（每行都加 Next-Key Lock），这是生产事故常见根因——务必确保 WHERE 走索引。

死锁排查：SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK 段，输出两个事务各自持有与等待的锁。常见死锁模式：事务 A-B 交叉持锁对方所需行；间隙锁交叉导致插入互等。开启 innodb_print_all_deadlocks=ON 将所有死锁写入 error log。

减少锁冲突实践：① 缩短事务（避免长事务持锁）；② 按固定顺序访问资源（防循环等待）；③ 用 RC 降低间隙锁；④ 大批量更新分批 + 避免 FOR UPDATE 长时间持有；⑤ 唯一索引等值命中只退化记录锁，优先用唯一索引。

追问 1：为什么 InnoDB 行锁锁的是索引而非数据行？

InnoDB 数据按聚簇索引组织，行数据就在聚簇索引叶子节点；二级索引查询需回表聚簇索引。加锁本质是给索引项加锁——若无索引，InnoDB 必须扫描聚簇索引每一行并加锁，等效全表锁。因此"走索引"不只是性能问题，更是锁粒度问题——无索引的 UPDATE/DELETE 会锁全表。

追问 2：高并发下死锁检测的 CPU 开销如何处理？

8.0 默认 innodb_deadlock_detect=ON，每次锁等待都触发检测，高并发热点行更新下检测本身成为 CPU 瓶颈。方案：① 关闭死锁检测 + 业务层控制访问顺序避免死锁；② 拆分热点行（如将一行库存拆多行）；③ 用队列串行化热点更新（Redis 预扣减 + 异步落库）；④ innodb_lock_wait_timeout 调短快速失败。

追问 3：除了行锁，还有哪些"隐形锁"在影响并发？自增锁会锁全表吗？

自增锁（AUTO-INC Lock）：为 INSERT 分配自增 ID 的表级锁。历史上是在语句结束才释放（innodb_autoinc_lock_mode=0/1），一次批量 INSERT 会阻塞其他插入——批量插入下表现为"锁表"。8.0 默认 mode=2，仅在分配瞬间取锁立即释放（interleaved），批量插入不再持锁，但要求 binlog 为 row 格式才能保证主从自增值一致。若用 statement 格式批量插入，主从可能生成不同自增 ID。

元数据锁（MDL）：DDL 与 DML 之间的并发热点——ALTER TABLE 需 MDL 写锁，会等待所有正在执行的 DML 释放 MDL 读锁，长查询会卡住 DDL；反之 DDL 排队也会阻塞后续一切读。线上 Show processlist 常见 waiting for table metadata lock 即此链。排查要 performance_schema.metadata_locks 定位阻塞源。

锁兼容矩阵本质：InnoDB 锁体系本质是一张加锁/等待矩阵——行锁、间隙锁（排他 + 插入意向锁）、意向锁、AUTO-INC、MDL 五个维度叠加，判断任意 SQL 会锁谁、被谁阻塞，才是真正的隔离级别并发放大了然于胸。

上线门禁：慢查询阈值、事务边界评审、主从延迟告警。表达提纲：执行计划→锁/索引证据→改写与容量。

这道题考察 MySQL 事务日志体系的全局理解：三种日志各司其职（Redo 崩溃恢复、Undo 回滚与 MVCC、Binlog 复制），又通过两阶段提交（2PC）协同保证 Redo 与 Binlog 的一致性，进而保证 ACID 中的原子性、持久性、一致性。本质是用 WAL（Write-Ahead Logging）+ 日志顺序写替代数据随机写，以最小代价保证崩溃不丢数据。架构师需理解双 1 参数、组提交、以及复制方案演进对日志体系的依赖。

三种日志的起源：Binlog 是 MySQL Server 层日志（早于 InnoDB），最初用于主从复制与 PITR；Undo/Redo 是 InnoDB 引擎层日志，借鉴经典数据库理论（ARIES 恢复算法）。MySQL 收购 InnoDB 后，需让 Server 层 Binlog 与引擎层 Redo 协同——这才催生两阶段提交。

复制方案演进（与日志体系强相关）：异步复制（默认，主库不等从库，可能丢数据）→ 半同步复制（5.5+，主库等至少一个从库收到 binlog 才返回，兼顾性能与数据安全）→ .lossless 半同步（5.7+，ACK 在 binlog 落盘后）→ MGR（Group Replication）（5.7.17 GA，基于 Paxos 变种 XCom，多数节点写成功才提交，强一致）。演进核心是在性能与一致性间不断向强一致推进。

组提交（Group Commit）：5.6 引入 binlog 组提交，5.7 改进为三阶段组提交（flush → sync → commit），将多个事务的日志合并一次 fsync，大幅提升高并发吞吐。这是半同步/MGR 能落地的性能基础。

趋势：MGR + ProxySQL/MySQL Router 成为云原生高可用主流；binlog 也被 Canal/Debezium 订阅用于 CDC 数据同步。日志体系向更强一致性 + 更高吞吐演进。

三种日志职责对照：

WAL（Write-Ahead Logging）核心：先写日志再改数据页。事务修改时：① 写 undo log（记录旧值，用于回滚/MVCC）；② 修改 Buffer Pool 中的数据页（脏页）；③ 写 redo log 到 log buffer；④ 提交时 redo log fsync 落盘。崩溃后用 redo log 重放已提交事务的页修改（崩溃恢复），保证持久性；未提交事务用 undo log 回滚，保证原子性。

两阶段提交（2PC）保证 Redo 与 Binlog 一致。这是协同的核心：

崩溃恢复规则：扫描 redo log，对每个事务：
① redo 有 COMMIT 标记 → 已提交，重放（保证持久性）；
② redo 只有 PREPARE 无 COMMIT → 查 binlog：若 binlog 完整（已写入）→ 提交（因 binlog 可能已被从库消费）；若 binlog 不完整 → 回滚（用 undo）。这保证了 Redo 与 Binlog 的一致性——不会出现主库提交而从库没收到的情况。

双 1 参数（持久性核心）：

• innodb_flush_log_at_trx_commit=1：每次事务提交都 fsync redo log（默认）。设 0/2 性能高但崩溃可能丢数据。
• sync_binlog=1：每次事务提交都 fsync binlog（默认）。设 N 则每 N 次才 fsync，崩溃丢 N 个事务的 binlog。

redo log 大小调优：MySQL 8.0.30+ / 8.4 生产以 innodb_redo_log_capacity 统一管理 redo 容量（动态可调，替代旧的 innodb_log_file_size × innodb_log_files_in_group）。容量过小 → 频繁 checkpoint、刷脏抖动；过大 → 崩溃恢复变长。按约 1 小时峰值写入量估算后压测确认。旧手册里的 innodb_log_file_size 仅作 8.0 早期历史对照，新环境勿再当主旋钮。

binlog 格式选择：ROW（行级，记录每行变更，安全可靠，主从一致性强，推荐）/STATEMENT（SQL 语句，小但有不确定函数风险）/MIXED。生产统一用 ROW，配合 binlog_row_image=MINIMAL 减小体积。

组提交调优：binlog_group_commit_sync_delay（微秒级延迟，攒批）+ binlog_group_commit_sync_no_delay_count（攒批数上限）。在高并发写场景可显著提升吞吐，低并发应设 0 避免增加延迟。

追问 1：为什么必须有两阶段提交？不要行不行？

不行。假设先写 redo 后写 binlog：redo 写完崩溃，binlog 没写——主库恢复后提交（redo 完整），但从库没收到该事务，主从数据不一致。假设先写 binlog 后写 redo：binlog 写完崩溃，redo 没写——主库恢复后回滚，但从库收到了事务并执行，从库多了主库没有的数据。两阶段提交通过"redo prepare → binlog → redo commit + 崩溃时用 binlog 完整性裁决"确保两份日志要么都成功要么都失败。

追问 2：redo log 与 binlog 都是日志，为何要两份？

职责不同且层次不同。Redo 是引擎层物理日志，记录"哪个页哪个偏移改成什么"，仅 InnoDB 能解读，用于崩溃恢复（崩溃后内存脏页丢失，需 redo 重建）。Binlog 是Server 层逻辑日志，记录 SQL 或行变更，所有存储引擎通用，用于复制与备份。Redo 循环写不可长期保留，binlog 追加写可保留数天用于 PITR。两者无法互相替代。

这道题考察性能问题定位的方法论：从发现慢 SQL 到定位根因再到优化的完整闭环。本质是用 EXPLAIN 理解执行计划，用索引失效规则判断为何没走索引，用针对性优化策略（索引/改写/分页）消除性能瓶颈。这不仅是背 EXPLAIN 字段，而是建立"现象 → 计划 → 根因 → 优化"的因果链。高级开发必须能在生产环境独立完成慢 SQL 治理。

发现手段演进：慢查询日志（slow_query_log，原始）→ pt-query-digest（Percona Toolkit，聚合分析慢日志）→ performance_schema（5.7+ 实时采样，events_statements_summary_by_digest）→ APM 全链路（SkyWalking 抓 SQL 耗时）→ 云数据库慢 SQL 诊断（RDS 控制台）。

分析工具演进：EXPLAIN（执行计划）→ EXPLAIN FORMAT=JSON（更详细）→ EXPLAIN ANALYZE（8.0.18+，实际执行并返回每算子真实行数与耗时）→ optimizer trace（8.0，查看优化器决策全过程）。

优化手段演进：加索引 → 覆盖索引 → 索引下推 ICP → 延迟关联分页 → 物化视图/汇总表 → 读写分离 → 分库分表 → 引入 ES/ClickHouse 卸载复杂查询。

排查五步法：

EXPLAIN 核心字段（重点看 type/key/rows/Extra）：

索引失效的八大场景（高频考点）：

-- 1. 函数操作索引列（破坏最左匹配的有序性）
WHERE YEAR(create_time) = 2024   -- 失效，改: WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'

-- 2. 隐式类型转换
WHERE phone = 13800138000         -- phone 是 varchar，MySQL 把字符串列转成数字（CAST(phone AS DECIMAL)）再比较，索引列被函数包裹 → 失效。改: WHERE phone = '13800138000'

-- 3. 最左前缀违反（联合索引 (a,b,c)）
WHERE b = 1                       -- 跳过 a，失效

-- 4. 范围查询右侧列失效
WHERE a = 1 AND b > 5 AND c = 3   -- c 失效（b 是范围，c 无法用索引）

-- 5. LIKE 左模糊
WHERE name LIKE '%abc'          -- 失效，改: LIKE 'abc%'

-- 6. OR 连接非索引列
WHERE a = 1 OR d = 2             -- d 无索引则全表，需两列都有索引或用 UNION

-- 7. != / <> / NOT IN（通常走全表，优化器判断）
WHERE status != 1                -- 选择率高时易失效

-- 8. IS NOT NULL（部分情况）
WHERE col IS NOT NULL            -- 依数据分布，NULL 少时易全表

分页优化：深分页问题。LIMIT 1000000,10 需扫描 1000010 行再丢弃前 100 万，极慢。方案：

-- 方案1：延迟关联（先索引查主键，再回表）
SELECT t.* FROM t
INNER JOIN (SELECT id FROM t WHERE x=? ORDER BY id LIMIT 1000000,10) tmp
ON t.id = tmp.id;

-- 方案2：游标分页（记住上一页最后 id，要求 id 自增有序）
SELECT * FROM t WHERE id > ? ORDER BY id LIMIT 10;

-- 方案3：业务限制深分页（最多看前 N 页，深分页走搜索/ES）

慢查询配置：slow_query_log=ON、long_query_time=0.5（线上可设 0.1-1s）、log_queries_not_using_indexes=ON（记录未走索引的 SQL，发现隐患）。用 pt-query-digest slow.log 按"次数 × 耗时"聚合，优先治理总耗时最高的 SQL（而非单次最慢的）。

常用优化策略：① 加合适索引（覆盖索引优先）；② 避免 SELECT *（减少回表与网络传输）；③ 大 IN 改 JOIN 或临时表；④ COUNT(*) 优化（用估算或汇总表，避免全表扫）；⑤ 复杂统计查询卸载到 ClickHouse/ES；⑥ 定期 ANALYZE TABLE 更新统计信息（rows 估算不准时）。

追问 1：EXPLAIN 的 rows 不准怎么办？

rows 是优化器基于统计信息（索引基数 cardinality）估算的。统计信息过期会导致估算偏差，优化器可能选错索引。处理：执行 ANALYZE TABLE t 更新统计信息；8.0 默认持久化统计信息（innodb_stats_persistent=ON），也可 ANALYZE TABLE t UPDATE HISTOGRAM ON col（8.0 直方图）对非索引列提供分布信息辅助优化器。最终用 EXPLAIN ANALYZE 看真实行数。

追问 2：为什么优化器有时选了"差"的索引？

优化器是基于成本（CPU + I/O）选择执行计划，成本估算依赖统计信息与启发式规则。可能误判的场景：① 统计信息过期；② 直方图缺失，对数据倾斜列估算偏差；③ 查询条件复杂无法准确估算。可用 FORCE INDEX(idx) 强制指定，或 optimizer_search_depth、optimizer_switch 调优。根因用 optimizer trace 分析。

把"发现 → 归因 → 治理 → 回归 → 防回归"做成可复用闭环，而不是单次 EXPLAIN：

1. 发现：慢查询日志（long_query_time）+ pt-query-digest 按总耗时排序；并行看 performance_schema events_statements_summary_by_digest 的 TOP SQL。
2. 归因：线下/只读副本 EXPLAIN → 必要时 EXPLAIN ANALYZE（8.0.18+）对比估算 vs 实际；可疑优化器用 optimizer_trace。
3. 治理：索引/改写/分页/拆分；变更走灰度与回滚预案（见 C07.12 DDL）。
4. 回归：同负载重放或影子流量对比 P95/扫描行数；确认无新全表扫。
5. 防回归：CI 拦截无索引大表扫描（可选）、慢 SQL 看板告警、统计信息定期 ANALYZE。

上线门禁：慢查询阈值、事务边界评审、主从延迟告警。表达提纲：执行计划→锁/索引证据→改写与容量。

这道题考察索引设计的系统性方法论：何时建索引、建什么索引、联合索引字段顺序、如何避免索引失效与冗余。本质是在查询性能与写入开销之间权衡，用最少的索引覆盖最多的查询。索引不是越多越好——每个索引是一棵 B+树，增加写入时的维护成本与存储。掌握最左前缀、覆盖索引、索引下推、选择率等原则，才能做出经得起数据量增长的设计。

单列索引：早期只对单列建索引，简单但无法支持多条件组合查询的高效检索。

联合索引（Composite Index）：多列组合索引，遵循最左前缀原则，一个索引覆盖多种查询模式。成为 OLTP 主流。

覆盖索引（Covering Index）：将查询所需所有列纳入索引，避免回表。InnoDB 二级索引自动含主键，扩展性好。

索引下推（ICP，Index Condition Pushdown，5.6）：把 WHERE 条件的一部分下推到存储引擎层在索引上过滤，减少回表次数。是"在索引层做更多过滤"的优化方向。

趋势：索引设计从手工经验走向工具辅助——sys.schema_unused_indexes 找无用索引、sys.schema_redundant_indexes 找冗余索引、pt-index-usage 工具分析。8.0 的不可见索引（Invisible Index）支持"先隐藏再删除"的灰度下线。

最左前缀原则：联合索引 (a,b,c) 的 B+树按 a→b→c 排序。能匹配前缀的查询走索引：(a)、(a,b)、(a,b,c) 走索引；(b,c) 不走（缺最左 a）；(a,c) 只能用 a 部分（c 因中间缺 b 无法参与索引查找，但 5.6+ ICP 可下推 c 过滤）。

联合索引字段顺序三原则：① 等值在前，范围在后——范围查询右侧列无法用索引，故范围列放最后；② 区分度高的放前面——快速缩小扫描范围（但需结合实际查询，不能只看区分度）；③ 尽量覆盖更多查询——一个联合索引服务多个 WHERE 组合，减少索引数量。

覆盖索引：查询列全在索引中，Extra 显示 Using index，无需回表。设计时把高频查询的 SELECT 列纳入索引。但索引列不宜过多（增大索引体积、降低更新性能），需权衡。

索引下推（ICP）：5.6 前，联合索引 (a,b,c) 查 WHERE a=1 AND c=3，引擎只用 a 过滤后回表逐行判断 c。ICP 后，c 条件下推到引擎层在索引上过滤，减少回表次数。EXPLAIN Extra 显示 Using index condition。

选择率（Cardinality）：索引列不同值的比例。选择率高的列（如用户 ID）建索引效果好；选择率低的列（如性别，仅 2 值）建索引意义不大，优化器可能放弃索引。SHOW INDEX FROM t 的 Cardinality 字段反映。

建索引决策清单：① 该列是否频繁出现在 WHERE/JOIN/ORDER BY/GROUP BY；② 选择率是否足够高（无绝对阈值，需结合数据分布与查询模式判断）；③ 是否能复用已有联合索引的最左前缀；④ 写多读少的表是否值得加（写放大）；⑤ 是否可用覆盖索引替代多个单列索引。

避免冗余与无用索引：联合索引 (a,b) 已覆盖 (a) 单列索引，后者冗余；主键索引已含 id，二级索引自动含 id 无需显式加。定期用 sys.schema_unused_indexes 清理长期未使用的索引。

追问 1：联合索引 (a,b,c)，查询 WHERE a=1 ORDER BY c 能用索引排序吗？

不能高效利用索引排序。索引顺序是 a,b,c，a 等值后应按 b 排序，但 ORDER BY 跳过 b 直排 c，无法利用索引有序性，会 Using filesort。优化：调整为 ORDER BY b,c（匹配索引），或建 (a,c) 索引（牺牲 b）。这说明排序字段也要考虑最左前缀。

追问 2：为什么不要在区分度低的列上单独建索引？

区分度低（如 status 只有 0/1），索引扫描后仍需回表大量行，优化器判断"走索引 + 回表"比"全表扫描"更慢，直接放弃索引。但低区分度列若与高区分度列组合成联合索引（如 (status, user_id)）仍有价值——先等值过滤 status 再用 user_id。所以单独建无意义，组合建有用。

索引设计不仅有定性原则，还有定量公式与统计信息增强手段，掌握这些才能在复杂场景做出精确决策。

• 索引选择性公式：selectivity = cardinality / total_rows，取值 0~1，越接近 1 越适合建索引。经验阈值：选择性 > 0.2（即过滤后剩余 20% 以下行数）适合建索引；选择性 < 0.1 的列建索引收益低，优化器倾向于全表扫描。
• 前缀索引选择性计算：对长字符串（如 URL），通过 SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(*) 逐步增大 N，选择达到完整列选择性 90% 以上的最短前缀长度，兼顾索引体积与区分度。
• MySQL 8.0 直方图（Histogram）：解决优化器对非均匀分布列行数估算不准确的问题（如 status 列 99% 为 0、1% 为 1，优化器误以为 50% 匹配而选错索引）。类型分 equi-height（等高直方图）和 singleton（单值直方图）；创建命令 ANALYZE TABLE t UPDATE HISTOGRAM ON col_name WITH 100 BUCKETS。直方图不是索引、不加速查询，只增强统计信息帮助优化器选更优执行计划；对不适合建索引的列也可建直方图。
• ICP 细节：联合索引 (a, b, c) 查询 WHERE a=1 AND c=2，无 ICP 时对每个 a=1 的索引行回表取整行再过滤 c；有 ICP 时在索引层直接判断 c=2，只对满足条件的行回表，大幅减少回表次数。EXPLAIN Extra 显示 Using index condition。

这道题考察数据库水平扩展的架构设计能力：何时该分、怎么分（垂直/水平、分片策略）、分之后跨库难题如何解（Join/分页/事务/全局 ID）、用什么中间件、如何平滑迁移。本质是用数据分片突破单机容量与吞吐瓶颈，代价是引入分布式复杂性。架构师需权衡"分"的收益与成本，避免过早分库分表，也要在必要时能设计可落地的分片方案并规划演进到 NewSQL 的路径。

单机 MySQL：读写在单库单表。瓶颈：单表数据量过大（B+树变高、维护成本升）、单机磁盘/内存/CPU 上限。

主从 + 读写分离：主库写、从库读，读扩展。瓶颈：写仍单点；从库延迟；单表数据量问题未解决。

分库分表（Sharding）：水平拆分数据到多库多表，写与读均可扩展。引入跨库 Join/分页/事务难题。代表中间件：MyCat（代理模式）、ShardingSphere（Sharding-JDBC 客户端模式 / Sharding-Proxy 代理模式）、TDDL（阿里）。

NewSQL（TiDB / CockroachDB / Spanner / OceanBase）：原生分布式数据库，底层自动分片 + 强一致性（Raft/Paxos）+ 兼容 SQL，对业务透明。无需业务层分片，自动扩缩容，分布式事务开箱即用。

趋势：从"业务侵入式分库分表"向"NewSQL 透明分布式"演进。但分库分表在存量系统仍有大量应用，且 NewSQL 也有局限（复杂 OLAP、超低延迟点查）。架构师应能评估何时用分库分表、何时上 NewSQL。

拆分维度：

• 垂直分库：按业务拆库（订单库、用户库、商品库），解耦业务，解决单库表过多/连接数上限。
• 垂直分表：将宽表拆为多表（热列与冷列分离），减少单行体积、提升缓冲池命中率。
• 水平分表：同库内按分片键拆多表，解决单表数据量大。
• 水平分库分表：跨库跨表分片，同时解决数据量与单库吞吐。

分片策略：

分片键选择是成败关键：① 尽量选高频查询条件作为分片键（如 user_id），使大部分查询能精准路由到单分片，避免广播；② 分片键不可变；③ 数据分布均匀。若查询不带分片键，中间件需广播到所有分片合并结果，性能急剧下降。

跨库难题与对策：

• 跨库 Join：避免——通过冗余字段、应用层组装、宽表反范式；或用 ES 宽表检索。
• 跨库分页：全局分页需各分片取 (offset + page) 条，再统一归并排序后取目标页，深分页不可行。改游标分页或限制分页深度。
• 跨库事务：用分布式事务（Seata AT/TCC）或最终一致性（本地消息表 + MQ）。尽量保证核心事务在单分片内。
• 全局唯一 ID：雪花算法（Snowflake）、号段模式（Leaf-segment）、Redis INCR。
• 跨库聚合统计：卸载到 ClickHouse/ES 或汇总表。

何时分库分表（经验阈值）：单表数据量 > 1000 万或单表 > 50GB、单库 QPS > 5000/TPS > 1000、单库连接数紧张。但优先尝试优化——索引优化、读写分离、归档冷数据、分区表，往往能延迟分库分表 1-2 年。过早分库分表是常见架构错误。

分片数量规划：预留 3-5 倍增长空间。如当前 5000 万、预计 3 年 5 亿，按 32 或 64 分片（2 的幂便于倍增扩容）。

平滑迁移方案（双写 + 校验）：① 新库建表 + 数据同步（全量 + 增量）；② 应用双写（新老库都写），读仍走老库；③ 校验新老库数据一致；④ 切读到新库（灰度）；⑤ 下线老库。关键：双写阶段需容忍短暂不一致，用 Canary 灰度切换。ShardingSphereScaling 或 DataX 可辅助。

中间件选型：ShardingSphere-JDBC（轻量、无额外部署、性能好，适合 Java 应用）vs Sharding-Proxy（独立部署、多语言支持、但多一跳）。云上可考虑 RDS 分库分表或直接用 TiDB。

追问 1：分库分表后如何做全局二级索引？

单分片内的索引天然局限——按 user_id 分片后，按 phone 查询需广播。方案：① 冗余异构索引——把 phone→user_id 映射单独存一张按 phone 分片的表，先查映射再查主表（两次查询但都单分片）；② ES 宽表——将数据同步到 ES 建二级索引，按非分片键查询走 ES；③ NewSQL（TiDB）自动维护全局二级索引，对业务透明。

追问 2：分库分表 vs NewSQL（TiDB），如何选？

分库分表优势：成熟、可控、复用 MySQL 运维体系、单分片性能极致。劣势：业务侵入、跨库难题、扩容复杂。NewSQL 优势：透明分片、自动均衡、强一致分布式事务、SQL 兼容。劣势：复杂查询/超低延迟点查略逊单机 MySQL、运维门槛、成本。选型：新业务、预期快速增长、需强一致 → NewSQL；存量 MySQL 系统、特定瓶颈单表 → 分库分表；混合用——核心交易库单机 MySQL + 分析/搜索用 ES/ClickHouse。

分库分表落地最该用量化证据支撑的选型结论是：迁移可不停机、热点可量化、翻倍扩容低成本。

这道题考察主从复制的完整链路与延迟治理：binlog 如何从主库到从库应用、三种线程协作、复制模式差异、延迟根因与并行复制优化。本质是用日志重放实现数据副本，核心矛盾是"强一致性"与"复制延迟"的权衡。高级开发需能定位延迟根因并配置并行复制，架构师需理解复制模式演进以支撑高可用选型。

异步复制（默认）：主库提交事务后不等从库即返回，binlog 由 dump 线程异步推送。性能最高但主库崩溃可能丢未同步事务。

半同步复制（5.5+）：主库提交后等待至少一个从库 ACK（收到 binlog）才返回客户端。AFTER_SYNC（5.7+，lossless，ACK 在 binlog 落盘后）vs AFTER_COMMIT（5.5，ACK 在提交后）。兼顾性能与数据安全，是金融场景标配。

并行复制（MTS，Multi-Threaded Slave）：5.6 引入按库并行（粒度粗）；5.7 引入 slave_parallel_type=LOGICAL_CLOCK 基于组提交的并行（同组事务可并发应用）；8.0 引入 WRITESET 依赖跟踪（无冲突事务可并发，突破组提交限制）——8.0 可通过 binlog_transaction_dependency_tracking=WRITESET 开启（该变量在 8.0.35/8.2 起废弃）。MySQL 8.4 LTS 起该变量已移除：主库在多线程从库场景下固定按 WRITESET 生成 binlog 依赖信息（效果等同于旧版 WRITESET），再配置会直接报错。这是降低从库延迟的关键。

MGR（Group Replication，5.7.17 GA，8.0 增强）：基于 Paxos 变种（XCom）的强一致复制，多数节点写成功才提交。详见 C07.09。

趋势：从异步到半同步到 MGR，一致性逐步增强；并行复制从库并行度持续提升；CDC 订阅 binlog（Canal/Debezium）成为数据同步主流，复制不再仅限 MySQL 间。

复制三线程模型：

复制流程：① 从库 IO 线程连接主库，主库创建 Binlog Dump 线程读取 binlog 发送；② 从库 IO 线程将事件写入 relay log；③ 从库 SQL 线程读取 relay log 重放（8.0 MTS 下 SQL 线程是协调器，分发事件给 worker 并行执行）。

复制格式：与 binlog_format 一致——STATEMENT（SQL 语句）、ROW（行变更，推荐）、MIXED。ROW 格式从库按行应用，确定性高、无主从不一致风险。

延迟根因：① 大事务——单事务 binlog 量大，从库串行应用慢；② 从库单线程——5.6 前从库单 SQL 线程重放，主库并发写从库追不上；③ 无主键表——ROW 格式下从库应用 UPDATE/DELETE 需全表扫描定位行；④ 从库硬件差/负载高——从库承担读/统计任务挤占资源；⑤ 网络延迟。

并行复制（MTS）配置（按版本分列）：

# ===== MySQL 8.0.x（仍可显式设 WRITESET；8.0.35+ 该变量已废弃但可用）=====
# 主库：用 WRITESET 生成事务依赖，供从库 MTS 突破「同组提交」限制
binlog_transaction_dependency_tracking = WRITESET   # 仅 8.0；取值 WRITESET / COMMIT_ORDER / WRITESET_SESSION
binlog_format = ROW                                 # WRITESET 依赖跟踪要求行格式

# 从库
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16              # worker 数，一般 ≈ CPU 核数
slave_preserve_commit_order = ON     # 保持提交顺序一致

# ===== MySQL 8.4 LTS（勿再配置 binlog_transaction_dependency_tracking）=====
# 主库：WRITESET 依赖跟踪已内建化——多线程从库场景下始终按 WRITESET 写依赖；
# 再写 binlog_transaction_dependency_tracking 会报 unknown variable / 启动失败。
binlog_format = ROW                  # 8.4 起 WRITESET 冲突检测也要求 ROW（MIXED 不再支持该路径）

# 从库（8.0.26+ 推荐 replica_* 别名；8.4 生产优先用 replica_）
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON

GTID 复制详细配置：

主库 GTID 配置：

[mysqld]
# 开启 GTID 模式
gtid_mode=ON
# 强制 GTID 一致性
enforce_gtid_consistency=ON
# Binlog 过期自动清理（8.0+ 用 binlog_expire_logs_seconds，单位秒）
# 注意：expire_logs_days 自 8.0 起废弃、MySQL 8.4 已移除；8.4+ 写该参数会导致 mysqld 启动报错
binlog_expire_logs_seconds=259200
# Binlog 格式必须为 ROW
binlog_format=ROW
# 记录执行的GTID到信息
log_replica_updates=ON  # 8.0 名称，5.7 及以前为 log_slave_updates

从库 GTID 复制搭建步骤（命令按版本分列；MySQL 8.4 LTS 以 SOURCE/REPLICA 为准）：

-- 1. 源库创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';      -- 8.0 常见
-- GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%'; -- 8.4 文档语义（按权限模型选用）
FLUSH PRIVILEGES;

-- 2. 源库导出全量（GTID 用 --set-gtid-purged）
mysqldump -uroot -p --single-transaction --set-gtid-purged=ON --all-databases > source_data.sql

-- 3. 副本导入
mysql -uroot -p < source_data.sql

-- 4a. MySQL 8.0.x 历史语法
CHANGE MASTER TO
    MASTER_HOST='source_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_AUTO_POSITION=1,
    MASTER_PORT=3306;
START SLAVE;
SHOW SLAVE STATUS\G

-- 4b. MySQL 8.4 LTS 现行（勿再把 MASTER/SLAVE 当默认教法）
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source_host',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='password',
    SOURCE_AUTO_POSITION=1,
    SOURCE_PORT=3306;
START REPLICA;
SHOW REPLICA STATUS\G
-- Replica_IO_Running / Replica_SQL_Running = Yes

-- 5. 延迟/连接态（performance_schema，跨版本更稳）
SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

半同步复制详细配置（插件与变量：master/slave → source/replica）：

MySQL 8.4 LTS（生产主线）：源端 semisync_source.so，副本 semisync_replica.so。启用后若副本接收线程已在跑，需 STOP REPLICA IO_THREAD; START REPLICA IO_THREAD; 才会以半同步身份重连。8.4 LTS 官方支持窗口长（至约 2032），仍是多数企业默认基线。

版本时间线（2026 现况，选型必答）：MySQL 9.7.0 LTS 已于 2026-04-21 GA，是 9.x 首个 LTS（Premier 至 2031、Extended 至 2034）；MySQL 8.0 已于 2026-04-30 EOL（最后版本 8.0.46，Premier 早在 2025-04-30 结束），继续运行 8.0 意味着不再获得主动补丁与安全修复——托管平台各有独立窗口（HeatWave 至 2027-04、AWS RDS 至 2026-07-31、Aurora MySQL 至 2028-04），不能与自建混谈。8.4 LTS Premier 至 2029-04、Extended 至 2032-04。

升级路径不可跨 LTS 跳升：只能 8.0.x → 8.4.x LTS → 9.7.0 LTS 两段升级。升级前必查三类兼容项：① 认证插件——mysql_native_password 在 8.4 已默认禁用、9.x 已移除，须先迁到 caching_sha2_password（9.7 起新增 PBKDF2 / SHA-512 存储格式）；② 已移除变量——expire_logs_days、query_cache_*、innodb_log_files_in_group 等，须从 my.cnf 清理；③ 外键约束——8.4 起要求外键引用的父表列必须有唯一索引。

9.7 社区版可用的新能力：Hypergraph Optimizer（基于 DPhyp 动态规划，可枚举 Bushy Tree 计划；默认关闭，须 optimizer_switch='hypergraph_optimizer=on' opt-in，开启后不支持 STRAIGHT_JOIN 与 EXPLAIN FORMAT=TRADITIONAL/JSON）、JSON Duality Views 全量 DML、cgroup cpuset 感知（容器 / K8s 场景下语义更准），以及原先仅企业版的多个复制与观测组件下放社区版。Hypergraph 建议先在预发单条 SQL 用 hint 验证，不要生产全局开启。

必须避免的误判：VECTOR 类型虽然可用，但 9.7 社区版不提供 HNSW 向量索引，也不含官方距离函数（DISTANCE() / VECTOR_DISTANCE() 仅 HeatWave on OCI 与 MySQL AI 提供）——把「MySQL 9.7 支持向量检索」当作社区版可用的 ANN 方案是错的，ANN 检索仍需 HeatWave 或外部向量库（pgvector / Milvus / Qdrant 等）。

选型建议：新集群若无强制约束，优先 8.4 LTS（稳定、第三方镜像与备份 / 集群工具跟进充分）；已在 8.0 且需要 Hypergraph / Duality Views / cgroup 能力、并希望一举脱离 EOL 风险，按 8.0 → 8.4 → 9.7 规划。无论哪条路径都需在测试环境跑完整回归；9.0~9.6 属季度 Innovation 版本，不应作为生产唯一基线。

# 源端 my.cnf（8.4）
[mysqld]
# INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
rpl_semi_sync_source_enabled=ON
rpl_semi_sync_source_timeout=10000
rpl_semi_sync_source_wait_for_replica_count=1
rpl_semi_sync_source_wait_no_replica=ON

# 副本 my.cnf（8.4）
[mysqld]
# INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
rpl_semi_sync_replica_enabled=ON

MySQL 8.0.x（历史对照）：旧插件 semisync_master.so / semisync_slave.so，变量前缀 rpl_semi_sync_master_* / rpl_semi_sync_slave_*。8.4 起已弃用，MySQL 9.5 起旧插件与旧变量一并移除（改 semisync_source.so / semisync_replica.so 与 rpl_semi_sync_source_* / rpl_semi_sync_replica_*），生产勿再默认教旧名。

[mysqld]
# 历史示例（8.0）
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_no_slave=ON
# 从库：rpl_semi_sync_slave_enabled=ON

Java 监控半同步复制状态（示例状态名偏 8.0；8.4 请查 Rpl_semi_sync_source_* / Rpl_semi_sync_replica_*）：

@Service
public class SemiSyncMonitorService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 检查源端半同步状态（8.4 用 SOURCE；8.0 旧名为 RPL_SEMI_SYNC_MASTER_STATUS）
     */
    public boolean isSourceSemiSyncEnabled() {
        try {
            String status = jdbcTemplate.queryForObject(
                "SHOW STATUS LIKE 'RPL_SEMI_SYNC_SOURCE_STATUS'", 
                String.class
            );
            return "ON".equals(status);
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * 获取半同步等待的副本数量（8.4；8.0 旧名 RPL_SEMI_SYNC_MASTER_WAITING_SLAVE_COUNT）
     */
    public int getWaitingReplicaCount() {
        try {
            String count = jdbcTemplate.queryForObject(
                "SHOW STATUS LIKE 'RPL_SEMI_SYNC_SOURCE_WAITING_FOR_REPLICA_COUNT'", 
                String.class
            );
            return Integer.parseInt(count);
        } catch (Exception e) {
            return 0;
        }
    }
    
    /**
     * 检查副本半同步状态（8.4；8.0 旧名 RPL_SEMI_SYNC_SLAVE_STATUS）
     */
    public boolean isReplicaSemiSyncEnabled() {
        try {
            String status = jdbcTemplate.queryForObject(
                "SHOW STATUS LIKE 'RPL_SEMI_SYNC_REPLICA_STATUS'", 
                String.class
            );
            return "ON".equals(status);
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * 监控半同步延迟
     */
    public void monitorSemiSyncDelay() {
        try {
            // 检查半同步是否正常
            if (!isSourceSemiSyncEnabled()) {
                logger.warn("源端半同步复制已关闭，可能存在数据丢失风险");
                return;
            }
            
            // 检查等待的副本数量
            int waitingCount = getWaitingReplicaCount();
            if (waitingCount == 0) {
                logger.warn("没有副本等待半同步ACK，数据一致性可能存在问题");
                return;
            }
            
            // 检查复制延迟
            Long secondsBehind = jdbcTemplate.queryForObject(
                "SELECT MASTER_DELAY FROM performance_schema.replication_applier_status_by_worker", 
                Long.class
            );
            
            if (secondsBehind != null && secondsBehind > 5) {
                logger.warn("复制延迟过大: {}秒", secondsBehind);
            }
        } catch (Exception e) {
            logger.error("监控半同步复制状态失败", e);
        }
    }
}

并行复制最佳实践参数：

主库优化参数：

[mysqld]
# 事务持久性保证（主库必须为1保证数据安全）
innodb_flush_log_at_trx_commit=1
# Binlog 立即刷盘（主库必须为1保证数据安全）
sync_binlog=1
# Binlog 缓冲大小（根据并发调整）
innodb_log_buffer_size=16M
# 并行IO线程数
innodb_read_io_threads=4
innodb_write_io_threads=4
# 提交组大小（MTS相关）
innodb_commit_concurrency=0
# 自动清理purge线程
innodb_purge_threads=1

从库高并发参数（并行应用靠 replica_parallel_workers；网上流传的多 IO/SQL 线程参数多为伪配置，官方无对应系统变量，照抄会无效或报错）：

[mysqld]
# 8.4 现行名（8.0 可见 slave_parallel_* 同义；新环境统一 replica_*）
# 注意：replica_parallel_type 自 8.0.29 废弃、MySQL 9.5 起已移除——LOGICAL_CLOCK 为唯一策略，无需再配
replica_parallel_type=LOGICAL_CLOCK
replica_parallel_workers=8
replica_preserve_commit_order=ON
innodb_flush_log_at_trx_commit=2
sync_binlog=0

Java 监控并行复制性能（8.4 用 SHOW REPLICA STATUS）：

@Service
public class ParallelReplicationMonitorService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 监控并行复制性能
     */
    public void monitorParallelReplicationPerformance() {
        try {
            // 检查并行复制状态
            Map<String, Object> slaveStatus = jdbcTemplate.queryForMap("SHOW REPLICA STATUS"); // 8.0 旧环境可用 SHOW SLAVE STATUS
            
            // 检查IO线程状态
            String ioRunning = (String) slaveStatus.get("Slave_IO_Running");
            String sqlRunning = (String) slaveStatus.get("Slave_SQL_Running");
            
            if (!"Yes".equals(ioRunning)) {
                logger.error("IO线程已停止: {}", ioRunning);
            }
            
            if (!"Yes".equals(sqlRunning)) {
                logger.error("SQL线程已停止: {}", sqlRunning);
            }
            
            // 获取复制延迟
            Long secondsBehind = (Long) slaveStatus.get("Seconds_Behind_Master");
            if (secondsBehind != null && secondsBehind > 30) {
                logger.warn("复制延迟过大: {}秒", secondsBehind);
            }
            
            // 检查并行复制队列
            Map<String, Object> queueStatus = jdbcTemplate.queryForMap(
                "SELECT * FROM performance_schema.replication_applier_status_by_worker WHERE WORKER_ID > 0"
            );
            
            int queueLength = (Integer) queueStatus.get("WORKER_ID");
            if (queueLength > 10) {
                logger.warn("并行复制队列过长: {}", queueLength);
            }
            
            // 检查半同步状态
            boolean semiSyncEnabled = isSemiSyncEnabled();
            if (!semiSyncEnabled) {
                logger.warn("半同步复制已关闭，数据一致性可能存在问题");
            }
            
        } catch (Exception e) {
            logger.error("监控并行复制性能失败", e);
        }
    }
    
    /**
     * 获取复制延迟趋势
     */
    public List<Long> getReplicationDelayTrend(int minutes) {
        String sql = String.format(
            "SELECT MASTER_DELAY FROM replication_heartbeat_log WHERE time > NOW() - INTERVAL %d MINUTE ORDER BY time",
            minutes
        );
        return jdbcTemplate.queryForList(sql, Long.class);
    }
    
    /**
     * 获取并行复制统计
     */
    public Map<String, Object> getParallelReplicationStats() {
        String sql = "SELECT * FROM performance_schema.replication_applier_status_by_worker";
        List<Map<String, Object>> workers = jdbcTemplate.queryForList(sql);
        
        Map<String, Object> stats = new HashMap<>();
        stats.put("total_workers", workers.size());
        stats.put("active_workers", workers.stream()
            .filter(w -> "Running".equals(w.get("STATUS")))
            .count());
        stats.put("queue_length", workers.stream()
            .mapToInt(w -> (Integer) w.get("REMAINING_DELAY"))
            .sum());
        
        return stats;
    }
}

延迟优化组合拳：① 开启 MTS 并行复制——8.0 主库显式 binlog_transaction_dependency_tracking=WRITESET，8.4 勿再设该变量（已内建 WRITESET 依赖跟踪），从库配齐 replica_parallel_workers + LOGICAL_CLOCK；② 拆分大事务（单事务 < 1000 行变更）；③ 所有表必须有主键（ROW 格式应用效率）；④ 从库与主库同规格硬件、从库不跑重统计；⑤ innodb_flush_log_at_trx_commit=2（从库可牺牲部分持久性换吞吐，崩溃从主库重同步）；⑥ sync_binlog=0（从库 binlog 可不立即 fsync）。从库是为读扩展，数据可从主库重建。

读分离与延迟容忍：写主库后立即读从库可能读到旧数据。方案：① 关键写后读走主库（同会话或短时间窗口）；② 半同步复制降低延迟；③ 用 gtid_executed 等待从库追上指定 GTID 再读（WAIT_FOR_EXECUTED_GTID_SET）。

追问 1：半同步复制降级如何处理？

当副本在超时（8.4：rpl_semi_sync_source_timeout；8.0 旧名 rpl_semi_sync_master_timeout，默认 10000ms）内未 ACK，主库自动降级为异步以保证可用性（不阻塞写）。风险：降级期间主库崩溃丢数据。处理：① 监控半同步状态（8.4 source / 8.0 master 状态名），降级即告警；② 设置较大 timeout（如 30s）减少误降级；③ 配置多个副本（8.4：rpl_semi_sync_source_wait_for_replica_count）降低单点影响；④ 严格场景用 MGR 替代半同步。

追问 2：GTID 复制相比传统 binlog 位点复制有何优势？

GTID（Global Transaction Identifier）= server_uuid:transaction_id，全局唯一标识每个事务。优势：① 自动定位复制位点——故障切换时从库自动从正确 GTID 继续，无需手动找 binlog file/position；② 防重复执行——从库已执行的 GTID 不会重复；③ 简化切换——8.0：CHANGE MASTER TO MASTER_AUTO_POSITION=1；8.4：CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1。生产推荐开启 GTID。

上线门禁：慢查询阈值、事务边界评审、主从延迟告警。表达提纲：执行计划→锁/索引证据→改写与容量。

这道题考察数据库高可用架构的选型决策能力：各方案（MHA【已停止维护，仅作历史对比】、Orchestrator、MGR、MGC、云 RDS、NewSQL）的原理、优劣、适用场景，以及故障切换、脑裂防范、数据丢失风险评估。本质是在 RPO（数据丢失量）、RTO（恢复时间）、复杂度、成本之间权衡。架构师需根据业务对一致性与可用性的要求选型，并设计配套的监控、切换、演练机制。

主从 + 手动切换：早期方案，主库故障人工选从库提升。RTO 长（依赖人），易出错。

MHA（Master High Availability，2010s，已停止维护）：Perl 脚本的故障切换管理器，监控主库，故障时从多个从库选最新的提升为新主，尽量补齐其他从库缺失的 binlog。广泛使用但已多年未维护（Perl 实现，无官方维护，仅作高可用演进的历史方案保留对比），当前推荐 MGR（MySQL Group Replication）/ Orchestrator 替代——MGR 提供原生强一致，Orchestrator 适合灵活拓扑的半同步切换场景。

Orchestrator（GitHub 出品）：Go 实现的拓扑管理器，支持复杂拓扑发现、手动/自动故障切换、优雅切换。比 MHA 更活跃、功能更强，但仍基于异步/半同步复制，不解决数据强一致。

MGR（Group Replication，5.7.17 GA，8.0 增强）：官方基于 Paxos 变种（XCom）的强一致复制组，多数节点写成功才提交。单主模式（默认，自动选主）或多主模式。原生高可用 + 强一致，无需外部管理器。

InnoDB Cluster：MySQL 官方完整高可用方案 = MGR + MySQL Router（读写分离路由）+ MySQL Shell（管理）。官方推荐的一体化方案。

云原生方案：AWS Aurora / 阿里 PolarDB / 腾讯 TDSQL——存储计算分离，共享存储 + 多计算节点，秒级故障切换，原生高可用。

趋势：从"主从 + 外部切换工具"向"原生分布式强一致（MGR/InnoDB Cluster）"和"云原生共享存储（Aurora/PolarDB）"演进。NewSQL（TiDB/OceanBase）在金融级场景替代传统 MySQL 高可用。

方案对比矩阵：

MGR 核心原理：3-9 个节点组成复制组，事务通过 Paxos 变种（XCom）在组内多数派认证后才提交。写流程：事务在发起节点本地执行 → 提交时广播到组 → 多数节点认证（冲突检测：不同节点并发改同一行则后到者回滚）→ 多数认证通过则提交并复制到所有节点。多数节点存活即可服务，自动选主（单主模式），脑裂时少数派分区自动只读。

故障切换关键问题：① 脑裂——网络分区导致两个主，MGR 用多数派天然防脑裂（少数派只读）；半同步+切换工具需引入第三方仲裁（如 ZooKeeper/配置中心）。② 数据丢失——异步复制主库崩溃丢未同步事务；半同步降级期间丢；MGR 多数派存活则 0 丢失。③ 切换透明性——需 VIP/DNS/MySQL Router/ProxySQL 转发，应用无感知。

选型决策树：① 云上优先 Aurora/PolarDB（托管、省心）；② 自建 + 强一致要求高 → InnoDB Cluster（MGR）；③ 自建 + 中等一致 + 灵活拓扑 → Orchestrator + 半同步；④ 存量 MHA 维持，新系统不再用；⑤ 金融级 + 水平扩展需求 → TiDB/OceanBase。

高可用必须配套：① 监控告警——复制状态、延迟、节点健康；② 切换演练——定期混沌演练验证切换有效；③ 客户端重试——切换瞬间连接失败需应用自动重连；④ 读分离中间件——ProxySQL/MySQL Router/ShardingSphere 透明路由；⑤ 回滚预案——切换后新主数据不全时的回退方案。

追问 1：MGR 多主模式为何不如单主模式常用？

多主模式允许多节点同时写，但冲突检测开销大——不同节点并发改同一行需认证回滚，冲突率高时性能急剧下降。且多主要求每次写都序列化认证，对应用写分布要求高（避免热点）。单主模式只有主节点写、从节点读，无写冲突，性能稳定，故障时自动选主切换。除特殊场景（多机房多写），生产默认单主模式。

追问 2：MGR 集群规模为什么限制 3-9 节点？

MGR 用 Paxos 多数派，节点越多写延迟越高（需多数 ACK）、认证开销越大。9 节点是官方测试的软上限。大规模读扩展不靠增加 MGR 节点，而靠 MGR 主集群 + 异步复制的只读从库（Read Replica）——核心高可用用 3-5 节点 MGR，读扩展挂异步从库。这也是 InnoDB Cluster 的典型拓扑。

这道题考察读懂执行计划并据此优化 SQL 的能力。EXPLAIN 是优化器对 SQL 的执行方案"说明书"——访问哪些表、用什么索引、扫描多少行、是否额外排序。本质是通过执行计划反推优化器决策，判断"是否走索引、走对索引、扫描量是否可控、是否有额外开销"。这是慢 SQL 治理的核心技能，每个字段都有明确的优化含义。

EXPLAIN（传统）：输出预估执行计划，不实际执行 SQL，快速但 rows 是估算值。

EXPLAIN FORMAT=JSON（5.6+）：结构化输出，含更详细的成本信息（cost_info）、使用的索引详情。

EXPLAIN ANALYZE（8.0.18+）：实际执行 SQL 并返回每个算子的真实行数、循环次数、耗时（首次/累计）。能发现"估算与实际偏差"——这是诊断优化器选错索引的关键。

optimizer trace（5.6+）：完整记录优化器决策过程（考虑了哪些执行计划、各自成本、为何选最终方案），是终极诊断工具。

趋势：从"估算计划"到"实际执行统计"到"决策过程透明"，可观测性持续增强。EXPLAIN ANALYZE + optimizer trace 组合能解决几乎所有"优化器为何这样选"的疑问。

EXPLAIN 完整字段解读：

type 访问类型从好到差（重点）：

• system：表只有一行（系统表）。
• const：主键/唯一索引等值查询，最多匹配一行，极快。
• eq_ref：JOIN 时被驱动表用主键/唯一索引等值连接，每次精确匹配一行。
• ref：普通索引等值查询，可能匹配多行。
• range：索引范围扫描（>、<、BETWEEN、IN）。
• index：扫描整个索引树（比 ALL 好，因索引比数据小），常见于覆盖索引或 ORDER BY。
• ALL：全表扫描，必须优化。

生产要求：至少达到 range，禁止 ALL；点查应达 const/ref；JOIN 被驱动表应达 eq_ref/ref。

Extra 重点关注（性能信号）：

# 好信号
Using index              # 覆盖索引，免回表，最优
Using where              # 用 WHERE 过滤，正常
Using index condition    # 索引下推 ICP 生效，减少回表

# 坏信号（需优化）
Using filesort           # 额外排序，未用索引有序性。ORDER BY 未走索引
Using temporary          # 用临时表，GROUP BY/DISTINCT 常见，耗内存/磁盘
Using join buffer        # JOIN 无索引用 Block Nested Loop，极慢
Using MRR                # 多范围读优化（中性，有时好有时需关）

EXPLAIN ANALYZE 示例（8.0+）：

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID';

# 输出示例（关键看 actual rows 与 loops）
-> Hash join: 1.2ms (actual time=0.1..1.2 rows=500 loops=1)
    -> Index lookup on o using idx_status: 0.5ms
       (actual time=0.05..0.4 rows=500 loops=1, cost=200)
    -> Index lookup on u using PRIMARY: 0.6ms
       (actual time=0.01..0.01 rows=1 loops=500, cost=150)
# loops=500 表示被驱动表查了 500 次，每次 1 行 → 驱动表 500 行

EXPLAIN ANALYZE 的 actual rows 与普通 EXPLAIN 的 rows 对比，可发现统计信息偏差。

解读四步法：① 看 type 是否走索引、级别够不够；② 看 key/key_len 是否走对索引、联合索引用了几列；③ 看 rows 扫描量是否可控；④ 看 Extra 有无 filesort/temporary。四步定位 90% 的 SQL 性能问题。

多表 JOIN 解读：注意 id 顺序（id 大先执行）、驱动表与被驱动表关系（rows 小的应做驱动表）、被驱动表 JOIN 字段是否有索引（type 应为 ref/eq_ref）。Using join buffer 表示无索引用 BNL，必须加索引。

optimizer trace 定位选错索引：

SET optimizer_trace = 'enabled=on';
SELECT ...;  # 执行待分析 SQL
SELECT * FROM information_schema.OPTIMIZER_TRACE;

# 输出 JSON，关注：
# - considered_execution_plans: 优化器考虑过的所有计划及成本
# - rows_estimation: 各索引的扫描行数估算
# - chosen: 最终选择的计划及原因

追问 1：key_len 如何判断联合索引用了几列？

key_len = 各使用列的索引长度之和。计算规则：INT 4 字节、BIGINT 8、CHAR(n) utf8mb4 = 4n、VARCHAR(n) utf8mb4 = 4n+2（变长）+1（NULL 标记）。例如联合索引 (user_id BIGINT, status VARCHAR(10))，若 key_len=8 表示只用 user_id；key_len=8+42+1=51 表示两列都用上（status 非空则 +42，可空 +43）。借此判断最左前缀用了几列、是否有列未参与索引。

追问 2：EXPLAIN 显示走了索引但仍然慢，可能原因？

① 回表次数多——二级索引选择性差，扫索引后大量回表，不如全表扫描。看 rows 与 filtered 估算回表量；② 范围查询扫描量大——type 是 range 但 rows 很大；③ Using filesort/temporary——虽然定位快但排序/聚合开销大；④ 锁等待——非执行计划问题，是并发竞争；⑤ 统计信息过期——rows 估算与实际偏差大，ANALYZE TABLE 修正。用 EXPLAIN ANALYZE 看真实耗时分布定位瓶颈算子。

窗口函数：ROW_NUMBER/RANK/DENSE_RANK、SUM() OVER (PARTITION BY … ORDER BY …) 等，在结果集上开窗而不折叠行，适合排行榜、去重留最新、会话连续区间。对比：GROUP BY 会折叠行；窗口保留明细并附聚合列。

CTE：WITH … AS (…) 提升可读性；WITH RECURSIVE 做树/图有界遍历（组织树、账单层级）。约束：递归终止条件必须明确，防止无限递归；深树优先物化路径/闭包表。

-- 每用户保留最新一条订单
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;

工程注意：窗口/CTE 不能替代索引设计；大分区窗口易临时表/文件排序，仍要看 EXPLAIN。

上线门禁：慢查询阈值、事务边界评审、主从延迟告警。表达提纲：执行计划→锁/索引证据→改写与容量。

考察InnoDB 锁冲突的自动检测与死锁排查能力。核心回答：InnoDB 通过等待图（Wait-for Graph）检测死锁环，当发现环路时选择"代价最小"的事务回滚（Undo Log 量小的年轻事务）；生产排查依赖 SHOW ENGINE INNODB STATUS 中的 LATEST DETECTED DEADLOCK 区块，结合慢日志与业务代码分析加锁顺序。

阶段 1：应用层超时回滚。早期数据库无死锁检测，靠应用设置锁等待超时（innodb_lock_wait_timeout），超时后报错由应用重试。简单粗暴但延迟高、无法定位根因。

阶段 2：InnoDB 死锁检测（MySQL 默认）。InnoDB 维护事务等待图，后台线程检测环路，发现死锁立即回滚一个事务（ER_LOCK_DEADLOCK 1213），另一个事务继续。响应快但检测本身有 CPU 开销。

阶段 3：死锁监控与可视化（MySQL 8.0）。8.0 引入 performance_schema.data_locks / data_lock_waits，可实时查询锁持有与等待关系，配合 sys 视图定位高频死锁 SQL。

阶段 4：乐观锁/无锁化规避。业务层用版本号乐观锁（CAS）、幂等设计、消息队列串行化，从根本上减少死锁概率。这是架构层面的演进方向。

死锁产生的四个必要条件（同时满足才会死锁）：

• 互斥：资源（行锁）一次只能被一个事务持有。
• 占有且等待：事务持有部分锁，同时等待其他事务持有的锁。
• 不可剥夺：锁只能由持有事务主动释放，不能被强制剥夺。
• 循环等待：事务间形成等待环路（T1 等 T2，T2 等 T1）。

InnoDB 死锁检测机制：

• 等待图（Wait-for Graph）：每个事务是节点，等待关系是有向边（T1 -> T2 表示 T1 等 T2 释放锁）。检测线程定期遍历图，发现环即死锁。
• 受害者选择策略：回滚Undo Log 量最小的事务（即修改最少/最年轻的事务），减少回滚成本。
• 检测开销：高并发大量锁等待时，遍历等待图的复杂度为 O(N^2)，可能成为性能瓶颈。MySQL 8.0.29+ 引入 innodb_deadlock_detect=OFF 关闭检测，靠超时解决（不推荐，除非极端场景）。

常见死锁场景：

死锁排查四步法：

① 获取死锁日志：

-- 查看最近一次死锁详情（仅保留最后一次）
SHOW ENGINE INNODB STATUS;
-- 关注 LATEST DETECTED DEADLOCK 区块

-- MySQL 8.0 实时查询锁等待（可多次执行观察）
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'orders';

② 解读死锁日志关键信息：

• 事务1/事务2 的 HOLDING：已持有的锁（LOCK_MODE X / S / GAP / NEXT KEY）。
• WAITING FOR：正在等待的锁。
• RECORD LOCKS：具体锁住的主键值与索引。
• WE ROLL BACK TRANSACTION (2)：回滚了事务 2。

③ 定位业务代码：根据死锁日志中的 SQL 语句，定位到具体代码位置，分析两个事务的加锁顺序差异。

④ 修复验证：统一加锁顺序、缩小事务范围、降级隔离级别、或改为乐观锁。修复后在测试环境用并发压测验证。

监控与预防：

-- 查看死锁发生次数（累计值，重启清零）
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

-- 开启死锁信息打印到 error log（MySQL 8.0.17+）
SET GLOBAL innodb_print_all_deadlocks = 1;
-- 这样所有死锁都会记录在 error log，便于事后分析

追问 1：为什么死锁检测在高并发下会成为性能瓶颈？

死锁检测需要遍历等待图，复杂度约为 O(N^2)（N 为等待锁的事务数）。当并发极高（如秒杀场景数万事务同时等待），检测线程消耗大量 CPU 遍历图，反而拖慢整体性能。MySQL 8.0 提供 innodb_deadlock_detect=OFF 关闭检测，靠 innodb_lock_wait_timeout 超时回滚。但关闭检测后，死锁事务会 hang 住直到超时，延迟更高。折中方案：热点行拆分（如库存拆 100 个桶），减少单点锁竞争，从根本上降低死锁概率。

追问 2：RC 隔离级别下为什么死锁更少？

RC（Read Committed）下无间隙锁（Gap Lock），只有记录锁。而 RR（Repeatable Read）下范围查询会加 Next-Key Lock（记录锁+间隙锁），间隙锁与插入意向锁（Insert Intention Lock）不兼容，极易引发死锁。因此如果业务可接受幻读（或应用层幂等），将隔离级别降为 RC 能显著减少死锁。

考察千万/亿级大表无停机变更 schema的工程能力。核心要讲清：① MySQL 原生 Online DDL（INPLACE/COPY/INSTANT）的能力边界与锁粒度；② pt-osc 与 gh-ost 通过"影子表 + 触发器/二进制日志回放"实现在线变更的原理差异；③ 选型权衡——何时用原生、何时用 gh-ost、何时走双写灰度。它是 DBA 与架构师的协作题——能否讲清三者锁影响、回滚成本、流量切换策略，是大表治理的实战分水岭。

原生 COPY 方式（MySQL 5.5 前）：ALTER TABLE 创建新表复制全量数据，全程元数据锁（MDL）独占，阻塞所有读写，大表等于停机。

Online DDL（MySQL 5.6+）：引入 INPLACE 算法，建/删二级索引、加列等可在 InnoDB 内部完成，允许并发 DML（row log 回放）。但仍有限制：ADD COLUMN 走 INPLACE 需重建表；部分操作（改列类型、改字符集）仍 COPY 全程锁。

pt-online-schema-change（pt-osc，Percona Toolkit）：经典第三方方案。创建影子表 → 分块 chunk 拷贝数据 → 用触发器同步变更期间的增量 DML 到影子表 → rename 交换。痛点：触发器对原表每次写都生效，高写入负载下性能损耗显著；外键表需用 --alter-foreign-keys-method 处理关联（auto/rebuild_link 等），不是"不支持外键"。

gh-ost（GitHub，2016）：放弃触发器，改解析 binlog 回放增量到影子表。无触发器开销、不与外键冲突、可动态调 throttling、支持暂停与回滚。已成为 GitHub/字节等大厂大表变更首选。

INSTANT DDL（MySQL 8.0.12+）：ADD COLUMN（末尾）/DROP COLUMN 仅改元数据，毫秒级、零数据拷贝、不锁表。MySQL 8.0.29 扩展 INSTANT 支持任意位置加列/删列。趋势：原生 INSTANT 覆盖越来越多的轻量变更，gh-ost 收缩到需重建表的复杂变更；超大表跨实例迁移走向双写灰度。

影子表三件套：pt-osc/gh-ost 本质都是"创建影子表 → 全量拷贝 + 增量同步 → 原子 rename"。差异在增量同步机制。

锁粒度对比：

双写灰度方案：当变更涉及数据迁移/分库分表/跨实例，pt-osc/gh-ost 不适用。采用应用层双写——新表与老表同步写，读走老表；历史数据用 mysqldump/DataX 全量回填、增量同步追平变更后，灰度切读到新表；全量验证后停老表写。本质是把 DDL 风险转化为应用层可控的灰度流程，代价是双写复杂度与一致性保障。

选型决策树：① 操作在 INSTANT 支持范围（MySQL 8.0.12+ 末尾加列、8.0.29+ 任意位置加列）→ 直接 INSTANT；② 简单加索引/加列且无外键、可接受触发器开销 → pt-osc；③ 高写入负载/有外键/需暂停回滚 → gh-ost；④ 跨实例/分库分表/数据重算 → 双写灰度。

# gh-ost 大表加列：核心参数
gh-ost \
  --host=mysql-master:3306 --user=ghost --password=xxx \
  --database=order_db --table=order_detail \
  --alter="ADD COLUMN remark VARCHAR(255) DEFAULT ''" \
  --allow-on-master \                   # 在主库执行（连主库 binlog）
  --chunk-size=2000 \                   # 每批拷贝行数，控制负载
  --max-load='Threads_running=50' \     # 超阈值自动 throttle
  --critical-load='Threads_running=200' \ # 超阈值中止
  --execute

# 原生 INSTANT 加列（MySQL 8.0.12+，毫秒级）
ALTER TABLE order_detail ADD COLUMN remark VARCHAR(255) DEFAULT '', ALGORITHM=INSTANT;

追问 1：gh-ost 相比 pt-osc 的根本优势是什么？

根本优势是无触发器。pt-osc 的触发器挂在原表上，每次 INSERT/UPDATE/DELETE 都额外执行触发器逻辑，高写入下原表写吞吐下降 20-50%，外键表还需额外处理。gh-ost 作为 binlog 消费者旁路运行，原表零额外开销，且可动态 throttle、暂停、回滚（cut-over 前可随时放弃影子表）。代价是依赖 binlog row 格式且需额外部署 gh-ost 进程。

追问 2：原生 Online DDL 的 INPLACE 真的不锁表吗？

"INPLACE"是 InnoDB 引擎层概念，不等于"无 MDL 锁"。它分 START/END 两个阶段：START 需 MDL 独占（极短，但若此时有长事务持有 MDL 会阻塞甚至锁等待超时）；中间允许并发 DML；END 又需短暂 MDL 独占交换元数据。所以 INPLACE 仍可能因长事务导致 MDL 阻塞，并非完全无锁。INSTANT 才是真正的元数据级零拷贝。

追问 3：双写灰度如何保证新老表最终一致？

三层保障：① 写双写——应用同时写新老表，任一失败需补偿（按主表为准）；② 增量同步——用 Canal/DTS 订阅老表 binlog 追平全量迁移期间的增量变更；历史数据用 mysqldump/DataX 全量回填。标准顺序：先启 binlog 订阅 → 再跑全量迁移 → 最后用 binlog 位点追平增量；③ 对账校验——切换前按主键分片 COUNT/SUM 对账，不一致不切。灰度切读按流量百分比逐步放量，全量验证后再停老表写。

gh-ost 在线 DDL 最该用量化证据支撑的选型结论是：限速参数可控、迁移速率与耗时可预估、主从延迟与线上 RT 影响可控。

这道题考察对 MySQL 类型转换规则与索引有序性关系 的理解：查询条件两侧类型不一致时，MySQL 按既定规则做隐式类型转换，一旦转换作用在索引列上（对列套了函数/CAST），索引有序性被破坏，优化器只能放弃索引走全表扫描（type=ALL）。这是当前高频的"看似该走索引却全表扫描"排查题，本质是比较前的类型归一化打断了 B+树的有序检索路径。

显式转换时代：SQL 标准鼓励用 CAST() / CONVERT() 显式声明类型，转换点清晰但写法冗长、易遗漏。

隐式转换（MySQL 默认行为）：为易用性引入——操作符两侧类型不同，MySQL 按 Type Conversion Rules 自动归一，优先把一切都往数值或字符串靠。代价是转换点不可见，索引可能在你不知情时失效。

与 Oracle/PostgreSQL 的差异：Oracle 按上下文做字符↔数值转换；PostgreSQL 对类型不匹配往往直接报错，问题在开发期暴露。MySQL 的"静默转换 + 全表扫描"最危险——不报错、只是慢。

核心规则：字符串列与数字常量比较时，MySQL 把字符串列转换为数字（CAST(col AS DECIMAL)），而非把常量转字符串。后果：索引按字符串字典序组织，查询却变成"对每行 col 算数字后比较"，列被函数包裹 → 索引无法定位。

为什么"数字列 vs 字符串常量"反而走索引：被转换的是常量，索引列 id 未被函数包裹，有序性保留。规律一句话：索引列永远不要被函数/隐式 CAST 包裹。

1. 参数类型严格匹配 DDL：varchar 列永远传字符串。MyBatis 中 #{phone} 若 Java 类型是 Long，会生成数字常量 → 触发失效，应把参数声明为 String。

2. 关联字段类型/字符集对齐：a.user_id(int) = b.user_id(varchar) 会导致一侧被 CAST → 索引失效 + 每行转换开销。建表统一类型，且字符集/排序规则也要一致，否则隐式 collation 转换同样废掉索引。

3. 厘清"主键空洞"的成因：隐式转换本身不制造主键空洞；空洞主要来自 DELETE、事务回滚、REPLACE、批量插入失败回滚。排查慢查询时要区分"索引失效"与"空洞导致页稀疏"是两类独立问题。

4. EXPLAIN + SHOW WARNINGS 验证：type=ALL 且 key=NULL 但你认为该走索引时，用 SHOW WARNINGS 看优化器改写后的 SQL，常能看到 CAST(...) 暴露在条件里。

追问 1：collation 不一致也会让索引失效吗？ 会。两表 JOIN 字段类型相同但 collation 不同（如 utf8mb4_general_ci vs utf8mb4_0900_ai_ci），MySQL 对一侧做排序规则转换，等价于对列套函数 → 失效。统一建表规范是根治手段。

追问 2：为什么有时"该失效"却走了索引？ 因为被转换的是常量而非列。WHERE id = '1001'（id 是 int）实际把 '1001' 转成 1001，列 id 未变。失效与否取决于"被转换的是列还是常量"。

追问 3：隐式转换还有什么副作用？ 字符串转数字时 '123abc' 被转成 123（截断语义），WHERE code = 123 会误匹配 '123abc' 行，造成逻辑错误而不仅是性能问题。

官方 Type Conversion Rules 全貌：除"字符串列 vs 数字常量→列转数字"这条最高频规则外，MySQL 还有几条常被忽略的归一化规则：① 两边都是字符串→按字符串逐字节比对，索引正常；② 两边都是数值→按数值比对；③ 日期/时间列 vs 数字常量：如 create_time = 20260907（数字）会转成日期再做比较，字段本身不动→索引可用（但可读性差，推荐写 '2026-09-07'）；④ NULL 与任何值比较结果都是 NULL（含 NULL = NULL 为 NULL，判等须用 IS NULL）。总口诀：字符串向数字看齐、日期按字面归一，谁被转换谁失效。

IN 列表的隐式转换（高频漏点）：WHERE uid IN (1001,1002)（uid 为 varchar）中，IN 列表是数字常量，MySQL 会把 uid 列逐行转数字→索引失效、全表扫描。正确写法：WHERE uid IN ('1001','1002')，使列表与列同为字符串。这条与"数字列 vs 字符串常量走索引"不冲突——被转换的仍是列（列是 varchar、常量列表是数字），所以失效。

-- bad：IN 列表数字，varchar 列被逐行转数字 → 索引失效
SELECT * FROM t WHERE uid IN (1001,1002);
-- good：列表与列同为字符串，类型匹配走索引
SELECT * FROM t WHERE uid IN ('1001','1002');

显式转换函数清单（CAST / CONVERT）：需要主动转换时优先转常量，绝不对 WHERE/JOIN ON 的索引列套函数。CAST(expr AS type) 与 CONVERT(expr, type) 等价，支持类型：CHAR(N) / SIGNED / UNSIGNED / DECIMAL(p,s) / DATE / DATETIME / TIME。CONVERT(expr USING charset) 专做字符集转码（如 CONVERT('abc' USING gbk)）。注意：CAST('123abc' AS SIGNED) 得 123、CAST('abc' AS SIGNED) 得 0，与隐式转换截断语义一致。

如何精准定位隐式转换（不止 EXPLAIN）：① EXPLAIN 只看现象（type=ALL、key=NULL）；② SHOW WARNINGS 看优化器改写后的 SQL，常能直接看到暴露的 CAST(...)；③ MySQL 8.0 优化器 trace（精准）：开启后在 information_schema.optimizer_trace 中搜索 converted，精确定位哪个操作数被做了类型转换。

SET optimizer_trace='enabled=on';
EXPLAIN SELECT * FROM t WHERE uid=123;
SELECT * FROM information_schema.optimizer_trace;  -- 搜 converted 看被转换的操作数
SET optimizer_trace='enabled=off';

高频工程表达五连问：Q1 where str=123（str 为 varchar 索引）为何不走索引？→ 字符串列被转数字，列被函数包裹。Q2 where int='123'（int 索引）走索引吗？→ 走，常量转数字、列不动。Q3 join on A.varchar = B.int 哪边失效？→ A 表 varchar 列转数字、A 索引失效。Q4 varchar IN (1,2,3) 为何慢？→ IN 列表数字导致列转数字、索引失效，改字符串列表。Q5 字符集不同也会隐式转换吗？→ 会，collate 不一致对一侧做排序规则转换等价于对列套函数、索引失效，建表须统一字符集与 collate。

这道题考察对 MyBatis 运行时机制的完整认知：缓存（减少 DB 访问）、插件（无侵入增强 SQL 执行链路）、SQL 拼接安全性（注入防护）。这是 Java 持久层的"基础分水岭"——很多开发者只用过 #{}，却说不清一级缓存为何在 Spring 里几乎不命中、二级缓存为何容易脏读、插件如何用责任链实现分页/脱敏/分库分表。

iBatis → MyBatis：Apache iBatis 2010 年迁移到 Google Code 后更名 MyBatis，核心不变（SQL 与代码分离、手动映射），强化了注解与动态 SQL。

缓存演进：早期只有 SqlSession 一级缓存；后续加入 Mapper 级别二级缓存，并通过 <cache> / @CacheNamespace 支持 EHCache、Redis 等第三方实现。

插件机制：MyBatis 用责任链 + JDK 动态代理在四大组件（Executor/StatementHandler/ParameterHandler/ResultSetHandler）上开放拦截点，使分页、数据权限、加解密、审计等能力可无侵入注入。

一级缓存（SqlSession 级别，默认开启）：同一个 SqlSession 内相同查询（相同 statementId + 参数 + 分页）命中缓存。执行 update/commit/close/clearCache 会清空。坑点：Spring 整合后 SqlSession 由 SqlSessionTemplate 管理，每次 Mapper 调用常是新 Session → 一级缓存在 Web 层几乎不命中（同一事务/同一 SqlSession 内才复用）。

二级缓存（Mapper / namespace 级别）：需 cacheEnabled=true 且 Mapper 显式声明 <cache/>。跨 SqlSession 共享，但多表关联查询跨 namespace 更新不会互清 → 易脏读，因此生产多用 Redis 替代。

插件原理：实现 Interceptor + @Intercepts 声明拦截点，Plugin.wrap(target, interceptor) 对目标做动态代理，形成拦截链，invocation.proceed() 放行到下一环。

// #{}：预编译占位符，参数作为值传入 PreparedStatement，防注入
SELECT * FROM user WHERE name = #{name};  // → WHERE name = ?  [预编译]

// ${}：字符串拼接，参数直接拼进 SQL 文本，存在注入风险
SELECT * FROM user WHERE name = '${name}'; // name="x' OR '1'='1" → 注入！

// ${} 仅可用于表名/列名/排序方向等"结构部分"，且须白名单校验
SELECT * FROM ${tableName} ORDER BY ${column} ${order};

插件实战：① 分页插件（PageHelper/MP）拦截 Executor.query 重写 SQL 加 limit + count；② 数据脱敏/加解密拦截 ParameterHandler / ResultSetHandler；③ 分库分表拦截 StatementHandler 改写路由。下面是慢 SQL 审计插件骨架：

@Intercepts({@Signature(type = Executor.class, method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class AuditInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation inv) throws Throwable {
        long start = System.nanoTime();
        try { return inv.proceed(); } finally {
            long ms = (System.nanoTime() - start) / 1_000_000;
            if (ms > 1000) log.warn("slow sql {}ms", ms);
        }
    }
    @Override public Object plugin(Object target) {
        return Plugin.wrap(target, this); // 责任链动态代理包装
    }
}

注入防护：禁止 ${} 拼接用户输入；需要动态排序/表名时，对列名做白名单枚举校验（ALLOWED.contains(col)）后再拼接。

追问 1：为什么 Spring 事务内一级缓存有时能命中？ Spring 把 SqlSession 绑定到事务上下文，同一事务内多次 Mapper 调用复用同一 SqlSession，一级缓存生效；事务结束即释放。

追问 2：多个插件执行顺序如何？ 按配置顺序从外到内嵌套代理，调用时从第一个插件进入，proceed() 依次向内，返回时逆序。顺序影响改写结果（如分页插件应在脱敏之前先改写 SQL）。

追问 3：MyBatis-Plus 的分页插件与 PageHelper 能共存吗？ 不建议。两者都拦截 Executor.query 且都依赖 ThreadLocal 存储分页参数，并存会导致分页条件错乱、count 语句异常。

这道题考察 ORM 选型判断力：MyBatis 系（手写 SQL、灵活、适合复杂多表与报表）与 JPA/Hibernate（对象化、DDD 友好、自动 CRUD、但复杂查询易失控）的取舍，以及两者工程化增强（MP 通用 CRUD/分页/乐观锁、JPA 脏检查/二级缓存）的底层原理。

JPA 谱系：EJB EntityBean（笨重）→ JPA 规范（Hibernate 实现）→ Spring Data JPA（方法名派生查询）。强调"对象即数据库"，屏蔽 SQL。

MyBatis 谱系：iBatis → MyBatis（SQL 与代码分离）→ MyBatis-Plus（在 MyBatis 上叠加通用 Mapper/Service、条件构造器、分页/乐观锁/逻辑删除插件），保留手写 SQL 灵活性的同时补齐"CRUD 样板"短板。

乐观锁原理（两者一致）：更新时携带版本号，SQL 形如 UPDATE t SET col=?, version=version+1 WHERE id=? AND version=?；影响行数=0 即版本冲突，业务层自旋重试。MP 用 @Version + 乐观锁拦截器自动拼接；JPA 用 @Version 由脏检查触发。

@TableName("orders")
public class Order {
    @TableId private Long id;
    private BigDecimal amount;
    @Version private Integer version; // 版本号
}
// MP 拦截器自动改写为：UPDATE orders SET amount=?, version=version+1
//                    WHERE id=? AND version=?  —— 影响行数0即冲突，自旋重试

分页实现：MP 配置 MybatisPlusInterceptor + PaginationInnerInterceptor，调用 page() 时自动追加 count 与 limit；JPA 用 Pageable / PageRequest。注意 MP 分页插件与 PageHelper 不能并存。

逻辑删除：MP @TableLogic 让查询自动追加 WHERE deleted=0，更新转软删；JPA 需 @SQLDelete + @Where 实现。

选型建议：互联网高并发、复杂多表 join、分析报表 → MyBatis/MP；企业内部系统、CRUD 密集、强 DDD 模型 → JPA；常见混合架构：JPA 管主数据 + MyBatis 做报表查询。

追问 1：JPA 的 N+1 如何产生与解决？ 延迟加载关联集合时，遍历 N 个父对象各发 1 条子查询 → N+1。解法：JOIN FETCH / @EntityGraph 一次性抓取，或 @BatchSize 批量查询。

追问 2：MP Wrapper 在大查询下可读性如何？ 复杂条件用 QueryWrapper 链式拼接会很长且难调试，可退回自定义 XML SQL 兼顾灵活与可读。

追问 3：乐观锁在批量更新为何易失效？ 批量 UPDATE 通常一次性改多行，version 条件难以逐行匹配，应改为逐条带 version 更新或改用业务幂等键。

长事务拖垮并发：锁等待、复制延迟、Undo 膨胀。值班先止血再追代码，禁止一上来改业务逻辑。

InnoDB 行锁持有到事务结束；长事务拉长锁与 MVCC 历史链。应用层事务边界应短、纯本地；远程调用不得包在 DB 事务里。超时要同时看锁等待超时与业务事务超时。

值班顺序：

1. information_schema.innodb_trx / 进程列表找长事务与持锁会话。
2. 评估能否 kill（权衡回滚更久 vs 继续堵）。
3. 限流写入入口或切只读止血。
4. 查是否缺提交、大事务批处理、事务内 RPC。
5. 改小事务与超时配置走变更门禁（对照 G04）。

信号：锁等待、history list length、复制延迟、Rows_examined 飙升。参数：innodb_lock_wait_timeout、应用事务超时、批大小、是否允许 kill。变更单写清：杀谁、影响面、预计回滚时长。

失败模式：盲目 kill 导致更长回滚；在长事务里做远程调用；用更大超时「拖过高峰」。回滚点：恢复旧超时配置；业务用补偿而非无限拉长事务。

长事务常表现为锁等待雪崩：先看 innodb_lock_wait 与事务持续时间，再判断是业务慢还是索引缺失。超时要能区分「语句超时」与「事务超时」。

止血顺序：①限流入口 ②杀掉明显空闲长事务 ③临时降隔离/拆批（有评审）④补索引与拆事务后回放。

量化：innodb_lock_wait_timeout 默认 50s（OLTP 建议 1~5s），长事务判定 = 持续 > 60s 或 history list length > 10 万；kill 前先估回滚时长（约等于已修改行数 / 1000 行每秒）。演进：事务内禁止 RPC / HTTP；大事务拆批（单批 ≤ 1000 行）并对齐复制延迟阈值；超时分「语句超时」与「事务超时」两层配置，避免只调一层。