innodb所有文件分析

1.innodb参数

show variable like '%pool_size%'和SELECT * from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME like '%pool_size%'结果一样。

1.1参数分为静态参数和动态参数 

静态参数在运行中是不可设置的。

动态参数可设置通过set global或session vaiable_name=expr;

global 表明参数的修改基于整个实例的生命周期如binlog_cache_size，session表示基于当前会话如autocommit。

有些可以在会话和整个实例的生命周期都分别有效如read_buffer_size.

set SESSION read_buffer_size=1310720;
set global read_buffer_size=13107200;
SELECT @@session.read_buffer_size;
show  global VARIABLES like 'read_buffer_size';

需要注意的是动态参数的修改并不会修改文件中的配置参数，所以重新启动后不会动态参数重设会失效。

所以重启数据库后

SELECT @@session.read_buffer_size;
show  global VARIABLES like 'read_buffer_size';都会恢复初始值。

2.错误日志文件

查看文件位置：show VARIABLES like 'log_error'。默认是服务器名作为文件名。

[mysqld]

log-error[=file_name]选项来开启mysql错误日志

3.慢查询日志文件

超过多少秒记录到慢日志中：show VARIABLES like 'long_query_time';
查看是否开启了慢日志查询及日志路径：show VARIABLES like '%slow_query_log%';需要设置为ON才会生成路径中的文件。
如果sql中没有使用索引，是否记录到慢日志：show VARIABLES like 'log_queries_not_using_indexes%';
每分钟允许没使用索引的sql的数量记录到慢日志中:show VARIABLES like 'log_throttle_%';

1.如何使用mysqldumpslow分析慢日志？

查询执行时间最长的十条sql:mysqldumpslow -s al -n 10 文件名

2.便捷查看慢查询

查看慢查询输出格式：show  VARIABLES like 'log_output'。

默认是FILE，修改为TABLE.执行set global log_output='TABLE'

执行各种慢sql.

测试sql可用：SELECT SLEEP(12)

然后SELECT * from mysql.slow_log可查出这条慢sql.

3.InnoSQL加强了SQL语句的捕获方式。

在slow log中增加了逻辑读取和物理读取的统计。

物理读取；从磁盘进行IO读取的次数

逻辑读取：包含所有的读取，即磁盘和缓冲池都有统计

slow_query_type用来表示启用slow log 方式，可取值0,1,2,3

0：不记录Sql;

1:根据运行时间记录

2：根据逻辑IO次数记录

3：根据运行时间和逻辑IO次数记录

show VARIABLES like 'slow_query_type';

long_query_io表示超过指定IO次数则记录到slow log
show VARIABLES like 'long_query_io';

4.查询日志

对所有数据库请求的信息（无论失败或成功）和未正确执行的sql进行记录

默认文件名：主机名.log

查询日志的启用状态查看：show global variables like "%genera%";

也可以通过SELECT * from mysql.general_log查询日志记录，如果想要将日志记录到表，要设置日志输出到表的格式。

即执行：set GLOBAL log_output='TABLE';

启用日志：在my.ini中[mysqld]里面添加 log="E:/phpStudy/MySQL/data/access.log"，然后

 set GLOBAL  general_log='ON';

重启后，执行sql查询语句，然后打开access.log，就可以看到所有的查询操作。

注意查询日志增长速度很快，一般只在调试时开启，调试完后关闭。想要删除access.log需要先禁用查询日志

 

关闭查询日志：set GLOBAL  general_log='OFF';

5.二进制日志

二进制文件记录对数据库操作执行更改的操作如drop ,create,update,insert,delete.但不包括select,show之类不更改的操作。

即便更改操作实际上没有影响任何记录，也会记录到日志。

作用有：恢复（某些数据的恢复需要二进制文件，如在全备份后进行point-in-time恢复 ），复制（配置主从数据库后，主从同步即复制），审计（判断是否有数据库注入攻击）

开启二进制日志：在my.ini中[mysqld]里面添加log-bin=mysql-bin（mysql-bin是文件名，可任意取）

重启后执行show MASTER status;

mysql-bin.000001是show MASTER status结果中File字段的内容。这个文件所在目录通过show VARIABLES like 'datadir'可查看。

若在linux环境可在mysql命令行中查看文件用system命令，如：mysql>system ls /usr/local/mysql/data

查看二进制文件内容mysql提供了 mysqlbinlog命令。

查看数据库修改操作记录：show binlog events in 'mysql-bin.000001';

执行update操作，如修改第一条记录，并且执行两次，很明显第二次并不会真正修改记录,但也会记录到mysql-bin.000001日志中。

如：update msb_goods set goods_sn= 'ECS6885859' limit 1。

建议开启二进制日志，因为有复制和point-in-time恢复功能,且性能不会太大影响。如果想记录show,select这类的普通查询可以启用access.log,通常情况下启用slow log就足够了。

