InnoDB 存储引擎之文件分类

学习《mysql 技术内幕：InnoDB 存储引擎》 第三章关于数据库文件以及 innodb 存储引擎相关文件介绍，学习心得总结如下：

mysql 文件主要包括参数文件，日志文件，socket 文件，pid文件，表结构文件，存储引擎文件。

参数文件

mysql 启动时会读取配置参数文件，如果找不到文件，会使用默认值和源代码中指定参数的默认值。

查看参数的两种方式：

• show variables

show varables like "%innodb_use_native_aio%";

• information_schema.GLOBAL_VARIABLES

select * from GLOBAL_VARIABLES where varable_name like "%innodb_use_native_aio%";

参数分类：

• 动态参数：

在实例运行中可以修改，但是只在当前生命周期中有效，如果 mysql 重启，还是会重新读取参数文件。所以如果想要在所有生命周期中有效，必须修改参数文件。

对参数的修改可以分为 global 和 session，表示影响的是整个生命周期还是当前会话。有些参数只能在当前会话中修改，有的参数只能在整个生命周期中修改，有些参数既可以在当前会话中修改也可以在整个生命中期中修改。

set @@global.read_buffer_size=104876;
set @@session.read_buffer_size=78929;
select @@session.read_buffer_size;
select @@global.read_buffer_size;

• 静态参数：

在整个生命中期中不可以修改。

日志文件

记录了 Mysql 数据库各种类型的操作行为，主要包括错误日志，慢查询日志，查询日志，二进制日志。

错误日志

不仅记录了所有错误信息，还有一些告警和正确的提示信息，方便定位问题。可以查看参数 log_error 来找到 mysql 错误日志文件的位置

show variables like "%log_error%"

慢查询日志

可以通过参数 log_slow_queries 设置是否开启慢查询日志。

主要可以记录三种慢查询 SQL：

• 查询时间大于参数 long_query_time 的 SQL
• 没有使用索引的查询，是否记录取决于 log_queries_not_using_indexes 参数的设置
• 对逻辑读取次数大于参数 long_query_io 次数的 SQL 记录到慢查询中

慢查询日志的输出格式，由 log_output 格式指定，主要有两种格式：

• FILE
  如果是文件格式，可以使用 mysqldumpslow 命令分析慢查询日志

- 查询执行时间最长的 10 条 SQL 语句
mysqldumpslow -s al -n 10 david.log

• TABLE：记录在 mysql 架构下的 slow_log 表中。

查询日志

查询日志记录了所有对 MYSQL 数据库请求的信息，无论这些请求是否得到正确的执行。通过 general_log 和 general_log_file 参数设置。

二进制日志

二进制日志(binary log) 记录了对 MYSQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 类型操作，通过参数 log-bin 参数来开启，主要用途如下：

• 恢复数据
• 复制：通过 binary log 将本数据库和其它数据库进行同步
• 审计：对数据的修改做审计

开启 binary log 会对性能造成一定的影响，但是这个影响有限，差不多 1% 的下降。

一些关于二进制日志的配置参数：

• max_binlog_size：指定了二进制文件的最大值，如果超过该值，则产生新的二进制文件
• binlog_cache_size：该参数记录二进制文件缓存的大小

当使用事务时，二进制文件的记录会被先放入缓存，当事务提交时后面再写入二进制文件，该参数记录二进制文件缓存的大小。当超过这个值但是事务没有被提交，这个时候会把缓存中的值写入临时文件。

• binlog_cache_use：记录使用缓存写二进制日志的次数
• binlog_cache_disk_use：记录了使用临时文件写二进制日志的次数
• sync_binlog：表示二进制文件每次写缓存多少次被同步到磁盘。
• binlog_do_db：表示需要写入哪些库的二进制文件
• binlog_ingore_db：忽略哪些库的二进制文件
• log_slave_update：是否主动将 master 取得的并执行的二进制日志写入 slave 角色中，高可用情况下，该参数必须开启
• innodb_support_xa: 该参数可以确保二进制日志与 InnoDb 存储数据文件的同步，如果不开启该参数，有些情况下虽然事务回滚了，但是仍然记录了二进制文件，会导致数据不一致
• binlog_format：记录二进制日志的格式，5.1 版本引入，动态参数，可以运行时修改。一共有三种日志格式：

- STATEMENT
记录日志文件执行的逻辑 SQL 语句，但是如果在服务器上运行 rand，uuid 等函数或者使用触发器，都可能导致主从服务器数据不一致，默认的日志格式就是 STATEMENT。

- ROW
记录表的行的更改情况，不会出现数据不一致情况，但由于记录的是行数据的更改，二进制文件有时候会很大，开启该参数对于磁盘的开销和复制的网络开销都有一定的成本
- MIXED
采用 STATEMENT 和 ROW 混合的方式记录日志，通常情况下使用 STATEMENT 方式，一些特殊情况下使用 ROW 方式：
    1) 存储引擎为 NDB
    2）使用 UUID，USER，CURRENT_USER，FOUND_ROWS,ROW_COUNT 等不确定函数
    3）使用了 INSERT DELAY 语句
    4）使用了用户定义的函数
    5）使用了临时表

binary log 文件格式二进制的，不能直接查看，必须用工具 mysqlbinlog 命令分析查看

mysqlbinlog --start-position=203 test.000004

