揭秘MySQL脏页

最新推荐文章于 2026-01-21 17:00:53 发布

原创最新推荐文章于 2026-01-21 17:00:53 发布 · 1k 阅读

14 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #android #数据库

一、什么是脏页

要理解脏页，首先要了解MySQL InnoDB存储引擎的几个关键设计：

缓冲池（Buffer Pool）：这是InnoDB在内存中开辟的一块核心区域，用来缓存从磁盘读取的数据页（Data Page）。几乎所有数据操作（读、写）都在这里进行，目的是为了减少对慢速磁盘的直接访问，极大提升性能。
数据页（Data Page）：InnoDB管理数据的基本单位（通常是16KB）。当你增删改数据时，你实际上是在修改缓冲池中的数据页，而不是直接修改磁盘上的数据页。
脏页（Dirty Page）：当一个数据页从磁盘被加载到缓冲池后，如果它在内存中被修改了（例如执行了UPDATE操作），那么此时内存中的数据页版本就比磁盘上的数据页版本更新。这个在内存中被修改过、但尚未写回磁盘的数据页，就被称为“脏页”。

简单比喻：

缓冲池 就像你的电脑桌面，你正在处理的工作文件都放在桌面上，访问速度快。
磁盘就像你的文件柜，用于永久存储文件。
数据页 就是你处理的一份份文件。
脏页就是你已经在桌面上修改过内容，但还没来得及存回文件柜的那份文件。

二、为什么产生了脏页

脏页的存在，是MySQL在追求极致性能和保证数据持久性之间做出的一个经典权衡。它不是Bug，而是一个精心设计的核心特性。主要原因有以下几点：

1. 性能优化：随机IO vs. 顺序IO

这是最根本的原因。如果每次事务提交时，都立即将修改对应的数据页同步到磁盘（随机写操作），性能会极其低下，因为磁盘的随机写速度很慢。

为了解决这个问题，InnoDB引入了 Write-Ahead Logging (WAL) 预写日志 机制。WAL的核心思想是：在数据页写回磁盘之前，先把对数据页的修改记录到一个顺序追加写的日志文件中（即Redo Log）。

Redo Log（重做日志）：它是物理日志，记录的是“在某个数据页上做了什么修改”。它是顺序写入的，速度非常快。
工作流程：
1. 事务修改缓冲池中的数据页，产生脏页。
2. 事务提交时，只需将对应的Redo Log记录持久化到磁盘的Redo Log文件即可（顺序写，速度快）。
3. 此时事务就算完成了，系统可以告诉客户端“操作成功”。
4. 至于被修改的脏页，InnoDB会找一个合适的时机，批量地将它们刷新回磁盘的数据文件中。
5. 通过这种方式，MySQL将一次实时的、慢速的随机写磁盘，转换成了两次快速的写操作：一次快速的顺序写Redo Log，和一次后台的延迟随机写数据页。脏页就是这个延迟写入过程中的必然产物。

2. 刷盘时机的触发条件

既然脏页迟早要刷回磁盘，那么什么时候刷呢？InnoDB会在以下几种情况下触发刷脏页（Flush）操作：

Redo Log写满了：这是最需要避免的情况。Redo Log是循环复用的文件。当写指针追上了擦除指针，表示没有新的空闲空间可以写入新的Redo Log。此时必须停下来，强制将一部分脏页刷到磁盘，从而释放出一部分Redo Log空间。这个过程会阻塞所有新的更新操作，对性能影响很大。
- 缓冲池（Buffer Pool）不足：当需要从磁盘加载新的数据页到缓冲池，但缓冲池没有空闲空间时，会根据LRU（最近最少使用）算法淘汰掉一些旧的页。如果被淘汰的页是脏页，那么必须先将它刷到磁盘，才能淘汰。
- 系统空闲时：MySQL会在系统比较空闲的时候，主动刷一些脏页，以减轻高峰期的压力。
- MySQL关闭时：正常关闭时，需要将所有脏页刷回磁盘，保证数据一致性。
检查点（Checkpoint）：InnoDB会定期推进检查点LSN，这也会触发脏页的刷新，目的是为了缩短数据库恢复的时间（因为恢复只需要从最后一个Checkpoint之后的Redo Log开始应用即可）。

