存储引擎

在 MySQL 中，存储引擎是真正负责数据存储与读取的底层组件。

MySQL 可以想象成一个三层结构：

1. 连接层

   负责客户端连接，权限认证等。

2. SQL 层

   负责解析 SQL、优化器、执行器，不关心数据怎么储存。

3. 存储引擎层

   不解析 SQL，只负责怎么把数据读写到磁盘里

   不同引擎有不同能力，是否支持事务，是否支持行锁，是否有 MVCC…..

常见存储引擎简介

MySQL 支持多种存储引擎，可按表级独立选择

1

CREATE TABLE users (...) ENGINE=InnoDB;

常见主要引擎有三种：

1. InnoDB—MySQL 默认且最强大的存储引擎

适用于：

• OLTP（高并发事务系统）
• 电商订单系统
• 用户账户系统
• 日志系统（写多读取也多）

为什么 InnoDB 是默认——————因为它是唯一同时支持事务 + 行级锁 + 崩溃恢复的通用存储引擎。

2. MyISAM—早期默认，现已淘汰

适用场景：

• 只读或报表型系统

• 历史数据归档

• 不需要事务的场景

为什么不推荐 MyISAM？

• 表锁导致并发性能差

• 崩溃后恢复能力弱

3. Memory—内存引擎

使用场景：

• 临时计算
• 排序中转表
• 会话缓存

避免用于：

• 要求数据可靠性的业务

核心对比总结

为什么大多数情况下必须用 InnoDB

因为它是唯一同时满足：

• 能保证数据安全性（事务，redo）
• 能支持高并发（行锁）
• 能支持一致性读（MVCC）
• 崩溃后不会丢数据

如何查看当前存储引擎

查看当前实例默认引擎

1

SHOW VARIABLES LIKE 'default_storage_engine';

查看某张表使用的引擎

1

SHOW TABLE STATUS LIKE 'users';

修改表的存储引擎

1

ALTER TABLE users ENGINE=InnoDB;

索引

索引是 MySQL 性能优化最重要的一环。

是否回报用索引，直接决定了一条 SQL 是毫秒级还是秒级甚至分钟级。

索引概述

1. 核心的作用

核心作用只有两个字：提速

更具体的说：

• 加速查询：
  避免全表扫描
  将 O(n) 的遍历，变成 O(log n) 的查找
  对 WHERE/JOIN/ORDER BY/GROUP BY 尤其重要

• 约束数据：
  主键索引：保证唯一且非空
  唯一索引：保证字段唯一性

本质：索引 = 用额外的数据结构，换取更快的查询速度

2. 索引的成本

索引有明确的代价。

• 空间成本
  每个索引都要占磁盘空间
  联合索引、前缀索引都会增加存储量

• 维护成本
  INSERT/UPDATE/DELETE 时
  不仅要改数据，还要维护索引结构
  索引越多，写操作越慢

索引结构

常见索引结构

数据库中常见的索引结构包括：

• B-Tree
• B+Tree
• Hash

MySQL InnoDB 引擎默认只使用 B+Tree

B-Tree 索引

基本结构：

• 多叉平衡树
• 每个节点同时存：Key, 数据
• 所有叶子节点在同一层

查找过程：

1. 从根节点开始
2. 按 Key 范围向下查找
3. 直到命中数据或失败

缺点:

• 非叶子节点也存数据
• 单页能存的 Key 数量减少
• 树会更高，磁盘 IO 次数增加

B+Tree 索引

为什么更适合磁盘存储：

1. 树更矮，非叶子节点只存 Key，单页能容纳更多索引项
2. 磁盘 IO 更少，查询通常 2~4 次 IO 就能完成
3. 范围查询性能极佳，只需要在叶子节点链表顺序扫描

Hash 索引

特点：

• 基于 Hash 表
• 等值查询 O(1)

限制：

