InnoDB：InnoDB 磁盘结构

Tables

• 见：“InnoDB：InnoDB 磁盘结构：Tables”

Indexes

• 见：“InnoDB：InnoDB 磁盘结构：Indexes”

Tablespaces

• 见：“InnoDB：InnoDB 磁盘结构：Tablespaces”

InnoDB Data Dictionary（数据字典）

InnoDB 数据字典由内部系统表组成，这些系统表包含用于跟踪对象（例如 表，索引和列）的元数据。

• 元数据实际上位于 InnoDB 系统表空间中。
• 由于历史原因，数据字典元数据在某种程度上与 InnoDB 表元数据文件(“.frm”文件)中存储的信息重叠。

1. MySQL 8.0 之前：

   

   Data Dictionary 除了存在于 InnoDB 系统表空间，还存在于文件系统的“.FRM”，“.TRG”，“.OPT”⽂件中，还存在于系统表中（基于 MyISAM 存储引擎的⾮事务引擎表中）。【而且在一定程度上彼此的信息是重叠的】

2. MySQL 8.0：

   

   Data Dictionary 全部存在于“Data Dictionary Storage Engine”（即 InnoDB 表中）

数据字典（InnoDB系统表）

InnoDB 中，实际上看不到系统表。有4个最基本的系统表来存储表的元数据：表、列、索引、索引列等信息。这4个表分别是“SYS_TABLES”、“SYS_COLUMNS”、“SYS_INDEXES”、“SYS_FIELDS”：

1. “SYS_TABLES”：

   存储所有以 InnoDB 为存储引擎的表，每条记录对应一个表。该表的列分别是：

   NAME：表名
   
   ID：表的ID号
   
   N_COLS：表的列数
   
   TYPE：表的存储类型，包括记录的格式、压缩等信息
   
   MIX_ID、MIX_LEN、CLUSTER_NAME：暂时未用
   
   SPACE：这个表所在的表空间ID。
   

   这个表在 NAME 上有聚集索引，ID上有唯一二级索引。

2. “SYS_COLUMNS”：

   存储列信息，每一列对应一条记录。该表的列分别是：

   TABLE_ID：该列所属表的ID
   
   POS：该列在表中第几列
   
   NAME：列名
   
   MTYPE：列的主数据类型
   
   PRTYPE：列的精确数据类型
   
   LEN：列数据长度，不包括 varchar 类型，因为该类型在记录里面已经存储了
   
   PREC：列数据的精度。
   

   该表的主键列是（TABLE_ID,POS）。

3. “SYS_INDEXES”：

   存储索引信息，每条记录对应一个索引。该表的列分别是：

   TABLE_ID：该列所属表的ID
   
   ID：索引的索引号
   
   NAME：索引名
   
   N_FIELDS：索引包含的列数
   
   TYPE：索引类型，包括聚集索引、唯一索引、DICT_UNIVERSAL、DICT_IBUF
   
   SPACE：索引所在表的表空间ID
   
   PAGE_NO：该索引对应的 B+ 树的根页面号。
   

   该表主键（TABLE_ID,ID）。

4. “SYS_FIELDS”：

   存储定义的索引列，每条记录对应一个索引列。该表的列分别是：

   INDEX_ID：该列所在的索引ID
   
   POS：该列在索引中第几列
   
   COL_NAME：列名
   

   该表主键是（INDEX_ID,POS）。

Doublewrite Buffer（双写缓冲区）

doublewrite 缓冲区是一个存储区域：在写到数据文件的适当位置之前，InnoDB 会将“从缓冲池刷新的页面”先写入 Doublewrite Buffer。

• 如果在页面写入过程中发生 os，存储子系统或 mysqld 进程崩溃，则 InnoDB 可以在崩溃恢复期间从 Doublewrite Buffer 中找到该页面的良好副本。
• 尽管数据被写入两次，但双写缓冲区不需要两倍的 I/O 开销或两倍的 I/O 操作。只需对 os 进行一次“fsync()”调用，就可以将数据按较大的顺序块写入 Doublewrite Buffer（除非将“innodb_flush_method”设置为“O_DIRECT_NO_FSYNC”）。

简单地说：脏页刷到磁盘过程中，先由“fsync()”调用写入双写缓冲区（一次写），再将脏页从缓冲池写入磁盘的对应位置（二次写）。

在大多数情况下，默认情况下会启用双写缓冲区。要禁用双写缓冲区，请将“innodb_doublewrite”设置为 0。