3. 产生脏页的时间节点

在内存中修改数据页（产生脏页）的情况非常普遍，几乎所有数据变更操作都会触发：

显式的数据操作：
- UPDATE table SET ... WHERE ...
- INSERT INTO table ...
- DELETE FROM table ...
隐式的数据变更：
- 更新索引（因为索引也是以页的形式存放在缓冲池里的）。
- 变更系统数据字典（例如，你新建一个表，相关的元信息也会在内存中修改）。
- 由于 UNDO 日志本身也存储在回滚段中，修改 UNDO 页也会在缓冲池中产生脏页。

三、mysql的事务和数据持久化

1、事务未提交时，数据的写入逻辑

结论： 事务未提交时，修改内容在内存中

当执行 UPDATE、INSERT、DELETE 等操作时，这些修改首先发生在内存的 缓冲池（Buffer Pool） 中。
具体流程如下：
1. 从磁盘找到需要修改的数据页，加载到缓冲池中。
2. 在缓冲池中修改这个数据页的内容。此时，这个数据页就变成了 “脏页”。
3. 同时，InnoDB会生成对应的 重做日志（Redo Log） 和 回滚日志（Undo Log）。
- Redo Log：记录的是物理变化（“在某个数据页上做了什么修改”），用于保证持久性。
- Undo Log：记录的是修改前的旧数据镜像，用于事务回滚和实现MVCC（多版本并发控制）。

关键点： 在事务提交之前，你对数据的所有修改都只存在于内存（缓冲池）和日志文件中，磁盘上的原始数据页完全没有被触动。

2、事务提交时，数据写入逻辑

**结论：**事务提交时，写入内存中的数据不一定会写入磁盘；即会产生“脏页”

将本次事务产生的所有 Redo Log 记录持久化到磁盘的 Redo Log 文件。 这是一个顺序、追加写的操作，速度非常快。
一旦 Redo Log 成功落盘，MySQL 就认为事务已经提交成功，可以向客户端返回 “OK”。

注意：此时，被修改的"脏页"（即内存中那个最新的数据页）并没有被写回磁盘的数据文件（.ibd文件）！

为什么这样做？
这又是为了极致的性能。如果每次提交都要把对应的数据页（随机IO）写回磁盘，数据库会慢得无法使用。通过先写 Redo Log（顺序IO）这个"代表"，MySQL 将一次慢速的随机写，转换成了两次快速的写操作（先顺序写 Redo Log，后延迟随机写数据页），从而实现了高性能和高持久性的平衡。

四、脏页的作用

既然事务提交只写了Redo Log，那脏页的存在还有什么意义？内存里的数据不是随时会丢吗？

这里的关键在于理解 Redo Log 和数据页的关系：

Redo Log 是 “操作日志”：它记录的是"如何把数据页从状态A变成状态B"的过程。它体积小，是顺序写入的。
数据页是 “最终结果”：它是数据的最终载体。

脏页是 “最新结果” 在内存中的缓存。 它的存在是为了：

提供读取服务：后续的 SELECT 查询可以直接从内存中的脏页读取到最新数据，而无需等待数据页刷盘，也无需通过 Redo Log 去重构数据，性能极高。
合并多次修改：一个数据页在内存（脏页）期间，可能会被同一个或多个事务修改多次。如果每次修改都立即刷盘，IO 压力巨大。而脏页允许将这些修改"攒"在一起，最后由后台线程一次性写回磁盘，大大减少了 IO 次数。
异步刷盘的效率：将多个随机写合并，在系统空闲时或必要时批量执行，远比同步的、一次一次的随机写要高效得多。

