InnoDB(四)-InnoDB文件 | HoleLin's Blog

参考文献

MySQL技术内幕 InnoDB存储引擎

参数文件

当MySQL实例启动时,数据库会先去读一个配置参数文件,用来寻找数据库的各种文件所在位置以及指定某些初始化参数,这些参数通常定义了某种内存结构有多大等.

可以通过命令SHOW VARIABLES来查看数据中的所有参数,也可以通过LIKE来过滤参数名.从MySQL5.1版本开始,可以通过performance_schema架构下的GLOBAL_VARIABLES视图来进行查找.

mysql> select * from global_variables where variable_name like 'innodb_buffer%';
+-------------------------------------+----------------+
| VARIABLE_NAME                       | VARIABLE_VALUE |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)
mysql> show variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)

参数类型

MySQL数据库中参数可以分为两类:
- 动态(dynamic)参数
- 静态(static)参数

动态参数意味着可以在MySQL实例运行中进行更改,静态参数说明在整个实例声明周期内都不得进行更改,就好像是只读的.通过SET命令对动态的参数进行修改.

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

SET read_buffer_size=524288;

GLOBAL和SESSION关键字,它们表明该参数的修改是基于整个实例的生命周期还是当前会话.

日志文件

错误日志(error log)

错误日志文件对MySQL的启动,运行,关闭过程进行了记录.在遇到问题是应该首先查看该文件以便定位问题.

该文件不仅记录了所有的错误信息,也记录了一些警告信息或正确的信息.可以通过SHOW VARIABLES LIKE 'log_error'来定位该文件

mysql> show variables like 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| log_error     | /usr/local/mysql/data/mysqld.local.err |
+---------------+----------------------------------------+
1 row in set (0.01 sec)
mysql> system hostname
192.168.0.107

可以看到错误文件的路径和文件名,在默认情况下错误文件的文件名为服务器的主机名.

当出现MySQL不能正常启动时,第一个必须查找的文件就是错误文件,该文件记录错误信息,能很好的指导用户发现问题.

二进制日志(binlog)

二进制日志记录了对MySQL数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改.

mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/binlog       |
| log_bin_index                   | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000033 |     33869 | No        |
| binlog.000034 |       179 | No        |
| binlog.000035 |     92672 | No        |
| binlog.000036 |       179 | No        |
+---------------+-----------+-----------+


mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> set session binlog_format = statement;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `temp`.`wechat_official_account` ( `app_id`, `name`, `sort`) VALUES ('wxxxxx', 'AI', 2);
Query OK, 1 row affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000091
         Position: 568
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show binlog events in 'binlog.000091'\G
*************************** 1. row ***************************
   Log_name: binlog.000091
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.26, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000091
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000091
        Pos: 156
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 235
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000091
        Pos: 235
 Event_type: Query
  Server_id: 1
End_log_pos: 317
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000091
        Pos: 317
 Event_type: Intvar
  Server_id: 1
End_log_pos: 349
       Info: INSERT_ID=5
*************************** 6. row ***************************
   Log_name: binlog.000091
        Pos: 349
 Event_type: Query
  Server_id: 1
End_log_pos: 537
       Info: use `temp`; INSERT INTO `temp`.`wechat_official_account` ( `app_id`, `name`, `sort`) VALUES ('wxxxxx', 'AI', 2)
*************************** 7. row ***************************
   Log_name: binlog.000091
        Pos: 537
 Event_type: Xid
  Server_id: 1
End_log_pos: 568
       Info: COMMIT /* xid=4800 */
7 rows in set (0.00 sec)

可以通过命令SHOW BINLOG EVENTS可以查看二进制日志中的记录.

如果用户想记录SELECT和SHOW操作,那只能使用查询日志,而不是二进制日志.此外日志还包括了执行数据库更改操作等其他额外信息.总得来说,二进制日志主要有以下作用:
- 恢复(recovery): 某些数据的恢复需要二进制日志,例如,在一个数据库全备文件恢复后,用户可以通过二进制日志进行point-in-time的恢复.
- 复制(replication): 其原理与恢复类似,通过复制和执行二进制日志使主备数据库进行实时同步.
- 审计(audit): 用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入攻击.

