InnoDB In-Memory Structures On-Disk Structures 内存结构 磁盘结构

小结： 

1、
Buffer Pool，控制块，缓存页，数据页，Free 链表，Flush 链表，LRU 链表，Page, Change Buffer, Log Buffer

 

 

MySQL - InnoDB 内存结构解析 https://mp.weixin.qq.com/s/U_KVZNGlTUvT3_ElZDhN7g

Buffer Pool，控制块，缓存页，数据页，Free 链表，Flush 链表，LRU 链表，Page, Change Buffer, Log Buffer

  1. Buffer Pool
  2. Change Buffer
  3. Log Buffer
  4. Adaptive Hash Index

下面是官方的 InnoDB 引擎架构图，主要分为内存结构和磁盘结构两大部分。

图片

1、Buffer Pool 概述

Buffer Pool：缓冲池，简称 BP。其作用是用来缓存表数据与索引数据，减少磁盘 IO 操作，提升效率。

Buffer Pool 由缓存数据（Page）和对缓存数据页进行描述的控制块组成，控制块中存储着对应缓存页的所属的表空间、数据页的编号、以及对应缓存页在 Buffer Pool 中的地址等信息。

Buffer Pool 默认大小是 128M， 以 Page 页为单位，Page 页默认大小 16K，而控制块的大小约为数据页的5%，大概是800字节。

图片

注:Buffer Pool 大小为 128M 指的就是缓存页的大小，控制块则一般占5%，所以每次会多申请 6M 的内存空间用于存放控制块。

如何判断一个页是否在 BufferPool 中缓存 ?

MySQl 中有一个哈希表数据结构，它使用表空间号+数据页号，作为一个 key，然后缓冲页对应的控制块作为 value。

图片

当需要访问某个页的数据时，先从哈希表中根据表空间号和页号看看是否存在对应的缓冲页。如果有，则直接使用；如果没有，就从 free 链表中选出一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置。

2、Page 页分类

Page 根据状态可以分为三种类型：

图片

1、Free Page：空闲 Page，未使用

2、Clean Page：被使用 Page 但是数据没有修改过

3、Dirty Page：脏页，使用过数据被修改过，与磁盘数据产生不一致

针对上面所说的三种 page 类型，InnoDB 通过三种链表结构来维护和管理。

BP 的底层采用链表数据结构管理 Page。在 InnoDB 访问表记录和索引时会在 Page页 中缓存，以后使用可以减少磁盘 IO 操作，提升效率。

2.1、Page 页管理之 Free 链表

free list:表示空闲缓冲区，管理 free page

Buffer Pool 的初始化过程中，是先向操作系统申请连续的内存空间，然后把它划分成若干个控制块&缓冲页的键值对。

free 链表是把所有空闲的缓冲页对应的控制块作为一个个的节点放到一个链表中，这个链表便称之为 free 链表

基节点：free 链表中只有一个基节点是不记录缓存页信息（单独申请空间），它里面就存放了 free 链表的头节点的地址，尾节点的地址，还有 free 链表里当前有多少个节点。

图片

磁盘加载页的流程:

  1. 从 free 链表中取出一个空闲的控制块，对应缓冲页。
  2. 把该缓冲页对应的控制块的信息填上，例如：页所在的表空间、页号之类的信息。
  3. 把该缓冲页对应的 free 链表节点即控制块从链表中移除。表示该缓冲页已经被使用了。

2.2、Page 页管理之 Flush 链表

flush list: 表示需要刷新到磁盘的缓冲区，管理 dirty page，内部 page 按修改时间排序。

InnoDB 引擎为了提高处理效率，在每次修改缓冲页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷新操作。所以需要使用到 flush 链表存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到 flush 链表。

flush 链表的结构与 free 链表的结构相似

图片

脏页即存在于 flush 链表，也在 LRU 链表中，但是两种互不影响，LRU 链表负责管理 page 的可用性和释放，而 flush 链表负责管理脏页的刷盘操作。

2.3、Page 页管理之普通 LRU 链表

LRU = Least Recently Used（最近最少使用）：就是末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰。

图片

  1. 当要访问某个页时，如果不在 Buffer Pool，需要把该页加载到缓冲池，并且把该缓冲页对应的控制块作为节点添加到 LRU 链表的头部。

  2. 当要访问某个页时，如果在 Buffer Pool中，则直接把该页对应的控制块移动到 LRU 链表的头部。

  3. 当需要释放空间时，从最末尾淘汰。

2.4、普通 LRU 链表的优缺点

优点： 所有最近使用的数据都在链表表头，最近未使用的数据都在链表表尾，保证热数据能最快被获取到。

