InnoDB 引擎底层存储和缓存原理

InnoDB 记录存储结构和索引页结构

 mysql与磁盘交互的基本单位是页，一页默认是16KB。一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

行格式

Compact：在记录 的真实数据处只会存储该列的该列的前 768 个字节的数据，然后把剩余的数据分 散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地址。

Dynamic： mysql5.7默认的行格式。不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

Compressed ：处理溢出页与 Dynamic一致。但采用压缩算法对页面进行压 缩，以节省空间。

  mysql数据行除了记录表中定义的数据列外还有很多额外的字段列：

变长字段长度列表：比如VARCHAR(M)、VARBINARY(M)、 各种 TEXT 类型，各种 BLOB 类型，他们的长度是不固定的，所以我们在存储真实数据 的时候需要顺便把这些数据占用的字节数也存起来。如果该可变字段允许存储的 最大字节数超过 255 字节并且真实存储的字节数超过 127 字节，则使用 2 个字节， 否则使用 1 个字节。

Null值列表：表中的某些列可能存储 NULL 值，mysql用一个二进制列表来当前记录的列数据是否为null，如果列表值为0，代表是null，如果是1，代表不为null。

记录头：由8个字节共40位组成

DB_ROW_ID(row_id) ：非必须， 6 字节，表示行 ID ，唯一标识一条记录

DB_TRX_ID ：必须， 6 字节，表示事务 ID

DB_ROLL_PTR ：必须， 7 字节，表示回滚指针

索引页格式

一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分：

File Header： 文件头部 38 字节 页的一些通用信息，如当前页编号，上一页、下一页的地址。页的校验信息等。

Page Header： 页面头部 56 字节 数据页专有的一些信息，如本页由多少记录，有多少个槽，第一条记录的地址等。

Infimum + Supremum： 最小记录和最大记录 26 字节 它时页面中虚拟出来的两条的行记录。

User Records： 用户记录 大小不确定 实际存储的行记录内容。

Free Space： 空闲空间 大小不确定 页中尚未使用的空间。当页面插入新记录时，都会从 Free Space 部分申请一个记录大小的空间划分到 User Records 部分。

Page Directory： 页面目录 大小不确定 页中的某些记录的相对位置。它是一个跳表。mysql将页面中的记录按顺序分为若干个组，第一个组一条记录，中间组4-8个记录。最后一个组1-8条记录。这些组的最大记录的地址偏移量称为槽。page dictory就是由槽构成的一个树结构。mysql查找数据时先通过page dictory二分查找定位到数据可能所在的槽，再根据槽找到对应的分组，顺序遍历分组链表里的记录，最终查询结果是否存在。

File Trailer： 文件尾部 8 字节 校验页是否完整。前 4 个字节代表页的校验和，与File Header 中的校验和相对应的。后 4 个字节代表页面被最后修改时对应的日志序列位置（LSN），这个也和校

验页的完整性有关。

InnoDB 的体系结构

  MySQL的记录存储在页中，每个页都是物理上连续的一段存储空间。连续的64个页组成了一个区，也就是1MB大小。每256个区称为一个组，第一个组最开始的 3 个页面的类型是固定的：用来登记整个表空间的一些整 体属性以及本组所有的区被称为 FSP_HDR，也就是 extent 0 ~ extent 255 这 256 个区，整个表空间只有一个 FSP_HDR。为了区分叶子节点和非叶子节点页面，所有的非叶子节点和叶子节点分别组成一个集合，称为段，即一个索引有两个段。

系统表空间

  MYSQL有且仅有一张系统表，表ID为0，系统表extent 1 和 extent 两个区，也就是页号从 64~191 这 128 个页面被称为 Doublewrite buffer，也就是双写缓冲区。

双写缓冲区/双写机制

