InnoDB的架构设计

https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

innodb-architecture

由图可知，InnoDB主要分为两部分：

InnoDB In-Memory Structures
InnoDB On-Disk Structures

InnoDB In-Memory Structures

MySQL如果发现需要修改的页不在内存里，就先将对页的修改记到Change Buffer，同时记录redo log，然后慢慢把数据load到内存，load完成后，再将Change Buffer里记录的修改应用到Buffer Pool（merge），之后再把数据刷到磁盘（purge）。

Buffer Pool

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

数据load的地方就是Buffer Pool，类似缓存。

innodb-buffer-pool-list

Buffer Pool是一个以页为元素的链表，基于LRU算法来管理内存。

show engine innodb status;

在”BUFFER POOL AND MEMORY”部分可以看到Buffer Pool的大小；

Change Buffer

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

innodb-change-buffer

merge：Change Buffer -> Buffer Pool

purge：Buffer Pool -> Disk

Change Buffer只在操作Secondary Index（二级索引）时才使用，因为聚簇索引必须是唯一的，即每次INSERT，UPDATE，DELETE时都需要检查是否已经存在相同的字段，所以就没必要使用Change Buffer了；而且聚簇索引操作的随机性比较小，通常在相邻的页操作，而二级索引访问非常随机。

Adaptive Hash Index

因为B+ Tree的查找次数取决于树的高度，在很多场景中，比如存在被频繁访问的数据，每次都要走B+ Tree查询，如果此时使用一个指针就可以把数据的位置记下就好了。

这就是Adaptive Hash Index自适应哈希，MySQL会自动评估使用自适应索引是否值得，如果观察到建立哈希索引可以提升速度，则建立。

If a table fits almost entirely in main memory, a hash index can speed up queries by enabling direct lookup of any element, turning the index value into a sort of pointer. InnoDB has a mechanism that monitors index searches. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.

Log Buffer

从架构图可以看到，Log Buffer里的redo log会被刷到磁盘。

The log buffer is the memory area that holds data to be written to the log files on disk.

InnoDB On-Disk Structures

https://dev.mysql.com/doc/refman/8.0/en/innodb-on-disk-structures.html

TableSpaces 表空间

https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace.html

分为五种：

The System Tablespace
File-Per-Table Tablespaces
General Tablespace
Undo Tablespaces
Temporary Tablespaces

平时创建的表数据，可以存放到The System Tablespace，File-Per-Table Tablespaces，General Tablespace三者中的任意一个地方，具体取决于配置和创建表时的SQL语句。

Doublewrite Buffer

Change Buffer提升性能，Doublewrite Buffer保证数据页的可靠性。

“双写缓冲”，MySQL在刷数据到磁盘之前，要先把数据写到Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer可以理解为是一个备份，如果发生 crash，就可以利用Doublewrite Buffer来修复磁盘里的数据。

设置InnoDB_doublewrite=0，即可关闭Doublewrite Buffer。

The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files. If there is an operating system, storage subsystem, or mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the doublewrite buffer in a large sequential chunk, with a single fsync() call to the operating system (except in the case that innodb_flush_method is set to O_DIRECT_NO_FSYNC).

Prior to MySQL 8.0.20, the doublewrite buffer storage area is located in the InnoDB system tablespace. As of MySQL 8.0.20, the doublewrite buffer storage area is located in doublewrite files.