整理定义

As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.

1. Buffer pool：是InnoDB存储引擎的一个重要组成部分，它是InnoDB用于缓存表和索引数据的内存区域。
Day59【概念解析】InnoDB Buffer Pool | 易浅小站
这是一个由NotionNext生成的站点

https://kuangyichen.com/article/industry-day59

2. flushing:
将更改写入已缓冲在内存区域或临时磁盘存储区域中的数据库文件。 定期刷新的InnoDB存储结构包括重做日志、撤消日志和缓冲池。
刷新可能会发生，因为内存区域已满并且系统需要释放一些空间，因为提交操作意味着可以完成事务的更改，或者因为缓慢的关闭操作意味着所有未完成的工作都应该完成。 当一次刷新所有缓冲数据并不重要时，InnoDB 可以使用一种称为模糊检查点的技术来刷新小批量的页面，以分散 I/O 开销。

MySQL :: MySQL 8.0 Reference Manual :: MySQL Glossary

To write changes to the database files, that had been buffered in a memory area or a temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.

Flushing can happen because a memory area becomes full and the system needs to free some space, because a commit operation means the changes from a transaction can be finalized, or because a slow shutdown operation means that all outstanding work should be finalized. When it is not critical to flush all the buffered data at once, InnoDB can use a technique called fuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

3. LSN(log sequence number):
LSN 是“日志序列号”的缩写。 这个任意的、不断增加的值表示与重做日志中记录的操作相对应的时间点。 （这一时间点与事务边界无关；它可以落在一个或多个事务的中间。）它由 InnoDB 在崩溃恢复期间内部使用并用于管理缓冲池。
💡
在 MySQL 5.6.3 之前，LSN 是一个 4 字节无符号整数。 当重做日志文件大小限制从 4GB 增加到 512GB 时，LSN 在 MySQL 5.6.3 中变成了 8 字节无符号整数，因为需要额外的字节来存储额外的大小信息。 在 MySQL 5.6.3 或更高版本上构建并使用 LSN 值的应用程序应使用 64 位而不是 32 位变量来存储和比较 LSN 值。

MySQL :: MySQL 8.0 Reference Manual :: MySQL Glossary

Acronym for “log sequence number”. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.

Prior to MySQL 5.6.3, the LSN was a 4-byte unsigned integer. The LSN became an 8-byte unsigned integer in MySQL 5.6.3 when the redo log file size limit increased from 4GB to 512GB, as additional bytes were required to store extra size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit rather than 32-bit variables to store and compare LSN values.

In the MySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take an incremental backup. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

复述展开

检查点处理的工作原理

MySQL :: MySQL 8.0 Reference Manual :: MySQL Glossary The cleanup activities that occur when MySQL is started again after a crash. For InnoDB tables, changes from incomplete transactions are replayed using data from the redo log. Changes that were committed before the crash, but not yet written into the data files, are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation. During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

MySQL :: MySQL 8.0 Reference Manual :: MySQL Glossary A technique that flushes small batches of dirty pages from the buffer pool, rather than flushing all dirty pages at once which would disrupt database processing.

理解体会