MySQL中的数据缓存利器——InnoDB缓冲池揭秘

2023-07-2607:41:33数据库教程Comments884 views字数 7058阅读模式

配置大量内存最大的原因其实不是因为可以在内存中保存大量数据：最终目的是避免磁盘I/O，因为磁盘I/O 比在内存中访问数据要慢得多。关键是要平衡内存和磁盘的大小、 速度、成本和其他因素，以便为工作负载提供高性能的表现。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL 需要为缓存分配更多的内存。它使用缓存来避免磁盘访问，磁盘访问比内存访问数据要慢得多。操作系统可能会缓存一些数据，这对 MySQL 有些好处（尤其是对 MyISAM)，但是 MysQL 自身也需要大量内存。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

下面是我们认为对大部分情说来说最重要的缓存：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  • InnoDB 缓冲池
  • InnoDB 日志文件和 MyISAM数据的操作系统缓存
  • MyISAM 键缓存
  • 查询缓存
  • 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

接下来我们重点学习 InnoDB 缓冲池。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

介绍

InnoDB是基于磁盘存储的，并将其中的数据按页的方式进行管理。因此InnoDB可视为基于磁盘的数据库系统。为了缓解 CPU 与磁盘速度之间的矛盾，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

缓冲池其实就是一块内存区域，没什么特别的。缓冲池（Buffer Pool）的默认大小为 128M，可通过 innodb_buffer_pool_size 参数来配置。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

如果大部分都是 InnoDB表，InnoDB 缓冲池或许比其他任何东西更需要内存。关于缓冲池的架构图如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

由图可知，缓冲池中缓存的数据页类型有：索引页、数据页、undo 页、插入缓存（insert buffer）、自适应哈希索引（adaptive hash index）、锁信息、数据字典信息等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

InnoDB 还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之，InnoDB 严重依赖缓冲池，你必须确认为它分配了足够的内存。但不是说缓冲池的内存大小越大越好，如果数据量不大，且数据增长缓慢，就没必要给缓冲池分配过多的内存；如果数据量增长迅速，则可以提前规划好缓冲池大小。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

执行下述命令可以查看缓冲池的状态信息：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

show engine innodb status;

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

往下定位到 BUFFER POOL AND MEMORY，相关字段含义如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  • Total large memory allocated: 这是指为InnoDB存储引擎分配的总内存大小，单位为字节。在此例中，总共分配了 274,726,912 字节的内存。
  • Dictionary memory allocated: 这是指为InnoDB字典（例如表结构和索引信息）分配的内存大小，单位为字节。在此例中，分配了 23,120,009 字节的内存。
  • Buffer pool size: 这是指缓冲池的大小，表示用于存储数据页的内存大小，单位为页（通常为 16KB）。在此例中，缓冲池大小为 16,384 页。
  • Free buffers: 这是指当前缓冲池中空闲的缓冲区数量。在此例中，有 1,018 个空闲缓冲区。
  • Pending reads: 这是指当前正在等待读取的数据库页数。在此例中，没有任何等待读取的数据库页。
  • Pending writes: LRU 0, flush list 0, single page 0: 这是指当前正在等待写入到磁盘的数据库页数。在此例中，没有任何等待写入的数据库页。
  • Buffer pool hit rate: 这是指缓冲池的命中率，表示从缓冲池中读取数据页时的命中次数与总的读取次数的比率。在此例中，命中率为 980/1000，即为98%。

数据页

在Innodb的B+树中，我们常说的节点被称之为 页(page)，每个页当中存储了用户数据，所有的页合在一起组成了一颗B+树。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

页 是InnoDB存储引擎管理数据库的最小磁盘单位，我们常说每个节点16KB，其实就是指每页的大小为16KB。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

在 Buffer Pool 中，也是以数据页为数据单位，存放着很多数据。但是我们通常叫做缓存页，因为 Buffer Pool 毕竟是一个缓冲池，并且里面的数据都是从磁盘文件中缓存到内存中。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

缓冲池和磁盘之间的数据交换的单位是数据页，包括从磁盘中读取数据到缓冲池和缓冲池中数据刷回磁盘中，如图所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

当MySQL服务重启和关闭时，通常需要进行缓冲池的预热和关闭操作。接下来我们具体学习了解一下预热和关闭操作做了哪些事。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