总结一下关系：

Redo Log 保证了： 即使服务器断电，内存中的脏页丢失，我依然能通过重放 Redo Log，将磁盘上的旧数据页"恢复"到事务提交后的最新状态。
脏页保证了： 在系统正常运行期间，所有读写操作都能获得最高的性能。

所以，脏页和 Redo Log 并不矛盾，而是协作关系：Redo Log 是数据的"安全绳"，而脏页是性能的"加速器"。

五、mysql如何确认数据写入内存/RedoLog

mysql如何确认数据是否在内存中

MySQL通过缓冲池（Buffer Pool）的管理结构来跟踪：

页表（Page Table）：缓冲池维护着一个哈希表，通过表空间ID + 页号可以快速查找数据页是否在缓冲池中。
控制块（Control Blocks）：每个缓冲池页都有一个控制块，记录页的状态信息：
- 是否脏页（is_dirty）
- 最近访问时间
- 页的LSN（Log Sequence Number）
- 引用计数等

查看方法：

-- 查看缓冲池状态
SHOW ENGINE INNODB STATUS\G

-- 或者查询information_schema
SELECT * FROM information_schema.INNODB_BUFFER_PAGE_LRU 
WHERE TABLE_NAME = 'your_table';

确认Redo Log哪些操作已持久化

通过LSN（Log Sequence Number）机制：

LSN：一个单调递增的64位整数，每个Redo Log记录、每个数据页都有对应的LSN
关键LSN位置：
- Log_flushed_lsn：已刷新到Redo Log文件的LSN
- Checkpoint_lsn：已刷新到数据文件的LSN（检查点）
- Page_lsn：每个数据页最后被修改时的LSN

判断规则：

如果Page_lsn ≤ Checkpoint_lsn：该页修改已持久化到数据文件
如果Checkpoint_lsn < Page_lsn ≤ Log_flushed_lsn：修改在Redo Log中，但数据页还是脏页
如果Page_lsn > Log_flushed_lsn：理论上不应该发生（事务未提交）

查看方法

SHOW ENGINE INNODB STATUS\G
-- 在LOG部分可以看到LSN信息

六、mysql存在脏页时的查询逻辑处理

由于存在“脏页”，mysql查询数据时可能会存在部分数据在磁盘中，部分数据在内存中的情况。

当执行全表扫描或带条件的范围查询时：

查询优化器不知道数据在内存还是磁盘 - 它只生成执行计划
存储引擎按需加载：
- 首先在缓冲池中查找需要的数据页
- 如果不在缓冲池中，从磁盘读取到缓冲池
- 如果缓冲池已满，根据LRU算法淘汰旧页（如果是脏页，先刷盘）
具体流程示例：

假设表有1000个数据页，查询需要扫描所有页：
```
SELECT * FROM big_table WHERE some_condition;
```
1. 第1-100页：已在缓冲池中（包含脏页）
2. 第101页：不在缓冲池 → 从磁盘加载
3. 缓冲池已满 → 淘汰第1页（如果是脏页，异步刷盘）
4. 重复过程：不断加载新页，淘汰旧页
5. 最终：查询接触过的页都在缓冲池中，但可能已被后续查询淘汰

注意：查询是只读操作，不会将数据从脏页刷新至磁盘。只有在缓冲池已满，需要腾出空间加载新页时，被淘汰的页恰好是脏页，才会执行将脏页出具持久化至磁盘的操作，然后再淘汰脏页

七、mysql的预读

缓冲池不是简单地把所有数据加载进来，也不是只加载查询的那一行数据。它的优化基于计算机科学的黄金法则：

数据访问的局部性原理

空间局部性：当程序访问一个数据时，它很可能在不久的将来访问附近的数据
时间局部性：当程序访问一个数据时，它很可能在不久的将来再次访问这个数据

缓冲池的实际工作方式

不是加载单行数据，而是加载整个数据页（通常是16KB）

