MySQL 技术要点

一、MySQL 架构

MySQL 采用分层架构设计，主要分为 Server 层 和 存储引擎层。

1.1 Server 层

1.2 存储引擎层

MySQL 支持插件式存储引擎，常用引擎对比：

InnoDB architecture

二、索引机制

2.1 索引数据结构

2.2 索引分类

按物理存储：

• 聚簇索引（Clustered Index）：叶子节点存储完整行数据，InnoDB 主键索引即聚簇索引
• 二级索引（Secondary Index）：叶子节点存储主键值，查询需回表

按字段特性：

• 主键索引：唯一且非空，一张表只能有一个
• 唯一索引：字段值唯一，允许 NULL
• 普通索引：无唯一性约束
• 前缀索引：对字符串前 N 个字符建立索引，节省空间

按字段数量：

• 单列索引：单个字段
• 联合索引：多个字段组合，遵循最左前缀原则

2.3 普通索引 vs 唯一索引

• 普通索引可利用 Change Buffer 优化写入，数据不在内存时直接写入 Change Buffer
• 唯一索引必须读取数据页判断唯一性，无法使用 Change Buffer
• 写入 Change buffer, 避免加载冷数据（按页加载，即使修改一个数据，也至少加载16kb）挤出热点数据，造成Buffer Pool Pollution

2.4 优化器选错索引的处理

-- 重新统计索引信息
ANALYZE TABLE t;

-- 强制使用指定索引
SELECT * FROM t FORCE INDEX(idx_name) WHERE name = 'test';

三、事务

3.1 ACID 特性

3.2 隔离级别

*InnoDB 在 RR 级别下通过 Next-Key Lock 解决了大部分幻读问题。

3.3 MVCC（多版本并发控制）

MVCC 通过为每行数据维护多个版本，实现非阻塞的一致性读：

• 隐藏列：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
• Read View：记录活跃事务列表，决定数据可见性（RC 级别每次读取时创建，RR 级别仅首次读取时创建）
• 版本链：通过 Undo Log 构建历史版本链表

3.4 当前读与快照读

-- 快照读：读取 MVCC 快照版本
SELECT * FROM t WHERE id = 1;

-- 当前读：读取最新版本并加锁
SELECT * FROM t WHERE id = 1 FOR UPDATE;        -- 排他锁
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁

四、锁机制

4.1 锁分类

锁
├── 全局锁
│   └── FTWRL (Flush Tables With Read Lock)
├── 表级锁
│   ├── 表锁 (LOCK TABLES)
│   ├── 元数据锁 (MDL)
│   └── 意向锁 (IS/IX)
└── 行级锁 (InnoDB)
    ├── 记录锁 (Record Lock)
    ├── 间隙锁 (Gap Lock)
    └── 临键锁 (Next-Key Lock)

4.2 行锁加锁规则

InnoDB 行锁的加锁规则：

1. 加锁基本单位是 Next-Key Lock（前开后闭区间）
2. 只对访问到的对象加锁
3. 唯一索引等值查询命中时，Next-Key Lock 退化为行锁
4. 等值查询向右遍历到不满足条件的记录时，Next-Key Lock 退化为间隙锁

(5,10,15)
select * from t where id>=10 and id<11 for update;
mysql 8.0.18及之后，修复了会锁住id=15的问题，加锁为[10,15)，之前为[10,15]。

4.3 幻读与间隙锁

幻读定义：同一事务中，后一次查询看到了前一次查询没看到的行（专指新插入的行，不包括更新和删除）。

解决方案：间隙锁 + 行锁 = Next-Key Lock

例如，对表 t（主键值为 0, 5, 10, 15, 20, 25）执行 SELECT * FROM t FOR UPDATE，会形成以下 Next-Key Lock：

• (-∞, 0], (0, 5], (5, 10], (10, 15], (15, 20], (20, 25], (25, +supremum]

注意：LOCK IN SHARE MODE 仅锁覆盖索引，而 FOR UPDATE 会同时锁定主键索引上满足条件的行。

五、日志系统

5.1 日志类型

5.2 Binlog 格式

InnoDB crash 的本质是：内存中的脏页没刷盘，Binlog不记录物理级别的数据页修改进度，一条sql语句涉及多个数据页(数据，索引，页分裂)修改，crash恢复时不知道哪些数据页更新了，所以不支持 crash recovery。

Redo Log 具有LSN（Log Sequence Number），一个全局递增的日志位置编号，用来标记“日志写到了哪里”。

5.3 两阶段提交

为保证 Redo Log 和 Binlog 数据一致性，采用两阶段提交：

1. Prepare 阶段：Redo Log 写入并标记为 prepare 状态
2. Commit 阶段：Binlog 写入 → Redo Log 标记为 commit

组提交优化：多个事务的日志合并写入，减少磁盘 I/O。

5.4 Doublewrite

InnoDB 使用 Doublewrite Buffer 解决部分页写入（Partial Page Write）问题：