预热

MySQL 5.6 引入了数据预热机制。innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 两个参数控制了预热，不过默认都是关闭的，需要开启。MySQL 5.7则是默认开启。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

InnoDB Buffer Pool 预热机制原理

1、关闭MySQL，执行导出 InnoDB Buffer Pool 数据到文件文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  • 在关闭MySQL时，将 InnoDB Buffer Pool 的数据保存到磁盘上，并且导出的文件是经过压缩的。
  • ib_buffer_pool 是InnoDB Buffer Pool中数据保存到磁盘上的文件名，它的名字和路径受 innodb_buffer_pool_filename 控制。该文件默认保存在InnoDB的数据目录下。
  • ib_buffer_pool 文件中保存了 tablespace IDs 和 page IDs 。

开启 “关闭MySQL导出 InnoDB Buffer Pool 数据”功能，如果需要永久执行，请加到my.cnf 。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

2、启动MySQL，执行ib_buffer_pool文件恢复到 InnoDB Buffer Pool文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  • 根据ib_buffer_pool 文件中的tablespace IDs 和 page IDs ，将数据恢复到 InnoDB Buffer Pool。tablespace IDs和page IDs信息来自INNODB_BUFFER_PAGE_LRU表。
  • ib_buffer_pool 文件过旧没有关闭，MySQL会比对数据的新老，如果磁盘中page最近有过DML操作（如update），那么ib_buffer_pool中的数据不会加载到 InnoDB Buffer Pool中。
  • 如果MySQL中有的page已经不存在了，那么加载机制会跳过这个page，不会把加载。

开启 “启动MySQL，InnoDB Buffer Pool历史数据导入”功能，建议直接加入到my.cnf 。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

mysqld --innodb_buffer_pool_load_at_startup=ON;

3、Online保存和恢复InnoDB Buffer Pool数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

在MySQL运行时，可以将InnoDB Buffer Pool数据保存到磁盘，或者恢复到InnoDB Buffer Pool。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

SET GLOBAL innodb_buffer_pool_dump_now=ON;
SET GLOBAL innodb_buffer_pool_load_now=ON;

4、查看执行保存、恢复 InnoDB Buffer Pool 的进展状态。主要用于Online保存和恢复场景。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

<1>显示执行 InnoDB Buffer Pool 数据保存到磁盘的进展状态文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170112 17:26:02 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

<2>显示恢复 InnoDB Buffer Pool数据时的进展状态文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170112 17:31:22 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

5、终止 InnoDB Buffer Pool 恢复操作文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

SET GLOBAL innodb_buffer_pool_load_abort=ON;

6、其他文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

如果MySQL 版本小于MySQL 5.6，可以手动对数据进行预热。对经常被使用的表进行count(*) 操作，也能起到数据预热的效果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

select count(*) t1;
select count(*) t2;

上文涉及到的参数，可以执行下面这些查询语句来看当前状态值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

SELECT @@innodb_buffer_pool_dump_now;
SELECT @@innodb_buffer_pool_load_now;
SELECT @@innodb_buffer_pool_load_at_startup;
SELECT @@innodb_buffer_pool_dump_at_shutdown;

缓存页管理

缓冲池 buffer pool 除了数据页的加载和导出，还需要对数据页进行管理。MySQL会使用 Free 链表、Flush 链表和 LRU链表来管理缓冲池中的数据页，我们来学习一下。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

缓冲池中的页不仅需要被读取，还需要进行修改操作。修改的页肯定发生在 LRU 链表中，当 LRU链表中的页被修改后，则称该页为脏页（dirty page)，即缓冲池中的页和磁盘上的页数据产生了不一致。这时数据库会通过 checkpoint 机制将脏页刷新回磁盘。而 flush 链表中的页即为脏页。需要注意的是，脏页既存在于 LRU链表中，也存在于 flush 链表中。LRU链表用于管理缓冲池中页的可用性，flush链表则用于管理将页刷新回磁盘，两者互不影响。下图显示了 free 链表、LRU 链表、flush 链表之间的关系：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

Free 链表记录空闲缓存页

