架构与存储引擎

理解 MySQL 架构是深入学习数据库的基础，存储引擎的选择直接影响系统性能和数据一致性。

MySQL 整体架构 ⭐⭐⭐⭐⭐

MySQL 采用分层架构设计，从上到下分为：Server 层和存储引擎层。

架构图解

┌──────────────────────────────────────────────────────────────────────────────┐
│                              客户端层                                         │
│                     (MySQL Client / JDBC / Python等)                         │
└────────────────────────────────┬─────────────────────────────────────────────┘
                                 │ TCP/IP、Socket
                                 ▼
┌──────────────────────────────────────────────────────────────────────────────┐
│                              Server 层                                        │
│                      (与存储引擎无关的通用功能)                                │
│                                                                               │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │  1. 连接器 (Connector)                                               │    │
│  │     - 管理连接、权限验证                                              │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                               │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │  2. 查询缓存 (Query Cache) - MySQL 8.0 已移除                        │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                               │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │  3. 分析器 (Analyzer)                                                │    │
│  │     - 词法分析、语法分析                                              │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                               │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │  4. 优化器 (Optimizer)                                               │    │
│  │     - 执行计划生成、索引选择、JOIN 顺序优化                            │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
│                                                                               │
│  ┌─────────────────────────────────────────────────────────────────────┐    │
│  │  5. 执行器 (Executor)                                                │    │
│  │     - 权限检查、调用存储引擎接口                                       │    │
│  └─────────────────────────────────────────────────────────────────────┘    │
└────────────────────────────────┬─────────────────────────────────────────────┘
                                 │ 存储引擎 API
                                 ▼
┌──────────────────────────────────────────────────────────────────────────────┐
│                            存储引擎层                                         │
│                        (插件式架构，可替换)                                   │
│                                                                               │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐   │
│  │   InnoDB     │  │    MyISAM    │  │    Memory    │  │   Archive    │   │
│  │  (默认引擎)   │  │  (已不推荐)   │  │   (内存表)    │  │  (归档表)     │   │
│  └──────────────┘  └──────────────┘  └──────────────┘  └──────────────┘   │
└────────────────────────────────┬─────────────────────────────────────────────┘
                                 │
                                 ▼
┌──────────────────────────────────────────────────────────────────────────────┐
│                              文件系统                                         │
│        数据文件(.ibd)、日志文件(redo/undo/binlog)                              │
└──────────────────────────────────────────────────────────────────────────────┘

Server 层组件详解

1. 连接器

功能：

• 建立 TCP 连接

• 验证用户名和密码

• 读取用户权限信息（保存在内存中，直到连接断开）

• 管理连接（长连接/短连接）

查看连接：

-- 查看当前所有连接
SHOW PROCESSLIST;

-- 查看连接状态
SHOW STATUS LIKE 'Threads%';

长连接问题：

-- 问题：长连接积累导致内存占用过高
-- 解决方案1：定期断开长连接
SET GLOBAL wait_timeout = 28800;  -- 8小时超时

-- 解决方案2：定期执行 mysql_reset_connection (重置连接状态但不重连)

2. 查询缓存（已废弃）

MySQL 8.0 之前的版本有查询缓存功能：

• 工作原理：将 SQL 作为 key，结果作为 value 缓存

• 失效机制：表的任何更新操作都会清空该表所有缓存

• 废弃原因：

  • 命中率极低（SQL 必须完全相同才能命中）

  • 维护成本高（频繁失效）

  • 不适合写密集型应用

3. 分析器

词法分析：识别 SQL 中的关键字、表名、列名

SELECT id, name FROM users WHERE age > 18;

-- 词法分析结果：
-- SELECT -> 关键字
-- id, name -> 列名
-- FROM -> 关键字
-- users -> 表名
-- WHERE -> 关键字
-- age, 18 -> 列名、常量

语法分析：检查 SQL 语法是否正确

-- 错误示例
SELECT * FORM users;  -- 报错：FORM 应为 FROM

4. 优化器 ⭐⭐⭐⭐⭐

核心职责：

• 选择使用哪个索引

• 决定多表 JOIN 的顺序

• 生成执行计划

优化器决策示例：

-- 假设 users 表有两个索引：idx_age 和 idx_name
SELECT * FROM users WHERE age > 18 AND name = 'Zhang';

-- 优化器会计算两种方案的成本：
-- 方案1：使用 idx_age 索引，然后过滤 name
-- 方案2：使用 idx_name 索引，然后过滤 age
-- 最终选择成本较低的方案

查看执行计划：

EXPLAIN SELECT * FROM users WHERE age > 18;

5. 执行器

执行流程：

1. 判断用户对表是否有执行权限

2. 打开表（调用存储引擎接口）

