面试复习：MySQL 索引、事务 MVCC、日志与调优（初中高级三层）

先讲个段子。我第一次被问「MySQL 索引为什么用 B+ 树」是在三年经验的时候，我自信满满地答了「因为快」，面试官微笑着问：「比谁快？」——场面一度安静得能听见空调外机在转。后来做了八年后端，发现 MySQL 这一块其实是面试里最容易从“背八股”滑到“讲线上事故”的专题：你答得越浅，追问就越深，直到你把自己线上踩过的坑全交代出来。

这篇是我给自己整理的复习备忘录，同时也是给读者的“划重点”。内容根据公开面经（各大八股站与技术社区里反复出现的题目与追问）和官方文档整理改编，不属于我本人某一场具体面试的记录。全文按难度分三层，方便你对号入座：

• 🌱 初级（0–3 年）：是什么、怎么用，概念要答准；
• 🌿 中级（3–5 年）：为什么、怎么选，底层机制与常见坑；
• 🌳 高级（5 年+）：怎么落地、出问题怎么办，架构取舍、线上治理、量化复盘。

每层都有「标准回答要点 / 追问链 / 加分项」。版本说明：除特别标注外，均以 MySQL 8.0（InnoDB）为准；5.7 的差异会点出来。

一、知识地图：MySQL 面试到底在考什么

把所有考点压成一张图：一条 SQL 从客户端进来，经过 Server 层（连接、解析、优化、执行），落到 InnoDB 引擎层（Buffer Pool、索引、锁、事务），再靠三种日志保证持久与复制。你只要能沿着这条线把每个点讲出“它解决什么问题、代价是什么”，八成的题都能接住。

二、索引：B+ 树、联合索引与失效

🌱 初级：索引是什么，为什么是 B+ 树

标准回答要点：

• 索引是为了减少磁盘 IO 而维护的有序数据结构。InnoDB 的数据页默认 16KB，读一页是一次 IO，索引的目标就是让“找到一行”所需的页数尽量少。
• InnoDB 用 B+ 树：非叶子节点只存键和子页指针，因此一页能放很多键（扇出大），树很矮；叶子节点存全部数据（聚簇索引）或主键值（二级索引），且叶子之间用双向链表相连，范围查询顺着链表扫就行。
• 粗算：主键 bigint（8 字节）加指针约 6 字节，一个非叶子页能放约 1170 个键；叶子页按一行 1KB 算放 16 行。三层树就能容纳 1170 × 1170 × 16 ≈ 2000 多万行，一次查询最多 3 次 IO（根页通常常驻内存）。这就是“单表两千万”这个经验值的来源，它是估算不是红线。
• 为什么不用 B 树？B 树非叶子节点也存数据，扇出小、树更高；范围查询要中序遍历，不如叶子链表顺畅。为什么不用哈希？哈希只支持等值，不支持范围和排序。为什么不用红黑树/二叉树？树太高，IO 次数多。
• 聚簇索引：叶子节点就是整行数据，一张表只有一个（主键；没主键则选第一个非空唯一索引；再没有就用隐藏 6 字节 row_id）。二级索引：叶子存“索引列 + 主键值”，查完还要拿主键回聚簇索引取整行，这一步叫回表。

追问链：

1. 为什么建议用自增主键？——顺序插入基本只往最右页追加，页分裂少、空间利用率高；用 UUID 这类无序主键会导致随机插入，页分裂和碎片严重，还让所有二级索引变胖（每个二级索引叶子都存主键值）。
2. 什么是覆盖索引？——查询需要的列都在二级索引里，不用回表，explain 的 Extra 显示 Using index。
3. 索引是不是越多越好？——不是。每个索引都占空间，每次写入都要维护，写多读少的表尤其明显。

加分项：说出“扇出”这个词并会算三层树容量；提一句 8.0 支持降序索引、函数索引（8.0.13）和不可见索引（invisible index，用来“假删除”验证索引是否真没用）。

🌿 中级：联合索引、最左匹配、索引下推与失效场景

标准回答要点：

联合索引 (a, b, c) 在 B+ 树里是先按 a 排序，a 相同按 b，b 相同按 c。所以：

索引下推（ICP，5.6 引入）：没有 ICP 时，存储引擎按定位条件取出主键回表，把整行交给 Server 层再判断剩余条件；有 ICP 时，剩余条件只要用的是索引里已有的列，就在引擎层先过滤，少回表。explain 的 Extra 里是 Using index condition。

