mysql学习之InnoDB（二）

mysql学习之InnoDB（二）

写在前面

本篇博客主要记录一下innoDB存储引擎中比较常见和重要得文件学习。这些文件主要是分为以下几类：

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

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

• socket文件：当用UNIX域套接字方式进行连接时需要的文件。

• pid文件：MySQL实例的进程ID文件。

• MySQL表结构文件：用来存放MySQL表结构定义文件。

• 存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会 有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等 数据。本章主要介绍与InnoDB有关的存储引擎文件。

主要介绍日志文件、Mysql表结构文件和存储引擎文件。

日志文件

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

• 错误日志（error log）

• 二进制日志（binlog）

• 慢查询日志（slow query log）

• 查询日志（log）

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录 了所有的错误信息，也记录一些警告信息或正确的信息。用户可以通过 命令**SHOW VARIABLES LIKE’log_error’**来定位该文件，操作如下图：

我们可以通过log_error的value值定位到错误日志的位置，找到并查看其内容。其内容如下图所示：

它分为 error system warning Note ，分别代表错误信息、系统信息、告警信息和标注信息。

慢查询日志

慢 查询日志（slow log）可帮助DBA定位可能存在问题的SQL语句，从而 进行SQL语句层面的优化。

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

我们可以通过mysql> set global slow_query_log=‘ON’ 来临时启动慢查询sql监控功能，数据库重启失效。操作如下图所示：

这里有一点需要注意。设置long_query_time这个阈值后，MySQL 数据库会记录运行时间超过该值的所有SQL语句，但运行时间正好等于 long_query_time的情况并不会被记录下。也就是说，在源代码中判断的 是大于long_query_time，而非大于等于。

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

可以看到默认该功能是关闭的，因为会影响性能。

我们可以通过set global log_queries_not_using_indexes=‘ON’;

我们可以通过慢查询日志来找出有问题的SQL语句，对其进行优化。然 而随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL 查询被记录到了慢查询日志文件中，此时要分析该文件就显得不是那么 简单和直观的了。而这时MySQL数据库提供的mysqldumpslow命令，可 以很好地帮助我们解决该问题。

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

我们现在通过慢查询日志来查看里面的内容，具体内容如下图所示：

可以看到每一个慢查询记录包括了时间节点和用户信息以及查询花费的时间、花费再锁上面的时间、sql发送行数等等

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否 得到了正确的执行。默认文件名为：主机名.log。我们先检查本地环境的查询日志是不是打开了，操作如下图所示：

接下来我们执行几条sql，看一下查询日志回怎样记录，具体如下图所示：

可以看到日志中记录了所有的sql请求。

二进制日志

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

我们改变了两次同一条数据，发现binlog.00001日志的位置发生了变化，从而推断出该日志肯定记录了这两次变化。那我们再试试select 和 show操作。

可以看到，binlog并没有任何变化。也就说binglog 日志不会记录不改变数据库行为的操作，但是上面说的查询日志会记录。

可以通过命令SHOW BINLOG EVENT可以看出在二进制日志中的确进 行了记录。

总的来说，二进制日志主要有以下几种作用。

• 恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个 数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的 恢复。

• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日
  志使一台远程的MySQL数据库（一般称为slave或standby）与一台 MySQL数据库（一般称为master或primary）进行实时同步（mysql主从或者读写分离的时候的一种同步方案）。

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

二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。 可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不 错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据 MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%。但 考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损 失绝对是可以且应该被接受的。
以下配置文件的参数影响着二进制日志记录的信息和行为：

• max_binlog_size

• binlog_cache_size

• sync_binlog

• binlog-do-db

• binlog-ignore-db

• log-slave-update

• binlog_format

参数max_binlog_size指定了单个二进制日志文件的最大值，如果超过该 值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。

从 MySQL 5.0开始的默认值为1 073 741 824，代表1 G（在之前版本中 max_binlog_size默认大小为1.1G）。

