零、 MySQL数据文件
1. InnoDB 的存储文件有两个，一个 `.frm`表定义文件 和 一个 `.idb`数据文件。
2. 数据库采用数据页的形式组织数据。MySQL默认的非压缩数据页为16KB。
在.idb文件中，0-16KB偏移量即为0号数据页，16-23KB为1号数据页，依次类推。
一、 InnoDB Architecture
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
in-memory and on-disk structures that comprise the InnoDB storage engine architechre.
二、 InnoDB In-Memory Structures
2.1 Bufer Pool
缓冲池是Innodb缓存表和索引数据的一块内存区。使得被频繁查询的数据能够直接从内存中被访问，不用再对硬盘进行IO，能够加速查询。在一些专用服务器上有时候会将80%的物理内存指定为buffer pool。
show variables like 'innodb_buffer_pool_size';
//单位ByteFor efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the least recently used (LRU) algorithm(最近最少使用算法).
2.2 Buffer Pool LRU Algorithm
The buffer pool is managed as a list using a variation of the LRU algorithm. When room is needed to add a new page to the buffer pool, the least recently used page is evicted and a new page is added to the middle of the list. This midpoint insertion strategy treats the list as two sublists:
· At the head, a sublist of new (“young”) pages that were accessed recently
· At the tail, a sublist of old pages that were accessed less recently
缓冲list前5/8为new sublist,后3/8为old sublist。它俩交接处为modpoint insertion,
2.3 Buffer Pool Configuration
可以自己配置的部分
2.4 Change Buffer
https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html
The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.
When INSERT, UPDATE, and DELETE operations are performed on a table, the values of indexed columns (particularly the values of secondary keys) are often in an unsorted order, requiring substantial I/O to bring secondary indexes up to date. The change buffer caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded into the buffer pool, and the updated page is later flushed to disk. The InnoDB main thread merges buffered changes when the server is nearly idle, and during a slow shutdown.
Because it can result in fewer disk reads and writes, change buffering is most valuable for workloads that are I/O-bound; for example, applications with a high volume of DML operations such as bulk inserts benefit from change buffering.
However, the change buffer occupies a part of the buffer pool, reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working data set fits entirely within the buffer pool, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.
2.5 Log Buffer
https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log-buffer.html
The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 16MB. The contents of the log buffer are periodically flushed to disk. A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.