架构与存储引擎

一条查询语句的完整流程

必背的分层模型，追问「SQL 是怎么执行的」就是考这个：

客户端
  ↓
连接器        —— 管理连接、权限认证（长连接 vs 短连接）
  ↓
【Server 层】
解析器        —— 词法分析 + 语法分析，生成解析树
预处理器      —— 校验表和列是否存在、展开 *
优化器        —— 决定用哪个索引、多表的连接顺序（基于成本估算）
执行器        —— 调用存储引擎接口逐行取数据
  ↓
【存储引擎层】 InnoDB / MyISAM / Memory
  ↓
磁盘文件

查询缓存（Query Cache）在 MySQL 8.0 被彻底移除。原因是命中率太低且维护成本高 —— 只要表有任何写入，这张表相关的所有缓存全部失效。写多读少的场景下，维护缓存的开销远大于收益。

优化器可能选错索引。它基于采样统计估算成本，统计信息不准就会误判。应对：ANALYZE TABLE 重新采样；用 FORCE INDEX 强制指定；或者改写 SQL 引导优化器。

Buffer Pool

InnoDB 的核心组件，缓存数据页和索引页。默认 128MB，生产通常配到物理内存的 50～70%。

三个链表：

Free List —— 空闲页
LRU List —— 已缓存的页，按冷热排序
Flush List —— 被修改过的脏页，等待刷盘

改进版 LRU（关键考点）。朴素 LRU 有两个致命问题，InnoDB 把 LRU 链表按 5:3 拆成 young 区和 old 区来解决：

问题	场景	解法
预读失效	预读进来的页实际没被访问，却占着链表头部，把真正的热点页挤出去	新页先插入 old 区头部（而非整个链表头），没被访问就会很快被淘汰，不污染 young 区
Buffer Pool 污染	一条全表扫描的 SQL 读入大量页，每页都被访问过一次，全部晋升到 young 区，把真热点全部挤掉	加时间门槛 `innodb_old_blocks_time`（默认 1000ms）—— 页在 old 区停留超过 1 秒后再次被访问才晋升

第二条是重点：全表扫描是「读入后立刻访问、然后再也不用」，停留时间远小于 1 秒，所以永远进不了 young 区。

脏页刷盘的四个时机：redo log 空间不足（要推进 checkpoint）、Buffer Pool 空闲页不足、系统空闲时主动刷、正常关闭时全部刷。

:::warning 刷脏页导致的「偶发性卡顿」业务反馈「SQL 平时很快，偶尔突然很慢」，一个常见原因就是 InnoDB 在刷脏页。两种情形：redo log 写满了必须停下来推进 checkpoint（此时所有更新全部阻塞）；或者要淘汰的页正好是脏页，必须先刷盘才能复用。

排查看 innodb_buffer_pool_wait_free 是否增长。缓解：innodb_io_capacity 要配成磁盘真实的 IOPS（SSD 配低了会导致刷盘太慢、脏页堆积），innodb_max_dirty_pages_pct 控制脏页比例上限。 :::

Change Buffer

解决什么：更新一条数据时，如果对应的数据页不在 Buffer Pool 里，就得先从磁盘读进来（随机 IO），很慢。Change Buffer 的做法是先把变更记在内存里，等这个页因为别的原因被读进来时再合并（merge）。

只对非唯一的二级索引有效（高频追问「为什么唯一索引用不了」）—— 唯一索引插入前必须判断是否违反唯一约束，判重就必须把页读进内存，那么直接更新就好了，缓存变更没有意义。

所以：写多读少的场景收益最大（变更在 Change Buffer 里攒得越多，一次 merge 省下的 IO 越多）；写完立刻读则是负收益（刚记下就要 merge，白绕一圈）。配置项 innodb_change_buffer_max_size 默认占 Buffer Pool 的 25%。

注意 Change Buffer 的变更也会写 redo log，所以崩溃不丢。

Doublewrite Buffer