Free 链表，它是一个双向链表，链表的每个节点就是一个个空闲的缓存页对应的描述数据块。每个描述数据块里都有两个指针，一个是 free_pre 指针，一个是 free_next 指针，分别指向自己的上一个 free 链表的节点，以及下一个 free 链表的节点。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

Free 链表用于跟踪空闲的数据页，即未被任何数据占用的页。这些页可以用来存储新读取或修改的数据页。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

当需要从磁盘读取新的数据页时，MySQL会从Free链表中获取可用的空闲页，并将其加载到缓冲池中。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

当 InnoDB 存储引擎启动后，其缓冲池是空的，所有页都在 free 链表中。由于数据库的所有读/写操作都需要首先在缓冲池中完成，故缓冲池的首要任务就是将外存中的页读取到缓冲池中，一般也称作页的物理读取 （physical read)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

在高并发环境下，如果有多个线程进行并发访问同一个页时，只有第一个访问读物理页的线程进行读取操作，其他线程需要等待该I/O操作完成，MySQL 会对此进行并发控制的保护。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

Flush 链表记录脏缓存页

Flush链表用于跟踪已被修改但尚未写入磁盘的数据页，也称为脏页（Dirty Pages）。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

当数据页在缓冲池中被修改后，它们会被添加到Flush链表中，表示需要将其刷新（Flush）到磁盘中以确保数据的持久性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

Flush链表通常使用一个链表数据结构进行管理，每个节点表示一个脏页。这些节点包含了指向下一个脏页的指针。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

在后台或适当的时机，MySQL会将Flush链表中的脏页写入到磁盘，从而保持数据的一致性和持久性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

LRU 链表记录缓存页的命中率

MySQL会使用LRU（最近最少使用）等算法来管理缓冲池中的数据页。如果缓冲池已满，MySQL可能会根据算法的规则将一些较早未使用的数据页从内存中淘汰出去，以为新的预读数据腾出空间。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

下图是一个 LRU 算法的基本模型。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

InnoDB 管理 Buffer Pool 的 LRU 算法，是用链表来实现的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  1. 在上图 的state 1 里，链表头部是 P1，表示 P1 是最近刚刚被访问过的数据页；
  2. 假设内存里只能放下这么多数据页；这时候有一个读请求访问 P3，因此变成state 2，P3 被移到最前面；
  3. state 3 表示，这次访问的数据页是不存在于链表中的，所以需要在 Buffer Pool 中新申请一个数据页 Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾 Pm 这个数据页的内存，存入 Px 的内容，然后放到链表头部。
  4. 从效果上看，就是最久没有被访问的数据页 Pm，被淘汰了。

如果做全表扫描一个 200G 的历史数据表，平时不怎么访问，按照上述的算法就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

对于一个正在做业务服务的库，这可不妙。你会看到，Buffer Pool 的内存命中率急剧下降，磁盘压力增加，SQL 语句响应变慢。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

所以，InnoDB 不能直接使用这个 LRU 算法。因此，InnoDB 对 LRU 算法做了改进。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

具体来说就是：新增了midPoint位置。新读取到的页并没有直接放在LRU列的首部，而是放在距离尾部37%的位置。这个算法称之为midpoint insertion stategy。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  • midPoint在整体列表的 5/8 处
  • midpoint之前的是new区域（热数据）
  • midpoint之后的数据是不活跃数据，old区域。
  • midpoint处，是新子列表的尾部与旧子列表的头相交的边界

MySQL中的数据缓存利器——InnoDB缓冲池揭秘文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

查看midpoint文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.04 sec)

改进后的 LRU 算法执行流程变成了下面这样。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  1. 上图中state 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成state 2。
  2. 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
  3. 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
    1. 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
    2. 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描 200G 的历史数据表为例，我们看看改进后的 LRU 算法的操作逻辑：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

  1. 扫描过程中，需要新插入的数据页，都被放到 old 区域 ;
  2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；
  3. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。

可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

总结

InnoDB缓冲池是MySQL中一项关键技术，它扮演着数据缓存的重要角色。本文深入介绍了InnoDB缓冲池的核心概念，从缓冲池中数据页的存储结构到预热机制的工作原理，一一揭示了其神奇之处。而除了加载和导出数据页，缓冲池还通过Free链表、Flush链表和LRU链表对数据页进行精准管理。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html

作者：hresh
来源：稀土掘金
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/51958.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/51958.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定