侯瑞哲的博客

MySQL 深度解析

MySQL 是世界上使用最广泛的开源关系型数据库。本文从存储引擎内核出发，深入解析索引原理、事务与锁、MVCC、查询优化器、主从复制等核心机制，并结合生产环境中的常见问题给出实践建议。

InnoDB 存储引擎架构

内存结构

InnoDB 的内存由两部分组成：

Buffer Pool（缓冲池）：InnoDB 最核心的内存结构，默认 128MB，生产环境通常设为物理内存的 60%~80%。所有数据页、索引页的读写都经过 Buffer Pool，避免频繁的磁盘 I/O。

Buffer Pool 使用改良的 LRU 算法管理页面淘汰：将 LRU 链表分为热数据区（young）和冷数据区（old），新读入的页先放入 old 区，只有在 old 区停留超过 1 秒后再次被访问，才会晋升到 young 区。这样防止全表扫描把热数据从缓存中挤出。

Change Buffer：缓存对非唯一二级索引的写操作。当目标数据页不在 Buffer Pool 中时，不立即读入磁盘，而是将变更记录在 Change Buffer，等数据页被读入时再合并（merge）。减少了随机 I/O。

Log Buffer：redo log 的内存缓冲区，默认 16MB。事务提交时，Log Buffer 中的内容会根据 innodb_flush_log_at_trx_commit 的配置决定是否立即刷盘。

磁盘结构

表空间（Tablespace）：InnoDB 的数据存储单元。系统表空间（ibdata1）存储数据字典和 undo log；独立表空间（.ibd 文件）每张表一个文件，便于管理。

数据页（Page）：InnoDB 的最小 I/O 单位，默认 16KB。一个页存储多行数据，页内数据按主键有序排列。

redo log：物理日志，记录数据页的修改。InnoDB 采用 WAL（Write-Ahead Logging）策略，先写 redo log，再写数据页。即使数据库崩溃，重启时可以通过 redo log 恢复未刷盘的数据。redo log 是循环写入的固定大小文件（默认两个文件，各 48MB）。

undo log：逻辑日志，记录数据修改前的值，用于事务回滚和 MVCC。undo log 存储在 undo 表空间中，事务提交后不会立即删除，需要等待没有更早的 Read View 引用它时才能被 purge 线程清理。

索引原理

B+ Tree 索引结构

InnoDB 使用 B+ Tree 作为索引数据结构。B+ Tree 的特点：

  • 所有数据存储在叶子节点，非叶子节点只存键值用于导航
  • 叶子节点之间通过双向链表连接，支持高效的范围查询
  • 树高通常为 3~4 层，每次查询最多 3~4 次磁盘 I/O

以一棵三层 B+ Tree 为例，假设每个节点 16KB，键值 8 字节，指针 6 字节：

  • 非叶子节点可存约 1170 个键值
  • 叶子节点可存约 16 行数据（每行 1KB）
  • 三层树可存：1170 × 1170 × 16 ≈ 2200 万行

聚簇索引与二级索引

聚簇索引（Clustered Index）：InnoDB 的主键索引，叶子节点存储完整行数据。每张表只有一个聚簇索引。如果没有显式定义主键，InnoDB 会选择第一个非空唯一索引，或者自动生成一个隐藏的 6 字节 rowid 作为聚簇索引。

二级索引（Secondary Index）：非主键索引，叶子节点存储索引列值 + 主键值。通过二级索引查询时，先找到主键值，再回到聚簇索引查询完整数据，这个过程称为回表。

-- 通过二级索引查询，需要回表
SELECT * FROM users WHERE age = 25;

-- 覆盖索引：查询的列都在索引中，不需要回表
SELECT id, age FROM users WHERE age = 25;

索引优化原则

最左前缀原则：联合索引 (a, b, c) 可以用于 a、(a, b)、(a, b, c) 的查询，但不能跳过前面的列直接用 b 或 c。

-- 索引 (name, age, city)
SELECT * FROM users WHERE name = 'Alice';              -- 使用索引
SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- 使用索引
SELECT * FROM users WHERE age = 25;                    -- 不使用索引（跳过 name）
SELECT * FROM users WHERE name = 'Alice' AND city = 'Beijing'; -- 只用 name 部分

索引下推（ICP）：MySQL 5.6 引入，将 WHERE 条件中能用索引过滤的部分下推到存储引擎层，减少回表次数。

