这是一篇 MySQL 面试八股总结：按模块归类，便于快速复习与串讲。每节都附带”真实面试问答”，A 是可以直接背的口语稿，W 把背后的为什么讲透——建议先看知识点，再用问答自测。

一、索引：B+ 树与回表（高频中的高频）

知识点

  • B+ 树结构：非叶子节点只存键和指针，数据全在叶子层；一页 16KB，非叶子节点约放 1170 个指针（键 8B + 指针 6B），2000 万行约 3 层——磁盘 IO 次数等于树高。
  • 对比 B 树：B 树节点带数据，扇出小、树更高；B+ 树叶子间有双向链表，范围查询顺链表扫，B 树要在树上上下横跳。
  • 对比哈希索引：等值查询 O(1)，但不支持范围查询和排序，只适合纯键值场景（InnoDB 的自适应哈希是内部自动优化，不是可选索引）。
  • 聚簇索引：主键索引的叶子直接放整行数据，表本身就是按主键组织的 B+ 树。
  • 二级索引与回表：二级索引叶子只存索引列 + 主键值，查到主键后还要回聚簇索引取整行，称为回表。
  • 主键选型：自增 bigint 追加写入、页写满自然分裂；UUID 随机插入导致频繁页分裂、空间利用率低。

面试问答

  • Q1：为什么用 B+ 树，不用 B 树或哈希？

A： 最主要的原因是它矮：非叶子节点不存数据，一页能放一千多个指针，两千万行的表也就三层，而磁盘 IO 次数就等于树高。其次所有数据都在叶子层并且用链表串起来，范围查询顺着链表扫就行。哈希索引等值查询虽然快，但做不了范围查询和排序，不适合当通用索引。

W： 为什么矮这么重要：InnoDB 按页读磁盘，一次 IO 读 16KB，走一层就是一次 IO；B 树每个节点都装数据，同样的数据要多出一两层，每次查询多一两次磁盘 IO。范围查询差距更大：B+ 树定位起点后顺着叶子链表顺序读，正好配合磁盘预读，B 树只能在树上反复横跳。

  • Q2：什么是回表？怎么避免？（追问率极高）

A： 回表就是二级索引查到主键后，还要拿主键去聚簇索引再查一次整行。避免的办法是覆盖索引：把查询要的列都放进索引里，比如只查 id 和 name，name 索引里就凑齐了，不用再回去查，执行计划里会显示 Using index。这也是平时不写 select 星号的原因。

W： 为什么二级索引不干脆把整行数据放进去：一份数据存多份，空间翻倍，每次更新所有索引都要跟着改。只存主键的代价就是回表。如果一个条件能命中一万行，一万次回表比顺序扫全表还贵，优化器算完账可能直接放弃索引——所以回表不只是慢，还可能连累索引整个不被使用。

二、联合索引与索引失效（爱考）

知识点

  • 联合索引的排序规则：(a,b,c) 先按 a 排，a 相同按 b，再按 c；索引对 a 全局有序、对 b 局部有序。
  • 最左前缀原则：条件必须从 a 开始才能定位；where b=1 跳过了 a，b 在整个索引里是无序的，用不上。
  • 范围条件的影响：a 走范围（a>1）时 b 不能定位；a 用 in 时会拆成多段等值，b 仍能定位。
  • 索引下推（ICP，5.6+）：把 b 条件的过滤从服务层下放到引擎层，在索引里先过滤，减少回表。
  • 覆盖索引：查询列都在索引里，免回表（Extra 显示 Using index）。
  • 索引失效五场景：列做运算或套函数；隐式类型转换（varchar 列传数字）；左模糊 like ‘%x’；or 两侧混入非索引列；不满足最左前缀。
  • 失效的统一根因：索引的有序性被破坏，或优化器估算回表成本高于全表扫描。

面试问答

  • Q1：phone 是 varchar，where phone=13800000000 走索引吗？