-- 假设执行：SELECT * FROM users WHERE id = 123;

实际发生的过程：

找到id=123所在的数据页（比如页号5）
将整个16KB的页5加载到缓冲池
从页5中读取id=123这一行数据

为什么这样优化？
因为用户很可能接下来会查询：

SELECT * FROM users WHERE id = 124;  -- 很可能在同一数据页
SELECT * FROM users WHERE id = 125;  -- 很可能在同一数据页
SELECT * FROM users WHERE email LIKE 'john%'; -- 可能涉及同一页的其他数据

缓冲池的智能预读机制

缓冲池不仅仅是被动加载，还有主动预读：

1. 线性预读

当顺序访问多个连续页时（比如全表扫描）
访问页1 → 缓冲池预加载页2,3,4,5
访问页2 → 已经在缓冲池，快速返回
访问页3 → 已经在缓冲池，快速返回

2. 随机预读

当检测到某个区域被频繁访问时
频繁访问页10,12,15 → 缓冲池预加载周围的页9,11,13,14,16

缓冲池大小的合理配置

缓冲池不需要容纳所有数据，只需要容纳热点数据：

## 配置建议
innodb_buffer_pool_size = 总内存的50-80%
## 监控热点数据大小
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';

缓冲池的真正价值

不是加载所有数据：只加载被访问的数据页

不是只加载查询行：加载整个16KB数据页，利用空间局部性
核心优化：通过一次磁盘IO服务后续的多次内存访问
智能预测：通过预读机制提前加载可能被访问的数据
淘汰策略：使用LRU等算法保留热点数据，淘汰冷数据

简单来说：缓冲池的意义在于将"每次查询都要磁盘IO"变成了"只有第一次需要磁盘IO，后续相同或邻近数据的查询都是内存访问"。

这就是为什么即使只加载查询的特定数据，缓冲池也能带来巨大性能提升的原因。它本质上是一个智能的磁盘IO缓存系统，而不是简单的数据加载器；这也就解释了为什么一个有时候可能第一次执行一个查询sql耗时较长，在第二次执行sql时相比第一个耗时明显减少。

八、利用预读机制优化sql性能

两种预读类型

-- 查看预读相关参数
SHOW VARIABLES LIKE 'innodb_read_ahead%';
/*
innodb_read_ahead_threshold: 线性预读阈值
innodb_random_read_ahead: 随机预读开关
*/

主动设计查询利用预读

顺序范围查询优化

-- 原查询（可能无法充分利用预读）
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY customer_id;  -- 按customer_id排序，访问模式随机

-- 优化：按顺序访问模式设计
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY order_date, order_id;  -- 按物理存储顺序访问

利用分页优化查询

-- 传统分页（越往后越慢）
SELECT * FROM large_table 
ORDER BY create_time 
LIMIT 10000, 20;  -- 需要扫描10020行

-- 优化：利用预读的"游标分页"
SELECT * FROM large_table 
WHERE create_time > '2024-01-01 00:00:00'  -- 使用上一页最后一条的时间
ORDER BY create_time 
LIMIT 20;  -- 顺序扫描，预读机制可以提前加载后续数据

表结构优化和索引设计优化预读

聚簇索引顺序存储

-- 良好的聚簇索引设计
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_date, sale_id),  -- 按日期顺序存储
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

-- 这样按日期范围的查询可以充分利用预读
SELECT * FROM sales 
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';
-- 数据物理上连续存储，预读效率高

覆盖索引减少随机IO

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders (customer_id, order_date, total_amount);

-- 查询可以直接从索引获取数据，减少数据页访问
SELECT customer_id, order_date, total_amount 
FROM orders 
WHERE customer_id = 123 
  AND order_date >= '2024-01-01';
-- 索引页顺序访问，预读效果好

参数调优优化预读效果

调整预读参数

