深入理解MySQL原理之五--如何高效利用InnoDB存储引擎

「爱情、让人受尽委屈。」 2022-10-16 14:26 72阅读 0赞

# 一、前言 #

我们通常所说的MySQL，一般默认指MySQL数据库，实际上，MySQL数据库包括数据库实例以及数据库存储，MySQL实现的是数据库实例引擎，负责对SQL的解析，优化，缓存等功能，对于数据的存储，选用各类的存储引擎，包括MyISAM，InnoDB，Memory等等，从MySQL5.5.8版本开始后，InnoDB存储引擎是默认的存储引擎。

其实前面我们所讲的索引，事务以及锁等，都是InnoDB的特性，其体系架构如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70][]

InnoDB由内存，后台线程和文件系统几部分构成，今天我们重点研究下InnoDB实现机制，并在此基础上了解如何高效利用这些特性。我们将了解到以下内容：

1、InnoDB内存区域区域包含哪些内容，缓存池包含哪些信息？

2、缓存池中是如何进行内存淘汰的，刷盘机制是什么？

3、重做日志内存和文件是如何确保数据的可靠性，与二进制日志文件有什么区别？

4、后台的线程包含哪些种类，其作用分别是什么？

5、表空间是如何组织的，一条行信息是如何记录在磁盘文件中的？

5、undo日志在文件中如何组织的，每种操作记录的内容是什么，又是如何确保事务的原子性，以及MVCC机制？

# 二、内存 #

有效利用内存，填补磁盘读写性能的不足，对于存储引擎来说是必不可少的，InnoDB使用内存，主要分为三部分，缓冲池，重做日志缓冲，额外内存池(注意与MySQL引擎的内存区别)，如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 1][]

## 1、缓冲池 ##

缓冲池是InnoDB最主要的内存部分，可以使用如下指令show variables like 'innodb\_buffer\_pool\_size'，查看缓存池大小。

![20210605073122558.png][]

InnoDB也支持设置多个缓存池实例(instance)，在MySQL8.0中，最大可支持64个，每个实例(instance)之间是相互独立的，支持并发访问，一个page只会存放在一个Instance中。可以用show variables like 'innodb\_buffer\_pool\_instances'查看实例数

![20210605073520678.png][]

### (1)缓存池页面类型 ###

如上图所示，除了所熟悉的数据页，索引页，还包括插入缓冲，锁信息，自适应哈希索引，数据字典信息等。

数据页和索引页默认大小为16KB(与磁盘物理页大小保持一致)，读取页时，优先读取缓冲池页，如果没有，再从磁盘文件中读取。写入时，也是先写入缓存池的页面，通过checkpoint机制(下面会详解)刷新到磁盘。

插入缓冲，对于非聚集索引的插入是离散的，通过插入缓冲，变离散为顺序，然后再合并到非聚集索引的子节点，提高插入性能。

自适应哈希索引，我们知道InnoDB采用的B+树索引，对于深度为3-4层的B+树，需要进过3-4次查询。对于哈希索引，已知key值，那么一次查询就能获取value值，查询效率大大提升，当然哈希索引的限制也非常多，比如无法满足模糊查询，范围查询等，但可以有条件的使用，InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引,称之为自适应哈希索引(AHI)。比如 select \* from table where a=xxxx,这个查询访问多次，就有可能建立AHI，其满足条件为：

*  以该模式访问了100次
 *  页通过该模式访问了N次，其中N=页中记录\*1/16

锁信息，主要保存InnoDB相关锁的信息，如行锁，间隙锁等。

数据字典信息，主要保存的是表名，列名，索引名，索引列名等元数据。

### (2)淘汰算法 ###

缓存池作为内存的一部分，其大小是有限了，这就意味着必须有淘汰机制，InnoDB采用的是LRU （lastest Recent Used）淘汰算法，并在该算法基础上进行了改造。

1、LRU

传统的LRU，将最近使用的数据放到表头，就形成了一个按照最近访问时间排序的列表，达到淘汰条件时，从后往前删除即可。

![20210605095558210.png][]

InnoDB的LRU在传统的基础了进行了改造，如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 2][]

当新的页插入时，并没有放到表头，而是定义了一个midpoint的位置，该位置可以由innodb\_old\_blocks\_pct设置，

![20210605102023157.png][]

这里的37表示距离表尾的位置为37%。那为什么要进行这个改造呢？在SQL查询时，如果是全表扫描，而这些数据也就是用一次，如果全部插入表头，势必会让真正的活跃数据淘汰。采用midpoint，有了缓冲的效果，那什么时候放到热端呢，可以通过配置innodb\_old\_blocks\_time设置时间。

![20210605103641206.png][]

需要注意的是，LRU list仅对数据页和索引页，不包含插入缓冲，自适应哈希等缓冲区。

可以通过调整innodb\_old\_blocks\_pct的值来扩大或者缩小活跃数据区域的大小。InnoDB有个重要的观察指标buffer pool hit rate，该指标表示缓冲池的命中率，100%表示缓冲池运行非常良好，小于95%，那就需要考虑是否由于全表扫描引起了LRU列表被污染的问题。

2、Free list

数据库启动时，LRU list是空的，这时的页都在Free list中，当需要从缓存池中分页时，首先从Free列表中查找是否有可用的空闲页，若有，则从Free list中删除，加入到LRU List，否则，根据LRU算法，淘汰LRU 列表末尾的页。