缺点：如果发生全表扫描（比如:没有建立合适的索引 or 查询时使用 select * 等），则有很大可能将真正的热数据淘汰掉。由于MySQL中存在预读机制，很多预读的页都会被放到 LRU 链表的表头。如果这些预读的页都没有用到的话，会导致很多尾部的缓冲页很快就会被淘汰。

图片

2.5、Page 页管理之改进型 LRU 链表

改进型 LRU：链表分为 new 和 old 两个部分，加入元素时并不是从表头插入，而是从中间 midpoint 位置插入（就是说从磁盘中新读出的数据会放在冷数据区的头部），如果数据很快被访问，那么 page 就会向 new 列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

图片

冷数据区的数据页什么时候会被转到到热数据区呢 ?

  1. 如果该数据页在 LRU 链表中存在时间超过1s，就将其移动到链表头部（链表指的是整个LRU 链表）。
  2. 如果该数据页在 LRU 链表中存在的时间短于1s，其位置不变（由于全表扫描有一个特点，就是它对某个页的频繁访问总耗时会很短）。
  3. 1s这个时间是由参数 innodb_old_blocks_time 控制的。

3、Change Buffer

3.1、概述

Change Buffer：写缓冲区,是针对二级索引（辅助索引） 页的更新优化措施

作用:：在进行 DML 操作时，如果请求的是辅助索引（非唯一键索引）没有在缓冲池中时，并不会立刻将磁盘页加载到缓冲池，而是在 CB 记录缓冲变更，等未来数据被读取时，再将数据合并恢复到 BP 中。

ChangeBuffer 占用 BufferPool 空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数 innodb_change_buffer_max_size ;

图片

3.2、Change Buffer 数据更新流程

场景1: 对于唯一索引来说，需要将数据页读如内存，判断没有冲突，插入这个值，语句执行结束；

场景2: 对于普通索引来说，则是将更新记录在 change buffer 流程如下

图片

1、更新一条记录时，该记录在 bp 存在，直接在 bp 修改，一次内存操作。

2、如果该记录在 bp 不存在（没有命中），在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中不用再去磁盘查询数据，避免一次磁盘 IO。

3、当下次查询记录时，会将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

问题一：为什么写缓冲区，仅适用于非唯一普通索引页？

如果在索引设置唯一性，在进行修改时，InnoDB 必须要做唯一性校验，因此必须查询磁盘，做一 次 IO 操作。会直接将记录查询 Buffer Pool 中，然后在缓冲池修改，不会在 Change Buffer 操作。

问题二：什么情况进行 merge？

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。

change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上，以下情况会进行持久化：

  1. 访问这个数据页会触发 merge。
  2. 系统有后台线程会定期 merge。
  3. 在数据库正常关闭( shutdown )的过程中，也会执行 merge 操作。

3.3、change buffer 使用场景

change buffer 的主要目的就是将记录的变更动作缓存下来，所以在 merge 发生之前应当尽可能多的缓存变更信息，这样 change buffer的优势发挥的就越明显。

应用场景：对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

4、Log Buffer

Log Buffer：日志缓冲区，用来保存要写入磁盘上的 Log 文件（redo/undo）的数据，日志缓冲区内容定期刷新道磁盘 Log 文件中，日志缓冲区满时会自动将其刷新到磁盘，节省磁盘 io

Log Buffer 主要记录 innoDB 引擎日志，在 DML 操作时会产生 Redo 和 Undo 日志

图片

Log Buffer 空间满了，会自动写入磁盘。可以通过将将 innodb_log_buffer_size 参数调大，减少磁盘 IO 频率

innodb_flush_log_at_trx_commit 参数控制日志刷新行为，默认为1

0 ： 每隔1秒写日志文件和刷盘操作（写日志文件 LogBuffer-->OS cache，刷盘 OS cache-->磁盘文件），最多丢失1秒数据

1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作

2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

图片

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

图片

5、Adaptive Hash Index

自适应哈希索引,用于优化 bp 数据查询

6、总结

本期讲了关于 InnoDB 内存结构，后续有机会在继续介绍 InnoDB 磁盘结构。

参考文章

https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html

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

 

招贤纳士

政采云技术团队（Zero），一个富有激情、创造力和执行力的团队，Base 在风景如画的杭州。团队现有500多名研发小伙伴，既有来自阿里、华为、网易的“老”兵，也有来自浙大、中科大、杭电等校的新人。团队在日常业务开发之外，还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践，推动并落地了一系列的内部技术产品，持续探索技术的新边界。此外，团队还纷纷投身社区建设，目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。