双写缓冲区 / 双写机制是 InnoDB 的三大特性之一，还有两个是 Buffer Pool 、自 适应 Hash 索引。mysql1页是16KB，而操作系统一页是4KB。也就是说mysql写一页数据操作系统需要写4次才能完成，如果这过程中出现了中断，那么MYSQL就会产生脏页。这种脏页无法通过redo日志恢复，因此引入了双写缓存机制(doublewrite buffer )。mysql进入磁盘写入时，会先将数据写到内存的双写缓冲区。

缓冲双写机制工作原理

  mysql对脏页刷盘时，先把脏页读到内存的双写缓存区，这个缓存区大小也是2M，然后从内存的双写缓存区分两次每次1M读到磁盘的双写缓存区，由于磁盘的双写缓存区是系统表中两个相邻的区，是一个物理上连续的区域，所以这个过程是一种顺序磁盘IO，效率较高。最后再把脏页从内存的双写缓存区写入到磁盘。开启缓存双写机制大概使性能下降5-10%左右。对于slave节点，可以关闭。 因为slave即使出了问题也可以从中继日志中恢复？

buffer pool

mysql在内存中的数据缓冲池，mysql启动时申请，它会按照配置大小稍微大一点去申请一块连续的内存，默认128M，实际它会略大于128M。它由若干个一一对应的空闲缓存页和描述信息数据块组成，描述信息数据块里面存放了这个数据页所属的表空间、数据页的编号、这个缓存页在buffer pool中的地址、free链表、flush链表、lru链表中的前后数据描述块的指针（free_pre、free_next）等等。描述数据大约占数据页大小的5%（约800字节），我们设置的innodb_buffer_pool_size并不包含这部分控制块占用的内存空间大小，所以最终申请的下来的空间比设置的大大概5%。Buffer Pool的缺省值其实是偏小的，生产环境一般可设置为机器可用内存的70%-75之间，如果没有专门的DBA监控，可保守设置为60%左右。

free链表的管理

 f ree链表是由buffer pool的控制块构成的双向链表，用以记录buffer pool中没有被使用的页。它的头节点用来存储当前链表的长度、头节点和尾节点的地址等。如果缓存中一个空闲页被使用，就根据链表中控制块找到一个空闲缓存页面，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后再把该控制块从链表中摘除。

缓存页的哈希处理

 对于被加载到buffer pool中的页，都会以表空间号+页编号为key，缓存页地址为value存于一个哈希表中。查询中根据这个哈希表确定页面是否已被加载到buffer pool。如果没有，则从磁盘加载到buffer pool，再更新这个哈希表。

flush链表的管理

 flush链表用于管理buffer pool中的脏页。其结构与free链表一致。mysql在更新数据时为提高效率，并不会立即进行刷盘操作，而是将页面从磁盘加载到buffer pool在内存中修改后立即返回客户端修改成功，同时以一个链表记录buffer pool中被修改的页，这个链表就是flush链表。

LRU链表的管理

lru链表（LRU的英文全称：Least Recently Used）用于实现buffer pool中的页面缓存淘汰机制。哪些页面时热点页、哪些应该最先被淘汰由此链表记录，其结构也与上述两链表结构一致。在mysql的lru链表中，链表前面的5/8的数据区域称为热数据区，也叫young区，后面的3/8称为冷数据区，也叫old区。当页面初次被使用时则进入到冷数据区的头部，淘汰链表末尾的页。当该页面再次被使用时则由冷数据区进入到热数据区的头部。由于全表扫描、mysql的预读机制会导致页面在短时间内被多次访问而进入热数据区，导致热数据区被重新洗盘，因此，mysql设定如果一个页面初次在缓存中被使用的时间与最近被使用的时间间隔小于1秒,则该页面不会进入到热数据区，否则将它移动到young区域的头部。上述的这个间隔时间是由系统变量innodb_old_blocks_time控制的，默认为1秒。对于young区来说，头部的那部分页面在使用过程中频繁的被移动到头部，对于效率提升的帮助不大，反而会因为链表节点移动而拖慢性能，所以，mysql设定对于热点区前1/4之后的页面再次被访问到时才会移动到头部。

