数据库导航一些收藏

存储引擎 - InnoDB

2022-05-03  本文已影响0人  Zeppelin421

存储结构

5.7-引擎架构图

内存结构

内存结构主要包括 Buffer Pool、Change Buffer、Adaptive Hash Index和 Log Buffer 四大组件

Buffer Pool

缓冲池，简称BP。BP以 Page 页为单位，默认大小 16 K。BP的底层采用链表数据结构管理 Page。
在 InnnoDB 访问表记录和索引是会在 Page 页中缓存，以后使用可以减少磁盘IO操作，提升效率。

Page管理机制

Page根据状态可以分为三种类型：

针对上述三种 Page 类型，InnoDB通过三种链表结构来维护和管理

mysql> show engine innodb status \G;
改进型 LRU 算法维护

每当有新的 page 数据读取到 buffer pool时，InnoDB 引擎会判断是否有足够空闲页，如果有，就将 free page 从 free list 列表删除，放入到 LRU 列表中。没有空闲页，就会根据 LRU算法淘汰 LRU 链表末尾的页，将内存空间释放分配给新的页

配置参数
mysql> show variables like '%innodb_page_size%';
mysql> select @@innodb_page_size;
+--------------------------------+-----------+
|    Variable_name               |  Value    |
+ -------------------------------+-----------+
|    innodb_page_size            |  16384    |
+ -------------------------------+-----------+

mysql> show variables like '%innodb_old%';
+--------------------------------+-----------+
|    Variable_name               |  Value    |
+ -------------------------------+-----------+
|    innodb_old_blocks_pct       |  37       |
|    innodb_old_blocks_time      |  1000     |
+ -------------------------------+-----------+

mysql> show variables like '%innodb_buffer%';
+-------------------------------------------------+-------------------+
|    Variable_name                                |  Value            |
+ ------------------------------------------------+-------------------+
|    innodb_buffer_pool_chunk_size                |  8388608          |
|    innodb_buffer_pool_dump_at_shutdownsize      |  ON               |
|    innodb_buffer_pool_dump_now                  |  OFF              |
|    innodb_buffer_pool_dump_pct                  |  25               |
|    innodb_buffer_pool_filename                  |  ib_buffer_pool   |
|    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                      |  8388608          |
+ ------------------------------------------------+-------------------+

TIPS：将 innodb_buffer_pool_size 设置为总内存大小的 60% ~ 80%，innodb_buffer_pool_instances 可以设置为多个，这样可以避免缓存争夺

Change Buffer

写缓冲区，简称CB。在进行 DML 操作是，如果 BP 没有其相应的 Page 数据，并不会立刻将磁盘页加载到缓冲池，而是在 CB 记录缓冲变更，等未来数据被读取时，再将数据合并恢复到 BP 中。

ChangeBuffer占用 Buffer Pool 空间，默认占 25%，最大允许占 50%，可以根据读写业务量来进行调整。

mysql> show variable like '%innodb_change_buffer_max_size%'
+-------------------------------------------------+-------------------+
|    Variable_name                                |  Value            |
+ ------------------------------------------------+-------------------+
|    innodb_change_buffer_max_size                |  25               |
+ ------------------------------------------------+-------------------+

mysql> set global innodb_change_buffer_max_size = 20;

当更新一条记录时，该记录在 BufferPool 存在，直接在 BufferPool 修改，一次内存操作；如不存在（没有命中），会直接在 ChangeBuffer 进行一次内存操作（不用再去磁盘查询，避免一次磁盘IO），当下次查询记录是，会先进行磁盘读取，然后再从 ChangeBuffer 中读取信息合并，最终载入 BufferPool 中

写缓冲区仅适用于非唯一普通索引页。因为索引设置唯一性后，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘做一次IO操作。此时会直接将记录查询到 BufferPool 中，然后在缓冲池修改，因此不会再在 ChangeBuffer 中操作

Adaptive Hash Index

自适应哈希索引，用于优化对 BP 数据的查询。InnoDB 存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB 存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

Log Buffer

日志缓冲区，用来保存要写入磁盘 log 文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘 log 文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到 BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O

LogBuffer主要是用于记录 InnoDB引擎日志，在 DML 操作时会产生 Redo 和 Undo 日志

LogBuffer空间满时会自动写入磁盘，可以通过将 innodb_log_buffer_size 参数调大，减少磁盘I/O频率
innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1

磁盘结构

