MySQL之深入InnoDB存储引擎——物理文件

一、参数文件

当 MySQL 实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数。在默认情况下，MySQL 实例会按照一定的顺序在指定的位置读取，没有参数文件也可以运行，这时所有的参数值取决于编译 MySQL 时指定的默认值和源代码中指定参数的默认值。

但是如果在默认的数据库目录下找不到 mysql 架构，则启动同样失败，mysql 架构中记录了访问该实例的权限。

可以通过命令​**show variables**​来查看数据库中的所有参数（可以通过 like 来过滤参数名）。

MySQL 数据库中的参数可以分为两类：

• 动态参数：可以在 MySQL 实例运行中进行更改
• 静态参数：在整个实例生命周期内只读，不可进行修改

可以通过 set 命令对动态参数进行修改，通过@@global和@@session来指定是对当前会话进行修改还是对整个实例生命周期都生效。

二、日志文件

• 错误日志

• 慢查询日志：可以在 MySQL 启动时设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询日志文件中，该阈值可以通过参数 long_query_time 来设置，默认为 10 秒。默认情况下不开启慢查询日志，可以通过 log_slow_queries 参数来开启

  • log_queries_not_using_indexes 参数用来开启记录没有使用索引的 SQL 语句
  • log_throttle_queries_not_using_indexes 参数用来表示每分钟允许记录到 slow log 的且未使用索引的 SQL 语句次数，默认为 0，即没有限制
  • 可以通过 mysqldumpslow 命令来方便的查看相关的信息
  • 可以通过 log_output 指定慢查询输出的格式，默认为 FILE，可以将它设置为 TABLE，之后就可以查询 mysql 架构下的 slow_log 表

• 查询日志：记录了所有对 MySQL 数据库请求的信息，无论请求是否得到了正确的执行（甚至包括 Access denied的请求）

• 二进制日志：记录了对 MySQL 数据库执行更改的所有操作，不包括 SELECT 和 SHOW 这类操作（需要使用查询日志）

二进制日志文件主要右以下几种作用：

• 恢复：某些数据的恢复需要二进制日志

  • 例如在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复

• 复制：原理与恢复相似，通过复制和执行二进制日志使 slave 数据库与 master 数据库进行实时同步

• 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

**二进制文件在默认情况下并没有启动，**需要手动指定参数来启动。开启这个选项会对数据库的整体性能有所影响，但是影响十分有限（1%）。如果使用 InnoDB 存储引擎进行复制，并且想得到最大的高可用性，建议开启。

在默认情况下，二进制日志并不是每次写的时候就同步磁盘，因此当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件。

• sync_binlog=1：每次提交事务的时候直接使用 fsync 写入磁盘，不使用操作系统的缓冲、
• sync_binlog=0（默认值）：每次提交事务的时候都保存到操作系统的 page cache，之后由文件系统自己控制缓存的刷新
• sync_binlog>1：每次提交事务都先写到 page cache，等到积累了 N 个事务之后才 MySQL 调用操作系统刷新操作刷入盘

日志的记录格式有以下三种：

1. STATEMENT：记录的是逻辑 SQL 语句

2. ROW：记录的不再是简单的 SQL 语句，而是记录行更改情况，如果一个update语句修改一百行数据，那么这种模式下就会记录100行对应的记录日志

3. MIXED：默认使用 STATEMENT 格式保存，一些情况下（无法完成主从复制的操作）使用 ROW 格式保存

   1. 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNTS() 等不确定函数
   2. 使用了 INSERT DELAY 语句
   3. 使用了用户定义函数
   4. 使用了临时表

一个 SQL 在不同的时间点执行它们产生的数据变化和影响是不一样的，所以这种情况下，数据同步或恢复的时候就容易出现不一致的情况，因此使用 ROW 可以带来更好的可靠性。

binlog 是二进制文件，需要使用 mysqlbinlog 命令查看。

三、表结构定义文件

因为 MySQL 插件式存储引擎的体系结构的关系，MySQL 数据的存储是根据表的， 每个表都会有与之对应的文件。在 MySQL 8 之前不论表采用何种存储引擎，都有一个以 frm 为后缀名的文件，记录了该表的表结构定义。MySQL 8之后 InnoDB 存储引擎的表定义结构整合到 ibd 文件中，而 MyISAM 的 frm 文件变为 sdi 文件。