建联合索引的排序原则：等值列在前、范围列在后；区分度高的放前面（但要服从最左匹配和排序需求，不是绝对）；能覆盖就覆盖，把常用的 select 列追加进去，用空间换回表。

索引失效的常见场景（务必会解释“为什么”）：

1. 对索引列做函数或运算：where date(create_time)='2026-01-01'、where id+1=10。B+ 树里存的是原值，对列施加函数后无法按有序性定位。改写成范围：create_time >= '2026-01-01' and create_time < '2026-01-02'。8.0.13 之后可以建函数索引兜底。
2. 隐式类型转换：varchar 列 where phone = 13800000000（传数字），MySQL 会把列转成数字比较，等于对列用了函数，索引失效，还会因为字符串转数字丢精度而查出错误多行。反过来数字列传字符串则没事（字符串常量被转成数字）。
3. 左模糊 / 全模糊：like '%abc' 无法确定起点。like 'abc%' 可以走索引。
4. or 的其中一侧没索引：整体退化成全表扫描；两侧都有索引时可能走 index merge。
5. 字符集/排序规则不一致的 join：一个表 utf8、一个表 utf8mb4，关联时被驱动表的列被转换，索引失效。
6. 优化器认为全表更划算：命中行数占比很高（经验上超过 20%–30%），回表成本大于顺序扫，就不走索引。这种“失效”不是 bug，是成本模型的选择。
7. 不等于、not in、is not null：不是一定失效，取决于选择性，别背成“必然失效”。

追问链：

1. select * 为什么不好？——破坏覆盖索引、增加网络与内存开销、表结构变化时易出问题。
2. 为什么二级索引查询有时比全表还慢？——回表是随机 IO，命中行数多时随机 IO 总量超过一次顺序扫描。
3. 索引的区分度怎么衡量？——count(distinct col)/count(*)，或看 SHOW INDEX 里的 Cardinality（是采样估算值，可 ANALYZE TABLE 刷新统计）。

加分项：提到统计信息不准会导致优化器选错索引，处理办法是 ANALYZE TABLE、调大 innodb_stats_persistent_sample_pages，或在极端情况下用 FORCE INDEX / 优化器 hint 兜底（但要写清楚原因，防止后人不敢动）。

🌳 高级：索引治理，怎么在线上落地

标准回答要点：

• 索引不是拍脑袋建的，是靠数据驱动的：用慢查询日志（long_query_time、log_queries_not_using_indexes 谨慎开）+ performance_schema / sys 库（sys.schema_unused_indexes、sys.schema_redundant_indexes）找出冗余索引（(a) 被 (a,b) 覆盖）与未使用索引。删除前先设为 INVISIBLE 观察一个业务周期，没问题再真删，出问题秒级 VISIBLE 回滚。
• 大表加索引的风险：8.0 加二级索引是 online DDL（INPLACE，不阻塞 DML），但会消耗大量 IO 与 CPU，且过程中需要短暂的元数据锁（后面 DDL 一节展开）；主从架构下还会造成从库延迟，因为从库回放同样要花这么长时间。
• 索引与写放大：一张表 10 个索引，每次 insert 要改 11 棵 B+ 树；update 只在被更新列参与的索引上产生变更，但仍要写 undo/redo。高写入表的索引数量应被当作“预算”管理，经验上一般控制在 5 个以内，超过要有理由。
• 量化复盘：一个典型的治理案例可以这样讲——“订单表 1.2 亿行，某列表页接口 P99 从 1.8s 降到 60ms：把 (user_id, status, create_time) 建成联合索引，并把 select * 改成只取列表所需 6 列，命中覆盖索引，回表次数从平均 2000 降到 0”。数字要能自洽、能讲清计算依据。

追问链：热点行更新、自增 ID 打满（int 上限约 21 亿，bigint unsigned 才是长期方案）、唯一索引在高并发下的插入死锁（后面锁的章节讲）、以及“为什么线上不推荐外键”（锁扩散、分库分表不友好、级联不可控，通常用应用层保证）。

加分项：能讲清楚“索引选择性 + 数据倾斜”的组合坑：status 列 99% 是 1，查 status=0 走索引很快、查 status=1 就该全表；同一条 SQL 因参数不同而执行计划不同，绑定参数在预编译下更易踩到。

三、事务：隔离级别、MVCC 与锁