3. 根据执行计划调用存储引擎接口获取数据

4. 返回结果给客户端

-- 执行器调用存储引擎的伪代码
// 打开表
table = engine.open_table("users");

// 读取第一行
row = table.read_first();
while (row != null) {
    // 判断是否满足条件
    if (row.age > 18) {
        result.add(row);
    }
    // 读取下一行
    row = table.read_next();
}
return result;

---

InnoDB 存储引擎 ⭐⭐⭐⭐⭐

InnoDB 是 MySQL 5.5 之后的默认存储引擎，也是面试重点。

InnoDB 核心特性

特性	说明
事务支持	支持 ACID 特性，支持事务的提交、回滚和崩溃恢复
行级锁	锁粒度小，并发性能好
MVCC	多版本并发控制，提高读写并发
聚簇索引	数据按主键顺序存储，主键查询性能高
外键约束	支持外键，保证参照完整性
崩溃恢复	通过 redo log 和 undo log 保证数据不丢失

InnoDB 内存结构

┌──────────────────────────────────────────────────────────────────────┐
│                          InnoDB 内存结构                              │
├──────────────────────────────────────────────────────────────────────┤
│                                                                       │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  Buffer Pool (缓冲池) - 默认 128MB                              │ │
│  │                                                                  │ │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐         │ │
│  │  │  Data Pages  │  │  Index Pages │  │   Undo Pages │         │ │
│  │  │   数据页      │  │   索引页     │  │   回滚页      │         │ │
│  │  └──────────────┘  └──────────────┘  └──────────────┘         │ │
│  │                                                                  │ │
│  │  管理策略：LRU 算法 (改进版，防止全表扫描污染缓存)                │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                                                                       │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  Change Buffer (写缓冲) - 针对非唯一二级索引                     │ │
│  │  - 将非聚簇索引的修改先缓存，减少随机 I/O                        │ │
│  │  - 后台定期合并到磁盘                                            │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                                                                       │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  Adaptive Hash Index (自适应哈希索引)                           │ │
│  │  - InnoDB 自动为热点页建立哈希索引                               │ │
│  │  - 进一步提升查询性能                                            │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                                                                       │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  Log Buffer (日志缓冲) - 默认 16MB                              │ │
│  │  - 缓存 redo log，定期刷盘                                       │ │
│  └────────────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────────┘

Buffer Pool 配置：

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

-- 设置 Buffer Pool 大小（建议设置为物理内存的 50%-80%）
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB

-- 查看 Buffer Pool 使用情况
SHOW ENGINE INNODB STATUS;

InnoDB 磁盘结构

┌──────────────────────────────────────────────────────────────────────┐
│                         InnoDB 磁盘结构                               │
├──────────────────────────────────────────────────────────────────────┤
│                                                                       │
│  表空间文件 (Tablespace)                                              │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  ibdata1 (系统表空间)                                            │ │
│  │  - 数据字典、undo log、change buffer                             │ │
│  └────────────────────────────────────────────────────────────────┘ │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  表名.ibd (独立表空间) - 推荐使用                                 │ │
│  │  - 存储表数据和索引                                              │ │
│  │  - innodb_file_per_table = ON                                   │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                                                                       │
│  日志文件 (Log Files)                                                 │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  ib_logfile0, ib_logfile1 (Redo Log)                            │ │
│  │  - 保证事务的持久性                                              │ │
│  │  - 循环写入                                                       │ │
│  └────────────────────────────────────────────────────────────────┘ │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │  Undo Log (存储在 ibdata1 或 独立 undo 表空间)                   │ │
│  │  - 支持事务回滚                                                  │ │
│  │  - 实现 MVCC                                                     │ │
│  └────────────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────────┘

InnoDB 数据页结构

InnoDB 以页（Page）为单位管理数据，默认页大小为 16KB。

┌──────────────────────────────────────────────────────────────────────┐
│                       InnoDB 数据页 (16KB)                            │
├──────────────────────────────────────────────────────────────────────┤
│  File Header (38 bytes)                                              │
│  - 页号、上一页、下一页指针、校验和等                                  │
├──────────────────────────────────────────────────────────────────────┤
│  Page Header (56 bytes)                                              │
│  - 记录数、空闲空间指针、最后插入位置等                                │
├──────────────────────────────────────────────────────────────────────┤
│  Infimum + Supremum Records                                          │
│  - 最小记录和最大记录（虚拟记录）                                      │
├──────────────────────────────────────────────────────────────────────┤
│  User Records                                                        │
│  - 实际的数据行                                                        │
│  - 通过链表连接（有序）                                                │
├──────────────────────────────────────────────────────────────────────┤
│  Free Space                                                          │
│  - 空闲空间                                                            │
├──────────────────────────────────────────────────────────────────────┤
│  Page Directory                                                      │
│  - 页目录（稀疏索引），加速页内查找                                    │
├──────────────────────────────────────────────────────────────────────┤
│  File Trailer (8 bytes)                                              │
│  - 校验和（用于检测页是否损坏）                                        │
└──────────────────────────────────────────────────────────────────────┘