• 不支持范围查询
• 不支持排序
• 不支持最左前缀
• 发生 Hash 冲突性能不稳定

Memory 引擎支持 Hash 索引，但 InnoDB 几乎不用 Hash 索引

索引分类

主键索引

• 唯一
• 非空
• 一张表只能有一个
• InnoDB 中：主键索引就是聚簇索引

唯一索引

• 保证唯一性
• 允许 NULL
• 常用于 email, 手机号等

普通索引 (INDEX)

• 最常见
• 不限制唯一性
• 纯粹用于加速查询

组合（联合）索引

1

CREATE INDEX idx_name_age ON users(name, age);

• 一个索引支持多个查询条件
• 遵循最左前缀法则
• 比多个单列索引更高效

索引创建语法

创建

1

CREATE INDEX idx_name ON users(name);

删除

1

DROP INDEX idx_name ON users;

建表时定义

1
2
3
4
5
6

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    INDEX idx_name_age (name, age)
);

通过 ALTER TABLE 添加索引

1

ALTER TABLE users ADD INDEX idx_age (age);

注意：ALTER：会重建索引，有锁表风险

索引使用规则与优化

1. 验证索引效率的思路

EXPLAIN 看是否走索引
rows 是否明显减少
是否出现 ALL（全表扫描）

2. 最左前缀法则

1

INDEX(a, b, c)

✔ a ✔ a + b ✔ a + b + c ❌ b ❌ b + c

3. 索引失效的常见情况

对索引列使用函数/计算
前缀模糊 LIKE '%xxx'
类型不匹配（隐式转换）

4. SQL 提示

1

SELECT * FROM users FORCE INDEX(idx_name) WHERE name='Tom';

仅在优化器选错索引、特殊调优场景使用。

5. 覆盖索引和回表

查询字段都在索引中 -> 覆盖索引

否则 -> 二级索引 + 回表

6. 前缀索引

1

CREATE INDEX idx_email ON users(email(10));

用于：长字符串，降低索引空间

8. 索引设计原则

高选择性字段优先
围绕查询条件设计
控制索引数量
主键使用自增整数

SQL 优化

SQL 优化的目标：用更少的资源，更快得得到结果

• 减少扫描行数
• 减少磁盘 IO
• 减少排序/临时表
• 减少锁冲突

插入数据优化

1. 批量插入

低效写法：

1
2
3

INSERT INTO orders VALUES (...);
INSERT INTO orders VALUES (...);
INSERT INTO orders VALUES (...);

高效写法：

1
2
3
4

INSERT INTO orders VALUES
(...),
(...),
(...);

为什么能优化：

• 减少 SQL 解析次数
• 减少 redo log/binlog 刷盘次数
• 显著提升写入吞吐量

2. 关闭/延迟索引

场景：
一次性导入大量历史数据

思路：

1. 先删索引
2. 插数据
3. 再建索引

1
2
3

ALTER TABLE orders DROP idx_xxx;
-- 插入 
ALTER TABLE orders ADD INDEX idx_xxx(col);

为什么能优化：

• 每插一行就维护索引，非常慢
• 统一建索引是顺序构建，快很多

要注意，生产环境慎用，操作期间索引不可用。

主键设计优化

推荐使用自增主键

1

id BIGINT AUTO_INCREMENT PRIMARY KEY

原因（InnoDB）：

• 主键即聚簇索引
• 数据即主键顺序存储

页分裂与主键顺序

随机主键的问题：

• 新数据插入到中间位置
• 触发页分裂
• 导致 IO 增多、索引碎片、性能下降

自增主键的好处：

• 始终追加到末尾
• 几乎不发生页分裂
• 写入性能稳定

ORDER BY 优化

1. 利用索引

1
2
3

SELECT dept_id, COUNT(*)
FROM emp
GROUP BY dept_id;

直接利用索引分组，避免临时表

2. 减少中间结果集

优化思路：

• 先 WHERE 再 GROUP BY
• 尽量缩小参与分组的数据量

