MySQL 存储引擎和索引结构原理详解（三）

MySQL 是一个关系型数据库管理系统（RDBMS），其性能和功能在很大程度上依赖于 存储引擎（Storage Engine） 和 索引结构（Index Structure） 的设计与实现。理解它们的原理，有助于优化数据库设计和查询性能。

---

一、MySQL 存储引擎概述

MySQL 支持多种存储引擎，每种存储引擎在数据存储、事务处理、索引实现等方面有所不同。

1. 常用存储引擎

存储引擎	特点	适用场景
InnoDB	支持事务（ACID）、行级锁、外键约束、MVCC	绝大多数 OLTP 场景
MyISAM	不支持事务、表级锁、全文索引支持较好	读多写少、统计分析场景
MEMORY	数据存储在内存中，速度快	临时表、缓存表
CSV	数据存储为 CSV 文件，便于导入导出	数据交换场景
Archive	适合大量写入、只读操作	历史日志归档

2. 存储引擎的作用

存储引擎负责以下几个核心功能：

1. 数据存储与检索：将数据以特定格式存储在磁盘或内存。

2. 事务与并发控制：支持事务的 ACID 特性、锁机制、MVCC（多版本并发控制）。

3. 索引管理：支持不同类型的索引（B-Tree、Hash、全文索引等）。

4. 日志与恢复：如 InnoDB 的 redo log 和 undo log，用于崩溃恢复。

---

二、MySQL 存储引擎的工作原理

以 InnoDB 为例，其工作原理核心在以下几个方面：

1. 数据页与表空间

• InnoDB 将数据按 16KB 数据页 存储在表空间中。

• 数据页分为不同类型：

  • 数据页：存储表记录。

  • 索引页：存储 B+Tree 索引。

  • Undo 页：存储事务回滚信息。

  • 系统页：存储表结构、事务信息等。

当查询数据时，InnoDB 会先查缓存池（Buffer Pool），命中则直接返回，否则从磁盘读取到内存。

2. 锁机制与事务

• 行级锁：减少并发写冲突，提高并发性能。

• 多版本并发控制（MVCC）：

  • 每条记录包含隐藏的 事务 ID 和 回滚指针。

  • 读取数据时，MVCC 会选择对当前事务可见的版本，实现 一致性读。

• 事务日志：

  • Redo Log：保证事务提交后数据可恢复。

  • Undo Log：支持事务回滚和 MVCC。

---

三、MySQL 索引结构详解

索引是提高查询效率的关键。MySQL 支持多种索引结构。

1. B+Tree 索引

B+Tree 是最常用的索引结构，InnoDB 的 主键索引和普通索引 都基于 B+Tree。

• 特点：

  • 所有叶子节点按顺序连接，支持范围查询。

  • 非叶子节点仅存储关键字和指针，不存储实际数据（主键索引除外）。

• 原理：

  • 插入/删除操作会保持树的平衡（B+Tree 自平衡特性）。

  • 查询时从根节点出发，沿树向下查找叶子节点，实现 O(log n) 查询复杂度。

• InnoDB 的聚簇索引：

  • 主键索引叶子节点存储 整行数据。

  • 辅助索引叶子节点存储 主键指针，需通过主键回表访问数据。

2. Hash 索引

• 基于哈希表实现，查找速度接近 O(1)。

• 适合 等值查询，不支持范围查询。

• MEMORY 存储引擎默认使用 Hash 索引。

3. 全文索引

• 用于文本检索，将文本拆分为词项（Token）。

• 内部通过倒排索引存储词项和出现位置。

• 适合搜索文章、日志内容等。

4. 索引使用优化原则

1. 选择性原则：索引列的取值越唯一，索引效果越好。

2. 前缀索引：对长字符串列可使用前缀索引，节省空间。

3. 联合索引顺序：遵循最左前缀原则，优化多列查询。

4. 避免过度索引：索引会增加写入开销。

---

四、查询过程原理

以 InnoDB 为例，查询过程大致如下：

1. 解析与优化：

   • SQL 被解析成执行计划，选择最优索引。

2. 索引访问：

   • 根据索引定位数据页。

   • 若为辅助索引，需要回表获取完整行。

3. 数据缓存：

   • 数据页优先从 Buffer Pool 中读取，减少磁盘 I/O。

4. 事务与锁控制：

   • 确保查询的一致性和隔离性。

5. 返回结果：

   • 数据经过排序、过滤后返回客户端。