---

存储引擎对比 ⭐⭐⭐⭐

InnoDB vs MyISAM

特性	InnoDB	MyISAM
事务	支持	不支持
锁	行级锁	表级锁
外键	支持	不支持
MVCC	支持	不支持
崩溃恢复	支持（通过 redo log）	不支持，易损坏
索引类型	聚簇索引（主键索引）	非聚簇索引
全文索引	5.6+ 支持	支持
适用场景	OLTP（在线事务处理）	OLAP（只读分析，已不推荐）
并发性能	高（行锁）	低（表锁）
存储空间	需要更多空间（存储索引和数据）	占用空间小

使用建议：

• 99% 的场景使用 InnoDB

• MyISAM 已不推荐使用（不支持事务、崩溃易损坏）

• 如需全文检索，可使用 InnoDB（5.6+）或 Elasticsearch

InnoDB vs Memory

特性	InnoDB	Memory
存储位置	磁盘	内存
持久化	持久化	不持久化（重启丢失）
事务	支持	不支持
锁	行级锁	表级锁
适用场景	通用	临时表、缓存

Memory 引擎注意事项：

-- 创建 Memory 表
CREATE TABLE temp_data (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=Memory;

-- 问题：服务器重启后数据丢失
-- 解决方案：使用 Redis 等外部缓存

---

如何选择存储引擎 ⭐⭐⭐

决策流程

需要事务支持？
  ├─ 是 → InnoDB
  └─ 否 → 继续判断
           ├─ 只读分析场景 → 考虑列式存储（ClickHouse）或 InnoDB
           ├─ 临时数据 → Memory
           └─ 归档数据 → Archive

查看和修改存储引擎

-- 查看数据库支持的存储引擎
SHOW ENGINES;

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看表使用的存储引擎
SHOW CREATE TABLE users;

-- 修改表的存储引擎
ALTER TABLE users ENGINE = InnoDB;

-- 创建表时指定存储引擎
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10, 2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

面试要点 ⭐⭐⭐⭐⭐

高频问题

Q1: MySQL 的架构分为几层？

• Server 层：连接器、分析器、优化器、执行器

• 存储引擎层：InnoDB、MyISAM、Memory 等

Q2: InnoDB 和 MyISAM 的区别？

• 事务：InnoDB 支持，MyISAM 不支持

• 锁：InnoDB 行级锁，MyISAM 表级锁

• 外键：InnoDB 支持，MyISAM 不支持

• 崩溃恢复：InnoDB 支持，MyISAM 不支持

• 索引：InnoDB 聚簇索引，MyISAM 非聚簇索引

Q3: InnoDB 的 Buffer Pool 有什么作用？

• 缓存数据页和索引页，减少磁盘 I/O

• 使用改进的 LRU 算法管理

• 建议设置为物理内存的 50%-80%

Q4: 什么是聚簇索引？

• 数据按主键顺序存储在叶子节点

• InnoDB 默认使用聚簇索引

• 主键查询性能高，但二级索引需要回表

Q5: InnoDB 如何保证崩溃恢复？

• 通过 redo log（重做日志）保证已提交事务不丢失

• 通过 undo log（回滚日志）支持事务回滚和 MVCC

Q6: 为什么 MySQL 8.0 移除了查询缓存？

• 命中率极低（SQL 必须完全相同）

• 失效频繁（任何写操作都会清空缓存）

• 维护成本高

Q7: 优化器如何选择索引？

• 基于成本估算（扫描行数、是否排序、是否使用临时表）

• 通过索引统计信息（SHOW INDEX）估算扫描行数

• 可能选择错误，需要通过 EXPLAIN 分析

常见误区

误区1：认为 MyISAM 比 InnoDB 快

• 真相：MyISAM 只在全表扫描场景下可能更快，但不支持事务和行锁，并发性能差

误区2：Buffer Pool 越大越好

• 真相：过大会导致启动慢、内存不足；建议不超过物理内存的 80%

误区3：以为每个 SQL 都会走优化器

• 真相：存储过程、触发器内部的 SQL 也会走优化器

---

参考资料

1. 官方文档：MySQL 8.0 Reference Manual - InnoDB Storage Engine

2. 书籍推荐：

   • 《高性能 MySQL》（第4版）

   • 《MySQL 技术内幕：InnoDB 存储引擎》（姜承尧）

3. 博客文章：

   • InnoDB 存储引擎详解

   • MySQL 架构设计