如果你想改变一直被事折腾，希望开始折腾事；如果你想改变一直被告诫需要多些想法，却无从破局；如果你想改变你有能力去做成那个结果，却不需要你；如果你想改变你想做成的事需要一个团队去支撑，但没你带人的位置；如果你想改变本来悟性不错，但总是有那一层窗户纸的模糊……如果你相信相信的力量，相信平凡人能成就非凡事，相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程，亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程，我觉得我们该聊聊。任何时间，等着你写点什么，发给 zcy-tc@cai-inc.com

 

【MySQL】InnoDB - 整体架构：内存结构与磁盘结构 - 掘金 https://juejin.cn/post/7007421609900245000

InnoDB 系列文章：

InnoDB 架构

image.png

从 MySql 官方文档中的配图可以看到，InnoDB 引擎的架构分为内存结构和磁盘结构。由于存储引擎仅负责数据的存储和提取工作，因此其设计非常简单和纯粹。

MySql 服务器层不负责数据的存储和提取。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎的差异。1

内存结构

InnoDB 内存结构主要分为：Buffer Pool、Change Buffer、Adaptive Hash Index 和 Log Buffer。

Buffer Pool

Buffer Pool 用于加速数据的访问和修改，通过将热点数据缓存在内存的方法，最大限度地减少磁盘 IO，加速热点数据的读和写。

image.png

  • Buffer Pool 中数据以页为存储单位，其实现数据结构是以页为单位的单链表。

  • 由于内存的空间限制，Buffer Pool 仅能容纳最热点的数据。Buffer Pool 使用最近最少使用算法（Least Recent Used，LRU）算法淘汰非热点数据页。

  • 依据时间局部性原理与空间局部性原理，Buffer Pool 在存储当前活动数据页的时候，会以预读 Read-ahead 的方式缓存目标数据页临近的数据页。

  • 预读机制带来预读失败的问题，InnoDB 采用分代机制解决预读失败问题：将 Buffer Pool 分为 New SubList 和 Old SubList 两部分，将最新读取的数据页置于 Old SubList 头部，Old SubList 中的数据再次被访问到才会置于 New SubList 头部；预读失败的冷数据将更快地从 Old SubList 中淘汰，而不会影响到 New SubList 中原有的热数据。

  • 预读失败问题可以引申到缓冲池污染问题，InnoDB 采用时间窗口（Time Window）机制解决缓冲池污染问题：对于 Old SubList 中的数据页，必须在 Old SubList 中停留到达指定时间之后再次被访问到，才能转移到 New SubList 中，默认窗口大小是 1s。

  • 对于 Buffer Pool 中数据的查询，InnoDB 直接读取返回；对于 Buffer Pool 中数据的修改，InnoDB 直接在 Buffer Pool 中修改，并将修改写入 redo Log 中，当数据页被 LRU 算法淘汰时写入磁盘，若持久化前系统崩溃，则在重启后使用 redo Log 进行恢复。

Change Buffer

Change Buffer 用于加速非热点数据中二级索引的写入操作。由于二级索引数据的不连续性，导致修改二级索引时需要进行频繁的磁盘 IO 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同时将写操作录入 redo log 中，在缓冲到一定量或系统较空闲时进行 ibuf merge 操作将修改写入磁盘中。Change Buffer 在系统表空间中有相应的持久化区域。

二级索引就是辅助索引，出了聚簇索引之外的所有索引都是二级索引。见【MySQL】索引简述 - 掘金 (juejin.cn)

image.png

Change Buffer 大小默认占 Buffer Pool 的 <span class="katex"><span class="katex-mathml">25%25\%<span class="katex-html"><span class="base"><span class="strut"><span class="mord">25%，在引擎启动时便初始化完成。其物理结构为一棵名为 <code>ibuf</code> 的 B Tree。Change Buffer 的使用条件为：

  • InnoDB 开启 innodb_change_buffering，且该表当前没有 flush 操作。
  • 仅对二级索引树的叶子节点进行修改，且该索引页不在 Buffer Pool 中。
  • 对于 Unique 二级索引，仅删除操作可以缓冲。

其 ibuf merge 时机为：

  • 用户使用该二级索引进行查询时。
  • 缓存插入操作时，预估到 page 空间不足可能导致索引页分裂时。
  • 本次缓存操作将导致 ibuf btree 页分裂，且分类后 Change Buffer 大小将超出限制时。
  • master 线程发起 merge 命令时。
  • 用户对该表进行 flush 操作时。

Adaptive Hash Index

