Lanchester Blog

Leave a leaf

0%

MySQL 文件

Posted on Edited on In

本章将分析构成 MySQL 数据库和 InnoDB 存储引擎表的各种类型文件。这些文件都存放在数据目录中，我们可以通过 SHOW VARIABLES LIKE 'datadir' 来查看该目录的位置。这些文件类别如下：

  • 参数文件：告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
  • 日志文件：用来记录 MySQL 实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
  • socket 文件：当用 UNIX 域套接字方式进行连接时需要的文件。
  • pid 文件：MySQL 实例的进程 ID 文件。
  • MySQL 表结构文件：用来存放 MySQL 表结构定义文件。
  • 存储引擎文件：因为 MySQL 表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

参数文件

当 MySQL 实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL 实例会按照一定的顺序在指定的位置进行读取，用户只需通过命令 mysql --help | grep my.cnf 来寻找即可。

通过命令 SHOW VARIABLES 或 information_schema 架构下的 GLOBAL_VARIABLES 查看数据库中的所有参数。

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

  • 动态（dynamic）参数
  • 静态（static）参数

动态参数意味着可以在 MySQL 实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读（read only）的。可以通过 SET 命令对动态的参数值进行修改，SET 的语法如下：

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

这里可以看到 session 和 global 关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。但需要注意的是，虽然修改了该变量的全局值，在这次的实例生命周期都有效，MySQL 实例本身并不会对参数文件中的该值进行修改。下次 MySQL 服务启动时，MySQL 实例依旧会读取参数文件。

日志文件

日志文件记录了影响 MySQL 数据库的各种类型活动。MySQL 数据库中常见的日志文件有：

  • 错误日志（error log）
  • 二进制日志（binlog）
  • 慢查询日志（slow query log）
  • 查询日志（log）

错误日志

错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录。MySQL DBA 在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。

用户可以通过命令 SHOW VARIABLES LIKE 'log_ error' 来定位该文件

慢查询日志

在 MySQL 启动时设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询日志文件中。该阈值可以通过参数 long_query_time 来设置，默认值为 10，代表执行时间大于 10 秒的 SQL 语句就会被记录在慢查询日志中。另外，使用 log_queries_not_using_indexes 可以将没有使用索引的 SQL 记录到慢查询日志。使用 log_throttle_queries_not_using_indexes 配置每分钟允许记录到 slow log 的且未使用索引的 SQL 语句次数。

使用 mysqldumpslow 命令方便的整理慢查询日志。

参数 logoutput 指定了慢查询输出的格式，默认为 FILE，可以将它设为 TABLE，然后就可以查询 mysql 架构下的 slow log 表了。参数 log_output 是动态的，并且是全局的，因此用户可以在线进行修改。查看 slow_log 表的定义会发现该表使用的是 CSV 引擎，对大数据量下的查询效率可能不高。用户可以把 slow_log 表的引擎转换到 MyISAM，并在 start_time 列上添加索引以进一步提高查询的效率。

在 InnoDB 引擎中对于 SQL 语句的捕获方式有多种：运行时间、物理读取次数（从磁盘 IO 读取的次数）、逻辑读取次数（所有的读取，包括磁盘和缓冲池），可以使用 slow_query_type 进行配置。

查询日志

查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。同样地，可以将查询日志的记录放人 mysql 架构下的 general_log 表中，同 slow_log 相同。

二进制日志

二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。总的来说，二进制日志有以下作用：

  • 恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
  • 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或 standby）与一台 MySQL 数据库（一般称为 master 或 primary）进行实时同步。
  • 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

以下配置文件参数影响二进制日志记录的信息和行为：

  • max_binlog_size

    指定单个二进制文件的容量最大值，单位是 B，默认 1G（1073741824）。

  • binlog_cache_size

    未提交事务的二进制日志会被记录在缓存中，待事务提交后直接写入 bin log。小为32K。此外，binlog_cache_size 是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过 SHOW GLOBAL STATUS 命令查看 binlog_cache_use、binlog_cache_disk_use 的状态，可以判断当前 binlog_cache_size 的设置是否合适。binlog_cache_use 记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use 记录了使用临时文件写二进制日志的次数。

  • sync_binlog

    在默认情况下，二进制日志并不是在每次写的时候同步到磁盘（用户可以理解为缓冲写）。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来问题。参数 sync_binlog = [N] 表示每写缓冲多少次就同步到磁盘。如果将 N 设为 1 表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。sync_ binlog 的默认值为 0，如果使用 InnoDB 存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为 ON。不过该值为 ON 时，确实会对数据库的 IO 系统带来一定的影响。

    但是，即使将 sync_binlog 设为 1，还是会有一种情况导致问题的发生。当使用
    InnoDB 存储引擎时，在一个事务发出 COMMIT 动作之前，由于 sync_binlog 为 1，因此会将二进制日志立即写人磁盘。如果这时已经写人了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在 MySQL 数据库下次启动时，由于 COMMIT 操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数 innodb_support_xa 设为 1 来解决，虽然 innodb_support_xa 与 XA 事务有关，但它同时也确保了二进制日志和 InnoDB 存储引擎数据文件的同步。

  • binlog-do-db、binlog-ignore-db、log-slave-update

    参数 binlog-do-db 和 binlog-ignore-db 表示需要写入或忽略写入哪些库的日志。默认为空，表示需要同步所有库的日志到二进制日志。如果当前数据库是复制中的 slave 角色，则它不会将从 master 取得并执行的二进制日志写人自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。如果需要搭建 master=>slave=>slave 架构的复制，则必须设置该参数。

  • binlog_format

    它有三种模式可供选择：STATEMENT、ROW、MIXED。

    STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

    ROW 格式下，二进制日志记录的是记录表的行更改情况。

    MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但在一些情况下会使用 ROW 格式，可能的情况有：

    1. 表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。
    2. 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数。
    3. 使用了 INSERT DELAY 语句。
    4. 使用了用户定义函数（UDF）。
    5. 使用了临时表（temporary table）。

    binlog_format 是动态参数，可以在任何时候修改。但修改时，需要确认是否会对复制造成影响。

