MySQL技术内幕-InnoDB存储引擎-第三章、文件

文件

一、参数文件

当MySQL启动的时候，需要加载一个配置文件，这些配置文件通常定义了某些内存结构有多大等。

1、什么是参数(键值对)

通过命令show variables查看数据库中的所有参数，也可以通过like过滤到参数名。

2、参数类型

类型

• 动态参数
• 静态参数

动态参数可以在MySQL实例运行过程中进行修改，静态参数说明在整个实例生命周期内不得进行修改，是只读的。
通过set命令对动态的参数值进行修改，set的语法如下：

这里可以看到global和session关键字。它们表名参数的修改是基于会话还是整个实例的生命周期。

二、日志文件

MySQL数据库中常见的日志文件有：

• 错误日志
• 二进制日志
• 慢查询日志
• 查询日志

1、错误日志

该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。
用户可以通过命令show variables like 'log_error’来定位文件。

2、慢查询日志

慢查询可以帮助DBA定位可能存在问题的SQL语句。
可以在MySQL启动的时候设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。可以通过参数long_query_time来设置，默认值为10.

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

• show variables like ‘log_slow_queries’;

运行时间正好等于long_query_time 的情况并不会被记录下，必须大于。

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

• show variables like ‘log_queries_not_using_indexes’;

MySQL5.6.5版本新加了一个参数log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log的且为使用索引的SQL语句次数。

如果用户希望得到执行时间最长的10条SQL语句，可以运行以下命令：

• mysqldumpslow -s al -n 10 david.log

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

参数log_output默认为file，你可以修改成TABLE，然后就可以查到mysql架构下的slow_log表了。如
参数log_output是动态的，是全局的，因此用户可以在线进行修改。

逻辑读取和物理读取

InnoDB加强了对于SQL语句的捕获功能，在原版的MySQL基础上在slow log中增加了对于逻辑读取和物理读取的统计。

• 物理读取：从磁盘进行IO读取的次数
• 逻辑读取包含所有的读取，不管是磁盘还是缓冲池

用户可以通过额外的参数long_query_io即那个超过指定逻辑IO次数的SQL语句记录到slowlog中。

3、查询日志(记录了对数据库请求的信息)

查询日志博客
查询日志记录了对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行，默认文件名称为：

• 主机名.log

4、二进制日志(记录对数据库更改的所有操作，即使update等语句没发生修改，也记录)

redo log是InnoDB特有的，Binlog是在数据库的上层提供的，不仅仅针对InnoDB

二进制日志记录了对MySQL数据库执行更改的所有操作。但是不包括select和show这类操作。如果操作本身没有导致数据库发生变化，那么该操作可能也会写入二进制日志，比如

• update t set a=1 where a=2

如果用户想记录select和show操作，那么只能使用查询日志，而不是二进制日志。
总的来说，二进制日志主要有以下几个作用：

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

• 复制
  通过复制和执行二进制日志使得一台远程的MySQL数据库与一台MySQL数据库进行实时同步。

• 审计
  通过二进制日志中的信息来进行审计，判断是否对数据库进行注入的攻击。

文件名称默认为主机名，后缀名为二进制日志的序列号

三、套接字文件

在UNIX系统下本地链接MySQL数据库可以使用UNIX套接字方式，这种方式需要一个套接字文件，套接字文件可由参数socket进行控制，一般在/tmp目录下，名字为mysql.sock：

• show variables like ‘socket’;

四、pid文件(存放进程ID)

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

五、表结构定义文件(.frm文件)

MySQL数据存储是根据表进行的，每个表会有与之对应的文件，但是不论采用什么引擎，MySQL都有一个以frm为后缀名的文件，存放了表的结构定义。

frm还用来存放视图的定义。

六、InnoDB存储引擎文件

1、表空间文件(设置成多个ibdata文件，通过参数每个表存一个文件名称为表名.ibd)

InnoDB将存储的时间按表空间进行存放的设计，在默认配置下会有一个初始化大小为10MB，名为ibdata1的文件，这个文件就是默认的表空间文件。可以使用参数innodb_data_file_path对其设置，格式如下：

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

这里将/db/ibdata1和/db/ibdata2两个文件用来组成表空间。如果两个为文件位于不同的磁盘，磁盘的负载可能会被平均。同时，两个文件后面都跟了属性，表示ibdata1大小为200M、ibdata2文件大小为200M，用完可以自增长。

如果设置参数innodb_file_per_table，则用户可以将每个给予InnoDB存储引擎的表产生一个独立表空间，独立表空间的命名规则为：表名.ibd

2、重做日志文件(ib_logfile0和ib_logfile1，先写入重做日志缓存，再写入日志文件，重做日志缓冲往磁盘写入的时候，是按照512字节，也就是一个扇区的大小进行写入，因为扇区是写入的最小单位，因此在重做日志的写入过程中不需要有doublewrite。)

在InnoDB存储引擎的数据目录下有两个名为ib_logfile0和ib_logfile1的文件，正准确的说是重做日志文件(redo log file)。

当实例失败的时候，重做日志就会派上用场。例如：数据库由于所在主机断电导致实例失败，InnoDB存储引起会使用重做日志恢复到断电前的时刻，来保证数据的完整性。

每个InnoDB引擎至少有1个日志文件组，每个日志文件组至少有2个重做日志文件，以循环写入的方式，0写满写1，1写满写0…，为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。

几个相关参数：

• innodb_log_file_size：指定重做日志文件大小
• innodb_log_files_in_group:指定日志文件组中重做日志文件的数量
• innodb_mirrored_log_groups：指定日志镜像文件组的数量

重做日志设置大了，恢复时间长，小了会导致切换重做日志文件频繁。

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

下图是重做日志的写入过程：

重做日志缓冲往磁盘写入的时候，是按照512字节，也就是一个扇区的大小进行写入，因为扇区是写入的最小单位。可以保证写入必定是成功的，因此在重做日志的写入过程中不需要有doublewrite。

重做日志写入磁盘的情况：

• 每秒钟主线程会将重做日志缓冲写入磁盘的重做日志文件，不论事务是否提交
• 另外一个触发由参数innodb_flush_log_at_trx_commit控制，表示在提交操作的时候，处理重做日志的方式。