自适应哈希索引（Adaptive Hash Index，AHI）用于实现对于热数据页的一次查询。使用聚簇索引进行数据页定位的时候需要根据索引树的高度从根节点走到叶子节点，通常需要 3 到 4 次查询才能定位数据。InnoDB 根据对索引使用情况的分析和索引字段的分析，通过自调优Self-tuning的方式为索引页建立或者删除哈希索引。

Adactive Hash Index -- Zohar Yip

图片风格不同是因为这一张官方文档没有图了，而图片是按照旧文档的风格做的

AHI 所作用的目标是频繁查询的数据页和索引页，而由于数据页是聚簇索引的一部分，因此 AHI 是建立在索引之上的索引，对于二级索引，若命中 AHI，则将直接从 AHI 获取二级索引页的记录指针，再根据主键沿着聚簇索引查找数据；若聚簇索引查询同样命中 AHI，则直接返回目标数据页的记录指针，此时就可以根据记录指针直接定位数据页。

AHI 的大小为 Buffer Pool 的 1/64，再 MySql 5.7 之后支持分区，以减少对于全局 AHI 锁的竞争，默认分区数为 8。

Log Buffer

InnoDB 使用 Log Buffer 来缓冲日志文件的写入操作。内存写入加上日志文件顺序写的特点，使得 InnoDB 日志写入性能极高。

对于任何修改操作，都将录入诸如 redo log 与 undo log 这样的日志文件中，因此日志文件的写入操作非常频繁，却又十分零散。这些文件都存储在磁盘中，因此日志记录将引发大量的磁盘 IO。Log Buffer 将分散的写入操作放在内存中，通过定期批量写入磁盘的方式提高日志写入效率和减少磁盘 IO。

Log Buffer 逻辑结构图 -- Zohar Yip

这种将分散操作缓冲为批量操作的优化方式将增加数据丢失的风险，事务提交的时候必将将操作写入日志中，此时日志文件若未落盘而系统崩溃，则相关操作将丢失而无法恢复。而使用 write 方式将 Log Buffer 写入日志文件时，操作系统会将写入操作先写入 OS 缓冲区中，需要调用 flush 指令将缓冲区数据刷入文件中，若操作系统在未刷入前崩溃，则同样将导致数据丢失不可恢复。

InnoDB 提供三种 Log Buffer 数据落盘方式，具体见：【MySQL】日志简述 - 掘金 (juejin.cn)

  • 0：按秒写，按秒刷。每秒调用 write() 写入 OS Buffer 并调用 flush() 刷入磁盘。
  • 1：实时写，实时刷。每次事务提交都调用 write() 写入 OS Buffer 并调用 flush() 刷入磁盘。
  • 2：实时写，延迟刷。每次事务提交都调用 write() 写入 OS Buffer，但每秒调用 flush() 刷入磁盘。

磁盘结构

InnoDB On disk Architecture -- Zohar Yip

在磁盘中，InnoDB 将所有数据都逻辑地存放在一个空间中，称为表空间（Tablespace）。表空间由段（Segment）、区（extent）、页（Page）组成。

表空间

表空间是 InnoDB 物理存储中的最高层，目前的表空间类别包括系统表空间（System Tablespace）、独立表空间（File-per-table Tablespace）、通用表空间（General Tablespace）、回滚表空间（Undo Tablespace）和临时表空间（The Temporary Tablespace）。

Table Space -- Zohar Yip

系统表空间

系统表空间是 InnoDB 数据字、双写缓冲、写缓冲和回滚日志的存储位置，如果关闭独立表空间，它也将存储所有表数据和索引。

它默认下是一个初始大小 10MB、名为 ibdata1 的文件，系统表空间所对应的文件由 innodb_data_file_path 定义，修改该参数我们可以更改表空间文件、大小、是否自动增长或使用多个文件组成一个表空间。指定系统表空间文件自动增长后，其增长大小由 innodb_autoextend_increment 设置（默认为 8MB）且不可缩减，即使删除系统表空间中存储的表和索引，此过程释放的空间仅仅是在表空间文件中标记为已释放而已，并不会缩减其在磁盘中的大小。

  • 数据字典（Data Dictionary）
    数据字典是由各种表对象的元数据信息（表结构，索引，列信息等）组成的内部表。

  • 双写缓冲（Doublewrite Buffer）
    双写缓冲用于保证写入磁盘时页数据的完整性，防止发生部分写失效Partial page write问题，Doublewrite Buffer 同时存在于磁盘与内存中，大小都为 2MB。由于操作系统的存储单元页大小为 4K，而 InnoDB 默认存储单元页大小为 16K，在数据从 Buffer 中刷写入磁盘时可能会出现页未写完全但系统崩溃的问题。InnoDB 在数据从 Buffer 刷写入磁盘前，先将数据保存于内存的双写缓冲中，达到 16K 后写入磁盘的双写缓冲中，再写入表数据文件，因此数据文件的写入总是按照 16K 单页递增，出现崩溃数据丢失时使用 redo log 恢复，以此保证数据完整性。

  • 修改缓冲（Change Buffer）
    内存中 Change Buffer 对应的持久化区域，同样为了数据完整性而设置。

  • 回滚日志（Undo Log）
    Undo Log 存放在 Undo Segment 中，undo log 是记录数据修改前状态的逻辑日志，保存所有被更新的数据行逻辑状态的历史版本。Undo log 主要是这两个功能：实现事务进行 Rollback 操作时对数据的恢复，和根据数据的历史版本实现多版本并发控制（MVCC）功能。