错误思维：先全表分组再过滤

正确示例：

1
2
3
4

SELECT dept_id, COUNT(*)
FROM emp
WHERE status = 1
GROUP BY dept_id;

LIMIT 优化

大 offset 的性能问题：

1

LIMIT 1000000, 20

前 1000000 行会被扫描并丢弃，非常慢

优化方案：

子查询 + 索引

1
2
3
4
5
6
7
8

SELECT * 
FROM orders 
WHERE id >= (
    SELECT id FROM orders
    ORDER BY id
    LIMIT 1000000, 1
)
LIMIT 20;

前提：id 是主键，或者有索引

优化本质：

• 利用索引定位起始位置
• 避免大量无效扫描

COUNT 优化

InnoDB：必须扫描数据，不维护行数缓存

计数缓存策略

常见做法：

• 单独维护一张计数表
• 或 Redis 缓存总数
• 写操作时同步更新计数

要避免频繁对大表做 COUNT(*)

UPDATE 优化

避免行锁升级为表锁

错误示例：

1
2

UPDATE orders
SET status = 1;

问题：

• 无 WHERE 条件
• 锁全表

正确写法：

1
2
3

UPDATE orders
SET status = 1
WHERE id = 100;

同时确保：

• WHERE 条件走索引
• 否则仍可能扫描大量行->锁范围扩大

批量 UPDATE 拆分

1
2
3

UPDATE orders
SET status = 1
WHERE id BETWEEN 1 ANS 1000;

拆成多次：

• 减少锁持有时间
• 降低并发冲突

视图

视图是 MySQL 提供的一种虚拟表机制，它本身不存数据，而是封装一条 SQL 查询，在使用时动态执行

视图介绍c

什么是视图

即一条被保存下来的 SELECT 语句

特点：

• 不存储真实数据
• 数据来源于基表
• 查询视图 约等于 查询其内部的 SELECT

可以把视图理解为：

• 给复杂的 SQL 起一个名字
• 对表结构的一层抽象封装

使用场景

1. 简化查询

复杂 SQL：

1
2
3
4

SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 1;

封装后为：

1

SELECT * FROM v_user_order;

优点：

• SQL 更简洁
• 减少重复代码
• 降低出错概率

2. 权限控制 只暴露部分字段给某些用户

1
2

CREATE VIEW v_user_public AS
SELECT id, name FROM users;

授权给普通用户

1

GRANT SELECT ON v_user_public TO 'guest';

3. 逻辑复用

• 报表统计
• 常用业务查询
• 多个系统共享同一查询逻辑

把查询逻辑从应用层，抽到应用层

基本语法

1. 创建视图

1
2
3
4

CREATE VEIW v_active_users AS
SELECT id, name
FROM users
WHERE status = 1;

2. 修改视图

1
2
3
4

CREATE OR REPLACE VIEW v_active_users AS
SELECT id, name, email
FROM users
WHERE status = 1;

也可使用 ALTER

1
2

ALTER VIEW v_active_users AS
SELECT ...

3. 删除视图

1

DROP VIEW v_active_users

视图检查选项

视图允许通过视图对数据进行写操作，但这可能破坏视图本身的逻辑约束

CHECK OPTION 用来解决这个问题

CHECK OPTION 的作用

保证通过视图插入/更新的数据，必须满足视图的 WHERE 条件

示例视图：

1
2
3
4
5

CREATE VIEW v_active_users AS
SELECT * 
FROM users
WHERE status = 1
WITH CHECK OPTION;

CASCADED VS LOCAL

CASCADED(默认)：

• 检查 当前视图+依赖的所有视图
• 限制更严格

1

WITH CASCADED CHECK OPTION;

LOCAL:

• 只检查当前视图
• 不检查底层视图条件

1

WITH LOCAL CHECK OPTION;

为什么需要 CHECK OPTION

没有 CHECK OPTION:

