发布日期: 2023-06-15

文章字数: 4.7k

阅读时长: 17 分

1 常见引擎

种类	特点
InnoDB	现`Mysql`的`默认`事务型存储引擎，并且提供了`行级锁`和`外键`的约束。性能不错且支持`自动崩溃恢复`
MyISAM	`Mysql 5.1` 版本前的`默认存储引擎`。特性丰富但`不支持事务`，也不支持`行级锁`和`外键`，也没有`崩溃恢复功能`
CSV	可以将 `CSV 文件`作为MySQL的`表`来处理，但这种表`不支持索引`
Memory	适合快速访问数据，且数据不会被修改，重启丢失也没有关系；默认使用`HASH索引`
Archive	`归档类型引擎`，只能支持`insert`和`select`，从`MySQL 5.1`开始支持索引。
Federated	远程的数据库的表。本地表不保存数据，通过`远程`来返回，类似于`SQL Server`的`链接服务器`和`Oracle`的`透明网关`。
Merge	一组`MyISAM`的表，结构相同，操作一个对所有的表都生效。

1.1 MyISAM和InnoDB对比

对比项	MyISAM	InnoDB
外键和事务	`不支持`，适合高速的检索和存储。	`支持`，适合大量新增和更新，强调安全和数据完整
锁机制	支持`表级锁`，表内所有数据都被锁定。	支持`行级锁`，可以锁定具体的一条记录，`基于索引加锁`。
索引结构	`索引`和`记录`是`分开的`，所以查询较快。	`聚集索引`，索引和记录存储`在一块`，缓存索引也缓存记录
并发处理能力	`表级锁`，并发写入性能低下。	`行级锁`，大量新增性能较好
存储文件	一个`.frm`表结构文件，一个`.MYD`数据文件，一个`.MYI`索引文件，`256TB`最大存储(5.0以上)	一个`.frm`表结构文件，一个`.idb`数据文件，最大支持`64TB`
应用场景	`高速查询`、`不需要事务`、`并发低`、`数据一致性要求低`	`数据安全`、`行级锁提高并发能力`、`事务控制`、`大量新增`、`提升服务器内存利用率`。`普遍场景:推荐使用InnoDB`

1.2 InnoDB内存结构和磁盘结构总览

内存结构和磁盘结构总览

2 InnoDB数据存储

2.1 逻辑结构

InnoDB存储引擎的逻辑存储结构如下图所示：

Tablespace-segment-extent-page-row

2.1.1 表空间

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）。

同一个数据库实例的所有表空间都有相同的页大小；
默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同：

Relation-Between-Page-Size-Extent-Size

从图中可以看出，在 InnoDB 存储引擎中，一个区的大小最小为 1MB，页的数量最少为 64 个。
InnoDB中，表空间主要分为独立表空间和系统（默认）表空间（还有共享表空间、回滚表空间、临时表空间）。
对于系统表空间来说，对应着文件系统中一个或多个实际文件;
对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。
InnoDB采用将存储的数据按表空间进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件。
设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。
独立表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在默认的表空间中。

InnoDB存储引擎对于文件的存储方式

Tips: 表的物理存储：MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在 .frm文件中，后者存储在 .ibd 文件中。

2.1.2 段

2.1节图中显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（图中的Leaf node segment），索引段即为B+树的非索引节点（图中的Non-leaf node segment）。回滚段较为特殊，将会在后面的章节进行单独的介绍。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念。

2.1.3 区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4～5个区。在默认情况下，InnoDB存储引擎页的大小为16KB，即一个区中一共有64个连续的页。

2.1.4 页

同大多数数据库一样，InnoDB有页（Page）的概念（也可以称为块），页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。而从InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库。

常见的页类型有：

数据页（B-tree Node）
undo页（undo Log Page）
系统页（System Page）
事务数据页（Transaction system Page）
插入缓冲位图页（Insert Buffer Bitmap）
插入缓冲空闲列表页（Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page）
压缩的二进制大对象页（compressed BLOB Page）

InnoDB 从表中获取某些记录所采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

2.1.5 行

与现有的大多数存储引擎一样，InnoDB 使用页作为磁盘管理的最小单位；数据在 InnoDB 存储引擎中都是按行存储的，每个 16KB 大小的页中可以存放 2-200 行的记录。

当 InnoDB 存储数据时，它可以使用不同的行格式进行存储；

