MySql-02-文件

MySql Comments

构成Mysql和InnoDB存储引擎表的各类文件包括：

  1. 参数文件：初始化参数、内存结构大小等设置
  2. 日志文件：记录MySql实例的各种类型活动
  3. socket文件：当用UNIX域套接字方式进行连接时所需要的文件
  4. pid文件：MySql实例的进程ID文件(作用是？)
  5. 存储引擎文件：每个引擎会有自己的文件保存数据(如记录和索引)

参数文件

当MySql实例启动时，会先读参数文件(如果没有则使用默认值)，用来初始化数据库

1
2
3
4
# 1. 查看数据库参数文件
mysql --help |grep my.cnf
# 2. 查看数据库参数
SHOW VARIABLES

日志文件

MySql常见的日志文件有：

  • 错误日志文件(error log)
  • 二进制日志文件(binlog)
  • 慢查询日志文件(slow query log)
  • 查询日志文件(log)

慢查询日志

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能

可用于定位可能存在问题的SQL语句，默认情况下并不启动慢查询日志

1
2
3
4
5
6
7
8
#查询慢查询阀值(等于阀值的并不会记录)
SHOW VARIABLES LIKE 'long_query_time'
#查询慢查询开关
SHOW VARIABLES LIKE 'log_slow_queries'
#慢查询没有使用索引(如果运行SQL而没有使用索引)
SHOW VARIABLES LIKE 'log_queries_not_using_indexes'
#表示每分钟允许记录到slow log且未使用索引的SQL语句(默认为0，防止语句频繁记录到slow log导致文件不断增大)
SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes'
1
2
#查看慢查询日志
mysqldumpslow nh122-190-slow.log

MySQL5.1 开始 可以将慢查询的日志记录放入一张表中，名为 slow_log

1
2
3
4
5
6
7
8
9
10
11
12
#指定慢查询输出的格式，默认为FILE
#TABLE则将日志写入slow_log表中
SHOW VARIABLES LIKE 'log_output'

#指定逻辑IO次数超过的记录写入slow log(默认100)
SHOW VARIABLES LIKE 'long_query_io'

SHOW VARIABLES LIKE 'slow_query_type' #表示slow log的启动方式
#0 表示不将sql语句记录到slow log
#1 表示根据运行时间将sql语句记录到slow log
#2 表示根据逻辑IO次数将sql语句记录到slow log
#3 表示根据运行时间及逻辑IO次数将sql语句记录到slow log

逻辑IO：包含所有的读取，不管是磁盘还是缓冲池

物理IO：指从磁盘进行IO读取的次数

查看日志文件：记录了对MySql数据库所有请求的信息，无论这些请求是否得到正确的执行。默认文件名为：主机名.log。和慢查询日志一样，可以将查询日志放入mysql架构下的general_log表中

二进制日志

记录了对MySql数据库执行更改的所有操作，但不包括select和show这类操作，因为这类对数据本身并没有修改

1
SHOW BINLOG EVENTS #查看二进制日志

主要有以下几点作用：

  • 恢复(recovery)：某些数据的恢复需要二进制日志。例如在一个数据库全备文件恢复后，用户可通过二进制日志进行point-in-time的恢复
  • 复制(replication)：通过复制和执行二进制日志使一台远程MySql数据库(一般为slave或standby)与一台MySql数据库(master或primary)进行实时同步
  • 审计(audit)：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击
1
2
#启动二进制文件my.cnf
log-bin[=name]

如果不指定name，则默认二进制日志文件名为主机名，后缀为二进制日志的序列号(bin_log.00001)，所在路径为数据库所在目录(datadir)

其他影响二进制日志记录的参数：

  • max_binlog_size：指定单个二进制日志文件的最大值，超过则新建二进制日志文件，后缀序列号+1 并记录到.index文件中(默认1G)
  • binlog_cache_size：
    • 当使用事务的表，所有未提交的二进制日志会被记录到一个缓存中，等该事务提交再将缓冲写入二进制日志文件。缓存大小由binlog_cache_size决定(默认32K)
    • binlog_cache_size是基于会话的，但一个线程开始一个事务，就会自动分配一个binlog_cache_size
    • 当事务记录大小大于缓冲时，回将缓冲中的日志写入一个临时文件中
    • 缓冲区大小的设置可以通过 SHOW GLOBASL STATUS命令查看
      • binlog_cache_use：记录使用缓冲区二进制日志的次数
      • binlog_cache_disk_use：记录使用临时文件写二进制日志的次数
  • binlog-do-db：表示需要写入哪些库的日志
  • binlog-ignore-db：表示需要忽略写入哪些库的日志
  • log-slave-update：如果是slave角色，则不会将从master取得并执行的二进制日志写入自己的二进制日志文件中。如果需要写入，则设置log–slave-update。如果搭建master=>slae=>slave架构复制，必须设置该值
  • Binlog-format：日志存储的不同格式(STATEMENT/ROW/MIX)

其他文件

  1. 套接字文件：UNIX系统下本地连接MySql可采用UNIX域套接字方式，需要一个套接字文件，
