MYSQL系列-各种Buffer优化一~性能增强利器
在MySQL数据库中，BUFFER是指用于存储和管理数据的内存区域。MySQL通过使用不同类型的缓冲区来提高数据库性能，其中包括查询缓存、键缓存、表缓存和日志缓存等。 这些缓冲区的优化可以带来以下好处：
- 提高查询性能
- 减少磁盘I/O
- 提高并发性能
- 降低系统开销
虽然缓冲区可以提高数据库的性能，但过多地使用缓冲区也可能导致内存的消耗过大，从而影响系统的稳定性。
本文主要总结MYSQL的各种BUFFER和相关配置参数，希望对MYSQL相关原理有更胜的理解。
重做日志缓存（Redo Log Buffer）
基本介绍
redo log缓冲区是一块内存区域，保存将要写入redo log的数据。
redo log缓冲区大小由innodb_log_buffer_size配置选项定义,默认8M。
mysql> show variables like 'innodb_log_buffer_size%';
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
redo log缓冲区会定期把内存中的回滚日志刷到磁盘上。一个大的redo log缓冲区意味着允许大事务运行，而无需在事务提交之前将redo log写入磁盘。因此，如果您有更新，插入或删除多行的事务，则使用更大的redo log缓冲区可节省磁盘I/O。
redo log 缓冲区实现原理
innodb_flush_log_at_trx_commit控制如何将redo log缓冲区的内容写入到日志文件。 innodb_flush_log_at_timeout控制redo log缓存写到redo log文件的频率。
InnoDB 修改数据操作，实际上修改的是Buffer Pool中的数据。
为了保障数据的安全稳定，不丢失数据。InnoDB并不是一个事务提交后就将 Buffer Pool 中被修改的数据同步到磁盘上，而是要先记录到redo log日志中，以防崩溃之后可以恢复。
最后再从Buffer Pool 中把脏页连续写入磁盘。
而是记录到redo log日志中也不是直接写磁盘，而是先写到redo log 缓冲区
缓存池 buffer pool
基本介绍
InnoDB缓存池，本质上是内存中的一块区域，当数据被访问时，InnoDB会把表数据、索引数据缓存在 buffer pool 缓存池中。
相关参数介绍：
-
innodb_buffer_pool_size：
- 描述：指定InnoDB存储引擎使用的Buffer Pool的大小。
- 默认值：根据MySQL版本和系统配置的不同而有所变化。通常为物理内存的一部分，建议设置为总内存的70-80%。
- 示例：innodb_buffer_pool_size=2G
-
innodb_buffer_pool_instances：
- 描述：指定Buffer Pool的实例数，用于并行读取和写入操作。
- 默认值：根据MySQL版本和系统配置的不同而有所变化。通常为CPU核心数。
- 示例：innodb_buffer_pool_instances=8
-
innodb_old_blocks_time：
- 描述：指定Buffer Pool中旧数据块的最长访问时间，超过该时间将被认为是“冷数据”。
- 默认值：1000（以毫秒为单位）。
- 示例：innodb_old_blocks_time=1000
-
innodb_lru_scan_depth：
- 描述：指定Buffer Pool中每次LRU（Least Recently Used）扫描的数据页数。
- 默认值：1024。
- 示例：innodb_lru_scan_depth=1024
-
innodb_flush_neighbors：
- 描述：指定在刷新脏数据页时，是否将相邻的数据页一起刷新到磁盘。
- 默认值：1（启用）。
- 示例：innodb_flush_neighbors=1
-
innodb_io_capacity：
- 描述：指定InnoDB存储引擎在执行随机I/O操作时的最大I/O吞吐量。
- 默认值：200。
- 示例：innodb_io_capacity=200
-
innodb_buffer_pool_chunk_size
- 描述：定义InnoDB缓冲池调整大小操作的块大小（MySQL 8.0版本引入的一个参数）
- 默认值：128MB
-
innodb_old_blocks_pct
- 描述：指定InnoDB用于旧块子列表的缓冲池的近似百分比。值的范围是5到95.
- 默认值：37（即池的3/8）
-
innodb_old_blocks_time
- 描述：指定插入到旧子列表中的页面在第一次访问后必须在毫秒（ms）内保留多长时间，然后才能移动到新子列表中。 如果值为0，则无论插入后何时发生访问，插入旧子列表的页面在第一次访问时都会立即移至新子列表。 如果该值大于0，则页面将保留在旧的子列表中，直到在第一次访问后至少几毫秒内发生访问。
- 默认值：1000
-
innodb_max_dirty_pages_pct
- 描述：InnoDB会尝试从缓冲池中刷新数据，以便脏页面的百分比不超过此值。 指定范围从0到99的整数。
- 默认值：75
-
innodb_max_dirty_pages_pct_lwm
- 描述：低水位的百分比表示启用来控制脏页预flush的比率。 默认值为0将完全禁用预冲洗行为。
- 默认值：0
作用
加速读
当需要访问一个数据page的时候，如果这个page已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中获取。
原理
缓存池允许经常使用的数据直接从内存中处理，这加快了处理速度。在专用数据库服务器上，经常将高达80％的物理内存分配给InnoDB缓冲池。
为了提高大容量读取操作的效率，缓冲池被划分成可能包含多行数据的页。为了缓存管理的效率，缓冲池被实现为页的链表; 会使用LRU的延伸算法让很少使用的数据超时。
InnoDB使用（LRU）算法的变体将pool作为list进行管理。当需要向pool中添加新block时，InnoDB将驱逐最近最少使用的block并将新block添加到list的中间。
这种“中点插入策略”将list视为两个子list：
在头部，最近访问过的“新”（或“年轻”）block的子列表。
在尾部，最近访问的“旧”block的子列表。
此算法把查询中大量访问的block放到新子列表中。 旧子列表包含较少使用的block，这些block是驱逐的候选人。
LRU算法默认情况下：3/8的缓冲池专用于旧子列表。列表的中点是新子列表的尾部与旧子列表的头部相交的边界。当InnoDB将block读入缓冲池时，它最初将其插入中点（旧子列表的头部）。
加速写
当需要修改一个page的时候，先将这个page在缓冲池中进行修改，记下相关的redo log，这个页面的修改就算已经完成了。至于这个被修改的dirty page（脏页）什么时候真正刷新到磁盘，后面会详细讲到。
原理
主要使用Write Ahead Log（预先日志持久化）策略，即先写日志，再写入磁盘，利用上文说的REDO LOG机制
Insert Buffer（插入缓冲区）
Insert Buffer是InnoDB存储引擎使用的一种机制，用于优化插入操作的性能。 当执行插入操作时，InnoDB将新插入的数据先写入Insert Buffer，而不是直接写入磁盘的数据文件。这样可以减少磁盘的随机写入操作，提高插入操作的性能。
适用场景
Insert Buffer对于非聚集索引、辅助索引和次要索引的插入操作特别有效。 没有直接控制Insert Buffer大小的参数，它的大小是由InnoDB自动管理的。
PS：在buffer pool中管理
Change Buffer（修改缓冲区）
Change Buffer是InnoDB存储引擎使用的一种机制，用于优化修改操作（例如更新和删除）的性能。 当执行修改操作时，InnoDB将修改的数据页的差异部分（delta）存储在Change Buffer中，而不是立即写入磁盘的数据文件。这样可以避免直接在磁盘上进行随机写入操作，提高修改操作的性能。
适用场景
Change Buffer对于非聚集索引的修改操作特别有效。
相关参数
- innodb_change_buffer_max_size
- 作用：控制change buffer能占用buffer pool总内存的比例
- 范围：默认25（表示change buffer最大能占用其25%的内存），最大50。
- innodb_change_buffering
- 作用：控制change buffer对哪些dml起作用
- 可选参数：all（insert、delete、update）、none（不缓存任何操纵）、inserts、deletes、purges
参考 1.【MySQL】事务日志 redo log 详解 2.redo log缓冲区 3.[玩转MySQL之十]InnoDB Buffer Pool详解 4.全网最清楚的：MySQL的insert buffer和change buffer 串讲