3、Flush list

在LRU列表中的页被修改后，称之为脏页(dirty page)，即缓冲池中的页和磁盘上的页数据不一致。这时会通过checkpoint机制将脏页刷新回磁盘，而Flush list中的页即为脏页列表。需要注意的是，脏页既存在于LRU列表，也存在于Flush列表中。

LRU：用来管理缓冲池中页的可用性。

Flush：用来管理将页刷新回磁盘。

我们通过show engine innodb status\\G;查看下上面各个列表的数据页大小。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 3][]

下面我们分析下数据：

*  1024(Free buffers)+7127(Database pages) < 8191(Buffer pool size)，这是因为缓冲池中的一些页面分配给了插入缓冲，自适应哈希等。
 *   2610(old database pages)/7127(database pages)=0.37，即为innodb\_old\_blocks\_pct的值。

## 2、重做日志缓冲(redo log buffer) ##

为了确保数据的可靠性，InnoDB存储采用WAL（write ahead log）机制，即先记录事务重做日志，再进行数据提交。那么在记录事务重做日志时，先写入重做日志缓存，然后再通过一定策略刷新到磁盘日志文件，以下三种情况下会将数据刷新到磁盘。

*  Master thread(后面章节介绍)每秒会刷新数据到磁盘文件，无论事务是否提交。
 *  每个事务提交时，会刷新重做日志到文件。
 *  当重做日志缓存池剩余空间小于1/2时，重做日志缓存刷新到文件。

由于刷新的频率非常高，该缓冲区一般不需要设置很大，可以使用show variables like 'innodb\_log\_buffer\_size'\\G;查看。需要注意的是，重做日志缓冲区是循环使用的，无需淘汰算法。

![20210605114448103.png][]

## 3、额外内存池 ##

在innodb存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。在对一些数据结构本身分配内存时，需要从额外获得内存池中申请，当该区域的内存不够时，Innodb会从缓冲池中申请。但是每个缓冲池中的frame buffer还有对应的缓冲控制对象，这些对象记录了诸如LRU、锁、等待等方面的信息，而这个对象的内存需要从额外内存中申请。因此，当你申请了很大的Innodb缓冲池时，这个值也应该相应增加；

简单理解为，额外缓冲池用于管理缓冲池的内容的，所以缓冲池越大额外换池也需要越大；

## 4、Checkpoint机制 ##

内存的大小是有限的，最终要保存到磁盘上，当 产生脏页后，InnoDB如何将脏页刷新到磁盘呢，是不是一旦有脏页就刷新？显然这不是好方式。InnoDB引擎的刷盘机制称之为Checkpoint机制，以一定的频率将所有已提交事务的脏页刷到磁盘，并记录最新的已提交事务的LSN号。

### (1)LSN ###

先看下LSN(lLog sequence number )概念，脏页刷盘后，需要记录上次刷盘的位置，下次刷盘的时候从这个位置继续即可，避免重复刷盘，这个位置标记就是LSN。我们查下引擎状态show engine innodb status\\G,可以看到有四个LSN相关的数据。

![20210605154056439.png][]

那这几个LSN分别代表什么，我们先也了解下这几个LSN产生的过程。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 4][]

*  创建日志，事务开始时，创建一条事务日志，生产一个LSN，其生成规则为，前一个日志的LSN+当前事务日志的大小。标记位LSN1
 *  日志刷盘，事务的重做日志刷盘到磁盘文件，当前已经写入磁盘日志文件的LSN，标记位LSN2。
 *  数据刷盘，脏页数据写入到磁盘上的数据文件，当前数据页中最旧的脏页数据(未刷盘)对应的LSN，标记位LSN3。
 *  写CKP，被当作Checkpoint写入日志文件，即已经写入Checkpoint的LSN，标记位LSN4。

可以看到创建日志阶段，就生成了一个新的事务LSN，后面只会记录当前阶段处理事务的LSN的位置，而不会重新生成该事务的LSN。按照WAL策略，LSN1>=LSN2>=LSN3>=LSN4。通过这四个值的比较，就可以快速查看到当期脏页刷盘的状态。

### (2)触发机制 ###

那么什么时间点触发checkpoint呢？对于InnoDB存储引擎，有两种CheckPoint，分别为：

*  Sharp Checkpoint
 *  Fuzzy Checkpoint

**Sharp Checkpoint**

这种模式是指一次性将所有的脏页刷盘。该模式一般使用在数据库发生关闭时，在数据库运行阶段，使用Fuzzy Checkpoint模式

**Fuzzy Checkpoint**

与Sharp Checkpoint不同，Fuzzy Checkpoint采用小批量，逐次刷新，最终达到将所有的脏页刷盘。Fuzzy Checkpoint将发生在以下几个阶段。

*  Master Thread Checkpoint

主线程每秒或者每10秒(下面主线程章节将重点介绍)，根据脏页的比例，以一定的数量进行刷盘。这个过程中是异步的，不会阻塞用户查询线程。

*  FLUSH\_LRU\_LIST Checkpoint

InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用，如果没有100个可用空闲页，那么InnoDB存储引擎会通过淘汰算法，将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH\_LRU\_LIST Checkpoint。1.2.X版本开始，这部分的刷新操作是由page cleaner thread完成。