如果系统表空间文件（“ibdata文件”）位于支持原子写的 Fusion-io 设备上，则会自动禁用双写缓冲，并且将 Fusion-io 原子写用于所有数据文件。由于 doublewrite 缓冲区设置是全局的，因此，对于非 Fusion-io 硬件上驻留的数据文件，也禁用 doublewrite 缓冲。此功能仅在 Fusion-io 硬件上受支持，并且仅在 Linux 上的 Fusion-io NVMFS 中启用。【？？？】

• 要充分利用此功能，建议将“innodb_flush_method”设置为“O_DIRECT”。

Doublewrite Buffer 与 redo log

部分写失效（partial page write）：因为 InnoDB 的数据页一般是 16K，但是磁盘的页一般是 4K，所以写一次磁盘数据，会有 4 次写磁盘的原子操作。在极端情况下就可能在磁盘写完前面 8K 后系统断点，前面 8K 是新数据，后面的 8K 是老数据，出现了数据被破坏的情况，这就叫做“部分写失效”（或“页断裂”？）。

• 那么磁盘数据库这个数据页就是不完整的，是一个坏掉的数据页。

redo log：即重做日志，是“物理日志”，它存储的是特定记录的变更。【一说是“Physiological Logging”日志，见：“逻辑日志与物理日志”】

• redo log 是对数据页（16k）进行操作的，如果这个数据页已经损坏（页断裂），则没法通过 redo log 来进行恢复。
• redo log 只能加上旧、校检完整的数据页恢复一个脏块，不能修复坏掉的数据页。

Q&A：

1. 为什么有了 redo log，还需要 Doublewrite Buffer？

   如上所述，redo log 无法解决“页断裂”，所以需要“DoubleWrite”（使用“Doublewrite Buffer”）。

2. Doublewrite Buffer 、 Buffer Pool（缓冲池）、redo log（重做日志）的联动过程？

   如下图：【？？？？？？？？？？？？这他妈不对吧？？？？？？？？？？？？】

   

与 Doublewrite Buffer 相关的若干问题：

Doublewrite Buffer

1. 如何通过 Doublewrite Buffer 恢复？恢复逻辑是什么样的？
   1. 恢复前，检查数据文件中页面的校验和是否与 Doublewrite Buffer 中页面的校验和不同，从而知道该页面是否已损坏。
   2. 恢复时，InnoDB 会在其原始位置检查 Doublewrite Buffer 的是否一致，如果不一致，则直接丢弃。【？？？】
   3. 如果是表空间不一致，则从双写缓冲区恢复。【？？？】
2. Doublewrite Buffer 存储区位于什么地方？
   1. MySQL 8.0.20 之前：位于 InnoDB 系统表空间中（“ibdata”文件）。
   2. MySQL 8.0.20 开始：位于doublewrite文件中,文件由“innodb_doublewrite_dir”和“innodb_doublewrite_files”配置确定。
3. InnoDB 什么时候将脏页写入 Doublewrite Buffer 中？

   由以下几个参数决定：

   • “innodb_max_dirty_pages_pct_lwm”:低位水平标记，达到该值将启动缓冲刷新,默认为10（百分比，脏页/缓冲池）。
   • “innodb_max_dirty_pages_pct”: 脏页数量与缓冲池比例阈值，默认为90。
   • 如果开启“自适应刷新”（Adaptive Flushing）InnoDB 根据重做日志生成的速度和当前的刷新率，使用自适应刷新算法来动态调整刷新率。
4. Doublewrite Buffer 从脏页刷新速度由哪些参数控制？【？？？】
   1. 清洗线程数由“innodb_page_cleaners”配置（8.0.20版本默认为4）。
   2. 单个线程最多每次写入 Doublewrite Buffer 的页面数由“innodb_doublewrite_pages”变量控制，在默认情况下为“innodb_write_io_threads”的值（默认4）
   3. 每秒写入次数还受到“innodb_io_capacity”（定义了每秒I / O操作数,默认值200）和“innodb_io_capacity_max”（InnoDB在由后台任务每秒执行的最大IOPS数 ）控制。