🌱 初级：ACID 与四种隔离级别

标准回答要点：

• ACID：原子性（undo log 保证回滚）、一致性（是目标，靠 A、I、D 加应用约束共同达成）、隔离性（锁 + MVCC）、持久性（redo log）。
• 并发下的三个问题：脏读（读到别人未提交的数据）、不可重复读（同一事务两次读同一行结果不同，针对“修改”）、幻读（同一事务两次范围查询行数不同，针对“新增/删除”）。

查看与设置：SELECT @@transaction_isolation;，SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;（8.0 里变量叫 transaction_isolation，5.7 的 tx_isolation 已被弃用）。

追问链：InnoDB 的 RR 真的彻底解决幻读了吗？——见下面的中级层，这是最高频的“陷阱题”。

🌿 中级：MVCC 与锁机制的真相

MVCC 怎么实现（能画图讲清楚）：

• 每行有隐藏列：DB_TRX_ID（最近一次修改它的事务 id，6 字节）、DB_ROLL_PTR（回滚指针，指向 undo log 中的上一版本，7 字节）。一行的多个历史版本通过 roll ptr 串成版本链。
• 事务做快照读（普通 select）时，会生成一个 ReadView，包含四个东西：m_ids（生成时刻活跃的事务 id 列表）、min_trx_id（活跃事务里最小的）、max_trx_id（下一个将分配的事务 id）、creator_trx_id（当前事务自己）。
• 可见性判断：沿着版本链，取到某个版本的 trx_id：等于 creator → 可见（自己改的）；小于 min_trx_id → 已提交，可见；大于等于 max_trx_id → 之后才开启的事务改的，不可见；落在 [min, max) 之间：如果在 m_ids 里说明当时还没提交，不可见，否则可见。不可见就顺着 roll ptr 找上一版本，直到找到可见的或到头。
• RC 与 RR 的区别只在 ReadView 生成时机：RC 每条语句都重新生成，所以能读到别人刚提交的数据（不可重复读）；RR 在事务里第一次快照读时生成并复用，所以整个事务看到的是同一个快照。
• delete 只是打删除标记，真正的物理删除由 purge 线程在没有事务再需要旧版本时做。所以长事务会让 undo log 无法清理，rollback segment 越来越大，官方文档也建议定期提交事务，包括只读事务。

快照读 vs 当前读：普通 select 是快照读；select ... for update、select ... lock in share mode、update、delete、insert 是当前读，读的是最新已提交版本并加锁。

“RR 彻底解决了幻读吗？”——没有。经典反例：

-- 事务 A（RR）
BEGIN;
SELECT * FROM t WHERE id = 5;        -- 快照读，没有这行
-- 此时事务 B 插入 id=5 并提交
UPDATE t SET name = 'x' WHERE id = 5; -- 当前读，更新了 B 插入的行（影响 1 行）
SELECT * FROM t WHERE id = 5;        -- 快照读，现在“看见”了这行：幻读
COMMIT;

结论：快照读之间不会幻读，但快照读与当前读混用会。想在 RR 下防幻读，需要在事务开始就用 for update 当前读（加 next-key 锁把间隙锁住）。

InnoDB 行锁三兄弟（官方文档语义）：

官方文档举例：索引值 10、11、13、20，则 next-key 的区间是 (-∞,10]、(10,11]、(11,13]、(13,20]、(20,+∞)。RC 下间隙锁被显式关闭（只在外键检查和唯一键冲突检查时使用），且不匹配的行在 WHERE 判断后会立即释放锁，这是很多公司把隔离级别改成 RC 的原因：锁范围小、死锁少、并发高。

加锁的经验规则（RR 下，唯一索引 vs 普通索引）：

1. 唯一索引等值查询，命中：退化为记录锁；没命中：退化为间隙锁（锁住该值所在的间隙）。
2. 唯一索引范围查询：会对范围内的记录加 next-key，并且到范围右边界之后的第一条记录也会加锁（“多锁一条”）。
3. 普通（非唯一）索引等值查询命中：对命中记录加 next-key，并且向右扫描到第一个不满足条件的记录，对这个记录前的间隙加间隙锁（防止插入同值）。
4. where 条件没走索引：扫描到的每一行都加 next-key，效果接近锁全表。这是线上“一条 update 卡住整张表”的典型原因。