*  Async/Sync Flush Checkpoint

前面我们介绍过，重做日志缓冲是循环使用的，实际上重做日志文件也是循环使用的(后面会详解)，为了确保数据的可靠性，在重做日志文件失效前，比较LSN2与LSN4，强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。1.2.X版本开始，这部分的刷新操作是由page cleaner thread完成。

*  Dirty Page too much

即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb\_max\_dirty\_pages\_pct控制，该值为75%，表示当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘。

# 三、后台线程 #

后台线程负责IO的请求并处理，将数据保存到内存中，并将最新的修改数据刷新到磁盘文件，同时保证在数据库发生异常情况下，InnoDB能恢复到正常运行状态。按照类型又分为Master thread(主线程)，IO thread(IO线程)，Purge Thread(回收线程)，Page Cleaner Thread(脏页刷新线程)。

## 1、Master thread(主线程) ##

顾名思义，主线程是InnoDB最核心的线程，承担了主要的工作。主线程由多个循环组成，包括主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)、暂停循环(suspend loop)。之间的转换关系如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 5][]

**(1)、主循环**

主循环按照每秒执行和每10s执行两个周期。

**每秒执行**

(1)重做日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)

即使某个事务还没有提交，InnoDB依然将将重做日志的缓存中的内容刷新到重做日志文件。这么做的目的，就是提升事务提交时间。

(2)合并插入缓冲(insert buffer)（可能）

插入缓冲是缓冲池的一部分，同时也是物理页的一部分。对于非聚集索引的插入，一般是离散的，为了提升插入的性能，先放到插入缓冲，再以一定的频率将插入缓冲和非聚集索引页子节点的合并操作。

InnoDB存储引擎会判断当前一秒内的IO次数是否小于5次，如果小于5次，就认为IO压力小，可以执行合并操作。在1.0.X之后的版本，可以设置innodb\_io\_capacity值，如小于该值的5%，就执行合并操作。

(3)之多刷新100个InnoDB的缓存池中的脏页到磁盘(可能)

这个也是主线程最核心的功能，InnoDB存储引擎通过判断当前缓冲池脏页的比例是否超过了配置文件中的innodb\_max\_dirty\_pages\_pct（默认为90，表示90%），如果超过了这个阀值，则认为需要做刷新操作，将100个脏页刷新到磁盘。

1.0.X版本后，每次刷新脏页的数量可以通过innodb\_io\_capacity值进行设置，灵活调整。innodb\_max\_dirty\_pages\_pct也调整为75。

![20210605170820791.png][]

(4)如果当前没有用户活动，则切换到background loop(可能)

**每10s执行**

(1)刷新100个脏页到磁盘(可能情况下)

InnoDB存储引擎判断过去10s之内的磁盘IO操作是否小于200次，如果是，则认为当前有足够的IO能力，将100个脏页刷新到磁盘。同样，1.0.X版本后，每次刷新脏页的数量可以通过innodb\_io\_capacity值进行设置。

(2)合并至多5个插入缓冲(总是)

不同于每秒可能合并插入缓冲，这次的合并插入缓冲操作总会在这个阶段进行。

(3)将日志缓冲刷新到磁盘(总是)

与每秒的操作一样，再进行一次将日志缓冲刷新到磁盘操作。

(4)删除无用的Undo页(总是)

事务提交后，使用的undolog可能不在需要，InnoDB会判断是否可以删除，如果可以，则立即删除，在执行full purge操作时，每次最多尝试回收20个undo页。在1.0.X版本后，引入了参数innodb\_purge\_batch\_size,可以控制每次full purge回收Undo的数量。

在1.1版本之后，undo的回收和分配从主线程剥离出来，有单独的Purge Thread线程完成。

(5)刷新100个或者10个脏页到磁盘(总是)

InnoDB存储引擎会判断缓冲池中脏页的比例(buf\_get\_modified\_ratio\_pct)，如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%，则只需刷新10%的脏页到磁盘。

**(2)、background loop**

若当前没有用户活动(数据库空闲)或者数据库关闭(shutdown)，就会切到这个循环，主要包含以下操作：

(1)删除无用的Undo页(总是)

(2)合并20个插入缓冲(总是)

(3)跳回主循环(总是)

(4)跳到flush loop(可能)

**(3)、flush loop**

不断的刷新100个页，直到符合条件，即脏页比例小于innodb\_max\_dirty\_pages\_pct，跳过到 suspend loop

**(4)、suspend loop**

suspend loop将Master Thread挂起，等待事件的发生，重新跳转到主loop。

总之，主线程通过每秒，以及每10s这两这个执行周期，将内存中的脏页(数据页，索引页)，插入缓冲，重做日志缓冲刷新到磁盘，以及执行undo页的删除。

## 2、IO Thread ##

主线程将缓冲刷新到磁盘，IO线程则负责处理IO请求的回调，分别为write、read、insert buffer和log IO thread，其中write，read线程分别为4个。

数据库采用AIO，即异步IO，当用户发出一条索引扫描查询，该SQL可能需要扫描多个索引页，也就是需要多次IO操作，此时，就可以通过多个read thread并行读取，然后等待所有的IO返回，这样大大提升了磁盘操作性能。

## 3、Purge Thread ##