套接字文件

前面提到过，在 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可由参数 socket 控制。一般在 /tmp 目录下，名为 mysql.sock。也可通过 SHOW VARIABLES LIKE ‘socket’ 查看文件地址。

pid 文件

当 MySQL 实例启动时，会将自己的进程 ID 写入一个文件中一该文件即为 pid 文件。该文件可由参数 pid_file 控制，默认位于数据库目录下，文件名为 主机名.pid。也可通 SHOW VARIABLES LIKE ‘pid_file’

表结构定义文件

因为 MySQL 插件式存储引擎的体系结构的关系，MySQL 数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL 都有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。

frm 还用来存放视图的定义，如用户创建了一个 v_a 视图，那么对应地会产生一个 v_a.frm 文件，用来记录视图的定义，该文件是文本文件，可以直接使用 cat 命令进行查看。

InnoDB 存储引擎文件

之前介绍的文件都是 MySQL 数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎还有其自己独有的文件。InnoDB 存储引擎密切相关的文件，包括重做日志文件、表空间文件。

表空间文件

InnoDB 采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为 10MB，名为 ibdata1 的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数 innodb_data_file_path 对其进行设置。用户可以通过多个文件组成一个表空间，同时制定文件的属性，这样可以将磁盘的负载平均，提高数据库的整体性能。

设置 innodb_datafile_path 参数后，所有基于 InnoDB 存储引擎的表的数据都会，记录到该共享表空间中。若设置了参数 innodb_file_per_table， 则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。

需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲 BITMAP 等信息，其余信息还是存放在默认的表空间中。下图显示了 InnoDB 存储引擎对于文件的存储方式。

Redo 日志文件

在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件。在 MySQL 官方手册中将其称为 InnoDB 存储引擎的日志文件，不过更准确的定义应该是重做日志文件（redo log file）。

为什么强调是重做日志文件呢？因为重做日志文件对于 InnoDB 存储引擎至关重要，它们记录了对于 InnoDB 存储引擎的事务日志。

当实例或介质失败（media failure）时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。 为了得到更高的可靠性，用户可以设置多个的镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，再当重做日志文件 2 也被写满时，会再切换到重做日志文件1中。

与 bin 日志文件的区别

redo log不是二进制日志。虽然二进制日志中也记录了 InnoDB 表的很多操作，也能实现重做的功能，但是它们之间有很大区别。

  1. 二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而 redo log 是 InnoDB 层产生的，只记录该存储引擎中表的修改。并且二进制日志先于 redo log 被记录。
  2. 二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的 SQL 设置，如该行记录的每列的值是多少。而 redo log 是在物理格式上的日志，它记录的是数据库中每个页的修改。
  3. 二进制日志只在每次事务提交的时候一次性写入缓存中的日志”文件”（对于非事务表的操作，则是每次执行语句成功后就直接写入）。而 redo log 在数据准备修改前写入缓存中的 redo log 中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的 redo log 写入日志，写入完成后才执行提交动作。
  4. 因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而 redo log 中是记录的物理页的修改，redo log 文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。
  5. 事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。

基本概念

redo log 包括两部分：一是内存中的日志缓冲（redo log buffer），该部分日志是易失性的；二是磁盘上的重做日志文件（redo log file），该部分日志是持久的。

在概念上，InnoDB 通过 force log at commit 机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的 redo log file 和 undo log file 中进行持久化。

从重做日志缓冲往磁盘写入时，是按 512 个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有 doublewrite。主线程（master thread），知道在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。另一个触发写磁盘的过程是由参数 innodb_flush_log_at_trx_commit 控制，表示在提交（commit）操作时，处理重做日志的方式。

