MySQL 数据库深入原理

MySQL 是最常用的关系型数据库。理解其内部原理，是写出高性能 SQL、排查线上问题的基础。

一、MySQL 架构

1.1 整体架构

连接层
  ↓
SQL 接口 → 解析器 → 优化器 → 执行器
  ↓
存储引擎层（InnoDB / MyISAM / Memory）
  ↓
文件系统层

1.2 连接层

连接池：管理客户端连接
    ↓
连接认证：用户名、密码、连接数限制
    ↓
每个连接一个线程

1.3 Server 层组件

1.4 存储引擎

InnoDB 是 MySQL 默认引擎，原因：

• 支持事务（ACID）
• 行级锁，并发性能好
• MVCC 解决读写冲突
• Crash Safe（崩溃恢复）
• 支持外键

二、InnoDB 存储引擎

2.1 内存结构

Buffer Pool（缓冲池）
  ├── 数据页（默认 16KB）
  ├── 索引页
  ├── 自适应哈希索引
  └── 锁信息
      ↓
Change Buffer（写缓冲）
  └── 优化非唯一索引的写入
      ↓
Log Buffer（日志缓冲）
  └── Redo Log 写入前缓冲

2.2 磁盘结构

表空间（ibdata1）
  ├── 数据页
  ├── 索引页
  ├── Double Write Buffer（双写缓冲）
  └── Undo Log（回滚日志）
      ↓
Redo Log（ib_logfile0, ib_logfile1）
  └── 事务提交前的日志

2.3 数据页结构

Page（16KB）
  ├── File Header（38字节）：页号、上一页、下一页、checksum
  ├── Page Header（56字节）：记录数、空闲空间起始
  ├── Infimum + Supremum：行记录最小/最大值
  ├── User Records：实际数据行
  ├── Free Space：空闲空间
  └── File Trailer（8字节）：校验和

三、索引原理

3.1 B+Tree 为什么是 MySQL 的选择

B+Tree 优势：

• 叶子节点链表，便于范围查询
• 非叶子节点不存数据，每页可存更多 key → 更矮、更宽
• 所有查询最终到叶子节点，性能稳定

3.2 InnoDB 索引实现

聚簇索引（Clustered Index）
  └── 主键索引，叶子节点存完整数据行

二级索引（Secondary Index）
  └── 非主键索引，叶子节点存主键值
        ↓
  查询时：先查二级索引得到主键 → 再查聚簇索引（回表）

3.3 索引匹配规则

-- 假设有索引 idx_name_age (name, age)

-- ✅ 完全匹配
SELECT * FROM users WHERE name = 'zhangsan' AND age = 25;

-- ✅ 最左前缀匹配
SELECT * FROM users WHERE name = 'zhangsan';

-- ✅ 匹配列值前缀
SELECT * FROM users WHERE name LIKE 'zhang%';

-- ❌ 不满足最左前缀
SELECT * FROM users WHERE age = 25;

-- ❌ 中间断层
SELECT * FROM users WHERE name = 'zhangsan' AND city = 'Beijing';
-- city 中断了，无法使用索引

3.4 索引失效场景

-- ❌ 左括号前
SELECT * FROM users WHERE SUBSTR(name, 1, 3) = 'zhang';

-- ❌ 类型转换
SELECT * FROM users WHERE name = 123;  -- name 是 varchar

-- ❌ 函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ❌ != / NOT IN / NOT EXISTS
SELECT * FROM users WHERE status != 'active';
SELECT * FROM users WHERE name NOT IN ('a', 'b');

-- ✅ 范围查询后失效
SELECT * FROM users WHERE name = 'zhang' AND age > 20;
-- age > 20 后，age 之后的索引列失效

-- ✅ LIMIT 后面仍然可能用索引
SELECT * FROM users WHERE name = 'zhang' LIMIT 100;

3.5 覆盖索引

查询所有列都在索引中，无需回表：

-- 假设有联合索引 idx_name_age (name, age)

-- ✅ 覆盖索引：不需要回表
SELECT name, age FROM users WHERE name = 'zhangsan';

-- ❌ 非覆盖索引：需要回表
SELECT * FROM users WHERE name = 'zhangsan';

3.6 索引下推（Index Condition Pushdown）

MySQL 5.6+ 优化，在索引遍历时过滤数据，减少回表：

-- 有索引 idx_name_age (name, age)

SELECT * FROM users WHERE name LIKE 'zhang%' AND age = 25;

-- 无 ICP：先按 name 查到所有记录，再回表，在 server 层过滤 age
-- 有 ICP：在索引层直接过滤 age，减少回表次数

四、SQL 优化

4.1 EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE name = 'zhangsan';