为了减轻Master Thread的压力，提升性能，从1.1版本后，undo页的回收和分配从主线程中独立出来，形成Purge Thread，可以通过设置innodb\_purge\_threads来决定启动多少个Purge thread线程。可以通过show variables like 'innodb\_purge\_threads';查看线程数

![20210602200954281.png][]

## 4、Page Cleaner Thread ##

从1.2.X版本后，对于脏页的刷新操作放到单独的线程中完成，即PageCleaner Thread，从而进一步减轻Master Thead的压力。

# 四、文件 #

前面所讲的缓存也好，线程也罢，其目的就是为了如何更快更高效的将数据持久化磁盘，并从磁盘读取加载。相关的数据包括索引，数据，redo log，undo log等等，最终是要保持到磁盘的。那么这些数据在磁盘上是如何组织的呢？本节将重点探讨。

## 1、表空间 ##

从InnoDB存储引擎的逻辑存储结构看，所有的数据(索引，数据，undolog)都被逻辑的放到一个空间中，称之为表空间，这个表空间可以由很多个文件组成，称之为表空间文件。

### (1)共享表空间和独立表空间 ###

**共享表空间**

所有表的数据都放到一个单独的表空间中，将一个大文件分割成多个小文件放置到磁盘上，从Innodb的官方文档中可以看到，其表空间的最大限制为64TB。因为多个表的索引和数据是混合存储的，对于磁盘管理带了很大的困难，比如表做了大量的删除操作，这部分就会在表空间上产生大量空隙。

看下共享表空间的路径，这里只有一个ibdtata1文件，初始分配12M，当空间用完后，该文件自动增长(autoextend)

![2021060822293098.png][]

在/var/lib/mysql下就能找到这个文件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 6][]

**独立表空间**

针对共享表空间的问题，独立表空间允许每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

通过修改innodb\_file\_per\_table的参数值可以设置表空间的管理模式。

innodb\_file\_per\_table=1 为使用独占表空间

innodb\_file\_per\_table=0 为使用共享表空间

![20210608221654850.png][]

设置独占后，我们看库目录下文件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 7][]

.frm为表结构文件，.idb为独立的表空间文件，需要注意的是，这些单独的表空间文件仅存储该表的数据，索引和插入BITMAP信息，其余的信息还是存放在默认的表空间中，

### (2)存储结构 ###

表空间是存储逻辑的最高层，其中有段，区，页等层级结构。如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 8][]

**段(segment)**

表空间是由段组成，常见的有数据段，索引段，回滚段。数据段即B+数的叶子节点，索引段为B+数的非叶子节点，回滚段为单独段，后面将重点介绍。

**区(extent)**

区是一段连续的页组成，大小为1M，即64个页(一个页为16KB)。表创建时，默认的大小为96K(6个页)，是不满足一个区大小条件的，实际上，数据段开始时用32个碎片页来组成，使用完这些页后，再申请区，为了保证区中页的连续性，Innodb存储引擎一次从磁盘申请4-5个区。

**页(page)**

页也称之为块(block)，是InnoDB磁盘管理的最小单位，每个页面的大小为16K，当然也可以通过设置innodb\_page\_size对页面大小进行设置。常见的页类型包括数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，二进制大对象页等。

**行(row)**

InnoDB引擎是面向行的存储，数据页中存放一条条行记录，支持以下几种行记录格式：

*  COMPACT 紧凑型行格式
 *  REDUNDANT 字段长度偏移类型
 *  DYNAMIC 动态行格式
 *  COMPRESSED 压缩行格式

接下来，我们重点介绍这几个格式内容

### (3)行记录格式 ###

InnoDB1.0.X版本之前，提供了Compact和Redundant两种格式存放数据，Redundant格式是为了兼容之前版本而保留的。1.0.X版本之后，支持了新的两种格式Compressed和Dynamic。

**1、Compact**

Compact行记录是在MySQL5.0中引入的，其设计目的是高效的存储数据，一个页中行数据越多，其性能就越高。其格式如下：

<table> 
 <tbody> 
  <tr> 
   <td style="width:162px;">变长字段长度列表</td> 
   <td style="width:101px;">NULL标志位</td> 
   <td style="width:124px;">记录头信息</td> 
   <td style="width:108px;">额外信息</td> 
   <td style="width:78px;">列1数据</td> 
   <td style="width:79px;">列2数据</td> 
   <td style="width:69px;">....</td> 
  </tr> 
  <tr> 
   <td style="width:162px;">每个字段最长2个字节</td> 
   <td style="width:101px;">不固定</td> 
   <td style="width:124px;">5个字节</td> 
   <td style="width:108px;">13或者19字节</td> 
   <td style="width:78px;"></td> 
   <td style="width:79px;"></td> 
   <td style="width:69px;"></td> 
  </tr> 
 </tbody> 
</table>

*  **变长字段长度列表**

针对于变长字段列入`VARCHAR`,`VARBINARY`,`TEXT`,`BLOB`等变长字段类型，由于存储的字段长度不固定，如果不记录该字段的真实长度，那么就无法计算出这一行的准确长度,那么就无法进行行与行的切分。比如有以下一条行记录。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>t1(varchar 10)</td> 
   <td>t2(varchar 10)</td> 
   <td>t3(char 10)</td> 
   <td>t4(varchar 10)</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>ab</td> 
   <td>abc</td> 
   <td>abcd</td> 
  </tr> 
 </tbody> 