死锁怎么产生与排查：两个事务以相反顺序加锁；或者两个事务在同一个间隙上都拿了间隙锁，然后都想往里 insert（插入意向锁与对方间隙锁冲突）。排查用 SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK，或者开启 innodb_print_all_deadlocks 把所有死锁打进错误日志。InnoDB 默认开启死锁检测，发现后回滚代价小的事务；等待超时由 innodb_lock_wait_timeout 控制，默认 50 秒。

追问链：

1. 为什么很多公司选 RC 而不是 RR？——RC 没有间隙锁、死锁概率低、并发高；代价是需要 binlog 用 ROW 格式（8.0 默认就是 ROW），以及应用要接受不可重复读。
2. select ... for update 在没索引的列上会怎样？——锁全表（所有扫描到的行加 next-key）。
3. 什么是意向锁？——表级锁，声明“我要在表里的某些行上加 S/X 锁”，让表锁与行锁的冲突检查不必逐行扫描。
4. 乐观锁与悲观锁怎么选？——冲突少用版本号/CAS（update ... set version=version+1 where id=? and version=?），冲突多且事务短用 for update。

🌳 高级：事务治理与线上问题

标准回答要点：

• 长事务是万恶之源：占着 undo（磁盘膨胀）、占着锁、阻塞 DDL（元数据锁排队）、拖慢主从回放。治理三板斧：监控 information_schema.innodb_trx 里 trx_started 超过阈值（比如 60 秒）的事务并告警；限制应用侧事务边界（不要在事务里做 RPC、发 MQ、上传文件）；兜底设置 max_execution_time、连接池 maxLifetime 与 innodb_lock_wait_timeout 的合理值（可调短到 5–10 秒，快速失败好过雪崩）。
• 热点行更新（如库存、余额）：同一行的更新在 InnoDB 里天然串行，单行 TPS 上限大约几百到一两千。方案：Redis 预扣 + 异步落库；分段库存（把一行拆成 N 行，随机选一行扣）；合并写（批量累加）；在 SQL 里用 update stock set n=n-1 where id=? and n>=1 一条语句完成校验与扣减，避免“先查再改”的竞态。
• Spring 事务失效与大事务：同类内部调用绕过代理、非 public 方法、异常被吞、抛出的不是 RuntimeException 且没配 rollbackFor、多线程下事务不传播。大事务要拆：先做校验与远程调用，最后开一个短事务只做写库。
• 分布式事务的现实选择：绝大多数业务用本地事务 + 可靠消息（事务消息 / 本地消息表）+ 幂等 + 补偿实现最终一致；强一致场景才考虑 Seata AT/TCC，并明确它带来的性能损耗和全局锁风险。

追问链：“线上突然大量 Lock wait timeout exceeded，你怎么查？”——先看 sys.innodb_lock_waits（8.0 里也可用 performance_schema.data_lock_waits）找到持锁者；再用 information_schema.innodb_trx 与 processlist 找到那个“Sleep 状态但事务没提交”的连接，多半是应用拿着事务去调了慢接口；止血方式是 KILL 该连接，根治是缩小事务边界。

加分项：能说明 8.0 用 performance_schema.data_locks / data_lock_waits 取代 5.7 的 information_schema.INNODB_LOCKS；知道 SELECT ... FOR UPDATE SKIP LOCKED / NOWAIT（8.0 新增）可以用来实现“抢任务”而不互相阻塞。

四、日志体系：redo、undo、binlog 与两阶段提交

🌱 初级：三种日志各管什么

标准回答要点：

• redo log 是循环写的固定大小空间，checkpoint 推进后旧部分可被覆盖；binlog 是追加写，写满一个文件换下一个。
• 为什么要 WAL？数据页在 Buffer Pool 里改，如果每次提交都随机写数据页到磁盘，太慢；redo 是顺序写，提交只要保证 redo 落盘，脏页可以后台慢慢刷（崩溃了靠 redo 重放）。
• redo 刷盘策略由 innodb_flush_log_at_trx_commit 控制：1（默认，每次提交 fsync，最安全）、2（每次提交写到 OS 缓存，每秒 fsync，MySQL 挂了不丢，机器掉电会丢约 1 秒）、0（每秒写并刷，MySQL 崩溃也会丢约 1 秒）。binlog 对应 sync_binlog，1 表示每次提交都 fsync。“双 1”配置是金融级别的持久性配置。
• 8.0.30 开始，redo 容量用 innodb_redo_log_capacity 动态配置（官方文档：InnoDB 维护约 32 个 redo 文件，每个大小是容量的 1/32），此前要改 innodb_log_file_size 并重启。

