跳到主要内容

MySQL 架构与存储引擎

问题​

MySQL 的整体架构是怎样的？InnoDB 和 MyISAM 有什么区别？一条 SQL 在 MySQL 中是怎么执行的？

答案​

一、MySQL 分层架构​

MySQL 采用 Server 层 + 存储引擎层 的分层设计，这是理解所有 MySQL 机制的基础：

Server 层​

组件职责
连接器管理客户端连接、身份认证、权限查询
分析器词法分析（识别关键字）→ 语法分析（构建语法树）
优化器生成执行计划、选择索引、JOIN 顺序优化
执行器校验权限 → 调用存储引擎接口逐行/批量获取数据

存储引擎层​

存储引擎是 插件式 的，通过统一的 Handler API 与 Server 层交互。每张表可以使用不同的引擎。

二、一条 SQL 的执行全流程​

以 SELECT * FROM users WHERE id = 1 为例：

对于 UPDATE 语句，额外涉及：

  1. Buffer Pool 中修改数据页（脏页）
  2. 写 redo log（WAL 机制，保证崩溃恢复）
  3. 写 binlog（Server 层，用于主从复制）
  4. 两阶段提交（保证 redo log 和 binlog 一致性）

三、InnoDB 引擎详解​

InnoDB 是 MySQL 5.5+ 的 默认存储引擎，也是面试几乎所有 MySQL 问题的核心。

InnoDB 内存架构​

Buffer Pool 工作原理​

Buffer Pool 是 InnoDB 最核心的内存结构，默认 128MB（生产环境通常设为物理内存的 60~80%）。

改进的 LRU 算法：

传统 LRU 的问题：全表扫描会将大量冷数据加载到 Buffer Pool，把热数据挤出去。

InnoDB 的解决方案 — 分区 LRU：

  • young 区（热数据，5/8）：频繁访问的页
  • old 区（冷数据，3/8）：新加载的页先放 old 区
  • 只有在 old 区停留超过 innodb_old_blocks_time（默认 1000ms）后再次被访问，才晋升到 young 区

这样全表扫描的页会在 old 区快速淘汰，不影响热数据。

四、InnoDB vs MyISAM​

特性InnoDBMyISAM
事务✅ 支持❌ 不支持
行级锁✅ 支持❌ 仅表锁
外键✅ 支持❌ 不支持
MVCC✅ 支持❌ 不支持
崩溃恢复✅ redo log❌ 无
聚簇索引✅ 数据与主键索引一起❌ 索引与数据分离
全文索引✅（5.6+）✅
COUNT(*)需遍历（MVCC 不同事务看到不同数据）存储了行数，直接返回
存储文件.ibd（表空间）.MYD（数据）+ .MYI（索引）
面试要点

为什么 InnoDB 的 COUNT(*) 比 MyISAM 慢？

因为 InnoDB 支持 MVCC，不同事务在同一时刻看到的行数可能不同，所以无法像 MyISAM 那样简单存储一个总行数，必须逐行判断可见性。

五、其他存储引擎​

引擎特点适用场景
Memory数据全在内存，速度快，重启丢失临时表、缓存
Archive只支持 INSERT/SELECT，高压缩日志归档
CSV数据以 CSV 文件存储数据交换
NDBMySQL Cluster 引擎分布式集群

常见面试问题​

Q1: MySQL 查询缓存为什么被废弃了？​

答案：

MySQL 查询缓存在 8.0 被完全移除，原因：

  1. 命中率极低：任何对表的写操作都会使该表所有缓存失效
  2. 并发瓶颈：查询缓存使用全局互斥锁，高并发时成为瓶颈
  3. 内存浪费：缓存管理本身消耗内存

替代方案：在应用层使用 Redis 等外部缓存。

Q2: 为什么 InnoDB 使用 B+ 树而不是 B 树？​

答案：

特性B 树B+ 树
数据存储所有节点都存数据只有叶子节点存数据
范围查询需要中序遍历叶子节点有链表，直接遍历
磁盘 IO非叶子节点大（含数据），每页存的 key 少非叶子节点小（只存 key），每页存的 key 多，树更矮
查询稳定性不稳定（可能在非叶子命中）稳定（必须到叶子节点）

核心原因：B+ 树更矮（3~4 层即可存千万级数据），IO 次数更少，且叶子节点链表天然支持范围查询。

Q3: 一条 UPDATE 语句的执行流程？​

答案：

1. 连接器 → 分析器 → 优化器 → 执行器（同 SELECT）
2. 执行器调用 InnoDB 接口读取目标行
3. InnoDB 在 Buffer Pool 中查找（未命中则从磁盘加载）
4. 记录旧值到 undo log（用于回滚和 MVCC）
5. 在 Buffer Pool 中修改数据页（脏页）
6. 写 redo log（prepare 状态）  ← WAL
7. 写 binlog
8. 提交事务：redo log 改为 commit 状态  ← 两阶段提交

Q4: Buffer Pool 的大小怎么设置？​

答案：

-- 查看当前 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 推荐设置为物理内存的 60~80%
SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB
  • 通过 SHOW ENGINE INNODB STATUS 查看 Buffer Pool 命中率
  • 命中率应 > 99%，低于 95% 说明需要增大

Q5: InnoDB 的四大特性是什么？​

答案：

  1. 插入缓冲（Change Buffer）：对非唯一二级索引的 INSERT 操作先缓冲，批量合并写入
  2. 二次写（Double Write）：防止部分写失效（页写了一半系统崩溃）
  3. 自适应哈希索引（AHI）：InnoDB 自动对热点页建立哈希索引
  4. 预读（Read Ahead）：预测即将访问的页，提前加载到 Buffer Pool

相关链接​