</table>

t1，t2，t4是varchar的变长类型，实际字节长度分别为1,2,4。那么变长字段列表列表，按照逆向顺序(从后往前，即t4，t2，t1)标记04,02,01。对于列的长度，

如果小于255字节，用1个字节表示；如果大于255个字节，用2个字节表示，变长字段的长度最长就是2个字节，所以变长字段最大长度是65535（2^16-1）字节(**注意不是字符**)。

*  **NULL标志位**

用bit位标记哪些列为null，一个bit位标记一个列，1表示是，0表示否，字段不够8的倍数时，高位补0。比如

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>t1(varchar 10)</td> 
   <td>t2(varchar 10)</td> 
   <td>t3(char 10)</td> 
   <td>t4(varchar 10)</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>null</td> 
   <td>null</td> 
   <td>abcd</td> 
  </tr> 
 </tbody> 
</table>

8个bit为00000110，表示2,3列上为null，转化为16进制为0x06。标记完成后，就可以实现null值不占空间。

*  **记录头信息**

记录行的位置，页信息，类型等信息，是固定的五个字节的大小

<table style="width:554px;"> 
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td style="width:84px;">大小(bit)</td> 
   <td style="width:371px;">描述</td> 
  </tr> 
  <tr> 
   <td>预留位</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">没有使用</td> 
  </tr> 
  <tr> 
   <td>预留位</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">没有使用</td> 
  </tr> 
  <tr> 
   <td>delete_flag</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">删除标识</td> 
  </tr> 
  <tr> 
   <td>min_rec_flag</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">B+树每层非叶子节点中最小的目录项记录都会添加该标记</td> 
  </tr> 
  <tr> 
   <td>n_owned</td> 
   <td style="width:84px;">4</td> 
   <td style="width:371px;">该记录拥有的记录数，一个页有多条记录，这些记录会再进行分组(Slot)，每个分组有个老大，老大会存储这个分组中有几条记录。</td> 
  </tr> 
  <tr> 
   <td>heap_no</td> 
   <td>13</td> 
   <td>当前记录在页中相对位置</td> 
  </tr> 
  <tr> 
   <td>record_type</td> 
   <td>3</td> 
   <td>记录类型 0表示普通记录 1表示B+树非叶子节点的目录项记录 2表示Infimum记录 3表示Supremum记录</td> 
  </tr> 
  <tr> 
   <td>next_record</td> 
   <td>16</td> 
   <td>下条记录的相对位置</td> 
  </tr> 
  <tr> 
   <td>total</td> 
   <td style="width:84px;">40</td> 
   <td style="width:371px;"></td> 
  </tr> 
 </tbody> 
</table>

*  ** 额外信息**

这个信息是MySQL自动生成的信息，主要包括row\_id 行号 ，trx\_id事务id ，roll\_pointer 回滚指针。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td>大小(字节数)</td> 
   <td>是否必须</td> 
   <td>描述</td> 
  </tr> 
  <tr> 
   <td>row_id</td> 
   <td>6</td> 
   <td>否</td> 
   <td>行号，当没有主键也没有不为null的Unique键时才会生成</td> 
  </tr> 
  <tr> 
   <td>trx_id</td> 
   <td>6</td> 
   <td>是</td> 
   <td>事务ID</td> 
  </tr> 
  <tr> 
   <td>roll_pointer</td> 
   <td>7</td> 
   <td>是</td> 
   <td>回滚指针</td> 
  </tr> 
 </tbody> 
</table>

其他的是字段的实际数据，**主要注意的是，对于null值字段不占空间**。

2、Redundant

Redundant是MySQL5.0版本之前的InnoDB行记录存储方式，其格式如下

<table style="width:651px;"> 
 <tbody> 
  <tr> 
   <td style="width:134px;">字段长度偏移列表</td> 
   <td style="width:90px;">记录头信息</td> 
   <td style="width:123px;">额外信息</td> 
   <td style="width:75px;">列1数据</td> 
   <td style="width:75px;">列2数据</td> 
   <td style="width:60px;">....</td> 
  </tr> 
  <tr> 
   <td style="width:134px;">每个字段最长2个字节</td> 
   <td style="width:90px;">6个字节</td> 
   <td style="width:123px;">13或者19字节</td> 
   <td style="width:75px;"></td> 
   <td style="width:75px;"></td> 
   <td style="width:60px;"></td> 
  </tr> 
 </tbody> 
</table>

*  **字段长度偏移量列表**

不同于Compact，字段长度偏移量列表是记录所有的字段，而不仅是变长字段。如果整行长度小于255，每个字段用一个字节表示，如果整行长度大于255，每个字段则用2个字节表示。我们来看下这条记录的字段长度偏移量列表

字段的偏移量是从隐藏的三列开始，即row\_id（6字节），trx\_id（6字节），roll\_pointer（7字节），t1（1字节），t2(2字节)，t3(10字节)，t4(4字节)。那么第一列长度06，第二列0C(6+6),第三列13(6+6+7),第四列14(6+6+7+1)，第五列16（6+6+7+1+2），第六列20（6+6+7+1+2+10），第七列24（6+6+7+1+2+10+4）。排序为06,，0C，13，14，16，20，24，再逆向过来，改行记录的字段长度偏移量列表为24,20,16,14,13,0C,06。