5. 如何查看 Doublewrite Buffer 已写入情况？

   mysql> show global status like '%dblwr%';
   +----------------------------+-------+
   | Variable_name              | Value |
   +----------------------------+-------+
   | Innodb_dblwr_pages_written | 103   |
   | Innodb_dblwr_writes        | 30    |
   +----------------------------+-------+
   2 rows in set (0.05 sec)
   

   1. “Innodb_dblwr_pages_written”：从 mysql 启动以来，已经写入 Doublewrite Buffer 的页面数。
   2. “Innodb_dblwr_writes”：执行写入 Doublewrite Buffer 的次数（脏页刷新到 doublewriete buffer 的次数）。
   • 【重启mysql，这两个变量会重置】
6. 如何查看 Doublewrite Buffer 的大小？
   1. MySQL 8.0.20 之前：Doublewrite Buffer 是系统表空间中连续的128个页（每个页16k），总共2M。

      $ innodb_space -f ibdata1 space-inodes-detail 
      
      INODE fseg_id=7, pages=160, frag=32 pages (9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40), full=2 extents (64-127, 128-191), not_full=0 extents () (0/0 pages used), free=0 extents ()
      

      Doublewrite Buffer 范围：从第 64-191 page，分为两个 block，每个64 page。

   2. MySQL 8.0.20 开始：Doublewrite Buffer 由单独文件保存。

      $ ls -lh /usr/local/mysql-8.0.20-macos10.15-x86_64/data/#ib_16384_*
      
      -rw-r-----  1 _mysql  _mysql   192K  6 30 18:09 /usr/local/mysql-8.0.20-macos10.15-x86_64/data/#ib_16384_0.dblwr
      -rw-r-----  1 _mysql  _mysql   8.2M  6 30 18:08 /usr/local/mysql-8.0.20-macos10.15-x86_64/data/#ib_16384_1.dblwr
      

      • DoublieWrite 文件格式：“#_页面大小_文件编号.dblwr”。
7. Doublewrite Buffer 在 HDD 和 SSD 磁盘写入有区别吗？

   Doublewrite Buffer 和 redo log 都是顺序写，如果启用了写入缓存，HDD 更快（HDD擅长顺序写，SSD则不是）。

DoubleWrite

1. DoubleWrite 解决了什么问题？

   为了解决“部分写失效”（Partial Page Write）的问题。

   MySQL 写入修改时刷新整个页面（默认16kb），而不仅仅是刷新页面中已更改的记录。而系统的单次I/O，一般是 512byte 为单位的，在断电，OS crash（操作系统崩溃）情况下可能会丢失数据。

2. DoubleWrite 是指哪两次写入？
   1. 写 Doublewrite Buffer。【注意: Doublewrite Buffer 是磁盘不是内存】
   2. 写入数据文件。
   • 写入顺序：先写 Doublewrite Buffer，写成功后再写到数据文件。
3. DoubleWrite 一定需要吗？

   生产环境建议开启。可以通过“my.cnf”中配置关闭“innodb_doublewrite=0”，或启动服务时加上参数关闭“--skip-innodb-doublewrite”。

4. DoubleWrite 对性能的影响？
   • 通常情况下只会降低 5% 的性能。
   • 负载较大的情况下，特别是大量需要读取的数据不在 Buffer Pool（缓冲区）中，DoubleWrite 的写入与随机读取之间竞争访问磁盘，性能降低 30% 以上都是可能的。
   • 如果 Doublewrite Buffer 位于支持原子写入的 Fusion-io 设备上，则会自动禁用 DoubleWrite 缓冲区。

Redo Log（重做日志）【？？？】

重做日志是基于磁盘的数据结构，在崩溃恢复期间用于纠正不完整事务写入的数据。在正常操作期间，重做日志对更改表数据的请求进行编码，这些请求是由 SQL 语句或低级 API 调用引起的。在初始化期间以及接受连接之前，会自动重播未完成意外关闭之前未完成更新数据文件的修改。

默认情况下，重做日志在磁盘上由名为“ib_logfile0”和“ib_logfile1”的两个文件物理表示。 MySQL 以循环方式写入重做日志文件。重做日志中的数据根据受影响的记录进行编码；此数据统称为重做。数据通过重做日志的传递由不断增加的“LSN”（页的最后修改的64位日志序列号）值表示。

更改 InnoDB 重做日志文件的数量或大小

要更改 InnoDB redo log 文件的数量或大小，请执行以下步骤：

1. 停止 MySQL 服务器，并确保它关闭且没有错误。
2. 编辑“my.cnf”以更改日志文件配置。要更改日志文件的大小，请配置“innodb_log_file_size”。要增加日志文件的数量，请配置“innodb_log_files_in_group”。
3. 再次启动 MySQL 服务器。