当使用事务的表存储引擎（如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的状态，可以判断当前binlog_cache_size的设置是否合适。

Binlog_cache_use记录了使用缓冲 写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二 进制日志的次数。具体操作如下图所示：

在默认情况下，二进制日志并不是在每次写的时候同步到磁盘（用户可 以理解为缓冲写）。因此，当数据库所在操作系统发生宕机时，可能会 有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来 问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将 N设为1，即sync_binlog=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存储引擎数据文件的同步。

如果当前数据库是复制中的slave角色，则它不会将从master取得并执行 的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。如果需要搭建master=＞slave=＞slave架构的复制，则 必须设置该参数。

binlog_format参数十分重要，它影响了记录二进制日志的格式。在 MySQL 5.1版本之前，没有这个参数。所有二进制文件的格式都是基于 SQL语句（statement）级别的，因此基于这个格式的二进制日志文件的 复制（Replication）和Oracle的逻辑Standby有点相似。同时，对于复制 是有一定要求的。如在主服务器运行rand、uuid等函数，又或者使用触 发器等操作，这些都可能会导致主从服务器上表中数据的不一致（not sync）。另一个影响是，会发现InnoDB存储引擎的默认事务隔离级别是 REPEATABLE READ。这其实也是因为二进制日志文件格式的关系， 如果使用READ COMMITTED的事务隔离级别（大多数数据库，如 Oracle，Microsoft SQL Server数据库的默认隔离级别），会出现类似丢 失更新的现象，从而出现主从数据库上的数据不一致。

MySQL 5.1开始引入了binlog_format参数，该参数可设的值有 STATEMENT、ROW和MIXED。

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

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而 是记录表的行更改情况。基于ROW格式的复制类似于Oracle的物理 Standby（当然，还是有些区别）。同时，对上述提及的Statement格式 下复制的问题予以解决。从MySQL 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参数还有对于存储引擎的限制，如表3-1所示。

在通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库 的恢复和复制带来更好的可靠性。但是不能忽略的一点是，这会带来二 进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。 比如我们有两张一样的表，大小都为100W，分别执行UPDATE操作：

在binlog_format格式为STATEMENT的情况下，执行 UPDATE语句后二进制日志大小只增加了200字节（306-106）。如果使 用ROW格式，同样对t2表进行操作，在ROW格式下竟然需要13 782 094字 节，二进制日志文件的大小差不多增加了13MB，要知道t2表的大小也 不超过17MB。而且执行时间也有所增加（这里我设置了 sync_binlog=1）。这是因为这时MySQL数据库不再将逻辑的SQL操作记 录到二进制日志中，而是记录对于每行的更改。

二进制日志文件的文件格式为二进制（好像有点废话），不能像错误日 志文件、慢查询日志文件那样用cat、head、tail等命令来查看。要查看 二进制日志文件的内容，必须通过MySQL提供的工具mysqlbinlog。

我们运行了一个简单的update语句，可以看到binlog里面的变化如下图所示：

第一行表示日志名称，第二行是位置，第三行是事件类型，第四行是服务id，第五行是日志结束位置，第六行是备注信息。

从1960字节开始，到2039字节代表了是设置了当前会话是anonymous;这种就是row格式的。

我们可以将当前会话切换成statement模式再试试，操作如下：

可以看到，它现在变成了记录了运行的这个sql语句。

InnoDB存储引擎文件

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

表空间文件

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

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

上图表示本地库开启了独立表空间的功能。

下图表示我们新建了一个名为zttest的数据库的一张表t的ibd文件。

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

重做日志文件

在默认情况下，在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_log_file_size

• innodb_log_files_in_group

• innodb_mirrored_log_groups

• 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数据库的数据目录下。

上图表示本机的重做日志组的一些配置参数。