🌿 中级：两阶段提交，为什么需要它

redo 属于 InnoDB，binlog 属于 Server 层，两个日志各写各的，如何保证它们一致？如果先写 redo 再写 binlog 时崩了：主库有数据（redo 恢复），binlog 没有，从库缺这条；反过来先写 binlog 再写 redo：从库有，主库没有。两个顺序都会导致主从不一致。答案是内部 XA：两阶段提交。

标准回答要点：提交时先写 redo（prepare）→ 写 binlog → redo 改 commit；崩溃恢复时，遇到处于 prepare 的事务，用其 XID 去 binlog 里找，binlog 完整就提交，否则回滚。因此 binlog 成为“事务是否提交”的最终裁判。

追问链：

1. 两阶段提交的代价是什么？——每次提交两次 fsync（redo prepare、binlog），高并发下成为瓶颈；解决办法是组提交（group commit）：多个事务的 redo/binlog 刷盘合并，分 flush、sync、commit 三个阶段队列，用一次 fsync 服务一批事务。相关参数 binlog_group_commit_sync_delay / binlog_group_commit_sync_no_delay_count 可以用一点延迟换更大批次。
2. 只要 binlog 不要 redo 行不行？——不行，binlog 是逻辑日志，没有页级别的崩溃安全能力（MyISAM 就是这样，所以没有 crash-safe）；只要 redo 也不行，因为 redo 循环写，不能做归档和主从复制。
3. binlog 三种格式：STATEMENT（记 SQL，遇到 now()、uuid() 等非确定函数会主从不一致）、ROW（记行变更，8.0 默认，最安全，体积大）、MIXED（混合）。
4. undo log 什么时候删？——insert 的 undo 提交后即可丢弃；update/delete 的 undo 要等没有任何事务的 ReadView 还需要它才由 purge 清理。
5. Buffer Pool、change buffer、doublewrite buffer 是什么？——Buffer Pool 缓存数据页（LRU 分冷热区，防止全表扫描把热数据挤出去）；change buffer 缓存对非唯一二级索引页的修改，页不在内存时先记下来后面合并，减少随机读；doublewrite 解决“部分页写”（16KB 页只写了一半掉电）导致的页损坏。

🌳 高级：日志与容灾的取舍

• 持久性与性能的取舍要能讲量化：在 SSD 上，“双 1”下单机 TPS 主要受 fsync 延迟约束；某些场景（离线导入、可重跑的数据）可以临时把 innodb_flush_log_at_trx_commit=2, sync_binlog=0 换吞吐，但必须限定范围与时间窗口并有回滚方案，线上核心库不动。
• PITR（按时间点恢复）流程：全量备份（物理备份如 XtraBackup、或云厂商快照）+ 从备份点开始的 binlog，恢复到误操作前的位置（mysqlbinlog --stop-datetime 或按 GTID 跳过误操作事务）。RTO/RPO 要和业务对齐：备份多久一次、binlog 保留多久（8.0 默认 binlog_expire_logs_seconds 为 2592000 秒即 30 天）。并且要定期演练——没演练过的备份等于没有备份。
• 误删数据怎么办：DML 误操作可用 binlog 闪回（ROW 格式配合 binlog2sql / 闪回工具）；DROP/TRUNCATE 只能靠备份 + binlog；预防措施是生产账号不给 DDL 权限、走审批平台、开启 sql_safe_updates 类保护。
• 大事务对 binlog 的伤害：一个事务一次产生几个 GB 的 binlog，从库必须等它完整传输与回放，造成分钟级延迟，还会撑爆 max_binlog_cache_size。批量操作要拆成每批几千行的小事务。

加分项：能讲清 GTID 复制的好处（故障切换不用找位点）、半同步复制的 AFTER_SYNC（无损）与 AFTER_COMMIT 的区别，以及 MGR（Group Replication）在强一致场景下的定位。

五、慢查询与 explain 优化

🌱 初级：怎么发现慢 SQL、explain 看什么

标准回答要点：先开慢查询日志：slow_query_log=ON，long_query_time=1（秒，可以设 0.5），用 mysqldumpslow 或 pt-query-digest 汇总；线上更常用的是云数据库或 APM 自带的慢 SQL 分析。定位到具体 SQL 后，用 EXPLAIN 看执行计划，重点看这几列：

