当前位置：首页 > news >正文

【MySQL】剖析InnoDB存储引擎

news 2025/7/15 6:45:09

🔥个人主页：中草药

🔥专栏：【MySQL】探秘：数据库世界的瑞士军刀

通过抛出问题的方式去剖析MySQL的InnoDB存储引擎

一、InnoDB存储引擎基本设计

InnoDB存储引擎的优点

（MySQL为什么默认使用InnoDB存储引擎，InnoDB较MyISAM的优势）

答：InnoDB在设计时考虑到了处理巨大数据量时的性能，InnoDB支持事务(transaction)、回滚(rollback)并且具有崩溃修复的能力(crash recovery capabilities)，通过多版本并发控制(multi-versioned concurrency control)减少锁定，同时还支持外键约束(FOREIGN KEY constraints)，通过缓冲池在主内存中缓存数据从而提高查询性能，也可以每个表使用各自的独立表空间存储数据并且文件大小只受限于操作系统，由于InnoDB存储引擎存储数据量大，性能高，可以有效的保证数据安全等优点，在MySQL5.5版本之后成为默认的存储引擎。

InnoDB的存储架构

InnoDB的存储架构，主要包括内存架构和磁盘架构MySQL :: MySQL 8.0 Reference Manual :: 17.4 InnoDB Architecture

内存结构中包括：

缓冲池 (Buffer Pool)--内存的主要工作区域，优化查询性能
变更缓冲区 (Change Buffer)-优化修改操作的性能
日志缓冲区 (Log Buffer)
自适应哈希 (Adaptive Hash Index)-进一步提升查询的性能

磁盘结构中包括：

系统表空间 (System Tablespace)
独立表空间 (File - Per - Table Tablespaces)
通用表空间 (General Tablespaces)
临时表空间 (Temporary Tablespaces)
撤销表空间 (Undo Tablespaces)

以上的磁盘空间负责保存真实的数据，下面的磁盘空间负责保证数据的安全

重做日志 (Redo Log)
双写缓冲区 (Doublewrite Buffer)

为什么设计成内存结构和磁盘结构两个部分

答：数据库的作用就是保存数据，用户的真实数据最终都会保存在磁盘上，在查询数据的过程中，如果每次都从磁盘上读取会严重影响效率，为了提高数据的访问效率，InnoDB会把查询到的数据缓存到内存中，当再次查询时，如果目标数据已经存在于内存中，就可以从内存中直接读取，从而大幅提升效率。

也就是说磁盘结构中的文件是用来保存数据实现数据持久化的，内存结构是用来缓存数据提升效率的。

使用InnoDB存储引擎创建的表对应的数据文件在哪里?

答：当使用InnoDB存储引擎创建一个表时，默认会在数据目录对应的数据库子目录中生成相应的表空间文件，以.ibd 为文件的后缀，用来存储数据和索引，如果每个表都对应一个表空间文件，称为独立表空间，在MySQL5.7及以后的版本中默认为每个表生成独立表空间，可以通过系统变量innodb_file_per_table[=ON|OFF]进行控制，如果关闭这个选项，则所有表的数据都在系统表空间中存储。

二、MySQL存储结构

mysql存储结构式为了有效维护数据而定义的一系列数据结构

什么是表空间文件

答：表空间文件是用来存储表中数据的文件，表空间文件的大小由存储的业务数据多少决定，不同的表空间文件存储数据的种类也有所不同，在MySQL中表空间分为五类，包括:系统表空间、独立表空间、通用表空间、临时表空间和撤销表空间，这些在上面的InnoDB架构图中都有体现。

表空间与表空间文件的关系是什么?

答：表空间可以理解为MySQL为了管理数据而设计的一种数据结构，主要描述的对结构的定义，表空间文件是对定义的具体实现，以文件的形式存在于磁盘上

用户数据在表空间中是怎么存储的

（mysql的存储结构是怎么定义的）

答：首先明确一点，用户的数据以数据行的方式存储在对应的表空间文件中，那么表空间中很多个数据行就需要进行管理，以便后续进行高效的查询;
为了方便管理，表空间由段(segment)、区组(group)、区(extent)、页(page)、数据行组成,其中页是 InnoDB 磁盘管理的最小单位;

为什么要使用页(page)这个数据管理单元

首先要明确一点，MySQL中的页是应用层的一个概念，是MySQL根据自身的应用场景，定义的一种数据结构。

通常操作系统中的文件系统在管理磁盘文件时以4KB大小为一个管理单元，称为"数据块"，但是在数据库的应用场景里，查询时数据量都比较大，如果也使用4KB做数据存储的最小的单元，就显的有点小了，同时会造成频繁的磁盘I/0，导致降低效率;

所以MySQL根据自身情况定义了大小为16KB的页，做为磁盘管理的最小单位;

每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘I/0，提高性能

答：MySQL根据自身的应用场景使用页作为数据管理单元，最主要的目的是减少磁盘的I/O,提高性能

什么是局部性原理

答：局部性原理是指程序在执行时呈现出局部性规律，在一段时间内，整个程序的执行仅限于程序中的某一部分。相应地，执行所访问的存储空间也局限于某个内存区域，局部性通常有两种形式:时间司部性和空间局部性。
时间局部性(Temporal Locality):如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。
空间局部性(SpatialLocality):将来要用到的信息大概率与正在使用的信息在空间地址上是临近的。

数据页有那些基本特性是必须要掌握的

答：

1）页的16KB的大小是MySQL的一个默认设置，可以适用于大多数场景，当然也可以根据自己的实际业务场景进行修改页的大小，通过系统变量 innodb_page_size 进行调整与查看，在调整页大小的时候需要保证设置的值是操作系统"数据块"4KB的整数倍，从而保证通过操作系统和磁盘交互时"数据块"的完整性，不被分割或浪费，所以规定了 innodb_page_size 可以设置的值，分别4096、8192、16384、32768、65536，对应4KB、8KB是16KB、32KB、64KB ;

mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 | # 16KB
+------------------+-------+
1 row in set, 1 warning (0.04 sec)

2）每一页即使没有数据也会使用16kb的存储空间，同时与索引的B+树中的节点对应

3）在不同的使用场景中，页的结构也有所不同，在MySQL中有多种不同类型的页，但不论哪种类型的页都会包含页头(File Header)和页尾(File Trailer)，在这页头和页尾之间的页主体信息根据不同的类型有不同的结构，最常用的就是用来存储数据和索引的"索引页"，也叫做"数据页"，页的主体信息使用数据"行"进行填充。（后文做详细解释）

不同的页在磁盘中是不是连续的？

答：答案是不一定，在不做任何控制的情况下，不同页在磁盘中申请的地址大概率是不连续的。我们可以很快的分析出来连续的地址对查询效率的影响，如果页在磁盘中可以被连续读取，那么查询效率就高，否则果询效率就低。

如果页不连续，对访问效率是否有影响

（为什么不连续的地址会降低查询的效率）

答：存储介质是机械硬盘时，访问不连续的地址会带来磁盘寻址的开销，也就是磁头在不同盘面、磁和扇区的机械转动，这个过程称为磁盘随机访问，非常影响效率，磁盘结构如下图所示:

当查询的数据大于一页时不加任何控制会产生磁盘随机访问，这个是影响查询效率的主要因素

InnoDB如何保证页在磁盘中的连续性

为了解决磁盘随机访问非常低效的问题，需要尽可能在磁道上读取连续的数据，减少磁头的移动，从而提升效率，MySQL使用 Extent(区)这个结构来管理页，规定每个区固定大小为 1MB ，可以存放 64 个页，这时如果跨页读数据时，大概率都在附近的地址，可以大幅减少读写磁头的移动;

同时，如果频繁的读取某个区中的页，可以把整个区都读取出来放入内存中，减少后续查询对磁盘的访问次数，进一步提升效率，如图所示

查询的数据超过一页的大小，怎么提升效率--区

答：InnoDB使用区这个数据结构来管理页，并且每个区的固定大小为1MB，它用于保证页与页之间的连续性，可以包含64个连续页，此时超过一页的大小会出现以下情况

页在区内是相邻的:磁盘顺序I/0，可以大幅提升效率
页在区内但不是相邻的:可以大幅减少碰头移动，可以提升效率
页在不同的区:还是要发生随机I/0，不能提升效率

新创建的表没有数据，或者数据很少，1MB的空间是用不完的，是否存在空间浪费的问题

最初创建表的时候，由于不知道当前表的数据量级，为了节省空间最初只创建7个初始页（MySQL5.7中创建6个初始页），而不是一个完整的区

114688 / 16384 = 7 个数据页

这些零散页会放在表空间中一个叫碎片区的区域，随着数据量的增加，会申请新的页来存储数据当碎片区达到32个页的时候，后续每次都会申请一个完整的区来存储更多的数据;

答：不存在，MySQL使用了零散页和碎片区来避免空间浪费的问题

如果访问的数据跨区了怎么办?区组

（InnoDB如何高效的管理区）

当表中的数据越来越多，为了有效的管理区，定义了区组的结构，每个区组固定管理256个区即256MB ，通过区组可以在物理结构层面非常高效的管理和定位到每个区

碎片区作为表空间中第一个区组中的第一个区，前四页比较特殊

File Space Header: 表空间和区组中条目信息
Insert Buffer Bitmap:Change Buffer相关信息
File Segment inode: 段信息
B-tree Node:索引根信息
其他为空闲页用来存储真实的数据

其他区组的首个区的结构都一样，前两页分别是

Extent Descriptor(XDES):区组条目信息
Insert Buffer Bitmap:Change Buffer相关信息

答：使用区组结构去有效的管理区，每个区组管理256个区，即256 MB，区组条目信息会记录每个区的偏移量，区管理数据的范围并用双向链表连接

以上这些数据结构还有优化的空间吗-段

段是个逻辑概念，"段"并不对应表空间中的连续的物理区域，可以看做是“区"和“页"的一个附加标注信息,段的主要作用是区分不同功能的区和在碎片区中的页，主要分为“叶子节点段“和“非叶子节点段"等，这两个段和我们常说的B+树索引中的叶子、非叶子节点对应，可以简单的理解为"非叶子节点段"存储和管理索引树，“叶子节点段"存储和管理实际数据，从逻辑上讲，最终由"叶子节点段"和“非叶子节点段“等段构成了表空间.ibd 文件，如下图所示:

答：存在，InnoDB使用"段"这个逻辑结构区分不同功能的区和在碎片区中的页，并按功能分为"叶子节点段"和"非叶子节点段"，做为B+树索引中的叶子、非叶子节点，从而进一步提升查询效率。

查询数据时MySQL会一次把表空间中的数据全部加载到内存吗?

答：当然不是，使用InnoDB存储引擎创建表，在查询数据时会根据表空间内部定义的数据结构(一般为索引)，定位到目标数据行所在的页，只把符合查询要求的页加载到内存。

每查询一条数据都要进行一次磁盘I/0吗?

答：不一定，每次查询都会把磁盘中数据行对应的数据页加载到内存中，如果当前查询的数据行已经在内存中，则直接从内存中返回结果，从而提高查询效率。

三、页结构

InnoDB在不同的使用场景定义多种不同类型的页，常用的有数据页、Undo Log页Change Buffer页、Extent Descriptor(XDES)页、InnoDB段信息页等，每种页的数据结构都不相同，其中最需要我们关注的就是数据页，由于InnoDB中有个概念叫"索引即数据”，所以也叫做索引页。

无论哪种类型的页都具有页头(File Header)和页尾(File Trailer)两个信息。

页头页尾包含了哪些信息

页头 - File Header

页号: FIL_PAGE_OFFSET 占用 4Byte，是页的唯一标识，通过这个长度可以计算出每个 InnoDB 表中最多可以拥有 2^(48)-1 约 42 亿个页，表空间第一个页编号从 0 开始，之后的页号分别是 1，2，3… 依此类推，具体页的偏移量计算公式为：页号 * 每页大小；那么按照每个页默认 16KB 大小计算，一个表空间最大容量为 2^(48) * 16KB = 64TB，这也是 InnoDB 表空间最大容量是 64T 的原因；
上一页页号: FIL_PAGE_PREV(页与页之间用双向链表连接)
下一页页号: FIL_PAGE_NEXT 多个页通过这两个信息组成双向链表，即使不同的页地址不连续，也可以通过链表连接
表空间 ID: FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID，当前页属于哪个表空间
页类型: FIL_PAGE_TYPE，数据页对应的页类型是 FIL_PAGE_INDEX = 0x45BF
最近一次修改的 LSN: FIL_PAGE_LSN，占用 8Byte
已被刷到磁盘的 LSN: FIL_PAGE_FILE_FLUSH_LSN，占用 8Byte
校验和: FIL_PAGE_SPACE_OR_CHKSUM，用于页的完整性校验

