本文将分析构成MySQL数据库和InnoDB存储引擎表的各种类型文件，这写文件包括以下类型：

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

参数文件

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

MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。但是如果MySQL实例在默认的数据库目录下找不到mysql架构，则启动会失败，此时可能在错误日志文件中找到如下内容：

MySQL数据库的参数文件是以文本方式进行存储的，用户可以直接通过一些常用的文本编辑软件进行参数的修改。

什么是参数

简单的说，数据库参数可以看成一个键-值（key-value）对，可以通过命令SHOW VARIABLES查看数据库中的所有参数（推荐），也可以通过LIKE来过滤参数名，还可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，例如：

这里MySQL数据库有一点不同于Oracle数据库：MySQL数据库并没有所谓的隐藏参数。

参数类型

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

动态（dynamic）参数：意味着可以在MySQL实例运行中进行更改。
静态（static）参数：说明在整个实例生命周期内都不得进行更改，就好像时只读的（read only）。

可以通过SET命令对动态参数进行修改，SET的语法如下：

1
2
3

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

这里可以看到global和session关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。有些动态擦拭农户只能在会话中进行修改，如autocommit；而有些参数修改完后，在整个实例生命周期中都会生效，如binlog_cache_seiz；而有些参数既可以在会话中又可以在整个实例的生命周期内生效，如read_buffer_size。

需要注意的是，对变量的全局值进行了修改，在这次的实例生命周期内都有效，但是MySQL实例本身并不会对参数文件中的该值进行修改。也就是说，在下次启动时，MySQL实例还是会读取参数文件。若想在数据库实例下一次启动时该参数还是保留为当前修改的值，那么用户必须去修改参数文件。

日志文件

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

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

错误日志

错误日志文件对MySQL的启动、运行和关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确信息。用户可以通过命令SHOW VARIABLES LIKE 'log_error来定位该文件，可以看到错误文件的路径和文件名，在默认情况下错误文件的文件名为：服务器的主机名.err 。

慢查询日志

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

例如可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中，该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。

在默认情况下，MySQL数据库并不启动慢查询日志，用户需要手动将log_slow_queries这个参数设为ON。

需要注意有两点。

设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但运行时间正好等于long_query_time的情况并不会被记录下来。
从MySQL 5.1 开始，long_query_time开始以微秒记录SQL语句运行的时间，这样可以更精确的记录SQL的运行时间。

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

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

随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到slow log，此时要分析该文件就显得不是那么简单和直观了，这时MySQL数据库提供的mysqldumpslow命令，可以很好的帮助DBA解决该问题。例如希望得到执行时间最长的10条语句，可以运行如下命令：

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

mysql> SHOW CREATE TABLE mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

参数log_output制定了慢查询输出的格式，默认为FILE，可以将它设置为TABLE,然后就可以查询mysql架构下的slow_log表了。参数log_output是动态的，并且是全局的，因此用户可以在线进行修改。

查看slow_log表的定义会发现该表使用的是CSV引擎，对大数据量下的查询效率可能不高。用户可以先关闭慢查询日志，然后把slow_log表的引擎转换到MyISAM，并在start_time列上添加索引以进一步提高查询的效率，再开启慢查询日志参数即可。

MySQL的slow log通过运行时间来对SQL语句进行捕获，这是一个非常有用的优化技巧，但是当数据库的容量较小时，可能因为数据库刚建立，此时非常大的可能是数据全部被缓存在缓冲池中，SQL语句运行的时间可能是非常短的。

InnoDB版本加强了对于SQL语句的捕获方式，在原版MySQL的基础上再slow log中增加了对于逻辑读取（logical reads）和物理读取（physical reads）的统计。这里的物理读取是指从磁盘机性IO读取的次数。逻辑读取包括所有的读取，不管是磁盘还是缓冲池。

用户可以通过额外的参数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

查询日志

查询日志记录了所有对MySQL数据请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log。如查看一个查询日志：

通过上面的查询日志会发现，查询日志甚至记录了Acess denied的请求，即对于未能正确执行的SQL语句，查询日志也会进行记录。同样地，从MySQL 5.1 开始可以将查询日志的记录放入mysql架构下的general_log表中。

二进制日志

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

如果用户像记录SELECT和SHOW操作，那只能使用查询日志，而不是二进制日志。此外，二进制还包括了执行数据库更改操作的时间等其它额外信息。总的来说，二进制日志主要有以下几种作用：

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