SHOW VARIABLES LIKE 'innodb_old_blocks_pct';  -- lru链表冷数据区的比例，默认37
SHOW VARIABLES LIKE 'innodb_old_blocks_time'; -- lru链表冷数据区进热数据区的时间间隔，默认1000毫秒

脏页刷盘机制

 mysql有个专门的后台进程每隔一段时间负责把脏页刷新到磁盘。主要有两种途径：

 1. 定期扫描lru链表尾部的一些页面，如果发现脏页则刷新到磁盘。这个页面数由innodb_lru_scan_depth来指定，这种刷 新页面的方式被称之为BUF_FLUSH_LRU。

 2. 定期从flush链表中刷新一部分页面到磁盘，刷新的速率取决于当时系统是 不是很繁忙。这种刷新页面的方式被称之为BUF_FLUSH_LIST。

 如果后台进程刷新脏页的进度比较慢导致buffer pool中没有空闲页，并且此时lru链表的冷数据区全是脏页，那么将不得不lru尾部的一个脏页刷新到磁盘，这种耍弄新单个页面到磁盘的方式被称 之为BUF_FLUSH_SINGLE_PAGE。

多个Buffer Pool实例

  在高并发情况下，buffer pool的每个链表的维护都要加锁处理而性能不高。所以在buffer pool比较大且并发量较大的时候，可以把它拆分成多个小的buffer pool。它们都是独立的，独立的去申请内存空间，独立的管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。buffer pool的实例个数由innodb_buffer_pool_instances指定，最大为64。由于管理buffer pool也存在开销，所以它不是越多越好。一般情况下，如果innodb_buffer_pool_size小于1G，则innodb_buffer_pool_instances设置为多个也不生效；如果innodb_buffer_pool_size大于1G，则应设置nnodb_buffer_pool_instances使得每个buffer pool的值达到1G为最佳。在mysql5.7.5及以后的版本中，支持动态的调整buffer pool的大小。mysql在调整buffer pool的大小时是以chunk为单位进行增减内存的，chunk的默认大小为128M，只能在mysql启动时调整，以变量innodb_buffer_pool_chunk_size指定。

Buffer Pool的缓存页除了用来缓存磁盘上的页面以外，还可以存储锁信息、自适应哈希索引等信息。

查看Buffer Pool的状态信息

SHOW ENGINE INNODB STATUS\G

Total memory allocated：代表Buffer Pool向操作系统申请的连续内存空间大小，包括 全部控制块、缓存页、以及碎片的大小。 
Dictionary memory allocated：为数据字典信息分配的内存空间大小，注意这个内存空 间和Buffer Pool没啥关系，不包括在Total memory allocated中。 
Buffer pool size：代表该Buffer Pool可以容纳多少缓存页，注意，单位是页！ Free buffers：代表当前Buffer Pool还有多少空闲缓存页，也就是free链表中还有多少 个节点。 
Database pages：代表LRU链表中的页的数量，包含young和old两个区域的节点数量。 
Old database pages：代表LRU链表old区域的节点数量。 
Modified db pages：代表脏页数量，也就是flush链表中节点的数量。 
Pending reads：正在等待从磁盘上加载到Buffer Pool中的页面数量。 当准备从磁盘中加载某个页面时，会先为这个页面在Buffer Pool中分配一个缓存页以及 它对应的控制块，然后把这个控制块添加到LRU的old区域的头部，但是这个时候真正的磁 盘页并没有被加载进来，Pending reads的值会跟着加1。 
Pending writes LRU：即将从LRU链表中刷新到磁盘中的页面数量。
Pending writes flush list：即将从flush链表中刷新到磁盘中的页面数量。 Pending writes single page：即将以单个页面的形式刷新到磁盘中的页面数量。 
Pages made young：代表LRU链表中曾经从old区域移动到young区域头部的节点数量。 
Page made not young：在将innodb_old_blocks_time设置的值大于0时，首次访问或者后 续访问某个处在old区域的节点时由于不符合时间间隔的限制而不能将其移动到young区域 头部时，Page made not young的值会加1。 youngs/s：代表每秒从old区域被移动到young区域头部的节点数量。 non-youngs/s：代表每秒由于不满足时间限制而不能从old区域移动到young区域头部的节 点数量。
 