下面再来看个NULL值的行记录

需要注意的是，不同于Compact，对于固定长度的null值，是需要占空间的，而对于变长是不占的。从隐藏的三列开始，即row\_id（6字节），trx\_id（6字节），roll\_pointer（7字节），t1（1字节），t2(0字节)，t3(10字节)，t4(4字节)。所以第一列长度06，第二列0C(6+6),第三列13(6+6+7),第四列14(6+6+7+1)，第五列14（6+6+7+1+0），第六列1E（6+6+7+1+0+10），第七列22（6+6+7+1+0+10+4）。

排序为06，0C，13，14，14，1E，22。对于字段值为null列的长度，其最高位设置为1，比如第五列为14，二进制为00010100，最高位设置为1后，二进制为10010100，转化为16进制为94，同样对于第六行1E，转化后为9E。所以最终的长度为06,，0C，13，14，94，9E，22，再逆向后得到22,9E，94,14,13,0C,06。

*  **记录头信息**

不同于Compact，Redundant的记录头为6个字节

<table style="width:554px;"> 
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td style="width:84px;">大小(bit)</td> 
   <td style="width:371px;">描述</td> 
  </tr> 
  <tr> 
   <td>预留位</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">没有使用</td> 
  </tr> 
  <tr> 
   <td>预留位</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">没有使用</td> 
  </tr> 
  <tr> 
   <td>delete_flag</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">删除标识</td> 
  </tr> 
  <tr> 
   <td>min_rec_flag</td> 
   <td style="width:84px;">1</td> 
   <td style="width:371px;">B+树每层非叶子节点中最小的目录项记录都会添加该标记</td> 
  </tr> 
  <tr> 
   <td>n_owned</td> 
   <td style="width:84px;">4</td> 
   <td style="width:371px;">该记录拥有的记录数，一个页有多条记录，这些记录会再进行分组(Slot)，每个分组有个老大，老大会存储这个分组中有几条记录。</td> 
  </tr> 
  <tr> 
   <td>heap_no</td> 
   <td>13</td> 
   <td>索引堆中该记录的索引号</td> 
  </tr> 
  <tr> 
   <td>n_fields</td> 
   <td>10</td> 
   <td>记录中列的数量</td> 
  </tr> 
  <tr> 
   <td>1byte_offs_flag</td> 
   <td>1</td> 
   <td>字段长度偏移量是1个字节还是2字节</td> 
  </tr> 
  <tr> 
   <td>next_record</td> 
   <td>16</td> 
   <td>页中下一条记录的相对位置</td> 
  </tr> 
  <tr> 
   <td>total</td> 
   <td style="width:84px;">48</td> 
   <td style="width:371px;"></td> 
  </tr> 
 </tbody> 
</table>

*  ** 额外信息**

与Compact一样，这个信息是MySQL自动生成的信息，主要包括row\_id 行号 ，trx\_id事务id ，roll\_pointer 回滚指针。

**3、Compressed和Dynamic**

这两种格式与Compact类似，只是在行溢出上有些区别。所谓行溢出，就是将一条记录中的某些数据存储在真正的数据页面之外，一般认为BLOB，LOB这类的大对象列类型的存储会把数据放在数据页之外大对象页，当然varchar类型长度足够长也会行溢出。

我们知道一个页是16K，也就是16384字节，根据B+树的特性，一个页中至少要保存2条记录，理论上列值字节长度为8192（实际有其他开销，小于这个值），就会行溢出。对于Compact格式类型，前768字节的前缀数据存放在数据页，其他的将溢出，并记录保存偏移量。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 9][]

对于Compressed和Dynamic格式，数据页中只存放20个字节指针，数据都放在off page中

![20210610145202836.png][]

Compressed行记录格式，对于存储其中的行数据会以zlib算法进行压缩，因此对于BLOB，TEXT，VARCHAR这类长度类型的数据能够非常有效的存储。

### (4)数据页结构 ###

InnoDB数据页由以下7个部分组成：

<table> 
 <tbody> 
  <tr> 
   <td style="width:151px;">名称</td> 
   <td style="width:101px;">字节数</td> 
   <td style="width:581px;">描述</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">File header</td> 
   <td style="width:101px;">38</td> 
   <td style="width:581px;">记录本页的头信息，主要包括checksum值，本页在表空间的偏移量，上一页和下一页的的指针(双向链表)，最后修改的LSN值，页类型，表空间id等。</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">Page Header</td> 
   <td style="width:101px;">56</td> 
   <td style="width:581px;">记录页的状态信息，包括slot(槽数)，第一条记录的位置，记录数，可重用空间的首指针，已删除记录的字节数，插入记录的位置，最后插入的方向，一个方向连续插入的数量，索引ID，该页的索引位置等。</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">Infimum和Supermun</td> 
   <td style="width:101px;">8</td> 
   <td style="width:581px;">页中的虚行，Infimum记录是比该页中任何主键值都要小的值，Supermum指比任何可能大的值还要大的值。</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">User Record</td> 
   <td style="width:101px;"></td> 
   <td style="width:581px;">实际的行记录，即前面所描述的记录格式</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">Free Space</td> 
   <td style="width:101px;"></td> 
   <td style="width:581px;">空闲空间，一条记录被删除后，该空间被加入到空闲列表中。</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">Page Directory</td> 
   <td style="width:101px;"></td> 
   <td style="width:581px;">InnoDB在页上采用稀疏目录，将页上记录分成多个槽(slots)，每个记录都属于某个槽，page directory就是槽的首记录的位置集合，当查找记录时，首先将定位到页，加在到内存，然后根据page directory查询所在槽，再根据行记录的next_record逐个进行查找。</td> 
  </tr> 
  <tr> 
   <td style="width:151px;">File Trailer</td> 
   <td style="width:101px;">8</td> 
   <td style="width:581px;">checksum值，确保数据完整性</td> 
  </tr> 
 </tbody> 