索引失效场景：

  • 对索引列使用函数或运算：WHERE YEAR(create_time) = 2026
  • 隐式类型转换：字符串列与数字比较，WHERE phone = 13812345678（phone 是 varchar）
  • 使用 !=、NOT IN、NOT EXISTS（低选择性时优化器可能不走索引）
  • LIKE 以通配符开头：WHERE name LIKE '%Alice'
  • OR 连接的条件，其中一个没有索引

事务与锁

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL InnoDB 默认使用 Repeatable Read（可重复读）：

  • Read Uncommitted：可以读到未提交的数据，存在脏读问题
  • Read Committed：只能读到已提交的数据，解决脏读，但存在不可重复读
  • Repeatable Read：同一事务内多次读取结果一致，解决不可重复读，InnoDB 通过 MVCC 和间隙锁解决了幻读
  • Serializable：完全串行执行，性能最差

锁的类型

行锁：InnoDB 的基本锁粒度，分为：

  • Record Lock（记录锁）：锁定单行记录
  • Gap Lock（间隙锁）：锁定索引记录之间的间隙，防止幻读。只在 Repeatable Read 及以上级别生效
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定记录本身及其前面的间隙。InnoDB 默认的行锁类型

表锁：

  • 意向锁（Intention Lock）：表级别的锁，在加行锁之前先加意向锁，用于快速判断表上是否有行锁，避免逐行检查
  • MDL（Metadata Lock）：保护表结构，DML 操作自动加 MDL 读锁，DDL 操作加 MDL 写锁

死锁

InnoDB 有死锁检测机制，发现死锁后会自动回滚代价较小的事务。死锁检测的时间复杂度为 O(n²)，高并发下可能成为性能瓶颈。

常见死锁场景：两个事务以相反顺序锁定同一组行。

-- 事务 A
BEGIN;
UPDATE orders SET status = 1 WHERE id = 1;  -- 锁定 id=1
UPDATE orders SET status = 1 WHERE id = 2;  -- 等待 id=2

-- 事务 B（同时执行）
BEGIN;
UPDATE orders SET status = 1 WHERE id = 2;  -- 锁定 id=2
UPDATE orders SET status = 1 WHERE id = 1;  -- 等待 id=1，死锁！

避免死锁的原则：固定加锁顺序、缩短事务、减少锁的粒度。

MVCC 多版本并发控制

MVCC 是 InnoDB 实现高并发读写的核心机制，读操作不加锁，通过读取历史版本数据实现一致性读。

版本链

每行数据有两个隐藏字段：

  • trx_id：最后修改该行的事务 ID
  • roll_pointer：指向 undo log 中上一个版本的指针

每次修改数据，旧版本被记录到 undo log，新版本的 roll_pointer 指向旧版本，形成版本链。

Read View

事务在执行快照读时，会创建一个 Read View，包含：

  • m_ids：当前活跃（未提交）的事务 ID 列表
  • min_trx_id：活跃事务中最小的 ID
  • max_trx_id：下一个将要分配的事务 ID
  • creator_trx_id：创建该 Read View 的事务 ID

判断某个版本是否对当前事务可见：

如果 trx_id == creator_trx_id → 可见（自己修改的）
如果 trx_id < min_trx_id → 可见（已提交的旧事务）
如果 trx_id >= max_trx_id → 不可见（未来的事务）
如果 trx_id 在 m_ids 中 → 不可见（活跃未提交的事务）
否则 → 可见（已提交的事务）

RC 隔离级别每次 SELECT 都创建新的 Read View；RR 隔离级别只在事务第一次 SELECT 时创建 Read View，后续复用，保证同一事务内读取结果一致。

查询优化器

执行计划

使用 EXPLAIN 查看查询的执行计划，关键字段：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
  • type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL
  • key：实际使用的索引
  • rows：预估扫描行数
  • Extra：附加信息，Using index 表示覆盖索引，Using filesort 表示需要额外排序，Using temporary 表示使用了临时表

优化器选择索引的原理

MySQL 优化器基于代价模型（Cost Model）选择执行计划。代价 = I/O 代价 + CPU 代价。优化器会估算每种执行方案的代价，选择代价最低的方案。