A： 走不了。MySQL 会把字符串列转成数字再比，等于给 phone 这一列套了个转换函数，列被加工过，索引就用不上了，最后全表扫描。反过来，int 列传字符串是常量被转换，列本身没变，索引还能走。所以字符串列的值一定要带引号传。

W： 为什么给列套函数索引就废了：索引是按原始值排好序的，加工后的值未必还保序——就像电话簿按姓排序，你拿名的拼音去找，排序帮不上忙。B+ 树定位依赖有序，有序性对加工后的值不成立，只能全扫。所有失效场景都能用这条根因套出来，不用死记清单。

  • Q2：联合索引 (a,b,c)，哪些查询能用上？（大厂高频）

A： 记住排序规则就行：索引先按 a 排、再按 b、再按 c。所以 a 单独、a 加 b、a 加 b 加 c 都能走；b 或 b 加 c 跳过了 a，用不了。a 用 in 的时候 b 还能接着用；a 用大于号走范围时，b 不能定位，但能靠索引下推先过滤掉一部分，少回表。

W： 为什么 a 走范围 b 就不能定位：a>1 命中的是索引里一大段区间，这段区间里 b 只是分段有序——每个 a 值内部 b 才有序，跨 a 值没有全局顺序，没法二分跳到 b=2 的位置，只能挨个看。所以 b 从”定位”降级成”过滤”，这就是索引下推干的事。

三、事务与 MVCC（大厂高频）

知识点

  • 并发三问题：脏读（读到未提交数据）、不可重复读（两次读同一行，值不同）、幻读（两次读，行数不同）。
  • 四级隔离：读未提交 → 读提交 → 可重复读 → 串行化；逐级消除上面三个问题；InnoDB 默认可重复读（RR）。
  • MVCC（多版本并发控制）：读旧版本、不加锁的机制；三件套——隐藏列、版本链、读视图。
  • 版本链：每行记录最后修改它的事务号；旧版本存于回滚日志（undo log），逐版回溯，链头最新。
  • 读视图（ReadView）：查询时拍快照，记录此刻活跃（未提交）的事务集合；沿版本链回溯，取第一个已提交且可见的版本。
  • 读提交与可重复读的唯一区别：读视图生成时机——读提交每条语句生成一次，可重复读整个事务只生成一次。
  • 当前读：更新、删除、加锁查询（for update）必须读最新版并加锁；行锁 + 间隙锁（next-key lock）锁住区间防插入，防幻读。
  • 防幻读的两条路：快照读靠多版本，当前读靠锁。

面试问答

  • Q1：MySQL 默认用哪个隔离级别？为什么不是读提交？

A： 我们默认用的是可重复读。主要是历史原因：早期 binlog 记的是 SQL 原文，在读提交级别下主从复制会乱序出错，可重复读更稳；另外它配合锁基本解决了幻读。现在 binlog 默认改记行数据了，这个理由没那么强，所以不少公司会主动改成读提交，锁冲突更少。

W： 为什么记 SQL 原文就会乱序？想象两个事务先后改同一行：读提交下，谁的语句先提交谁先可见，binlog 里两笔记录的先后，可能和主库真正加锁修改的先后对不上，从库照着重放就算出错的结果。可重复读把整个事务的修改攒到提交时一次性写进 binlog，重放天然同序。所以这是复制安全的取舍，不是这个级别本身更高级。

  • Q2：MVCC 的实现原理说一下？（追问率极高）

A： 核心是三样东西：每行数据记着最后修改它的事务号；旧版本存放在回滚日志里，一版连一版串成链；读的时候生成一张读视图，记下此刻哪些事务还没提交。查询就沿版本链往回走，遇到第一个已经提交、对我可见的版本就返回。读的是旧版本，所以完全不用加锁。