以下配置参数会影响二进制文件的信息和行为：

max_binlog_size:最大日志文件大小。超过则创建新文件

binlog_cache_size：默认32K。innodb的未提交事务产生的二进制日志先缓存，事务提交后写入到二进制文件件，binlog_cache_size基于会话，所以不能设置太大，若太小，如果事务的记录大于binlog_cache_size会把缓存中的日志写入到临时文件，会降低性能，所以要小心设置。判断设置的值是否合适可参考下面两个值
查看从缓冲写入到日志文件的次数 ：    show global status like 'binlog_cache_use';
查看从临时文件写入到日志文件的次数：show global status like 'binlog_cache_disk_use';

sync_binlog  ：表示每写多少次缓冲同步到磁盘。1表示不写缓冲，直接写入日志文件，如果使用innodb复制并具有高可用性，建议设置为on。但对数据库的IO系统性能肯定有些影响。

即便设置为1，也会有一个问题：未提交事务也会写入到文件，若commit之前宕机，这是不能回滚的。这可以通过设置support_innodb_xa=1解决。support_innodb_xa与xa有关，但binlog中未提交的事务也会在redo log中存在，而redo log中未提交事务是可以被回滚的。

binlog_do_db：表示需要写入哪些库的日志，空表示需要同步所有库的日志到二进制日志文件

binlog_ignore_db：表示忽略写入哪些库的日志。

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

log_slave_update

binlog_format：show VARIABLES like 'binlog_format'可以看到默认是statement。可选值有ROW（记录更改的详细信息，比statement记录的信息大很多），statement（记录操作的sql），MIXED。

6.套接字文件

unix系统本地连接mysql采用unix 域socket方式，这种方式需要一个socket文件，文件名可由参数socket控制。

一般是在/tmp/mysql.sock

show VARIABLES like 'socket'

7.pid文件

当mysql实例启动后，会将PID写入到这个pid文件，一般在mysql/data下面。该文件可由参数pid_file控制。

show VARIABLES like 'pid_file'

8.表结构定义文件

frm为后缀的文件定义该表结构定义文件

9.innodb存储引擎文件

和innodb存储引擎密切相关的文件有重做日志文件，共享表空间文件

1.共享表空间文件

默认情况下,会初始一个10M大小的ibdata1文件

show VARIABLES like 'innodb_data_file_path'结果可看到ibdata1:10M:autoextend

用户可用多个文件构成一个表空间。如ibdata1和ibdata2，可设置他们的属性，

如：在[mysqld]下添加innodb_data_file_path='ibdata1:100M;ibdata2:100M:autoextend'

若设置了innodb_file_per_table,那么基于innodb的表会产生一个独立表空间文件：表名.ibd。这个文件仅存储数据，索引，插入缓冲bitmap信息。其他信息放在共享表空间。

show VARIABLES like 'innodb_file_per_table'

2.重做日志  文件

默认情况下，innodb存储引擎的数据目录下至少有两个ib_logfile1和ib_logfile2重做日志文件，组成一个重做日志文件组，记录innodb的事务日志。可设置多个镜像日志组，放在不同的磁盘，提高可用性。在日志文件组中，每个文件大小一样，循环写入，如ib_logfile2写满后会切换到写第一个文件。

相关参数：

show VARIABLES like 'innodb_log_file_size';
show VARIABLES like 'innodb_log_files_in_group';
show VARIABLES like 'innodb_mirrored_log_groups';
show VARIABLES like 'innodb_log_group_home_dir';

重做日志文件不能设置得太大，否则恢复时间很长。又不能设置太小，否则导致一个事务的日志多次切换日志文件,还会导致频繁的async checkpoint,导致性能抖动。

二进制日志文件和重做日志文件区别：1.二进制文件记录所有与mysql相关的日志记录，不限于特定存储引擎;重做日志文件只记录innodb的事务日志。2.二进制文件是逻辑日志;重做日志文件记录事务对每个页的物理修改。3.写入时间不同，二进制文件在事务提交前写入磁盘一次，无论事务多大;重做日志文件在事务过程中不断 有重做日志条目写入文件。

写重做日志文件每次按512字节，即一个扇区的大小顺序写入，而扇区是写入的最小单位，因此能保证每次写入是成功的，所以在重做日志文件的写入过程中不需要有doublewrite

在事务提交时，处理重做日志的方式由innodb_flush_log_at_trx_commit控制，为了保证事务ACID的持久性，必须设置为1，即每次事务提交写磁盘。可选值有0,1,2。

0：不将事务日志写入文件，等待主线程每秒刷新，不论事务是否已提交;

2:将日志文件异步写到磁盘，即写到文件系统的缓冲，并不保证commit时能写入到重做日志文件,只是有这个动作发生，若数据库宕机而服务器正常，一样能保证数据不丢失。

show VARIABLES like 'innodb_flush_log_at_trx_commit';