独立表空间

开启独立表空间（File-per-table TableSpace）（innodb_file_per_table=ON）之后，InnoDB 会为每个数据库单独创建子文件夹，数据库文件夹内为每个数据表单独建立一个表空间文件 table.ibd 用于存放每个表的数据、索引和插入缓冲 Bitmap 页，同时创建一个 table.frm 文件用于保存表结构信息。其他类型的信息，如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等仍存放于系统表空间内。因此即使使用独立表空间，系统表空间也会不断增长。每个独立表空间的初始大小是 96KB。

通用表空间

通用表空间（General Tablespace）是一个由 CREATE TABLESPACE 命令创建的共享表空间，创建时必须指定该表空间名称和 ibd 文件位置，ibd 文件可以放置于任何 MySql 实例有权限的地方。该表空间内可以容纳多张数据表，同时在创建时可以指定该表空间所使用的默认引擎。通用表空间存在的目的是为了在系统表空间与独立表空间之间作出平衡。系统表空间与独立表空间中的表可以向通用表空间移动，反之亦可，但系统表空间中的表无法直接与独立表空间中的表相互转化。每个共享表空间初始大小为 64KB。

Tablespace conversion -- Zohar Yip

共享表空间

共享表空间（Shared Tablespace）包括系统表空间和通用表空间。InnoDB 从 MySQL 5.7.24 开始不推荐在共享表空间中放置表分区，后面的版本已经将它删除了。

Undo 表空间

Undo TableSpace 用于存放一个或多个 undo log 文件。默认 undo log 存储与系统表空间中，MySql 5.6 以后支持自定义 Undo log 表空间并存储所有 undo log。一旦用户定义了 Undo Tablespace，则系统表空间中的 Undo log 区域将失效。对于 Undo Tablespace 的启用必须在 MySql 初始化前设置，Undo Tablespace 默认大小为 10MB。Undo Tablespace 中的 Undo log 表可以进行 truncate 操作。

临时表空间

MySql 5.7 之前临时表存储在系统表空间中，极大加剧了 ibdata 文件贪婪的增长性，5.7 版本之后 InnoDB 引擎从系统表空间中抽离出临时表空间（Temporary Tablespace），用于独立保存临时表数据及其回滚信息。该表空间文件路径由 innodb_temp_data_file_path 指定，但必须继承 innodb_data_home_dir。

段

表空间由各个段（Segment）组成，创建的段类型分为数据段、索引段、回滚段等。由于 InnoDB 采用聚簇索引与 B+ 树的结构存储数据，因此事实上数据页和二级索引页仅仅只是 B+ 树的叶子节点，因此数据段称为 Leaf node segment，索引段其实指的是 B+ 树的非叶子节点，称为 Non-Leaf node segment。

区

区（Extend）是由连续的页组成的空间，大小固定为 1MB，由于默认页大小为 16K，因此一个区默认存储 64 个连续的页。如果页大小调整为 4K，则 256 个连续页组成一个区。为了保证页的连续性，InnoDB 存储引擎会一次从磁盘申请 4 ~ 5 个区。

对于新创建的独立表空间，其大小默认是 96K 而不是 1MB，这是因为在每个段开始都会使用 32 个页大小的碎片页Fragement page来存放数据，当碎片页写满了在进行 Extend 的申请，以节省磁盘容量的开销。

页

页（Page）是 InnoDB 的基本存储单位，每个页大小默认为 16K，从 InnoDB1.2.x 版本开始，可通过设置 innodb_page_size 修改为 4K、8K、16K。InnoDB 首次加载后便无法更改。InnoDB 中的页类型有数据页（B-tree Node Page）、undo 页（Undo Log Page）、系统页（System Page）、事务数据页（Transation system Page）、插入缓冲位图页（Insert Buffer Bitmap Page）、插入缓冲空闲列表页（Insert Buffer Free List）、未压缩的二进制大对象页（Uncompressed BLOB Page）、压缩的二进制大对象页（Compressed BLOB Page）。