通过配置参数log-bin [= name]可以启动二进制日志，如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据所在目录。例如：
这里的bin_log.00001即为二进制日志文件，bin_log.index为二进制的索引文件，用来存储过往产生的二进制日志序号。

二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动，一位内开启这个选型的确会影响性能，但是性能的损失十分有效，开启二进制日志会使性能下降1%，但考虑到可以使用复制和恢复功能时，这些性能损失绝对是可以且应该被接受的。

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

max_binlog_size：指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件，默认大小为1G。
binlog_cache_size：当使用事务的表存储引擎（如InnoDB）时，所有未提交的二进制日志会被记录到一个缓存中去，等该事务提交时直接将缓存中的二进制日志写入二进制日志文件，而该缓存的大小即由binlog_cache_size决定，默认大小为32K。此外该参数是基于会话（session）的，也就是说当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大；但是也不能过小，因为当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中。
sync_binlog：在默认情况下，二进制日志并不是每次在写的时候同步到磁盘，因此当数据所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来问题。参数sync_binlog = [N] 表示每写缓冲多少次就同步到磁盘，如果将N设为1，表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。默认情况下该值为0，代表不开启该参数。但是即使将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：如果当前数据库是复制中的slave角色，则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置该参数。如果需要搭建master=>slave=>slave架构的复制，则必须设置该参数。
binlog_format：该参数影响着记录二进制日志的格式，该值可设置的值有STATEMENT、ROW和MIXED：
- STATEMENT：二进制日志文件记录的是日志的逻辑SQL语句。
- ROW：二进制日志记录的是表的行更改情况，如果设置为该值，可以将InnoDB的事务隔离基本设为READ COMMITED，以获得更好的并发性。
- MIXED：该值情况下MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是一些情况下会使用ROW格式，可能的情况有：
  - 表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。
  - 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT（）等不确定函数。
  - 使用了INSERT DELAY语句。
  - 使用了用户定义函数。
  - 使用了临时表。

此外binlog_format参数还有对于存储引擎的限制：

binlog_format是动态参数，因此可以在数据库运行环境下进行更改。

在通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性，但是不能忽略的一点是，这会带来二进制文件大小的增加，有些语句下ROW格式可能需要更大的容量，如同样UPDATE 100万条语句，STATEMENT情况下，需要200字节的空间；但是ROW情况下则需要大约13MB的空间，所以将参数binlog_format设置为ROW，会对磁盘空间要求有一定的增加，而由于是采用传输二进制日志方式实现的，所以复制的网络开销也有所增加。

二进制日志文件的文件格式为二进制，要查看文件的内容，必须通过MySQL提供的工具mysqlbinlog，并且加上参数-vv可以显示出更详细的内容，如更新的类型。

套接字文件

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

mysql> show variables like 'socket'\G
*************************** 1. row ***************************
Variable_name: socket
        Value: MySQL
1 row in set, 1 warning (0.04 sec)

pid文件

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

mysql> SHOw variables like 'pid_file'\G
*************************** 1. row ***************************
Variable_name: pid_file
        Value: C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-MNHTPI2P.pid
1 row in set, 1 warning (0.00 sec)

表结构定义文件

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

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

InnoDB存储引擎文件

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

表空间文件

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

1	innodb_data_file_path = datafile_spec1[;datafile_spec2]...

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

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

这里将/db/ibdata1和/db/ibdata2两个文件用来组成表空间，若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。同时两个文件的文件名后都跟了属性，表示文件ibdata1的大小为2000MB，文件ibdata2的大小为2000MB，如果用完这2000MB，该文件可以自动增长。

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

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

重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，称其为重做日志文件（redo log file），重做日志文件对于InnoDB存储引擎至关重要，因为他们记录了对于InnoDB存储引擎的事务日志。

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

每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组，将不同的文件组放在不同的磁盘上，以此提供重做日志的高可用性。

在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志1中，如图：

下列参数影响着重做日志文件的属性：

innodb_log_file_size：指定每个重做日志文件的大小，在InnoDB 1.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，导致性能的抖动。

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

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

在InnoDB存储引擎中，对于各种不同的操作有着不同的重做日志格式，虽然各种重做日志的类型不同，但是它们有着基本的格式：

从上图可以看到重做日志条目是由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控制，表示在提交操作时，处理重做日志的方式。

参数innodb_flush_log_at_trx_commit的有效值有0、1、2。

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

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