如果 InnoDB 检测到“innodb_log_file_size”与重做日志文件大小不同，则会写入日志检查点，关闭并删除旧的日志文件，以请求的大小创建新的日志文件，然后打开新的日志文件。

组提交以重做日志刷新

InnoDB 像其他任何符合 ACID 的数据库引擎一样，在提交事务之前先刷新该事务的 redo log。 InnoDB 使用 group commit 功能将多个此类刷新请求分组在一起，以避免每次提交都进行一次刷新。使用组提交，InnoDB 向日志文件发出一次写入操作，以对大约同时提交的多个用户事务执行提交操作，从而显着提高了吞吐量。

Undo Logs（撤消日志）【？？？】

撤消日志是与单个读写事务关联的撤消日志记录的集合。撤消日志记录包含有关如何撤消事务对聚集索引记录的最新更改的信息。如果另一个事务需要将原始数据视为一致读取操作的一部分，则将从撤消日志 Logging 检索未修改的数据。撤消日志存在于撤消日志段中，包含在rollback segments中。回滚段位于 system tablespace，undo tablespaces 和 temporary tablespace 中。

• 驻留在临时表空间中的撤消日志用于修改用户定义的临时表中的数据的事务。这些撤消日志未重做记录，因为崩溃恢复不需要它们。它们仅在服务器运行时用于回滚。这种类型的撤消日志通过避免重做日志 I/O 来提高性能。

InnoDB 支持最多 128 个回滚段，其中 32 个回滚段分配给了临时表空间。剩下的 96 个回滚段可以分配给修改常规表中数据的事务。

• “innodb_rollback_segments”变量定义 InnoDB 使用的回滚段数。

回滚段支持的事务数取决于回滚段中的撤消插槽数和每个事务所需的撤消日志数。

回退段中撤消插槽的数量根据InnoDB页的大小而有所不同。

InnoDB 页面大小	回滚段中的撤消插槽数（InnoDB 页面大小/ 16）
4096 (4KB)	256
8192 (8KB)	512
16384 (16KB)	1024
32768 (32KB)	2048
65536 (64KB)	4096

一个事务最多可以分配四个撤消日志，以下每种操作类型都可以分配一个：

1. “INSERT”对用户定义表的操作
2. 用户定义的表上的“UPDATE”和“DELETE”操作
3. “INSERT”对用户定义的临时表的操作
4. 用户定义的临时表上的“UPDATE”和“DELETE”操作

撤消日志根据需要分配。例如，对常规表和临时表执行“INSERT”，“UPDATE”和“DELETE”操作的事务需要完全分配四个撤消日志。仅对常规表执行“INSERT”操作的事务需要单个撤消日志。

从已分配的系统表空间或撤消表空间回滚段中分配了对常规表执行操作的事务的撤消日志。从指定的临时表空间回滚段中分配了对临时表执行操作的事务的撤消日志。

分配给事务的撤消日志在其持续时间内一直与事务相关。例如，为常规表上的“INSERT”操作分配给事务的撤消日志将用于该事务对常规表上的所有“INSERT”操作。

给定上述因素，以下公式可用于估计 InnoDB 能够支持的并发读写事务数：

• 事务在达到 InnoDB 能够支持的并发读写事务数之前可能会遇到并发事务限制错误。当分配给事务的回滚段用尽撤消插槽时，就会发生这种情况。在这种情况下，请尝试重新运行事务。
• 当事务对临时表执行操作时，InnoDB能够支持的并发读写事务数受分配给临时表空间的回滚段数（即 32）所限制。

1. 如果每个事务执行“INSERT”或“UPDATE”或“DELETE”操作，则 InnoDB 能够支持的并发读写事务数为：

   (innodb_page_size / 16) * (innodb_rollback_segments - 32)
   

2. 如果每个事务执行“INSERT”和“UPDATE”或“DELETE”操作，则 InnoDB 能够支持的并发读写事务数为：

   (innodb_page_size / 16 / 2) * (innodb_rollback_segments - 32)
   

3. 如果每个事务在临时表上执行“INSERT”操作，则 InnoDB 能够支持的并发读写事务数为：

   (innodb_page_size / 16) * 32
   

4. 如果每个事务在临时表上执行“INSERT”和“UPDATE”或“DELETE”操作，则 InnoDB 能够支持的并发读写事务数为：

   (innodb_page_size / 16 / 2) * 32