Appearance
Day16 MySQL：索引、事务、锁、MVCC、SQL 优化
📖 基础知识讲解【基础原理 + 高级深挖知识点】
基础原理
1. 全景：一条 SQL 从进程到磁盘经过了什么
Day15 讲的是应用层怎么开启和提交事务，Day16 要往下钻一层：事务到了 MySQL 里面，是靠什么机制真正落地的。先把整条链路画出来，后面所有知识点都能挂到这张图上。
text
客户端
│ TCP / Unix Socket
▼
┌───────────────────── Server 层（所有引擎共用） ─────────────────────┐
│ ① 连接器 Connector 权限校验、维持长连接、wait_timeout（默认 8h） │
│ ② 查询缓存 Cache 8.0 已彻底删除（命中率低 + 表一改全失效） │
│ ③ 解析器 Parser 词法/语法分析 → 语法树，报 You have an error.. │
│ ④ 预处理器 检查表/列是否存在、展开 select * │
│ ⑤ 优化器 Optimizer ★ 选索引、定 join 顺序、改写子查询（基于成本） │
│ ⑥ 执行器 Executor 权限再校验、逐行调用引擎接口、累计 rows │
└──────────────────────────────┬────────────────────────────────────┘
│ Handler API
┌──────────────────────────────▼────────────────────────────────────┐
│ 存储引擎层 InnoDB │
│ 内存：Buffer Pool（数据页/索引页）、Change Buffer、Log Buffer │
│ 磁盘：.ibd 表空间（段 → 区 → 页 16KB → 行）、redo log、undo（在共享表空间/独立 undo 表空间）│
└────────────────────────────────────────────────────────────────────┘
│
binlog（Server 层，所有引擎共用）四个必须记住的结论：
| 结论 | 为什么重要 |
|---|---|
| 索引和事务都在引擎层实现，Server 层不管 | 所以才有 "MyISAM 不支持事务"，同一条 SQL 换引擎行为就变了 |
| binlog 在 Server 层，redo log 在 InnoDB 层 | 这是两阶段提交存在的根本原因（见基础原理 13） |
| 优化器是基于成本的，不是基于规则的 | 所以 "建了索引却走全表" 不一定是 bug，可能是优化器算出全表更便宜 |
| InnoDB 读写的最小单位是页（16KB），不是行 | 解释了页分裂、随机 IO、Buffer Pool 污染、limit 深分页为什么慢 |
2. 存储引擎：InnoDB vs MyISAM
| 维度 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ 支持，完整 ACID | ❌ 不支持 |
| 锁粒度 | 行锁（走索引时）+ 表锁 | 只有表锁 |
| 外键 | ✅ 支持 | ❌ 不支持 |
| MVCC | ✅ 支持 | ❌ 不支持 |
| 索引结构 | 聚簇索引：主键 B+ 树叶子节点存整行 | 非聚簇索引：叶子节点存的是数据文件行地址/行号，索引和数据分离（.MYI / .MYD） |
| 崩溃恢复 | ✅ redo log 保证 crash-safe | ❌ 只能 REPAIR TABLE，且可能丢数据 |
count(*) | 需要实时统计（8.0 走最小的二级索引） | 维护了行数计数器，O(1) 返回 |
| 适用场景 | 一切线上业务 | 已淘汰（8.0 起系统表也全部换成了 InnoDB） |
⚠️ 草稿里的一个常见口误：MyISAM 不是 "非聚簇索引所以慢"，它的主键索引和二级索引结构完全一样（都存行地址），所以 MyISAM 没有回表概念，二级索引查询反而只需一次定位。它被淘汰的真正原因是没有事务、只有表锁、崩溃不可恢复这三条。
面试加分表述：「选 InnoDB 不只是因为支持事务，更重要的是行锁和 crash-safe。MyISAM 在写多的场景下表锁会把并发直接压到 1，而且一次断电就可能需要人工 repair，这在线上是不可接受的。唯一还有人提 MyISAM 的场景是纯只读的统计表想要 O(1) 的
count(*)，但现在都用单独的计数表或 Redis 来解决了。」
3. InnoDB 的内存与磁盘结构
内存（以 Buffer Pool 为核心）
| 组件 | 作用 | 关键参数 |
|---|---|---|
| Buffer Pool | 缓存数据页和索引页，读写都先过它；命中率是 MySQL 性能的生命线 | innodb_buffer_pool_size（建议物理内存的 50%~70%） |
| Change Buffer | 对非唯一二级索引的写操作，若目标页不在内存，先记在 Change Buffer，避免随机读磁盘；后续合并（merge） | innodb_change_buffer_max_size |
| Log Buffer | redo log 的内存缓冲 | innodb_log_buffer_size |
| Adaptive Hash Index | 对热点页自动建哈希索引，把 B+ 树的多次比较降为一次哈希 | innodb_adaptive_hash_index |
磁盘（段 → 区 → 页 → 行）
- 页（Page）16KB：IO 的最小单位。一个页内部是
File Header + Page Header + Infimum/Supremum + User Records + Free Space + Page Directory + File Trailer，行记录之间用单向链表连接，页与页之间用双向链表连接。 - 区（Extent）1MB = 64 页：为了让 B+ 树叶子节点在物理上连续，范围查询可以顺序 IO。
- 段（Segment）：叶子节点段、非叶子节点段、回滚段等。
text
B+ 树（三层，主键索引示意）
┌──────── 根页（非叶子，只存 key + 页号）────────┐
│ [1 | p2] [500 | p3] [1000 | p4] │
└───────┬────────────┬─────────────┬───────────┘
┌──────────────┘ │ └──────────────┐
┌─────▼─────┐ ┌──────▼─────┐ ┌──────▼─────┐
│ 中间页 p2 │ │ 中间页 p3 │ │ 中间页 p4 │
└─────┬─────┘ └──────┬─────┘ └──────┬─────┘
┌─────▼──────┐ 双向链表 ┌──────▼─────┐ 双向链表 ┌──────▼──────┐
│ 叶子页：整行 │◄─────────►│ 叶子页：整行 │◄─────────►│ 叶子页：整行 │
└────────────┘ └────────────┘ └─────────────┘为什么说三层 B+ 树能存两千万行？（高频追问，要能当场算）
- 非叶子节点一行 = key（bigint 8B）+ 页号（6B）≈ 14B，一页 16KB ≈ 16384B，可存
16384 / 14 ≈ 1170个指针。 - 叶子节点存整行，假设一行 1KB，一页可存 16 行。
- 三层容量 =
1170 × 1170 × 16 ≈ 2190 万行。 - 结论：两千万行的表，按主键查只需 3 次 IO，而且根节点和中间节点常驻 Buffer Pool，实际磁盘 IO 往往只有 1 次。
4. 为什么是 B+ 树，不是别的结构
| 结构 | 为什么不行 |
|---|---|
| 二叉搜索树 / AVL / 红黑树 | 一个节点只存一个 key，两千万行树高约 24 层 → 24 次磁盘 IO，而磁盘随机 IO 约 10ms 量级 |
| B 树 | 非叶子节点也存数据 → 同样大小的页能存的 key 变少 → 树变高；而且叶子之间没有链表，范围查询要反复回溯中间节点 |
| Hash 索引 | O(1) 等值查询确实最快，但不支持范围查询、不支持排序、不支持最左前缀，且哈希冲突时退化成链表。InnoDB 只在自适应哈希索引里内部使用 |
| 跳表 | Redis/LevelDB 在用，内存友好但对磁盘不友好（节点分散，无法按页批量读） |
B+ 树的三个决定性优势：① 非叶子节点只存 key，扇出极大、树极矮；② 数据全在叶子，每次查询的 IO 次数稳定（这对 SLA 很关键）；③ 叶子双向链表有序，范围查询、order by、分页全部变成顺序 IO。
5. 聚簇索引、二级索引、回表、覆盖索引
| 概念 | 定义 | 关键点 |
|---|---|---|
| 聚簇索引（主键索引） | 叶子节点存整行数据 | 一张表有且只有一个。没显式建主键时，InnoDB 会找第一个 NOT NULL 的唯一索引；再没有就生成 6 字节隐藏的 row_id |
| 二级索引（辅助索引） | 叶子节点存 索引列 + 主键值 | 所以主键越大，所有二级索引越占空间 |
| 回表 | 二级索引拿到主键 → 再查一次聚簇索引取完整行 | 本质是一次额外的随机 IO，是慢查询的头号元凶 |
| 覆盖索引 | select 的列 + where 的列全部在二级索引里，无需回表 | Extra: Using index 就是它。慢查询优化的第一手段 |
text
-- 表：user(id PK, name, age, phone)，索引 idx_name(name)
-- SQL：select * from user where name = 'zhangsan';
idx_name 这棵 B+ 树 主键这棵 B+ 树
叶子：('zhangsan', id=100) ──回表──► 叶子：(100, 'zhangsan', 18, '138...')
↑ 一次额外的随机 IO
-- 改成 select id, name from user where name='zhangsan';
叶子：('zhangsan', id=100) ──► 直接返回，Extra: Using index（覆盖索引，不回表）面试加分表述：「回表的代价常被低估。二级索引扫出来的主键是按索引列有序、按主键乱序的，所以回表是纯随机 IO。这也是为什么
limit 100和limit 10000,100性能差几十倍——后者要回表 10100 次再扔掉前 10000 行。我们线上优化慢查询，第一步永远是看Extra有没有Using index，能做成覆盖索引就不碰其他方案。」
6. 索引类型全景
| 类型 | 语法 | 说明与坑 |
|---|---|---|
| 主键索引 | PRIMARY KEY | 唯一且非空，就是聚簇索引 |
| 唯一索引 | UNIQUE KEY | 允许多个 NULL（NULL 之间互不相等）；无法使用 Change Buffer（必须读页判重） |
| 普通索引 | KEY idx(col) | 可用 Change Buffer，写性能更好 |
| 联合索引 | KEY idx(a,b,c) | 遵循最左前缀，设计时顺序决定一切 |
| 前缀索引 | KEY idx(col(10)) | 省空间，但一定无法覆盖索引（因为存的是截断值），且无法用于 order by |
| 全文索引 | FULLTEXT | 中文需配 ngram 分词器；线上一般直接上 ES |
| 函数索引 | KEY idx((LENGTH(name))) | MySQL 8.0.13+，解决 "索引列不能用函数" 的祖传痛点 |
| 降序索引 | KEY idx(a DESC) | 8.0 才真正生效，5.7 只是语法允许但被忽略 |
| 不可见索引 | ALTER TABLE t ALTER INDEX idx INVISIBLE | 8.0 新增，删索引前先设为不可见观察一周，出问题立刻改回，零成本回滚 |
| 空间索引 | SPATIAL | 地理位置查询，用得少 |
7. 事务 ACID 与它们各自的实现机制
很多人只会背四个单词，面试官真正想听的是每个特性靠什么实现：
| 特性 | 含义 | 实现机制 |
|---|---|---|
| A 原子性 Atomicity | 要么全成功，要么全失败 | undo log：每条修改都记录反向操作，回滚时逐条执行 |
| C 一致性 Consistency | 数据始终满足业务约束 | 它是目的不是手段，由 A + I + D 加上应用层约束共同保证 |
| I 隔离性 Isolation | 并发事务互不干扰 | MVCC（快照读）+ 锁（当前读） |
| D 持久性 Durability | 提交后不丢 | redo log（WAL：先写日志再写数据页，顺序 IO 换随机 IO） |
面试加分表述：「我一般这样串：undo 管回滚（A），redo 管恢复（D），MVCC 加锁管并发（I），三者共同服务于 C。再补一句 WAL——redo log 是顺序追加写，数据页刷盘可以异步攒批，这才是 MySQL 写性能的关键；崩溃后用 redo 重放已提交但没刷盘的页，用 undo 回滚没提交的事务。」
8. 隔离级别与三类并发问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
READ UNCOMMITTED 读未提交 | ❌ | ❌ | ❌ | 能读到别人未提交的数据，基本不用 |
READ COMMITTED RC | ✅ 解决 | ❌ | ❌ | Oracle / PostgreSQL / SQL Server 默认；互联网公司常主动调成 RC |
REPEATABLE READ RR | ✅ | ✅ 解决 | ⚠️ 基本解决 | MySQL InnoDB 默认；快照读靠 MVCC，当前读靠 Next-Key Lock |
SERIALIZABLE 串行化 | ✅ | ✅ | ✅ | 所有读都加共享锁，并发退化为串行 |
三类问题的区分（面试常被追着问 "不可重复读和幻读有什么区别"）：
| 问题 | 焦点 | 例子 |
|---|---|---|
| 脏读 | 读到未提交的数据 | A 改了没提交，B 读到了，A 回滚 → B 读到了不存在的数据 |
| 不可重复读 | 同一行两次读值不同（针对 UPDATE/DELETE） | B 两次读 id=1 的余额，中间 A 提交了修改 |
| 幻读 | 同一范围两次读行数不同（针对 INSERT） | B 两次 where age>18 统计，中间 A 插入了新行 |
记忆口诀：不可重复读是"值变了"，幻读是"行多了"；前者靠行锁就能防，后者必须锁住"还不存在的间隙"，所以才需要间隙锁。
9. MVCC：隐藏字段 + undo 版本链 + ReadView
MVCC（Multi-Version Concurrency Control）是 InnoDB 实现 RC/RR 的核心，目标是读不加锁、读写不互相阻塞。它由三块拼成：
① 行记录的三个隐藏字段
| 字段 | 宽度 | 作用 |
|---|---|---|
DB_TRX_ID | 6B | 最后一次插入/修改这行的事务 ID |
DB_ROLL_PTR | 7B | 回滚指针，指向 undo log 里的上一个版本 |
DB_ROW_ID | 6B | 隐藏主键，只有在表没有主键也没有唯一非空索引时才生成 |
② undo log 版本链
每次 update 都会把旧值写进 undo log，并让新记录的 DB_ROLL_PTR 指向它，形成一条从新到旧的单向链表：
text
当前行（最新） undo log 版本链
┌──────────────────────────┐
│ id=1 balance=300 │
│ DB_TRX_ID = 103 │
│ DB_ROLL_PTR ──────────────┼──► ┌──────────────────────┐
└──────────────────────────┘ │ balance=200 trx=102 │
│ roll_ptr ─────────────┼──► ┌──────────────────────┐
└──────────────────────┘ │ balance=100 trx=101 │
│ roll_ptr → NULL │
└──────────────────────┘③ ReadView（一致性视图）的四个字段
| 字段 | 含义 |
|---|---|
m_ids | 创建 ReadView 瞬间，所有活跃（未提交）事务的 ID 列表 |
min_trx_id | m_ids 里的最小值 |
max_trx_id | 系统下一个将要分配的事务 ID（不是 m_ids 的最大值！） |
creator_trx_id | 创建这个 ReadView 的事务自己的 ID |
可见性判断算法（拿到版本链上某个版本的 DB_TRX_ID = trx_id，逐条判断）：
text
if (trx_id == creator_trx_id) → 可见（自己改的，总能看到）
else if (trx_id < min_trx_id) → 可见（创建视图前就已提交）
else if (trx_id >= max_trx_id) → 不可见（创建视图之后才开启的事务）
else if (trx_id in m_ids) → 不可见（创建视图时还活跃，即未提交）
else → 可见（ID 在区间内但不活跃，说明已提交）
不可见 → 顺着 DB_ROLL_PTR 找上一个版本，重复判断，直到找到可见版本或链表尽头（返回空）RC 和 RR 的唯一区别，就在 ReadView 的创建时机：
| 隔离级别 | ReadView 创建时机 | 结果 |
|---|---|---|
| RC | 每次 select 都重新创建 | 每次都能看到最新已提交数据 → 不可重复读 |
| RR | 事务内第一次快照读时创建，之后全程复用 | 整个事务看到的是同一个瞬间的快照 → 可重复读 |
⚠️ 两个高频细节：① RR 下如果事务一开始就执行了当前读或 update，相当于提前拿到了最新数据，后续快照读的视图基准会和直觉不同；②
start transaction并不会立刻创建 ReadView 和分配事务 ID，第一条语句执行时才真正开启事务；想立刻开启要用start transaction with consistent snapshot。
10. 快照读 vs 当前读
| 快照读（一致性非锁定读） | 当前读（锁定读） | |
|---|---|---|
| 语句 | 普通 SELECT | SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE（8.0 推荐 FOR SHARE）、UPDATE、DELETE、INSERT |
| 读什么 | undo 版本链里对本事务可见的历史版本 | 最新已提交版本 |
| 加锁 | ❌ 不加锁 | ✅ 加行锁（FOR UPDATE 排他 X，FOR SHARE 共享 S） |
| 并发 | 读写互不阻塞 | 会阻塞其他当前读和写 |
| 典型用途 | 查询、报表、列表页 | 扣库存、转账、状态流转 |
面试加分表述：「
SERIALIZABLE隔离级别下 MVCC 实际上被绕开了——普通select也会被隐式加上共享锁，退化成当前读。所以 MVCC 严格说只服务于 RC 和 RR 这两个级别。另外insert也是当前读，它要去检查唯一键冲突，这就是插入意向锁和间隙锁冲突的源头。」
11. 锁体系全景
text
MySQL 锁
├─ 全局锁 FLUSH TABLES WITH READ LOCK（FTWRL）→ 整库只读，全库逻辑备份时用
├─ 表级锁
│ ├─ 表锁 LOCK TABLES t READ / WRITE（InnoDB 下几乎不用）
│ ├─ MDL 元数据锁 ★ 所有 DML 自动加 MDL 读锁，DDL 加 MDL 写锁 → 读写互斥
│ ├─ 意向锁 IS / IX：加行锁前自动加的表级标记，让表锁能 O(1) 判断冲突
│ └─ AUTO-INC 锁 自增主键分配时的轻量锁（受 innodb_autoinc_lock_mode 控制）
└─ 行级锁（只有 InnoDB 有，且必须走索引）
├─ Record Lock 记录锁：锁住索引上的一条记录
├─ Gap Lock 间隙锁：锁住两条记录之间的"开区间"，只为阻止 INSERT
├─ Next-Key Lock 临键锁 = Record Lock + Gap Lock，左开右闭区间 (a, b]
└─ Insert Intention Lock 插入意向锁：INSERT 前在目标间隙加的意向锁S 锁与 X 锁的兼容矩阵（行级）
| S（共享） | X（排他） | |
|---|---|---|
| S | ✅ 兼容 | ❌ 冲突 |
| X | ❌ 冲突 | ❌ 冲突 |
间隙锁的兼容性是个特例——这是很多人答不上来的细节：
| 其他事务的 Gap Lock | 其他事务的 Insert Intention Lock | |
|---|---|---|
| Gap Lock | ✅ 兼容（间隙锁之间不冲突！） | ❌ 冲突（这才是它阻止幻读的方式） |
也就是说：间隙锁只和"往这个间隙里插入"这个动作冲突，两个事务可以同时持有同一个间隙的间隙锁。这正是经典死锁场景的根源——两个事务都先拿到同一间隙的 Gap Lock（兼容，都成功），然后各自想插入，各自等对方的 Gap Lock 释放 → 循环等待。
意向锁为什么存在：没有意向锁时，要给表加写锁必须逐行扫描看有没有行锁；有了 IX 标记，只需看表上有没有 IS/IX 即可。注意：意向锁之间全部互相兼容，它只和表级 S/X 锁冲突。
MDL 为什么是线上事故高发区：alter table 需要 MDL 写锁，而 MDL 写锁会在队列里阻塞后续所有 MDL 读锁。只要有一个长事务没提交（持有 MDL 读锁），DDL 就卡住，紧跟其后的所有普通查询也全部卡住 → 库瞬间不可用。
12. InnoDB 加锁规则（RR 级别，极高频）
记住 "两个原则 + 两个优化 + 一个 bug"（来自官方行为的经典总结）：
| 规则 | 内容 |
|---|---|
| 原则 1 | 加锁的基本单位是 Next-Key Lock（左开右闭） |
| 原则 2 | 查找过程中访问到的对象才会加锁（所以 select 的列影响不到加锁，但索引选择会） |
| 优化 1 | 唯一索引上的等值查询，若记录存在，Next-Key Lock 退化为 Record Lock（不需要间隙锁） |
| 优化 2 | 索引上的等值查询，向右遍历时最后一个值不满足等值条件，Next-Key Lock 退化为 Gap Lock |
| 一个 bug | 唯一索引上的范围查询，会一直访问到第一个不满足条件的值为止（多锁一个 next-key） |
举例（表 t(id PK, c unique, d)，数据 id/c = 0,5,10,15,20）：
| SQL | 加锁范围 | 依据 |
|---|---|---|
where id = 10 for update | 只锁 id=10 这一行 | 优化 1，退化为 Record Lock |
where id = 7 for update | 间隙 (5, 10) | 记录不存在，不退化，但也无记录可锁 → 只剩 Gap |
where c = 10 for update（非唯一索引时） | (5,10] + (10,15) | 原则 1 + 优化 2 |
where id > 10 and id <= 15 for update | (10,15] + (15,20] | 一个 bug：范围查询多锁了一个 next-key |
where d = 5 for update（d 无索引） | 扫全表，锁全部记录 + 所有间隙 | 原则 2：访问到了所有行 |
面试加分表述：「最后一条最致命：行锁必须命中索引，否则退化成『给全表每一行都加锁』，效果等同表锁。线上事故里很大一部分
Lock wait timeout exceeded，根因都是update ... where 非索引字段。我们现在在 CI 里加了 SQL 审核，update/delete的 where 条件如果走不到索引会直接卡住发布。」
13. 日志体系：redo log、undo log、binlog 与两阶段提交
| redo log | undo log | binlog | |
|---|---|---|---|
| 所属层 | InnoDB 引擎层 | InnoDB 引擎层 | Server 层（所有引擎共用） |
| 内容 | 物理日志："在某个表空间某页某偏移量做了什么修改" | 逻辑日志：反向 SQL / 旧版本数据 | 逻辑日志：STATEMENT / ROW / MIXED 三种格式 |
| 作用 | 崩溃恢复（持久性） | 事务回滚（原子性）+ MVCC 快照读 | 主从复制 + 数据归档 / 误删恢复 |
| 写入方式 | 循环写，固定大小（ib_logfile0/1），写满就要推进 checkpoint 刷脏页 | 写在 undo 表空间，由 purge 线程异步清理 | 追加写，文件写满就切下一个，可长期保留 |
| 关键参数 | innodb_flush_log_at_trx_commit（0 每秒刷 / 1 每次提交刷（默认，最安全） / 2 写 OS 缓存） | innodb_undo_log_truncate | sync_binlog（0 靠 OS / 1 每次提交刷（默认，最安全） / N 攒 N 个） |
两阶段提交（2PC）—— 为什么需要它
redo log 和 binlog 是两个独立的日志系统，如果不做协调，任何一个写完后崩溃都会导致主从数据不一致：
text
一条 update 的提交流程（两阶段提交）
① 写 redo log，状态 = prepare ← 引擎层
② 写 binlog ← Server 层
③ 写 redo log，状态 = commit ← 引擎层
崩溃恢复时的判定逻辑：
redo 是 commit → 直接提交
redo 是 prepare + binlog 完整 → 提交（因为从库会拿到这条 binlog，主库必须跟上）
redo 是 prepare + binlog 不完整 → 回滚如果反过来会怎样（必须能答）：
| 假设 | 后果 |
|---|---|
| 先写完 redo 就提交，再写 binlog，中间崩溃 | 主库有数据，binlog 没有 → 从库和备份都丢了这条，主从不一致 |
| 先写完 binlog，再写 redo，中间崩溃 | binlog 有数据，主库没有 → 从库多了一条，主从不一致 |
面试加分表述：「两阶段提交的本质是把『两个日志都写成功』变成一个可判定的原子状态。再补两个工程细节：一是
innodb_flush_log_at_trx_commit=1+sync_binlog=1是所谓的『双 1 配置』，最安全但每次提交两次 fsync，所以 MySQL 用 组提交（group commit） 把多个事务的 fsync 合并，靠binlog_group_commit_sync_delay调节；二是我们线上非核心库会把sync_binlog调成 100、flush_log_at_trx_commit调成 2，用『崩溃可能丢 1 秒数据』换 3~5 倍写入吞吐，这是需要和业务方明确确认的取舍。」
14. 一条 update 的完整执行链路
把前面所有组件串起来，这是 MySQL 面试最爱的一道"串联题"：
text
UPDATE user SET balance = balance - 100 WHERE id = 1;
1. 连接器：校验权限
2. 解析器 → 优化器：决定走主键索引
3. 执行器调用 InnoDB：读 id=1 的页
├─ 页在 Buffer Pool？ 命中则直接返回
└─ 不在 → 从磁盘读入 Buffer Pool
4. InnoDB 给这行加 X 锁（当前读，读最新版本）
5. 写 undo log（记录 balance 旧值 500），并挂上版本链（服务回滚 + MVCC）
6. 在 Buffer Pool 里修改数据页 → 该页成为 **脏页**
7. 写 redo log 到 log buffer，状态 prepare
8. Server 层写 binlog（row 格式：before image + after image）
9. redo log 状态改为 commit，按双 1 配置 fsync
10. 释放行锁，返回客户端"影响 1 行"
11. 【异步】后台线程按 checkpoint / LRU 把脏页刷到磁盘
12. 【异步】purge 线程清理不再被任何 ReadView 需要的 undo log四个"异步"是关键认知：提交返回的那一刻，数据页还在内存里是脏的；真正保证不丢的是第 9 步落盘的 redo log。这就是 WAL（Write-Ahead Logging）：用一次顺序写换掉多次随机写。
✨ 高级深挖知识点
1. 最左前缀原则的本质，以及"范围查询后失效"到底失效了什么
联合索引 idx(a, b, c) 的 B+ 树，是先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序——就像字典里先按首字母、再按第二个字母排序。
text
idx(a,b,c) 叶子节点的实际顺序
(1, 10, 100) → (1, 10, 200) → (1, 20, 100) → (2, 5, 300) → (2, 5, 400) → (3, 1, 50)
└─ a 全局有序 ─┘
└─ b 只在 a 相同的范围内有序 ─┘
└─ c 只在 (a,b) 都相同的范围内有序 ─┘于是一切都清楚了：
| 查询条件 | 索引使用情况 | 原因 |
|---|---|---|
a=1 | ✅ a | 命中最左 |
a=1 and b=10 | ✅ a, b | 连续命中 |
a=1 and b=10 and c=100 | ✅ a, b, c | 全部命中 |
b=10 / c=100 / b=10 and c=100 | ❌ 不走该索引 | b 在全局是乱序的，无法二分定位 |
a=1 and c=100 | ⚠️ 只用 a，c 靠索引下推过滤 | c 在跳过 b 后是乱序的 |
a=1 and b>10 and c=100 | ⚠️ a、b 走索引，c 不能用于定位 | b 是范围后，c 在这个范围里乱序 |
a>1 and b=10 | ⚠️ 只有 a 用于定位 | 同上 |
a=1 and b in (10,20) and c=100 | ✅ 三列都能用 | IN 是多个等值，不是范围，可以拆成多次等值查找 |
where b=10 and a=1（顺序写反） | ✅ 正常走索引 | 优化器会自动调整 where 条件顺序，和书写顺序无关 |
⚠️ 草稿里写 "c 失效" 不够准确。准确说法是：c 不能用于 B+ 树的定位（
key_len里不包含 c），但从 MySQL 5.6 起，索引下推（ICP）会把c=100这个条件下推到引擎层，在二级索引上就把不匹配的行过滤掉，从而减少回表次数。EXPLAIN的Extra会显示Using index condition。这是一个很能体现深度的补充。
ICP（Index Condition Pushdown）前后对比
text
没有 ICP（5.5 及之前） 有 ICP（5.6+）
引擎层：按 a=1 扫出 1000 行主键 引擎层：按 a=1 扫出 1000 行，
↓ 全部回表 1000 次 就地用 c=100 过滤掉 990 行
Server 层：用 c=100 过滤，剩 10 行 ↓ 只回表 10 次
Server 层：拿到 10 行
→ 1000 次随机 IO → 10 次随机 IO2. order by / group by / join 怎么用上索引
索引不只服务 where，它的有序性同样能消掉排序和分组的开销。
| 场景 | 能否用索引 | 说明 |
|---|---|---|
where a=1 order by b | ✅ | idx(a,b)，b 在 a=1 的范围内有序，Extra 无 Using filesort |
where a=1 order by c | ❌ filesort | 跳过了 b |
order by a asc, b desc | ❌ filesort（5.7）/ ✅（8.0 若建了 idx(a asc, b desc)） | 混合排序方向需要降序索引 |
where a>1 order by a | ✅ | 排序列就是范围列本身 |
where a>1 order by b | ❌ filesort | b 在 a 的范围内乱序 |
group by a, b | ✅ idx(a,b) | MySQL 的 group by 隐含排序，索引有序即可免排序；8.0 起不再隐式排序，需显式 order by |
join on t1.a = t2.b | ✅ 被驱动表的关联字段必须有索引 | 否则退化成 BNL，复杂度从 O(n) 变 O(n·m) |
3. 索引失效的真正原因（逐条拆解，不只是背清单）
| 失效写法 | 根本原因 | 正确写法 |
|---|---|---|
where YEAR(create_time) = 2026 | 索引里存的是 create_time 的原值，不是 YEAR() 的结果，B+ 树无法用函数结果做二分 | where create_time >= '2026-01-01' and create_time < '2027-01-01'，或 8.0 建函数索引 |
where id + 1 = 10 | 同上，运算发生在索引列上 | where id = 9（把运算移到常量侧） |
where phone = 13800000000（phone 是 varchar） | 隐式类型转换：MySQL 会把字符串列转成数字再比较，等价于 CAST(phone AS signed) = ... → 列上有函数 | where phone = '13800000000' |
where id = '10'（id 是 int） | 反方向转换是把常量转成数字，列上无函数 → 索引仍然有效（这是个关键的不对称点！） | 无需改，但仍建议类型对齐 |
where name like '%abc' / '%abc%' | 前缀未知，无法定位 B+ 树起点 | like 'abc%' 可用；真要前导模糊 → 反转列 + 反转索引，或上 ES |
where a=1 or b=2（b 无索引） | or 要求两边都能用索引，否则必须全表扫 | 给 b 建索引，或改成 union all |
where a != 1 / not in / not like | 否定条件通常匹配大部分行，优化器算出全表扫更便宜 | 改写成正向范围，或确认选择性后用 FORCE INDEX |
where a is null | 不一定失效！InnoDB 索引里会存 NULL（排在最前），is null 可以走索引；但 is not null 常因选择性差被放弃 | 字段尽量设 NOT NULL DEFAULT '' |
| 字符集/排序规则不一致的 join | 关联字段 utf8 vs utf8mb4 时会隐式转换 → 被驱动表索引失效 | 统一全库字符集为 utf8mb4 |
| 索引区分度太低（如性别） | 优化器估算扫索引 + 回表的成本高于全表顺序扫 | 不要给低区分度列单独建索引，放进联合索引后面 |
面试加分表述：「我更愿意把这些归成一句话：索引失效的唯一根因是『无法在有序的 B+ 树上确定一个连续的扫描区间』，剩下的都是推论。再加一条容易被忽略的——即使条件完全可以走索引，优化器也可能主动放弃，因为它是基于成本的；这时要看
EXPLAIN的rows估算是否严重失真，用ANALYZE TABLE重新收集统计信息，必要时才用FORCE INDEX。」
4. 幻读到底是什么，RR 下为什么说"没完全解决"
先明确官方定义：幻读是在同一事务中，同样的查询条件，第二次查询出现了第一次没有的行。
RR 下的双重防护：
| 读类型 | 防幻读机制 |
|---|---|
| 快照读（普通 select） | MVCC：整个事务复用同一个 ReadView，新事务插入的行 trx_id >= max_trx_id，天然不可见 |
当前读（for update / update / delete） | Next-Key Lock：锁住记录 + 间隙，别人插不进来 |
但有三个场景仍然能观察到幻读现象（这是能明显拉开差距的回答）：
sql
-- 场景 1：先快照读，再当前读（最经典）
-- 事务 A
BEGIN;
SELECT * FROM t WHERE id > 10; -- 快照读，返回 0 行
-- 事务 B：INSERT id=11; COMMIT;
SELECT * FROM t WHERE id > 10; -- 快照读，仍返回 0 行 ✅ 可重复读
SELECT * FROM t WHERE id > 10 FOR UPDATE; -- 当前读！返回 1 行 ❌ 幻读出现sql
-- 场景 2：自己 update 了别人新插入的行，它就"变成自己的"从而可见
-- 事务 A
BEGIN;
SELECT * FROM t WHERE c = 5; -- 返回 0 行
-- 事务 B：INSERT (id=1, c=5); COMMIT;
UPDATE t SET d = 100 WHERE c = 5; -- 当前读，改到了这行，trx_id 变成 A 自己
SELECT * FROM t WHERE c = 5; -- 返回 1 行 ❌（creator_trx_id 规则：自己改的总可见）sql
-- 场景 3：事务开启后延迟了第一次快照读
-- 事务 A：BEGIN;（此时还没创建 ReadView）
-- -- 事务 B：INSERT; COMMIT;
-- 事务 A：SELECT ... -- 这才创建 ReadView，看到了 B 的数据
-- 解法：BEGIN 后立刻 SELECT，或用 START TRANSACTION WITH CONSISTENT SNAPSHOT面试加分表述：「所以严谨说法是：RR 在纯快照读或纯当前读的场景下都能避免幻读，但在同一事务里混用两种读的时候就可能出现。工程上的应对不是去调隔离级别，而是统一约定：一个事务内要么全程快照读做查询，要么一上来就用当前读加锁，不要混。另外很多互联网公司把隔离级别主动降成 RC，因为 RC 下没有间隙锁，锁冲突和死锁大幅减少，代价是要靠业务层（版本号/状态机）自己保证一致性——这是个典型的工程取舍。」
5. 间隙锁加锁规则实战
同样一条 select ... for update，索引类型不同，锁的范围天差地别。表 t(id PK, c KEY, d)，数据 (0,0),(5,5),(10,10),(15,15),(20,20)：
| SQL | 锁范围 | 说明 |
|---|---|---|
where id = 10 for update | [10] 单行 | 唯一索引等值 + 记录存在 → 退化 Record Lock |
where id = 7 for update | (5, 10) | 记录不存在，只能锁间隙 |
where c = 10 for update | (5,10] + (10,15) | 非唯一索引：next-key + 向右一个 gap |
where c = 7 for update | (5, 10) | 不存在 → gap |
where id >= 10 and id < 11 for update | [10] + (10,15] | 范围查询"多锁一个"的 bug 行为 |
where c >= 10 and c < 11 for update | (5,10] + (10,15] | 非唯一索引范围 |
where d = 10 for update（d 无索引） | 全表所有记录 + 所有间隙 | 退化为类表锁，线上禁止 |
where id > 20 for update | (20, +∞)（supremum pseudo-record） | 右端是正无穷伪记录 |
怎么实时观察锁（排查必备）：
sql
-- MySQL 8.0：直接看当前持有和等待的锁
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- 5.7 及以前
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_trx; -- 看长事务
-- 通用：看最近一次死锁详情
SHOW ENGINE INNODB STATUS; -- 关注 LATEST DETECTED DEADLOCK 段6. 死锁：成因、复现、排查、预防
四个必要条件：互斥、持有并等待、不可剥夺、循环等待。
三类线上最常见的死锁：
text
【类型 1】更新顺序不一致（最常见，也最好修）
事务 A：update id=1 → update id=2
事务 B：update id=2 → update id=1
修复：统一按主键升序更新；批量更新前先对 ID 列表排序
【类型 2】间隙锁 + 插入意向锁（RR 独有，最隐蔽）
事务 A：select ... where id=7 for update → 拿到 gap(5,10) 锁
事务 B：select ... where id=8 for update → 也拿到 gap(5,10) 锁（间隙锁互相兼容！）
事务 A：insert id=7 → 等 B 的 gap 锁
事务 B：insert id=8 → 等 A 的 gap 锁 → 死锁
修复：改用 insert ... on duplicate key update，或降到 RC，或先 insert 捕获唯一键冲突再处理
【类型 3】唯一键冲突回滚引发的锁等待
两个事务并发 insert 同一个唯一键，后者被阻塞在 S 锁上，
若前者回滚，后者继续；但若各自还持有别的锁，极易成环
修复：幂等插入用唯一索引 + 捕获 1062 错误，不要用"先查后插"死锁日志怎么读（SHOW ENGINE INNODB STATUS 的关键字段）：
| 字段 | 看什么 |
|---|---|
*** (1) TRANSACTION / *** (2) TRANSACTION | 两个事务各自执行的 SQL |
WAITING FOR THIS LOCK TO BE GRANTED | 它在等什么锁（lock_mode X locks gap before rec 就是间隙锁） |
HOLDS THE LOCK(S) | 它已经持有什么锁 |
*** WE ROLL BACK TRANSACTION (N) | InnoDB 选择回滚了哪个（选 undo 量小的，即"代价小"的） |
死锁检测的开销（容易被忽略的深挖点）：innodb_deadlock_detect=ON（默认）时，每个被阻塞的事务都要做一次有向图检测，复杂度 O(n²)。热点行更新场景下（如秒杀同一个商品），1000 个线程等同一行，光死锁检测就能吃满 CPU。应对：① 业务层做热点打散（库存分段）；② 前置限流，控制到库的并发；③ 极端场景关闭检测改用 innodb_lock_wait_timeout 兜底（有风险，需评估）。
7. 主键选择：自增 vs UUID vs 雪花 ID
| 方案 | 优点 | 缺点 |
|---|---|---|
BIGINT AUTO_INCREMENT | 有序追加，只在最右页插入，几乎不页分裂；8 字节，二级索引小 | 单库唯一，分库分表要额外处理；会暴露业务量级 |
UUID / CHAR(36) | 全局唯一、可本地生成 | 无序 → 随机插入 → 频繁页分裂 + 页利用率下降（碎片）；36 字节让所有二级索引暴涨；比较字符串也更慢 |
| 雪花 ID（Snowflake，bigint） | 趋势递增 + 全局唯一 + 8 字节，综合最优 | 依赖时钟，要处理时钟回拨；需要 ID 服务 |
| 业务字段（如订单号） | 省一个索引 | 业务规则一变就要改主键，强耦合，不推荐 |
text
自增主键插入（顺序） UUID 插入（随机）
┌────┬────┬────┐ ┌────┬────┬────┐
│ p1 │ p2 │ p3 │ ← 只在 p3 尾部追加 │ p1 │ p2 │ p3 │ ← 随机命中任意页
└────┴────┴────┘ └────┴────┴────┘
页利用率高、顺序 IO 命中已满的页 → 页分裂 → 一页拆两页
页利用率掉到 50%、随机 IO、索引体积翻倍innodb_autoinc_lock_mode 三种模式（深挖点）：
| 值 | 行为 | 影响 |
|---|---|---|
| 0 传统 | 整个 insert 语句期间持有表级 AUTO-INC 锁 | 并发差，但自增值连续 |
| 1 连续（5.7 默认） | 简单 insert 立即释放锁；批量 insert 仍持锁到语句结束 | 折中 |
| 2 交错（8.0 默认） | 全程只用轻量互斥，不持表锁 | 并发最好，但自增值可能不连续；且binlog 必须是 ROW 格式，否则主从不一致 |
8. Buffer Pool 的改良 LRU、预读失效与缓冲池污染
朴素 LRU 在数据库里有两个致命问题，InnoDB 用**分区 LRU（young / old 区，默认 old 占 37%）**解决：
| 问题 | 场景 | 解法 |
|---|---|---|
| 预读失效 | InnoDB 顺序读时会预读整个区（64 页），但可能只用到 1 页，其余白占头部 | 新页先放 old 区头部，而不是 young 头部；没被二次访问就先被淘汰 |
| 缓冲池污染 | 一条 like '%xx%' 全表扫描，把几十 G 的冷数据全刷进内存，挤掉所有热点页 | innodb_old_blocks_time（默认 1000ms）：页在 old 区停留超过这个时间后再次被访问，才允许晋升到 young 区；全表扫描的页读完就不再访问，自然晋升不了 |
怎么看 Buffer Pool 健不健康：
sql
SHOW ENGINE INNODB STATUS; -- BUFFER POOL AND MEMORY 段
-- 核心指标：Buffer pool hit rate，线上应该 > 99%（显示为 1000/1000）
SELECT * FROM information_schema.innodb_buffer_pool_stats;9. Change Buffer：唯一索引 vs 普通索引的写性能差异
这是一个"看起来和索引无关，其实很深"的点：
| 普通索引 | 唯一索引 | |
|---|---|---|
| 插入时目标页不在内存 | 写进 Change Buffer 就返回，不读磁盘 | 必须把页读进内存判重 → 一次随机读 |
| 插入时目标页在内存 | 直接改 | 直接改，差异不大 |
| 结论 | 写密集 + 目标页大概率不在内存（如大表按时间插入历史数据）→ 普通索引写入快一个数量级 | 需要数据库层保证唯一性时才用 |
面试加分表述：「所以『能用业务逻辑保证唯一就别建唯一索引』这个说法是有性能依据的，但我在实践中仍然倾向于建唯一索引——因为分布式环境下业务层的『先查后插』根本无法保证唯一，唯一索引是最后一道防线。真正的权衡点是：写入量极大且确定不会重复的归档/日志表用普通索引，业务表一律用唯一索引兜底幂等。」
10. count(*) 的真相与各种写法对比
| 写法 | 行为 | 性能 |
|---|---|---|
count(*) | 优化器专门优化过，会选最小的那棵二级索引树来扫，不取值 | 最快（InnoDB 里） |
count(1) | 遍历全表，每行塞个常量 1 进去计数 | 与 count(*) 几乎相同，没有"更快"这回事 |
count(主键 id) | 遍历聚簇索引（最大的树），取 id 值返回给 Server 层 | 比 count(*) 慢 |
count(普通字段) | 遍历、取值，且要判断 NULL，NULL 不计数 | 最慢，且语义不同 |
为什么 InnoDB 不能像 MyISAM 一样 O(1) 返回：因为 MVCC 的存在，不同事务在同一时刻看到的行数本来就不一样，不存在一个"全局正确的行数"。
大表 count 的工程解法：
| 方案 | 说明 |
|---|---|
SHOW TABLE STATUS / information_schema.tables 的 rows | 采样估算，误差可达 40%~50%，只能做展示，不能做业务判断 |
单独的计数表（同事务 +1） | 精确且与业务数据强一致，代价是计数行成为热点 |
| Redis 计数 | 快，但无法与数据库操作做到原子，崩溃会不一致 |
| 列表页改成"不显示总数" | 大厂常见做法（只给"下一页"），从产品侧绕开问题 |
11. EXPLAIN 全字段解读：面试必考的"读懂执行计划"
EXPLAIN 是优化 SQL 的入口，面试官常直接给一个输出让你解读。先记住 最该看的四列：type、key、rows、Extra。
| 列 | 含义 | 关注点 |
|---|---|---|
id | 查询序号，越大越先执行；相同则从上往下 | 判断子查询 / 派生表的执行顺序 |
select_type | SIMPLE / PRIMARY / SUBQUERY / DERIVED / UNION / MATERIALIZED | 出现 DERIVED 说明有派生表，可能无法用索引 |
table | 表名，或 <derived2> / <union1,2> | 定位是哪张表走了烂计划 |
partitions | 命中的分区 | 分区表才有意义 |
type | 访问类型，见下表 | 至少要到 range，核心接口要 ref 及以上 |
possible_keys | 优化器"考虑过"的索引 | 为空说明根本没有可用索引 |
key | 实际选用的索引 | 为 NULL 即全表/全索引扫描 |
key_len | 使用的索引长度（字节） | 判断联合索引用到了几个字段的唯一依据 |
ref | 索引比较的对象（常量 const 或其他表字段） | 看 join 的驱动关系 |
rows | 预估扫描行数 | 与最终返回行数差距过大 = 过滤效率差 |
filtered | 存储引擎返回数据经 Server 层过滤后剩余百分比 | rows * filtered 才是给下一步的行数 |
Extra | 附加信息，见下表 | 信息量最大的一列 |
type 从好到坏：
text
system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery
> range > index > ALLconst：主键 / 唯一索引等值匹配，最多一行，优化器直接当常量eq_ref：join 时被驱动表用主键 / 唯一索引等值匹配，每行只对应一行ref：普通索引等值匹配，可能返回多行（最常见的"健康"状态）range：索引范围扫描（between、>、in）index：扫描整棵二级索引树（比 ALL 好，因为索引小且常能覆盖）ALL：全表扫描，大表必须优化
Extra 高频值：
| Extra | 含义 | 是否需要优化 |
|---|---|---|
Using index | 覆盖索引，不回表 | 最佳状态 |
Using where | Server 层做了额外过滤 | 配合 rows 大才需关注 |
Using index condition | ICP 索引条件下推生效 | 好事 |
Using filesort | 需要额外排序（内存或磁盘） | 排序字段加索引可消除 |
Using temporary | 用了临时表（常见于 group by / distinct / union） | 必须优化 |
Using join buffer (Block Nested Loop) | 被驱动表没索引，走 BNL | 给 join 字段加索引 |
Using join buffer (hash join) | 8.0 的 hash join | 比 BNL 好，但仍说明无索引 |
Select tables optimized away | 聚合被优化掉（如 max(主键)） | 最佳 |
Impossible WHERE | 条件恒假，不查了 | 检查 SQL 逻辑 |
key_len 怎么算（面试追问重点）：
text
int -> 4
bigint -> 8
datetime -> 5
varchar(n) utf8mb4 -> n * 4 + 2（+2 存长度）
char(n) utf8mb4 -> n * 4
可为 NULL 的列 -> 再 + 1（存 NULL 标志位）面试加分表述：「比如
idx(a,b,c)三列都是not null int，总 key_len 是 12。如果 EXPLAIN 里 key_len 是 8，说明只用到了a和b，c没参与索引定位——这是我排查最左前缀是否生效时最直接的手段，比猜测靠谱。」
比 EXPLAIN 更深的两个工具：
sql
-- 1. EXPLAIN ANALYZE（8.0.18+）：真实执行并给出实际耗时与行数
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY create_time DESC LIMIT 10;
-- 2. optimizer trace：看优化器为什么选了这个索引（成本计算过程）
SET optimizer_trace = "enabled=on";
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace = "enabled=off";optimizer_trace 里的 considered_execution_plans 会列出每个候选索引的 cost，这是解释"明明有索引为什么不走"的终极证据——通常是因为优化器基于采样统计信息估算的 rows 偏大，认为回表成本高于全表扫描。此时的解法：
ANALYZE TABLE t;重新采样统计信息（成本最低，优先尝试）- 调大
innodb_stats_persistent_sample_pages（默认 20 页，大表采样不足） - 最后手段：
FORCE INDEX(idx_xxx)或 8.0 的优化器提示/*+ INDEX(t idx_xxx) */
12. 慢查询定位全流程：从"系统变慢了"到"这行 SQL"
这是一道极高频的场景题，必须有完整的排查链路而不是只会说"加索引"。
第一步：确认慢日志开着
sql
SHOW VARIABLES LIKE 'slow_query_log%'; -- 是否开启 + 文件路径
SHOW VARIABLES LIKE 'long_query_time'; -- 阈值，线上建议 0.1 ~ 1 秒
SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; -- 记录未用索引的语句
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2; -- 注意：只对新连接生效坑：
long_query_time改了之后当前会话不生效，必须重连；而且它的判定不含锁等待时间，所以"因为锁等待慢"的 SQL 可能根本不进慢日志。
第二步：聚合分析慢日志
bash
# pt-query-digest：按总耗时排序给出 TOP SQL（指纹聚合，变量会被抽象成 ?）
pt-query-digest /var/log/mysql/slow.log > report.txt
# 只看最近一小时
pt-query-digest --since '1h' /var/log/mysql/slow.log关注三个指标：总耗时占比（优化收益最大）、调用次数（高频小慢查询常比单次大慢查询更致命）、Rows examined / Rows sent 比值（比值越大越说明过滤效率差）。
第三步：实时现场（故障正在发生时）
sql
-- 当前所有连接在干什么，找 Time 大、State 异常的
SHOW FULL PROCESSLIST;
-- 8.0 更推荐：看正在执行的语句 + 等待事件
SELECT * FROM sys.session WHERE command = 'Query' ORDER BY time DESC;
-- 看事务（找长事务 / 未提交事务）
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;
-- 看锁等待（8.0）
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;
-- 杀掉元凶
KILL QUERY 12345; -- 只终止语句，保留连接（更温和）
KILL 12345; -- 断开连接，会触发回滚常见 State 的含义：
| State | 说明 |
|---|---|
Sending data | 不是在传数据，而是正在读行并处理（大部分慢查询都卡在这） |
Copying to tmp table | 正在用临时表，group by / order by 没走索引 |
Waiting for table metadata lock | MDL 阻塞，通常被长事务或未提交的 DDL 卡住 |
Creating sort index | 正在 filesort |
statistics | 优化器在算执行计划，一般很快，慢说明统计信息有问题 |
第四步：单条 SQL 深挖
sql
-- 看这条语句各阶段耗时（旧工具，但直观）
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
-- 8.0 推荐：performance_schema 的语句摘要
SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_ms,
sum_rows_examined/count_star AS avg_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;第五步：分层归因——这是答题结构的关键，不要一上来就说加索引：
text
慢 SQL 的根因分层
├── SQL 层 索引缺失/失效、select *、深分页、join 无索引、排序无索引
├── 数据层 单表过大、数据倾斜（某用户 100 万条）、冷数据未归档
├── 事务层 长事务导致 undo 链过长、MVCC 回溯开销、锁等待
├── 实例层 Buffer Pool 太小导致频繁读盘、磁盘 IO 打满、CPU 饱和
└── 架构层 无缓存、读写未分离、热点集中、未分库分表面试加分表述：「我们有次接口 P99 从 80ms 飙到 3s，但慢日志里没有慢 SQL。最后是通过
innodb_trx发现有个批处理任务开了 20 分钟的长事务，导致 undo 版本链很长，正常的快照读要沿版本链回溯几千次。这件事让我养成了排查顺序：先看锁和事务，再看 SQL——因为锁等待时间不计入long_query_time，慢日志会漏报。」
13. join 原理：NLJ、BNL、hash join 与"小表驱动大表"
三种算法：
| 算法 | 触发条件 | 复杂度 | 说明 |
|---|---|---|---|
| Index Nested-Loop Join（NLJ） | 被驱动表 join 字段有索引 | N + N * log M | 逐行驱动，走索引查被驱动表，最优 |
| Block Nested-Loop Join（BNL） | 被驱动表无索引（5.7 及之前） | N * M | 驱动表批量进 join_buffer，被驱动表全表扫描一次（buffer 装不下则扫多次） |
| Hash Join | 8.0.18+ 且无可用索引 | N + M | 驱动表建哈希表，被驱动表探测，比 BNL 快一个量级 |
text
NLJ：orders(驱动, 1000 行) join users(被驱动) on orders.user_id = users.id
for each row r in orders: -- 扫 1000 行
查 users 主键索引 r.user_id -- 每次 log M
=> 扫描行数 ≈ 1000 + 1000 * 3
BNL：被驱动表 join 字段无索引
把 orders 1000 行放进 join_buffer
全表扫描 users（10 万行），每行与 buffer 内 1000 行做内存比较
=> 磁盘扫描 1000 + 100000，内存比较 1 亿次（CPU 被打满的真凶）"小表驱动大表"的准确含义：不是比较两张表的总行数，而是比较 过滤条件之后参与 join 的数据量（行数 × 参与字段长度）。
sql
-- t1 一千万行，但 where 过滤后只剩 10 行 → t1 才是"小表"，应作驱动表
select * from t1 straight_join t2 on t1.b = t2.b where t1.id < 10;straight_join 可以强制左边为驱动表，绕过优化器选择；但生产慎用，数据分布变化后会固化错误计划。
BNL 的三个真实危害（面试追问常问）：
- 多次全表扫描被驱动表：
join_buffer_size装不下驱动表时要分块，被驱动表被扫 K 次 - 污染 Buffer Pool：被驱动表的冷数据大量进入 young 区（被扫两次以上会从 old 升到 young），把真正的热数据挤出去，影响的是全库而不只是这条 SQL
- CPU 飙高：内存比较次数是行数乘积
优化手段优先级：
text
1. 给被驱动表 join 字段加索引（BNL → NLJ） ← 首选
2. 升级到 8.0 让 hash join 接管 ← 次选
3. 增大 join_buffer_size 减少分块次数 ← 缓解
4. 改写：用临时表给被驱动表加索引后再 join ← 5.7 兜底方案
5. 拆成多条单表查询，在应用层做 join ← 微服务/分库分表场景的常规做法面试加分表述：「阿里规范里写'禁止三表以上 join'，本质原因是 join 的执行计划由优化器选择顺序，表越多搜索空间越大（n! 量级），一旦选错驱动表性能差几个数量级，而且跨库之后 join 根本做不了。我们的做法是单表查完在应用层组装，虽然多了几次 RPC，但每次都是主键或索引等值查询，耗时可预测、可缓存，整体 P99 反而更稳。」
14. order by 原理：全字段排序、rowid 排序与 filesort
sql
select city, name, age from t where city = '杭州' order by name limit 1000;假设有索引 idx_city(city)，MySQL 的排序流程取决于一次要排的数据有多大：
（1）全字段排序
text
1. 初始化 sort_buffer，放入 city、name、age 三个字段
2. 从 idx_city 找到第一个 city='杭州' 的主键，回表取三字段放进 sort_buffer
3. 重复直到 city 不等于 '杭州'
4. 对 sort_buffer 按 name 快排
5. 取前 1000 行返回若数据量超过 sort_buffer_size，则使用外部排序：分成多个有序临时文件，再做归并（number_of_tmp_files > 0 即说明走了磁盘）。
（2）rowid 排序
当单行长度超过 max_length_for_sort_data（默认 4096）时，sort_buffer 只放 排序字段 + 主键，排完序再按主键回表取其他字段。
| 维度 | 全字段排序 | rowid 排序 |
|---|---|---|
| sort_buffer 内容 | 所有查询字段 | 排序字段 + 主键 |
| 单次容纳行数 | 少 | 多 |
| 回表次数 | 1 次（取数据时） | 2 次（排序前 + 排序后） |
| 适用 | 行短 | 行长 |
（3）如何彻底避免 filesort
建 idx_city_name(city, name)，此时从索引里天然取出的就是按 name 有序的，扫够 1000 行即可停止，Extra 中 Using filesort 消失。再把 age 加进去做成 idx(city, name, age)，就连回表都省了，Extra 变为 Using index。
观察排序细节：
sql
SET optimizer_trace = 'enabled=on';
SELECT /* ... */;
SELECT * FROM information_schema.optimizer_trace;
-- 看 filesort_summary 里的 rows / examined_rows / number_of_tmp_files / sort_mode
-- sort_mode: <sort_key, packed_additional_fields> = 全字段
-- <sort_key, rowid> = rowid 排序两个高频坑：
- 排序字段方向不一致：
order by a asc, b desc在 8.0 之前无法用idx(a,b)消除 filesort；8.0 可以建降序索引idx(a asc, b desc)解决。 order by rand()取随机行：会创建临时表 + 为每行算随机数 + 全量排序，大表上是灾难。正确做法是先select count(*)，在应用层生成随机 offset，再用主键范围查询。
15. 索引设计实战：区分度、联合索引顺序、前缀索引、冗余索引
（1）区分度（选择性）
sql
-- 结果越接近 1 越适合建索引；低于 0.1 基本无意义
SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders;性别、是否删除这类字段单独建索引几乎无用（优化器会判定走索引比全表扫描更贵）。但注意例外：若 status=9 只占 0.1%，而查询总是查 status=9，这个索引对这个特定查询仍然极其有效——区分度要结合真实查询的数据分布看，不能只看全表平均值。
（2）联合索引字段顺序的三条规则
text
1. 等值条件在前，范围条件在后
where a = ? and b > ? → idx(a, b) ✅
idx(b, a) ❌（b 范围后 a 失去定位能力）
2. 高区分度在前（在都能用上的前提下）
既能过滤更多数据，也让索引树更"扁平有序"
3. 兼顾排序：where + order by 要一起考虑
where a = ? order by b → idx(a, b) 同时消除 filesort（3）前缀索引
sql
-- 邮箱、URL、长文本：只索引前 N 个字符，减小索引体积
ALTER TABLE users ADD INDEX idx_email (email(12));
-- 如何确定 N：找到区分度接近完整列的最小长度
SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS l8,
COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS l12,
COUNT(DISTINCT email) / COUNT(*) AS full
FROM users;代价：前缀索引无法用于覆盖索引（因为只存了部分值，必须回表确认），也无法用于 order by / group by。
对于"前缀区分度很差"的场景（如身份证号前 6 位是地区码），两个技巧：
- 倒序存储：存
REVERSE(id_card)，查询时也REVERSE一下，用后 6 位的高区分度 - 哈希字段：额外加一列
id_card_crc存CRC32(id_card)并建索引，查询时where id_card_crc = CRC32(?) and id_card = ?（必须带原字段防哈希冲突）
（4）冗余索引与重复索引
sql
-- idx(a) 是 idx(a,b) 的冗余索引，可以删掉
-- 但 idx(b) 不是冗余的，必须保留
-- 主键已是聚簇索引，再给主键建唯一索引是纯浪费
-- 8.0 可借助 sys 库找出没用过的索引
SELECT * FROM sys.schema_unused_indexes;
-- 找出冗余索引
SELECT * FROM sys.schema_redundant_indexes;（5）索引过多的四项代价
| 代价 | 说明 |
|---|---|
| 写放大 | 每个二级索引都要维护，insert/update/delete 变慢 |
| 空间占用 | 索引常占表空间的 30%~50%，还挤占 Buffer Pool |
| 优化器成本上升 | 候选索引越多，执行计划评估越慢，选错的概率也越大 |
| DDL 变慢 | 加字段、重建表时要重建所有索引 |
面试加分表述：「我们团队的约定是单表索引不超过 5 个，而且新增索引前必须先看
schema_redundant_indexes和schema_unused_indexes，能靠调整现有联合索引的顺序解决的，就不新建。有次把三个单列索引合并成一个精心排序的联合索引，写入 TPS 提升了 20%，查询反而更快了，因为覆盖索引生效不用回表了。」
16. 深分页优化：limit 1000000, 10 为什么慢
sql
-- 慢：要扫描 1000010 行再丢弃前 1000000 行
select * from orders order by create_time limit 1000000, 10;根因：limit M, N 的语义是"取出 M+N 行，丢掉前 M 行"，offset 越大浪费越多；若走二级索引还要回表 M+N 次。
四种解法：
sql
-- 方案 1：延迟关联（覆盖索引 + 自连接），回表只做 10 次
select o.* from orders o
inner join (
select id from orders order by create_time limit 1000000, 10
) t on o.id = t.id;
-- 方案 2：游标 / 书签分页（最优，但要求前端按序翻页）
select * from orders where create_time > '2026-03-01 10:00:00'
order by create_time limit 10;
-- 注意 create_time 可能重复，需要加主键做"二级游标"保证不丢不重：
select * from orders
where (create_time, id) > ('2026-03-01 10:00:00', 10086)
order by create_time, id limit 10;
-- 方案 3：主键区间（仅适用于主键连续且无删除的场景）
select * from orders where id > 1000000 order by id limit 10;
-- 方案 4：业务侧限制最大页数（如只允许翻到 100 页），引导用户用筛选条件| 方案 | 性能 | 限制 |
|---|---|---|
| 延迟关联 | 中（仍要扫索引 M+N 行，但不回表） | 无限制，可随机跳页，改造成本最低 |
| 游标分页 | 最优（O(N)） | 不能随机跳页，需要有序且唯一的游标键 |
| 主键区间 | 最优 | 主键必须连续，有删除就会错位 |
| 限制页数 | — | 需产品配合 |
面试加分表述：「我们导出功能原来用
limit offset翻到几十万页时单次要 8 秒，直接把从库 CPU 打满。改成(create_time, id)元组游标后稳定在 20ms 以内。这里有个容易踩的坑：一开始只用create_time > ?做游标，结果同一秒内有多条数据时会漏记录，必须把主键作为第二排序键一起放进游标，才能保证'不重不漏'。」
17. 大表 DDL：MDL、Online DDL 与 gh-ost
为什么"给大表加个字段"会让全站挂掉：
text
事务 A: begin; select * from t limit 1; -- 持有 MDL 读锁，但一直不 commit
事务 B: alter table t add column x int; -- 申请 MDL 写锁 → 被 A 阻塞
事务 C: select * from t where id = 1; -- 申请 MDL 读锁 → 排在 B 后面被阻塞
事务 D/E/F... -- 全部阻塞 → 连接池耗尽 → 全站不可用关键点：MDL 写锁的等待会阻塞后续所有读锁（公平队列，防止写饿死），所以一个长事务 + 一次 DDL 就能瞬间打死整个库。
安全做法：
- 改表前先查
information_schema.innodb_trx，确认没有长事务 - 用带超时的 DDL，抢不到锁就放弃，不要堵住后面的请求：sql
ALTER TABLE t ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE; -- MariaDB / 部分版本支持： SET lock_wait_timeout = 5; -- MySQL 用这个限制 MDL 等待 - 低峰期执行，并提前准备 kill 预案
Online DDL 的三种 ALGORITHM：
| ALGORITHM | 行为 | 是否阻塞 DML |
|---|---|---|
COPY | 建临时表 + 全量拷贝数据 + rename（5.6 前的唯一方式） | 全程阻塞写 |
INPLACE | 原表原地修改，只在开始/结束短暂持有 MDL 写锁 | 基本不阻塞（加字段、加索引） |
INSTANT | 只改数据字典，不动数据页（8.0.12+） | 秒级完成 |
8.0 的 INSTANT 支持：加列（只能加在最后、8.0.29 后支持任意位置）、改列默认值、重命名表、加/删虚拟列。不支持：改列类型、加主键、改字符集——这些仍需 INPLACE 或 COPY。
gh-ost / pt-online-schema-change：
text
pt-osc: 建影子表 → 建三个触发器同步增量 → 分批拷贝老数据 → rename
缺点：触发器带来写放大，与原表写入同事务，有锁风险
gh-ost: 建影子表 → 伪装成从库解析 binlog 同步增量 → 分批拷贝 → cut-over
优点：无触发器，可暂停/限流/动态调参，对主库压力更可控面试加分表述：「即使 8.0 有 INSTANT，我们线上给亿级表加字段仍然走 gh-ost，原因有两个：一是 INSTANT 加列有次数上限，超过后会退化成 INPLACE 全表重建，而这个退化是隐式的、不可预期的；二是 gh-ost 能随时暂停和限流，出问题可以立刻停下来，DDL 一旦开始就只能等或者 kill（kill 后还要回滚，更危险）。可控性比速度重要。」
18. 长事务的危害与 undo purge
长事务到底坏在哪（能说出四条才算答全）：
| 危害 | 机制 |
|---|---|
| undo 日志无法回收 | 只要有活跃事务的 ReadView 可能用到旧版本，purge 线程就不能删除 undo，ibdata / undo 表空间持续膨胀 |
| 快照读变慢 | 版本链越长，可见性判断要沿链回溯的次数越多，一条简单 select 也可能变慢 |
| 锁长期不释放 | 行锁、间隙锁、MDL 读锁全部持有到事务结束，阻塞别人 |
| 主从延迟 | 大事务提交时 binlog 一次性写入并传输，从库单事务重放期间延迟飙升 |
排查与治理：
sql
-- 1. 找出执行超过 60 秒的事务
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_rows_locked, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;
-- 2. 看版本链堆积程度（History list length）
SHOW ENGINE INNODB STATUS; -- TRANSACTIONS 段里的 History list length
-- 3. 兜底：限制单条语句最长执行时间（8.0，只对 SELECT 生效）
SET GLOBAL max_execution_time = 3000; -- 毫秒工程防御四件套：
- 关闭自动提交的隐患：确认应用层不是
autocommit=0却忘了 commit（框架常见坑） - 事务内禁止做 RPC、发消息、读文件等慢操作——这是最常见的长事务来源
- 大批量操作必须分批提交（每 500~1000 行一个事务）
- 监控告警：
History list length > 10 万或存在> 60s的事务时直接报警
面试加分表述：「我见过最典型的事故是在
@Transactional方法里调用了第三方支付接口，对方超时 30 秒，这个事务就持有行锁 30 秒，下游全部雪崩。所以我们现在的规范是：事务只包数据库操作，RPC 和消息发送必须挪到事务提交之后（用事务提交后的回调钩子），宁可引入最终一致性，也不要让事务变长。」
19. 字段类型选型：细节决定线上有没有事故
| 场景 | 推荐 | 原因 |
|---|---|---|
| 变长字符串 | varchar(n)，n 按业务上限给 | char(n) 定长会补空格、浪费空间 |
| 固定长度（MD5、手机号） | char(32) / char(11) | 定长无需存长度，且不会因更新变长导致行迁移 |
| 金额 | decimal(20,4) 或 bigint（存分） | 绝不能用 float/double，二进制浮点无法精确表示小数 |
| 时间 | datetime(3) 为主 | 范围大（1000~9999 年）、不受时区影响 |
| 需要随时区变化 | timestamp | 占 4 字节、带时区转换，但 2038 年溢出 |
| 布尔 | tinyint(1) unsigned | MySQL 无原生 boolean |
| 枚举状态 | tinyint + 应用层常量 | enum 改值要 DDL，顺序依赖容易出错 |
| 主键 | bigint unsigned auto_increment | int unsigned 上限 42 亿，大表有用尽风险 |
| 大文本 / JSON | 拆到单独的表或对象存储 | text/blob 行溢出，页外存储影响扫描效率；且 select * 会把它一起拉出来 |
两个必须知道的坑：
（1）自增主键用尽
text
int unsigned 上限 4294967295。到顶之后再插入会报主键冲突，
而且 InnoDB 不会报"自增用尽"，只会报 Duplicate entry。
如果表没有显式主键，InnoDB 会用 6 字节隐式 row_id（上限 2^48），
用尽后会从 0 重新开始并覆盖数据——这是静默的数据丢失，所以
"每张表必须有显式主键"不只是规范，是数据安全要求。（2）utf8 不是真的 UTF-8
MySQL 的 utf8 是 utf8mb3，最多 3 字节，存不了 emoji 和部分生僻字（插入会报错或被截断）。必须用 utf8mb4。排序规则 8.0 默认 utf8mb4_0900_ai_ci（不区分大小写与重音），若需要区分大小写用 utf8mb4_bin。
关联坑：字符集影响
varchar的索引长度上限。InnoDB 单列索引最长 3072 字节（DYNAMIC行格式），utf8mb4 下即varchar(768)；若行格式是COMPACT，上限只有 767 字节，varchar(255)刚好卡在边界——这是老库上"加索引报Specified key was too long"的根因。
20. 主从复制、延迟与读写分离的边界
复制链路：
text
主库 从库
┌──────────────┐ ┌────────────────────┐
│ 事务提交 │ │ IO 线程 │
│ 写 binlog │ ──── TCP ──────> │ 收 binlog→relay log│
│ dump 线程推送│ │ SQL 线程 / worker │
└──────────────┘ │ 回放 relay log │
└────────────────────┘三种复制模式：
| 模式 | 主库行为 | 数据安全 | 性能 |
|---|---|---|---|
| 异步（默认） | 写完 binlog 就返回 | 主库崩溃可能丢数据 | 最好 |
| 全同步 | 等所有从库 apply 完 | 最安全 | 最差，基本不用 |
| 半同步（semi-sync） | 等至少 N 个从库收到（不需 apply） | 折中，主流选择 | 可接受 |
延迟根因与对策：
| 根因 | 对策 |
|---|---|
| 从库单线程回放跟不上 | 开并行复制：binlog_transaction_dependency_tracking=WRITESET（8.0），按写集合并行 |
| 大事务 | 拆小事务；delete 百万行改分批 |
| 从库无索引（表结构不一致） | 保证主从表结构一致 |
| 从库承担大查询 / 备份 | 备份走专用从库 |
| DDL 在从库串行执行 | 用 gh-ost 分别在主从执行 |
sql
-- 观察延迟
SHOW SLAVE STATUS\G -- Seconds_Behind_Master（不准，主库空闲时会显示 0）
-- 更准：对比 performance_schema.replication_applier_status_by_worker
-- 的 LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP读写分离的三种"读己之写"方案：
| 方案 | 做法 | 代价 |
|---|---|---|
| 强制走主库 | 关键读（如下单后查订单）打标走主 | 主库压力上升，最常用 |
| 判断延迟后再读 | 查 Seconds_Behind_Master，有延迟就走主 | 指标不精确 |
| 等位点 / 等 GTID | SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid, 1) 超时则走主 | 精确但实现复杂 |
面试加分表述：「我们踩过的坑是下单成功后立刻跳转详情页，读从库读不到，用户以为下单失败又下一单。修复方案是在网关层对'写后 1 秒内的同用户读请求'强制路由主库——用的是 Redis 打一个 1 秒的用户级标记。比全局强制走主库更省主库资源，也比判断延迟更可靠。」
21. 分库分表的边界：什么时候该做、什么时候不该做
先做这些，再考虑分库分表：
text
1. 加索引 / 改 SQL ← 收益最大、成本最低
2. 加缓存（Redis） ← 挡掉 80% 读
3. 读写分离 ← 挡掉剩余读压力
4. 冷热分离 / 历史数据归档 ← 单表从 5 亿降到 2000 万，很多问题自然消失
5. 升级硬件 / 调大 Buffer Pool
6. 分区表（单机内按时间分区）
7. 分库分表 ← 最后手段阈值参考：单表 2000 万行 或 单表 20GB 是常见警戒线。但这不是硬标准——真正的判断依据是：B+ 树是否因为层高增加而导致 IO 次数上升，以及索引是否还能装进 Buffer Pool。
分片键选择（最关键的决策，错了就没法改）：
| 维度 | 说明 |
|---|---|
| 必须是高频查询条件 | 否则每次查询都要广播到所有分片 |
| 数据分布要均匀 | 按商家 ID 分片，头部商家会造成热点 |
| 尽量避免跨片事务 | 同一业务聚合根（如一个订单的所有子表）要落在同一片 |
典型解法：订单表用 user_id 分片（C 端查询主路径），同时维护一张 order_id → user_id 的路由表或把 user_id 编码进 order_id（基因法），解决"按订单号查"的问题。
分库分表后失去的能力：跨库 join、跨库事务（需要 TCC / Saga / 本地消息表）、全局唯一自增 ID（需要雪花算法）、跨片 count 与排序分页（需要应用层归并）、跨片 order by + limit 深分页（最难，常退化为"二次查询法"）。
面试加分表述：「我们当时 8000 万行的日志表，第一反应是分表，后来发现 90% 的查询只查最近 7 天。于是改成按月分区 + 定时归档到归档库，单个活跃分区只有 600 万行，问题直接消失，总共只改了几十行代码。这件事让我明白：分库分表是把技术复杂度转移给了业务代码，能不做就不做。」
22. 优化器为什么会选错索引：成本模型与统计信息
优化器不是"有索引就一定走索引"，而是挑一个它算出来最便宜的执行计划。成本由两部分构成（权重存在 mysql.server_cost / mysql.engine_cost 两张系统表里）：
| 成本项 | 含义 | 默认单价 |
|---|---|---|
| IO cost | 读取数据页 | io_block_read_cost = 1.0，已在 Buffer Pool 里的页 memory_block_read_cost = 0.25 |
| CPU cost | 取行、比较、排序、建临时表 | row_evaluate_cost = 0.1、disk_temptable_create_cost = 20 等 |
总成本 ≈
预估读页数 × IO 单价 + 预估行数 × CPU 单价
所以整件事只取决于一个变量：预估行数 rows。而 rows 是估出来的，不是数出来的：
text
① 等值查询 → 查索引统计信息里的 cardinality（不同值个数）
cardinality 来自"随机采样 N 个叶子页，数不同值，再乘总页数"
N = innodb_stats_persistent_sample_pages，默认 20
② 范围查询 → index dive：真的去 B+ 树上探一下区间两端，数出大致行数
但等值区间个数超过 eq_range_index_dive_limit（默认 200）
就放弃 dive，退回用①的统计信息 → 精度骤降
③ 统计信息何时更新 → innodb_stats_persistent=ON（默认）持久化到
mysql.innodb_table_stats / index_stats，
表行数变化超过 10% 才由后台线程重算四类典型"选错"成因
| 成因 | 机理 | 现场特征 |
|---|---|---|
| 统计信息过期 | 批量导入 / 大量 delete 后没触发重算，cardinality 严重失真 | EXPLAIN 的 rows 和真实行数差一个数量级 |
| 长事务撑住旧版本 | delete 的行因 undo 未 purge 仍在索引里，采样把旧版本也数进去，rows 偏大 | 刚清过数据反而变慢，innodb_trx 里有长事务 |
| 采样页不足 + 数据倾斜 | 20 页采样碰不到倾斜值，字段 99% 是同一个值却被估成高区分度 | 大表、枚举字段、status 字段 |
| 为了避免排序而换索引 | 走 idx_create_time 可以省掉 filesort，优化器认为"排序很贵"，结果扫描行数暴涨 | SQL 带 order by + limit，去掉 order by 就快 |
五种解法，按优先级从高到低
sql
-- ① 先确认是不是统计信息的问题（最常见，代价最低）
ANALYZE TABLE t_order; -- 重新采样，不锁表（只拿 MDL 读锁）
-- ② 大表调大采样页数（全局或表级）
ALTER TABLE t_order STATS_SAMPLE_PAGES = 200;
-- ③ 看优化器到底怎么算的——拿证据，不要猜
SET optimizer_trace = 'enabled=on';
SELECT /* 你的 SQL */ ...;
SELECT * FROM information_schema.optimizer_trace\G
-- 关注 rows_estimation / considered_execution_plans 里每个候选的 cost
-- ④ 改写 SQL 引导优化器（比 FORCE INDEX 更稳，不会因索引改名失效）
-- 例：order by create_time limit 10 导致走错索引
-- 改成 order by create_time, id limit 10 → 让优化器重新算成本
-- 例：把 limit 1000 适当放大/缩小，改变"提前终止"的成本预估
-- ⑤ 兜底：FORCE INDEX（有隐患，索引被删/改名后 SQL 直接报错）
SELECT * FROM t_order FORCE INDEX(idx_user_status) WHERE ...;
-- ⑥ 8.0 专属：给倾斜字段建直方图，不依赖索引也能估准
ANALYZE TABLE t_order UPDATE HISTOGRAM ON status WITH 16 BUCKETS;
ANALYZE TABLE t_order DROP HISTOGRAM ON status;索引该不该删？ 怀疑某个索引在误导优化器时，8.0 先 ALTER TABLE t ALTER INDEX idx INVISIBLE——优化器立刻无视它但索引仍在维护，观察一两天没问题再 DROP，出事秒级 VISIBLE 回滚。5.7 没有这个能力，只能先在从库验证。
面试加分表述：「我遇到过一次'删完历史数据反而变慢'。原因不是索引坏了，而是删除的行被一个跑了 40 分钟的报表事务撑着无法 purge，索引里还留着大量旧版本，采样算出的
rows偏大了十几倍，优化器就放弃了二级索引。我的处理顺序是：先optimizer_trace拿到两个候选计划的 cost 作为证据，再ANALYZE TABLE验证统计信息，最后杀掉长事务。FORCE INDEX我只在止血时用，因为它把索引名硬编码进了 SQL，后面改索引会直接报错。」
23. 刷脏页与"MySQL 偶尔抖一下"
现象：SQL 平时 1ms，偶尔某几秒内全部变成几百毫秒，没有慢 SQL、没有锁等待。这类抖动绝大多数是 flush 脏页 引起的。
WAL 让写入变快的前提是"先写日志、后刷数据页"，但脏页总要还的，InnoDB 在四种时机集中还债：
| 刷脏时机 | 触发条件 | 对业务的影响 |
|---|---|---|
| redo log 写满 | 写入位置追上 checkpoint | 最严重：所有更新全部阻塞，直到推进 checkpoint |
| 脏页比例过高 | 超过 innodb_max_dirty_pages_pct（默认 90） | 刷脏速度被拉满，抢占正常 IO |
| Buffer Pool 淘汰脏页 | LRU 尾部要淘汰的页是脏页，必须先刷盘 | 单条查询偶发变慢（命中就倒霉） |
| 系统空闲 / 正常关库 | 后台慢慢刷 | 无影响 |
关键参数：innodb_io_capacity。它告诉 InnoDB"你的磁盘每秒能干多少 IO"，默认 200——这是机械盘的量级。SSD 上没改这个值，InnoDB 就会一直以为自己很弱，刷脏刷得特别慢，于是脏页越积越多，最终被迫进入"全力刷脏"状态，业务就抖了。
sql
-- 诊断：脏页比例是不是一直很高
SELECT (SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty')
/ (SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') AS dirty_ratio;
-- 配置建议
SET GLOBAL innodb_io_capacity = 2000; -- SSD 按 fio 实测 IOPS 的 70% 给
SET GLOBAL innodb_io_capacity_max = 4000; -- 紧急刷脏的上限
SET GLOBAL innodb_max_dirty_pages_pct = 75; -- 别等到 90% 才着急
SET GLOBAL innodb_flush_neighbors = 0; -- SSD 必须关：别再"连坐"刷邻居页innodb_flush_neighbors = 1 是机械盘时代的优化——刷一个脏页时顺手把相邻的脏页一起刷，减少寻道。SSD 上随机 IO 不贵，这个"连坐"只会白白放大 IO，8.0 已默认为 0，5.7 需要手动关。
另一类抖动来源：redo log 太小。innodb_log_file_size × innodb_log_files_in_group 如果只有几百 MB，高写入下会频繁写满并强制推进 checkpoint，表现为"每隔十几秒卡一下"。生产建议至少 1~4 GB（8.0.30 后用 innodb_redo_log_capacity 一个参数搞定，且支持在线调整）。
面试加分表述：「我排查过一次'每隔几十秒抖一下'。慢日志里没有慢 SQL，
innodb_trx也没有长事务，最后是看Innodb_buffer_pool_pages_dirty发现脏页比例长期贴着 90%。机器是 SSD，但innodb_io_capacity还是默认的 200，InnoDB 一直在'龟速还债'，攒到阈值就集中爆发。把 io_capacity 调到 2000、flush_neighbors关掉、redo 从 512M 扩到 2G，抖动彻底消失。这件事的教训是：没有慢 SQL 的变慢，要去看引擎的后台工作，而不是继续翻 SQL。」
24. delete 完磁盘没变小：数据空洞与表空间回收
delete 只是把记录标记为可复用（等 purge 真正清理），页和文件都不会还给操作系统，所以 .ibd 文件大小不变。这些可复用但没数据的位置就是空洞：
text
页内空洞：delete 掉页中间的记录 → 该位置可复用，但页仍占 16KB
页间空洞：整页记录都被删 → 整页标记为可复用，仍在表空间里
插入空洞：随机主键插入导致页分裂 → 两个半满页，页利用率 50%空洞的代价不是磁盘，而是IO 放大：同样 100 万行，空洞多意味着要读更多页，Buffer Pool 命中率下降。
回收手段：重建表
sql
-- 推荐写法（本质就是重建表，Online DDL，5.6+ 支持）
ALTER TABLE t_order ENGINE = InnoDB;
-- 等价于 OPTIMIZE TABLE t_order;（= recreate + analyze）
-- 原理：按主键顺序把数据重新写进一个临时文件，紧凑排列，再替换原文件
-- Online 模式下期间的增删改记录在 row log 里，最后回放必须知道的三个坑：
| 坑 | 说明 |
|---|---|
| 需要额外磁盘空间 | 临时文件大小接近原表，磁盘余量不足一半时会失败 |
| 首尾要拿 MDL 写锁 | 虽然是 Online，但开始和结束的瞬间要升级为写锁，有长事务就会卡住整张表 |
| 大表很慢且吃 IO | 千万级以上建议用 gh-ost / pt-online-schema-change，可限速、可暂停 |
| 必须是独立表空间 | innodb_file_per_table = ON（5.6.6 起默认）才有意义；共享表空间重建也不会把空间还给 OS |
什么时候才值得做？用 information_schema.TABLES 估算碎片率，超过 30% 且表确实大才做，日常不需要：
sql
SELECT table_name,
ROUND(data_length/1024/1024) AS data_mb,
ROUND(data_free/1024/1024) AS free_mb,
ROUND(data_free/(data_length+index_length+1)*100, 1) AS frag_pct
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY free_mb DESC;面试加分表述：「有人问我'删了一半数据为什么磁盘没释放'，我会先解释 InnoDB 的 delete 只是标记可复用，然后强调真正该关心的是 IO 放大而不是磁盘。回收方式是
ALTER TABLE ... ENGINE=InnoDB重建表，但大表我一定走 gh-ost，因为原生重建在首尾要抢 MDL 写锁，线上有长事务时会把整张表堵死——这跟加字段是同一个风险点。」
25. 连接层的坑：短连接风暴、max_connections 与 kill 不生效
很多"MySQL 挂了"其实是连接层问题，和 SQL 一点关系都没有。
短连接风暴：业务用短连接，QPS 一上来每秒成千上万次建连。MySQL 的连接建立成本很高（TCP + 认证 + 线程创建），连接数瞬间顶到 max_connections 就开始报 Too many connections，而此时数据库的 CPU 可能还很闲。
| 处理手段 | 说明 | 风险 |
|---|---|---|
| 应用层连接池 | 根治方案，池大小一般 CPU 核数× 2 + 磁盘数 就够 | 池配太大反而加剧争抢 |
调大 max_connections | 止血用 | 每个连接都要内存和调度，调太大会让整体更慢 |
| 主动踢掉空闲连接 | kill connection 掉 Sleep 且空闲久的 | 可能踢掉事务中的连接，导致回滚 |
调小 wait_timeout | 让空闲连接自动断开 | 连接池需要配探活，否则拿到死连接 |
| 跳过权限验证 | --skip-grant-tables 省掉认证开销 | 极度危险，等于裸奔，不要用 |
长连接的内存问题：连接执行过程中临时用到的内存挂在连接对象上，只在断开时释放。有大查询（大排序、大临时表）的长连接会让 mysqld 内存持续上涨，甚至被 OOM Killer 干掉。对策：定期重连，或 5.7+ 在大查询后执行 mysql_reset_connection 重置连接状态（不重连、不丢认证）。
kill 为什么不生效：
sql
SHOW PROCESSLIST; -- 或 select * from performance_schema.threads
KILL QUERY 1234; -- 只终止当前语句，连接保留（事务不回滚！）
KILL 1234; -- = KILL CONNECTION，断开连接并回滚事务KILL 不是"立即杀死"，而是设置一个标志位 + 把线程从等待队列里唤醒，线程要执行到下一个"埋点"才会检查这个标志并开始退出。所以两种情况你会看到 Killed 状态挂很久：
- 回滚量巨大：比如更新了 1000 万行，
KILL后要花同样甚至更长的时间回滚 undo，这时候最不该做的就是重启——重启后崩溃恢复还要继续回滚，而且恢复期间不可用。 - 线程卡在不检查标志位的地方：如等 IO、等 MDL、
innodb_thread_concurrency满了在排队。
面试加分表述：「
KILL QUERY和KILL CONNECTION的区别我吃过亏：只 kill 掉慢查询语句，连接和事务都还在，持有的行锁一个也没放，后面的阻塞一点没缓解。要释放锁必须KILL CONNECTION让事务回滚。另外我会提醒一句：看到Killed状态半天不消失，先判断是不是在回滚大事务，这种时候耐心等待比重启安全得多。」
26. MySQL 8.0 关键新特性（面试加分项）
| 特性 | 说明 | 实战价值 |
|---|---|---|
| 窗口函数 | ROW_NUMBER() / RANK() / LAG() / SUM() OVER() | 分组取 TopN 不用再写自连接 |
| CTE / 递归 CTE | WITH RECURSIVE | 查询树形结构（部门、评论）一条 SQL 搞定 |
| 原子 DDL | DDL 要么全成功要么全失败 | 不会再出现改表改一半的脏状态 |
| INSTANT DDL | 加列只改数据字典 | 秒级加字段 |
| 降序索引 | index(a asc, b desc) | 消除混合方向排序的 filesort |
| 函数索引 | index((UPPER(name))) | 让 where upper(name)=? 也能走索引 |
| 不可见索引 | alter index idx invisible | 删索引前先隐藏观察，有问题秒级回滚 |
| hash join | 无索引 join 不再退化为 BNL | 分析型查询提速数倍 |
| 全局数据字典 | 元数据存进 InnoDB 表 | 彻底解决 .frm 文件不一致问题 |
SELECT ... FOR UPDATE SKIP LOCKED / NOWAIT | 跳过/不等待已锁行 | 做队列抢单、任务分发的利器 |
| 资源组 | 给不同语句分配 CPU | 隔离分析查询对线上的影响 |
| 角色（ROLE） | 权限打包授予 | 权限管理简化 |
sql
-- 窗口函数：取每个用户最近 3 笔订单（8.0 之前要写复杂的自连接）
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) rn
FROM orders
) t WHERE rn <= 3;
-- SKIP LOCKED：多个 worker 并发抢任务，互不阻塞且不重复
BEGIN;
SELECT id FROM task WHERE status = 0 ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;
UPDATE task SET status = 1 WHERE id IN (...);
COMMIT;
-- 不可见索引：怀疑索引没用，先隐藏观察一周再删
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;面试加分表述：「
SKIP LOCKED是我在 8.0 里受益最大的特性。之前多个消费者抢任务表，用for update会互相阻塞，只能靠update ... set owner=? where status=0 limit 1再回查，既丑又有竞争。换成FOR UPDATE SKIP LOCKED之后，N 个 worker 真正做到了无锁竞争的批量领取，吞吐线性提升。」
❓ 高频面试题【基础简答 + 连环追问链 + 精简口述答案 + 项目关联话术】
题 1：为什么 InnoDB 用 B+ 树做索引？聚簇索引、回表、覆盖索引分别是什么？
精简口述答案（90 秒版）
「B+ 树的核心优势是矮、宽、叶子有序且成链。非叶子节点只存键和指针不存数据，所以一页 16KB 能放上千个键，三层就能索引两千万行，一次查询最多 3 次磁盘 IO；叶子节点通过双向链表串起来，范围查询和排序可以顺序扫描而不用回溯树。相比之下二叉树太高、B 树的非叶子节点存数据导致扇出小、Hash 索引不支持范围和排序。
InnoDB 的表数据本身就按主键组织在 B+ 树的叶子节点里，这棵树叫聚簇索引；二级索引的叶子节点存的是主键值，所以通过二级索引查非索引字段时，要拿主键再去聚簇索引查一次，这个过程叫回表。如果一条查询需要的所有字段都在二级索引里，就不用回表了，这叫覆盖索引，是最常用的优化手段。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 为什么不用 Hash 索引？ | 不支持范围查询、不支持排序、不支持最左前缀；等值查询确实 O(1)，所以 InnoDB 内部的自适应哈希索引 AHI 就是针对热点等值查询自动建的 |
| 为什么二级索引叶子存主键而不是行地址？ | 行地址会随页分裂、行迁移变化，存主键则只需维护聚簇索引，二级索引不用动 |
| 那为什么主键要尽量短？ | 每个二级索引的叶子都要存一份主键，主键越长所有二级索引越大 |
| B+ 树三层能存多少数据？ | 页 16KB，主键 bigint 8B + 指针 6B = 14B，一个非叶子页约 1170 个指针；叶子页按行 1KB 算存 16 行。三层 = 1170 × 1170 × 16 ≈ 2190 万行 |
| 为什么说"IO 最多 3 次"还不够准确？ | 根节点和非叶子节点通常常驻 Buffer Pool，实际磁盘 IO 往往只有 1 次甚至 0 次 |
| 索引越多越好吗？ | 写放大、空间占用、优化器选择成本上升、DDL 变慢，单表建议不超过 5 个 |
✅ 项目关联话术
「我优化过一个订单列表接口，原来是 select * from orders where user_id = ? order by create_time desc limit 20，走 idx_user_id 要回表 20 次，P99 在 300ms。我把索引改成 idx(user_id, create_time) 并且只 select 列表页真正需要的 6 个字段，让它变成覆盖索引，Extra 从 Using filesort 变成 Using index，P99 降到 15ms。关键认知是：覆盖索引不是'加索引'，而是'索引和 select 字段一起设计'。」
题 2：讲一下 MVCC，RC 和 RR 的区别在哪？
精简口述答案（90 秒版）
「MVCC 是多版本并发控制，目的是让读不加锁，读写互不阻塞。实现上靠三样东西：
- 每行有三个隐藏字段：
DB_TRX_ID（最后修改它的事务 ID）、DB_ROLL_PTR（指向 undo log 的回滚指针）、DB_ROW_ID； - 每次修改都把旧值写进 undo log，通过回滚指针串成版本链；
- 快照读时生成一个 ReadView，包含
m_ids（生成时还活跃的事务 ID 列表）、min_trx_id、max_trx_id、creator_trx_id。
可见性判断是：沿版本链从新往旧找，某版本的 trx_id 小于 min_trx_id 就可见（已提交）；大于等于 max_trx_id 不可见（未来的事务）；在中间则看是否在 m_ids 里，在就不可见（当时还没提交），不在就可见。
RC 和 RR 的唯一区别是 ReadView 的生成时机：RC 是每条 select 都重新生成一个，所以能看到别的事务新提交的数据，出现不可重复读；RR 是事务内第一次 select 时生成，之后一直复用，所以整个事务看到的是同一个快照。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 快照读和当前读的区别？ | 普通 select 是快照读走 MVCC；select ... for update / lock in share mode、update、delete、insert 是当前读，读最新版本并加锁 |
| 为什么 update 必须是当前读？ | 如果基于旧快照改，会丢失其他事务已提交的修改，产生更新丢失 |
| RR 下真的没有幻读吗？ | 快照读由 MVCC 解决，当前读由 Next-Key Lock 解决；但事务内先快照读再当前读、或自己 insert 后再查，仍能观察到幻读 |
| undo log 什么时候删除？ | 由 purge 线程回收，但必须等到没有任何 ReadView 可能用到它，所以长事务会让 undo 疯涨 |
| MVCC 对 insert 的处理？ | insert 的 undo 是 insert undo，事务提交后可立即删除（没有旧版本需要被别人看到） |
| 两个事务同时 update 同一行会怎样？ | 后者在当前读阶段被行锁阻塞，等前者提交后再基于最新版本执行 |
✅ 项目关联话术
「我排查过一个对账不平的问题：统计任务在一个长事务里先 select sum(amount)，中间做了几分钟的业务处理，再 select count(*)，两次结果对不上业务预期。根因是同事以为 RR 下两次读一定一致——确实一致，但他中间穿插了一次 select ... for update 当前读，拿到了最新数据，跟前面的快照对不上。修复方式是把整个统计改成单条 SQL 一次读完。这让我理解了快照读和当前读混用是 RR 下最隐蔽的坑。」
题 3：MySQL 有哪些锁？间隙锁解决什么问题？死锁怎么排查？
精简口述答案（90 秒版）
「按粒度分三层：全局锁（FTWRL，全库备份用）、表级锁（表锁、元数据锁 MDL、意向锁 IS/IX）、行级锁（记录锁 Record Lock、间隙锁 Gap Lock、临键锁 Next-Key Lock、插入意向锁）。
行锁实际是加在索引上的，所以如果 where 条件没走索引，会退化成给所有记录加锁，效果等同于锁表。
间隙锁锁的是记录之间的"空隙"，本身不阻止读，只阻止往空隙里插入，目的就是解决 RR 下当前读的幻读。Next-Key Lock = 记录锁 + 前面的间隙锁，是左开右闭区间，是 RR 下的默认加锁单位。
间隙锁之间互相兼容——两个事务可以同时持有同一个间隙的间隙锁，这是很多死锁的根源：双方都持有间隙锁，然后都要插入对方的间隙，插入意向锁与间隙锁冲突，互相等待就死锁了。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 意向锁有什么用？ | 加表锁时不用逐行检查有没有行锁，只需看表上有没有 IX/IS，把 O(n) 变成 O(1) |
| 加锁的基本规则？ | 两原则：①加锁单位是 next-key lock，左开右闭；②查找过程中访问到的对象才加锁。两优化：①等值查询唯一索引命中时退化为行锁；②等值查询遍历到不满足条件的最后一个值时，next-key 退化为间隙锁 |
| 死锁怎么处理？ | innodb_deadlock_detect=ON 主动检测并回滚代价小的事务；也可靠 innodb_lock_wait_timeout（默认 50s，太长）超时 |
| 死锁检测有什么代价？ | 每个新来的锁等待都要遍历等待图，热点行上 N 个并发是 O(N²) 开销，可能把 CPU 打满 |
| 怎么降低死锁概率？ | 固定加锁顺序、缩短事务、热点行拆分成多行分散、改用 RC + binlog_format=ROW 去掉间隙锁 |
| 怎么看死锁现场？ | SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段；或开 innodb_print_all_deadlocks=ON 把死锁都写进错误日志 |
✅ 项目关联话术
「我们线上出过一个高频死锁：两个并发请求都先 delete from t where biz_no = ?（不存在，加了间隙锁）再 insert，结果互相等对方的间隙锁，必死。因为间隙锁之间是兼容的，所以 delete 阶段不会冲突，冲突全部暴露在 insert 阶段。最终解法是去掉 delete + insert 的写法，改成 insert ... on duplicate key update 配合唯一索引，一条语句原子完成，死锁归零。这件事让我记住了：先删后插的幂等写法是死锁高发区。」
题 4：索引失效的场景有哪些？最左前缀到底是什么？
精简口述答案（90 秒版）
「最左前缀的本质是：联合索引 idx(a,b,c) 的排序规则是先按 a 排，a 相同按 b 排，b 相同按 c 排。所以只有 a 确定了，b 才是有序的；b 确定了，c 才是有序的。索引能用于快速定位的前提是数据有序，一旦某个字段用了范围查询，它后面的字段就失去了全局有序性，不能再参与索引定位。
索引失效的典型场景：
- 不满足最左前缀（跳过 a 直接查 b）
- 对索引列做运算或用函数：
where id + 1 = 10、where date(create_time) = '2026-01-01' - 隐式类型转换：字符串列传数字会失效（相当于对列做 CAST）
- 前导模糊查询：
like '%abc' or连接的条件中有一侧无索引!=、not in、is not null导致优化器判断扫描量过大而放弃索引- 优化器基于成本主动放弃（回表太多时宁愿全表扫描）
注意第 6、7 条本质都是成本选择，不是"语法上用不了"，所以在数据分布变化后行为可能变化。」
连环追问链
| 追问 | 答题要点 |
|---|---|
idx(a,b,c) 查 a=1 and c=3 能用到几个字段？ | key_len 只算 a；但 c 会通过 ICP 索引条件下推在引擎层过滤，减少回表次数，Extra 显示 Using index condition |
| 隐式转换为什么是不对称的？ | varchar 列传数字：MySQL 把列转成数字比较，等于对列做函数 → 失效。int 列传字符串：转换的是参数不是列 → 索引仍有效 |
like 'abc%' 为什么有效？ | 前缀确定，等于范围扫描 ['abc', 'abd')，索引有序性可用 |
怎么让 date(create_time)=? 走索引？ | 改写成范围 create_time >= '2026-01-01' and create_time < '2026-01-02'；或 8.0 建函数索引 |
为什么 select * 更容易导致索引失效？ | 需要的字段不在索引里就必须回表，回表行数多时优化器判定全表扫描更便宜 |
| 明明有索引优化器不走，怎么办？ | 先 ANALYZE TABLE 修正统计信息；再调大采样页数；最后才用 FORCE INDEX 或优化器提示 |
✅ 项目关联话术
「我们有张用户表 phone 是 varchar，前端传过来的参数在某个新接口里被当成 Long 处理，查询变成 where phone = 13800138000，索引直接失效，全表扫描 800 万行，接口 5 秒。排查时用 EXPLAIN 看到 key=NULL 一眼定位。修完之后我在团队加了一条 CR 检查项：字符串类型的业务编号（手机号、订单号、身份证号）在 DTO 里必须是 String，从源头避免隐式转换。」
题 5：redo log、undo log、binlog 的区别？什么是两阶段提交？
精简口述答案（90 秒版）
「三者定位完全不同：
- redo log：InnoDB 引擎层的物理日志，记录"在某个数据页的某个偏移做了什么修改"，循环写、固定大小，作用是崩溃恢复保证持久性 D；它是 WAL 的核心——先写日志再刷脏页，把随机写变成顺序写。
- undo log：InnoDB 引擎层的逻辑日志，记录反向操作，作用是事务回滚保证原子性 A，同时为 MVCC 提供版本链。
- binlog：Server 层的逻辑日志，记录 SQL 或行变更，追加写不覆盖，作用是主从复制和数据归档恢复。
两阶段提交是为了解决 redo log 和 binlog 的一致性问题：
- 写 redo log，状态标记为
prepare - 写 binlog 并持久化
- 提交事务，redo log 状态改为
commit
崩溃恢复时：redo 处于 commit 就提交；处于 prepare 则去看 binlog 是否完整，完整就提交，不完整就回滚。这样保证了 redo 和 binlog 的内容始终一致，主从数据不会分叉。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 如果先写 binlog 再写 redo 会怎样？ | binlog 写完崩溃，主库没改成功但从库会执行 → 主从不一致（从库多数据） |
| 如果先写 redo 再写 binlog 且不做两阶段？ | redo 写完崩溃，主库恢复后有数据但 binlog 没记录 → 从库少数据 |
| 怎么判断 binlog 是否完整？ | statement 格式以 COMMIT 结尾、row 格式以 XID event 结尾，XID 同时写进 redo 用于关联 |
| "双 1" 配置是什么？ | innodb_flush_log_at_trx_commit=1（每次提交 redo 刷盘）+ sync_binlog=1（每次提交 binlog 刷盘），保证不丢数据，代价是每事务两次 fsync |
| 两次 fsync 性能不行怎么办？ | 组提交（group commit）：多个事务的 fsync 合并成一次；可调 binlog_group_commit_sync_delay 人为延迟来攒更多事务 |
| binlog 三种格式区别？ | statement 省空间但有主从不一致风险（如 now()、limit）；row 记录前后镜像最安全但量大；mixed 自动切换。线上统一用 row |
✅ 项目关联话术
「我们做过一次数据恢复：有同事误执行了 update 漏了 where 条件，更新了全表 30 万行。因为 binlog_format=ROW 且 binlog_row_image=FULL，binlog 里完整记录了每行的前后镜像，我们用 mysqlbinlog 解析出 before image 反向生成了 30 万条 update 语句，20 分钟恢复完毕。这次之后我彻底理解了为什么 row 格式虽然占空间但必须用，以及为什么 binlog 保留天数不能设太短。」
题 6：给你一条慢 SQL，你的优化思路是什么？
精简口述答案（90 秒版）
「我的流程是四步，先定位再优化，不会一上来就加索引：
第一步：量化现状。拿到执行计划（EXPLAIN / EXPLAIN ANALYZE），重点看 type、key、rows、Extra 四列，确认是全表扫描、filesort 还是临时表；同时看 Rows_examined / Rows_sent 比值，判断过滤效率。
第二步：分层归因。慢的原因可能在 SQL 层（索引缺失/失效）、数据层（单表过大、数据倾斜）、事务层（锁等待、长事务导致版本链过长）、实例层（Buffer Pool 不足、IO 打满）、架构层（没缓存、没读写分离）。先用 innodb_trx 和 data_lock_waits 排除锁的因素，因为锁等待不计入慢日志会漏报。
第三步：对症优化。索引层面——补联合索引、做成覆盖索引、调整字段顺序消除 filesort；SQL 层面——去掉 select *、改写子查询为 join、深分页用游标、拆大 SQL 为多条小 SQL；数据层面——归档冷数据。
第四步：验证与防回归。对比优化前后的 rows 和真实耗时，上线后观察慢日志是否消失，并把这条 SQL 加进回归用例。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 优化顺序为什么是这个？ | 按"收益/成本"排序：改 SQL 和索引成本最低收益最高，分库分表成本最高放最后 |
Using temporary 怎么消除？ | group by 字段加索引让其天然有序；或 group by null 去掉隐式排序；或减少参与分组的数据量 |
| join 慢怎么优化？ | 被驱动表 join 字段必须有索引（BNL→NLJ）；小表（过滤后）驱动大表；8.0 可依赖 hash join |
| 子查询慢怎么办？ | in 子查询在 5.6 后会被物化或转半连接，但 not in + 可空列有坑；通常改写成 join 或 exists 更可控 |
| 加了索引反而更慢，为什么？ | 回表过多；或索引区分度低导致扫描量大却仍要回表；或写入变慢拖累整体 |
| 优化完怎么证明有效？ | 压测对比 P99、看 events_statements_summary_by_digest 的 avg_timer_wait 变化，而不是只看一次执行时间 |
✅ 项目关联话术
「我接手过一个报表接口 12 秒超时。EXPLAIN 显示两个 ALL + Using temporary + Using filesort。我没有直接加索引，而是先看了业务：这个报表只需要最近 30 天数据，但 SQL 没有时间过滤，扫了三年的数据。加上时间条件后扫描行数从 4000 万降到 80 万，再补一个 idx(create_time, status) 覆盖索引，最终 400ms。最大的收益来自读懂业务减少数据量，而不是技术优化本身，这是我做 SQL 优化最大的体会。」
题 7：limit 1000000, 10 为什么慢？怎么优化？
精简口述答案（90 秒版）
「因为 limit M, N 的语义是'取出 M+N 行，然后丢掉前 M 行'，MySQL 没法直接跳到第 M 行——B+ 树的叶子链表只能顺序走。所以 offset 100 万就要真实扫描 100 万零 10 行；如果走的是二级索引，还要回表 100 万次，这才是最致命的开销。
优化方案按优先级：
- 游标分页（书签分页）：记住上一页最后一条的排序键，
where (create_time, id) > (?, ?) limit 10，复杂度降到 O(N)，是最优解。注意必须把主键作为第二排序键，否则排序键重复时会漏数据或重复。 - 延迟关联：
select * from t join (select id from t order by create_time limit 1000000,10) x on t.id = x.id。仍要扫 100 万行索引，但只回表 10 次，通常能快 5~10 倍，而且不改变接口语义（还能随机跳页），改造成本最低。 - 主键区间：
where id > ? limit 10，前提是主键连续无空洞。 - 产品侧限制：只允许翻到第 N 页，引导用户用筛选条件缩小范围。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 游标分页的缺点？ | 不能随机跳页，只能上一页/下一页；且游标键必须有序且能构造唯一性 |
| 为什么游标要带主键？ | create_time 可能重复，只用 > 会跳过同值记录，用 >= 会重复。元组比较 (create_time, id) > (?, ?) 才能不重不漏 |
| 延迟关联为什么快？ | 子查询只扫索引不回表（覆盖索引），回表次数从 M+N 降到 N |
| 深分页在分库分表下更麻烦？ | 每个分片都要取 M+N 行再归并，offset 很大时内存爆掉，常用"二次查询法"优化 |
| 导出全量数据怎么做？ | 不要用分页，用主键游标批量拉取（where id > last_id order by id limit 1000），或走数仓 |
count(*) 配合深分页的开销？ | 每次翻页都 count(*) 是双倍开销，应该只在第一页算一次并缓存，或干脆不显示总数 |
✅ 项目关联话术
「我们运营后台的订单导出功能原来用 limit offset，翻到 50 万条时单次查询 8 秒，直接把从库 CPU 打满影响了其他报表。我改成 (create_time, id) 元组游标后稳定在 20ms。踩的坑是第一版只用了 create_time > ?，结果同一秒内有十几条订单时会漏掉，对账少了几百条。这件事让我记住：游标分页的排序键必须构造出全局唯一的有序元组。」
题 8：隔离级别有哪几种？RR 真的解决幻读了吗？
精简口述答案（90 秒版）
「四种隔离级别和对应的问题：
| 级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 RU | 可能 | 可能 | 可能 |
| 读已提交 RC | 否 | 可能 | 可能 |
| 可重复读 RR | 否 | 否 | InnoDB 已基本解决 |
| 串行化 Serializable | 否 | 否 | 否 |
InnoDB 默认是 RR，这点和 Oracle、PostgreSQL、SQL Server 默认的 RC 不同。历史原因是早期 MySQL 只有 statement 格式的 binlog，RC 下 statement 复制会导致主从不一致，所以必须用 RR 加间隙锁来保证。
RR 下解决幻读靠两套机制配合：快照读靠 MVCC（整个事务用同一个 ReadView），当前读靠 Next-Key Lock（锁住记录和间隙，阻止别人插入）。
但严格说 RR 下仍能'观察到'幻读：比如事务内先快照读再 select for update（当前读看到新数据）；或者自己 insert 一行后再查（自己的修改总是可见）；或者快照读之后执行 update 命中了别人新插入的行。所以准确说法是：RR 解决了同一种读方式下的幻读，混用快照读和当前读时仍会暴露。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 不可重复读和幻读的区别？ | 不可重复读针对已有行的内容变了（update/delete）；幻读针对行数变了（insert）。口诀：一个管"变"，一个管"多" |
| 很多大厂为什么把隔离级别改成 RC？ | 没有间隙锁 → 死锁大幅减少、并发更高；且 row 格式 binlog 下主从安全；代价是要在业务层处理一致性 |
| RC 下怎么避免更新丢失？ | 用乐观锁版本号，或 update ... where status = 旧值 做 CAS |
| Serializable 怎么实现的？ | 所有普通 select 隐式加共享锁，读写互斥，并发极低 |
| 隔离级别是在哪一层实现的？ | 引擎层（InnoDB），所以切换引擎行为会变；MyISAM 根本没有事务 |
| 怎么查看和修改隔离级别？ | select @@transaction_isolation; / set session transaction isolation level read committed; |
✅ 项目关联话术
「我们一个高并发的库存服务最早用默认 RR，压测时死锁率超过 1%，日志里全是 gap lock 相关的死锁。排查后发现业务上并不需要'可重复读'——每次读都希望拿到最新库存。于是切成 RC，死锁率直接降到 0.01% 以下，同时把更新改成 update stock set num = num - 1 where id = ? and num >= 1 靠行锁本身保证正确性。这让我理解：隔离级别是可以按业务特性权衡的工程选择，不是必须用默认值。」
题 9：线上亿级大表要加一个字段，你怎么做？
精简口述答案（90 秒版）
「核心风险不是慢，而是 MDL 元数据锁引发的全站雪崩。只要有一个长事务持有 MDL 读锁，DDL 申请 MDL 写锁就会被阻塞，而 MDL 写锁的等待会阻塞它之后所有的读请求（为防写饿死采用公平队列），几秒钟就能把连接池打满。
所以我的操作顺序是：
- 改前检查：查
information_schema.innodb_trx确认没有运行中的长事务，必要时先 kill - 设置锁等待超时：
set lock_wait_timeout = 5，抢不到锁就放弃，不要堵住队列 - 评估能否用 INSTANT：8.0.12+ 加列到末尾只改数据字典，秒级完成，
ALGORITHM=INSTANT - 否则用工具：
gh-ost优先于pt-osc，因为前者用 binlog 同步增量、无触发器、可随时暂停限流 - 低峰期执行 + 准备 kill 预案 + 双写兼容：代码先发布成"不依赖新字段也能跑"的版本，DDL 和发布解耦
另外一定要先在和线上同规格的预发库上演练，拿到真实耗时。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| INSTANT 有什么限制？ | 只能加列（8.0.29 前只能加在末尾）；改类型、加主键、改字符集不支持；加列次数有上限，超过会隐式退化成全表重建 |
| gh-ost 和 pt-osc 的区别？ | pt-osc 用三个触发器同步增量（写放大、与原表同事务）；gh-ost 伪装从库解析 binlog（无触发器、可暂停限流、可控性强） |
| gh-ost 的 cut-over 阶段会锁表吗？ | 会有一个极短的锁窗口做 rename 原子切换，设计上控制在毫秒级 |
| 为什么 MDL 写锁会阻塞后面的读？ | 公平队列避免写饿死，一旦写锁在排队，后续读锁必须排在其后 |
| 如果 DDL 已经卡住了怎么救？ | 先 show processlist 找到持锁的长事务并 kill，不要先 kill DDL（DDL 回滚可能更久） |
| 加字段要不要默认值？ | 8.0 加带默认值的列仍可 INSTANT（只记录在数据字典）；5.7 加默认值会触发全表重建 |
✅ 项目关联话术
「我们有次给 3 亿行的流水表加字段，走的是 gh-ost，跑了 4 个小时，中途因为主库 QPS 上涨自动触发了限流暂停，等低峰继续，全程业务无感知。为什么不用 8.0 的 INSTANT？因为 INSTANT 的加列次数上限一旦超过会静默退化成全表重建，这个退化是不可预期的；而 gh-ost 可以随时暂停、限流、观察，出问题能停下来比跑得快更重要。」
题 10：高并发下如何设计库存扣减，保证不超卖？
精简口述答案（90 秒版）
「不超卖的本质是把'检查'和'扣减'变成一个原子操作。最常见的错误写法是先 select num 判断够不够，再 update num = num - 1，这中间有竞态窗口，必然超卖。
数据库层面有三种正确写法：
- 原子更新（推荐）：
update stock set num = num - 1 where sku_id = ? and num >= 1，靠行锁天然串行化，通过affectedRows == 0判断扣减失败。一条 SQL 搞定，性能最好。 - 悲观锁：
select ... for update锁住后再判断再更新，适合扣减逻辑复杂（要读多个字段算规则）的场景，代价是锁持有时间长。 - 乐观锁：带版本号
update ... set num = num - 1, version = version + 1 where id = ? and version = ?，失败重试。适合冲突不激烈的场景，冲突高时重试风暴反而更差。
但单行行锁在秒杀场景下会成为瓶颈（同一行串行，QPS 上限几千）。所以高并发要在数据库之上加三层：Redis 预扣减挡住绝大部分无效流量 → MQ 削峰串行化 → 数据库做最终一致的兜底校验，并且用订单的唯一索引保证幂等。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 为什么不能先查后改？ | 查和改之间其他事务可能已经扣减，经典的 check-then-act 竞态 |
| 单行成为热点怎么办？ | 库存分桶：把 100 库存拆成 10 行各 10 个，随机打到一个桶，扣减失败再换桶，把一行的锁竞争分散成十行 |
| Redis 预扣减怎么保证原子？ | 用 Lua 脚本把判断和扣减放在一次执行里；或用 decrby 后判断结果小于 0 再补回 |
| Redis 扣了但数据库失败怎么办？ | 用本地消息表 / MQ 重试保证最终一致；Redis 作为"限流器"而非唯一真相源，数据库仍是最终账本 |
| 怎么保证接口幂等？ | 请求带唯一业务号，扣减流水表对该业务号建唯一索引，重复请求插入冲突直接返回成功 |
用 8.0 的 SKIP LOCKED 能做什么？ | 多 worker 并发领取待处理订单任务时互不阻塞、不重复，比 for update 阻塞式抢占好得多 |
✅ 项目关联话术
「我们做过一个限量商品的抢购，峰值 QPS 2 万。设计是三层：Nginx 层按 SKU 做限流只放行库存数的 10 倍流量；Redis 用 Lua 原子预扣减并返回排队序号；通过的请求进 MQ，消费端单线程串行写库，SQL 用 update ... where num >= 1。最后用扣减流水表的 biz_no 唯一索引兜底幂等。上线后零超卖，数据库峰值 QPS 只有 300。核心思路是让数据库只承担'最终账本'的职责，把并发挡在前面。」
题 11：主从延迟的原因是什么？读写分离后读不到刚写的数据怎么办？
精简口述答案（90 秒版）
「主从复制链路是：主库事务提交写 binlog → dump 线程推给从库 → 从库 IO 线程写 relay log → SQL 线程回放。延迟可能出现在任何一环，但最常见的是从库回放跟不上。
主要原因有五个：从库单线程回放而主库是多线程并发写；存在大事务（比如 delete 百万行，主库 10 秒，从库也要 10 秒且是串行的）；从库表结构缺索引导致回放时全表扫描；从库同时承担大查询或备份；DDL 在从库串行执行。
对策：开启并行复制（8.0 用 binlog_transaction_dependency_tracking = WRITESET，按写集合判断能否并行，效果最好）、拆小事务、保证主从表结构一致、备份走专用从库。
读不到刚写的数据有三种方案：强制走主库（最常用，对关键读打标）、判断延迟（不可靠，Seconds_Behind_Master 主库空闲时恒为 0）、等 GTID（WAIT_FOR_EXECUTED_GTID_SET 精确但复杂，超时后降级走主库）。」
连环追问链
| 追问 | 答题要点 |
|---|---|
为什么 Seconds_Behind_Master 不准？ | 它算的是"正在执行的事务在主库的时间戳 vs 从库当前时间"，主库没有新写入时它恒为 0，网络断开时也可能显示 0 |
| 半同步复制解决延迟了吗？ | 没有。它只保证"至少一个从库收到了 binlog"，不保证回放完成，所以仍可能读不到 |
| 并行复制的几个阶段？ | 5.6 按库并行 → 5.7 按组提交并行（同一组必然无冲突）→ 8.0 WRITESET，按行级写集合判断，粒度最细 |
| 主库宕机切换会丢数据吗？ | 异步复制会丢；半同步减少丢失窗口；要零丢失需要 MGR / Paxos 类方案 |
| 什么场景可以容忍延迟？ | 报表、搜索、列表页、历史查询；不能容忍的是"写后立即读"和"涉及金额判断"的读 |
| 读写分离怎么落地？ | 中间件层（ShardingSphere / MyCat）或框架层（动态数据源 + @Master 注解），关键是提供强制走主的逃生口 |
✅ 项目关联话术
「我们出过一个线上问题：用户下单成功后跳详情页，读从库读不到订单，前端提示'订单不存在'，用户以为失败又下了一单，造成重复订单。修复方案不是全局走主库（那读写分离就白做了），而是在网关做了一个'写后读保护'：用户发生写操作后，在 Redis 打一个 1 秒的用户级标记，这期间该用户的所有读请求强制路由主库。既保证了一致性体验，又把对主库的额外压力控制在很小的范围。」
题 12：线上 MySQL 突然变慢，但慢日志里没有慢 SQL，你怎么排查？
精简口述答案（90 秒版）
「我会先分清是单条 SQL 慢还是整个实例慢。慢日志里没东西，基本就是实例级问题，我按四条线查：
① 锁——查 information_schema.innodb_trx 看有没有长事务，performance_schema.data_lock_waits 看谁在等谁。表现是部分 SQL 卡住、CPU 却不高。加字段被长事务堵住导致 MDL 全表排队也属于这一类。
② 引擎后台——看脏页比例 Innodb_buffer_pool_pages_dirty / pages_total。如果长期贴着上限，说明 innodb_io_capacity 配小了（SSD 上还是默认 200），InnoDB 在集中刷脏，表现为周期性抖动、没有任何慢 SQL。
③ 连接与资源——show processlist 看连接数和状态：大量 Sleep 是连接池泄漏，大量 Sending data/Copying to tmp table 是大查询打满 IO，Waiting for table metadata lock 是 DDL 冲突。
④ 执行计划突变——同一条 SQL 昨天快今天慢，大概率是优化器选错索引：统计信息过期或长事务撑住旧版本导致 rows 估算失真。用 optimizer_trace 拿 cost 证据，ANALYZE TABLE 修统计信息。
如果是从库变慢，还要加一条：主从延迟 + 从库跑了备份或大报表。
顺序上我一定先止血（kill 掉元凶连接、限流），再定位根因，最后才改配置或改 SQL。」
连环追问链
| 追问 | 答题要点 |
|---|---|
| 怎么快速定位阻塞源头？ | 8.0 用 sys.innodb_lock_waits 一眼看出 blocking_pid，再 KILL 它；5.7 用 innodb_trx + innodb_lock_waits 联查 |
KILL QUERY 杀了锁为什么没放？ | 它只终止语句不结束事务，行锁要等事务结束才释放，必须 KILL CONNECTION 触发回滚 |
看到 Killed 一直不消失要重启吗？ | 不要。大概率在回滚大事务，重启后崩溃恢复仍要继续回滚且期间不可用，等待更安全 |
| CPU 不高但 QPS 掉了，可能是什么？ | IO 打满（刷脏 / 大查询扫表）、锁等待、连接数顶满在排队，三者都不吃 CPU |
| 同一条 SQL 昨天快今天慢，怎么证明是选错索引？ | 对比 EXPLAIN 的 key 和 rows，再开 optimizer_trace 看 considered_execution_plans 里两个候选的 cost |
| 排查要开哪些观测能力？ | 慢日志（long_query_time=0.1、log_queries_not_using_indexes）、performance_schema、sys 库、pt-query-digest 定时报告 |
✅ 项目关联话术
「我们线上出过一次典型的'无慢 SQL 变慢'。监控上接口 P99 每隔一分钟左右跳一次高峰，慢日志干干净净。我按这四条线查下来，最后落在脏页上：机器是 SSD，但 innodb_io_capacity 还是默认 200，redo 只配了 512M，高写入时反复写满并强制推进 checkpoint。调完 innodb_io_capacity、关掉 innodb_flush_neighbors、把 redo 扩到 2G，抖动就没了。这件事之后我在排查清单里固定加了一条：慢日志为空的变慢，去看引擎后台和锁，而不是继续翻 SQL。」
✍️ 手撕代码【核心实现 + 边界 Case 自测清单 + 变种拓展题】
MySQL 的"手撕"不是写算法，而是当场写出可执行的 SQL + 说清执行计划的变化。下面每题都建议在本地真实跑一遍，面试时才能说出
EXPLAIN的具体字段值。
题 1：建表造数，验证最左前缀、覆盖索引与 ICP
核心实现
sql
-- 1. 建表
DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
amount DECIMAL(20,4) NOT NULL DEFAULT 0,
sku_name VARCHAR(64) NOT NULL DEFAULT '',
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (id),
KEY idx_u_s_c (user_id, status, create_time)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
-- 2. 造 50 万行数据（递归 CTE，8.0 可用）
SET cte_max_recursion_depth = 1000000;
INSERT INTO t_order (user_id, status, amount, sku_name, create_time)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 500000
)
SELECT FLOOR(RAND() * 10000), -- user_id
FLOOR(RAND() * 5), -- status 0~4
ROUND(RAND() * 1000, 2),
CONCAT('sku_', n % 100),
DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 300 * 86400) SECOND)
FROM seq;
ANALYZE TABLE t_order; -- 更新统计信息，避免执行计划被旧统计误导sql
-- 3. 验证最左前缀：逐条看 key / key_len / Extra
-- (a) 命中 user_id：key_len = 4
EXPLAIN SELECT id FROM t_order WHERE user_id = 100;
-- (b) 命中 user_id + status：key_len = 4 + 1 = 5
EXPLAIN SELECT id FROM t_order WHERE user_id = 100 AND status = 1;
-- (c) 三列全中：key_len = 4 + 1 + 6 = 11（datetime(3) 占 6 字节）
EXPLAIN SELECT id FROM t_order WHERE user_id = 100 AND status = 1
AND create_time = '2026-02-01 00:00:00.000';
-- (d) 跳过 status：key_len 仍是 4，但 create_time 被 ICP 下推
-- Extra = "Using index condition" 或 "Using where; Using index"
EXPLAIN SELECT id FROM t_order WHERE user_id = 100
AND create_time > '2026-02-01';
-- (e) 不带 user_id：possible_keys 为 NULL → type = ALL 全表扫描
EXPLAIN SELECT id FROM t_order WHERE status = 1;
-- (f) 范围在中间：status 范围后 create_time 不参与定位，key_len = 5
EXPLAIN SELECT id FROM t_order WHERE user_id = 100 AND status > 1
AND create_time > '2026-02-01';sql
-- 4. 覆盖索引 vs 回表对比
-- 覆盖：Extra = Using index（amount 不在索引里，所以不要 select amount）
EXPLAIN SELECT user_id, status, create_time FROM t_order WHERE user_id = 100;
-- 回表：Extra 没有 Using index，多了一次聚簇索引查找
EXPLAIN SELECT user_id, amount FROM t_order WHERE user_id = 100;
-- 5. 消除 filesort：排序字段在索引里且方向一致
EXPLAIN SELECT id FROM t_order WHERE user_id = 100 AND status = 1
ORDER BY create_time DESC LIMIT 20; -- 无 filesort（逆向扫描索引）
EXPLAIN SELECT id FROM t_order WHERE user_id = 100 AND status = 1
ORDER BY amount DESC LIMIT 20; -- 有 Using filesort
-- 6. 隐式转换验证（关键对照实验）
ALTER TABLE t_order ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '',
ADD KEY idx_phone (phone);
EXPLAIN SELECT id FROM t_order WHERE phone = '13800138000'; -- ref，走索引
EXPLAIN SELECT id FROM t_order WHERE phone = 13800138000; -- ALL，索引失效边界 Case 自测清单
| # | Case | 期望行为 |
|---|---|---|
| 1 | where user_id = 100（只给首列） | key=idx_u_s_c, key_len=4, type=ref |
| 2 | where status = 1（跳过首列） | key=NULL, type=ALL |
| 3 | where user_id=100 and create_time>?（跳中间列） | key_len=4 但 Extra 含 Using index condition |
| 4 | where user_id>100 and status=1（首列范围） | key_len=4，status 不参与定位 |
| 5 | select user_id,status,create_time | Extra=Using index（覆盖） |
| 6 | select amount | 无 Using index，发生回表 |
| 7 | order by create_time + 等值 user_id/status | 无 Using filesort |
| 8 | order by status asc, create_time desc | 5.7 有 filesort；8.0 建降序索引可消除 |
| 9 | where phone = 数字 | 索引失效，type=ALL |
| 10 | where user_id + 0 = 100 | 列做运算 → 索引失效 |
| 11 | where sku_name like 'sku_1%' vs like '%1%' | 前者 range，后者 ALL |
| 12 | 删掉 ANALYZE TABLE 后再测 | 统计信息不准时可能选错索引，体会 optimizer_trace 的价值 |
🚀 变种拓展题
- 把
idx_u_s_c改成idx(status, user_id, create_time)，重新跑上面 12 个 Case，解释为什么大部分查询变慢了。 - 给
sku_name建前缀索引idx_sku(sku_name(5))，验证它为什么无法做覆盖索引、也无法消除order by sku_name的 filesort。 - 用 8.0 的函数索引
KEY idx_day((DATE(create_time)))，让where date(create_time) = '2026-02-01'重新走上索引。 - 用
SHOW INDEX FROM t_order看Cardinality，手动算出status和user_id的区分度，解释为什么status不该放在联合索引首位。 - 开启
optimizer_trace，找出 Case 2 中优化器放弃索引时计算出的cost值，并用FORCE INDEX对比强制走索引后的真实耗时。
题 2：防超卖——悲观锁、乐观锁、原子更新与幂等三件套
核心实现
sql
-- 库存表 + 扣减流水表（流水表的唯一索引是幂等的关键）
CREATE TABLE t_stock (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
sku_id BIGINT UNSIGNED NOT NULL,
num INT NOT NULL,
version INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uk_sku (sku_id)
) ENGINE = InnoDB;
CREATE TABLE t_stock_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
biz_no VARCHAR(64) NOT NULL COMMENT '业务唯一号，幂等依据',
sku_id BIGINT UNSIGNED NOT NULL,
delta INT NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_biz (biz_no)
) ENGINE = InnoDB;
INSERT INTO t_stock (sku_id, num) VALUES (1001, 100);sql
-- ❌ 错误写法：check-then-act，有竞态窗口，必然超卖
BEGIN;
SELECT num FROM t_stock WHERE sku_id = 1001; -- 读到 1
-- 另一个事务此刻也读到 1
UPDATE t_stock SET num = num - 1 WHERE sku_id = 1001; -- 两边都减 → num = -1
COMMIT;
-- ✅ 方案 A：原子更新（首选，一条 SQL，靠行锁串行化）
UPDATE t_stock SET num = num - 1 WHERE sku_id = 1001 AND num >= 1;
-- 应用层判断 affectedRows == 1 才算成功，== 0 表示库存不足
-- ✅ 方案 B：悲观锁（扣减逻辑复杂、要读多个字段算规则时用）
BEGIN;
SELECT num FROM t_stock WHERE sku_id = 1001 FOR UPDATE; -- 当前读 + X 锁
-- 应用层做复杂校验（优惠、限购、风控）
UPDATE t_stock SET num = num - 1 WHERE sku_id = 1001;
COMMIT;
-- ✅ 方案 C：乐观锁（冲突不激烈时用，失败需重试）
-- step1: SELECT num, version FROM t_stock WHERE sku_id = 1001; -- 得到 version=5
-- step2:
UPDATE t_stock SET num = num - 1, version = version + 1
WHERE sku_id = 1001 AND version = 5 AND num >= 1;
-- affectedRows == 0 → 被别人改过，重试（建议限制重试次数 + 退避）sql
-- ✅ 完整幂等扣减：流水唯一索引 + 原子更新放在同一事务
BEGIN;
-- 1. 先插流水，重复请求会触发唯一键冲突 → 捕获后直接返回"已处理"
INSERT INTO t_stock_log (biz_no, sku_id, delta) VALUES ('ORDER_20260301_001', 1001, -1);
-- 2. 原子扣减
UPDATE t_stock SET num = num - 1 WHERE sku_id = 1001 AND num >= 1;
-- 3. 应用层检查 affectedRows，为 0 则 ROLLBACK 返回库存不足
COMMIT;sql
-- 🔥 热点行优化：库存分桶，把一行的锁竞争分散到 N 行
CREATE TABLE t_stock_bucket (
sku_id BIGINT UNSIGNED NOT NULL,
bucket TINYINT NOT NULL,
num INT NOT NULL,
PRIMARY KEY (sku_id, bucket)
) ENGINE = InnoDB;
-- 100 库存拆 10 桶各 10 个；请求随机落桶，失败后换桶重试（最多试 3 桶）
UPDATE t_stock_bucket SET num = num - 1
WHERE sku_id = 1001 AND bucket = 7 AND num >= 1;
-- 查总库存：SELECT SUM(num) FROM t_stock_bucket WHERE sku_id = 1001;边界 Case 自测清单
| # | Case | 期望行为 |
|---|---|---|
| 1 | 库存 1，两个会话同时执行方案 A | 一个 affectedRows=1，另一个 0，num 最终为 0 不为负 |
| 2 | 库存 0 继续扣减 | where num >= 1 不匹配，affectedRows=0，不报错 |
| 3 | 同一 biz_no 重复提交 | 第二次 INSERT 抛 Duplicate entry，捕获后返回成功（幂等） |
| 4 | 方案 B 中第二个会话的 for update | 阻塞直到第一个事务提交，超过 innodb_lock_wait_timeout 报 1205 |
| 5 | 方案 C 两个会话拿到同一 version | 一个成功，另一个 affectedRows=0 需重试 |
| 6 | 乐观锁无限重试 | 冲突高时形成重试风暴，必须限次 + 退避 |
| 7 | sku_id 没有唯一索引 | where sku_id = ? 走非唯一索引会加 next-key lock，并发更差甚至死锁 |
| 8 | 分桶后某桶为 0 | 需换桶重试，否则会出现"总库存有但扣减失败"的假性售罄 |
| 9 | 事务里先 update 库存再调 RPC | 长事务持锁，必须把 RPC 移到提交之后 |
| 10 | 回滚补偿（取消订单） | update num = num + 1 必须同样带 biz_no 幂等校验，防重复返还 |
🚀 变种拓展题
- 用两个 session 手动复现方案 A 与错误写法的差异，并用
performance_schema.data_locks观察锁的持有情况。 - 把扣减改成 Redis + Lua 预扣减，设计 Redis 扣成功但数据库失败时的补偿方案（本地消息表）。
- 实现"超卖自动修复"的对账任务：按
t_stock_log的sum(delta)校验t_stock.num是否一致。 - 在 RC 和 RR 两种隔离级别下分别跑方案 A，解释为什么结果都正确（提示：update 是当前读）。
- 用
SELECT ... FOR UPDATE SKIP LOCKED改写分桶方案，让请求自动跳过被锁住的桶。
题 3：深分页优化——延迟关联与元组游标
核心实现
sql
-- 基于题 1 的 t_order 表，先建一个按时间排序的索引
ALTER TABLE t_order ADD KEY idx_create_time (create_time);
-- ❌ 原始写法：扫描 200010 行，回表 200010 次
SELECT id, user_id, amount, create_time
FROM t_order
ORDER BY create_time
LIMIT 200000, 10;
-- ✅ 方案 1：延迟关联——子查询走覆盖索引，只回表 10 次
SELECT o.id, o.user_id, o.amount, o.create_time
FROM t_order o
INNER JOIN (
SELECT id FROM t_order ORDER BY create_time LIMIT 200000, 10
) t ON o.id = t.id
ORDER BY o.create_time;
-- ✅ 方案 2：元组游标（最优）——把上一页最后一行的 (create_time, id) 传下来
SELECT id, user_id, amount, create_time
FROM t_order
WHERE (create_time, id) > ('2026-03-01 10:23:45.123', 180233)
ORDER BY create_time, id
LIMIT 10;
-- ⚠️ 错误的游标写法：create_time 重复时会漏数据
SELECT * FROM t_order
WHERE create_time > '2026-03-01 10:23:45.123' -- 同一毫秒的其他行被跳过
ORDER BY create_time LIMIT 10;
-- ⚠️ 用 >= 又会重复返回上一页最后一行
SELECT * FROM t_order
WHERE create_time >= '2026-03-01 10:23:45.123'
ORDER BY create_time LIMIT 10;
-- ✅ 方案 3：主键区间（仅当主键连续无空洞）
SELECT * FROM t_order WHERE id > 200000 ORDER BY id LIMIT 10;
-- ✅ 全量导出的正确姿势：主键游标批量拉，不用 offset
SELECT * FROM t_order WHERE id > ? ORDER BY id LIMIT 1000;sql
-- 对比验证：用 EXPLAIN ANALYZE 看真实耗时与扫描行数（8.0.18+）
EXPLAIN ANALYZE SELECT id FROM t_order ORDER BY create_time LIMIT 200000, 10;
EXPLAIN ANALYZE SELECT id FROM t_order
WHERE (create_time, id) > ('2026-03-01 10:23:45.123', 180233)
ORDER BY create_time, id LIMIT 10;
-- 关注 actual time 与 rows=... 的差异，通常相差 3 个数量级边界 Case 自测清单
| # | Case | 期望行为 |
|---|---|---|
| 1 | limit 0, 10（第一页） | 游标条件为空，直接 order by create_time, id limit 10 |
| 2 | 同一毫秒有多条记录 | 元组游标不重不漏；单字段游标会漏或重 |
| 3 | 翻到最后一页之后 | 返回空集，不报错，前端据此禁用"下一页" |
| 4 | 中途有数据被删除 | 游标分页自动跳过，不会出现 offset 方案的"页面错位" |
| 5 | 中途插入了早于游标的数据 | 游标方案看不到（符合"快照式翻页"预期），offset 方案会重复 |
| 6 | order by create_time desc | 游标条件要反向：(create_time, id) < (?, ?) |
| 7 | 带额外筛选条件 status = 1 | 索引要改成 idx(status, create_time, id) 才能同时过滤和有序 |
| 8 | 主键有空洞时用方案 3 | 每页可能不足 10 条，结果不正确 |
| 9 | 分库分表下的深分页 | 每片都要取 M+N 行归并，需用"二次查询法"优化 |
| 10 | 前端要显示总页数 | count(*) 只在第一页算一次并缓存，或改成"不显示总数" |
🚀 变种拓展题
- 用
EXPLAIN ANALYZE对比三种方案在 offset=1000 / 10 万 / 50 万时的耗时曲线，找出延迟关联开始失效的拐点。 - 实现一个双向游标：同时支持"上一页"（反向比较 + 反向排序 + 结果反转）。
- 把游标编码成 Base64 的不透明 token（内含
create_time和id）返回给前端，避免暴露内部字段。 - 在
status过滤条件下设计索引，使游标分页仍然没有Using filesort。 - 实现分库分表场景的"二次查询法"：先按
offset/N从各片取数，用最小时间戳回查各片补齐，再归并。
题 4：死锁复现与修复
核心实现
sql
-- 准备：RR 隔离级别（默认），确认间隙锁开启
SELECT @@transaction_isolation; -- REPEATABLE-READ
CREATE TABLE t_lock (
id INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
PRIMARY KEY (id),
KEY idx_c (c)
) ENGINE = InnoDB;
INSERT INTO t_lock VALUES (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20);sql
-- 【场景 A】更新顺序相反导致的经典死锁
-- session 1 -- session 2
BEGIN; BEGIN;
UPDATE t_lock SET d=1 WHERE id=5; UPDATE t_lock SET d=1 WHERE id=10;
-- 持有 id=5 的 X 锁 -- 持有 id=10 的 X 锁
UPDATE t_lock SET d=2 WHERE id=10; UPDATE t_lock SET d=2 WHERE id=5;
-- 等待 session2 的锁 -- 等待 session1 的锁 → 死锁！
-- 修复：所有业务对多行加锁时按主键升序排序后再依次更新sql
-- 【场景 B】间隙锁 + 插入意向锁死锁（最隐蔽、线上最高频）
-- session 1 -- session 2
BEGIN; BEGIN;
SELECT * FROM t_lock WHERE id=8 FOR UPDATE; SELECT * FROM t_lock WHERE id=9 FOR UPDATE;
-- id=8 不存在 → 持有间隙锁 (5,10) -- id=9 不存在 → 也持有间隙锁 (5,10)
-- 注意：间隙锁之间互相兼容，此处不冲突
INSERT INTO t_lock VALUES (8,8,8); INSERT INTO t_lock VALUES (9,9,9);
-- 申请插入意向锁，与 s2 的间隙锁冲突 → 等待 -- 同样冲突 → 死锁！
-- 【场景 C】先删后插的幂等写法（同理）
-- DELETE FROM t_lock WHERE c = 7; (不存在 → 间隙锁)
-- INSERT INTO t_lock VALUES (7,7,7);sql
-- 排查：查看最近一次死锁的完整现场
SHOW ENGINE INNODB STATUS\G
-- 重点读 LATEST DETECTED DEADLOCK 段：
-- (1) TRANSACTION / (2) TRANSACTION：两个事务各自在执行什么
-- WAITING FOR THIS LOCK TO BE GRANTED：正在等什么锁
-- HOLDS THE LOCK(S)：已经持有什么锁
-- lock_mode X locks gap before rec → 间隙锁
-- lock_mode X insert intention → 插入意向锁
-- WE ROLL BACK TRANSACTION (N) → 被回滚的是哪个
-- 把所有死锁都记进错误日志（线上建议常开）
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 实时看谁在等谁
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits\G
SELECT * FROM performance_schema.data_locks;sql
-- ✅ 修复方案
-- 1) 场景 A：固定加锁顺序
UPDATE t_lock SET d = d + 1 WHERE id IN (5, 10) ORDER BY id; -- 或应用层排序后逐条更新
-- 2) 场景 B/C：用唯一索引 + 原子语句替代"先查/删再插"
ALTER TABLE t_lock ADD UNIQUE KEY uk_c (c);
INSERT INTO t_lock (id, c, d) VALUES (8, 8, 8)
ON DUPLICATE KEY UPDATE d = VALUES(d);
-- 或 REPLACE INTO（注意它是 delete + insert，会改变主键和触发器行为）
-- 3) 降级隔离级别去掉间隙锁（需评估业务）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 4) 兜底：缩短锁等待，让失败快速暴露而不是堆积
SET SESSION innodb_lock_wait_timeout = 3;边界 Case 自测清单
| # | Case | 期望行为 |
|---|---|---|
| 1 | 场景 A 两会话交叉执行 | 报 1213 Deadlock found，其中一个被回滚 |
| 2 | 场景 B 的两条 for update | 都成功（间隙锁互相兼容），死锁发生在 insert 阶段 |
| 3 | 切到 RC 后重跑场景 B | 无间隙锁，不死锁（但要自行保证业务一致性） |
| 4 | where c = 7 无索引时更新 | 退化为全表加锁，并发直接归零 |
| 5 | 关闭 innodb_deadlock_detect | 不再秒级报错，改为等 innodb_lock_wait_timeout 超时（默认 50s，太长） |
| 6 | 热点行上 1000 并发 | 死锁检测 O(N²) 遍历等待图，CPU 飙高 |
| 7 | 死锁后事务状态 | 被选中的事务整个回滚，另一个继续；应用层必须捕获 1213 并重试 |
| 8 | 唯一键冲突场景 | insert 冲突时会给已存在行加 S 锁，三个并发 insert 同值也能死锁 |
| 9 | 只读事务参与死锁 | 快照读不加锁，不会参与；for update 才会 |
| 10 | 死锁重试 | 必须幂等 + 限次 + 随机退避，否则重试风暴 |
🚀 变种拓展题
- 用三个 session 构造一个"环形等待"的三方死锁，观察 InnoDB 选择回滚哪一个（提示：看 undo 量）。
- 复现唯一键冲突死锁：三个会话同时
insert同一个唯一值且都不提交。 - 对比 RR 和 RC 下
delete from t where c between 5 and 15的加锁范围（用data_locks逐条列出）。 - 写一个带指数退避的死锁重试装饰器（任意语言），并说明为什么必须配合幂等。
- 用
performance_schema.events_transactions_history_long还原死锁前两个事务的完整语句序列。
题 5：慢查询诊断全流程与 SQL 改写
核心实现
sql
-- ========== 第一步：开启观测 ==========
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2; -- 对新连接生效，需重连
SET GLOBAL log_queries_not_using_indexes = ON; -- 临时开，量大时会刷爆日志
SHOW VARIABLES LIKE 'slow_query_log_file';
-- ========== 第二步：找出 TOP SQL（不依赖慢日志文件） ==========
SELECT
LEFT(digest_text, 80) AS sql_digest,
count_star AS calls,
ROUND(sum_timer_wait / 1e12, 2) AS total_sec,
ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
sum_rows_examined AS examined,
sum_rows_sent AS sent,
ROUND(sum_rows_examined / GREATEST(sum_rows_sent, 1), 1) AS examine_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = DATABASE()
ORDER BY sum_timer_wait DESC
LIMIT 10;
-- examine_ratio 远大于 1 的就是过滤效率最差的 SQL，优先优化
-- ========== 第三步：排除锁与长事务（慢日志不含锁等待，会漏报） ==========
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_sec,
trx_rows_locked, trx_rows_modified, LEFT(trx_query, 60) AS q
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT * FROM sys.innodb_lock_waits\G
SHOW ENGINE INNODB STATUS\G -- 看 History list length 判断 undo 堆积sql
-- ========== 第四步：五个典型烂 SQL 的改写 ==========
-- 【1】函数包裹索引列 → 改写成范围
-- ❌ type=ALL
SELECT COUNT(*) FROM t_order WHERE DATE(create_time) = '2026-02-01';
-- ✅ type=range（半开区间，避免边界漏/重）
SELECT COUNT(*) FROM t_order
WHERE create_time >= '2026-02-01 00:00:00' AND create_time < '2026-02-02 00:00:00';
-- 【2】select * 导致无法覆盖 → 只取需要的列
-- ❌ 回表 N 次
SELECT * FROM t_order WHERE user_id = 100 ORDER BY create_time DESC LIMIT 20;
-- ✅ 先用覆盖索引拿主键，再精准回表（延迟关联）
SELECT o.id, o.user_id, o.amount, o.create_time FROM t_order o
INNER JOIN (SELECT id FROM t_order WHERE user_id = 100
ORDER BY create_time DESC LIMIT 20) t ON o.id = t.id;
-- 【3】or 两侧索引不一致 → 改 union all
-- ❌ 一侧无索引就整条退化
SELECT id FROM t_order WHERE user_id = 100 OR amount > 999;
-- ✅ 各走各的索引（需给 amount 建索引，且注意去重语义）
SELECT id FROM t_order WHERE user_id = 100
UNION
SELECT id FROM t_order WHERE amount > 999;
-- 【4】not in 子查询 → 改 left join ... is null
-- ❌ 子查询无法用索引且 NULL 语义坑多
SELECT id FROM t_order
WHERE user_id NOT IN (SELECT user_id FROM t_blacklist);
-- ✅ 显式 anti-join（且不受子查询 NULL 影响）
SELECT o.id FROM t_order o
LEFT JOIN t_blacklist b ON o.user_id = b.user_id
WHERE b.user_id IS NULL;
-- 【5】大批量 delete 一次性执行 → 分批
-- ❌ 大事务：锁多、undo 爆、主从延迟
DELETE FROM t_order WHERE create_time < '2025-01-01';
-- ✅ 分批 + 每批独立事务 + 批间 sleep 让从库追上
DELETE FROM t_order WHERE create_time < '2025-01-01' ORDER BY id LIMIT 1000;
-- 循环执行直到 affectedRows < 1000sql
-- ========== 第五步：索引设计与验证 ==========
-- 查区分度决定字段顺序
SELECT
COUNT(DISTINCT user_id) / COUNT(*) AS sel_user,
COUNT(DISTINCT status) / COUNT(*) AS sel_status,
COUNT(DISTINCT sku_name)/ COUNT(*) AS sel_sku
FROM t_order;
-- 找冗余 / 未使用索引（8.0 sys 库）
SELECT * FROM sys.schema_redundant_indexes;
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = DATABASE();
-- 删索引前先隐藏观察（8.0），有问题秒级回滚
ALTER TABLE t_order ALTER INDEX idx_create_time INVISIBLE;
-- 观察一周无异常再真正 DROP；若有异常立刻 VISIBLE
ALTER TABLE t_order ALTER INDEX idx_create_time VISIBLE;
-- 优化前后量化对比
EXPLAIN ANALYZE SELECT /* 旧 SQL */;
EXPLAIN ANALYZE SELECT /* 新 SQL */;
-- 对比 actual time、rows、loops 三个值，而不是只看一次执行墙钟时间边界 Case 自测清单
| # | Case | 期望行为 |
|---|---|---|
| 1 | 改了 long_query_time 但不重连 | 当前会话不生效，容易误判"没有慢 SQL" |
| 2 | SQL 因锁等待而慢 | 不进慢日志，必须靠 innodb_trx / data_lock_waits 发现 |
| 3 | DATE(create_time) 改写成范围 | 必须用 >= 当天 00:00 and < 次日 00:00，用 between 配 23:59:59 会漏掉毫秒 |
| 4 | or 改 union | union 去重有排序开销；确定无重复时用 union all |
| 5 | not in 子查询含 NULL | 结果恒为空集，这是 NULL 三值逻辑的经典坑 |
| 6 | 分批 delete 不加 order by id | 每批扫描位置不确定，可能反复扫同一批数据 |
| 7 | 分批 delete 不 sleep | binlog 瞬间暴增，从库延迟飙升 |
| 8 | EXPLAIN 显示 rows 很小但实际很慢 | 统计信息过期，先 ANALYZE TABLE |
| 9 | 加了索引但不走 | 看 optimizer_trace 的 cost，再决定是否 FORCE INDEX |
| 10 | 优化后写入变慢 | 新增索引的写放大代价，需评估读写比后再决定保留 |
| 11 | log_queries_not_using_indexes 长期开启 | 小表全表扫描也被记录，日志爆盘 |
| 12 | 只在预发验证 | 数据量和分布不同会得出错误结论，必须用生产同量级数据 |
🚀 变种拓展题
- 写一个巡检 SQL：输出所有
examine_ratio > 100且calls > 1000的语句摘要，作为每日优化清单。 - 用
pt-query-digest分析一份慢日志，解释"总耗时 TOP1"和"平均耗时 TOP1"为什么常常不是同一条 SQL，以及应该先优化哪个。 - 为分批 delete 写一个带自适应限流的脚本：根据从库
Seconds_Behind_Master动态调整 sleep 时长。 - 针对
where status = ? and user_id = ? order by create_time desc limit 20，设计索引并用EXPLAIN证明同时消除了回表和 filesort。 - 用
optimizer_trace找出一条"明明有索引却走全表扫描"的 SQL 的 cost 计算过程，并通过调大innodb_stats_persistent_sample_pages让优化器改变选择。
⚠️ 当日踩坑实录
| ❌ 坑 | ✅ 解决方案 |
|---|---|
| 以为"索引建在列上"，实际行锁是加在索引上的 | where 没走索引时行锁退化为锁住所有记录，等同锁表——加锁分析第一步永远是先确认走了哪个索引 |
| 以为 MyISAM 也有"回表" | MyISAM 主键与二级索引结构完全相同（叶子都存行地址），没有聚簇索引也没有回表概念；被淘汰的真因是无事务、表级锁、崩溃不可恢复 |
| 以为 InnoDB 默认隔离级别是 RC | InnoDB 默认是 RR；RC 是 Oracle / PostgreSQL / SQL Server 的默认级别，别记混 |
以为 idx(a,b,c) 查 a=1 and c=3 时 c "完全失效" | 准确说法是 c 不参与索引定位（key_len 只算 a），但会被 ICP 下推到引擎层过滤，减少回表次数 |
| 以为隐式类型转换一定导致索引失效 | 不对称：varchar 列传数字 → 对列做 CAST → 失效；int 列传字符串 → 转的是参数 → 索引仍有效 |
| 以为间隙锁之间会互斥 | 间隙锁之间完全兼容，两个事务可同时持有同一间隙的间隙锁；冲突发生在插入意向锁与间隙锁之间——这正是"先删后插"死锁的根源 |
| 以为 RR 彻底消灭了幻读 | 快照读靠 MVCC、当前读靠 Next-Key Lock；混用快照读和当前读时仍能观察到幻读 |
以为 count(*) 比 count(1) 慢 | InnoDB 对 count(*) 做了专门优化，会选最小的二级索引扫描；效率排序是 count(*) ≈ count(1) > count(主键) > count(普通字段) |
用 count(字段) 统计总数 | count(字段) 不统计 NULL，结果会少；要总行数只能用 count(*) |
| 用 Redis 存精确计数 | Redis 与 MySQL 无法做到原子更新，崩溃或超时会导致计数长期不一致；需要精确值应用单独的计数表并与业务同事务 |
以为 limit M, N 会直接跳到第 M 行 | 实际是"取 M+N 行再丢弃前 M 行"，offset 越大浪费越多，走二级索引还会回表 M+N 次 |
游标分页只用 create_time > ? | 时间重复时会漏数据（用 >）或重复（用 >=）；必须用元组 (create_time, id) > (?, ?) |
以为 long_query_time 改完立即生效 | 只对新连接生效，老连接要重连；而且锁等待时间不计入，因锁而慢的 SQL 根本不进慢日志 |
看到 State = Sending data 以为在传输网络数据 | 它表示正在读行并处理，是大部分慢查询的真实停留状态，和网络无关 |
| 排查慢查询只看 SQL 不看事务 | 长事务会让 undo 版本链变长，普通快照读也要大量回溯；排查顺序应是"先锁和事务，再 SQL" |
| 以为 join 的"小表"是指总行数少 | 应该是经过 where 过滤后参与 join 的数据量（行数 × 字段长度），千万行表过滤后剩 10 行也是小表 |
| 以为 BNL 只是慢一点 | 它会把被驱动表的冷数据大量读进 Buffer Pool 的 young 区，污染全库热数据，影响范围远超这一条 SQL |
大表加字段直接 alter table | 一个未提交的长事务 + DDL 的 MDL 写锁即可阻塞后续所有读请求，几秒打满连接池；必须先查 innodb_trx、设 lock_wait_timeout、用 gh-ost |
| 以为 8.0 的 INSTANT 加列万能 | 加列次数有上限，超过会静默退化成全表重建；且不支持改类型、加主键、改字符集 |
在 @Transactional 方法里调 RPC / 发 MQ | 下游超时多久，行锁就持有多久，极易引发雪崩；RPC 必须移到事务提交之后 |
先 select 判断库存再 update 扣减 | 经典 check-then-act 竞态，必然超卖；应改成 update ... where num >= 1 一条原子语句，用 affectedRows 判断结果 |
| 乐观锁无限重试 | 高冲突下形成重试风暴，比悲观锁更差；必须限次 + 随机退避 |
| 认为唯一索引比普通索引更好 | 唯一索引无法使用 Change Buffer（必须读页判重），写密集且不常立即读的场景性能明显更差 |
用 utf8 字符集 | MySQL 的 utf8 是 utf8mb3，存不了 emoji；必须用 utf8mb4 |
用 float / double 存金额 | 二进制浮点无法精确表示小数，对账必错；用 decimal(20,4) 或 bigint 存分 |
| 表不建显式主键 | InnoDB 会用 6 字节隐式 row_id，用尽后从 0 循环覆盖数据，是静默的数据丢失 |
| 主键用 UUID / 随机字符串 | 随机插入导致频繁页分裂、空间碎片、二级索引膨胀；应用自增或雪花等趋势递增 ID |
以为 Seconds_Behind_Master 能准确反映延迟 | 主库空闲时恒为 0，网络断开时也可能是 0；应对比 GTID 或 worker 的提交时间戳 |
| 以为半同步复制能解决"读不到刚写的数据" | 它只保证从库收到 binlog，不保证回放完成，仍可能读不到 |
| 为了"读己之写"全局强制走主库 | 读写分离直接失效；应只对"写后短时间内的同用户读"强制走主 |
order by rand() 取随机数据 | 会建临时表 + 为每行算随机数 + 全排序，大表上是灾难；应在应用层生成随机 offset 或随机主键 |
| 索引建得越多越好 | 写放大、空间膨胀、优化器候选变多更容易选错计划、DDL 变慢；单表建议不超过 5 个 |
| 删索引前不做灰度 | 应先 ALTER INDEX ... INVISIBLE 观察一周，有问题可秒级恢复 |
| 优化只在预发环境验证 | 数据量和分布不同会得出完全相反的结论，必须用生产同量级数据验证 |
| 一遇到大表就想分库分表 | 顺序应是：改 SQL/索引 → 加缓存 → 读写分离 → 冷热分离归档 → 分区表 → 最后才分库分表 |
| 分库分表后才发现分片键选错 | 分片键一旦确定几乎无法更改；必须是高频查询条件、分布均匀、能把同一聚合根收拢到一片 |
log_queries_not_using_indexes 长期开启 | 小表全表扫描也会被记录，日志迅速爆盘 |
| 死锁日志只看最后一条 | SHOW ENGINE INNODB STATUS 只保留最近一次死锁；线上应开 innodb_print_all_deadlocks 全量落盘 |
| 捕获死锁后直接重试 | 必须保证操作幂等并限制重试次数，否则会放大故障 |
| 以为 "有索引优化器就一定会走" | 优化器只认成本：IO 单价 × 预估页数 + CPU 单价 × 预估行数，而预估行数来自采样，采样失真就会选错 |
用 FORCE INDEX 当常规手段 | 索引被改名或删除后 SQL 直接报错；优先 ANALYZE TABLE 修统计信息或改写 SQL 引导，FORCE INDEX 只用于止血 |
| 删完历史数据反而更慢 | 长事务撑住 undo 使旧版本无法 purge，采样把旧版本也数进去导致 rows 偏大，优化器转而全表扫；先杀长事务再 ANALYZE TABLE |
| 大表统计信息用默认采样 | innodb_stats_persistent_sample_pages 默认只有 20 页，倾斜字段必然估错；大表按表设置 STATS_SAMPLE_PAGES，8.0 可加直方图 |
SSD 上不改 innodb_io_capacity | 默认 200 是机械盘量级，InnoDB 以为自己很弱而龟速刷脏，脏页攒满后集中爆发，表现为没有慢 SQL 的周期性抖动 |
SSD 上保留 innodb_flush_neighbors=1 | 机械盘时代的 "连坐刷邻居页" 优化，SSD 上只会放大无用 IO；8.0 已默认 0，5.7 需手动关 |
| redo log 只配几百 MB | 高写入下反复写满并强制推进 checkpoint，所有更新被阻塞，表现为 "每隔十几秒卡一下"；生产建议 1~4 GB |
以为 delete 能释放磁盘 | delete 只是标记可复用，.ibd 不会缩小；真正该关心的是空洞带来的 IO 放大，回收要靠 ALTER TABLE ... ENGINE=InnoDB 重建 |
随手对大表执行 OPTIMIZE TABLE | 需要接近原表大小的额外磁盘，且首尾要升级 MDL 写锁，有长事务就堵死整张表；大表走 gh-ost |
用 KILL QUERY 释放行锁 | 它只终止语句不结束事务，锁一个也不放；要释放锁必须 KILL CONNECTION 触发回滚 |
看到 Killed 不消失就重启实例 | 大概率在回滚大事务，重启后崩溃恢复仍要继续回滚且期间整库不可用，等待比重启安全 |
靠调大 max_connections 扛短连接风暴 | 每个连接都要内存与调度，调太大会让整体更慢；根治是应用层连接池，并配 wait_timeout 回收空闲连接 |
| 长连接跑完大查询不重置 | 大排序/大临时表的内存挂在连接上，直到断开才释放，容易把 mysqld 吃到 OOM；大查询后调用 mysql_reset_connection |
📌 当日复盘任务
索引与存储结构
- [ ] 能画出 B+ 树三层结构图，并口算"三层存 2190 万行"的推导过程
- [ ] 说清聚簇索引、二级索引、回表、覆盖索引四者关系，并给出一个把回表改成覆盖的真实例子
- [ ] 解释为什么 InnoDB 不用 Hash / B 树 / 跳表，每个至少说出两条理由
- [ ] 手写
key_len的计算规则，并用它判断联合索引用到了几个字段 - [ ] 列出 7 类索引失效场景，并说清其中哪几条本质是"优化器成本选择"而非语法限制
- [ ] 解释隐式类型转换的不对称性（varchar 传数字 vs int 传字符串）
- [ ] 说明前缀索引为什么不能覆盖、不能排序，以及倒序存储和哈希字段两种补救手段
- [ ] 在本地跑完手撕题 1 的 12 个 Case，记录每个 Case 的
key_len与Extra
事务与 MVCC
- [ ] 说清 ACID 各自由哪个机制保证（undo→A、redo→D、MVCC+锁→I、C 是结果）
- [ ] 画出 undo 版本链 + ReadView 四字段，并复述可见性判断算法
- [ ] 一句话说出 RC 与 RR 的唯一差异（ReadView 生成时机）
- [ ] 区分不可重复读与幻读：一个管"内容变"，一个管"行数多"
- [ ] 举出 RR 下仍能观察到幻读的两个具体 SQL 场景
- [ ] 说出长事务的四条危害，并能用
innodb_trx和 History list length 定位
锁
- [ ] 画出锁体系全景树（全局 / 表级 / 行级）并说明意向锁存在的意义
- [ ] 背出加锁规则"两原则 + 两优化"，并用
data_locks验证一个范围查询的实际加锁区间 - [ ] 解释为什么间隙锁之间互相兼容，以及它如何导致"先删后插"死锁
- [ ] 本地复现场景 A 和场景 B 两种死锁，读懂
LATEST DETECTED DEADLOCK的每个字段 - [ ] 说出降低死锁概率的四种手段及各自代价
- [ ] 解释 MDL 写锁为什么会阻塞后续所有读请求
日志与持久化
- [ ] 用一张表对比 redo / undo / binlog 的层级、类型、写法、作用
- [ ] 复述两阶段提交的三个步骤，以及崩溃恢复时如何判断提交还是回滚
- [ ] 说明先写 binlog 和先写 redo 分别会导致哪种主从不一致
- [ ] 解释"双 1"配置的含义与组提交如何缓解 fsync 开销
- [ ] 说出
binlog_format=ROW的必要性，以及它在误操作恢复中的作用
SQL 优化
- [ ] 背出
type的优劣顺序和 8 个高频Extra值的含义 - [ ] 完整复述慢查询排查五步法（开观测 → 聚合 TOP → 看锁和事务 → 单条深挖 → 分层归因）
- [ ] 说清 NLJ / BNL / hash join 的触发条件与"小表驱动大表"的准确含义
- [ ] 说明 filesort 的全字段排序与 rowid 排序差异，以及如何彻底消除 filesort
- [ ] 写出深分页的三种改写方案，并解释元组游标为什么必须带主键
- [ ] 完成手撕题 5 的五个 SQL 改写，并用
EXPLAIN ANALYZE量化前后差异 - [ ] 说清优化器的成本模型（IO 单价 × 页数 + CPU 单价 × 行数）与
rows的采样来源，并用optimizer_trace拿到 cost 证据 - [ ] 复述统计信息失真的四类成因（统计过期 / 长事务撑旧版本 / 采样不足 + 数据倾斜 / 为避排序换索引）与六种解法
架构与运维
- [ ] 说出亿级大表加字段的完整操作清单（查长事务 → 设超时 → INSTANT 评估 → gh-ost → 预案）
- [ ] 对比 gh-ost 与 pt-osc，说清为什么"可暂停"比"更快"重要
- [ ] 设计一套防超卖方案：Redis 预扣 + MQ 削峰 + 原子更新 + 唯一索引幂等
- [ ] 列出主从延迟的五个原因与对应手段，说明并行复制三个阶段的演进
- [ ] 说出"读己之写"的三种方案及各自代价，并设计一个只对写后同用户生效的路由策略
- [ ] 背出分库分表前应该先做的六件事，以及分片键选择的三条标准
- [ ] 整理 MySQL 8.0 新特性清单，重点准备窗口函数、不可见索引、
SKIP LOCKED三个加分点 - [ ] 说出刷脏页的四种时机，以及
innodb_io_capacity/innodb_flush_neighbors/ redo 容量在 SSD 上的配置原则 - [ ] 说明
delete后数据空洞的危害与重建表（ALTER TABLE ... ENGINE=InnoDB）的三个风险 - [ ] 区分
KILL QUERY与KILL CONNECTION，并解释Killed长时间不消失时为什么不该重启实例 - [ ] 复述实例级变慢的四条排查线（锁与事务 / 引擎后台刷脏 / 连接与资源 / 执行计划突变）