Pages read、created、written：代表读取，创建，写入了多少页。后边跟着读取、创 建、写入的速率。
Buffer pool hit rate：表示在过去某段时间，平均访问1000次页面，有多少次该页面已 经被缓存到Buffer Pool了。 
young-making rate：表示在过去某段时间，平均访问1000次页面，有多少次访问使页面 移动到young区域的头部了。 
not (young-making rate)：表示在过去某段时间，平均访问1000次页面，有多少次访问 没有使页面移动到young区域的头部。 
LRU len：代表LRU链表中节点的数量。 
unzip_LRU：代表unzip_LRU链表中节点的数量。 
I/O sum：最近50s读取磁盘页的总数。 
I/O cur：现在正在读取的磁盘页数量。 
I/O unzip sum：最近50s解压的页面数量。 
I/O unzip cur：正在解压的页面数量。

Change Buffer

   change buffer是buffer pool中的一块内存，默认25%，由innodb_change_buffer_max_size指定。它是一种应用于普通非唯一索引上为降低IO随机写频率的缓存技术。在mysql5.5之前叫inset buffer，只对插入操作做优化，后面对delete、update操作都支持。mysql在进行update操作时，如果相关的普通非唯一性索引页在buffer pool中，则直接修改；如果不存在，则在chang buffer 中记录要更新的相关信息，给客户端返回修改成功。当该索引页在下次被使用而不得不加载到buffer pool中时，将索引页与change buffer中的更新信息合并，以此避免了在修改时进行磁盘随机IO操作，达到提升效率的目的。

为什么写缓冲优化，仅适用于非唯一普通索引页呢？

  因为唯一性索引在修改数据时必须先校验数据的唯一性，导致当时必须要找到相关的页面而不得不进行磁盘IO。在这种情况change buffer就排不上用场了。

change buffer的刷盘时机

• 访问这个数据页；
• 后台master线程会定期 merge；
• 数据库缓冲池不够用时；
• 数据库正常关闭时；
• redo log写满时；

change buffer的使用场景

• 数据库大部分是非唯一索引；
• 业务是写多读少，或者不是写后立刻读取；

mysql> show variables like '%change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.00 sec)
#innodb_change_buffering：默认是all支持所有DML操作
#innodb_change_buffer_max_size：默认是25，即缓冲池的1/4。最大可设置为50,采用默认即可
1.5 监控指标
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
 #这行显示了关于size（size 1代表了已经合并记录页的数量）、 free list（代表了插入缓冲中空闲列表长度）和seg size大小（seg size  2显示了当前insert buffer的长度，大小为27572*16K=440M左右）的信 息。0 merges代表合并插入的次数
Ibuf: size 1, free list len 0, seg size 2, 0 merges
#这个标签下的一行信息insert，delete mark，delete 分别表示merge操作合并了多少个insert buffer，delete buffer，purge  buffer 
merged operations:
 insert 0, delete mark 0, delete 0
 #这个标签下的一行信息表示当change buffer发生 merge时表已经被删除了，就不需要再将记录合并到辅助索引中
discarded operations:
 insert 0, delete mark 0, delete 0
 
#因为没有update buffer，所以对一条记录进行update的操作可以分为两个过 程： # A：将记录标记为删除 # B：真正将记录删除 #因此，delete buffer对应update 操作的第一个过程，即将记录标记为删除， purge buffer对应update的第二个过程，即将记录真正地删除