InnoDB存储结构这一篇就够了

文章目录

• • 一、innodb中的log总结
  • • 1. redo log(重做日志)
    • 2. undo log(撤销日志)
    • 3. bin log(二进制日志)
  • 二、change buffer
  • • 1. 简介
    • 2. 合并时机
    • 3. 优缺点
    • 4. 组成部分
    • 5. 参数配置
    • 6. 额外的内存池
  • 三、InnoDB存储结构
  • • 1. InnoDB逻辑存储结构
    • 2. 页的结构
    • 3.页存储数据过程
    • • user Records/free space
      • Page Directory
      • Page Header
      • File Header
      • File Trailer
      • 总结
  • 四、杂谈
  • • 1. 表空间

一、innodb中的log总结 1. redo log(重做日志)

redo log叫做重做日志，记录InnoDB的更改后的值,用来恢复数据（数据库宕机后数据恢复）。redo log用于保证事务的持久性。

redo log有两种类型：

• 位于内存中 redo log buffer 重做日志缓冲，类似于之前的缓冲池用于提升性能
• 位于磁盘中 redo log 重做日志文件，最终持久化的地方。

记录redo log的时机：

每一次数据修改（每个sql执行完）之后,(可能)脏页刷新之前。

缓冲写入磁盘时机：

1. redo log buffer的日志占据redo log buffer总容量的一半

2. 一个事务提交时 ，他的redo log都刷入磁盘

3. 后台线程定时刷新

4. MySQL关闭时 ，redo log都被写入磁盘
   

2. undo log(撤销日志)

undo log叫做撤销日志，undo log存在的意义是确保数据库事务的原子性

• 对记录做了变更 *** 作的时候就需要产生undo log，其中记录的是老版本的数据，当旧事务需要读取数据时，可以顺着undo链找到满足其可见性的记录。
• undo log通常以逻辑日志的形式存在。我们可以认为当delete一条记录时，undo log会产生一条对应的insert记录，反之亦然。当update一条记录时，会产生一条相反的update记录。
• undo log采用段segment的方式来记录，每个undo *** 作在记录的时候占用一个undo log segment。
• undo log也会产生redo log，因为undo log也要实现持久性保护。

purge *** 作

​ innodb中delete语句是在改记录中设置delete_flag =1 同理update *** 作是旧记录设置删除标识，再创建一条新记录。InnoDB使用undo日志进行旧版本的删除 *** 作，这个 *** 作称为purge *** 作。

3. bin log(二进制日志)

​ binlog 二进制日志文件，这个文件记录了MySQL所有的DML *** 作(或者内容)。通过binlog日志我们可以做数据恢复，增量备份，主主复制和主从复制等等。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志，与存储引擎无关，一旦缓冲池 或者磁盘产生数据则即会生成二进制日志。

binlog的模式有三种：STATEMENT、ROW、MIXED 。

1、STATMENT模式：基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave（主从复制模式）中的数据不一致(如sleep(暂停指定时间执行)函数， last_insert_id（自增）等情况下会出现问题)

2、ROW模式：基于行的复制，不记录每一条SQL语句的上下文信息，仅记录哪条数据被修改了，修改后的结果是什么

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、MIXED模式，混合模式的复制方式：如上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的相关 *** 作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

三种log添加时机

二、change buffer 1. 简介

​ 更改缓冲区是一种特殊的数据结构，数据不在缓冲池中时在不影响数据一致性的前提下，InooDB 会将这些更新 *** 作缓存在 change buffer 中。

​ 缓冲的更改可能由插入、更新或删除 *** 作(DML)引起，稍后当其他读取 *** 作将页面加载到缓冲池中时，这些更改会被合并。或满足一定条件时，合并成一次IO，刷新到磁盘上。

在内存中，更改缓冲区占用了缓冲池的一部分。在磁盘上，更改缓冲区是SYSTEM表空间的一部分，当数据库服务器关闭时，索引更改将在其中进行缓冲。

2. 合并时机

1. 数据加载到缓冲池中时：
2. 定期更新到磁盘中：