行

InnoDB 是面向列（row-oriented）的关系存储引擎，因此数据是按行存储的。每个 Page 最多存放 7992 行记录。InnoDB 会为每个数据行前添加事务 ID 列（TransactionID，占 6 Byte）和回滚指针列（Roll Pointer，占 7 Byte），如果该表没有定义主键，则会选择第一个定义的非空唯一索引作为主键，若没有非空唯一索引则会在 TransactionID 前添加一列主键 ID 列（RowId，占 6 Byte）作为主键列。

InnoDB 目前支持两种文件格式：Antelope 和 Barracuda 格式。Antelope 支持原先的 Compact 和 Redundat 行格式。Barracuda 支持新的 Compressed 和 Dynamic 行格式。

Redundat 行格式

Redundat 是 MySQL 5.0 以前 InnoDB 的行记录存储格式，5.0 以后为了兼容进行保留。其存储方式为：

字段长度偏移列表记录头信息列 1 数据列 2 数据…列 n 数据
n 或 2n byte 6 byte 列 1 长度 列 2 长度 … 列 n 长度

字段长度偏移列表会记录每一列数据的偏移量，该列表长度取决于数据中是否有长度大于 255 Byte 的列，有则为 2n，否则为 n。该部分识别信息由头信息中的 1byte_offs_flag 标识。头信息包含删除标志、索引号、列数量、下一记录位置等信息，各信息通过 Bit 表示。对于 char 列，其长度取决于用户设置的字符数（m）和字符类型，对于 Latin1 为 m，对于 utf8mb4 为 4m。对于 Null 列，如果是 varchar 类型则会忽略在后面数据中不予以表示，但字段长度偏移列表中仍会有所记录，只不过该列和后面一列的偏移值是一样的；对于 char 类型则仍旧占用相应的空间。

Compact 行格式

MySQL 5.0 引入 Compact 行格式，其设计目标是高效地存储数据。一个页中存储的行数据越多，其性能就越高。

非 NULL 列数据长度列表Null 标志位记录头信息列 1 数据列 2 数据…列 n 数据
- null_cols/8 Byte 5 Byte 列 1 长度 列 2 长度 … 列 n 长度

非 Null 列数据长度列表从右往左记录非 null 列的长度，舍弃 null 列长度 0 不予以写入，默认用 1B 记录单列长度，如果某列长度大于 255，则使用 2B 记录。Null 列的信息存储在 Null 标志位中，用每一位表示每一个允许 null 的列，如果当前行该列为 null，则该标志位为 1，1B 可以标识 8 个列，若有 8 个以上 null 列，则其长度为 2B，若有 16 个以上则长度为 3B 依此类推。由于 null 列信息被标志位所判定，列数据中不需要存储任何 null 列的信息，因此 Compact 行无论是 varchar 类型还是 char 类型数据，只要为 null 都不存储不占用空间。

《MySql 技术内幕 - InnoDB 引擎》关于 Null 标志位长度写错了，书中描述为“该部分所占字节应该为 1 字节”并不准确，实际上是多少个 Null 列就多少位，以字节长度递增。

行数据溢出

对于 Redundant 和 Compact 行格式，数据溢出不是绝对的，BLOB、LOB 大对象数据可能会存储在数据行中，而 varchar 数据也可能溢出存储在 Uncompressed BLOB Page 中。InnoDB 限制单页最少数据行数为 2，如果 InnoDB 判定 varchar 所占字节数过长使得单页无法满足存储两行数据，则会将触发行溢出保留前 768Byte 作为前缀，将 768Byte 后的数据存储于 Uncompressed BLOB Page 中。同理，如果 InnoDB 判定 BLOB 数据很短不会影响单页存储两行数据，则不会将 BLOB 数据存放于 Uncompressed BLOB Page 中。

Compact Row overflow

Dynamic 行格式

Barracuda 文件下新的行格式，对于存放 BLOB 中的数据或者行溢出采用了完全行溢出的方式，对于行溢出的数据全部存储在 Off Page 中，在数据行中仅存放 20Byte 的指针指向该 Page，而不再是和之前一样存储 768Byte 的前缀。

Compressed 行格式

在 Dynamic 行格式基础上，将行数据以 zib 算法进行行压缩，非常有利于 BLOB、TEXT、VARCHAR 这类大长度数据类型的存储。

Footnotes

  1. 《高性能 MySQL》 ↩

 

 

 

SHOW ENGINE INNODB STATUS;