页尾 - File Trailer

最近一次修改的 LSN
校验和：对应页头中的校验和

如果在数据传输的过程中数据丢失或异常中断，导致一个数据页不完整就可以通过页头和页尾的校验和进行验证，验证算法默认使用 CRC32

什么是LSN

答：LSN:是"Log Sequence(序列) Number"的缩写，表示日志序号。用一个任意的、不断增加的值表示日志中记录的操作对应的时间点，用8字节的无符号长整形表示，后文会详细介绍如何生成LSN的值

除页头页尾，数据页中还有哪些信息

答：页头和页尾中的各个字段描述了当前页的类型以及在文件系统中的位置，也就是说通过页头可以找到对应的页。数据页的主要功能是保存数据，在一个数据页中，除了页头与页尾占用的46个字节之外的空间都用来存储真正的数据，也就是数据行，数据行会与表里的数据行一一对应，基于这一特性MySQL也被称为"行式数据库"，也可以把除了页头页尾的区域称为页主体。

页主体包含哪些信息

答：页主体中的信息都是和数据相关的，其中包括刚才提到了数据行，还有为了提高查询效率的页目录Page Directory 和为了方便操作和管理数据页的数据页头 Page Header。

数据行有哪些信息

数据行主要存储真实数据，为了方便数据的管理与描述，InnoDB在每个数据行中还添加了一些额外(管理)信息，于是每一个 DYNAMIC 数据行都可以划分为两部分，一部分存储额外信息，一部分存储真实数据，额外信息部分包含变长字段的长度列表(记录每个变长字段所占的真实长度)和NULL值列表(表示所有可以为空的列，真实值是否为空)两个大小不确定的区域，以及固定占5字节及40BIT的头信息区域，头信息中存储了行的基本信息，包括行在页内的位置heap_no、行类型 record_type、下一行的地址偏移量 next_record 等6项信息，如下图所示:

答：数据行可以划分为两部分，一部分存储额外信息，一部分存储真实数据，额外信息部分包含变长字段长度列表和NULL值列表两个大小不确定的区域，以及固定占5字节的头信息区域

数据行是如何组织到一起的

答：数据行通过头信息中包含的下一行的地址偏移量，即 next_record 将页内所有数据行组成了一个单向链表，这里要注意的是，地址偏移量指向的是下一行中真实数据的起始地址，这样做的好处是，向右是真实数据，向左就是头信息，而无需额外的长度计算，如图所示:

如何标识新页中的第一行和最后一行

每当创建一个新页，都会自动分配两个行，一个是行类型为2的最小行Infimun，heap_no 位置固定为0号，和一个是行类型为3的最大行 Supremun， heap_no 位置固定为1号，这两个行并不存储任何真实信息，而是做为数据行链表的头和尾，虽然不存储真实数据，但它们的数据结构和真实数据行完全一致，只不过数据区域存储的是代表它们身份的固定字符串Infimun 和 Supremun ，新页中没有数据时，最小行 Infimun 的 next_record 直接连接最大行 Supremun，最大行不连接任何行，它的next_record为0;

当向一个新页插入数据时是如何执行的?

当向一个新页插入数据时，heap_no 会从 2号开始递增，表示当前记录在页面堆中的相对位置;如果是真实数据则 record_type为0，如果是索引目录(B+树非叶节点)数据则record_type 为1;再将 Infimun 连接第一个数据行，最后一行真实数据行连接Supremun ，这样数据行就构建成了一个单向链表，更多的行数据插入后，会按照主键从小到大的顺序进行链接;为了使页的结构更加清晰，通常将页中有数据行的区域称为用户数据区UserRecords，把未被数据行占用的区域称为空闲区Free Space ，如下图所示:

页内查询是如何提高效率的-页目录

为了提高查询效率，InnoDB 采用二分查找来解决查询效率问题。

具体实现方式是，在每一个页中加入一个叫做页目录 Page Directory 的结构，将页内包括头行、尾行在内的所有行进行分组，约定头行单独为一组，其他每个组最多 8 条数据，同时把每个组最后一行在页中的地址，按主键从小到大的顺序记录在页目录中在，页目录中的每一个位置称为一个槽，每个槽都对应了一个分组，这样在插入数据行完成链接后，一旦最后一个分组中的数据行超过分组的上限 8 个时，就会分裂出一个新的分组，为了快速判断每个分组是否达到了 8 个的上限，在每个分组最后一行中用 n_owned 记录了这个分组内的行数，与此同时在页目录中创建一个新的槽，后续插入的行都遵守这个规则；

后续在查询某行时，就可以通过二分查找，先找到对应的槽，然后在槽内最多 8 个数据行中进行遍历即可，从而大幅提高了查询效率；

例如要查找主键为 6 的行，先比对槽中记录的主键值，定位到最后一个槽 2，再从最后一个槽中的第一条记录遍历，第二条记录就是我们要查询的目标行。

答：为了提高查询效率，在每一个页中加入一个叫做页目录 Page Directory 的结构，采用二分查找来解决查询效率问题。

事务索引这些信息在页中怎么记录的-数据页头

答：在页主体部分维护了数据页头PageHeader这一结构这其中包含了三部分信息，分别是统计信息，位置信息，索引事务信息

数据页的完整结构是什么样的

四、行结构

InnoDB支持的数据行的格式都有那些

答：nnoDB支持四种行格式，分别是:REDUNDANT 冗余格式，COMPACT紧凑格式，DYNAMIC动态格式和 COMPRESSED 压缩格式，默认是 DYNAMIC 格式。

查看当下行格式

指定行格式

# 通过全局变量设置
SET GLOBAL innodb_default_row_format=DYNAMIC;# 在创建表时明确的指定⾏格式
CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

dynamic格式由哪些部分组成

答： DYNAMIC 格式的数据行会被分为两部分，一个部是存储真实数据的区域，一部分是存储额外信息的区域。

数据区是如何存储真实数据的

如上图，真实数据的前三个字段在每个数据行都存在

第一个字段存储真实数据的主键值，存在已下四种情况：

如果表中定义了主键，则直接存储主键的值;
如果是复合主键会根据列定义的顺序依次排列在这里;
如果没有主键，会优先使用第一个不允许为NULL的 UNIQUE 唯一列作为主键;
如果既没有主键也没有唯一键，那么InnoDB会构建一个6字节的字段 DB_ROW_ID 作为行的唯一标识，存储在真实数据的头部

紧接着后两个字段

6字节的事务ID字段DB_TX_ID，记录创建或最后一次修改该记录的事务ID，每个DML操作（增删改）都会包含在一个事务之中

7字节的回滚指针字段 DB_ROLL_PTR，如果在事务中这条记录被修改，指向这条记录的上一
个版本

接下来除了逐渐和值为null的列（节省空间，后文详解），其他列的真实数据，按照顺序从左往右依次排序

额外信息区域中的头信息包含了那些信息

下一行地址偏移量: next_record 占 16bit，通过这个信息将所有的行链接成一个单向链表
行类型: record_type 占 3bit，包括四种类型:
- 0: 普通数据行
- 1: 索引目录行
- 2: 页内最小行 infimun
- 3: 页内最大行 supremun
行在整个页中的位置 heap_no：占 13bit;
分组的行数: n_owned 占 4bit，只在该行是分组最后一行才有值，这样就可以快速查询行数，而不用一条条的累加了
B + 树索引树每层最小值标记: min_rec_flag 占 1bit，如果当前行的类型是目录行也就是 record_type=1，同时也是 B + 索引树某层的最小值，则会置为 1，会在索引查询时用到
删除标记: delete_mask 占 1bit，从页中删除数据行时，并不会直接移除，而是修改这个删除标记为 1
预留区：占 2bit

删除一行记录时在InnoDB内部执行了那些操作

答：从页中删除数据行时，并不会直接移除，而是修改delete_mask 这个删除标记为 1，并将next_record 改为0，同时将上一行的 next_record 指向后续的行，从而把该行从链表中新开，如果执行事务提交后，则将这行的 next_record 指向一个被称为垃圾链表的区域，这个链表会被用在事务回滚中

Null列表有啥作用?列表中的值是什么?

（null值列表的实现方式）

头信息区再向右就是 NULL 值列表的可变区域，用来存储数据行中所有列允许为 Null 的值从而节省空间，具体的实现方式是，用 1BIT 的大小来表示行中某一列是否为空，这样空列就不需要记录在真实数据区域中了

为每个没有定义 NOT NULL 约束也就是可以为 NULL 的列在 NULL 值列表中都安排了一个 bit 位，按列序号从小到大的顺序从右至左依序安排，这就是常说的逆序排列，NULL 值列表最小 1 字节即 8bit，如果没有那么多可以为 NULL 的列，则会用 0 补满 8bit，如果为值为 NULL 的列超过 8 个，则新开辟 1 字节的空间，依此类推；

如果某列为空，则 NULL 值列表中对应的 bit 设置为 1，这样只用了一 bit 就存储了 NULL 列，非常节省空间

变长字段列表有什么用？列表中的值是什么?

（为什么要记录变长字段中数据的实际长度）

需要记录的变长字段类型常见的有varchar、varbinary、text、blob，以及当使用了例如utf-8、gbk等变长字符集的char类型，当char类型的字节数可能超过768个字节时，比如使用utf8mb4字符集时定义了char(255)，这个字段的最大字节数是4*255=1020

每个变长字段分配1~2个字节来存放这些字段的真实大小，放置顺序也是按表中字段的顺序从右至左逆序排列;

2个字节最大可以表示65535个字节，按照最大长度字符串，比如 utf8mb4，一个字符占用最多4个字节计算，2个字节最多可以表示65535/4=16383个字符，列数据类型varchar的长度上限16383就是根据这个计算来的;