当有许多受影响的行和大量辅助索引需要更新时，更改缓冲区合并可能需要几个小时。在此期间，磁盘I/O会增加，这可能会导致磁盘绑定查询的速度显著减慢。在提交事务之后，甚至在服务器关闭和重新启动之后，更改缓冲区合并也可能继续发生。

3. 优缺点

优点：

​ DML *** 作比较多的情况有用

因为它可以减少磁盘读取和写入，所以更改缓冲区功能对于受I/O限制的工作最有价值，例如具有大量DML *** 作(如批量插入)的应用程序。

缺点：

​ 占用缓冲池的内存，导致缓冲池相关的性能受损。

​ 更改缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集几乎可以放入缓冲池，或者如果您的表的辅助索引相对较少，那么禁用更改缓冲可能会很有用。如果工作数据集完全适合缓冲池，则更改缓冲不会带来额外开销，因为它只适用于不在缓冲池中的页。

4. 组成部分

• insert buffer 新增数据

• delete buffer 将数据标记为删除

• purge buffer 将数据真正的删除

  更新 *** 作是一个delete buffer + insert buffer

5. 参数配置

• Innodb_CHANGE_BUFFERING

  您可以使用INNODB_CHANGE_BUFFING配置参数控制InnoDB执行更改缓冲的程度。您可以启用或禁用插入、删除 *** 作(最初将索引记录标记为删除时)和清除 *** 作(物理删除索引记录时)的缓冲。更新 *** 作是INSERT和DELETE的组合。默认Innodb_CHANGE_BUFFERING值为ALL。

  参数	含义
  all	默认值：缓冲区插入，删除标记 *** 作和清除
  none	不要缓冲任何 *** 作
  inserts	缓冲区插入 *** 作
  deletes	缓冲区删除标记 *** 作
  changes	缓冲插入和删除标记 *** 作
  purges	缓冲在后台发生的物理删除 *** 作

  ​ 您可以innodb_change_buffering在MySQL选项文件（my.cnf或 my.ini）中设置 参数，或使用SET GLOBAL 语句动态更改参数，该 语句需要足够的权限来设置全局系统变量。请参见 第5.1.8.1节“系统变量特权”。更改设置会影响新 *** 作的缓冲；现有缓冲条目的合并不受影响。

  mysql> show variables like 'Innodb_CHANGE_BUFFERING';
  +-------------------------+-------+
  | Variable_name           | Value |
  +-------------------------+-------+
  | innodb_change_buffering | all   |
  +-------------------------+-------+
  1 row in set (0.03 sec)
  

• innodb_change_buffer_max_size
  允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比，默认为25，最大为50

  mysql> show variables like 'innodb_change_buffer_max_size';
  +-------------------------------+-------+
  | Variable_name                 | Value |
  +-------------------------------+-------+
  | innodb_change_buffer_max_size | 25    |
  +-------------------------------+-------+
  1 row in set (0.03 sec)
  

  考虑在具有大量插入、更新和删除活动的MySQL服务器上增加Innodb_change_buffer_max_size，其中更改缓冲区合并没有跟上新的更改缓冲区条目的步伐，从而导致更改缓冲区达到其最大大小限制。

  考虑减少MySQL服务器上用于报告的静态数据的Innodb_change_buffer_max_size，或者如果更改缓冲区消耗了太多与缓冲池共享的内存空间，从而导致页面比预期更早地从缓冲池中过期。

  使用具有代表性的工作负载测试不同设置，以确定最佳配置。Innodb_change_buffer_max_size设置是动态的，它允许在不重新启动服务器的情况下修改设置。

• 监控变更缓冲区

​ InnoDB标准监视器输出包括更改缓冲区状态信息。要查看监视器数据，请发出该SHOW ENGINE INNODB STATUS语句。

mysql> SHOW ENGINE INNODB STATUS\G

##省略部分内容##
-------------------------------------
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 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

• INFORMATION_SCHEMA.INNODB_METRICS 表提供了在InnoDB标准监视器输出中找到的大多数数据点 ，以及其他数据点。要查看更改缓冲区度量标准以及每个度量标准的描述，请发出以下查询：

  mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
  

• INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 表提供有关缓冲池中每个页面的元数据，包括更改缓冲区索引和更改缓冲区位图页面。更改缓冲区页面由标识 PAGE_TYPE。IBUF_INDEX是更改缓冲区索引页面IBUF_BITMAP的页面类型，并且 是更改缓冲区位图页面的页面类型。

  查询该INNODB_BUFFER_PAGE表可能会带来很大的性能开销。

  例如，您可以查询该 INNODB_BUFFER_PAGE表以确定缓冲池页面总数中所包含的IBUF_INDEX和 的大概数量 IBUF_BITMAP。

  #查询该INNODB_BUFFER_PAGE表以确定缓冲池页面总数中
  #所包含的`IBUF_INDEX`和 的大概数量 `IBUF_BITMAP`
  
  mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
         WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
         (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
         (SELECT ((change_buffer_pages/total_pages)*100))
         AS change_buffer_page_percentage;
  +---------------------+-------------+-------------------------------+
  | change_buffer_pages | total_pages | change_buffer_page_percentage |
  +---------------------+-------------+-------------------------------+
  |                  25 |        8192 |                        0.3052 |
  +---------------------+-------------+-------------------------------+
  

• Performance Schema 提供更改缓冲区互斥锁等待检测，以进行高级性能监视。要查看更改缓冲区检测，请发出以下查询：

  mysql> SELECT * FROM performance_schema.setup_instruments
         WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
  +-------------------------------------------------------+---------+-------+
  | NAME                                                  | ENABLED | TIMED |
  +-------------------------------------------------------+---------+-------+
  | wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
  | wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
  | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
  +-------------------------------------------------------+---------+-------+
  

6. 额外的内存池

对一些数据结构本身的存储，会在该池中申请，不够则从缓冲池申请

例如，分配了缓冲池（innodb_buffer_pool）,但是每个缓冲池中的帧缓冲frame buffer和对应缓冲控制的对象，这些对象记录了一些诸如LRU、锁、等待等信息。这个对象所占的内存，就需要从额外空间中申请。

三、InnoDB存储结构 1. InnoDB逻辑存储结构

InnoDB表中的每行数据是基于B+树存储的，在B+树之外InnoDB对数据文件中数据的管理和组织定义一个新的逻辑结构如下图所示：(表空间是由各个段组成，有数据段，索引段，回滚段等。数据与索引段都是用B+树数据结构(逻辑结构)。

.jpg)]

• 表空间(tablespace)：所有的数据都被逻辑地存放在表空间中，同一个数据库可以共享一个表空间又了可以拥有各自私有的表空间，私有表空间存储数据、索引、和插入缓冲BitMap页，其他的数据如回滚（undo）信息、系统事务信息等还是存储在共享表空间里。
• **段(segment)：表空间中的数据分类为各个段：数据段(Left node segment)、索引段(Non-left node segment)、回滚段(Rollback segment)等。
• **区(extend)：**各个段中继续拆分为区,每个区大小固定为 1M，区是由连续的页组成的。一个页的大小为 16 KB，所以每个区有64个连续的页。即使支持压缩页，区的大小也是固定的，页数会发生变化而已。
• 页(page)：区可以继续拆分为页，页是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位。页分为几种类型：**数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）**等，页的大小为16kb。
• 行(row): 在InnoDB中，数据是按照行的格式来存储的。

在一般情况下，InnoDB一次最少从磁盘中读取一页的内容到内存中，一次最少把内存中的一页内容刷新到磁盘中。

2. 页的结构

，InnoDB的页结构分为七个部分，下面用表格说明一下各个部分对应的作用：

名称	中文名	占用空间大小	简单描述
File Header	文件头	38字节	描述页的信息
Page Header	页头	56字节	页的状态信息
Infimum + SupreMum	最小记录和最大记录	26字节	两个虚拟的行记录zzzzz
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页目录	不确定	存放着数据页组，形成的slot，记录相对位置
File Trailer	文件结尾	8字节	结尾信息