MySQL 5.7 版本支持以下格式的行存储方式：

Antelope-Barracuda-Row-Format

当前熟知的类型为：Compact、Redundant、Dynamic 和 Compressed 行格式。
Antelope 是 InnoDB 最开始支持的文件格式；Antelope 的名字是在新的文件格式 Barracuda 出现后才起的。
InnoDB 对于文件格式都会向前兼容。
Compact 和 Redundant 在磁盘上按照以下方式存储：
Compact 和 Redundant存储结构

Compact和 Redundant格式最大的不同就是记录格式的第一个部分：在Compact中，行记录的第一部分倒序存放了一行数据中列的长度（Length），而Redundant中存的是每一列的偏移量（Offset），从总体上上看，Compact行记录格式相比Redundant格式能够减少20%的存储空间。
在Compact和Redundant行格式中，对于占用存储空间非常大的列(如极长的VARCHAR或者BLOB这类大对象)，在记录的真实数据处只会存储该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用20个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。
在MySQL 5.1版本中，默认设置为Compact行格式。5.7现在默认是Dynamic。
通过SHOW TABLE STATUS LIKE 'table_name'; 查看当前表锁使用的行格式。
3 InnoDB数据页结构
一个 InnoDB 页有以下七个部分：

File Header：文件头部，38 字节，页的一些通用信息
Page Header：页面头部，56 字节，数据页专有的一些信息
Infimum + Supremum：最小记录和最大记录，26 字节，两个虚拟的行记录
User Records：用户记录，大小不确定，实际存储的行记录内容
Free Space：空闲空间，大小不确定，页中尚未使用的空间
Page Directory：页面目录，大小不确定，页中的某些记录的相对位置
File Trailer：文件尾部，8 字节，校验页是否完整
InnoDB-B-Tree-Node
每一个页中包含了两对 header/trailer：内部的 Page Header/Page Directory 关心的是页的状态信息，而 File Header/File Trailer 关心的是记录页的头信息。
在页的头部和尾部之间就是用户记录和空闲空间了，每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录（可以理解为占位符），Infimum 记录是比该页中任何主键值都要小的值，Supremum 是该页中的最大值。
User Records 就是整个页面中真正用于存放行记录的部分，而 Free Space 就是空余空间了，它是一个链表的数据结构，为了保证插入和删除的效率，整个页面并不会按照主键顺序对所有记录进行排序，它会自动从左侧向右寻找空白节点进行插入，行记录在物理存储上并不是按照顺序的，它们之间的顺序是由 next_record 这一指针控制的。

4 Buffer Pool

4.1 作用

存放缓存和配置，加速读和写。

加速读：当需要访问一个数据页(page)的时候，如果这个page已经在Buffer Pool中，那么就不再需要访问磁盘，直接从Buffer Pool中获取。
加速读原理
加速写：当需要修改一个page的时候，先将这个page在缓冲池中进行修改，记下相关的redo log，这个页面的修改就算已经完成了。至于这个被修改的dirty page（脏页）什么时候真正刷新到磁盘，后面会详细讲到。
加速写原理

4.2 总体架构

Buffer Pool整体架构图

4.3 Buffer Pool 内部组成

Buffer Pool 中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是 16KB。为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在 Buffer Pool 中的地址、链表节点信息、一些锁信息以及 LSN 信息，以及一些别的控制信息。

每个缓存页对应的控制信息占用的内存大小是相同的，我们称为控制块。

控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边，所以整个 Buffer Pool 对应的内存空间看起来就是这样的：

Buffer Pool内存结构图

4.4 管理机制

free page：空闲page，未被使用
clean page：被使用page，但没有被修改
dirty page：脏页，被使用、被修改、与磁盘不一致

用三种链表结构来管理这三个page：

freelist：管理free page。
freelist的效果图
flushlist：管理dirty page，按修改时间排序，最早在后，先更新磁盘，寄存在lrulist中，但互不影响。效果图类似freelist。
lrulist：管理正在使用的 clean page 和 dirty page，以midpoint为中点，左面为new列表，存放经常访问的数据，占63%；右面为old，存放不用的，要被清掉的，占37%。

4.5 采用改进型LRU算法维护

普通LRU：末尾淘汰法，新数据进入头，释放空间从末尾释放。