W： 为什么这样就能不加锁？关键在那张读视图：它相当于拍照记录”我开始的这一刻，哪些事务还开着没提交”。版本链上某个版本的事务号还在照片里，说明它当时没提交完，对我不可见，顺着指针继续找更旧的；事务号比照片里所有人都老，说明早就提交了，放心读。读的是过去某个时刻的一致状态，自然不用等别人，也不用锁。

  • Q3：可重复读级别下还会有幻读吗？（爱考）

A： 基本没有了，但没堵死。普通查询读旧版本快照，新插入的行看不见，天然没幻读；加锁查询和增删改走当前读，会把查过的区间锁住，别人插不进来。但有个边界：同一个事务里先用普通查询、再对同一段数据做当前读，就可能突然看到新行。

W： 为什么有这条缝？快照读和当前读是两条时间线：普通查询永远看事务开始时拍的旧照片，当前读永远看最新画面。先看照片再看实时，中间别人插进来的行自然就冒出来了。真要彻底避免，事务一开始就直接用加锁查询，把这段数据的当前版本锁住。

四、锁：行锁、间隙锁与死锁（手撕级别高频）

知识点

  • 行锁三形态：记录锁（锁一行）、间隙锁（锁一段开区间，只防插入、间隙锁之间互不冲突）、临键锁（记录 + 间隙，左开右闭）。
  • 加锁规则：可重复读级别下当前读默认加临键锁；唯一索引等值命中退化为纯记录锁；普通索引等值会多锁住下一个间隙。
  • 间隙锁只在可重复读：读提交级别没有间隙锁，锁冲突和死锁都少一截。
  • 锁加在索引上：条件列没索引会全表扫描，把所有行都锁住，近似锁表。
  • 插入意向锁：insert 撞上别人的间隙锁时挂起等待；两个事务各持一个间隙锁（互不冲突）又都想插进对方的间隙，是 insert 死锁的经典剧本。
  • 死锁处理：innodb_deadlock_detect 默认开启，发现环就回滚代价小的事务；关闭则靠锁等待超时（innodb_lock_wait_timeout，默认 50 秒）兜底。

面试问答

  • Q1：死锁怎么排查？怎么避免？（追问率极高）

A： 排查用 show engine innodb status，看 LATEST DETECTED DEADLOCK 那一段，里面有双方正在执行的 SQL、各自持有和等待的锁。避免就死磕三件事：批量更新先按主键排序，保证所有事务按同样顺序拿锁；把 RPC 这类耗时操作挪出事务，缩短持锁时间；唯一键插入冲突改用 on duplicate key update，减少锁碰撞。

W： 为什么按同一顺序拿锁就不死锁：死锁的必要条件是”环”——A 等 B、B 又等 A。所有事务都按主键从小到大拿锁，后来的只等前面的，等待关系是一条直线，成不了环。反过来，两个事务一个从头拿一个从尾拿，第一对锁就撞上，环就闭了。

  • Q2：什么时候会加间隙锁？

A： 可重复读级别下，加锁查询或者更新语句命中一段范围时，会把命中的索引区间连带头尾锁住，别人往这段里插数据会被挡住，目的是防幻读。读提交级别没有间隙锁。另外唯一索引等值命中时不需要防重复插入，会退化成只锁那一条记录。

W： 为什么防幻读要锁区间而不是锁行：幻读说的是”两次读行数变了”，多出来的行是别人新插入的——还不存在的行没得锁，只能锁住”可以插入的位置”，也就是两个已有值之间的间隙。间隙锁锁的是位置不是数据，这就是它只防插入、不防查询的原因。

五、三本日志与两阶段提交（大厂高频）

