MySQL架构原理（三）线程模型和文件模型

InnoDB线程模型

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

  • read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

• write thread：负责写操作，将缓存脏页刷新到磁盘。4个

  • log thread：负责将日志缓冲区内容刷新到磁盘。1个

  • insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

• Purge Thread
  事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。
  show variables like '%innodb_purge_threads%';

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

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

  • 刷新日志缓冲区，刷到磁盘
  • 合并写缓冲区数据，根据IO读写压力来决定是否操作
  • 刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）

  每10秒的操作：

  • 刷新脏页数据到磁盘

  • 合并写缓冲区数据

  • 刷新日志缓冲区

  • 删除无用的undo页

InnoDB数据文件

一、InnoDB文件存储结构

InnoDB数据文件存储结构：
分为一个ibd数据文件–>Segment（段）–>Extent（区）–>Page（页）–>Row（行）

• Tablesapce
  表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。

• Segment
  段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf nodesegment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。

• Extent
  区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区。

• Page
  页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。

• Row
  行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Fieldpointers）等信息。

Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和pagebody组成。如下图所示，

二、InnoDB文件存储格式

• 通过 SHOW TABLE STATUS 命令
  

一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

• 通过 information_schema 查看指定表的文件格式

  select * from information_schema.innodb_sys_tables;	
  

三、File文件格式（File-Format）

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

• Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。

• Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。

通过innodb_file_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本开始改为Barracuda。

四、Row行格式（Row_format）

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

  DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。

  每个表的数据分成若干页来存储，每个页中采用B树结构存储；

  如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

• REDUNDANT 行格式
  使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便能够在页外存储。

• COMPACT 行格式
  与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。

• DYNAMIC 行格式
  使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制。

• COMPRESSED 行格式
  COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

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

ALTER TABLE 表名 ROW_FORMAT=格式类型;

Undo Log

Undo Log介绍

  Undo： 意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。

  Undo Log： 数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。

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

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

show variables like '%innodb_undo%';

innodb_undo_logs,128 128 日志文件大小128k
innodb_undo_tablespaces,0 0表示使用系统表空间 大于0表示使用独立表空间，2表示可以存储两个文件

3.5.2 Undo Log作用

• 实现事务的原子性
  Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

• 实现多版本并发控制（MVCC）
  Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。
  
  事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
  事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

Redo Log和Binlog

Redo Log和Binlog是MySQL日志系统中非常重要的两种机制，也有很多相似之处，下面介绍下两者细节和区别。

Redo Log日志

• Redo Log介绍
  Redo： 顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。

  Redo Log： 指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

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

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

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

如图所示：

• write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；

• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下。

• Redo Log相关配置参数
  每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。可以通过下面一组参数控制Redo Log存储：

show variables like '%innodb_log%';

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

• 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作，事务提交先到redobuffer，再由master线程一秒刷一次。

• 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。

• 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OScache -> flush cache to disk 的操作，每次事务执行的时候会直接提从redobuffer提交OScache中，后续再由master线程执行flush。

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

Binlog日志

• Binlog记录模式
  Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binarylog（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。

  • 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。

  • 数据恢复：通过mysqlbinlog工具来恢复数据。

  Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENTROW和MIXED三种，具体含义如下。

  • ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
    优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
    缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

  • STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
    优点：日志量小，减少磁盘IO，提升存储和恢复速度
    缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

  • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用
    STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

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

Binlog文件中Log event结构如下图所示：

Binlog写入机制

• 根据记录模式和操作触发event事件生成log event（事件触发执行机制）

• 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。

• 事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

  show variables like 'Innodb_flush_log_at_trx_commit';
  

Binlog文件操作

• Binlog状态查看

  show variables like '%log_bin%';
  

• 开启Binlog功能

  set global log_bin=mysqllogbin
  [2021-07-09 21:03:08] [HY000][1238] Variable 'log_bin' is a read only variable
  

  需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启MySQL服务。

  	#log-bin=ON
  	#log-bin-basename=mysqlbinlog
  	binlog-format=ROW
  	log-bin=mysqlbinlog  #这里等于上面注释掉的两行
  

• 使用show binlog events命令

  show binary logs; //等价于show master logs;
  show master status;
  show binlog events;
  show binlog events in 'mysqlbinlog.000001';
  

• 使用mysqlbinlog 命令

  mysqlbinlog "文件名"
  mysqlbinlog "文件名" > "test.sql"
  ```* 使用 binlog 恢复数据
  ```shell
  //按指定时间恢复
  mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
  //按事件位置号恢复
  mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002| mysql -uroot -p1234
  

  mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

• 删除Binlog文件

  purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
  purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
  reset master; //清除所有文件
  

  可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

Redo Log和Binlog区别

• Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。

• Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。

• Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。

• Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。