1
2

INSERT INTO v_active_users (id, name, status)
VALUES (10, 'Tom', 0);

插入成功，但查询视图时看不到这条数据

开启 CHECK OPTION 后：

• 该写操作会被拒绝
• 保证视图语义一致性

视图更新和作用

可更新视图

满足以下条件之一，视图通常是可更新的：

• 基于单表
• 不包含：
  • 聚合函数（COUNT/SUM）
  • GROUP BY
  • DISTINCT
  • UNION
• 不包含子查询

示例：

1
2

CREATE VIEW v_users AS
SELECT id, name FROM users;

1

UPDATE v_users SET name='Jack' WHERE id = 1;

更新了 users 表

不可更新表

以下通常不可更新

1
2
3
4

CREATE VIEW v_stat AS
SELECT dept_id, COUNT(*)
FROM emp
GROUP BY dept_id;

因为是聚合结果，无法映射会具体行

存储过程

存储过程介绍

什么是存储过程？

存储过程 = 一组预编译的 SQL + 控制逻辑，存放在数据库中

特点：

• 一次创建，多次调用
• 可以包含判断，循环
• 可接受参数，返回结果

存储过程的优点

1. 封装

• 把复杂 SQL 封装在数据库中
• 应用层只负责调用

2. 复用

• 多个系统/模块复用同一逻辑
• 避免复制粘贴 SQL

3. 降低网络开销

• 一次调用，数据库内部执行多条 SQL
• 减少客户端 <-> 数据库的往返次数

常见使用场景：

• 批量数据处理
• 复杂业务规则
• 定时任务（配合事件）

基本语法

创建存储过程

1
2
3
4
5
6
7
8

DELIMITER $$

CREATE PROCEDURE p_test()
BEGIN
    SELECT 'hello';
END $$

DELIMITER ;

调用：

1

CALL p_test;

变量

1. 系统变量

MySQL 内置变量，用于控制系统行为

以两个 @ 开头，根据作用域可分为全局系统变量和回话系统变量

• @@global.xxx

• @@session.xxx

2. 用户变量

以一个 @ 开头，用户自定义的临时变量，用于存储中间结果或传递数据，仅在当前回话中有效，断开自动销毁

1
2

SET @count = 10;
SELECT @count;

3. 局部变量

只能在 BEGIN...END 内使用，必须先 DECLARE

1

DECLARE total INT DEFAULT 0;

流程控制

1. IF/CASE

IF 示例：

1
2
3
4
5

IF score >= 60 THEN
  SET result = 'pass';
ELSE
  SET result = 'fail';
END IF;

CASE 示例：

1
2
3
4
5

CASE level
  WHEN 1 THEN SET bonus = 100;
  WHEN 2 THEN SET bonus = 200;
  ELSE SET bonus = 0;
END CASE;

2. 循环结构

WHILE:

1
2
3
4

WHILE i <= 10 DO
  SET sum = sum + i;
  SET i = i + 1;
END WHILE;

REPEAT(至少执行一次)：

1
2
3
4

REPEAT
  SET i = i + 1;
UNTIL i > 10
END REPEAT;

LOOP(需手动退出)：

1
2
3
4
5

loop_label: LOOP
  IF i > 10 THEN
    LEAVE loop_label;
  END IF;
END LOOP;

参数类型

IN（输入参数，默认）

1
2
3
4

CREATE PROCEDURE p_add(IN a INT, IN b INT)
BEGIN
  SELECT a + b
END;

OUT (输出参数)

1
2
3
4

CREATE PROCEDURE p_sum(IN a INT, IN b INT, OUT s INT)
BEGIN
  SET s = a + b;
END;

调用：

1
2

CALL p_sum(1, 2, @res);
SELECT @res;

INOUT (输入 + 输出)

1
2
3
4

CREATE PROCEDURE p_inc(INOUT x INT)
BEGIN
  SET x = x + 1;
