跳到主要内容

数据库缓冲池

减少与磁盘直接进行 I/O 的时间。

一、缓冲池 vs 查询缓存​

缓冲池与查询缓存并不是同一个东西。

1、缓冲池 buffer pool​

缓存池的重要性​

缓存原则​

优先对使用频率高的热数据进行加载

缓冲池的预读特性​

预习把当前数据页前后的数据页加载到内存中去

2、查询缓存​

查询缓存缓存的是查询结果。 mysql8.0 之后不支持。

二、 缓冲池如何读取数据​

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

1、如果执行了 sql 语句，更新了缓冲池中的数据，这些数据会马上同步到磁盘吗？​

#interview #MySQL

当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息。然后会以一定的频率刷新到磁盘中。并不是每次发送更新操作，都会立即进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据回写到磁盘上，这样做的好处是提升了数据库的整体性能。

2、什么是 checkpoint 机制？​

#interview #MySQL

Checkpoint机制就是在特定的时刻，将内存中的数据状态和磁盘上的数据状态强制同步，使得两者达到一致状态。这个特定的时刻就是执行Checkpoint操作的时刻。

Checkpoint机制的主要作用有两个：

1. 减少数据库系统崩溃恢复的时间：因为Checkpoint操作会将内存中的数据写入磁盘，所以在系统崩溃后，不需要回滚所有的操作，只需要回滚最后一个Checkpoint之后的操作。
    
2. 保证数据的持久性：即使在数据库系统崩溃的情况下，通过Checkpoint操作，也能保证至少到最后一个Checkpoint时刻的所有操作都已经被持久化到磁盘上。

3、如何手动触发 checpoint 操作？​

慎用下面的操作。下面的命令会设置 innodb 引擎中的脏页的最大百分比为 0 ，这将导致 InnoDB 立即开始 checkpoint 操作，将所有的脏页（dirty page）写入磁盘。会对数据库的性能产生影响，因为它会导致大量的磁盘 I/O。因此执行完下面的命令之后，需要将 innodb_max_dirty_pages_pct 设置回去， 的默认值为 90 

SET GLOBAL innodb_max_dirty_pages_pct = 0;

4、如何查看缓冲池的大小？​

#mysql调优 #interview #MySQL

MyISAM 引擎，只缓存索引，不缓存数据，对应的键缓存参数为 key_buffer_size(8388608)。 InnoDB 引擎，主要缓存表中的数据和索引数据，对应的键缓存参数为 innodb_buffer_pool_size。

show variables like 'innodb_buffer_pool_size';
-- 134217728 单位是 byte 
-- 134217728 = 128 * 1024 * 1024 ， 即 128 MB

修改缓存池的大小

  • 方案一：修改全局变量（重启后失效）
-- 修改缓存池的大小为 256 MB。 注意，这个在重启之后会失效。
set global innodb_buffer_pool_size = 268435456
  • 方案二：修改 my.cnf 文件（重启任有效）
[server]
innodb_buffer_pool_size = 268435456

innodb_buffer_pool_size 参数建议是物理内存的 50%~75%，但这个并不是绝对的。如果物理机有 256G 的内存，如果根据建议设置成 192G ，假设操作系统等其他软件占用 8G，那么还有 56G 的内存浪费掉了。因此需根据实际情况来设置 innodb_buffer_pool_size 参数的大小。

innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * X

三、多个 buffer pool 实例​

在多线程环境下，访问 buffer pool 中的数据都需要加锁。在 buffer pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer pool 可能会影响请求的处理速度。所以在 buffer pool 特别大的时候，可以将其拆分为若干个小的 buffer pool。每个 buffer pool 都称为一个实例，他们是独立的，独立的去申请内存空间，独立的管理各种链表。所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

通过 innodb_buffer_pool_instances 参数进行设置。只能够在 mysql 未启动的时候在 my.cnf 中设置。

[server]
innodb_buffer_pool_instances=4

**注意：当 buffer pool size 小于 1 G 的时候，设置多个实例是无效的。mysql 建议每个缓存池实例至少为 1G **

https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html

四、查询和更新数据的问题​

查询时：先查询 buffer pool 里面的数据，如果不存在就将磁盘的数据加载到 buffer pool 里面。 更新时：先判断 buffer pool 里是否有待更新的数据，如果有，会修改buffer pool 的数据。没有则把磁盘的数据加载到 buffer pool 里面，并更新 buffer pool 的数据。 buffer pool 的数据会定时刷入磁盘（checkpoint 操作）。

在更新数据操作时会有一些问题。

  1. 更新数据时，已经修改了 buffer pool 里面的数据，此时，mysql 宕机了。此时 buffer pool 的数据不就丢失了
  2. 从 buffer pool 把数据刷入磁盘到一半的时候，mysql 宕机了，如何进行回滚数据呢？