</table>

## 2、重做日志文件 ##

前面我们介绍到重做日志缓冲，后台主线程每秒会将数据刷新到磁盘文件，即重做日志文件(redo log file)，当实例掉电或者宕机后，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，保证数据的完整性。重做日志是独立的文件，位于存储根目录下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 10][]

默认有两个文件，当一个文件写满后，切换到另个一个文件，循环写入。所以文件不能太大，也不能太小，太大会导致恢复的时间过长，太小会导致切换频繁。1.2.X版本后，文件总大小限制扩大到512G。当然，重做日志文件可以设置多个分组，以及指定每个分组中文件个数。

重做日志的的结构如下：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:177px;">redo_log_type(1个字节)</td> 
   <td style="width:217.9px;">space (压缩后可能&lt;4字节)</td> 
   <td style="width:168px;">page_no</td> 
   <td style="width:12px;">redo_log_body</td> 
  </tr> 
 </tbody> 
</table>

*  redo\_log\_type,占用1字节，表示重做日志类型。各种不同操作有不同的重做日志格式，但有基本的格式。
 *  space,表空间的ID，采用压缩的方式，占用空间可能小于4字节。
 *  page\_no,页的偏移量，同样采用压缩方式。
 *  redo\_log\_body,每个重做日志的数据部分，恢复时需要调用相应的函数解析。

重做日志文件经常拿来与binlog日志文件做比较，两者有一定的共性，就是都是对事物日志记录。但也是有明显的区别，主要包括：

*     类别

二进制日志：记录MySQL数据库相关的日志记录，包括InnoDB，MyISAM等其它存储引擎的日志。

重做日志：只记录InnoDB存储引擎本身的事务日志。

*       内容

二进制日志：记录事务的具体操作内容，是逻辑日志。

重做日志：记录每个页的更改的物理情况。

*       时间

二进制日志：只在事务提交前进行写入，只写磁盘一次，不论这时事务量多大。

重做日志：在事务进行中，就不断有重做日志条目写入重做日志文件。

## 3、undo日志 ##

undo log是事务原子性的保证，当事务执行失败，或者用户执行rollback指令，就可以利用undo 信息将数据回滚到修改前的样子。undo还有另外一个作用就是MVCC（查看[深入理解MySQL原理--事务与分库分表][MySQL_--]），就是通过undo来完成的，当用户读取一行记录时，若该记录已经备其他事务占用，当前事务就可以通过undo读取之前的行版本信息，以此实现非锁定读取。

与redo log文件不同，undo日志没有独立的文件，是存放在表空间一个特殊的段(回滚段，参考表空间章节的示意图)中，InnoDB1.1版本支持128个回滚段，每个段记录1024个unlog segment，即支持同时在线事务数为128\*1024个，InnoDB1.2版本后，增加了对如下参数的设置：

*  innodb\_undo\_directory,设置回滚段文件所在的路径，这就意味着回滚段可以放在共享表空间意外的位置。
 *  innodb\_undo\_logs,设置回滚段个数，默认为128。
 *  innodb\_undo\_tablespaces,设置回滚段文件的数量，回滚段可以较平均的分布到各个文件中。

对于insert，delete，update来说，undo的记录内容和回收有所不同

**insert**

主要记录事务ID，table\_id以及所有主键的列和值等，事务提交后，该记录会立即删除(insert无需提供MVCC机制)；如果事务需要回滚，则根据主键的列和值找到记录，进行删除。

**delete/update**

delete和update的undo记录信息，会比insert多一个update vector信息，表示delete/update操作导致发生改变的列。由于delete/update都需要提供MVCC机制，所以事务提交后，并不会立即删除，而是由purge线程，根据该条记录是否有事务关联判断，最终完成回收。

delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除。

update操作分成两种情况：

如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。

如果是主键列，update分两部执行，先删除该行，再插入一行目标行。

# 五、总结 #

按照惯例，我们通过对开篇的问题回答，作为本章的总结。

**Q:InnoDB内存区域区域包含哪些内容，缓存池包含哪些数据信息？**

**A:**InnoDB内存区域包含缓冲池，重做日志缓冲，额外内存池。缓冲池占内存的大部分区域，主要包含数据页，索引页，插入缓冲，自适应哈希，锁信息，数据字典，其中数据页，索引页又占了缓存池的大部分区域。

**Q:缓存池中是如何进行内存淘汰的，脏页刷盘机制是什么？**

**A:**缓存池的数据淘汰主要针对数据页和索引页，采用是变种的LRU算法。通过checkpoint机制实现脏页的刷盘。