END;

游标（CURSOR）

用于逐行遍历查询结果集

使用步骤：

1. 声明游标
2. 声明结束标志
3. 打开游标
4. 循环读取
5. 关闭游标

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

DECLARE v_id INT;
DECLARE done INT DEFAULT 0;

DECLARE cur CURSOR FOR
SELECT id FROM users;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

read_loop: LOOP
  FETCH cur INTO v_id;
  IF done = 1 THEN 
    LEAVE read_loop;
  END IF;
  -- 处理逻辑  
END LOOP;

CLOSE cur;

注意：

• 游标性能较差
• 能用 SQL 解决的，尽量不用游标

条件处理程序（HANDLER）

用于处理异常/特殊情况

常见用途：

• 游表读完
• SQL 执行异常
• 自定义错误处理

示例：异常捕获

1
2

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET error_flag = 1;

有两种类型：

• CONTINUE: 不中断，继续执行
• EXIT：直接终止过程

存储函数与存储过程的区别

示例：

1
2
3
4
5

CREATE FUNCTION f_add(a INT, b INT)
RETURNS INT
BEGIN
  RETURN a + b;
END;

调用：

1

SELECT f_add(1, 2);

触发器

触发器是一种由数据库自动触发执行的程序，不需要显式调用，只要对表发生特定操作，就会执行对应逻辑。

介绍

什么是触发器：

触发器 = 绑定在表上的自动执行程序

特点：

• 与表强绑定
• 由数据库自动触发
• 对应用层透明（应用无感知）

触发时机：(由两个维度决定)

1. 触发事件：

• INSERT
• UPDATE
• DELETE

2. 触发时刻：

• BEFORE
• AFTER

NEW/OLD 关键词

在触发器中访问数据：

• NEW.column
  INSERT/UPDATE 后的新值

• OLD.column
  UPDATE/DELETE 后的新值

常见场景

日志记录

示例：

• 订单状态变化日志
• 用户信息修改记录

优点：

• 自动执行
• 不依赖应用代码
• 不会被忘记写

审记 (Audit)

• 记录：

  • 修改人
  • 修改时间
  • 修改前/后的值

• 满足合规/风控要求

特点： 必须执行、不可绕过 -> 非常适合触发器

INSERT / UPDATE / DELETE 触发器案例

INSERT 触发器

场景：插入前校验或补充字段

1
2
3
4
5
6

CREATE TRIGGER trg_user_insert
BEFORE INSERT ON users
FOR EACH ROW  -- 行级触发  
BEGIN
  SET NEW.create_time = NOW();
END;

UPDATE 触发器

场景：记录修改日志

1
2
3
4
5
6
7

CREATE TRIGGER trg_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN 
  INSERT INTO user_log(user_id, old_name, new_name, op_time)
  VALUES (old.id, OLD.name, NEW.name, NOW());
END;

DELETE 触发器

场景：删除前备份数据

1
2
3
4
5
6
7

CREATE TRIGGER trg_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_backup
  SELECT * FROM users WHERE id = OLD.id;
END;

一个表，一个事件，一个时机 -> 只能有一个触发器

与业务逻辑的取舍

什么时候适合用触发器：

• 日志
• 审计
• 强一致性校验
• 必须执行，不可绕过的规则

什么时候不适合用：

• 核心业务逻辑
• 复杂流程控制
• 依赖外部服务（HTTP/MQ）

原因：

• 触发器隐式执行
• 难调试，难排查
• 性能问题不直观
• 应用层不可控

视图 & 存储过程 & 触发器小结

使用原则总结

1. 视图

• 用于查询
• 权限控制
• 不写复杂逻辑

2. 存储过程/函数

• 数据处理
• 计算逻辑
• 辅助型逻辑

3. 触发器

• 日志
• 审计
• 强制一致性

锁机制

锁的本质：在并发环境下，保证数据一致性