`binlog`格式

该参数可设的值有STATEMENT,ROW,MIXED.
STATEMENT格式,二进制日志文件记录的是日志的逻辑SQL语句.
ROW格式,二进制日志记录的不再是简单的SQL语句了,而是记录表的行更改情况.从MySQL5.1版本开始,如果设置了binlog_format为ROW,可以将InnoDB的事务隔离级别设置为READ COMMITTED,以获得更好的并发性.
MIXED格式,默认采用STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式.可能的情况有:
- 表的存储引擎未NDB,此时对表的DML操作都会以ROW格式记录.
- 使用UUID(),USER(),CURRENT_USER()的不确定函数.
- 使用了INSERT DELAY语句.
- 使用了用户自定义函数(UDF).
- 使用了临时表.

`binlog`查看

1	mysqlbinlog -vv --start-position=155 binlog.000089

慢查询日志(`slow query log`)

慢查询日志可以帮助定位存在问题的SQL语句,从而进行SQL语句层面的优化.在MySQL启动时,设置一个阈值,将运行超过这个值的所有SQL语句都会记录到慢查询日志文件中.该阈值可以通过参数long_query_time来设置,默认值为10,单位为秒.

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/192-slow.log |
+---------------------+------------------------------------+
1 row in set (0.03 sec)

需要注意的是:设置long_query_time这个阈值后,MySQL数据库会记录运行时间超过该值的所有SQL语句,但运行时间正好等于long_query_time的情况并不会被记录下来.也就是说,源码中判断的是大于long_query_time,而非大于等于.其次,从MySQL5.1开始以微妙记录SQL运行的时间.

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

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

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

查询日志

查询记录记录了所有对MySQL数据库请求的信息,无论这些请求是否得到登录正确的执行,默认文件名为:主机名.log

mysql> show variables like 'general_log%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           |
| general_log_file | /usr/local/mysql/data/192.log |
+------------------+-------------------------------+
2 rows in set (0.01 sec)

`Socket`文件

在Unix系统下本地连接MySQL可以采用UNIX域套接字方式,这种方式需要一个套接字文件.

mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.00 sec)

`pid`文件

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

mysql> show variables like 'pid_file';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| pid_file      | /usr/local/mysql/data/mysqld.local.pid |
+---------------+----------------------------------------+
1 row in set (0.01 sec)

`InnoDB`存储引擎文件

表空间文件

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

mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

设置innodb_temp_data_file_path参数后,所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中.若设置innodb_file_per_table,则用户可以将每个基于InnoDB存储引擎的表产生一个独立的表空间.独立表空间的命名规则为:表名.ibd.

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

由于设置参数innodb_file_per_table=ON因此产生了单独的ibd独立表空间文件.需要注意的是,这些单独的表空间文件仅存储该表的数据,索引和插入缓冲BITMAP等信息,其余信息还是存放在默认的表空间.

重做日志文件

在默认情况下,在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件.

1
2
3

root@192 data # ls -al|grep ib_log
-rw-r-----    1 _mysql  _mysql  50331648  5  1 18:39 ib_logfile0
-rw-r-----    1 _mysql  _mysql  50331648  5  1 18:38 ib_logfile1

它记录了对于InnoDB存储引擎的事务日志.当实例或介质失败时,重做日志就能排上用场.例如,数据库由于所在主机断电导致实例失败,InnoDB会使用重做日志恢复到断电前的时刻,以此来保证数据的完整性.
每个InnoDB存储引擎至少有一个重做日志文件组,每个文件组下至少有两个重做日志文件.为了得到更高的可靠性,用户可以设置多个镜像日志组.将不同的文件组放在不同的磁盘上,以此提高重做日志的高可用性.
- 在日志组中每个重做文件的大小一致,并以循环写入的方式运行.InnoDB存储引擎先写重做日志文件1,当达到文件的最后时,会切换至重做日志文件2,再当重做日志文件2也被写满时,会在切换到重做文件1中.

mysql> show variables like 'innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.00 sec)