**Q:重做日志内存和文件是如何确保数据的可靠性，与二进制日志文件有什么区别？**

**A:**重做日志内存每秒刷新数据到重做日志文件，记录页的物理变化，当实例宕机，可以通过重做日志实现数据的恢复。

重做日志文件与二进制文件在类别，内容，刷盘时间上存在不同。

**Q:后台的线程包含哪些种类，其作用分别是什么？**

**A:**后台线程包含四种，分别为Master thread(主线程)，IO thread(IO线程)，Purge Thread(回收线程)，Page Cleaner Thread(脏页刷新线程)。

Master thread，通过每秒，以及每10s这两这个执行周期，将内存中的脏页(数据页，索引页)，插入缓冲，重做日志缓冲刷新到磁盘，以及执行undo页的删除。

IO thread则负责处理IO请求的回调。

Purge Thread负责Undo页的回收和分配。

Page Cleaner Thread负责脏页的刷新。

**Q:表空间是如何组织的，一条行信息是如何记录在磁盘文件中的？**

**A:**表空间按类型可分为共享表空间和独立表空间，表空间从逻辑结构上，按层次划分，从上而下分别为段，区，页，行。InnoDB按照行进行数据存储，行记录格式有Redundant，Compact，Compressed和Dynamic。

**Q:undo日志在文件中如何组织的，每种操作记录的内容是什么，又是如何确保事务的原子性，以及MVCC机制？**

**A:**Undo日志存储在表空间中，以独立的回滚段保存，记录变化的逻辑数据。当事务执行错误或者rollbac后，就可以利用undo 信息将数据回滚到修改前的样子。对于delete/update操作，事务提交后，不会立即删除undo日志，通过undo日志实现MVCC机制。

附：

[深入理解MySQL原理之一--如何提升查询SQL的性能][MySQL_--_SQL]

[深入理解MySQL原理之二--如何建立高效索引][MySQL_-- 1]

[深入理解MySQL原理之三--如何实现事务与分库分表][MySQL_-- 2]

[深入理解MySQL原理之四--如何实现高可用][MySQL_-- 3]

[深入理解MySQL原理之五--如何高效利用InnoDB存储引擎][MySQL_--_InnoDB]

[深入理解MySQL原理之六--核心算法有哪些][MySQL_-- 4]

[深入理解MySQL原理之七--云原生时代将何去何从][MySQL_-- 5]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70]: /images/20221014/7241d88efd124909b8d8fd386beeb724.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 1]: /images/20221014/9494b3aba8bf49588a1135c070ef5dde.png
[20210605073122558.png]: /images/20221014/65b22a5ea75448eb9b76e3a1768cf3c3.png
[20210605073520678.png]: /images/20221014/7eed208c631d43b09ad4adf5707fe922.png
[20210605095558210.png]: /images/20221014/b257a6450c9c42df9167eee8bb6905de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 2]: /images/20221014/40d0740f6af84795ba794d9c087f2e0f.png
[20210605102023157.png]: /images/20221014/7eaeb6e4b7bb4c00a572b3eca878204d.png
[20210605103641206.png]: /images/20221014/451af49c78764132a2bb7f25a985eca4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 3]: /images/20221014/ac48578ca6234694be0a0b68337b46c2.png
[20210605114448103.png]: /images/20221014/3149c87c70dc4416994162a854a77d58.png
[20210605154056439.png]: /images/20221014/9ed8cdd2aa644680b8d934c4f0b4a840.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 4]: /images/20221014/996d452d04a545d79c7753baddbba071.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 5]: /images/20221014/24864ee0f02d474fb47e3f89c918913b.png
[20210605170820791.png]: /images/20221014/2ff76d30f4354f21bba1509536542f0d.png
[20210602200954281.png]: /images/20221014/5dbd25f3d8fd46f0a867c1561a02ce5d.png
[2021060822293098.png]: /images/20221014/51230031d44f4cb68bf64e7a4ead186f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 6]: /images/20221014/910e258c3b1f4d3885c5ccd26238962a.png
[20210608221654850.png]: /images/20221014/26a597fc7c6a424594666ae4bebddde2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 7]: /images/20221014/fe81e195cf014b718425dca92369e2e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 8]: /images/20221014/7b0a7628bfeb4c90ba047989cb8bd7bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 9]: /images/20221014/30766971470c4e8294b7c1b03e84c3d8.png
[20210610145202836.png]: /images/20221014/3b6487f0dd334aeea15d1d43fa842ff5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RjeTgz_size_16_color_FFFFFF_t_70 10]: /images/20221014/4f45963e67d84125b4ed38c605aa0a93.png
[MySQL_--]: https://mp.csdn.net/editor/html/114848104
[MySQL_--_SQL]: https://blog.csdn.net/tcy83/article/details/108546004
[MySQL_-- 1]: https://blog.csdn.net/tcy83/article/details/114048168
[MySQL_-- 2]: https://blog.csdn.net/tcy83/article/details/114848104
[MySQL_-- 3]: https://blog.csdn.net/tcy83/article/details/115453583
[MySQL_--_InnoDB]: https://blog.csdn.net/tcy83/article/details/117407724
[MySQL_-- 4]: https://blog.csdn.net/tcy83/article/details/117918385
[MySQL_-- 5]: https://blog.csdn.net/tcy83/article/details/119088747