1
2
#查看套接字文件位置
SHOW VARIABLES LIKE 'socket'
  1. PID文件：当MySQL实例启动，会将自己的进程ID写入到pid文件
1
2
#查看PID文件位置
SHOW VARIABLES LIKE 'pid_file'

InnoDB存储引擎文件

表空间文件

InnoDB将存储的数据按表空间进行存放。默认配置下会有一个初始大小为10MB的默认表空间文件，名为ibdata1的文件。

1
Innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

将/db/ibdata1和/dr2/db/ibdata2两个文件组成表空间。

  • 若两个文件处于不同的磁盘上，磁盘的负载可能被平均。可以提供数据库的整体性能
  • 如果文件ibdata2用完了，则可以继续自动增长(autoextend)
  • 设置innodb_data_file_path之后，所有基于InnoDB存储引擎的表数据都会记录到该共享表空间
  • 设置innodb_file_per_table,将每个基于InnoDB的表都产生一个独立表空间，命令规则为：表名.ibd

注意：单独表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还存放在默认表空间中

单独表空间是指将每个表或索引存储在单独的表空间文件中。这种方式可以通过使用InnoDB存储引擎的"innodb_file_per_table"选项来启用。当"innodb_file_per_table"选项设置为ON时，每个表或索引都会存储在一个以表名命名的.ibd文件中，而不是默认的表空间文件。这种方式可以提供更好的灵活性和管理性，例如可以独立地备份、还原或迁移单个表或索引

默认表空间：默认表空间是MySQL数据库的默认存储方式。在默认情况下，表和索引数据存储在数据库目录下的与数据库名相同的文件中。例如，对于名为"mydatabase"的数据库，表和索引数据会存储在"mydatabase.ibd"文件中。默认表空间适用于InnoDB存储引擎和其他支持表空间概念的存储引擎

image-20210627130047312

Redo Log 文件

默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，称为重做日志文件(redo log file)。用于记录对于InnoDB存储引擎的事务日志。

每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。

为了得到更高的可靠性，可设置多个镜像日志组(mirrored log groups)，将不同的文件组放在不同的磁盘上

image-20210627130956222

采用循环写入的方式运行：在三个重做日志文件的重做日志文件组中，InnoDB存储引擎先写重写日志文件ib_logfile0，当到达文件最后时，会切换直重做日志文件1，当文件1也满了切到文件2，最后回到文件0，重复循环

  • innodb_log_file_size：指定每个日志文件大小(<512GB)
  • innodb_log_files_in_group：日志文件组中重做日志文件的数量，默认为2
  • innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为1(若磁盘高可用，如磁盘阵列，可不开)
  • Innodb_log_group_home_dir：指定日志文件组所在的目录

重做日志文件不能设置的太大，如果设置太大，恢复时需要太长时间，如果设置太小，可能导致一个事务的日志需要多次切换重做日志文件，会导致频繁的发生 async checkpoint

重做日志文件与二进制日志区别：

  1. 二进制日志记录所有与数据库相关的日志，而InnoDB存储的重做日志只记录该InnoDB本身的事务日志
  2. 二进制日志记录的都是关于一个事务的具体操作内容，即该日志为逻辑日志。而重做日志记录的是关于每个页(Page)的更改的物理情况
  3. 写入时间不同，二进制日志仅在事务提交前进行提交，即只写磁盘一次，不论事务多大。而在事务进行的过程中，会不断有重做日志条目(redo entry)被写入到重做日志文件中

重做日志由四部分组成：

  1. redo_log_type占用1字节，表示重做日志的类型
  2. space表示空间的ID，但采用压缩的方式，占用空间可能小于4字节
  3. page_no表示页的偏移量，铜梁采用压缩的方式
  4. redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲(redo log buffer)，再按照一定的条件顺序地写入日志文件

image-20210627133153148

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

写入条件：

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

  2. 另外一个由参数 innodb_flush_log_at_trx_commit控制，表示提交(commit)操作时，处理重做日志方式

    • 0 代表等待主线程每秒的刷新
    • 1 表示执行commit时将重做日志缓冲同步写到磁盘，伴有fsync的调用
    • 2 表示重做日志异步写到磁盘，即写到文件系统的缓存中，不能保证在执行commit时肯定会写入重做日志文件

    为了保证事务的持久性，必须将innodb_flush_log_at_trx_commit设置为1，即使宕机也能通过重做日志文件恢复

    0和2都有可能恢复时部分事务丢失，不同在于，为2时如果数据库宕机而操作系统没有宕机，此时日志保存在文件系统缓存中，恢复时同样能保证数据不丢失

Undo Log 文件

Undo Log通常存储在Undo Log文件中，这些文件位于InnoDB存储引擎的表空间中。每个Undo Log文件的大小由innodb_undo_log_truncate参数控制，默认大小为10MB。

Undo Log文件记录了事务执行过程中对数据所做的修改操作，以及旧数据的备份。在事务提交之前，对数据的修改操作都会先记录到Undo Log中。如果事务回滚或需要进行一致性读取，可以使用Undo Log中的信息来还原数据到事务之前的状态。

Undo Log文件的命名规则通常是undo_<ID>.log，其中<ID>是一个递增的标识符，表示Undo Log文件的编号。