-- 查看当前预读设置
SHOW VARIABLES WHERE Variable_name LIKE '%read_ahead%';

-- 调整线性预读阈值（默认56，范围0-64）
SET GLOBAL innodb_read_ahead_threshold = 48;  -- 更早触发预读

-- 启用随机预读（默认OFF）
SET GLOBAL innodb_random_read_ahead = ON;

根据工作负载调整

-- OLAP场景（分析型，大量顺序扫描）
SET GLOBAL innodb_read_ahead_threshold = 32;  -- 降低阈值，更积极预读
SET GLOBAL innodb_random_read_ahead = ON;     -- 启用随机预读

-- OLTP场景（事务型，随机访问）
SET GLOBAL innodb_read_ahead_threshold = 56;  -- 较高阈值，避免过度预读  
SET GLOBAL innodb_random_read_ahead = OFF;    -- 关闭随机预读

查询模式优化

批量顺序处理

-- 避免大量随机单条查询
-- 不佳做法：
SELECT * FROM users WHERE user_id = 1;
SELECT * FROM users WHERE user_id = 2;
SELECT * FROM users WHERE user_id = 3;

-- 优化：批量顺序查询
SELECT * FROM users WHERE user_id IN (1, 2, 3) ORDER BY user_id;
-- 或者使用范围查询
SELECT * FROM users WHERE user_id BETWEEN 1 AND 1000 ORDER BY user_id;

预加载热点数据

-- 应用启动时预加载热点数据
SELECT * FROM config_table;  -- 配置表
SELECT * FROM category_table; -- 分类表
SELECT * FROM hot_products LIMIT 1000; -- 热销商品

-- 这些查询会触发预读，为后续查询预热缓冲池

监控预读效果

查看预读统计

-- 查看预读相关统计信息
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

/*
Innodb_buffer_pool_read_ahead：预读的页数
Innodb_buffer_pool_read_ahead_evicted：预读但被淘汰的页数
Innodb_buffer_pool_read_requests：逻辑读请求
Innodb_buffer_pool_reads：物理读次数
*/

-- 计算预读效率
SELECT 
    variable_name,
    variable_value
FROM information_schema.GLOBAL_STATUS 
WHERE variable_name IN (
    'Innodb_buffer_pool_read_ahead',
    'Innodb_buffer_pool_read_ahead_evicted',
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads'
);

分析预读命中率

-- 计算预读有效率
SELECT 
    ROUND(
        (1 - 
            (SELECT variable_value 
             FROM information_schema.GLOBAL_STATUS 
             WHERE variable_name = 'Innodb_buffer_pool_read_ahead_evicted'
            ) / 
            NULLIF(
                (SELECT variable_value 
                 FROM information_schema.GLOBAL_STATUS 
                 WHERE variable_name = 'Innodb_buffer_pool_read_ahead'
                ), 0
            )
        ) * 100, 2
    ) as pre_read_hit_rate;

九、脏页的影响

性能波动：刷脏页本身是写磁盘操作，会消耗IO资源。如果刷脏页的IO和用户查询的IO发生了资源竞争，就可能导致查询的响应时间变长，你可能会观察到数据库偶尔的“卡顿”。
监控：可以通过 SHOW ENGINE INNODB STATUS\G 命令查看脏页相关信息，或者查询 information_schema 库中的 INNODB_METRICS 或 INNODB_BUFFER_POOL_STATS 表。
优化：
- 确保服务器硬件（尤其是磁盘）的IO能力足够。使用SSD是解决IO瓶颈最有效的方法。
- 合理设置 innodb_buffer_pool_size（缓冲池大小）和 innodb_log_file_size（Redo Log文件大小）。一个足够大的Redo Log可以减少因为日志写满而导致的强制刷脏页。
- 关注 innodb_max_dirty_pages_pct_lwm 和 innodb_max_dirty_pages_pct 参数，它们控制了缓冲池中脏页的比例阈值，达到阈值后会触发后台刷脏。