参数 innodb_flush_log_at_trx_commit 的有效值有 0、1、 2。 0 代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。1 和 2 不同的地方在于：1 表示在执行 commit 时将重做日志缓冲同步写到磁盘，即使有 fsync 的调用。2 表示将重做日志异步写到磁盘，即写到文件系统的缓存中。因此不能完全保证在执行 commit 时肯定会写入重做日志文件，只是有这个动作发生。

因此为了保证事务的 ACID 中的持久性，必须将 innodb_flush_log_at_trx_commit 设置为 1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。而将重做日志文件设置为 0 或 2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为 2 时，当 MySQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

刷盘规则及 checkpoint

log buffer 中未刷到磁盘的日志称为脏日志（dirty log）。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。

刷日志到磁盘有以下几种规则：

  1. 发出 commit 动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。
  2. 每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是 1 秒。要注意，这个刷日志频率和 commit 动作无关。
  3. 当 log buffer 中已经使用的内存超过一半时。
  4. 当有 checkpoint 时，checkpoint 在一定程度上代表了刷到磁盘时日志所处的 LSN 位置。

InnoDB 的恢复行为

在启动 InnoDB 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。

因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志（如二进制日志）要快很多。而且，InnoDB 自身也做了一定程度的优化，让恢复速度变得更快。

重启 InnoDB 时，checkpoint 表示已经完整刷到磁盘上 data page 上的 LSN，因此恢复时仅需要恢复从 checkpoint 开始的日志部分。例如，当数据库在上一次 checkpoint 的 LSN 为 10000 时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的 LSN，如果数据页的 LSN 小于日志中的 LSN，则会从检查点开始恢复。

还有一种情况，在宕机前正处于 checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的 LSN 就会大于日志页中的 LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

Undo 日志文件

undo log 有两个作用：提供回滚和多个行版本控制（MVCC）。

在数据修改的时候，不仅记录了 redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。

undo log 和 redo log 记录物理日志不一样，它是记录了修改命令对应的撤销命令的逻辑日志。

当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

log 存储方式

InnoDB 存储引擎对 undo 的管理采用段的方式。rollback segment 称为回滚段，每个回滚段中有 1024 个 undo log segment。

MySQL 5.5 以后支持 128 个 rollback segment，即支持 128*1024 个 undo 操作，还可以通过变量 innodb_undo_logs 自定义 rollback segment 个数，默认值为 128。

undo log 默认存放在共享表空间中。

[root@xuexi data]# ll /mydata/data/ib*
-rw-rw---- 1 mysql mysql 79691776 Mar 31 01:42 /mydata/data/ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile1

如果开启了 innodb_file_per_table ，将放在每个表的 .ibd 文件中。

默认 rollback segment 全部写在一个文件中，但可以通过设置变量 innodb_undo_tablespaces 平均分配到多少个文件中。该变量默认值为 0，即全部写入一个表空间文件。该变量为静态变量，只能在数据库示例停止状态下修改，如写入配置文件或启动时带上对应参数。但是 InnoDB 存储引擎在启动过程中提示，不建议修改为非 0 的值，如下：

2022-02-15 13:16:00 7f665bfab720 InnoDB: Expected to open 3 undo tablespaces but was able
2022-02-15 13:16:00 7f665bfab720 InnoDB: to find only 0 undo tablespaces.
2022-02-15 13:16:00 7f665bfab720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2022-02-15 13:16:00 7f665bfab720 InnoDB: correct value and retry. Suggested value is 0

另外，undo log 也会产生 redo log，因为 undo log 也要实现持久性保护。

delete/update 操作的内部机制

当事务提交的时候，InnoDB 不会立即删除 undo log，因为后续还可能会用到 undo log，如隔离级别为 repeatable read 时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即 undo log 不能删除。

但是在事务提交的时候，会将该事务对应的 undo log 放入到删除列表中，未来通过 purge 来删除。并且提交事务时，还会判断 undo log 分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的 undo log 页而浪费存储空间和性能。

通过 undo log 记录 insert、delete 和 update 操作的结果发现：

  • insert 操作插入行，直接向 undo log 写入一条 delete 操作

  • delete 操作实际上不会直接删除，而是将 delete 对象打上 delete flag，标记为删除，最终的删除操作是 purge 线程完成的。

  • update 分为两种情况：update 的列是否是主键列。
    • 如果不是主键列，在 undo log 中直接反向记录是如何 update 的。
    • 如果是主键列，update 分两部执行：先删除该行，再插入一行目标行。

MVCC 原理

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含下面这两个必要的隐藏列（row_id 并不是必要的；在创建的表中有主键时，或者有不允许为 NULL 的 UNIQUE 键时，都不会包含 row_id 列）。

  • trx_id：一个事务每次对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列。
  • roll pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中。这个隐藏列就相当于一个指针，可以通过它找到该记录修改前的信息。

如下图，我们依次提交三次事务，形成的 roll pointer 版本链，头结点是最新的一条数据，向下依次递减。

MVCC 即是根据事务的隔离状态通过 roll pointer 查询到对应的历史数据。