知识点

  • redo log（重做日志）：InnoDB 层物理日志，记”某页某偏移改成了什么”；先写日志再择机刷数据页（WAL），用顺序写替代随机写；文件固定大小、循环写，写满一圈会强制刷脏，表现为整库抖动。
  • undo log（回滚日志）：逻辑日志，记反向操作（insert 的 undo 是 delete）；用于回滚和 MVCC 版本链，由 purge 线程回收，长事务会拖住回收导致膨胀。
  • binlog（归档日志）：Server 层逻辑日志，所有引擎共用；三种格式——statement 记 SQL 原文、row 记行变化（8.0 默认）、mixed 自动切换；追加写不覆盖，用于主从复制和时间点恢复。
  • 两阶段提交：redo 写入并置 prepare → 写 binlog → redo 置 commit；崩溃恢复时以 binlog 是否完整裁决提交或回滚。
  • 双 1 配置：innodb_flush_log_at_trx_commit=1 加 sync_binlog=1，两本日志每次提交都落盘，宕机不丢；调 2 或 0 换性能，代价是宕机丢最后约 1 秒。

面试问答

  • Q1：redo log 和 binlog 有什么区别？

A： 三个维度：层次上，redo 是 InnoDB 引擎的，binlog 是 Server 层的，所有引擎都有；内容上，redo 是物理格式，记哪个页改了什么，binlog 是逻辑格式，记语句或者行变化；写法上，redo 固定大小循环写，binlog 追加写用来归档。一个管崩溃恢复，一个管复制和备份。

W： 为什么要有两本：redo 是 InnoDB 为了崩溃恢复自己造的，物理格式恢复快；binlog 是 MySQL 服务层给复制和备份用的，得让所有引擎都能接。各管一摊，就冒出”两本不一致怎么办”的问题——这正是两阶段提交存在的意义。

  • Q2：两阶段提交解决什么问题？不要它会怎样？（爱考）

A： 它保证 redo 和 binlog 要么都成功、要么都失败。顺序是 redo 写到准备态，然后写 binlog，最后提交。反过来先写完 redo 就崩溃，主库认定事务成功，但 binlog 没有这笔，从库永远丢这条数据；先写 binlog 就崩溃，主库回滚了，从库却重放了，两边对不上。两阶段就是拿 binlog 做裁决。

W： 恢复时怎么裁决：崩溃后扫描处于准备态的 redo，去看对应事务的 binlog 完不完整——完整就提交，不完整就回滚。因为 binlog 完整意味着它已经可以被复制到从库，主库必须认这笔账；binlog 还没写成就当没发生过。两边的判断标准统一到同一份证据上，就不会分裂。

六、SQL 优化与慢查询（生产必问）

知识点

  • explain 四列：type（访问级别）、key（实际使用的索引）、rows（预估行数）、Extra（附加信号）。
  • type 档位：const > eq_ref > ref > range > index > ALL；index 是扫整棵索引树，大表上和全表扫描差别不大；生产底线 range。
  • key_len 反推：联合索引用了几列——(a,b) 都是 bigint 时 key_len=8 只用了 a，16 用到 b。
  • 两个坏信号：Using filesort（排序没走索引）、Using temporary（用了临时表）；Using index 反而好，是覆盖索引。
  • 慢查询日志：long_query_time 默认 10 秒，生产一般调 0.1~1 秒再开启。
  • 统计信息过期：rows 预估和实际差太远时跑 analyze table；explain analyze（8.0.18+）真实执行并显示每步实际耗时。

面试问答

  • Q1：explain 你主要看什么？

A： 先看 type，至少要到 range，出现 ALL 就是全表扫描要处理；再看 key 和 key_len，确认索引用上了、联合索引用到第几列；然后看 rows 预估扫多少行；最后看 Extra，出现 Using filesort 说明排序没走索引，Using temporary 说明用了临时表，都得回头改索引或者改写 SQL。

W： 为什么 key_len 能看出用到几列：联合索引是按列顺序排的，key_len 就是参与定位的列的字节数，bigint 占 8 字节，(a,b) 的 key_len 是 8，说明只有 a 参与定位，b 只是陪着被扫——索引设计还有压缩空间，这就是优化线索。

  • Q2：深分页 limit 1000000,10 怎么优化？