=====================================
2022-09-29 10:05:00 140242898269952 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1560 srv_active, 0 srv_shutdown, 2333745 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3509
OS WAIT ARRAY INFO: signal count 2162
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 153526
Purge done for trx's n:o < 153526 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421718049730800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421718049729944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421718049729088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421718049728232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1565 OS file reads, 25947 OS file writes, 19067 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 9 buffer(s)
109.13 hash searches/s, 26.33 non-hash searches/s
---
LOG
---
Log sequence number 87187639
Log buffer assigned up to 87187639
Log buffer completed up to 87187639
Log written up to 87187639
Log flushed up to 87187639
Added dirty pages up to 87187639
Pages flushed up to 87187639
Last checkpoint at 87187639
8381 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137035776
Dictionary memory allocated 1092634
Buffer pool size 8192
Free buffers 6483
Database pages 1688
Old database pages 603
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1830, not young 4979
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1440, created 266, written 11940
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1688, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1048, Main thread ID=140242574391040 , state=sleeping
Number of rows inserted 173, updated 2564, deleted 14, read 2484089
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 413, updated 915, deleted 274, read 70032
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 118.79 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 

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

https://dev.mysql.com/doc/refman/8.0/en/innodb-in-memory-structures.html

 

15.5.1 Buffer Pool

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

For 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.

Knowing how to take advantage of the buffer pool to keep frequently accessed data in memory is an important aspect of MySQL tuning.

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

Figure 15.2 Buffer Pool List

Content is described in the surrounding text.

The algorithm keeps frequently used pages in the new sublist. The old sublist contains less frequently used pages; these pages are candidates for eviction.

By default, the algorithm operates as follows:

  • 3/8 of the buffer pool is devoted to the old sublist.

  • The midpoint of the list is the boundary where the tail of the new sublist meets the head of the old sublist.

  • When InnoDB reads a page into the buffer pool, it initially inserts it at the midpoint (the head of the old sublist). A page can be read because it is required for a user-initiated operation such as an SQL query, or as part of a read-ahead operation performed automatically by InnoDB.

  • Accessing a page in the old sublist makes it “young”, moving it to the head of the new sublist. If the page was read because it was required by a user-initiated operation, the first access occurs immediately and the page is made young. If the page was read due to a read-ahead operation, the first access does not occur immediately and might not occur at all before the page is evicted.

  • As the database operates, pages in the buffer pool that are not accessed “age” by moving toward the tail of the list. Pages in both the new and old sublists age as other pages are made new. Pages in the old sublist also age as pages are inserted at the midpoint. Eventually, a page that remains unused reaches the tail of the old sublist and is evicted.

By default, pages read by queries are immediately moved into the new sublist, meaning they stay in the buffer pool longer. A table scan, performed for a mysqldump operation or a SELECT statement with no WHERE clause, for example, can bring a large amount of data into the buffer pool and evict an equivalent amount of older data, even if the new data is never used again. Similarly, pages that are loaded by the read-ahead background thread and accessed only once are moved to the head of the new list. These situations can push frequently used pages to the old sublist where they become subject to eviction. For information about optimizing this behavior, see Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”, and Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.

InnoDB Standard Monitor output contains several fields in the BUFFER POOL AND MEMORY section regarding operation of the buffer pool LRU algorithm. For details, see Monitoring the Buffer Pool Using the InnoDB Standard Monitor.

Buffer Pool Configuration

You can configure the various aspects of the buffer pool to improve performance.

Monitoring the Buffer Pool Using the InnoDB Standard Monitor

InnoDB Standard Monitor output, which can be accessed using SHOW ENGINE INNODB STATUS, provides metrics regarding operation of the buffer pool. Buffer pool metrics are located in the BUFFER POOL AND MEMORY section of InnoDB Standard Monitor output:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

The following table describes buffer pool metrics reported by the InnoDB Standard Monitor.

Per second averages provided in InnoDB Standard Monitor output are based on the elapsed time since InnoDB Standard Monitor output was last printed.

Table 15.2 InnoDB Buffer Pool Metrics