当多个事务同时读写同一份数据时，如果没有锁：

• 数据会被覆盖
• 读到脏数据
• 业务结果不可控

锁，就是数据库解决并发问题的核心手段

锁概述

为什么需要锁

并发事务可能产生的问题：

锁 + 事务隔离级别 = 解决方案

锁的分类维度

MySQL 的锁，可以从多个维度理解：

1. 作用范围

• 全局锁
• 表级锁
• 行级锁

2. 锁的意图

• 共享锁 (S)
• 排它锁 (X)

3. InnoDB 特有

• 意向锁
• 间歇锁
• 临键锁

全局锁

什么是全局锁：

整个数据库只读，所有写操作被阻塞

加锁命令：

1

FLUSH TABLES WITH READ LOCK;

释放锁：

1

UNLOCK TABLES;

使用场景

全库一致性备份：

• 保证备份过程中数据不被修改
• 常用于逻辑备份 (mysqldump)

特点：

• 影响范围极大
• 持续时间要尽量短
• 生产环境慎用

表级锁

表锁

特点:

• 锁住整张表
• 并发性能低
• MyISAM 主要使用

示例:

1
2

LOCK TABLE users READ;
LOCK TABLE users WRITE;

元数据锁(MDL)

MDL 是自动加的锁, 看不到, 但一定存在

触发时机:

• 对表做 CRUD -> 加 MDL 读锁
• 对表做 DDL (ALTER) -> 需要 MDL 写锁

常见事故:

一个长事务不提交 占着 MDL 读锁 ALTER TABLE 一直卡住

这是线上 DDL 卡死的最常见原因

意向锁

意向锁是表级锁, 用于配合行锁使用

• 意向共享锁 (IS)
• 意向排它锁 (IX)

作用:

告诉数据库: 这张表即将或正在被加行锁

避免:

• 每次加表锁都去遍历行锁
• 大幅提升性能

行级锁

行锁

• 锁住具体某一行
• 并发性能最高
• InnoDB 默认使用

示例

1

SELECT * FROM users WHERE id = 10 FOR UPDATE;

前提:
必须走索引, 否则可能退化成表级锁

间歇锁

锁住索引之间的区间, 不锁具体行

目的:

• 防止其他事务在区间内插入新数据
• 用于解决幻读

示例:

1

SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;

不允许插入 id = 7 的新记录

临键锁

行锁 + 间歇锁的组合

• 锁住当前行
• 同时锁住前后的间歇

这是 InnoDB 在可重复读下的默认策略

锁与索引的关系

1. 锁是加在索引上的

InnoDB 中:

• 没有索引 -> 锁全表
• 有索引 -> 精确锁行

如错误示例:

1

UPDATE users SET status = 1 WHERE name = 'Tom';

如果 name 没有索引:

• 扫描全表
• 锁大量行甚至整表

2. 索引设计 = 锁粒度设计

好的索引 -> 锁行少, 并发高
坏的索引 -> 锁范围大, 容易阻塞, 死锁

死锁

什么是死锁

两个事务互相等待对方释放锁

形成了死锁

MySQL 如何处理死锁

• InnoDB 自动检测
• 回滚代价最小的事务
• 报错:

  1	Deadlock found when trying to get lock

如何减少死锁

• 统一加锁顺序
• 减少事务范围
• 尽量走索引
• 减少长事务

小结

1. InnoDB 的优势在于行锁
2. 锁是否精确, 取决于索引
3. RR 隔离级别下:

• 行锁
• 间歇锁
• 临键锁

4. MDL 是线上 DDL 卡死的常见原因

InnoDB 引擎内部原理

InnoDB 逻辑存储结构

InnoDB 并不是直接把一行数据写到文件里, 而是有一套层级化的逻辑存储结构:

表空间（Tablespace） └── 段（Segment） └── 区（Extent） └── 页（Page） └── 行（Row）

1. 表空间