A： 两个方案。首选游标法：记住上一页最后一条的 id，下一页直接 where id 大于这个值，再按 id 排序取 10 条，成本恒定，代价是没法跳页。必须跳页就用延迟关联：先在覆盖索引里把目标那 10 个 id 找出来，再拿这 10 个 id 回表取整行，把一百万次回表压成十次。

W： 慢的根源在 limit 的语义：它要把前一百万零一十行都取出来、逐行回表，然后丢掉前一百万行，越往后翻越贵。游标法快是因为直接从上一页的 id 定位起点，顺链表读 10 行就结束；延迟关联快是因为子查询全程在索引上滑、不回表，把最贵的回表留到最后只做 10 次。

七、InnoDB 与 buffer pool（内存管理）

知识点

  • InnoDB 对比 MyISAM：InnoDB 有事务、行锁、崩溃恢复、MVCC 四件套；MyISAM 只有表锁、无事务；5.5 起默认 InnoDB，8.0 系统表也全部迁入。
  • buffer pool：数据页的内存缓存（默认 128MB，生产常调到物理内存的 50%~70%），读写都先走它。
  • LRU 分代：链表劈成 young（约 5/8）和 old（约 3/8）两区；新页先进 old 区，呆满 innodb_old_blocks_time（默认 1 秒）后再次被访问才晋升 young 区。
  • 冷页污染：一次全表扫描或备份把几万个只用一次的页灌进内存，挤走全部热点页，线上查询大量未命中、性能瞬时塌方；分代后扫描页 1 秒内用完即弃，进不了 young 区。
  • change buffer：非唯一二级索引的目标页不在内存时，先把变更记下来，等页被读入再合并，省一次随机读。
  • count(*) 差异：MyISAM 存了总行数 O(1)；InnoDB 因 MVCC 每个事务看到的行数不同，要真数（8.0.13+ 会挑最矮的索引树数）。

面试问答

  • Q1：为什么 InnoDB 的 LRU 要改造？原生 LRU 有什么问题？（大厂高频）

A： 原生 LRU 怕冷数据冲刷：一次全表扫描或者一次备份，把整表几万个页灌进内存，这些只用一次的页把真正的热点页全挤出去，后面线上查询大量缓存未命中，性能瞬间塌掉。InnoDB 把链表分成新区和老区，新读的页先进老区，只有过了一秒还有人来访问，才升级进新区，一次性的扫描页进不了新区，热点就保住了。

W： 为什么”过一秒还在被访问”就能区分冷热：扫描产生的页有个特征——在同一条 SQL 里被连续访问一次，之后再也没人碰；而热点页会被不同的查询反复访问。一秒就是给”还会不会被再次访问”划的判定线，代价是真正的热点页第一次晋升最多晚一秒，几乎无感。

八、主从复制（架构必问）

知识点

  • 复制链路：主库写 binlog → dump 线程推送 → 从库 IO 线程写入 relay log（中继日志）→ SQL 线程重放。
  • 异步复制：主库不等从库确认，天生可能延迟。
  • 延迟两大来源：大事务（SQL 线程按事务重放，大事务拆不开）和从库硬件差。
  • 并行复制：5.7 按组提交并行（slave_parallel_workers），8.0 的 writeset 模式按行冲突关系并行。
  • 半同步复制：主库等至少一个从库收到 binlog 才返回提交，解决丢数据（不解决延迟）。
  • 监控口径：seconds_behind_master 网络断时失真，更稳的是对比主从 binlog 位点或心跳表。

面试问答

  • Q1：主从延迟怎么排查、怎么解决？（追问率极高）

A： 先确认延迟来源：从库机器配置、磁盘 IO、有没有大事务。解决按顺序来：把一次性删几十万行的大事务拆小；开并行复制，8.0 的 writeset 模式按行冲突并行，重放能力大幅提升；实在要求写完立刻读的请求，强制路由到主库。监控别只看延迟秒数，网络一断它就失真，用主从位点对比更准。