🌿 中级：常见优化套路与 8.0 新工具

• filesort 怎么消除：让 ORDER BY 的列顺序、方向与联合索引一致。8.0 支持真正的降序索引，混合方向排序 order by a asc, b desc 可以建 (a asc, b desc)。
• group by 与 distinct：同样靠索引有序性避免临时表；8.0 起 group by 不再隐式排序。
• join 优化：小表驱动大表；被驱动表的关联列必须有索引（否则是 Block Nested Loop，8.0.18 起用 hash join 取代 BNL，但仍比走索引慢）；join 的字段类型与字符集要一致；join 表数别过多（经验上不超过 3 张，大 join 拆成多次查询在应用层组装）。
• count 的坑：count(*) 与 count(1) 一样快（优化器都选最小的二级索引扫描），count(col) 要判断 NULL；InnoDB 因为 MVCC 不能像 MyISAM 那样存精确行数，大表精确 count 很慢。可以用近似值（show table status 的 rows 或 explain 的 rows）、单独维护计数表或放到 Redis。
• IN 与 EXISTS：现代优化器会把很多子查询改写成半连接（semijoin），不要再死记“小表驱动”，看 explain 说话。
• EXPLAIN ANALYZE（8.0.18）：会真正执行语句并给出实际耗时、实际行数和循环次数，对比预估的 rows 能发现统计信息失真，是 8.0 排查慢 SQL 的利器（注意别对 DML 或超慢的语句随便跑）。
• 优化器 trace：SET optimizer_trace='enabled=on' 后查 information_schema.OPTIMIZER_TRACE，可以看到各候选索引的成本对比，回答“优化器为什么没选我建的索引”。

-- 反例：函数导致失效 + select *
SELECT * FROM orders WHERE DATE(create_time) = '2026-09-01';

-- 改写：范围条件 + 只取需要的列（配合索引 idx_ct_status(create_time, status)）
SELECT id, status, amount
FROM orders
WHERE create_time >= '2026-09-01' AND create_time < '2026-09-02';

-- 看真实执行情况（8.0.18+）
EXPLAIN ANALYZE
SELECT id, status FROM orders WHERE user_id = 1001 AND status = 2 ORDER BY create_time DESC LIMIT 20;

🌳 高级：慢查询治理体系

高级岗位不会只问“这条 SQL 怎么优化”，而是问“你们线上慢 SQL 怎么治理”。可以按下面的体系回答：

1. 发现：慢日志 + 全量 SQL 指纹（performance_schema.events_statements_summary_by_digest）统计 Top N（按总耗时、按平均耗时、按扫描行数三种排序，别只看平均）。
2. 预防：上线前 SQL 审核（如 SQLAdvisor、Archery 类平台）卡住没走索引、扫描行数超阈值的语句；新表设计评审（字段类型、索引、字符集统一为 utf8mb4）。
3. 止血：线上突发慢查询打满连接时，先 show processlist 找出头部慢查询 KILL；max_execution_time（只对 SELECT 生效）设保护值；应用侧限流与熔断；必要时切只读从库。
4. 根治：索引优化、SQL 改写、冷热数据分离、结果缓存、走 ES 承接复杂检索。
5. 度量：治理前后 P99、慢 SQL 条数、Buffer Pool 命中率（Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests，通常要求命中率 99% 以上）、连接数峰值。

面试官常追问：“CPU 高、IOPS 高、连接数满，三种现象分别怎么排？”——CPU 高多是缺索引导致大量扫描或排序、临时表；IOPS 高多是 Buffer Pool 太小或全表扫描冲刷缓存、大量随机回表；连接数满多是慢 SQL 堆积或事务未提交，要顺着 processlist 与 innodb_trx 找源头。

六、规模化：深分页、主从延迟、分库分表与大表 DDL

🌱 初级：深分页为什么慢，怎么改

select * from t order by id limit 1000000, 10 为什么慢？因为 MySQL 要扫描并丢弃前 100 万行，如果还是二级索引 + select *，这 100 万行每一行都回表，代价极高。

三种改法：

-- 1) 游标分页（推荐）：记住上一页最后一个 id，不允许跳页
SELECT * FROM t WHERE id > #{lastId} ORDER BY id LIMIT 10;

-- 2) 延迟关联：先用覆盖索引查出主键，再回表取整行
SELECT t.* FROM t
JOIN (SELECT id FROM t WHERE status = 1 ORDER BY create_time LIMIT 1000000, 10) x
ON t.id = x.id;