改进LRU：链表分为new和old，添加不是从头插入，而是从中点插入，数据被访问了，那么就从中点开始左移，如果没怎么被访问，那么就会被挤压到中点右面，等待被释放。

  > 每当有新的page数据放入缓存池，InnoDB先判断有没有足够的`free page`，如果有就删除`free list`中的数据，然后放到LRU，如果没有了，那么LRU末尾就会开始释放，够了再插进来。

InnoDB采用改进的LRU算法(后面刷LeetCode LRU时可以看看算法流程和代码)，将链表分为两个区域，一部分使用频率高的在链表的头部称为young区，一部分使用频率低的在链表的尾部称为old区。主要示意图如下：

Buffer Pool List

4.6 配置参数

show variables like '%innodb_page_size%'; -- page大小
show variables like '%innodb_old%';  -- lru old的参数
show variables like '%innodb_buffer%'; -- 查看所有参数

缓存池的存储模块嵌套关系: buffer->instance->chunk ->page。
buffer推荐占物理内存的60%-80%，instance按需增加数量， page微量调整。

4.7 多个 Buffer Pool 实例

Buffer Pool 本质是 InnoDB 向操作系统申请的一块连续的内存空间，在多线程环境下，访问 Buffer Pool 中的各种链表都需要加锁处理，在 Buffer Pool 特别大而且多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响请求的处理速度。所以在 Buffer Pool 特别大的时候，我们可以把它们拆分成若干个小的 Buffer Pool，每个 Buffer Pool 都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

通过设置 innodb_buffer_pool_instances 的值来修改 Buffer Pool 实例的个数，那每个 Buffer Pool 实例实际占多少内存空间呢?

公式：

innodb_buffer_pool_size/innodb_buffer_pool_instances

也就是总共的大小除以实例个数，结果就是每个 Buffer Pool 实例占用的大小。

不过也不是说 Buffer Pool 实例创建的越多越好，分别管理各个 Buffer Pool 也是需要性能开销的，InnoDB 规定：

当 innodb_buffer_pool_size(默认 128M) 的值小于1G 的时候设置多个实例是无效的，InnoDB 会默认把 innodb_buffer_pool_instances的值修改为 1。所以 Buffer Pool 大于或等于 1G 的时候应该设置多个 Buffer Pool 实例。

4.8 innodb_buffer_pool_chunk_size

在 MySQL 5.7.5 之前，Buffer Pool的大小只能在服务器启动时通过配置innodb_buffer_pool_size启动参数来调整大小，在服务器运行过程中是不允许调整该值的。不过在MySQL 5.7.5以及之后的版本中支持了在服务器运行过程中调整 Buffer Pool 大小的功能。

但是有一个问题，就是每次当我们要重新调整 Buffer Pool 大小时，都需要重新向操作系统申请一块连续的内存空间，然后将旧的 Buffer Pool 中的内容复制到这一块新空间，这是极其耗时的。所以 MySQL 决定不再一次性为某个 Buffer Pool 实例向操作系统申请一大片连续的内存空间，而是以一个所谓的 chunk 为单位向操作系统申请空间。

也就是说，一个Buffer Pool实例其实是由若干个chunk组成的，一个 chunk 就代表一片连续的内存空间，里边包含了若干缓存页及其对应的控制块：

Chunk-in-Pool

4.9 Change Bufferi

负责存储新增和更新的缓存，避免频繁的磁盘操作，先对比缓存池中已有数据，然后更新的数据放这，闲置时再更新入磁盘。

占用Buffer Pool空间默认25%，最大允许50%，根据写入量进行调整。

show variables like '%innodb_change_buffer_max_size%'; -- 查看当前cb占比
set global innodb_change_buffer_max_size = `占比数`;

Change Buffer

4.10 Log Buffer

负责存储操作记录
存储一定数量更新到磁盘
也可定期更新到磁盘
容量大小通过参数innodb_log_buffer_size控制
刷盘策略：通过参数innodb_flush_log_at_trx_commit控制。其中：
- 0：每隔1秒进行写日志和刷盘操作，流程: log buffer-> OScache ->DISK；该模式下在事务提交时不会主动触发写入磁盘的操作。
- 1：事务提交就刷盘；
- 2：事物提交，就写日志（意味着写入到了操作系统的文件缓存），每隔一秒刷盘。

借用小林子的图可以更好地加深理解：

刷盘机制

innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？

InnoDB 的后台线程每隔 1 秒：

针对参数 0：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
针对参数 2：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。