-- 关键字段：
-- type: ALL（全表扫描）, ref（索引查找）, range（范围）
-- key: 实际使用的索引
-- rows: 预计扫描行数
-- Extra: Using index（覆盖索引）, Using filesort（文件排序）, Using temporary（临时表）

4.2 慢查询优化步骤

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 2. 查看慢查询
SHOW FULL PROCESSLIST;

-- 3. 分析执行计划
EXPLAIN SELECT ...

-- 4. 优化方向：
--    - 索引优化
--    - SQL 重写（减少子查询、用 JOIN 替代）
--    - 分库分表

4.3 分页优化

-- ❌ 深度分页：跳过了大量数据
SELECT * FROM orders LIMIT 1000000, 10;

-- ✅ 优化1：延迟关联
SELECT * FROM orders
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS t2 USING (id);

-- ✅ 优化2：游标分页（推荐）
SELECT * FROM orders
WHERE id > #{lastId}
ORDER BY id
LIMIT 10;

4.4 JOIN 优化

-- 小表驱动大表（MySQL 优化器自动选择）
-- 但可以用 STRAIGHT_JOIN 强制指定

SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;
-- orders 驱动 users（固定先扫 orders）

-- 索引要求：ON 的列必须有索引
-- 多表 JOIN：控制数量（不超过 5 张）

五、主从复制

5.1 复制原理

主库（Master）
  └── 事务提交 → 写入 Binlog
                       ↓
              Dump Thread 读取 Binlog → 发送给从库
                       ↓
从库（Slave）
  └── I/O Thread 接收 → 写入 Relay Log
                       ↓
              SQL Thread 读取 Relay Log → 重放执行

5.2 复制模式

5.3 GTID 复制

传统 Binlog 复制：
  - 依赖文件名 + 位置
  - 主从切换后难以定位新主从的同步点

GTID（Global Transaction Identifier）：
  - 每个事务有唯一 ID
  - 自动记录已执行的事务
  - 主从切换后自动定位同步点

5.4 读写分离

写入请求 → 主库
读取请求 → 从库（负载均衡）

延迟问题：

主库写入 → 从库复制（有延迟）
    ↓
读取到旧数据（主从不一致）

解决方案：

• 强制走主库（重要数据）
• 延迟读取（业务层延迟）
• 缓存读取（读请求先查缓存）

六、分库分表

6.1 什么时候需要分库分表

6.2 分片策略

6.3 分片后的问题

七、日志系统

7.1 Binlog（逻辑日志）

记录内容：所有 DDL 和 DML（逻辑变化）
  - INSERT/UPDATE/DELETE
  - CREATE/DROP/ALTER

用途：
  - 主从复制
  - 数据恢复
  - 增量备份

格式：
  - STATEMENT：记录 SQL 语句
  - ROW：记录每行变化（推荐）
  - MIXED：混合模式

7.2 Redo Log（物理日志）

记录内容：数据页的物理变化（物理变化）
  - "某偏移量写入 XX 字节"

用途：
  - Crash Safe（崩溃恢复）
  - WAL（Write-Ahead Logging）：先写日志再写数据

刷盘策略：
  - innodb_flush_log_at_trx_commit
  - 1：事务提交时刷盘（最安全）
  - 2：提交时写到 OS 缓存
  - 0：每秒刷盘

7.3 Undo Log（回滚日志）

记录内容：数据修改前的值
  - INSERT → 整行记录
  - UPDATE → 修改前的值
  - DELETE → 整行记录

用途：
  - 事务回滚
  - MVCC（读取历史版本）

八、常见问题

8.1 为什么用自增主键

-- 自增主键的好处：
-- 1. 插入时顺序追加，不频繁页分裂
-- 2. 二级索引存储空间小（主键值更小）
-- 3. 范围查询性能好

-- 为什么不建议用 UUID：
-- 1. 插入无序，频繁页分裂
-- 2. 字符串比整数大，索引存储空间大

8.2 CHAR vs VARCHAR

8.3 为什么不建议使用外键

-- 外键的问题：
-- 1. 级联更新/删除影响多表，性能差
-- 2. 并发时容易死锁
-- 3. 水平扩展时外键难处理

-- 建议：
-- 在应用层保证数据一致性
-- 外键用于数据库约束而非业务逻辑

8.4 COUNT(*) vs COUNT(1) vs COUNT(列)

-- InnoDB 优化后三者性能差不多
-- 但 COUNT(*) 会处理 NULL
-- COUNT(列) 跳过 NULL 值

-- 最快：EXPLAIN SELECT COUNT(*) FROM orders;
-- 实际读取最小：主键索引最小

8.5 MySQL 8.0 新特性