NameDescription
Total memory allocated The total memory allocated for the buffer pool in bytes.
Dictionary memory allocated The total memory allocated for the InnoDB data dictionary in bytes.
Buffer pool size The total size in pages allocated to the buffer pool.
Free buffers The total size in pages of the buffer pool free list.
Database pages The total size in pages of the buffer pool LRU list.
Old database pages The total size in pages of the buffer pool old LRU sublist.
Modified db pages The current number of pages modified in the buffer pool.
Pending reads The number of buffer pool pages waiting to be read into the buffer pool.
Pending writes LRU The number of old dirty pages within the buffer pool to be written from the bottom of the LRU list.
Pending writes flush list The number of buffer pool pages to be flushed during checkpointing.
Pending writes single page The number of pending independent page writes within the buffer pool.
Pages made young The total number of pages made young in the buffer pool LRU list (moved to the head of sublist of “new” pages).
Pages made not young The total number of pages not made young in the buffer pool LRU list (pages that have remained in the “old” sublist without being made young).
youngs/s The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young. See the notes that follow this table for more information.
non-youngs/s The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young. See the notes that follow this table for more information.
Pages read The total number of pages read from the buffer pool.
Pages created The total number of pages created within the buffer pool.
Pages written The total number of pages written from the buffer pool.
reads/s The per second average number of buffer pool page reads per second.
creates/s The average number of buffer pool pages created per second.
writes/s The average number of buffer pool page writes per second.
Buffer pool hit rate The buffer pool page hit rate for pages read from the buffer pool vs from disk storage.
young-making rate The average hit rate at which page accesses have resulted in making pages young. See the notes that follow this table for more information.
not (young-making rate) The average hit rate at which page accesses have not resulted in making pages young. See the notes that follow this table for more information.
Pages read ahead The per second average of read ahead operations.
Pages evicted without access The per second average of the pages evicted without being accessed from the buffer pool.
Random read ahead The per second average of random read ahead operations.
LRU len The total size in pages of the buffer pool LRU list.
unzip_LRU len The length (in pages) of the buffer pool unzip_LRU list.
I/O sum The total number of buffer pool LRU list pages accessed.
I/O cur The total number of buffer pool LRU list pages accessed in the current interval.
I/O unzip sum The total number of buffer pool unzip_LRU list pages decompressed.
I/O unzip cur The total number of buffer pool unzip_LRU list pages decompressed in the current interval.
 

Notes:

  • The youngs/s metric is applicable only to old pages. It is based on the number of page accesses. There can be multiple accesses for a given page, all of which are counted. If you see very low youngs/s values when there are no large scans occurring, consider reducing the delay time or increasing the percentage of the buffer pool used for the old sublist. Increasing the percentage makes the old sublist larger so that it takes longer for pages in that sublist to move to the tail, which increases the likelihood that those pages are accessed again and made young. See Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”.

  • The non-youngs/s metric is applicable only to old pages. It is based on the number of page accesses. There can be multiple accesses for a given page, all of which are counted. If you do not see a higher non-youngs/s value when performing large table scans (and a higher youngs/s value), increase the delay value. See Section 15.8.3.3, “Making the Buffer Pool Scan Resistant”.

  • The young-making rate accounts for all buffer pool page accesses, not just accesses for pages in the old sublist. The young-making rate and not rate do not normally add up to the overall buffer pool hit rate. Page hits in the old sublist cause pages to move to the new sublist, but page hits in the new sublist cause pages to move to the head of the list only if they are a certain distance from the head.

  • not (young-making rate) is the average hit rate at which page accesses have not resulted in making pages young due to the delay defined by innodb_old_blocks_time not being met, or due to page hits in the new sublist that did not result in pages being moved to the head. This rate accounts for all buffer pool page accesses, not just accesses for pages in the old sublist.

Buffer pool server status variables and the INNODB_BUFFER_POOL_STATS table provide many of the same buffer pool metrics found in InnoDB Standard Monitor output. For more information, see Example 15.10, “Querying the INNODB_BUFFER_POOL_STATS Table”.

 

 缓冲池是InnoDB在主存中缓存表和索引数据的区域。缓冲池允许直接从内存访问频繁使用的数据，这加快了处理速度。在专用服务器上，高达80%的物理内存通常分配给缓冲池。

为了提高大容量读操作的效率，缓冲池被划分为可能容纳多行的页面。为了提高缓存管理的效率，缓冲池被实现为页面的链表;使用最近最少使用(LRU)算法的变体将很少使用的数据从缓存中老化。

使用LRU算法的变体将缓冲池作为列表进行管理。当需要空间向缓冲池添加新页时，将删除最近最少使用的页，并将新页添加到列表的中间。这种中点插入策略将列表视为两个子列表:

在头部，最近被访问的新(“年轻”)页面的子列表

在尾部，是最近访问次数较少的旧页面的子列表

理想情况下，将缓冲池的大小设置为实际的最大值，为服务器上的其他进程留下足够的内存，以便在不过度分页的情况下运行。缓冲池越大，InnoDB就越像一个内存数据库，从磁盘读取一次数据，然后在后续的读取过程中从内存访问数据。

 

 

 
posted @ 2022-09-19 20:44  papering  阅读(99)  评论(0)  编辑  收藏  举报