-- 3) 业务限制：最多翻 100 页（搜索引擎也是这样干的），超过引导用户加筛选条件

排序键不唯一时游标要带上主键做 tie-breaker：where (create_time, id) > (?, ?) order by create_time, id，否则同一时间的数据会漏或重。

🌿 中级：主从延迟与读写分离

标准回答要点：

• 复制流程：主库写 binlog → 从库 IO 线程拉取写入 relay log → SQL 线程（或多个 worker）回放。延迟可能出现在传输与回放两段，绝大多数是回放慢。
• 延迟的常见原因：主库大事务（一个事务在从库也要串行回放同样久）；从库配置低或同时跑报表等重查询；从库单线程回放（5.6 之前）或并行度不足；表没有主键导致 ROW 格式下每行更新都要全表扫描（这是一个经典大坑）；DDL 在从库同样要执行很久；网络。
• 治理方法：开启并行复制（8.0 的 replica_parallel_workers 配合 binlog_transaction_dependency_tracking=WRITESET，让互不冲突的事务在从库并行，即使它们在主库不是同一组提交）；拆大事务；从库配置不低于主库；每张表必须有主键；监控延迟（Seconds_Behind_Source 只是估算值，不够精确，生产上常用心跳表方案：主库定时写时间戳，从库读取对比）。
• 业务上如何应对延迟：写后立刻读的关键链路强制读主；或读从库前用 WAIT_FOR_EXECUTED_GTID_SET 等待从库追上某个 GTID；或写入后短时间内把该用户路由到主库（会话粘滞）；或用缓存承接“刚写完的数据”。

追问链：半同步复制能保证不丢数据吗？——AFTER_SYNC（无损半同步，5.7 起默认）在事务提交前等待至少一个从库确认收到 binlog，主库崩溃切换后不会丢已确认提交的事务；但如果超时（rpl_semi_sync_master_timeout）会退化成异步。要更强的一致性看 MGR / 云厂商的多副本共识方案。

🌳 高级：分库分表怎么决策、怎么落地

先亮明立场：分库分表是最后一招，不是第一招。标准的升级路径是：索引与 SQL 优化 → 冷热数据归档（历史订单迁到归档库）→ 读写分离 → 缓存 → 垂直拆分（按业务拆库）→ 最后才是水平拆分。因为拆分之后你要为它付出永久的复杂度税。

中间件：客户端型（ShardingSphere-JDBC，无额外一跳，语言绑定）和代理型（ShardingSphere-Proxy、MyCat 等，语言无关，多一跳）。也可以直接评估分布式数据库（TiDB、OceanBase 等）来换取透明扩展，代价是运维体系、MySQL 兼容性边界与成本，不要为了“不用分库分表”而盲目迁移。

平滑迁移的标准剧本（能讲清就是高分）：①新库建好分片表；②全量迁移 + 增量同步（binlog 订阅，如 Canal / DTS）；③校验（按行数、checksum、抽样比对）；④双写阶段，读仍走旧库；⑤灰度切读（按用户或比例），观察；⑥切写，旧库变为只读兜底；⑦保留回滚窗口（反向同步一段时间）后下线。每一步都要有回滚开关。

🌳 高级：大表在线加字段 / 改表结构

标准回答要点（这是很爱考的场景题：“一亿行的表要加一列，怎么不影响线上”）：

