MySQL技术内幕读书笔记二、MySQL文件类型

一、文件

构成MySQL数据库和InnoDB存储引擎表的各种类型文件。

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

1.1　参数文件

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

1.1.1　什么是参数

可以把数据库参数看成一个键/值（key/value）对。

可以通过命令SHOW VARIABLES查看数据库中的所有参数，也可以通过LIKE来过滤参数名。

mysql＞SELECT*FROM
-＞GLOBAL_VARIABLES
-＞WHERE VARIABLE_NAME LIKE'innodb_buffer%'\G;
***************************1.row***************************
VARIABLE_NAME:INNODB_BUFFER_POOL_SIZE
VARIABLE_VALUE:1073741824
1 row in set(0.00 sec)

mysql＞SHOW VARIABLES LIKE'innodb_buffer%'\G;
***************************1.row***************************
Variable_name:innodb_buffer_pool_size
Value:1073741824
1 row in set(0.00 sec)

无论使用哪种方法，输出的信息基本上都一样的，只不过通过视图GLOBAL_VARIABLES需要指定视图的列名。推荐使用命令SHOW VARIABLES，因为这个命令使用更为简单，且各版本的MySQL数据库都支持。

1.1.2　参数类型

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

• 动态（dynamic）参数
• 静态（static）参数

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

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

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

mysql＞SET read_buffer_size=524288;
Query OK,0 rows affected(0.00 sec)
mysql＞SELECT@@session.read_buffer_size\G;
***************************1.row***************************
@@session.read_buffer_size:524288
1 row in set(0.00 sec)
mysql＞SELECT@@global.read_buffer_size\G;
***************************1.row***************************
@@global.read_buffer_size:2093056
1 row in set(0.00 sec)

1.2　日志文件

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

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

这些日志文件可以帮助DBA对MySQL数据库的运行状态进行诊断，从而更好地进行数据库层面的优化。

1.2.1　错误日志

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

mysql＞SHOW VARIABLES LIKE'log_error'\G;
***************************1.row***************************
Variable_name:log_error
Value:/mysql_data_2/stargazer.log
1 row in set(0.00 sec)
mysql＞system hostname
stargazer

当出现MySQL数据库不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了错误信息，能很好地指导用户发现问题。当数据库不能重启时，通过查错误日志文件可以得到如下内容：

[root@nineyou0-43 data]#tail-n 50 nineyou0-43.err
090924 11:31:18 mysqld started
090924 11:31:18 InnoDB:Started;log sequence number 8 2801063331
090924 11:31:19[ERROR]Fatal error:Can't open and lock privilege tables:Table'mysql.host'doesn't exist
090924 11:31:19 mysqld ended

错误日志文件提示了找不到权限库mysql，所以启动失败。有时用户可以直接在错误日志文件中得到优化的帮助，因为有些警告（warning）很好地说明了问题所在。

1.2.2　慢查询日志

慢查询日志（slow log）可帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。例如，可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。

该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。

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

mysql＞SHOW VARIABLES LIKE'long_query_time'\G;
***************************1.row***************************
Variable_name:long_query_time
Value:10.000000
1 row in set(0.00 sec)
mysql＞SHOW VARIABLES LIKE'log_slow_queries'\G;
***************************1.row***************************
Variable_name:log_slow_queries
Value:ON
1 row in set(0.00 sec)

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

mysql＞SHOW VARIABLES LIKE'log_queries_not_using_indexes'\G;
***************************1.row***************************
Variable_name:log_queries_not_using_indexes
Value:ON
1 row in set(0.00 sec)

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

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

#Time:111227 23:49:16
#User@Host:root[root]@localhost[127.0.0.1]
#Query_time:6.081214 Lock_time:0.046800 Rows_sent:42 Rows_examined:727558 Logical_reads:91584 Physical_reads:19
use tpcc;
SET timestamp=1325000956;
SELECT orderid,customerid,employeeid,orderdate
FROM orders
WHERE orderdate IN
(SELECT MAX(orderdate)
FROM orders
GROUP BY(DATE_FORMAT(orderdate,'%Y%M'))
);

该子查询的逻辑读取次数是91584次，物理读取为19次。从逻辑读与物理读的比例上看，该SQL语句可进行优化。

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

1.2.3　查询日志

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

[root@nineyou0-43 data]#tail nineyou0-43.log
090925 11:00:24 44 Connect zlm@192.168.0.100 on
44 Query SET AUTOCOMMIT=0
44 Query set autocommit=0
44 Quit
090925 11:02:37 45 Connect Access denied for user'root'@'localhost'(using password:NO)
090925 11:03:51 46 Connect Access denied for user'root'@'localhost'(using password:NO)
090925 11:04:38 23 Query rollback

通过上述查询日志会发现，查询日志甚至记录了对Access denied的请求，即对于未能正确执行的SQL语句，查询日志也会进行记录。

1.2.4　二进制日志

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

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

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

通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录（datadir），如：

mysql＞show variables like'datadir';
+---------------+----------------------------+
|Variable_name|Value|
+---------------+----------------------------+
|datadir|/usr/local/mysql/data/|
+---------------+----------------------------+
1 row in set(0.00 sec)

1.3　套接字文件

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

mysql＞SHOW VARIABLES LIKE'socket'\G;
***************************1.row***************************
Variable_name:socket
Value:/tmp/mysql.sock
1 row in set(0.00 sec)

1.4　pid文件

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

mysql＞show variables like'pid_file'\G;
***************************1.row***************************
Variable_name:pid_file
Value:/usr/local/mysql/data/xen-server.pid
1 row in set(0.00 sec)

1.5　表结构定义文件

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

1.6　InnoDB存储引擎文件

1.6.1　表空间文件

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

innodb_data_file_path=datafle_spec1[;datafle_spec2]...

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

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

这里将/db/ibdata1和/dr2/db/ibdata2两个文件用来组成表空间。若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。

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

mysql＞SHOW VARIABLES LIKE'innodb_file_per_table'\G;
***************************1.row***************************
Variable_name:innodb_file_per_table
Value:ON
1 row in set(0.00 sec)
mysql＞system ls-lh/usr/local/mysql/data/member/*
-rw-r-----1 mysql mysql 8.7K 2009-02-24/usr/local/mysql/data/member/Profile.frm
-rw-r-----1 mysql mysql 1.7G 9月25 11:13/usr/local/mysql/data/member/Profile.ibd

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

1.6.2　重做日志文件

在默认情况下，在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存储引擎的性能有着非常大的影响。一方面重做日志文件不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间；另一方面又不能设置得太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。

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

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

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

重做日志条目是由4个部分组成：

• redo_log_type占用1字节，表示重做日志的类型
• space表示表空间的ID，但采用压缩的方式，因此占用的空间可能小于4字节
• page_no表示页的偏移量，同样采用压缩的方式
• redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后按照一定的条件顺序地写入日志文件。

1.7　小结

文件可以分为MySQL数据库文件以及与各存储引擎相关的文件。

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

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

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