innodb数据页结构如图所示：

3.页存储数据过程 user Records/free space

当我们在存储数据的时候，记录会存储到User Records部分 。但是在一个页新形成的时候是不存在User Records 这个部分的，每当我们在插入一条记录的时候，都会从Free Space中去申请一块大小符合该记录大小的空间并划分到User Records，当Free Space的部分空间全部被User Records部分替换掉之后，就意味着当前页使用完毕，如果还有新的记录插入，需要再去申请新的页，过程如下：

Page Directory

页中记录是一个按照大小从小到大连续的单向链表,根据主键查询在数据量比较大的情况下性能回比较差(链表遍历)，所以其提供了对页的数据进行分组 二分查找的方式即为Page Directory。

---

1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。

2. 每个组的最后一条记录的头信息中的`n_owned`属性表示该组内共有几条记录。

3. 将每个组的最后一条记录的地址偏移量按顺序存储起来，每个地址偏移量也被称为一个`槽`（英文名：`Slot`）。这些地址偏移量都会被存储到靠近`页`的尾部的地方，页中存储地址偏移量的部分也被称为`Page Directory` 。

InnoDB分组的条数控制

InnoDB 对每个分组中的记录条数是有规定的，对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。所以分组是按照下边的步骤进行的：

• 初始情况下一个数据页里面只有最小记录和最大记录（伪记录），它们属于不同的分组，也就是两个；
• 之后插入的每一条记录都会放到最大记录所在的组，直到最大记录所在组的记录数等于8条；
• 当最大记录所在组中的记录数等于8条的时候，如果还有记录插入的话，就会将最大记录所在组平均分裂成2个组，这个时候最大记录所在组就只剩下4条记录，这里再把这条记录再放入最大记录所在组；

页中数据的检索方式

下图插入16条数据，如下分成了五个槽 0~4数据id为1-16，下面看看使用二分查找快速定位到某个组的数据。

以查找主键id=5情况：

1. 首先得到中间槽的位置：(0 + 4)/2 = 2 ,所以得到槽2，根据槽2的地址偏移量知道它的主键值是8，因为8>5，设置high=2 ，low不变；

2. 再次计算中间槽的位置：(0 + 2)/2 = 1 ,所以得到槽1，根据槽1的地址偏移量知道它的主键值是4, 因为4<5，设置low=1 ，high不变；

3. 因为high - low的值为1，所以确定主键值为5的记录在槽1和槽2之间，接下来就是遍历链表的查找了；

所以在一个数据页中查找指定主键值的记录的过程分为两步：

1. 通过二分法确定该记录所在的槽。

2. 通过记录的next_record属性组成的链表遍历查找该槽中的各个记录。

Page Header

​ Page Header 记录数据页存储的记录状态，比如存储记录个数、首条记录地址，Page Directory中存储了多少个槽等等。

名称	大小（单位：byte）
PAGE_N_DIR_SLOTS	2	在页目录中的槽数量
PAGE_HEAP_TOP	2	第一个记录的地址
PAGE_N_HEAP	2	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE	2	指向可重用空间的地址（就是标记为删除的记录地址）
PAGE_GARBAGE	2	已删除的字节数，行记录结构中delete_flag为1的记录大小总数
PAGE_LAST_INSERT	2	最后插入记录的位置
PAGE_DIRECTION	2	最后插入的方向
PAGE_N_DIRECTION	2	一个方向连续插入的记录数量
PAGE_N_RECS	2	该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID	2	修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL	2	当前页在索引树中的位置，高度
PAGE_INDEX_ID	8	索引ID，表示当前页属于哪个索引
PAGE_BTR	10	非叶节点所在段的segment header，仅在B+树的Root页定义
PAGE_LEVEL	10	B+树所在段的segment header，仅在B+树的Root页定义

• PAGE_DIRECTION

  假如新插入的一条记录的主键值比上一条记录的主键值比上一条记录大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。

• PAGE_N_DIRECTION

  假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

File Header