优化器的判断依据是统计信息（cardinality，基数），通过 ANALYZE TABLE 更新。统计信息不准确时，优化器可能选错索引，可以用 FORCE INDEX 强制指定：

SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 100;

慢查询分析

开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

常见慢查询原因及优化：

  • 全表扫描：添加合适的索引
  • 回表过多：使用覆盖索引，或减少 SELECT 的列
  • 排序慢：在 ORDER BY 的列上建索引，避免 filesort
  • JOIN 慢：确保 JOIN 条件上有索引，小表驱动大表
  • 深度分页：LIMIT 100000, 10 需要扫描 10 万行，改用游标分页
-- 深度分页优化：用主键游标替代 OFFSET
-- 原始（慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 优化（快）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

主从复制

复制原理

MySQL 主从复制基于 binlog（二进制日志）：

  1. 主库将数据变更写入 binlog
  2. 从库的 IO 线程连接主库，拉取 binlog 写入 relay log（中继日志）
  3. 从库的 SQL 线程读取 relay log，重放 SQL 语句

binlog 有三种格式：

  • Statement：记录 SQL 语句，日志量小，但某些函数（NOW()、RAND()）可能导致主从不一致
  • Row：记录每行数据的变化，主从一致性好，但日志量大
  • Mixed：默认用 Statement，遇到不安全的语句自动切换为 Row

MySQL 8.0 推荐使用 Row 格式，配合 GTID（全局事务 ID）管理复制位点。

主从延迟

主从延迟是生产环境的常见问题，主要原因：

  • 从库单线程回放（MySQL 5.6 以前），主库并发写入时从库跟不上
  • 大事务：一个执行 10 分钟的事务，从库也需要 10 分钟回放
  • 从库机器性能差于主库

解决方案：

  • MySQL 5.7+ 开启并行复制：slave_parallel_workers = 8
  • 拆分大事务为小事务
  • 主从机器配置保持一致
  • 读写分离时，对实时性要求高的读操作走主库

生产实践

表设计规范

  • 主键使用自增整数，避免使用 UUID（UUID 是随机的，会导致 B+ Tree 频繁页分裂）
  • 字段尽量定义为 NOT NULL，NULL 值会增加索引存储开销和查询复杂度
  • VARCHAR 长度按实际需要设置，不要无脑设 VARCHAR(255)
  • 时间字段用 DATETIME 而非 TIMESTAMP（TIMESTAMP 有 2038 年问题，且受时区影响）
  • 金额字段用 DECIMAL，不要用 FLOAT/DOUBLE（浮点精度问题）

连接池配置

MySQL 每个连接消耗约 1MB 内存，连接数过多会导致 OOM。生产环境推荐：

max_connections = 1000          # 最大连接数
innodb_buffer_pool_size = 12G   # 物理内存的 60%~80%
innodb_flush_log_at_trx_commit = 1  # 每次提交刷盘（最安全）
sync_binlog = 1                 # 每次提交同步 binlog（最安全）

应用侧使用连接池（如 HikariCP），连接池大小建议：核心数 × 2 + 有效磁盘数，通常 10~20 个连接已足够大多数场景。

常见问题排查

锁等待超时：

-- 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看事务持锁情况
SELECT * FROM information_schema.INNODB_TRX;

-- 杀掉阻塞的事务
KILL [trx_mysql_thread_id];

Buffer Pool 命中率低：

-- 查看 Buffer Pool 命中率（应 > 99%）
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads: 从磁盘读取的次数
-- Innodb_buffer_pool_read_requests: 总请求次数
-- 命中率 = 1 - reads / read_requests

主从数据不一致：

-- 查看主从复制状态
SHOW SLAVE STATUS\G
-- 关注 Seconds_Behind_Master（延迟秒数）
-- 和 Last_Error（错误信息）

总结

MySQL 的核心是 InnoDB 存储引擎，理解 Buffer Pool、redo/undo log、B+ Tree 索引、MVCC 这四个机制，基本就掌握了 MySQL 的工作原理。

生产环境中，90% 的 MySQL 问题来自于索引设计不合理和慢查询。定期分析慢查询日志、用 EXPLAIN 检查执行计划、合理设计联合索引，是保证 MySQL 稳定运行的基本功。

分布式场景下，主从复制提供了读写分离和高可用能力，但主从延迟是不可回避的问题，业务设计时需要考虑最终一致性的容忍度。