3.1 InnoDB存储引擎之文件(参数文件、日志文件等)

3.1 参数文件

告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。

3.1.1 什么是参数

• 数据库参数可以看成K/V键值对，可以通过SHOW VARIABLES查看数据库的所有参数，也可以通过LIKE来过滤，5.1版本开始，还可以通过information_schema架构下的GLOBAL_VARIABLES试图来查找。

3.1.2 参数类型

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

1. 动态（dynamic）参数
2. 静态（static）参数

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

	SET
	| [global | session ] system_var_name= expr
	| [@@global. l @@session. l @@]system_var_name= expr

• 有些动态参数只能在会话中进行修改，如 autocommit; 而有些参数修改完后，在整个实例生命周期中都会生效，如 binlog cache size; 而有些参数既可以在会话中又可以在整个实例的生命周期内生效，如 read buffer size。

3.2 日志文件

• 用来记录 MySQL 实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
• Mysql最常见的日志有

1. 错误日志文件（error log）
2. 二进制文件（binlog）
3. 慢查询文件（slow query log）
4. 查询日志（log）

3.2.1 错误日志

用户可以通过命令 SHOW VARIABLES LIKE 'log error'来定位该文件，如∶

3.2.2 慢查询文件

• 慢查询日志（slow log）可帮助 DBA 定位可能存在问题的 SQL 语句，从而进行 SQL 语句层面的优化。例如，可以在 MySQL 启动时设一个阈值，将运行时间超过该值的所有 SOL 语句都记录到慢查询日志文件中。DBA 每天或每过一段时间对其进行检查，确认是否有 SOL语句需要进行优化。该阈值可以通过参数 long_query_time 来设置，默认值为 10，代表 10秒。
  在默认情况下，MySQL 数据库并不启动慢查询日志，用户需要手工将这个参数设为ON

• 这里有两点需要注意。首先，设置 long_query_time 这个阈值后，MySQL 数据库会记录运行时间超过该值的所有 SQL 语句，但运行时间正好等于 long query _time 的情况并不会被记录下。也就是说，在源代码中判断的是大于long_query_time，而非大于等于。其次，从 MySQL 5.1 开始，long_query_ time 开始以微秒记录 SQL 语句运行的时间，之前仅用秒为单位记录。

• 另一个和慢查询日志有关的参数是 log_queries_not_using_indexes，如果运行的 SQL语句没有使用索引，则 MySQL 数据库同样会将这条 SQL 语句记录到慢查询日志文件。首先确认打开了log_queries_not_using_indexes∶

• MySQL 5.6.5 版本开始新增了一个参数 log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到 slow log 的且未使用索引的 SQL 语句次数。该值默认为 0，表示没有限制。在生产环境下，若没有使用索引，此类 SOL 语句会频繁地被记录到 slow log，从而导致 slow log 文件的大小不断增加，故 DBA 可通过此参数进行配置。

• MySOL 数据库提供的 mysgldumpslow 命令，可以很好地帮助 DBA 解决该问题。
  
  
  

• MySQL 5.1 开始可以将慢查询的日志记录放入一张表中，这使得用户的查询更加方便和直观。慢查询表在 mysql架构下，名为 slow_log，其表结构定义如下∶
  

• 参数 log output 指定了慢查询输出的格式，默认为 FILE，可以将它设为 TABLE，然后就可以查询 mysql 架构下的 slow_log 表了，如∶
  
  

• 参数 log_output是动态的，并且是全局的，因此用户可以在线进行修改。

• 查看 slow_log 表的定义会发现该表使用的是 CSV 引擎，对大数据量下的查询效率可能不高。用户可以把 slow_log 表的引擎转换到 MyISAM，并在 start_time 列上添加索引以进一步提高查询的效率。但是，如果已经启动了慢查询，将会提示错误∶
  

• 不能忽视的是，将 slow_log 表的存储引擎更改为 MyISAM后，还是会对数据库造成额外的开销。不过好在很多关于慢查询的参数都是动态的，用户可以方便地在线进行设置或修改。

• 用户可以通过额外的参数 long_query_io 将超过指定逻辑 IO 次数的 SQL 语句记录到 slow log 中。该值默认为 100，即表示对于逻辑读取次数大于100 的 SQL 语句，记录到 slow log 中。为了兼容原MySQL 数据库的运行方式，还添加了参数 slow_query_type，用来表示启用 slow log 的方式，可选值为∶
  □ 0表示不将 SQL 语句记录到 slow log
  □ 1表示根据运行时间将 SQL 语句记录到 slow log
  □ 2表示根据逻辑 IO 次数将 SQL 语句记录到 slow log
  □ 3 表示根据运行时间及逻辑 IO 次数将 SQL 语句记录到 slow log

3.2.3 查询日志

• 查询日志记录了对MySQL数据库的请求信息（无论是否成功执行）
• 默认文件名为：主机名.log, tail来查询这个日志
• 
• 从MySQL 5.1开始，可以将查询日志放入mysql架构下的general_log表中

3.2.4 二进制文件

• 二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT和 SHOW 这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。例如∶
  
  
  
• 二进制文件主要有以下的功能：

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

• 二进制文件默认不启动，开启此功能也仅影响1%的性能。
  socket 文件∶ 当用 UNIX 域套接字方式进行连接时需要的文件。 pid 文件∶ MySQL 实例的进程 ID 文件。
• 以下配置文件的参数影响着二进制日志记录的信息和行为∶

4. max_binlog_size
5. binlog_cache_size
6. sync_binlog
7. binlog-do-db
8. binlog-ignore-db
9. log-slave-update
10. binlog_format