Undo Log文件是InnoDB存储引擎的特定功能，其他存储引擎如MyISAM并不使用Undo Log文件。

写入过程

只要redo log和binlog保证持久化到磁盘，就能确保MySql异常重启后，数据可以恢复

Bin Log 的写入机制

首先需要明确，不是所有的操作都会记录到bin log中

  1. SELECT语句：SELECT语句用于读取数据，它通常不会写入二进制日志，因为它只涉及数据的查询和检索，而没有对数据进行修改。
  2. DDL语句（Data Definition Language）：DDL语句用于创建、修改或删除数据库对象（例如表、索引、视图等），例如CREATE、ALTER和DROP语句。这些操作通常不会写入二进制日志，因为它们可以通过备份和还原数据库来进行恢复。
  3. TRUNCATE TABLE语句：TRUNCATE TABLE语句用于快速删除表中的所有数据。与DELETE语句不同，TRUNCATE TABLE语句不会写入二进制日志，因为它是一种快速的操作，可以通过删除底层数据文件来完成。
  4. 内部临时表操作：在某些情况下，MySQL使用内部临时表来执行查询和排序操作。这些内部临时表的创建和操作通常不会写入二进制日志

如果MySQL服务器的日志格式设置为"ROW"格式，那么即使某些语句本身不会写入二进制日志，但对于表的修改操作（例如INSERT、UPDATE、DELETE）的行级别变化仍然会被写入二进制日志。因此，即使某些语句本身不会被记录，表的修改仍然可以通过解析二进制日志进行恢复或复制

img

  1. 事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中

  2. 系统为每个线程分配了一片binlog cache内存，但共用一份binlog文件

  3. 参数binlog_cache_size控制单个线程内binlog cache大小，一个事务的binlog是不能拆分的(不论事务多大，都要确保一次性写入)，如果超过这个大小就要暂存到磁盘

  4. 事务提交的时候，执行器把binlog cache 里完整的事务写入binlog中，并清空binlog cache

1
2
ssize_t write(int fd, const void *buf, size_t count);
int fsync(int fd);

write 是把日志写入到文件系统的page cache，内存中，没有持久化到磁盘，所以速度比较快，函数返回并不代表已经写入磁盘

fsync 是将数据持久化到磁盘，占用磁盘的IOPS，通知内核将数据写到硬盘中

write 和 fsync 的时机，是由参数 sync_binlog 控制的

  1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
  2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
  3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync(如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志)

当执行UPDATE语句时，是否写入二进制日志（Binary Log）取决于二进制日志的设置以及执行的上下文。下面是一些常见的情况：

  1. 二进制日志已启用：如果二进制日志已启用（通过配置文件或运行时设置），则UPDATE语句通常会被写入二进制日志。这是默认行为，旨在确保数据更改能够在主从复制等场景中进行传播。
  2. 非事务环境：如果不在事务环境中执行UPDATE语句，它通常会立即写入二进制日志。每个UPDATE语句都会作为一个单独的事件记录到二进制日志中。
  3. 事务环境：如果在事务中执行UPDATE语句，并且事务尚未提交，则UPDATE语句不会立即写入二进制日志。在事务提交之前，对同一事务的多个修改操作会被合并为一个事件写入二进制日志。当事务提交时，包含UPDATE操作的完整事务将被写入二进制日志。

redo log的写入机制

redo log存在三种状态：

  1. 存在redo log buffer中，物理上是在MySql进程内存中
  2. 写到磁盘(write)，但是没有持久化(fsync)，物理上是在文件系统的page cache
  3. 持久化到磁盘，对应的是hard disk

为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数

  • 0 表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，等待主线程每秒刷新
  • 1 表示每次事务提交时都将 redo log 直接持久化到磁盘(fsync)；
  • 2 表示每次事务提交时都只是把 redo log 写到 page cache(文件系统缓存)

另外InnoDB有一个后台线程，每隔1s把redo log buffer中的日志，调用write写到文件系统的page cache,然后调用fsync持久化到磁盘

注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也可能已经持久化到磁盘的

实际上，除了后台线程没每秒一次的轮询操作，还有两种场景会让一个没有提交的事务的redo log写入到磁盘中

  1. redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache
  2. 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘

一条update语句的执行过程：

img
  1. 执行器先找引擎取 ID=2 这一行
    • 如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
    • 否则，需要先从磁盘读入内存，然后再返回
  2. 执行器拿到引擎给的行数据，更新行得到新的一行数据，再调用引擎接口写入这行新数据
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

问题

  1. 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的

    一个事务的 binlog 必须连续写，整个事务完成后，再一起写到文件里。而 redo log 记录的是，中间有生成的日志可以写到 redo log buffer 中。其他事务提交的时候可以被一起写到磁盘中

  2. 为什么执行器更新数据之后不直接提交事务接口，而是和引擎有一个交互(这里是redo log)，其他引擎并没有redo log

  3. 更新的时候数据量太大，内存放不下怎么办

    LRU

参考链接

  1. 《MySql技术内幕》

  2. https://time.geekbang.org/column/article/76161