• 存储表数据 + 索引数据
• 常见形式:
  • 共享表空间(ibdata1, 旧)
  • 独立表空间(*.ibd, 默认, 推荐)

2. 段

• 一个段通常对应:

  • 一个聚簇索引段
  • 若干个二级索引段

• 段是 InnoDB 分配空间的逻辑单位

3. 区

• 每个区大小: 1MB
• 一个区包含:
  • 1MB / 16KB = 64 页
• 目的: 减少磁盘碎片, 提高顺序 IO 性能

4. 页

• InnoDB 最小 IO 单位
• 默认大小: 16KB
• 常见页类型:
  • 数据页 (B+Tree 节点)
  • Undo 页
  • 系统页
  • 索引页

一次读取是 16KB, 不是一行

5. 行

• 真正的数据记录
• 行中包含:
  • 用户定义字段
  • 隐藏字段 (用于事务和 MVCC)

InnoDB 架构

        ┌─────────────┐
        │   Buffer    │
        │   Pool      │
        └─────┬───────┘
              │
    ┌─────────┴─────────┐
    │   后台线程（IO）   │
    └─────────┬─────────┘
              │
  ┌───────────┴───────────┐
  │     磁盘文件（ibd）    │
  └───────────────────────┘

内存结构

Buffer Pool (缓冲池)

• InnoDB 性能核心
• 功能:
  • 缓存 数据页和索引页
  • 所有读写 先走内存

读数据流程:

1. 先查 Buffer Pool
2. 命中 -> 直接返回
3. 未命中 -> 从磁盘加载页 -> 放入 Buffer Pool

Buffer Pool 越大, 磁盘 IO 越少

Change Buffer (变更缓冲)

• 针对二级索引
• 当二级索引页不在内存时:
  • 先把变更写到 Change Buffer
  • 后续再合并

提升写性能

Log Buffer

• 缓存 redo log
• 提交事务前, 先写 log buffer
• 再刷盘 (受参数控制)

磁盘结构

表空间文件 (*.ibd)

• 存储:
  • 数据
  • 索引
  • undo 页 (部分)

redo log

• 物理日志
• 用于 崩溃恢复

undo log (逻辑上)

• 存储回滚信息
• 支持事务回滚 和 MVCC

后台线程

• 刷新脏页
• 合并 Change Buffer
• 写 redo log
• IO 调度

应用线程只负责 提交任务, 脏活累活交给后台线程

事务原理 (redo log & undo log)

redo log (重做日志) — 保证持久性

作用:

即使 MySQL 宕机, 已提交事务的数据也不会丢失

核心思想:

• 先写日志, 再写磁盘 (WAL)
• redo log 是物理日志

提交流程:

1. 修改 Buffer Pool 中的数据页
2. 生成 redo log (内存)
3. redo log 刷盘
4. 事务提交成功
5. 后台线程慢慢刷脏页

事务提交 != 数据页落盘

undo log (回滚日志)

作用:

• 事务回滚
• MVCC 的版本链

特点:

• 逻辑日志
• 记录:
  • 修改前的日志
• 位于:
  • undo tablespace

MVCC (多版本并发控制)

InnoDB 解决读写并发冲突的核心机制

行的隐藏字段

每一行都有三个隐藏字段

undo log 版本链

1
2
3
4
5

当前行
  ↓
undo log（上一个版本）
  ↓
undo log（更早版本）

一行数据 = 多个历史版本

Read View

Read View 决定:

当前事务能看到哪一版本的数据

包含:

• 活跃事务 ID 列表
• 最小/最大事务 ID

RC & RR 下的 MVCC 行为

RC (读已提交)

• 每次 SELECT 都生成新的 Read View
• 可能出现
  • 不可重复读

RR (可重复读, 默认)

• 事务第一次 SELECT 时生成 Read View
• 之后复用
• 避免:
  • 不可重复读
  • 大部分幻读 (配合间歇锁)