• 参数 max_binlog_size 指定了单个二进制日志文件的最大值。
• 当使用事务的表存储引擎（如 InnoDB 存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由 binlog_cache_size 决定，默认大小为 32K。此外，binlog_cache_size 是基于会话（session）的，也就是说，当个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的 binlog_cache_size时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看。Binlog_cache_use 记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use 记录了使用临时文件写二进制日志的次数。
• 在默认情况下，二进制日志并不是在每次写的时候同步到磁盘。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来问题。参数 sync_binlog= 【N】 表示每写缓冲多少次就同步到磁盘。如果将 N设为1，即 sync_binlog=1 表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。sync_binlog 的默认值为 0，如果使用InnoDB 存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为 ON。
  但是，即使将 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 表示需要写入或忽略写入哪些库的日志。默认为空，表示需要同步所有库的日志到二进制日志。
• 如果当前数据库是复制中的 slave 角色，则它不会将从 master 取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。如果需要搭建 master=>slave=>slave 架构的复制，则必须设置该参数。
• binlog_format参数十分重要，它影响了记录二进制日志的格式。
  这其实也是因为二进制日志文件格式的关系，如果使用 READ COMMITTED 的事务隔离级别（大多数数据库，如 Oracle，Microsoft SOL Server 数据库的默认隔离级别），会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。
• MySQL 5.1 开始引入了binlog_format参数，该参数可设的值有 STATEMENT、ROW和 MIXED。
  （1）STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑 SQL 语句。
  （2）在 ROW 格式下，二进制日志记录的不再是简单的 SQL 语句了，而是记录表的行更改情况。从 MySOL 5.1 版本开始，如果设置了binlog format 为 ROW，可以将InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。
  （3）在 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 参数还有对于存储引擎的限制。
  
• 在通常情况下，我们将参数 binlog_format 设置为 ROW，这可以为数据库的恢复和复制带来更好的可靠性。但是不能忽略的一点是，这会带来二进制文件大小的增加。
• 要查看二进制日志文件的内容，必须通过 MySQL 提供的工具 mysqlbinlog。对于 STATEMENT 格式的二进制日志文件，在使用 mysqlbinlog 后，看到的就是执行的逻辑 SQL 语句。
  

3.3 套接字文件

在 UNIX系统下本地连接 MySQL 可以采用 UNIX 域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可由参数 socket 控制。一般在/tmp目录下，名为mysql.sock∶

3.4 pid 文件

当 MySQL 实例启动时，会将自己的进程 ID 写人一个文件中——该文件即为 pid文件。该文件可由参数 pid file控制，默认位于数据库目录下，文件名为主机名 .pid∶

3.5 表结构定义文件

• 因为 MySQL 插件式存储引擎的体系结构的关系，MySQL 数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL 都有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。
• frm 还用来存放视图的定义，如用户创建了一个 v_a 视图，那么对应地会产生一个v_ a.frm 文件，用来记录视图的定义。

3.6 InnoDB 存储引擎文件

3.6.1 表空间文件

• InnoDB 采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为 10MB，名为 ibdatal 的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数 innodb_data_file_path 对其进行设置，格式如下∶

innodb_data_file_path=dataflle_specl{;datafile_spec2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性，如∶

[mysq1d]
innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

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

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

3.6.2 重做日志文件

• 在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为 ib_logfileO 和 ib_logfile1的文件。更准确的定义应该是重做日志文件（redo log file）。记录了对于InnoDB 存储引擎的事务日志。
• 当实例或介质失败（media failure）时，重做日志文件就能派上用场，保证数据的完整性。
• 每个InnoDB 存储引擎至少有 1个重做日志文件组（group），每个文件组下至少有 2个重做日志文件，如默认的 ib_logfile0 和ib_logfile1。用户可以设置多个的镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写人的方式运行。+InnoDB 存储引擎先写重做日志文件 1，当达到文件的最后时，会切换至重做日志文件 2，再当重做日志文件 2 也被写满时，会再切换到重做日志文件 1中。
  
• 下列参数影响着重做日志文件的属性∶

1. innodb_log_file_size
2. innodb_log_files_in_group
3. innodb_mirrored_log_groups
4. innodb_log_group_home_dir

• 参数 innodb_log_file_size指定每个重做日志文件的大小。在 InnoDB1.2.x 版本之前，重做日志文件总的大小不得大于等于 4GB，而1.2.x 版本将该限制扩大为了512GB。
• 参数innodb_log_files_in_group 指定了日志文件组中重做日志文件的数量，默认为 2。
• 参数 innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，表示只有一个日志文件组，没有镜像。若磁盘本身已经做了高可用的方案，如磁盘阵列，那么可以不开启重做日志镜像的功能。
• 参数innodb_log_group_home_dir指定了日志文件组所在路径，默认为 ./，表示在 MySQL数据库的数据目录下。
• 
• 重做日志太大影响数据恢复时间，太小可能导致一个事务多次切换重做日志。太小会频繁导致async checkpoint,
  
• 也许有人会问，既然同样是记录事务日志，和之前介绍的二进制日志有什么区别?

5. 首先，二进制日志会记录所有与 MySQL数据库有关的日志记录，包括 InnoDB、MyISAM、Heap 等其他存储引擎的日志。而InnoDB 存储引擎的重做日志只记录有关该存储引擎本身的事务日志。
6. 其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为 STATEMENT还是 ROW，又或者是 MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而 InnoDB 存储引擎的重做日志文件记录的是关于每个页（Page）的更改的物理情况。
7. 此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目（redo entry）被写入到重做日志文件中。

• 重做日志的基本格式
  
• 写入重做日志文件先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地写入日志文件。
  
• 从重做日志缓冲往磁盘写入时，是按 512个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。
• 从日志缓冲写入磁盘上的重做日志文件的条件有

1. 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。
2. 由参数 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 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。