InnoDB磁盘主要包含 Tablespaces、InnoDB Data Dictionary、Doublewrite Buffer、Redo Log 和 Undo Logs

Tablespaces

表空间，用于存储表结构和数据。

mysql> show variable like '%innodb_data_file_path%'
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_data_file_path                |  ibdata1:12M:autoextend |
+ ----------------------------------------+-------------------------+

mysql> show variable like '%innodb_file_per_table%'
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_file_per_table                |  ON                     |
+ ----------------------------------------+-------------------------+

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB;   // 创建表空间ts1
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;   // 将表添加到ts1表空间

InnoDB Data Dictionary

数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠

Doublewrite Buffer

双写缓冲区位于系统表空间，是一个存储区域。在 BufferPage 的 page 页刷新到磁盘真正的位置前，会先将数据存在 Doublewrite 缓冲区。如果在 page 页写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃，InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到页面的一个好备份。默认启用双写缓冲区。

mysql> show variable like '%innodb_doublewrite%'
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_doublewrite                   |  ON                     |
+ ----------------------------------------+-------------------------+

MySQL 的 innodb_flush_method 这个参数控制着 InnoDB 数据文件及 redo log 的打开、刷写模式。

使用 Doublewrite 缓冲区时建议将 innodb_flush_method 设置为 O_DIRECT

Redo Log

重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL 以循环方式写入重做日志文件，记录 innodb 中所有对 Buffer Pool 修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生 redo log。默认情况下，重做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1 的文件物理表示

Undo Logs

撤销日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤销日志属于逻辑日志，根据每行记录进行记录。撤销日志存在于系统表空间、撤销表空间和临时表空间中

新版本结构演变

8.0-引擎架构图
MySQL 5.7版本
MySQL 8.0版本

线程模型

IO Thread

在 InnoDB 中使用了大量的AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能。在 InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log thread，后来版本将 read 和 write thread分别增大到4个，一共有10个了

mysql> show engine innodb  status \G;
---------------------
FILE I/O
---------------------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)

Purge Thread

事务提交之后，其使用的 undo 日志将不再需要，因此需要 purge thread 回收已经分配的 undo 页

mysql> show variables like '%innodb_purge_thread%';
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_purge_thread                  |  4                      |
+ ----------------------------------------+-------------------------+

Page Cleaner Thread

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能达到 redo log 循环使用的目的。会调用 write thread 线程处理

mysql> show variables like '%innodb_page_cleaners%';
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_page_cleaners                 |  1                      |
+ ----------------------------------------+-------------------------+

Master Thread

Master Thread 是 InnoDB 的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包括：脏页的刷新（page cleaner threa）、undo 页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等。
内部有两个主处理：

mysql> show variables like '%innodb_max_dirty_pages_pct%';
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_max_dirty_pages_pct           |  75.000000              |
|    innodb_max_dirty_pages_pct_lwm       |  0.000000               |
+ ----------------------------------------+-------------------------+
mysql> show variables like '%innodb_io_capacity%';
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_io_capacity                   |  200                    |
|    innodb_io_capacity_max               |  2000                   |
+ ----------------------------------------+-------------------------+

数据文件

文件存储结构

文件存储格式

File-Format

在早期的 InnoDB 版本中，文件格式只有一种，随着 InnoDB 引擎的发展，出现了新文件格式，用于支持新的功能。

mysql> show variables like '%innodb_file_format%';
+-----------------------------------------+-------------------------+
|    Variable_name                        |  Value                  |
+ ----------------------------------------+-------------------------+
|    innodb_file_format                   |  Antelope               |
|    innodb_file_format_check             |  ON                     |
|    innodb_file_format_max               |  Antelope               |
+ ----------------------------------------+-------------------------+
Row-Format

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和 DML 操作的性能。如果在单个 Page 页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少

每个表的数据分成若干页来存储，每个页中采用B数结构存储。如果某些字段信息过长，无法存储在B数节点中，这时会被单独分配空间，此时称为溢出页，该字段被称为页外列

InnoDB 存储引擎支持四种行格式

Row Format Compact Storage Characteristics Enhanced Variable-Length Column Storage Large Index key Prefix Support Compression Support Supported Tablespace Types Required File Format
REDUNANT No No No No system, file-per-table Antelope or Barracuda
COMPACT Yes No No No system, file-per-table Antelope or Barracuda
DYNAMIC Yes Yes Yes No file-per-table Barracuda
COMPRESSED Yes Yes Yes Yes file-per-table Barracuda