• 先看版本。MySQL 8.0.12 起 ADD COLUMN 默认使用 ALGORITHM=INSTANT：只修改数据字典中的元数据，不重建表、不拷贝数据，几乎瞬时完成，且允许并发 DML（官方文档）。8.0.12 只支持加在表末尾，8.0.29 起支持任意位置。每次 INSTANT 操作会产生新的行版本，官方文档限定最多 64 个版本，之后要重建表（OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB）才会归零。
• INSTANT 的限制：不支持 ROW_FORMAT=COMPRESSED、有全文索引的表、临时表；不能与不支持 INSTANT 的其它 ALTER 操作合并在一条语句里；加 AUTO_INCREMENT 列不行（需要 INPLACE + 至少共享锁，期间禁止并发 DML）。写法上建议显式声明：ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;——不满足条件时直接报错而不是悄悄退化成重建表，这一点非常重要。
• 5.7 及更早：加列是 INPLACE 但仍要重建表（rebuild），期间允许 DML，但大表耗时长、IO 大、从库延迟；此时用 pt-online-schema-change（触发器同步增量）或 gh-ost（伪装成从库订阅 binlog，不用触发器，可暂停、可限速）。
• 真正的风险常常不是 DDL 本身，而是元数据锁（MDL）：DDL 开始和结束时需要拿表的 MDL 排他锁，如果此时有一个长事务/长查询持有该表的 MDL 共享锁，DDL 会等待；而DDL 一旦在排队，后续所有对该表的读写都会排在它后面阻塞，瞬间把连接池打满。这是线上大事故的经典路径。防护措施：执行前检查该表是否有长事务（information_schema.innodb_trx、performance_schema.metadata_locks）；DDL 会话先设 SET lock_wait_timeout=5（等不到就放弃，宁可重试也不要排队堵全表）；在业务低峰执行；有重试机制。
• 发布与兼容策略：加列要“先加库后发代码”，新字段允许 NULL 或有默认值，保证新旧代码都能跑；删列反过来，“先发代码（不再读写）再删列”，并预留观察期；改列类型/长度这类会重建表的操作，优先考虑新增列 + 双写 + 回填 + 切换的方案。

-- 8.0：显式要求 INSTANT，不满足就报错，不会悄悄重建整张表
SET SESSION lock_wait_timeout = 5;   -- 拿不到 MDL 就快速失败
ALTER TABLE orders
  ADD COLUMN remark VARCHAR(255) NULL COMMENT '备注',
  ALGORITHM = INSTANT;

-- 事前检查：有没有长事务会堵住 DDL
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec, trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started LIMIT 10;

追问链：“INSTANT 之后旧行是怎么读到新列的？”——旧行没有被改写，读取时按行版本号补上列的默认值；所以它只适合“加列 + 默认值”这类可用元数据表达的变化。“gh-ost 与 pt-osc 怎么选？”——gh-ost 无触发器、对主库写入影响小、可随时暂停/限速/回退，缺点是要求 ROW 格式 binlog、对有外键/触发器的表限制较多；pt-osc 成熟，但触发器在高写入表上会放大锁竞争。

七、高频追问速查表

一句话备忘：索引看“扇出与有序”，联合索引看“最左与下推”，事务看“ReadView 与 next-key”，日志看“redo 恢复、undo 回滚、binlog 复制、两阶段提交对齐”，规模化看“先优化后拆分、DDL 先防 MDL”。

八、答题的几个通用心法

• 先结论再展开：“为什么快”先答“减少 IO 次数”，再讲扇出；面试官会顺着你的第一句往下追，第一句要给自己挖对的坑。
• 每个机制都带代价：两阶段提交 → 两次 fsync → 组提交；间隙锁 → 防幻读 → 死锁与并发下降；分库分表 → 扩展 → 跨库 join 与事务。会说代价，才像做过。
• 版本敏感：INSTANT DDL 是 8.0.12、任意位置是 8.0.29、EXPLAIN ANALYZE 是 8.0.18、redo 容量动态调整是 8.0.30，说错版本会被追问，不确定就说“8.0 之后”。
• 线上问题先止血再定位：KILL 长事务、限流、切只读、摘流量，然后才是 explain 与改索引。

写完这篇我最大的感触是：MySQL 的八股其实是一张因果网，索引是为了少 IO，MVCC 是为了读写不互相阻塞，日志是为了崩溃不丢，分库分表是为了单机装不下。把每一个“是什么”都追问到“不这样会怎样”，这一章就稳了。剩下的，就是别在生产库上手抖敲 drop。

参考资料

• MySQL 8.0 官方文档：InnoDB Locking：记录锁、间隙锁、next-key 锁的定义，以及 RC 下关闭间隙锁的说明。
• MySQL 8.0 官方文档：InnoDB Multi-Versioning：隐藏列 DB_TRX_ID / DB_ROLL_PTR、undo log 与 purge、二级索引的 MVCC 行为。
• MySQL 8.0 官方文档：Redo Log：redo 的作用与 8.0.30 起的 innodb_redo_log_capacity。
• MySQL 8.0 官方文档：Online DDL Operations：ADD COLUMN 默认 INSTANT（8.0.12 起）、限制与行版本上限。
• JavaGuide：MySQL 专题：公开的八股知识体系，涵盖索引、事务与 MVCC、日志、执行计划与慢 SQL 排查的考点结构。