# 看这个创建表的例⼦
CREATE TABLE test_varchar (`id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(20000) NULL,PRIMARY KEY (`id`)
);# 返回错误
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use 
BLOB or TEXT instead

需要特别说明的是，如果text、blob存储的内容过大，一个页已经不够放了，就会把这个列放入一个叫"溢出页"的独立空间中，在这个数据行对应的真实数据处，只使用20个字节来标记这个溢出页的位置信息

答：行结构的最左侧是变长字段列表，也叫可变字段长度列表，在这个列表中记录了数据行中所有变长字段的实际长度，这样做的目的，是为了在真实数据区域，可以根据列的长度进行列与列之间的分割;

如何记录变长字段的实际长度

不同的字符集在处理字符对应的最大字节长度不同，以如 ascii最大1个字节，utf8mb3 最大3个字节，utf8mb4 最大4个字节，如下所示

| ascii   | US ASCII      | ascii_general_ci     | 1 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci   | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci   | 4 |

当使用varchar(M)指定一个字段的最大字符数时，该字段真实使用的字节数与建表时指定的字符集有关，如果指定的字符集单个字符最大占 w个字节，从理论上讲，该列最多使用的字节数 M *W，如果M*W<= 255 则用一个字节记录这个变长字段的长度就足够了
如果M*W>255 可能分为两种情况，假设当前变长字段实现占用了L个字节:

L<=127 用一个字节表示长度
L>127 用两个字节表示长度

读取长度如何处理粘包问题

（如何确定读一个字节还是读两个字节）

在任何时候都是先读一个字节，然后判断这个字节的高位是否为0，如果是0则表示当前用一个字节表示长度，

如果是1则表示当前用两个字节表示长度为1时再读一个字节，然后合并在一起进行解析得到该字段真实的使用的字节数，而且第二个BIT位表示是否使用溢出页

其他行格式与Dynamic有什么区别

REDUNDANT 冗余格式
已被淘汰，之所以存在是为了与旧版本 MySQL 兼容，不建议使用，这里不再讨论。

COMPRESSED 压缩格式
行结构与 DYNAMIC 完全相同，只是会对数据进行压缩，以减少对空间的占用。

COMPACT 紧凑格式
在结构上与 DYNAMIC 相同，只是对超长字段的处理上有些区别，它不会把所有超长数据都放在溢出页中，而是会在本行中保留前 768 个字节的数据，多出的部分放在溢出页中，溢出页的地址额外用 20 个字节表示，那么在本行的列中就会占用 768+20 个字节。

五、InnoDB内存结构

InnoDB存储引擎的内存架构主要组成部分有哪些，为什么需要内存结构

答：InnoDB存储引擎中内存结构主要分为

Buffer Pool 缓冲池
Change Buffer 变更缓冲区
adaptive_hash_index 自适应哈希索引
Log Buffer日志缓冲区

磁盘结构中的文件是用来保存数据实现数据持久化的，内存结构是用来缓存数据提升效率的

缓冲池的作用

答：缓冲池主要用来缓存被访问的InnoDB表和索引数据页，是主内存中的一片区域，允许直接从内存访问频繁使用的数据从而提高效率。在专用数据库服务器上，通常会将多达80%的物理内存分配缓冲池。

其次缓冲池不仅缓存了磁盘的数据页，也存储了锁信息、Change Buffer信息、Adaptive hashindex、Double write buffer等信息。如上图所示

缓冲池的结构

缓冲池采用与表空间类似的方式对数据进行组织，如下图所示:缓冲池中包含至少一个 Instances 实例，Instances 是真正的缓冲池的实例对象，内存操作都是在 Instances 中进行的;

每个 Instances 中包含至少一个 Chunk块，Chunk 是在服务器运行状态下动态分配和回收内存;每个块中包含和管理若干个从磁盘加载到内存的 Page 数据页

缓冲池中的页与页之间是如何建立连接的

由于数据页中没有一个字段用来表示内存中下一页的地址，为了每个数据页在内存中实现链表连接，InnoDB定义了一个叫"控制块"的数据结构，"控制块"中有三个重要的信息分别是

指向数据页的内存地址
前一个控制块的内存地址
后一下控制块的内存地址

之后再用一个双向链表管理每个控制块，如下图所示:

为了确定控制块链表的超始位置，专门定义了一个头节点，头节点中包含了三个主要的信息，如图中所示:

第一个控制块的内存地址。
最后一个控制块的内存地址
链表中控制块的数量

答：缓冲池中主要缓存的是磁盘中的数据页，由于数据页中没有一个字段用来表示内存中下一页的地址，InnoDB定义了"控制块"的数据结构，控制块中有一个指向数据页内存地址的指针，实现"控制块"与数据页的一一对应，并且把每个控制块连接成一个双向链表，用一个单独的头节点记录链表的第一个和最后一个节点，这样通过遍历控制块链表就可以遍历内存中的数据页。

缓冲池是如何管理数据的

答：缓冲池用于缓存各种数据，最主要的是缓存从磁盘加载的数据页，为了方便数据组织定义了不同的数据结构，包括Instances实例，Chunk块，其中 Buffer Pool中包含至少一个 Instances，Instances 包含至少一个Chunk 块，Chunk 管理若干个从磁盘加载到内存的 Page 数据页，缓冲池能用三个链表管理内存中的数据页，分别是Free List，LRU List 和 Flush List

内存中的数据页与磁盘上的数据页是什么关系?

答：内存中的数据页和磁盘中的数据页是一一对应的关系。只不过内存中管理的是控制块组成的链表，控制块有一个指针指向内存中真实的数据页。

Buffer Pool 的大小可以设置吗？

可以通过系统变量 innodb_buffer_pool_size 进行设置，设置时以字节为单位：默认值为 134217728 字节，即 128MB ；最大值取决于 CPU 架构和操作系统，在 32 位系统上最大值为 4294967295 (2^32 -1)，在 64 位系统上最大值为 18446744073709551615 (2^64 -1)
这里需要注意的是，InnoDB 为 "控制块" 分配额外的内存空间，也就是说 "控制块" 并不会占用 Buffer Pool 的内存空间，所以实际分配的内存总空间比指定的缓冲池大小大 10% 左右。
缓冲池设置的值越大，在多次访问相同表数据时，磁盘 I/O 就会越少，因为数据都已经缓存在内存中，所以效率也就越高，但是服务器启动时初始化时间会比较长。
查看缓冲池大小可以使用下面的 SQL 语句

Chunk的作用是什么

答：Chunk是内存分配和管理的基本单位，为避免在调整大小操作期间复制所有缓冲池中的数据页，调整操作以"块"为基本单位执行;

注意：启动调整大小操作时，在所有活动事务完成后操作才会开始。一旦调整大小操作开始，新的事务和操作必须等到调整大小操作完成才可以访问缓冲池。

Instances 中 Chunk 的数量如何确定？

Chunk 大小可以通过系统变量 innodb_buffer_pool_chunk_size 进行设置，默认为 134217728 字节即 128MB；在设置大小时可以以 1048576 字节即 1MB 为单位增加或减少；块中包含的数据页数取决于 innodb_page_size；

更改 innodb_buffer_pool_chunk_size 的值时可能会影响缓冲池大小，一定要计算大小，注意以下条件：

如果 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 大于当前缓冲池大小，innodb_buffer_pool_chunk_size 将被截断为 innodb_buffer_pool_size /innodb_buffer_pool_instances。
缓冲池大小必须始终等于或倍数于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances。如果修改了 innodb_buffer_pool_chunk_size 的值，导致不符合这个规则，那么在缓冲池初始化时 innodb_buffer_pool_size 会自动四舍五入为等于或者倍数于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的值。

控制块与Page是如何初始化的?

chunk 中管理的是具体的数据页，当缓冲池初始化完成时会把每个数据页所占用的内存空间和对应的控制块分配好，只不是没有从磁盘加载数据时，内存中的数据页是空的。

当缓冲池初始化的过程中，会为 chunk 分配置内存空间，此时"控制块"会从 chunk 的内存空间从左向右进行初始化，数据页所占的内存会从 chunk 的内存空间从右向左进行初始化，当所剩的内存空间不够一组"控制块"+数据页所占的空间时，就会产生碎片空间，如果适好够用则不会出现碎片空间，如下图所示:

内存初始化完成之后，建立控制块与内存中缓冲数据页之间的关系，从左开始第一个控制块指向第一个缓冲数据页的内存地址

此时从硬盘中加载数据页时，就可以把数据缓存在内存中的空白数据页

可以通过缓冲池配置来提升性能吗?

当然可以，通过配置以下关于缓冲池的系统变量来提高性能，其中包括:

配置缓冲池大小
配置多个缓冲池实例
防止缓冲池扫描
配置缓冲池预取(预读)
配置缓冲池刷新策略
保存和恢复缓冲池状态
从核心文件中排除缓冲池页

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
11 rows in set, 1 warning (0.00 sec)

缓冲池的页是如何进行管理的

这里指的是管理内存中页的状态

当缓冲池初始化完成后，缓冲池中的数据页只是被分配了内存空间，并没有真实的数据，当用户进行数据查询时真实的数据从磁盘加载到内存中并分配一个内存中的数据页，这时内存中数据页的态从空间变成了有实际的数据;当用户修改数据时，并不是直接修改磁盘中的数据页，而是修改存中数据页中的数据页，这时内存中数据页的状态从有实际数据变成了被修改。

在缓冲池中采用三个链表维护内存页，这三个链表也对应着内存中页的三种状态，分别是:

Free 未使用的页，也可以称做空闲页;
Clean 已使用但未修改的页，也可以称做干净页:
Dirty 已修改的页，也可以称做脏页。

对应的三个链表分别是 Free List、LRU List和 Flush List:

Free List:只管理Free 页
LRU List:管理 Clean页和Dirty页
Flush List:只管理Dirty页

Free List：管理着空闲的也就是没有被使用的内存页，当执行查询操作时，如果对应的页已经在 buffer pool 中则直接返回数据，如果没有且 Free List 不为空，则从磁盘中查询对应的数据并存到 Free List 的某一页中，然后把这个页从 Free List 中移除并放入 LRU List 中。

LRU List：管理所有从磁盘中读取的数据页，包括未被修改的和已被修改的数据页，并根据 LRU 算法对链表中的页节点进行维护与淘汰。当数据库刚启动时 LRU List 是空的，这时从内存中申请到的页都存放在 Free List 中，当数据从磁盘读取到缓冲池时，首先从 Free List 中查找是否有可用的空闲页，如果有则把该页从 Free List 中删除并加入到 LRU List；如果没有，则根据 LRU 算法淘汰 LRU List 末尾的页，并将该内存空间分配给新数据页；

Flush List：当 LRU List 中的页被修改后会被标识为脏页 (Dirty page)，并把脏页加入到 Flush List 中，在这种情况下，数据库会通过刷盘机制把 Flush List 中的脏页刷回磁盘；Flush List 是一个专门用来管理脏页的列表。脏页既存在于 LRU List 中，也存在于 Flush List 中，LRU List 用来管理缓冲池中页的可用性，Flush List 用来管理要被刷回磁盘的页，二者互不影响。Flush List 中的脏页在执行了刷盘操作后会将空间还给 Free List。

答：每个缓冲池都采用三个链表维护内存页，这三个链表也对应着内存中页的三种状态，分别是

Free 未使用的页，也可以称做空闲页;
Clean 已使用但未修改的页，也可以称做干净页:
Dirty 已修改的页，也可以称做脏页。

内存中如何从众多数据页找到目标页

答：InnoDB采用的是 Page Hash 的方式，也就是每当把磁盘中数据页加载到内存时，用数据页的表空间ld和页号做为Key，当前页在内存中的地址做为Value保存起来，每次查询时就可以通过Key快速定位到目标页，如果内存中没有目标页，则从磁盘中获取。

缓冲池的数据放不下了怎么办？

答：InnoDB根据根据自身的实际场景，使用淘汰策略来淘汰相应的数据页，从而释放出内存空间，以便新的数据页加载到内存中。

缓冲池采用那种淘汰策略？是如何实现的？

缓冲池使用 LRU算法管理链表，当有新页面添加到缓冲池时，最近最少使用的页将被淘汰，并将新页添加到列表的中间，这种中点插入策略将列表视为两个子列表:

链表头部，是存放最近访问的新页(年轻页)子列表;
链表尾部，是存放最近较少访问的旧页子列表。

经常使用的页保存在新子列表中，较少使用的页保存在旧子列表中，随着时间的推移，旧子列表中的页将会逐渐被淘汰。默认情况下，算法的执行过程如下：

缓冲池总容量的前 5/8 用于新子列表，后3/8 用于旧子列表（逻辑的划分）；
列表的中间插入点是新子列表的尾部与旧子列表头部的交界；
当一个页被读入缓冲池时，首先插入到中点做为旧子列表的头节点；
当访问的页在旧子列表中时，把被访问的页移动到新子列表的头部，使其成为 “新” 页；
数据库运行的过程中，缓冲池中被访问页面的位置不断更新，未访问的页面向列表的尾部移动，从而逐渐 “变老”，最终超出缓冲池容量的页从旧子列表的尾部被淘汰。

答：缓冲池淘汰策略采用变形的最近最少使用(LRU)算法

为什么要把页插入到中间而不是直接插入到新子列表的头部

答：因为InnoDB在读取页时，可能会发生"预读"，预读的意思是InnoDB根据当前访问的记录自动推断后面可能会访问哪个页，并把他们提前加载到内存中，从而提高以后查询的效率，预读的页以并不一定会被真正的读取，从中间点插入作为旧子列表的头部，可以使其尽快被淘汰。

怎么查看当前缓冲池的信息

通过使用 SHOW ENGINE InnODB STATUS 访问 InnoDB 标准监视器输出中 BUFFER POOLAND MEMORY 部分查看有关缓冲池的指标。缓冲池指标位于InnoDB标准监视器输出的缓冲池和内存部分:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************Type: InnoDBName:
Status:
=====================================
... # 省略
----------------------
BUFFER POOL AND MEMORY # 缓冲池和内存
----------------------
Total large memory allocated 2198863872 # 为缓冲池分配的总内存，以字节为单位
Dictionary memory allocated 776332      # 为InnoDB数据字典分配的总内存，以字节为单
位
Buffer pool size 131072                 # 分配给缓冲池的总⻚⼤⼩
Free buffers 124908                     # 缓冲池空闲列表的总⻚⼤⼩
Database pages 5720                     # 缓冲池LRU列表的总分⻚⼤⼩
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]... # 省略

变更缓冲区的作用

变更缓冲区用来缓存对二级索引数据的修改，是一个特殊的数据结构，当使用INSERTUPDATE 或 DELETE 语句修改二级索引对应的数据时，如果对应的数据页在缓冲池中则直接更新，如果不在缓冲池中，那么就把修改操作缓存到变更缓冲区，这样就不用立即从磁盘读取对应的数据页了，当之后的读操作将对应的数据页从磁盘加载到缓冲池中时，变更缓冲区中缓存的修改操作再批量合并到缓冲池，从而达到减少磁盘I/0的目的。执行流程如图所示:

答：变更缓冲区用来缓存对二级索引数据的修改，当数据页没有被回载到内存中时先把修改缓存起来等到其他查询操作发生时数据页被加载到内存后，再直接修改内存中的数据页，从而达到减少磁盘I/0的目的。

为什么是二级索引

答：索引分为聚集索引（主键）和二级索引（自定义）。由于聚集索引具有唯一性，我们分析一下聚集索引为什么不能被放入变更缓存，假设表中有一个主键(ID)，现在有两条 INSER 语句，都在插入数据时ID的值相同(id=1)，那么在变更缓冲区中就存在两个修改操作，如果以后要合并到缓冲池中，这时就会出现重复的主键值，所以聚集索引的修改不能被加入到变更缓冲区;

与聚集索引不同，二级索引通常是不唯一的，并且向二级索引中插入数据时由于数据列不同，所以位置相对随机，同样对于删除和更新操作可能会影响不相邻的二级索引页，如果每次都从磁盘读取数据就会发生大量的随机I/0，以变更缓冲区的方式先将修改缓存起来，当真正的读取数据时再把修改合并到缓冲池中可以提升效率。

Merge的触发时机有哪些？

读取对应的数据页时;
当系统空闲或者 Slow Shutdown 时;
Change buffer的内存空间即将耗尽时;
Redo Log 写满时。

此时，主线程会发起merge

变更缓冲区的主要配置项

主要的配置项有 缓冲类型和缓冲区的最大大小

缓冲类型

在修改二级索引数据时变更缓冲区可以减少磁盘 I/O 从而提高效率，但是变更缓冲区占用了缓冲池的一部分空间，从而减少了可用于缓存数据页的内存，如果业务场景读多写少，或者表中的二级索引相对较少，那么可以考虑禁用更改缓冲从而提高缓冲池空间。

可以通过选项文件或 SET GLOBAL 语句对系统变量 innodb_change_buffering 进行设置，来控制变更缓冲区对于插入、删除操作 (索引记录被标记为删除) 和清除操作 (当索引记录被物理删除时) 的开启或禁用：

all：默认值，缓存插入、删除标记操作和清除
none：不缓存任何操作
inserts：只缓存插入操作
deletes：只缓存删除标记操作
changes：缓存插入和删除标记操作
purges：缓存发生在后台的物理删除操作

更改缓冲区的最大大小

通过 innodb_change_buffer_max_size 系统变量可以设置更改缓冲区的最大大小，默认为 25，最大为 50，表示更改缓冲区占缓冲池内存总大小的百分比。
在有大量插入、更新和删除的业务场景中，可以考虑增加 innodb_change_buffer_max_size 的值，在大部分是读多写少，比如用于报表的静态数据场景中考虑减小 innodb_change_buffer_max_size 的值。
需要注意的是，如果更改缓冲区占了缓冲池太多的内存空间，会导致缓冲池中的数据页更快地淘汰。

Adaptive Hash Index自适应哈希索引的作用

答：自适应哈希的只要需要就是提升效率，自适应哈希索引可以使InnoDB存储引擎在不牺牲事务特性和可靠性以及缓冲池空间足够的前提下提升效率，使用起来更像是一个内存数据库，哈希索引根据经常访问的索引页自动构建;
根据InnoDB内部的监控机制，如果监控到某些查询通过建立哈希索引（查找的时间复杂度为O(1)）可以提高性能，则自动对这个页创建哈希索引，这个过程称为自适应，所以叫自适应哈希索引。

为什么要创建自适应哈希索引

InnoDB存储引擎的数据存储于B+树中，B+树通常只有3到5层，但从根节点到叶节点的寻路涉及到多层页面内记录的比较，即使所有路径上的页面都在内存中，也非常消耗CPU的资源

InnoDB对寻路的开销进行了优化，比如寻路结束后将cursor(游标，定位索引的指针)缓存起来方便下次查询复用;尽可能的避免单词寻路开销，Adaptive hashindex(AHI)便是为此而设计，可以理解为B+树的索引。

自适应哈希索引，本质上是通过缩短寻路路径(SearchPath)从而提升MySQL查询性能的一种方式

自适应哈希索引的key-Value如何设置

答：以查询条件为key，B+树页的地址为value。

自适应哈希索引保存在哪里

答：自适应哈希索引会占用缓冲池一部分内存区域（如图所示），在缓冲池初始化后被初始化，为了避免AHI的锁竞争压力，AHI支持分区，可以使用 innodb_adaptive_hash_index_parts 参数配置分区个数，默认为8。

自适应哈希索引有那些配置项

通过设置系统变量 innodb_adaptive_hash_index 开启或关闭自适应哈希索引

在选项文件中设置系统变量 innodb_adaptive_hash_index=[1|0] 实现开启与关闭
通过命令行选项 --skip-innodb-adaptive-hash-index 也可以关闭自适应哈希索引

每个自适应散列索引被绑定到不同的分区中，每个分区有不同的锁保护，分区数量由系统变量 innodb_adaptive_hash_index_parts 控制，默认值为 8，最大值为 512。

日志缓冲区的作用

答：日志缓冲区是服务器启动时向操作系统申请的一片连续的内存区域，存储即将要写入磁盘日志文件的数据。

在对数据库进行DML操作时，InnoDB会记录对应操作的日志，比如为保证数据完整性实现数据库崩溃恢复的Redo Log，这些日志会首先写入Log Buffer中，从而解决同步写磁盘导致的性能问题，然后根据不同落盘策略最终写入磁盘

日志能不通过Log Buffer直接写入磁盘吗

答：如果日志不通过Log Buffer直接写入磁盘，那么每次进行DML操作都会进行一次磁盘1/0，这样会严重影响效率，所以把日志统一写入内存中的LogBuffer，根据刷盘策略统一进行落盘操作，可以实现一次磁盘I/0写入多条日志，从而提升效率

六、InnoDB磁盘文件

InnoDB存储引擎包含哪些磁盘文件?

答：InnoDB的磁盘文件主要是表空间文件和其他文件，表空间包括:系统表空间、独立表空间、通用表空间、临时表空间和撤销表空间;其他文件有重做日志和双写缓冲区

系统表空间的作用

答：系统表空间存储了MySQL中所有系统表的数据，也包括数据字典

系统表空间也是变更缓冲区的存储区域，当数据库服务器关闭时，没有合并到缓冲池的二级索引修改操作被保存到系统表空间;

在以前的版本中，系统表空间也包含双写缓冲区，从MySOL8.0.20开始，双写缓冲区从系统表空间中移到单独的文件中。

系统表空间文件保存在哪里

答：系统表空间可以对应一个或多个数据文件，默认情况下，MySQL在 data 目录中创建一个系统表空间数据文件 ibdata1。系统表空间数据文件的大小和数量由 innodb_data_file_path 启动选项定义。

系统表空间如何进行自定义配置

可以通过 innodb data_file_path 选项定义，如果没有指定 innodb_data_file_path的值，则默认创建一个大小可以自动扩展的数据文件，文件名为ibdata1，初始大小 12MB 。

数据文件命名规范的完整语法包括文件名、文件大小、自动扩展属性和max属性

file_name:file_size[:autoextend[:max:max_file_size]]

通过在 file_size 值后面指定单位K、M或G来设置文件大小，单位为千字节、兆字节或千兆字节。如果以 K为单位指定文件大小，应设置为1024的倍数。否则，千字节值四舍五入到最接近的兆字节(MB)，且文件大小至少为12MB。

注意：

指定多个数据文件可以使用分号 ; 分隔。

# mysqld节点
[mysqld]
# ⽂件1名称为：ibdata1 ⼤⼩为50M
# ⽂件2名称为：ibdata2 ⼤⼩为50M，⾃动扩容
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

autoextend 和 max 属性只能用于最后指定的数据文件，当指定 autoextend 属性时，数据文件的大小会根据需要自动扩容，默认每次增加64MB。可以通过系统变量innodb_autoextend_increment 控制增量的大小;如果要指定数据文件的最大容量，在autoextend 后面指定 max 属性。注意:只有在明确了解限制磁盘使用的情况下才使用 max 属性。下面的配置允许 ibdata1 扩展到 500MB:

# mysqld节点
[mysqld]
#初始大小12M，允许自动扩容，最大可以扩容到500M
innodb_data file_path=ibdata1:12M:autoextend:max:500M

系统表空间文件默认创建在 data 目录下。如果指定其他的目录，使用innodb_data_home_dir 选项。例如，要在名为 myibdata 的目录下创建一个系统表空间数据文件，可以使用如下配置:

# mysqld节点
[mysqld]
# 指定innodb数据⽬录
innodb_data_home_dir = /myibdata/
# 配置系统表空间
innodb_data_file_path=ibdata1:50M:autoextend

指定 innodb_data_home_dir时，必须以斜杠 / 结尾，InnoDB不会自动创建目录,所以在启动服务器之前要确保指定的目录已经存在，最终通过 innodb_data_home_dir 指定的路径与数据文件名组合起来生成完整路径，如果innodb_data_home_dir不指定，默认值为"./"，即MySQL的数据目录
如果 innodb_data_file_path 指定一个绝对路径，则不会读取 innodb_data_home_dir的值，系统表空间文件根据指定的绝对路径创建，启动服务器之前必须确保指定的目录存在。
在添加新的数据文件时，不要指定现有的文件名，InnoDB在启动服务器时会创建并初始化新的数据文件。

File-Per-Table Tablespace独立表空间的优缺点

前文讲的段，区组，区，页，数据行的内容就是独立表空间的。

优点

使用 TRUNCATE 或 DROP 语句删除 File-Per-Table 表空间中的表后，磁盘空间会返回给操作系统，从而提高磁盘利用率，而共享表空间 (比如：System Tablespace) 则不会回收磁盘空间，而且在共享表空间中这些空间只能被 InnoDB 表重新使用；
执行时 TRUNCATE TABLE 时性能更好；
可以在其他目录或单独的存储设备上创建 File-Per-Table 表空间文件的数据文件，从而达到 I/O 优化、空间管理或备份的目的；

# 指定DATA directory⼦句，可以在外部⽬录中创建表
CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

支持与 DYNAMIC 和 COMPRESSED 行格式，而系统表空间不支持；
发生数据损坏、备份、二进制日志不可用或 MySQL 服务器实例无法重新启动时提高成功恢复的机会；
单个表容量大小限制为 64TB ，所以可以存储更多的数据，而共享表空间中的表的总容量为 64TB。

缺点

每个表都可能有未使用的空间，这些空间只能由对应的表使用，如果管理不当，可能会导致空间浪费；
当每个表都有自己的数据文件，操作系统需要维护更多的文件描述符，如果表非常多，可能会影响性能；
可能会出现更多的磁盘碎片，会影响 DROP TABLE 表扫描性能；
innodb_autoextend_increment 系统变量定义了自动扩展共享表空间文件的增量大小，但对于 File-Per-Table 表空间文件不起作用，File-Per-Table 表空间文件始终自动扩展，初始大小根据表定义分配最小的空间，之后以 4MB 为增量进行扩容。

每个表都对应一个独立表空间吗

答：不一定，默认每张表都对应一个表空间数据文件，但也可以通过系统变量 innodb_file_per_table[=[OFFION}]控制开启或禁用是否为每张表生成一个独立表空间文件，如果禁用会在系统表空间中创建表;

可以在选项文件中指定 innodb_file_per_table 设置，也可以在运行时使用 SET GLOBAL

# 选项⽂件中的mysqld节点
[mysqld]
innodb_file_per_table=OFF # 禁⽤，强烈不建议# 在运⾏时通过SET GLOBAL 设置
mysql> SET GLOBAL innodb_file_per_table=ON;

撤销表空间的作用

答：撤销表空间中包含撤销日志(Undo Log)，撤销日志记录了如何撤销事务对聚集索引记录的最新更改(事务的回滚)，通过对事务的回滚，从而保证事务ACID特性中的原子性。

在使用MySQL时并没有手动创建撤销表空间，它是什么时候被创建的?

答：MySQL初始化时会在数据目录下创建两个默认的撤销表空间，数据文件名分别为undo_001 和undo_002

手动创建撤销表空间

对于长时间运行的大事务，撤销日志可能会变得很大，通过创建额外的撤销表空间来防止单个撤销表空间变得太大，从MySOL8.0.14开始，可以在运行时使用 CREATE UNDO TABLESPACE 语法创建额外的撤销表空间;

# 语法
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';# ⽰例
CREATE UNDO TABLESPACE tablespace_test ADD DATAFILE 'undo_log_test1.ibu'; 
Query OK, 0 rows affected (0.09 sec)# 查看所有撤销表空间
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+----------------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+----------------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
| tablespace_test | ./undo_log_test1.ibu | # 新创建的撤销表空间
+-----------------+----------------------+
3 rows in set (0.01 sec)

注意：

通过系统变量 innodb_undo_directory 指定撤销表空间的默认存放路径，如果不指定默认位置为数据目录；
撤销表空间文件名必须以.ibu 为扩展名，定义 undo 表空间文件名时如果需要指定路径，必须使用绝对路径；不允许指定相对路径，建议使用唯一的撤销表空间文件名，避免在以后移动和复制的过程中发生文件名冲突；
如果指定其他路径，那么路径必须在 innodb_directories 中定义，以便 MySQL 扫描并识别；
最多支持 127 个 undo 表空间，包括实例初始化时创建的两个默认表空间；
MySQL 8.0.23 开始初始撤销表空间大小通常为 16MB，并根据服务器负载以 [16MB，256MB] 的增量进行扩容。

删除撤销表空间

从 MVSOL8.0.14开始使用 CREATE UNDO TABLESPACE 语法创建的撤销表空间可以使用DROP UNDO TABALESPAC 语法删除;
撤销表空间在被删除之前必须是空的，要清空撤销表空间，必须首先使用 ALTER UNDOTABLESPACE 语法将撤销表空间标记为不活动，以便该表空间不再用于其他新的事务;

# 语法
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

在将undo表空间标记为非活动后，等待当前undo表空间的事务完成后表空间被截断到初始大小，当undo表空间为空，就可以进行删除操作

# 语法
DROP UNDO TABLESPACE tablespace_name;

示例：

# 查询指定的表空间状态
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACESWHERE NAME LIKE 'tablespace_test';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| tablespace_test | active | # 活动状态
+-----------------+--------+

# 设置为不活动状态
mysql> ALTER UNDO TABLESPACE tablespace_test SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)

# 再次查询状态
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES-> WHERE NAME LIKE 'tablespace_test';
+-----------------+-------+
| NAME            | STATE |
+-----------------+-------+
| tablespace_test | empty | # 表空间为empty，也有可能是inactive 
+-----------------+-------+
1 row in set (0.00 sec)

# 当表空间状态为empty时，要以进⾏删除操作
mysql> DROP UNDO TABLESPACE tablespace_test;
Query OK, 0 rows affected (0.01 sec)# 查询撤销表空间，发现删除成功
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES -> WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME  |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.01 sec)

通过 SHOW STATUS LIKE'Innodb_undo_tablespaces%';语句可以查看撤销表空间的基
本信息

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 2     |  # 撤销表空间的总数
| Innodb_undo_tablespaces_implicit | 2     |  # 隐式(InnoDB)创建撤销表空间数量
| Innodb_undo_tablespaces_explicit | 0     |  # 显式(⽤⼾)创建撤销表空间数量
| Innodb_undo_tablespaces_active   | 2     |  # 活动中的撤销表空间数量
+----------------------------------+-------+
4 rows in set (0.01 sec)

撤销表空间被置为不活动并且已被截断为初始大小，这时不想删除了是否可以重新启用?

答：可以重新激活，语句如下

# 语法
ALTER undo tablespace tablespace name SET ACTIVE;

什么是撤销日志

答：当事务对数据进行修改的时候，每个修改操作都会在磁盘上创建与之对应的UndoLog，当事务需要回滚时，会根据Undo Log逐一进行撤销操作，从而保证事务的原子性。也就是说撤销日志是为事务的回滚操作而诞生的机制，它是一个撤销操作记录的集合。

Undo日志保存在Undo日志段中，Undo日志段位于回滚段中，回滚段位于undo表空间和全局临时表空间中。

撤销日志的写入时机

在事务执行每个DML之前，会根据DML构建对应的撤销日志，并申请一个undo logsegments(撤销日志段)，把日志记录在申请到的撤销段中，再执行真正的DML操作，执行过程如下所示

撤销日志在撤销表空间中的组织形式是怎样的?

撤销日志在撤销表空间中的组织结构

Undo log segments (撤销日志段) 也称为撤销段，一个撤销日志段可以保存多个事务的回滚日志，但在同一时间只能被一个活跃事务使用，对应的空间在事务提交或回滚后才可以被重用。
rollback segments (回滚段) 中包含撤销日志段，通常位于 undo 表空间和全局临时表空间中，使用系统变量 Innodb_rollback_segments 可以定义分配给每个 undo 表空间和全局临时表空间的回滚段的数量，默认值为 128，取值范围是 [1,128]；
一个回滚段支持的事务数取决于回滚段中的 undo slots (槽数) 和每个事务所需的 undo 日志数，一个回滚段中的 undo 槽数可以根据 InnoDB 页面大小进行计算，公式是 (InnoDB Page Size / 16)，比如默认情况下 InnoDB Page Size 大小为 16KB ，那么一个回滚段就可以包含 1024 个 undo slot 用来存储事务的撤销日志。
回滚段中还记录了 History List 的头节点 History List Base Node，以及回滚段的大小。
通过系统变量 innodb_rollback_segments 可以设置 Undo 表空间中的回滚段数量，最大值和默认值都是 128

# 查看Undo表空间中的回滚段数量
mysql> show variables like 'innodb_rollback_segments';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_rollback_segments |  128  |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)# 设置Undo表空间中的回滚段数量
mysql> SET GLOBAL innodb_rollback_segments = 128;
Query OK, 0 rows affected (0.00 sec)

答：撤销表空间中包含 rollback segments(回滚段)，每个回滚段中包含若干undo slots(槽数)，每个槽对应一个 Undo log segments (撤销日志段)，撤销日志段中包含具体的撒销日志

撤销日志的格式是怎样的

答：-条记录在Undo Log页中的Undo Log日志大体包含两部分:分别是记录了Undo类型、表ID、上一条、下一条日志的偏移地址等在内的"基本信息"，以及记录了不同操作和数据的"操作信息"，如上图所示

在事务中不同的 DML 操作对应的撤销日志是否不同？

答：在执行 DML 语句操作数据库时，不同 SQL 语句对应的撤销操作不同，不同的撤销操作对应的 Undo Log 存储格式也不相同，按照增、删、改等不同的 DML 操作，生成对应的撤销日志。

不同操作对应的撤销日志如何区分？

答：Undo 类型有很多种，最常见的就是增、删、改，分别用 TRX_UNDO_INSERT_REC、TRX_UNDO_DEL_MARK_REC 和 TRX_UNDO_UPD_EXIST_REC 表示，如图所示:

新增 (TRX_UNDO_INSERT_REC) 时的 Undo Log 操作信息相对简单，只记录了主键值，主键长度等主键信息；

删除 (TRX_UNDO_DEL_MARK_REC) 时除了记录主键信息之外，还记录了旧的事务 ID，旧的 ROLL_POINTER 信息，用来构建有序的 Undo 版本链，还会记录一些被索引字段的信息；

更新 (TRX_UNDO_UPD_EXIST_REC) 时较为复杂，如果不更新主键则和删除时类似，会记录主键信息、旧的事务 ID、旧的 ROLL_POINTER 信息、被索引字段的信息和被更新的信息；如果更新了主键，则会记录两条 Undo Log，一条删除的和一条新增的；

撤销日志是如何组织在一起的

一条条 Undo Log 会被逐一放在 Undo Log 页中，Undo Log 页和其他类型的页一样都会包含头尾信息，除此之外还有 Undo Log 特有的信息，包括：

UNDO PAGE HEADER：记录了 Undo Log 页类型、日志偏移位置、上一页下一页链表引用等信息
UNDO LOG SEGMENT HEADER：记录了回滚段信息，包括1、标记当下这个段是不是活跃的 2、记录最后一个事务的偏移量 3、记录段中第一个页的位置
UNDO LOG HEADER：记录了产生这条日志的事务 Id：Trx ID；事务的提交顺序：Trx No 和其他事务相关信息

在这三个特有的头信息之外，其他空间都会用来记录 Undo Log 日志，如果某个事务很大，一个 Undo Log 页没有办法完整记录，就需要申请新的 Undo Log 页，然后通过 UNDO PAGE HEADER 中链表引用信息链接到前一个页，后面的这些页只需要记录 Undo Log 并不需要记录 Undo 头信息。

这个由 Undo Log 构成的链表称做 Undo 链，在事务中会起到非常重要的作用。

事务提交后，Undo Log能否删除

答：不一定，根据当前修改的数据有没有上一个版本而定。对于新增操作所记录的Undo Log日志，在事务提交之后就可以直接删除了，而删除和更新的Undo Log日志还需要服务事务的MVCC，所以并不能直接删除，而是加入到hisotry list 中。因些InnoDB为了最大程度节省空间提升效率对Undo Log进行了分类

撤销日志分类（为了管理而分类）

Undo Log 分为两大类：一类只记录新增操作，事务提交后可以直接清除；另一类记录删除和更新操作，所以相应的回滚链也会被区分为 2 个：Insert Undo 链和 Update Undo 链 (Delete + Update)。

另外普通表和临时表分别对应这两类 Undo 链，如是一个事务既有新增又有修改并且用到了临时表，那么这个事务最多可以分配四个撤销日志，也就是四个 Undo 链，分别是：

对用户定义的普通表进行 INSERT 操作
对用户定义的普通表进行 UPDATE 和 DELETE 操作
对用户定义的临时表进行 INSERT 操作
对用户定义的临时表进行 UPDATE 和 DELETE 操作

根据事务的操作按需要写入 Undo 日志，比如，在普通表和临时表执行 INSERT、UPDATE 和 DELETE 操作的事务需要会写入以上四种类型的撤销日志；只在普通表上执行 INSERT 操作的事务只需要一个撤销日志。

对普通表执行操作的事务将从指定的系统表空间或 undo 表空间的回滚段分配 undo 日志。对临时表执行操作的事务从指定的临时表空间回滚段分配 undo 日志。

InnoDB最大支持并发读写事务的数量如何计算

可以用以下公式计算InnoDB能够支持的并发读写事务的数量

如果每个事务执行 INSERT 或 UPDATE 或 DELETE 操作，注意只执行一种类型的操作，并发读写事务数为:

# 最⼤⽀持 (16384 / 16) * 128 * 127 = 16,646,144
# 页大小                    回滚段的数量                撤销表空间的个数
(innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces

如果每个事务执行 INSERT 和 UPDATE 或 DELETE 操作，并发读写事务数为:

# 最⼤⽀持 (16384 / 16 / 2) * 128 * 127 = 8,323,072 一个事务要用撤销日志段
(innodb_page_size / 16 / 2) * innodb_rollback_segments * number of undo
tablespace

如果一个事务在临时表上执行 INSERT 操作，并发读写事务数为:

# 临时表最⼤⽀持 (16384 / 16) * 128 = 131,072
(innodb_page_size / 16) * innodb_rollback_segments

如果一个事务在临时表上执行 INSERT 和 UPDATE 或 DELETE 操作，并发读写事务数为:

# 临时表最⼤⽀持 (16384 / 16 / 2) * 128 = 65,536
(innodb_page_size / 16 / 2) * innodb_rollback_segments

如何理解Undo链

Insert Undo链和 Update Undo链采用了不同的组织方式;

对于新增操作，Insert Undo链中的每个Undo Log都会对应一条新的数据行，这个数据行中用ROLL_POINTER 信息来关联Undo Log，在回滚时就可以通过它找到需要回滚的Undo Log了，如图所示:

对于删除和更新，Update Undo链中的每个Undo Log也都对应一个数据行，每次更新都会通过Undo Log中的 ROLL_POINTER 进行关联，从而每个数据行都会构成一个Undo Log版本链，回滚的时候就可以依序撤销，Update Undo链如下图所示:

撤销日志为什么需要落盘

在对数据进行修改时，都是在内存中操作的，也就是在BufferPool中修改对应的数据页，在修改数据页之前先把对应的撤销日志记录在内存中，如果此时事务回滚直接根据内存中的撤销日志做回滚操作即可;

在修改完成提交事务后，脏页进行落盘操作，此时事务已提交，不能回，所以撤销日志也就失效，当服务器崩溃时，如果事务没有提交，所有的修改都在内存中，还没有落盘，对于修改直接丢弃;如果事务已经提交，则根据重做日志和双写缓冲区中的备份进行恢复;

通过分析看上去撤销日志并不需要落盘，其实以上的分析场景并没有考虑到全部的场景，比如大事务的运行、MVCC中版本链的销毁时机、事务的不同隔离级别等因素;

答：在运行大事务时，InnoDB为了避免大事务提交时统一落盘操作带来的性能问题，允许在事务进行的过程中就进行落盘操作并记录对应的UndoLog，当发生崩溃恢复时，通过回放UndoLog把未提交的事务进行回滚;

如果一个事务已经提交，但还有其他事务需要访问版本链中对应的UndoLog，那么也需要把相应的撤销日志保存到 hisotry list 中。

不同隔离级别下，没有提交的事务也可能会落盘，回滚时依然要完成撤销操作

撤销日志在内存中如何记录

与数据页在内存中的保存方式相同，撤销日志在内存中也保存在 Buffer Pool 中，与磁盘中的 UndoLog 页结构一致，内存中每个 UndoLog 页通过控制块管理。

在内存中使用四个链表来管理正在使用的 UndoLog 页和空闲 UndoLog 页，根据不同的日志类型分为：

Insert List：正在被使用的用来管理 Insert 类型的 UndoLog 页
Insert Cache List：空闲的或被清理后可以被后续事务重用的 Insert 类型 UndoLog 页
Update List：正在被使用的用来管理 Update 类型的 UndoLog 页
Update Cache List：空闲的或被清理后可以被后续事务重用的 Update 类型 UndoLog 页

撤销日志的写入过程

当写事务开始时，会先分配一个处理 ACTIVE 状态的 Rollback Segment；
当第一次 DML 操作产生 Undo Record 时，会轮询当前 Rollback Segment 中可用的Slot，以便获取一个 Undo Log Segment；
根据撤销日志的类型获取 UndoLog 页，并挂载到对应的 List 当中；
在 UndoLog 页顺序写入日志，当一个 UndoLog 页写满之后，会获取新的 UndoLog 页以便继续写入当前事务生成的日志，这里注意：单条 UndoLog 不能跨页存储，也就是说当某条日志在当前页中放不下时，会整体保存在下一页中；
由后台线程把日志刷入磁盘；
当事务结束之后 (commit 或者 rollback)，insert 日志类型对应的 Undo Log Segment 和 UndoLog page 会直接回收，而 update 日志类型对应的 Undo Log Segment 和 UndoLog page 会等待后台的清理操作完成后，确保日志不会有事务再访问后进行回收，回收的 UndoLog 页被挂载到 Cache List 中。

撤销日志的回滚过程

回滚操作可以是用户通过 rollback 主动触发，也可能发生在崩溃恢复时，不论是哪种触发条件，回滚操作都是相同的，基本过程就是读取该事务的 Undo Log，从后向前依次进行逆向操作，从而恢复索引记录；
对于 Insert 类型的回滚操作就是 Delete，在删除的过程中会重新调整主键索引和二级索引；
对于 Update 和 Delete 类型的回滚操作，主要是回退这次操作在所有主键索引和二级索引的影响，可能包括重新插入被删除的二级索引记录、去除行管理信息中的 Delete Mark 标记、将二级索引记录修改回之前的值等；
完成回滚的 Undo Log 会进行回收，将不再使用的 UndoLog 页的磁盘空间还给 Undo Log Segment，这个过程是写入过程的逆操作。

撤销日志的清理过程

InnoDB 通过保存多份 Undo Log 的历史版本来实现 MVCC，当某个历史版本已经确认不会被任何现有的和未来的事务访问时，就应该被清理掉；
当开启一个事务时都会被分配一个事务编号 trx_id，而事务进行读操作时会创建一个 ReadView，并记录当前所有事务中的最小活跃事务编号 m_low_limit_id，如果版本链中日志的 trx_id < m_low_limit_id，则表示当前读操作发生时，日志对应的事务已提交，其修改的新版本是可见的，因此不再需要通过 Undo 版本链构建之前的版本，这个事务的 Undo Log 也就可以被清理了。
Undo 的清理工作是由专门的后台线程进行扫描和分发，并由多个线程进行清理，并可以通过系统变量 innodb_purge_threads 配置清理线程数，系统变量 innodb_purge_batch_size 可以配置每次清理的页数，这里不再进行过多的讨论。

双写缓冲区-Doublewrite Buffer

答：双写缓冲区是磁盘上的一个存储区域，当 InnoD8 将缓冲池中的数据页写入到磁盘上表空间数据文件之前，先将对应的页写到双写缓冲区;如果在数据真正落盘的过程中出现了意外退出，比如作系统、存储子系统崩溃或异常断电的情况，InnoD8 在崩溃恢复时可以从双写缓冲区中找到一份完好的页副本，执行过程如下图所示:

数据存储

在MySQL8.0.20之前， doublewrite 缓冲区位于InnoDB系统表空间中。从MySQL8.0.20开始，doublewrite 缓冲区默认存储区域位于数据目录下的 doublewrite 文件中。

配置

是否启用 doublewrite 缓冲区可以通过系统变量 innodb_doublewrite[=ON|OFF]控制,犬认为启用，如果真实的业务场景更关注性能而不是数据完整性，可以考虑禁用doublewrite缓冲，例如在执行测试的环境中;

doublewrite 文件所在目录通过系统变量 innodb_doublewrite_dir(MySOL8.0.20中弓、)指定，如果不指定则在 innodb_data_home_dir 目录(默认为data目录)下创建;如果指定doublewrite目录，建议设置在最快的存储介质上，以提高效率;

双写文件的数量通过系统变量 innodb_doublewrite_files 设置，默认情况下，为每个缓冲池实例创建两个doublewrite文件，也就是说文件数量为 innodb_buffer_pool_instances*2;此变量用于高级性能调优，大多数场景使用默认设置即可;

重做日志Redo Log作用与写入时机

Redo日志保证了事物持久性的一致性

答：重做日志用于在数据库崩溃后恢复已提交事务还没有来的及落盘的数据。重做日志以文件的形式保存在磁盘上，在正常的操作过程中，MySQL根据受影响的记录进行编码并写入重做日志文件，这些数据称为"Redo"，在重新启动时自动读取重做日志进行数据恢复。

为什么使用Redo log，而不是直接写入磁盘

我们对数据进行的 DML 操作都会包含在事务当中，当完成修改并且提交事务之后，在内存中被修改的数据页就要刷新到磁盘完成持久化；
如果这次 DML 操作对应的修改开始刷盘的话，当服务器崩溃，没有被刷到磁盘的数据页就从内存中丢失，这时这个事务的修改在磁盘上就是不完整的，也就是没有保证事务的一致性
为了解决这个问题，InnoDB 在执行每个 DML 操作时，当内存中的数据页修改完成之后，把修改的内容以日志的形式保证在磁盘上，然后再对数据页进行真正的落盘操作，这样做就相当于对修改进行了一次备份，即使当服务器崩溃也不会受到影响，当服务器重启之后，可以从磁盘上的日志文件中找到上次崩溃之前没有来的及落盘的数据继续执行落盘操作；
InnoDB 引擎的事务采用了 WAL 技术 (Write-Ahead Logging)，基本思想是先写日志，再写磁盘，只有日志写入成功，事务才算提交成功，这里的日志就是 Redo Log。当发生宕机且数据未刷到磁盘的时候，可以通过 Redo Log 来恢复，保证 ACID 中的持久性，这也是 Redo Log 的作用。

Redo Log的写入时机

当发生数据修改操作时追加重做日志，已落盘数据对应的日志位置被记录为一个检查点，检查点之前的数据被置为无效，所以重做日志文件可以循环使用。以一个更新操作为例，重做日志的写入过程与时机，如下图所示:

Redo Log的格式

为了节省空间提高效率，RedoLog只记录被修改的内容，比如当前的DML修改了哪个表空间、表空间中的哪个数据页，数据页中多少偏移量处的值修改成了什么，比如:

RedoLog 本质上只是记录了事务对数据库做了哪些修改，修改操作包含多种场景，比如对数据行、索引页的增删改，对范围的修改与删除等等，不同场景的 redo 日志定义了不同的类型，但是绝大部分类型的 redo 日志都有下边这种通用的结构包括：

Type：日志类型，1BTYE
Space ID：操作所属的表空间，4BTYE
Page no：操作的数据页在表空间中的编号，4BTYE
data：日志的内容，长度不固定

data部分的具体内容

分为数据页中的偏移量，修改内容的长度和具体的修改内容

RedoLog的分类

答：RedoLog的类型根据数据操作的不同场景和对日志的优化方式有几十种之多，总体可以分为:

用于数据页的日志类型，比如对数据页的修改
用于表空间文件的日志类型，比如对表空间的修改
提供额外信息的日志类型

不同日志类型对应了那些操作

属于用于数据页的日志类型中的几种最常见数据操作所对应的日志类型如下:MLOG_WRITE_STRING = 30，type 对应的值为30，表示在页面的某个偏移量处写入一个字符串，由于字符串的长度不固定，需要用到一个表示长度的区域记录，此时日志内容格式如下图所示:

MLOG_4BYTES =4，type 对应的值为4，这种类型应用于对固定长度值的修改，比如修改整型字段，由于长度固定，所以用于表示长度的区域可以省略，从而尽可能的减少空间使用，此时日志内容格式如下图所示:

类似的类型还有 MLOG_1BYTE = 1，MLOG_2BYTES = 2，MLOG_8BYTES = 8 分别表示固定修改 1 字节、2 字节、8 字节的数据，日志格式与 MLOG_4BYTES = 4 相同

还有其他一些日志类型，比如：

MLOG_REC_INSERT_8027 = 9，表示写入一条行格式为非紧凑型的记录，对应的行格式为 Redundant
MLOG_COMP_REC_INSERT_8027 = 38，表示写入一条行格式为紧凑型的记录，对应的格式为 Compact、Dynamic 和 Compressed
MLOG_REC_UPDATE_IN_PLACE_8027 = 13，表示更新一条记录
MLOG_REC_DELETE_8027 = 14，表示从数据页中删除一条记录
MLOG_LIST_END_DELETE_8027 = 15，表示从索引页中删除最后一条记录
MLOG_LIST_START_DELETE_8027 = 16，表示从索引页中删除第一条记录
MLOG_LIST_END_DELETE_8027 和 MLOG_LIST_START_DELETE_8027 配合可以删除索引页中一个范围的记录，而不用记录每一条记录的删除日志
MLOG_PAGE_CREATE = 19，表示创建一个索引页，这个类型是关于页的类型

属于用于表空间文件的日志类型：

MLOG_FILE_CREATE = 33，表示创建一个.ibd 表空间文件
MLOG_FILE_RENAME = 34，表示重命名一个表空间文件
MLOG_FILE_DELETE = 35，表示删除一个表空间文件

属于提供额外信息的日志类型：

MLOG_MULTI_REC_END = 31，只由一个字节的 Type 构成，用于标识一个 Mini - Transaction (MTR) 的结尾

答：不同的日志类型对应的日志内容和作用各不相同

用于数据页的日志类型，主要记录数据页的修改，比如创建和删除数据页，以及对数据的增删改查操作
用于表空间文件的日志类型主要记录对表空间文件的修改
提供额外信息的日志类型主要标记一个Mini-Transaction的结尾

什么是Mini-Transaction

以一个Insert操作为例，对数据页的影响一般分为两种情况:
如果写入记录所在的数据页空间充足，足够存储一条将要写入的记录，那么就可以直接写入，如下图所示:

如果写入的数据页空间不充足，无法放下这条记录，由于在数据页中真实数据是按主键顺序排列的，那么就要新建一个数据页，对原来的数据进行调整，把一部分数据复制到新的数据页中，以便在目标数据页上留出足够的空间来保存即将写入的记录，此时对应的示意图如下所示:

通过以上两种情况下插入一条记录的分析可以看出，当数据页空间充足的情况下可以直接写入数据，并记录一条对应RedoLog即可

当数据页空间不充足无法放下这条记录的情况下，会创建一个新数据页，同时还有数据的复制和写入，索引树非叶子节点上修改，在实际的执行过程中还有对表空间中段、区中统计信息的修改等等，这意味一个简单的Insert操作有会产生很多条RedoLog。

在记录RedoLog时服务器崩溃了导致日志不完整怎么办

所以在记录RedoLog的时候要保证一个DML所对应的一系列日志必须是完整的才可以执行恢复操作，否则就不执行恢复。

Mini-Transaction就是针对以上的操作过程定义的概念，也就是说把记录一个DML操作的过程称为一个 Mini-Transaction ，简称 MTR，一个所谓的MTR包含一个DML操作产生的一组完整日志，在进行崩溃恢复时这一组RedoLog做为一个不可分割的整体。

这里所说的不可分割的组是MySQL中定义的，常见的有

向聚簇索引对应B+树的页面中插入一条记录时产生的RedoLog不可分割;
向某个二级索引对应B+树的页面中插入一条记录时产生的RedoLog不可分割;
还有其他的一些对页面的访问操作时产生的RedoLog不可分割。

每条语句根据具体的执行情况可能会产生多个MTR。

答：Mini-Transaction是MySQL内部对底层数据页的一个原子操作，包含一个DML操作产生的一组完整日志，保证数据库异常恢复时数据页中数据的一致性。

如何标记一组RedoLog属于一个MTR

答：在执行DML操作的过程中，每一个对数据页的修改都会记录一条RedoLog，这些日志会被顺序记录下来，并在这组日志的最后加一条特殊的日志标识作为一个MRT的结尾，这条特殊的日志结构非常简单，只有一个 TYPE字段，类型为MLOG_MULTI_REC_END =31，也就是日志分类中的提供额外信息的日志类型，一个MTR对应的日志组，如下图所示:

如果一个MTR只有一条日志是否可以优化

当然可以，如果一个MTR只有一条日志，直接在这条日志后加一个类型为MLOG_MULTI_REC_END = 31 的标识可以做为MTR的结尾，但这样做有点浪费空间;

InnoDB为了尽可能的节省空间，在MTR只有一条日志的情况下，做了一个优化。通过上面的介绍了解了日志类型虽然很多，但也只有几十种，而用来表示日志类型的 TYPE 字段长度为 1BTYE而这 1BTYE 中只用7个比特位，代表整数127，就完全可以表示所有的日志类型，与是省出来一个比特位就可以用来表示当前MTR包含一条还是一组RedoLog，也就是说如果 TYPE 字段的第一个比特位为1，表示MTR只包含一条RedoLog，为0表示MTR包含一组RedoLog，如下图所示:

事务与Mini-Transaction是什么关系?

Mini-Transaction是包含的是一个DML操作对应的一组RedoLog，而一个事务中可能会包含多个。DML操作，所以一个事务中包含一个或多个SQL语句，一个SOL语句包含一个或多个MTR，一个MTR包含一条或多条RedoLog，他们之间的关系如下图所示:

RedoLog 是如何写入缓冲区的

用来组织RedoLog的数据结构是Redo页，页的大小是512B，也可以称为一个Redo LogBlock，这个大小刚好对应磁盘上一个扇区，当日志写入磁盘时可以保证连续性，Redo LogBlock 的示意图如下所示:

Log Block Header

LOG_BLOCK_HDR_NO：Block 的唯一标识，是一个大于 0 的值，取值范围 1~0x40000000UL，而 0x40000000UL 对应的整数是 1073741824 即 1GB，也就是说 InnoDB 最多能够生成 1GB 个日志块，每个日志块为 512B，所以 InnoDB 允许维护日志的最大容量为 512GB，在后面介绍配置日志相关的选项时，关于日志容量的大小就是以此为依据；
LOG_BLOCK_HDR_DATA_LEN：表示 Block 中已经使用了多少字节，由于块头占用了 12B 的空间，所以初始值为 12，当 Log Block Body 被全部写满时那么这个值就是 512；
LOG_BLOCK_FIRST_REC_GROUP：如果一个 MTR 会生产多条 redo 日志记录，这些日志记录被称之为一个 redo 日志记录组，LOG_BLOCK_FIRST_REC_GROUP 代表该 Block 中第一个 MTR 中第一条日志的偏移量。
LOG_BLOCK_CHECKPOINT_NO：表示检查点的编号，关于检查点后面会详细详解

Log Block Trailer

LOG_BLOCK_CHECKSUM：表示 Block 的校验和，用于正确性校验。

向日志缓冲区中写入日志是一个顺序写入的过程，也就是从缓冲区的第一个 Redo Log Block的 Log Block Body 开始依次向后写，一个block的空间空间用完之后再写下一个block，那么有一个首先要解决的问题，当有一记日志需要写入缓冲区时，应该往哪个block中的哪个位置写呢?

从日志缓冲区写RedoLog时从内存中的哪个地址开始写?

InnoDB 的提供了一个名为 buf_free 的全局变量，该变量表示后续写入日志在 Log Buffer中的起始位置，如图所示:

不同的事务在并发执行时如何记录RedoLog?
InnoDB以MTR为单位记录RedoLog，一个事务中包含多个MTR，一个MTR包含多条RedoLog，这些RedoLog是一个不可分割的日志组;

一个事务在执行过程中并不是每生成一条RedoLog就写入到LogBuffer中，而是把生成的RedoLog先缓存在内存的一个区域中，当一个MTR执行完成后把这组日志一起复制到LogBuffer;

假设有两个事务T1,T2并发执行，每个事务中都包含2个MRT，即事务T1包含mtr_t1 1和mtr_t1_2，T2包含mtr_t2 1和mtr_t2_2，如下图所示:

在并发环境下不同事务中的MTR是交替执行的，当MTR执行完成之后对应生成的RedoLog会被写入Log Buffer,

答：RedoLog在内存中用Redo页进行组织，称为RedoLogBlock，每个Redo Log Block大小固定为512B，对应磁盘上一个扇区，日志被顺序安排在Log Block Body 中;

在Log Buffer中多个 Redo Log Block 顺序排列，Redo Log Block 的个数由Log Buffer的大小决定;

当执行事务时，不同的语句对应不同的数据库操作，一条SQL语句可能包含多个MTR，一个MTR包含多条RedoLog，MTR中的多条日志称为一个日志组，写入LogBuffer的日志是以MTR对应的日志组为一个单位，这组日志不可分割。

Redo Log的刷盘时机

InnoDB 在以下情况会把 RedoLog 刷到磁盘：

Log Buffer 空间不足时：Log Buffer 大小是有限的，可以通过系统变量 innodb_log_buffer_size 设置，如果当前 Log Buffer 中的 RedoLog 占用了 Log Buffer 总容量一半左右会触发刷盘；
事务提交时：当事务提交时，事务中对应的 MTR 已经完全记录在了 Log Buffer 中，在数据真正落盘之前，需要把对应的 RedoLog 刷新到磁盘；
后台线程定时刷盘：后台的 Master Thread 线程，大约每秒都会把 Log Buffer 中的 RedoLog 刷新到磁盘；
正常关闭服务器时：在服务关闭之前会把会把 Log Buffer 中的 RedoLog 刷新到磁盘；
做检查点 (checkpoint) 操作时

不同的刷盘策略

日志缓冲区的内容定期刷新到磁盘，可以通过系统变量 Innodb_flush_log_at_timeout=N 设置，N 默认为 1，单位为秒；

通过设置系统变量 innodb_flush_log_at_trx_commit 设置写入和刷盘策略，默认值为 1

0：日志每秒写入系统缓冲区并刷新到磁盘，未写入系统缓冲区的事务日志可能会在 MYSQL 崩溃时丢失；
1：日志在每次事务提交时写入系统缓冲区并刷新到磁盘；
2：日志在每次事务提交后写入系统缓冲区并每秒一次刷新到磁盘，未刷新到磁盘的日志可能在系统崩溃时丢失。

如果启用二进制日志且设置 sync_binlog = 1 时，则必须设置 innodb_flush_log_at_trx_commit = 1

不同刷盘策略的影响如下：

值为0时:表示日志每秒写入操作系统缓存并刷新到磁盘，如果MySQL崩溃，那么在一秒内没有写入操作系统缓存的Redo Log将会丢失;
值为2时:日志在每次事务提交后写入系统缓冲区并每秒一次刷新到磁盘，此时已提交的事务Redo Log全部都写入了操作系统缓存，MySQL无论是否崩溃，RedoLog都会以指定的时间刷新到磁盘，但是如果服务器崩溃或断电，将会导致操作系统缓存中的RedoLog丢失;
值为 1时:日志在每次事务提交时写入系统缓冲区并刷新到磁盘，此时RedoLog从LogBuffer中写入操作系统缓存并立即刷新到磁盘，从而尽可能的保证日志的完整性，推荐使用。

Redo Log对应磁盘上的文件是什么?

重做日志文件位于数据目录下的 #innodb_redo 目录中，在MySQL8.0中InnoDB 共维护 32 个重做日志文件，每个文件的大小等于1/32*innodb_redo_log_capacity

重做日志文件分为普通类型和备用类型，并且使用 #ib_redoN命名约定，其中N是重做日志文件编号，备用的重做日志文件使用_tmp为后缀

重做日志的总容量可以通过系统变量 innodb_redo_log_capacity 设置，8.0.34版本开始最大为512GB

通过查看 #innodb_redo 目录，可以看到系统生成了32个RedoLog文件，当RedoLog从内存刷到磁盘时，先从第一个日志文件开始写，第一个写满之后顺序写到第二个，以此类推;如果最后-个也写满了，就会重新从第一个文件开始写，也就是说重做日志文件可以循环使用，如图所示:

什么是LSN

LSN 是 Log Sequence Number 的简写，称为日志序号；

MySQL 在运行期间，只要执行 DML 操作就会修改数据页，意味着会不断的生成 RedoLog，InnoDB 为了记录生成的日志总量 (字节数)，设计了一个只增不减的全局变量，这个全局变量就是 LSN，起始值：16*512 = 8192，最大值 2^64 - 1；

当一个 MTR 所包含的一组 RedoLog 被记录在 Redo Log Block 中时，实际是保存在 Log Block Body 区域，但是在统计 LSN 增量时，如果 MTR 跨 Block 保存时，是按照实际写入的日志大小加上 Log Block Header 所占的 12Byte) 和块尾 Log Block Trailer 所占 4Byte；

示例：
a. 系统启动后初始化 Log Buffer，buf_free 指向第一个 block 偏移量为 12Byte 的位置，也就是 block Header 之后，此时 LSN 也会增加 12，即 8192 + 12 = 8204

b. 假设 MTR_1 中包含的一组 RedoLog 大小为 200Byte，那么 LSN 就会在原来的基础上加 200，即：8204 + 200 = 8404

c. 假设 MTR_2 中包含的一组 RedoLog 大小为 1000Byte，这里当前的 Block_1 已经放不下这个 MTR，于是日志顺序保存在后面的 Block 中，占满第二个 Block 后，直到使用了第三个 Block 的一部分空间，日志保存完成；这时 LSN 不但要记录 MTR_2 中日志的总大 1000Byte，还要记录 Block_1+Block_2 的 Log Block Trailer 和 Block_2+Block_3 的 Log Block Header，总大小为：1000 + 12 * 2 + 4 * 2 = 1032，此时 LSN 的值为：8404 + 1032 = 9436

Redo Log日志文件的格式

Log Buffer中的Redo Log Block与磁盘中的Redo Log Block有哪些不同?

磁盘中RedoLog的格式与内存中的格式相同，在内存中LogBuffer是一片连续的内存空间，被划分成了若干个 512 字节大小的 Redo Log Block 用来保存Redo Log，将Log Buffer中的RedoLog刷新到磁盘，本质就是把 Redo Log Block 的写入日志文件中，所以Redo Log对应的日志文件其实也是由若干个 512 字节大小的 block 组成，只不过在磁盘上多了用于文件管理的文件头信息。3

checkpoint的作用

哪些Redo Log可以被覆盖

RedoLog是用作崩溃后恢复没有完成落盘的事务，也就是说当Buffer Pool中的脏页写入RedoLog，但数据页还没有落盘时发生的崩溃，当服务器重启之后可以根据RedoLog进行恢复，这也是RedoLog的应用时机，所以这种状态下的RedoLog不能被覆盖，如下图所示:

如果缓冲池中的脏页在记录RedoLog之后，也完成了真正的落盘操作，那么相应的RedoLog就没有用了，所以这部分RedoLog就可以被覆盖，如下图所示:

如何记录可以覆盖的日志文件位置

在这个基础上InnoDB采用一个全局变量checkpoint_lsn 来记录当前系统中可以被覆盖日志总量是多少，也就是说checkpoint_lsn 记录已落盘脏页对应的日志结束时LSN的值，此时LSN小于checkpoint_lsn 的RedoLog就可以被覆盖，如图所示:

当脏页刷新到磁盘之后，重新计算 checkpoint_lsn 的操作，称为一次 CHECKPOINT 操作，也可以说是重置一次检查点，系统会用一个 checkpoint_no 变量记录发生 CHECKPOINT 操作的次数，每做一次 CHECKPOINT 操作 checkpoint_no 就会加 1
由于 RedoLog 文件的大小是固定的，在系统启动时已经分配好了对应的 Redo Log Block，所以很容易就可以根据 checkpoint_lsn 计算写入位置在日志文件中的偏移量
关于检查点相关的 checkpoint_no、checkpoint_lsn 以及写入偏移量的信息会被记录在第一个日志文件的管理区，同时 InnoDB 规定，当 checkpoint_no 的值是偶数时写到 checkpoint1 中，是奇数时写到 checkpoint2 中。

答：CHECKPOINT 也称为检查点，由于RedoLog文件是可以循环使用的，当最后一个文件写满时又会从第一个文件开始写入，这必将导致老的日志被覆盖，CHECKPOINT 是标记已被刷新到磁盘的脏页刷对应的RedoLog可以被覆盖的一种操作，当日志的LSN小于已落盘脏页对应的LSN都可以被覆盖。

如果没有小于 checkpoint_lsn 的日志时如何处理?

如果日志文件中没有小于 checkpoint_lsn 的日志时，表明日志文件已经使用完了，这时原来的日志不能被覆盖，InnoDB会先优先刷新脏页到磁盘，再做CHECKPOINT 操作，之后再继续进行日志记录。

重做日志还有哪些主要的配置项

重做日志在磁盘上所占的空间可以通过系统变量 innodb_redo_log_capacity 控制，变量值以字节为单位，最大值549755813888，表示512GB，可以在选项文件或在运行时使用 SETGLOBAL语句进行设置，如下所示:

# 将RedoLog的最⼤容量设置为8GB
SET GLOBAL innodb_redo_log_capacity = 8589934592;

重做日志的目录可以通过系统变量 innodb_log_group_home_dir 进行设置，如果没有指定则日志文件位于数据目录的 #innodb_redo 目录中，如果定义了innodb_log_group_home_dir 变量，则日志文件存放在该目录下的 #innodb_redo 目录中:

查看重做日志状态

通过状态变量 innodb_redo_log_capacity_resized 显示当前重做日志容量限制:

如何根据Redo Log进行崩溃恢复

如何确定哪些日志需要恢复?

前面我们介绍过每一次 CHECKPOINT 操作都会重新计算 checkpoint_lsn，checkpoint_lsn 之前的日志表示已经被刷到磁盘数据页所生成的RedoLog，既然已被刷到磁盘，也就没有必要进行恢复，所以需要恢复的是 checkpoint_lsn 之后的日志

如何获取最新的 checkpoint_lsn 和恢复的起点?

RedoLog文件组中的第一个文件的管理信息中有两个block checkpoint1和 checkpoint2其中都存储了 checkpoint_lsn 和 checkpoint_no 信息，每次做 CHECKPOINT 操作时,会在这两个block中交替写入CHECKPOINT 信息，只要需要把这两个block中保存的checkpoint_no 值比较一下，哪个值大就表示哪个block存储的就是最近的一次checkpoint信息。这样我们就能拿到最近发生的 checkpoint 对应的 checkpoint_lsn 值以及它在RedoLog文件组中的偏移量 checkpoint offset。

恢复的过程如下：

第一条日志在 checkpoint_lsn 之前，表示已经落盘不用恢复;

checkpoint_lsn 之后的日志可以通过顺序扫描的方式，根据日志记录的内容依次恢复对应的数据页

InnoDB在顺序读取日志进行恢复的过程中采用了一些优化措施:首先根据日志的 Space Id 和Page No 计算出散列值，以这个散列值为 KEY，把 Space Id 和 Page No 相同的日志放到哈希表的同一个槽里，如果有多个 Space Id 和 Page No 相同的日志，那么按照日志生成的先后顺序使用链表连接起来，如下图所示:

如何确定哪些日志在崩溃前已经落盘

checkpoint_lsn 之后的日志有可能就根本没有落盘，也有可能已经落盘但没有来的及做CHECKPOINT，在恢复时如何区分呢?

在页结构章节介绍过，磁盘上的每个页都包含一个 File Header 信息，其中又包含已被刷到磁盘的LSN:FIL_PAGE_FILE_FLUSH_LSN 信息，在恢复时就可以通过当前日志对应的LSN与FIL PAGE_FILE_FLUSH_LSN 进行比较，如果日志的LSN小于已刷新到磁盘的LSN，那就证明日志对应的数据在崩溃之前已经落盘，直接跳过即可

答：恢复的过程主要分为以下几步:

1.通过 checkpoint_lsn 和第一个没有写满的日志页确定需要恢复日志的起始和结束位置;

2.遍历日志并把Space ld 和 Page No相同的日志组织在一起，以便一次性恢复完相应数据页的所有内容;

3.日志的LSN小于磁盘数据页文件记录的已刷新LSN时，表示这些数据在崩溃之前已落盘，跳过即可。

通用表空间

通用表空间是使用 CREATE tablespace 语法创建的共享 InnoDB 表空间

通用表空间能够存储多个表的数据，与系统表空间类似也是共享表空间；

服务器运行时会把表空间元数据保存在内存中，在表的数量相同的情况下，通用表空间比独立表空间的数量更少，所以消耗的内存也就更少；

数据文件可以放置在数据目录或数据目录之外的其他位置，对于单独管理关键表非常有用；

支持所有的表格式和行格式的相关特性；

作用

可以把数据量较小且强相关的表，都用一个通用表空间管理起来

创建通用表空间

CREATE TABLESPACE tablespace_name [ADD DATAFILE 'file_name'] [FILE_BLOCK_SIZE =
value] [ENGINE [=] engine_name]

注意：表空间名区分大小写

举例

# 指定表空间⽂件名
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
# 或使⽤随机⽂件名
CREATE TABLESPACE `ts1` Engine=InnoDB;

ADD DATAFILE 子句在MySOL8.0.14及以后的版本是可选的，之前是必需的。如果没有指定ADD DATAFILE 子句，则自动创建一个以 UUID 为文件名的表空间数据文件，通用表空间数据文件以 .ibd 为扩展名。

每创建一个数据库，都会在数据目录生成一个与数据库名相同的子目录，为了为了避免数据目录创建的子目录与以后要创建的数据库重名，不能把通用表空间创建在数据目录的子目录中

如何添加表

在创建的表时使用TABLESPACE指定通用表空间即可

# 在ts1表空间中添加t1表
CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.00 sec)# 在ts1表空间中添加t2表
CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.02 sec)# 把t1表移动到ts1表空间
ALTER TABLE t1 TABLESPACE ts1;

删除通用表空间

DROP TABLESPACE 语句用于删除一个InnoDB通用表空间，在删除通用表空间之前，必须将所有表从表空间中删除，如果表空间不为空，将返回错误。查询通用表空间中的表，可以使用下面的语句:

SELECT a.NAME AS space_name, b.NAME AS table_name FROM
INFORMATION_SCHEMA.INNODB_TABLESPACES a, INFORMATION_SCHEMA.INNODB_TABLES b 
WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test_db/t2 |
| ts1        | test_db/t3 |
| ts1        | test_db/t1 |
+------------+------------+
3 rows in set (0.02 sec)

示例

# 创建通⽤表空间ts1
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
# 在通⽤表空间中创建t1表
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;
# 删除t1表
DROP TABLE t1;
# 删除通⽤表空间ts1
DROP TABLESPACE ts1;

注意事项

使用 TRUNCATE 或 DROP 语句截断或删除表时，通用表空间的空闲容量并不会释放，并且只能用于新的InnoDB表;
通用表空间不属于任何数据库，使用 DROP DATABASE 操作数据库和属于该数据库所有的表时，并不会删除通用表空间。
tablespace_name 表空间名区分大小写

临时表空间

临时表存储的是临时数据，不能永久的存储数据，一般在复杂的查询或计算过程中用来存储过渡的中间结果，MySOL在执行查询与计算的过程中会自动生成临时表，比如表连接查询时得到的结果集就是一张临时表，因为结果中可能包含多个表中的字段并没有一张真实的表与之完全对应。当查询的结果并没有一个真实的表阈值对应，那么这个结果集就会用一个临时表把数据组织起来。

手动创建临时表

用户可以通过使用 CREATE TEMPORARY TABLE 语句手动创建临时表用户创建的临时表也称为外部临时表;MySQL在执行查询与计算的过程中自动生成的临时表称为内部临时表。

外部临时表

使用 CREATE TEMPORARY TABLE语句创建的临时表是外部临时表

通过 INNODB_TEMP_TABLE_INFO 查询临时表元数据。

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************TABLE_ID: 194 # 临时表的表IDNAME: #sql7a79_1_0 # 临时表的名称N_COLS: 4 # 临时表中的列数(包含3个默认隐藏列)SPACE: 182 # 临时表所在的临时表空间ID

TEMPORARY 表只在当前会话中可见，并且在会话关闭时自动删除。这意味着两个不同的会话可以使用相同的临时表名，而不会相互冲突，临时表也不会与已有的非临时表名冲突，如果创建了与现有表同名的临时表，则现有表被隐藏，直到临时表被删除。

重启MySQL服务器后，再次查询临时表信息，得到空集合

内部临时表

由服务器自动创建的临时表是内部临时表服务器在以下情况会自动创建临时表，这个过程用户不能直接控制

使用 UNION 语句合并查询结果
对视图时的一些操作，比如使用 UNION 或聚合函数
使用子查询
使用 DISTINCT 和 ORDER BY 的查询可能需要一个临时表
使用 INSERT...SELECT 语句向表中写入数据时，需要先用一个内部临时表来保存 SELECT 语句查询出来的行，然后将这些行插入到目标表中
使用 COUNT(DISTINCT) 和 GROUP_CONCAT() 表达式时
使用窗口函数时

如何确定服务器创建了临时表

要确定SQL语句是否需要临时表，使用EXPLAIN 并检查Extra列

相关设置

通过配置对应的系统变量来指定临时表使用的存储引擎、使用内存的大小、表中的最大行数等选项。

系统变量 internal_tmp_mem_storage_engine 用于指定内存中内部临时表的存储引擎，值为 TempTable (默认值) 或 MEMORY；
TempTable 存储引擎为 VARCHAR 和 VARBINARY 列以及其他二进制大对象类型进行了优化；
从 MySQL 8.0.28 开始 tmp_table_size 定义了由 TempTable 存储引擎创建的单个内部临时表允许使用内存的最大值，当达到 tmp_table_size 限制时，MySQL 自动将内存中的内部临时表转换为磁盘上的 InnoDB 内部临时表。tmp_table_size 的默认值是 16MB；
系统变量 temptable_max_ram 定义 TempTable 存储引擎创建的所有临时表可以使用的最大内存，默认为 1GB，超出限制后将内存中的内部临时表转换为磁盘上内部临时表；
当内存临时表使用内存储引擎 internal_tmp_mem_storage_engine=MEMORY 时，系统变量 max_heap_table_size 可以限制内存内部临时表的最大行数，默认 16777216；
内存储引擎临时表变得太大，MySQL 会自动将其转换为磁盘上的临时表，内存中临时表的大小由 tmp_table_size 和 max_heap_table_size 这两个系统变量中最小的值决定。

数据存储位置

磁盘上的临时表数据存储在临时表空间中，MVSQL8.0版本中磁盘上的临时表存储引擎支持InnoDB，分为两种类型分别是:

会话临时表空间( session temporary tablespaces)
全局临时表空间( global temporary tablespace )

会话临时表空间的作用与数据存储位置

磁盘上的会话临时表空间存储由用户创建的外部临时表和优化器创建的内部临时表

当 MySQL 接收到第一个创建磁盘临时表的请求时，从临时表空间池中分配会话临时表空间；一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。会话的临时表空间用于存储会话创建的所有磁盘临时表，当会话断开连接时，临时表空间将被截断并释放回池中；
服务器启动时会创建一个包含 10 个临时表空间的临时表空间池，表空间会根据需要自动添加到池中，临时表空间池在 MySQL 正常关闭或中止初始化时被删除；
会话临时表空间文件扩展名为.ibt；
系统变量 innodb_temp_tablespaces_dir 可以指定会话临时表空间的位置。默认数据目录下的 #innodb_temp 目录 (开头的 #号是为了避免与数据库目录命名冲突)，如果无法创建临时表空间池，服务器则拒绝启动；

全局临时表空间的作用与数据存储位置

全局临时表空间存储对用户创建的临时表所做的更改，以便以后回滚操作

系统变量 innodb_temp_data_file_path 指定了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有指定，则默认在系统表空间目录(系统变量innodb_data_home_dir 指定的目录)中创建，默认名为 ibtmp1，初始文件大小略大于12MB :

全局临时表空间在正常关闭或中止初始化时被删除，并在每次启动服务器时重新创建，如果无法创建全局临时表空间，则拒绝启动;如果服务器意外停止，重启服务器时会自动删除并重新创建全局临时表空间。

总结

磁盘上的临时表数据存储在临时表空间中，临时表空间分为两种分别是：