解决「部分页写失败」（partial page write）。InnoDB 页是 16KB，操作系统页是 4KB，磁盘扇区更小。刷一个 16KB 的页需要多次底层写，如果中途断电，页就处于写坏了一半的状态。

为什么 redo log 救不了：redo log 记的是「在某页的某偏移做什么改动」，它的前提是这个页本身是完整的。页本身损坏了，拿 redo 去改一个坏页毫无意义。

所以刷脏页时先把页顺序写到共享表空间的 doublewrite 区（这是顺序 IO，开销小），再写到真实位置。崩溃恢复时如果发现页损坏，就从 doublewrite 区把完整副本拿回来。

自适应哈希索引（AHI）

InnoDB 监控对二级索引页的访问模式，对频繁以相同模式做等值查询的热点页自动建立哈希索引，把 B+ 树的多次比较降为一次哈希定位。

特点：全自动、无法手动指定；只对等值查询有效，范围查询用不上。在某些高并发工作负载下它的内部锁反而成为瓶颈（btr_search_latch 争用），所以有些场景需要手动关掉 innodb_adaptive_hash_index。

页与行格式

页（Page）是 InnoDB 的最小 IO 单位，默认 16KB。页内结构：

File Header      —— 页号、前后页指针（双向链表就靠这个）、校验和
Page Header      —— 页内记录数、空闲空间位置、页目录槽数
Infimum/Supremum —— 虚拟的最小/最大记录，作为单链表的头尾哨兵
User Records     —— 真实的行数据，按主键顺序用单链表串联
Free Space       —— 未使用空间
Page Directory   —— 页目录，稀疏槽位，用于页内二分查找
File Trailer     —— 校验和，与 File Header 比对以检测页是否写坏

为什么需要页目录：页内记录是单链表，顺序遍历是 O(n)。页目录把记录分组，每组一个槽，查询时先在槽上二分定位到组，再在组内遍历（每组最多 8 条）。这就是「B+ 树定位到页之后怎么在页内查找」的答案。

行格式（ROW_FORMAT）：DYNAMIC 是 5.7+ 的默认值。

行溢出：一个页 16KB，但 varchar 可以声明到 65535 字节，放不下时就要溢出到额外的页。

COMPACT —— 在原页存前 768 字节前缀 + 20 字节指向溢出页的指针
DYNAMIC —— 原页只存 20 字节指针，数据全放溢出页

DYNAMIC 更好的原因：不浪费宝贵的页内空间存前缀，同一页能放更多行，减少 IO。

:::tip 字符集的坑 utf8 在 MySQL 里是「伪 UTF-8」，最多 3 字节，存不了 emoji 和部分生僻字（会报 Incorrect string value）。真正的是 utf8mb4（4 字节）。8.0 起默认字符集已改为 utf8mb4，但老库升级上来的表要手动改。

代价：varchar(255) 在 utf8mb4 下最大占 1020 字节，而索引键长度有限制（InnoDB 单列索引前缀默认上限 767 字节，开 DYNAMIC + 大页后为 3072 字节），所以给长 varchar 建索引容易超限，需要用前缀索引。 :::

存储引擎对比

	InnoDB	MyISAM	Memory
事务	✅	❌	❌
锁粒度	行锁	表锁	表锁
崩溃恢复	✅ redo log	❌ 需修表	数据全丢
外键	✅	❌	❌
索引组织	聚簇索引（数据在叶子）	非聚簇（索引存行地址）	默认哈希索引
`COUNT(*)`	需要扫描	O(1)，存了行数	—
适用	几乎所有场景	只读报表（已基本淘汰）	临时表、会话数据

MySQL 5.5 起默认引擎是 InnoDB。被问「什么时候用 MyISAM」—— 现实答案是基本不用了，它唯一的优势（COUNT(*) 快、全文索引）在 InnoDB 里都已补齐（5.6+ 支持全文索引）。

一条查询语句的完整流程​

Buffer Pool​

Change Buffer​

Doublewrite Buffer​

自适应哈希索引（AHI）​

页与行格式​

存储引擎对比​