“InnoDB：InnoDB 磁盘结构”的版本间差异

来自Wikioe
跳到导航 跳到搜索
第179行： 第179行：




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


=== 更改 InnoDB 重做日志文件的数量或大小 ===
=== 更改 InnoDB 重做日志文件的数量或大小 ===
要更改 InnoDB redo log 文件的数量或大小，请执行以下步骤：
要更改 InnoDB redo log 文件的数量或大小，请执行以下步骤：
# 停止 MySQL 服务器，并确保它关闭且没有错误。
# 停止 MySQL 服务器，并确保它关闭且没有错误。

2021年4月20日 (二) 00:33的版本


Tables

Indexes

Tablespaces

InnoDB Data Dictionary（数据字典）

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

  • 元数据实际上位于 InnoDB 系统表空间中。
  • 由于历史原因，数据字典元数据在某种程度上与 InnoDB 表元数据文件(“.frm”文件)中存储的信息重叠。
  1. MySQL 8.0 之前：
    MySQL Data Dictionary before MySQL8.0.png
    Data Dictionary 除了存在于 InnoDB 系统表空间，还存在于文件系统的“.FRM”，“.TRG”，“.OPT”⽂件中，还存在于系统表中（基于 MyISAM 存储引擎的⾮事务引擎表中）。【而且在一定程度上彼此的信息是重叠的】
  2. MySQL 8.0：
    Transactional Data Dictionary in MySQL8.0.png
    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 在将页面写入 InnoDB 数据文件中的适当位置之前，先写入从缓冲池刷新的页面。如果在页面写入过程中发生 os，存储子系统或 mysqld 进程崩溃，则 InnoDB 可以在崩溃恢复期间从 doublewrite 缓冲区中找到该页面的良好副本。

InnoDB DoubleBuffer图示.png


尽管数据被写入两次，但双写缓冲区不需要两倍的 I/O 开销或两倍的 I/O 操作。只需对 os 进行一次“fsync()”调用，就可以将数据按较大的 Sequences 块写入 Doublewrite 缓冲区（除非将“innodb_flush_method”设置为“O_DIRECT_NO_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 的若干问题：

  1. Doublewrite解决了什么问题？
    为了解决部分页面写入问题（Partial Page Write）。
    MySQL写入修改时刷新整个页面（默认16kb），而不仅仅是刷新页面中已更改的记录。而系统的单次io，一般是512byte为单位的，在断电，OS crash（操作系统崩溃）情况下可能会丢失数据。
  2. Doublewrite是指哪两次写入？
    1. 写Doublewrite buffer。【注意: Doublewrite buffer是磁盘不是内存】
    2. 写入数据文件。
    • 写入顺序：先写doublewrite buffer,写成功后再写到数据文件。
  3. Doublewrite buffer存储区位于什么地方？
    1. MySQL 8.0.20 之前：位于 InnoDB 系统表空间中（“ibdata”文件）。
    2. MySQL 8.0.20 开始：位于doublewrite文件中,文件由“innodb_doublewrite_dir”和“innodb_doublewrite_files”配置确定。
  4. innodb 什么时候将脏页写入Doublewrite buffer中？
    由以下几个参数决定：
    • “innodb_max_dirty_pages_pct_lwm”:低位水平标记，达到该值将启动缓冲刷新,默认为10（百分比，脏页/缓冲池）。
    • “innodb_max_dirty_pages_pct”: 脏页数量与缓冲池比例阈值，默认为90。
    • 如果开启“自适应刷新”（Adaptive Flushing）InnoDB 根据重做日志生成的速度和当前的刷新率，使用自适应刷新算法来动态调整刷新率。
  5. 如何查看双写缓冲区的大小？
    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 ()
      双写缓冲区范围：从第 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”。
  6. 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数 ）控制。
  7. 如何查看 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，这两个变量会重置】
  8. 如何通过 doublewrite buffer 恢复？恢复逻辑是什么样的？
    1. 恢复前，检查数据文件中页面的校验和是否与 doublewirte buffer 中页面的校验和不同，从而知道该页面是否已损坏。
    2. 恢复时，innodb 会在其原始位置检查 doublewirte buffer 的是否一致，如果不一致，则直接丢弃。【？？？】
    3. 如果是表空间不一致，则从双写缓冲区恢复。【？？？】
  9. double write 一定需要吗？
    生产环境建议开启。可以通过“my.cnf”中配置关闭“innodb_doublewrite=0”，或启动服务时加上参数关闭“--skip-innodb-doublewrite”。
  10. 为什么有了 redo log，还需要 doublewrite buffer？
    redo log 是对数据页进行操作的，如果这个数据页已经损坏，则没法通过redo log来进行恢复。
  11. Double write对性能的影响？
    • 通常情况下只会降低 5% 的性能。
    • 负载较大的情况下，特别是大量需要读取的数据不在 buffer pool 缓冲区中，Doublewrite 的写入与随机读取之间竞争访问磁盘，性能降低 30% 以上都是可能的。
    • 如果Doublewrite buffer位于支持原子写入的 Fusion-io 设备上，则会自动禁用doublewrite缓冲区。
  12. Doublewrite buffer 在 HDD 和 SSD 磁盘写入有区别吗？
    Doublewrite buffer 和 redo log 都是顺序写，如果启用了写入缓存，HDD更快（HDD擅长顺序写，SSD则不是）。
  13. Doublewrite buffer 与 Buffer Pool（缓冲池）、redo log（重做日志）的联动过程？
    如下图：
    InnoDB DoubleBuffer的使用过程.png

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
取自“http://wiki.eijux.com/index.php?title=InnoDB：InnoDB_磁盘结构&oldid=2430”