# 架构与存储引擎

> 理解 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 连接
* 验证用户名和密码
* 读取用户权限信息（保存在内存中，直到连接断开）
* 管理连接（长连接/短连接）

**查看连接**：

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

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

**长连接问题**：

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

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

#### 2. 查询缓存（已废弃）

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

* **工作原理**：将 SQL 作为 key，结果作为 value 缓存
* **失效机制**：表的任何更新操作都会清空该表所有缓存
* **废弃原因**：
  * 命中率极低（SQL 必须完全相同才能命中）
  * 维护成本高（频繁失效）
  * 不适合写密集型应用

#### 3. 分析器

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

```sql
SELECT id, name FROM users WHERE age > 18;

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

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

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

#### 4. 优化器 ⭐⭐⭐⭐⭐

**核心职责**：

* 选择使用哪个索引
* 决定多表 JOIN 的顺序
* 生成执行计划

**优化器决策示例**：

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

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

**查看执行计划**：

```sql
EXPLAIN SELECT * FROM users WHERE age > 18;
```

#### 5. 执行器

**执行流程**：

1. 判断用户对表是否有执行权限
2. 打开表（调用存储引擎接口）
3. 根据执行计划调用存储引擎接口获取数据
4. 返回结果给客户端

```sql
-- 执行器调用存储引擎的伪代码
// 打开表
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 配置**：

```sql
-- 查看 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 引擎注意事项**：

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

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

***

## 如何选择存储引擎 ⭐⭐⭐

### 决策流程

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

### 查看和修改存储引擎

```sql
-- 查看数据库支持的存储引擎
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](https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html)
2. **书籍推荐**：
   * 《高性能 MySQL》（第4版）
   * 《MySQL 技术内幕：InnoDB 存储引擎》（姜承尧）
3. **博客文章**：
   * [InnoDB 存储引擎详解](https://www.cnblogs.com/)
   * [MySQL 架构设计](https://tech.meituan.com/)