如果说Page Header描述的是页内的各种状态信息，那么File Header描述的就是页外的各种状态信息，比方说这个页的编号，它的上下页地址。File Header是InnoDB页的第一部分，这个部分占用固定的38个字节，下边我们看看这个部分的各个字节都是代表啥意思吧：

名称	大小（单位：byte）	描述
FIL_PAGE_SPACE_OR_CHKSUM	4	页的校验和（checksum值）
FIL_PAGE_OFFSET	4	页号
FIL_PAGE_PREV	4	上一个页的页号
FIL_PAGE_NEXT	4	下一个页的页号
FIL_PAGE_LSN	8	最后被修改的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE	2	该页的类型：B+树叶子节点、Undo log页、索引非叶子节点页、Insert Buffer空闲列表、Insert Buffer BitMap、系统页、扩展描述页、BLOB页
FIL_PAGE_FILE_FLUSH_LSN	8	仅在系统表空间的一个页中定义，代表文件至少被更新到了该LSN值，独立表空间中都是0
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	页属于哪个表空间

对照着这个表格，我们看几个目前比较重要的部分：

• FIL_PAGE_SPACE_OR_CHKSUM

  这个代表当前页面的校验和（checksum）。啥是个校验和？就是对于一个很长很长的字节串来说，我们会通过某种算法来计算一个值，这个值就称为校验和。这样在比较两个很长的字节串之前先比较这两个长字节串的校验和，如果校验和都不一样两个长字节串肯定是不同的（hashCode和equals），所以省去了直接比较两个比较长的字节串的时间损耗(和后面的File Trailer里面的那个相对应，看到后面您就明白了)。

• FIL_PAGE_OFFSET

  每一个页都有一个单独的页号，就跟您的身份z号码一样，InnoDB通过页号来可以唯一定位一个页。

• FIL_PAGE_TYPE

  这个代表当前页的类型，我们前边说过，InnoDB为了不同的目的而把页分为不同的类型，本集中介绍的其实都是存储记录的数据页，其实还有很多别的类型的页：

• FIL_PAGE_PREV和FIL_PAGE_NEXT

  一张表中可以有成千上万条记录，一个页只有16KB，所以可能需要好多页来存放数据，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号（双向链表）。

File Trailer

文件尾部是为防止脏页刷盘时断电或mysql故障导致页面不完整的情况。

尾部包含2部分：页的校验和（与文件头部的校验和一致）和LSN（和头部的LSN一致）。

原理：

​ 当页数据被修改，这个修改会先在内存中的页生效，内存中的页会重新计算校验和与LSN并把这两个属性更新到文件头部和尾部。

​ 页刷盘时，是按照 *** 作系统的块（block）为单位刷盘的而不是按页（page）刷盘，刷盘从页头部开始。如果发生断电，一个页只有部分块刷盘成功，那么File Header的 校验和与LSN 肯定与 File Trailer的 校验和与LSN不同。

此时就会通过 redo log 文件恢复该页。

总结

1. InnoDB为了不同的目的而设计了不同类型的页，用于存放我们记录的页也叫做数据页。
2. 一个数据页可以被分为7个部分，分别是
   • File Header，表示文件头，占固定的38字节。
   • Page Header，表示页里的一些状态信息，占固定的56个字节。
   • Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
   • User Records：真实存储我们插入的记录的部分，大小不固定。
   • Free Space：页中尚未使用的部分，大小不确定。
   • Page Directory：页中的记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
3. 每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单向链表。
4. InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：
   • 通过二分法确定该记录所在的槽。
   • 通过记录的next_record属性组成的链表遍历查找该槽中的各个记录。
5. 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。
6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

四、杂谈 1. 表空间

InnoDB在本地会生成两个文件 表定义文件：表名.frm，共享表空间文件：ibdata1，私有表空间文件：表名.ibd 表空间文件用于存储索引和数据。

MyISAM在本地生成三个文件：xxxx.firm，存储表信息；xxxx.MYI，存储索引信息和数据的指引路径；xxxx.MYD，存储数据信息。