1. 先将脏页写入 Doublewrite Buffer（顺序写）
2. 再将脏页写入数据文件（随机写）
3. 崩溃恢复时，若数据页损坏，可从 Doublewrite Buffer 恢复

redolog：一个sql语句修改多个数据页，redolog 记录哪些数据页修改了，哪些没修改。

doublewrote：一个数据页对应四个 linux 页，doublewrite 保证一个数据页不会出现部分写入。

六、主从复制

6.1 复制流程

主库：事务提交 → 写 Binlog
   ↓
从库 I/O 线程：读取主库 Binlog → 写入 Relay Log
   ↓
从库 SQL 线程：重放 Relay Log → 更新数据

6.2 主备延迟原因

• 从库机器性能较差
• 从库承担过多查询压力
• 大事务执行时间长(delete大量数据,大表DDL(gh-ost解决))
• 网络延迟

6.3 处理过期读

七、查询执行分析

7.1 执行计划分析（EXPLAIN）

Explain extra 字段解释

• Using index: 使用了覆盖索引，避免回表查询

• Using filesort: order by 后面的字段没有索引，分为全字段排序和 rowid 排序

• Using index condition: 使用了索引下推（index conditionpushdown）,利用二级索引提前过滤，减少回表次数。

  SQL

  (age,city)
  select * from tb where age>5 and city="aaa" and name = "abc";
  -- age可以用到索引,city用到索引下推，回表之前就过滤到部分数据。

• Using join buffer: Block Nested Loop，Batched-key access(BKA)

• Using MRR: 用到 Multi-Range Read 优化，批量回表查询，以id递增顺序回表以实现顺序读。

  SQL

  (a)
  select * from tb where a>5 and a<10;
  -- 根据索引a得到一些结果，根据id排序，按id递增的方式回表查询

• Using temporary: union 或 group by

7.2 慢查询诊断

-- 查看当前执行的 SQL 状态
SHOW PROCESSLIST;

-- 查看行锁等待（MySQL 8.0+）
SELECT * FROM sys.innodb_lock_waits;

查询长时间不返回的原因：

• 等待 MDL（Metadata Lock）
• 等待 Flush
• 等待行锁

查询慢的原因：

• 快照读需遍历长版本链
• 扫描行数过多
• 索引选择不当
• 客户端接收慢（MySQL 边读边发，导致 net_buffer 阻塞）

7.3 JOIN 算法

• MRR：将随机 I/O 转为顺序 I/O，先读取索引排序后再回表
• BKA：用到 join buffer，NLJ->BKA（在被驱动表建立索引，如果不适合建立索引，就使用临时表，在临时表上建立索引），基于 MRR，批量将驱动表数据传递给被驱动表查询

7.4 ORDER BY 优化

最优情况：利用索引有序性，EXPLAIN 不出现 Using filesort。

filesort 算法（无索引时）：

MySQL 根据 max_length_for_sort_data 自动选择：行宽超过该值时使用 Rowid 排序。

7.5 外部排序

当 Sort Buffer 不足时，MySQL 使用外部排序：

• 归并排序：分批排序写入临时文件，最后多路归并
• 优先队列：ORDER BY ... LIMIT N 场景，维护 N 个元素的堆，避免全量排序

7.6 内存数据结构与临时表选择

• 如果语句执行过程可以一边读数据，一边直接得到结果，不需要额外内存来保存中间结果；

• join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构

• 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。

八、Buffer Pool 与刷脏页

8.1 性能抖动原因

• Redo Log 写满，触发 Checkpoint 刷脏页
• Buffer Pool 满，淘汰脏页需先刷盘
• 后台线程定期刷脏页

8.2 Buffer Pool LRU 优化

InnoDB 采用改进的 LRU 算法（Young 区:Old 区 = 5:3）防止缓存污染：

1. 新数据先放入 Old 区域
2. 在 Old 区域停留超过 innodb_old_blocks_time 后再次访问，才移入 Young 区域
3. 防止全表扫描等操作将热点数据挤出缓存

九、常见问题与解决方案

9.1 删除数据不释放空间

-- DELETE 仅标记删除，空间不会立即释放
DELETE FROM t WHERE create_time < '2024-01-01';

-- 重建表释放空间
ALTER TABLE t ENGINE = InnoDB;

9.2 COUNT 性能

性能从低到高：count(字段) < count(id) < count(1) ≈ count(*)

count(*) 经过优化器特殊优化，推荐使用。

9.3 临时表

触发场景：UNION 去重、GROUP BY 无索引、复杂子查询。

9.4 分区表

分区表在引擎层分区，对业务透明。优势：ALTER TABLE t DROP PARTITION p_2023 比 DELETE 更快。

适用场景：按时间分区的日志表、历史数据表。

9.5 数据迁移

参考资料：MySQL 实战 45 讲、《高性能 MySQL》、从一条慢SQL说起：交易订单表如何做索引优化