重做日志文件的大小设置对于InnoDB存储引擎的性能有着非常大的影 响。一方面重做日志文件不能设置得太大，如果设置得很大，在恢复时 可能需要很长的时间；另一方面又不能设置得太小了，否则可能导致一 个事务的日志需要多次切换重做日志文件。此外，重做日志文件太小会 导致频繁地发生async checkpoint，导致性能的抖动。例如，用户可能会 在错误日志中看到如下警告信息：

InnoDB:ERROR:the age of the last checkpoint is 9433645，InnoDB:which exceeds the log group capacity 9433498。这是因 为重做日志有一个capacity变量，该值代表了最后的检查点不能超过这 个阈值，如果超过则必须将缓冲池（innodb buffer pool）中脏页列表 （flush list）中的部分脏数据页写回磁盘，这时会导致用户线程的阻 塞。

也许有人会问，既然同样是记录事务日志，和之前介绍的二进制日志有 什么区别？

首先，二进制日志会记录所有与MySQL数据库有关的日志记录，包括 InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎 的重做日志只记录有关该存储引擎本身的事务日志。

其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为 STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事 务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做 日志文件记录的是关于每个页（Page）的更改的物理情况。

此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交， 即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不 断有重做日志条目（redo entry）被写入到重做日志文件中。

在InnoDB存储引擎中，对于各种不同的操作有着不同的重做日志格 式。到InnoDB 1.2.x版本为止，总共定义了51种重做日志类型。虽然各 种重做日志的类型不同，但是它们有着基本的格式，表3-2显示了重做 日志条目的结构：

从表3-2可以看到重做日志条目是由4个部分组成：

• redo_log_type占用1字节，表示重做日志的类型

• space表示表空间的ID，但采用压缩的方式，因此占用的空间可能小 于4字节

• page_no表示页的偏移量，同样采用压缩的方式

• redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的 函数进行解析

在上一篇博客中已经提到，写入重做日志文件的操作不是直接写，而是先写 入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地 写入日志文件。下图很好地诠释了重做日志的写入过程。

从重做日志缓冲往磁盘写入时，是按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数据库发生宕机而操作系统及服 务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系 统缓存中，当恢复时同样能保证数据不丢失。

总结

这篇文章主要介绍了与MySQL数据库相关的一些文件，并了解了文件可以分为 MySQL数据库文件以及与各存储引擎相关的文件。与MySQL数据库有 关的文件中，错误文件和二进制日志文件非常重要。当MySQL数据库 发生任何错误时，DBA首先就应该去查看错误文件，从文件提示的内容 中找出问题的所在。当然，错误文件不仅记录了错误的内容，也记录了 警告的信息，通过一些警告也有助于DBA对于数据库和存储引擎进行优 化。

二进制日志的作用非常关键，可以用来进行point in time的恢复以及复制 （replication）环境的搭建。因此，建议在任何时候时都启用二进制日 志的记录。

从MySQL 5.1开始，二进制日志支持STATEMENT、ROW、 MIX三种格式，这样可以更好地保证从数据库与主数据库之间数据的一 致性。

志文件**非常重要。当MySQL数据库 发生任何错误时，DBA首先就应该去查看错误文件，从文件提示的内容 中找出问题的所在。当然，错误文件不仅记录了错误的内容，也记录了 警告的信息，通过一些警告也有助于DBA对于数据库和存储引擎进行优 化。

二进制日志的作用非常关键，可以用来进行point in time的恢复以及复制 （replication）环境的搭建。因此，建议在任何时候时都启用二进制日 志的记录。

从MySQL 5.1开始，二进制日志支持STATEMENT、ROW、 MIX三种格式，这样可以更好地保证从数据库与主数据库之间数据的一 致性。

最后介绍了和InnoDB存储引擎相关的文件，包括表空间文件和 重做日志文件。表空间文件是用来管理InnoDB存储引擎的存储，分为 共享表空间和独立表空间。重做日志非常的重要，用来记录InnoDB存 储引擎的事务日志，也因为重做日志的存在，才使得InnoDB存储引擎 可以提供可靠的事务。