在创建表和索引时，文件格式都被用于每个 InnoDB 表数据文件（其名称与*.ibd匹配）。修改文件格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用如下命令

ALTER TABLE 表名 ROW_FORMAT=格式类型;

查看文件存储格式

mysql> SHOW TABLE STATUS
            Name : dept
          Engine : InnoDB
         Version : 10
      Row_format : Dynamic
            Rows : 3
  Avg_row_length : 5461
     Data_length : 0
 Max_data_length : 0
    Index_length : 0
       Data_free : 0
  Auto_increment : NULL
     Create_time : 2022-05-04 11:59:24
     Update_time : 2022-05-05 10:29:54
      Check_time : NULL
       Collation : latin1_swedish_ci
        Checksum : NULL
  Create_options : 
         Comment : 

mysql> select * from information_schema.innodb_sys_tables where name = 'dept' \G;
     TABLE_ID : 71
         NAME : dept
         FLAG : 33
       N_COLS : 5
        SPACE : 54
  FILE_FORMAT : Barracuda
   ROW_FORMAT : Dynamic
ZIP_PAGE_SIZE : 0
   SPACE_TYPE : Single

Undo Log

Undo ：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作
Undo Log ：数据库事务开始之前，会将要修改的记录存放在 Undo 日志里，当事务回滚时或数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响

产生和销毁

Undo Log 在事务开始前产生。事务在提交时，并不会立刻删除 Undo Log，InnoDB 会将该事务对应的 Undo Log 放入到删除列表中，后续会通过后台进程 Purge Thread 进行回收处理
Undo Log 属于逻辑日志，记录一个变化过程。例如执行一个 delete，Undo Log 会记录一个 insert；执行一个 update，Undo Log 会记录一个相反的 update

存储

Undo Log 采用段的方式管理和记录。在 InnoDB 数据文件中包含一种 Rollback Segment 回滚段，内部包含 1024 个 Undo Log Segment。可以通过下面一组参数来控制 Undo Log 存储

mysql> show variables like '%innodb_undo%';

作用

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中
事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

Redo Log

Redo ：就是重做。以恢复操作为目的，在数据库发生意外时重现操作
Redo Log ：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志

生成和释放

随着事务操作的执行，就会生成 Redo Log，在事务提交时会将产生 Redo Log 写入 Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘后，Redo Log 的使命就完成了，Redo Log 占用的空间就可以重用（被覆盖写入）。

工作原理

Redo Log 是为了实现事务的持久化而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性


  • 事务提交，为什么不直接持久化 User 到磁盘，而是要通过 Redo Log 来处理
    每次提交事务时，都直接持久化到磁盘，IO频率过高，效率低下。通常优化方案是，多个事务执行后统一持久化
  • 持久化到 Redo Log 和 直接持久化 User 到磁盘都是 IO 操作，有何区别
    假如有两个 User （ID 分别为 1， 8），直接持久化 User，需要分别查找定位到 1 和 8 的用户信息位置，然后再更新，属于随机IO，效率低；而通过 Redo Log 写时是将这两个变更信息顺序的写入到 Redo Log 中，属于顺序IO，效率高

写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写


相关配置

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（Group），每个文件组至少有 2 个重做日志文件，默认为 ib_logfile0 和 ib_logfile1。可以通过参数控制 Redo Log 存储

mysql> show variables like '%innodb_log%';

Redo Buffer 持久化到 Redo Log 的策略，可以通过 innodb_flush_log_at_trx_commit 设置

一般建议选择取值 2， 因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失 1 秒的事务提交数据


Bin Log

二进制日志（Binary Log），Binlog 是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录 SELECT 和 SHOW 这类操作。Binlog 日志是以事件形式记录，还包含语句所执行的消耗时间。

使用场景：

记录模式

Binlog 文件名默认为“主机名_binlog-序列号”格式，例如 oak_binlog-000001，也可以在配置文件中指定名称。

文件记录模式有：

文件结构

MySQL 的 binlog 文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是 Log Event。不同的修改操作对应的不同的 Log Event。比较常用的 Log Event 有：Query Event、ROW Event、Xid Event等。binlog文件的内容就是各种 Log Event 的集合


Log Event结构

写入机制

和 Redo Log 区别

上一篇下一篇

猜你喜欢

热点阅读