socket 文件

在本地连接 MYSQL 时使用 UNIX 域套接字方式时会用到的连接文件，通过参数 socket 来设置套接字文件的存储位置

pid 文件

存放 mysql 进程 ID，通过参数 pid_file 来设置 pid 文件存放位置

表结构文件

以 frm 为后缀名的文件，记录了相关表和视图的结构定义。

InnoDB 存储引擎文件

上面介绍的查询日志，二进制日志等都是 mysql 数据库本身的问题，和存储引擎无关，而每个存储引擎有自己独立文件，其中InnoDB 存储引擎文件包括重做日志文件和表空间文件：

表空间文件

• 通过参数 innodb_data_file_path 设置表空间文件的共享空间
• 通过 innodb_data_per_table 设置是否基于每个表产生一个独立的表空间
• 单独的表空间只存储了该表的数据，索引和插入缓存 BITMAP 等信息，其它信息仍然存放在默认共享表空间中。

重做日志文件

• 重做日志文件记录了 InnoDB 存储引擎的事务日志，在发生宕机的情况下，对事务操作进行恢复操作。
• 重做日志文件至少有一个重做日志文件组，每个重做日志文件组至少有 2 个重做日志文件。
• 为了保证高可用性，可以设置镜像日志文件组，将日志组存储在不同的磁盘上。
• 重做日志的写入不需要 doublewrite，因为写入是按照一个扇区的大小进行写入的，是写入的最小单位，所以可以保证写入的可靠性。
• 重做日志也有缓存，但是缓存不大，默认为 8MB，将重做日志缓存刷新到日志文件文件的三个条件：

- Master Thread 会每一秒刷新一次
- 每个事物提交时会将刷新，由 innodb_flush_log_at_trx_commit 参数控制：
    0 表示事务提交时，不写入重做日志文件，而是等待 Master Thread 的定时刷新
    1 表示每当事务提交时，就必须同步写入重做日志文件，为了保证 ACID 中的持久性，事务必须设置为 1。才能保证宕机时可以恢复
    2 表示重做日志文件异步写入磁盘
- 当重做日志缓存池剩余空间小于 1/2 时会刷新，重做日志缓存不能设置太小，否则会导致频繁的执行 checkpoint 操作，导致性能的抖动。

• 重做日志文件几个相关的参数：

- innodb_log_file_size：指定每个重做日志文件的大小
- innodb_log_files_in_group：每个日志文件中重做日志文件的数量
- innodb_mirrored_log_groups：日志镜像文件组的数量
- innodb_log_group_home_dir：日志文件组所在路径

• 重做日志文件和二进制文件的区别：

- 二进制日志文件记录所有与 MYSQL 数据库有关的记录，而重做日志文件只记录了关于 InnoDB 的事务日志
- 二进制日志文件记录的是关于一个事务的具体操作内容，而重做日志文件记录了 InnoDB 关于每个页的更改的物理情况
- 二进制日志文件仅在事务提交前进行提交，而重做日志文件在事务进行的过程中，却有不断重做日志条目被写入

本文涉及到的相关配置参数

• log_error：错误日志文件所在位置
• long_query_time：慢查询日志阈值，默认是 10 s
• long_slow_queries：是否开启慢查询日志记录，默认是 off
• long_queries_not_using_indexes：是否开启查询没有使用索引时记录到慢查询日志中，默认为 0
• long_throttle_queries_not_using_indexes：表示每分钟容许记录到慢查询日志中未使用索引的 SQL 个数
• log_output：指定慢查询日志的输出格式，默认是 FILE，可以将其设置为 TABLE，就可以 slow_log 表中查询了
• long_query_io：将超过指定逻辑 IO 次数的 SQL 语句记录到慢查询日志中
• general_log：是否开启查询日志
• general_log_file：查询日志记录文件
• log-bin：是否开启 binary log
• max_binlog_size：指定了二进制文件的最大值，如果超过该值，则产生新的二进制文件
• binlog_cache_size：该参数记录二进制文件缓存的大小
• binlog_cache_use：记录使用缓存写二进制日志的次数
• binlog_cache_disk_use：记录了使用临时文件写二进制日志的次数
• sync_binlog：表示二进制文件每次写缓存多少次被同步到磁盘。
• binlog_do_db：表示需要写入哪些库的二进制文件
• binlog_ingore_db：忽略哪些库的二进制文件
• log_slave_update：是否主动将 master 取得的并执行的二进制日志写入 slave 角色中，高可用情况下，该参数必须开启
• innodb_support_xa: 该参数可以确保二进制日志与 InnoDb 存储数据文件的同步
• binlog_format：记录二进制日志的格式，5.1 版本引入，动态参数，可以运行时修改。
• socket：套接字文件的存储位置
• pid_file：pid 文件存放位置
• innodb_data_file_path：定义 innodb 表空间文件位置
• innodb_file_per_table：设置是否每个表都会产生一个独立的表空间
• innodb_flush_log_at_trx_commit：是否在事务提交时将重做日志缓存文件写入磁盘文件
• innodb_log_file_size：指定每个重做日志文件的大小
• innodb_log_files_in_group：每个日志文件中重做日志文件的数量
• innodb_mirrored_log_groups：重做日志镜像文件组的数量
• innodb_log_group_home_dir：重做日志文件组所在路径