W： 为什么大事务最致命：从库 SQL 线程以事务为单位重放，主库一个跑十分钟的大事务，从库也要十分钟才能重放完、才能开始下一个，并行也拆不开它。拆成一千个小事务后，writeset 并行能把没有行冲突的事务同时重放，延迟立刻追回来。

九、分库分表（架构进阶）

知识点

  • 什么时候拆：经验信号单表 2000 万行、单库 2TB；真正依据是慢查询比例、磁盘 IO 水位、连接数这些实测指标。
  • 拆前先试三招：读写分离、冷热归档、升级硬件；没到瓶颈别拆，拆出去的复杂度收不回来。
  • 垂直拆：分库按业务域（订单库、用户库）；分表把大字段拆出去让主表变瘦。
  • 水平拆：按分片键 hash（数据均匀但扩容要迁数据）或 range（好扩容但新数据热点集中）。
  • 分片键选型：挑查询最高频的维度（订单表选 user_id），让多数查询落单库。
  • 拆后配套：
    • 分布式 ID：雪花算法、号段模式（自增主键无法全局唯一）
    • 跨库查询：字段冗余、宽表，复杂检索交给 ES 或数仓
    • 跨库事务：优先最终一致（本地消息表、事务消息），强一致 XA 少用
    • 扩容路径：按 2 的幂次规划分片，翻倍扩容只迁一半数据

面试问答

  • Q1：单表数据量到多少要分库分表？为什么是这个数？

A： 业内经验值是单表 2000 万行左右开始评估。但要说清楚它不是魔法数字，背后的逻辑是：数据量再涨，B+ 树要从三层变四层，每次查询固定多一次磁盘 IO，加上磁盘空间和连接数的压力，性能曲线开始明显变弯。所以 2000 万是开始评估的信号，真正动手要看慢查询比例和 IO 水位这些实测指标。

W： 为什么树变高影响这么大：三层 B+ 树的容量是 16KB 页乘以每页指针数算出来的，两千万行已经贴近这个物理结构的天花板；上到第四层，所有查询都要多走一层。另一个同样重要的原因是这个量级之后优化器统计信息和内存缓存的效率都在下降——经验值是多个信号叠加出来的，不是一条硬线。

十、场景：秒杀扣库存（手撕级别高频）

知识点

  • 超卖根源：查库存、判断、扣减三步分开执行，并发下两个请求同时通过”还有库存”的判断，各扣一次。
  • 条件更新：update set stock=stock-1 where id=? and stock>0，判断和扣减合成一条原子语句。
  • 受影响行数：affected rows 为 0 即售罄，业务层直接返回失败。
  • 单行吞吐上限：热点行更新约几百到一两千 TPS（行锁排队 + 死锁检测开销）。
  • 库存分桶：1000 件拆 10 个桶，请求打散到不同桶，热点一行变十行。
  • 配套机制：
    • Redis 预扣：脚本原子判断加扣减，成功后异步落库
    • 对账兜底：Redis 与数据库定时核对
    • 库存回补：下单超时未付款，延时队列自动取消并回补

面试问答

  • Q1：秒杀扣库存怎么防止超卖？

A： 核心是把”查、判、扣”压成一条原子语句：更新库存表 set stock 等于 stock 减一，条件是 id 等于商品并且 stock 大于零。行锁保证同一行串行执行，条件保证不卖穿，受影响行数为零就代表售罄。流量再高就把扣减挪到 Redis 里用脚本原子完成，数据库只做最后的记账，再配上对账和超时回补兜底。

W： 为什么一条 SQL 就够：超卖的本质是两个请求同时读到”库存为 1”，然后都判断可买。条件更新把判断放进更新语句里，行锁又保证同一行的更新排队执行——第二个请求轮到它时库存已经是 0，条件不满足直接失败。判断永远跑不到更新的前面，竞态就消失了。