四、InnoDB 存储引擎文件

前面的文件都是 MySQL 数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎还有自己独有的文件。

1、表空间文件

InnoDB 存储引擎可将所有数据存放于 ibdata* 的共享表空间，也可将每张表存放于独立的 .ibd 文件的独立表空间(部分数据)。共享表空间以及独立表空间都是针对数据的存储方式而言的。

• 共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。 默认的文件名为 ibdata1，初始大小为 10M。可以使用 innodb_data_file_path 设置一个或者多个文件组成表空间，同时可以指定大小属性，如果用完文件可以自动增长

  • 其中会包括 undo 信息，在事务未提交时数据即已经写入了表空间文件，当事务rollback时Undo信息不会被删除，但是此空间会被标记，后续会以覆盖的方式被重新使用
  • Changebuffer 和 doublewrite buffer 也保存在其中

• 独立表空间：可以通过 innodb_file_per_table = ON 来开启独立表空间。开启后每个表都会生成独立的 .ibd 文件来进行存储

  • 包括了单独一个表的数据、索引等内容
  • 其余数据仍存放在共享表空间中，默认情况下独立表空间的存储位置也是在表的位置之中。

InnoDB 采用将存储的数据按表空间进行存放的设计。在默认配置下会有一个初始大小为 10 MB，名为 ibdata1 的文件，该文件就是默认的共享表空间文件。

2、重做日志文件

在默认情况下，data 目录下会有两个名为 ib_logfile0 和 ib_logfile1的文件。每个 InnoDB 存储引擎至少有一个重做日志文件组，每个文件组下至少有 2 个重做日志文件。为了得到更高的可靠性，可以设置多个镜像日志组，将不同的文件组放在不同的磁盘以此提高重做日志的可用性。

在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎险些重做日志文件1，当到达文件的最后时，会切换至重做日志文件2，再当重做日志2也被写满时，会再切换到重做日志文件1中。

重做日志文件不能设置的太大，否则恢复时可能需要很长的时间，也不能设置的太小，否则可能导致一个事务的日志需要多次切换重做日志文件，并且导致频繁地发生 async checkpoint，导致性能的抖动。

与 binlog 的区别：

1. binlog 会记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MyISAM、Heap 等，而 redo log 只记录 InooDB 存储引擎本身的事务日志
2. binlog 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志，而 redo log 记录的是关于每个页的更改的物理情况
3. binlog 仅在事务提交前进行提交，即一个事务只刷盘一次，而重做日志条目会在事务进行的过程中不断地写入到重做日志文件中
4. binlog 是可以追加写入的（指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，保存的是全量的日志），redo log 是循环写的，空间固定会用完，只会记录未刷盘的日志，已经刷入磁盘的数据都会因为空间有限而在后续被覆盖

虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。而 redo log 每次刷盘会更新日志文件中的Check Point根据对应的LSN来判断该条操作是否已经落盘。所以redo log具有crash-safe能力

redo log 条目结构：

• redo_log_type：一个字节，表示重做日志的类型
• space：表空间的ID，采用压缩的方式，有可能小于 4 个字节
• page_no：页的偏移量，同样采用压缩的方式
• redo_log_body：重做日志的数据部分

重做日志缓冲往磁盘写入时，是按 512 个字节，也就是一个扇区的大小进行写入。因为扇区时写入的最小单位，因此可以保证写入必定是成功的，所以重做日志的写入过程中不需要有 doublewrite。

重做日志的写入磁盘的情况：

1. 每秒 Master Thread 都会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交

2. 事务提交时，根据 innodb_flush_log_at_trx_commit 参数控制：

   • 0 表示提交事务时不将事务的重做日志写入磁盘的日志文件，而是等待主线程每秒的刷新

   • 1 表示提交时将重做日志缓冲同步写到磁盘，即伴随 fsync

   • 2 表示提交时将重做日志缓冲异步写道磁盘，即写道文件系统的缓存中，因此不能完全保证在执行 commit 时肯定会写入重做日志文件

     • 设置为 2 时当数据库宕机而操作做系统以及服务器没有发生故障时数据不会丢失