InnoDB存储引擎 (第3章 文件)

参数文件: 用于mysql实例启动时找到数据库文件,并指定某些初始化参数;

这些定义某些内存结构的大小等设置;

日志文件: 用来记录mysql实例对某种条件作出响应时写入的文件,例如错误日志,二进制日志文件,慢查询日志文件,查询日志文件等;

socket文件: 当用UNIX域套接字方式进行连接时需要的文件

pid文件: mysql实例的进程ID文件

mysql表结构文件: 用来存放mysql表结构定义文件

存储引擎文件: 每个存储引擎都会有自己的文件来保存各种数据,这些存储引擎真正存储了记录和索引等数据;

 

3.1　  参数文件

mysql实例启动时,数据库会先去读一个配置参数文件;

3.1.1　什么是参数

show variables like 'innodb_buffer%'

mysql没有"隐藏参数" ;

3.1.2　参数类型

分为两类: 动态参数 和静态参数 ;

动态参数可以实例运行中进行更改,  静态参数是整个实例生命周期内都不得进行更改;

可以通过SET命令对动态的参数值进行修改;

有些参数是会话级别的,有些是全局的;

例如: set read_buffer_size=524288 ;

select @@session.read_buffer_size ;

--> 524288

select @@global.read_buffer_size ;

-->65536

 

动态参数可以通过官方手册查看; Dynamic System Variables

 

3.2　  日志文件

错误日志(error log)

二进制日志(binlog)

慢查询日志(slow query log)

查询日志(log)

3.2.1　错误日志

对mysql启动,运行,关闭过程进行记录;

show variables like 'log_error%' ;

--> log_error ./VM-0-9-centos.err

默认文件名是服务器的主机名;

.../mysql/data/ 文件夹下 tail -n 50 VM-0-9-centos.err 查看文件50行

可以根据错误日志或警告日志来解决问题;

 

3.2.2　慢查询日志

mysql启动时设置一个阈值; long_query_time ;

show variables like 'long_query_time%' ;

查看是否启用:(mysql8中命令)

show variables like 'slow_query_log%' ;

默认不启用,需要手动设置on ;

查看语句没有使用索引则会记录到慢查询日志文件中:

show variables like '%log_queries_not_using_indexes%' ;

每分钟允许记录到slow log且未使用索引的SQL语句次数:

默认是0,表示没有限制;(生产环境会不断加大日志文件大小,可以通过这个参数配置)

show variables like '%log_throttle_queries_not_using_indexes%'

 

命令: mysqldumpslow

 

慢查询日志会存在mysql的database下表名slow_log中;

select * from mysql.slow_log ;

参数: log_output 全局的,动态的,用户可以在线修改

show variables like '%log_output%' ;

慢查询输出格式,默认FILE ;可以设置成TABLE,然后既可以查询slow_log表了;

例如:

set global log_output='TABLE' ;

select sleep(11) ;

然后可以看到slow_log表数据;

slow_log表使用的CSV引擎; 可以转为myisam,并start_time列添加索引提交查询效率;

slow_log中包含逻辑读取和物理读取的统计;(logical reads和physical reads);

物理读取指的是从磁盘IO读取的次数;

逻辑读取包含所有的读取,不管是磁盘还是缓冲池;

参数 long_query_io: 默认值100,表示对于逻辑读取次数>100的SQL语句记录到slow log中;

参数: slow_query_type: 表示启用slow log的方式,可选值:

• 0 表示不将SQL语句记录到slow log

• 1 表示根据运行时间将SQL语句记录到slow log

• 2 表示根据逻辑IO次数将SQL语句记录到slow log

• 3 表示根据运行时间及逻辑IO次数将SQL语句记录到slow log

mysql8中上面两个参数没有;

 

3.2.3　查询日志

默认文件名: 主机名.log ;

--mysql8设置 通用日志

set global general_log ='ON' ;

 

3.2.4　二进制日志

记录mysql执行更改的所有操作;

不包括select 和show 这类操作;

但操作本身没有导致数据库发生变化,也可能会写入二进制日志;

二进制日志还包括了执行数据库更改操作的时间等其他额外日志 ;

作用:

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

复制(replication): 其原理与恢复类似,通过复制和执行二进制日志,使一台远程的mysql数据库(一般称为slave/standby) 与一台mysql(msater/primary)进行实时同步;

审计(audit): 用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入的攻击;

binlog.000001 这种就是日志文件;

binlog.index为二进制的索引文件

 

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

max_binlog_size

binlog_cache_size

sync_binlog

binglog-do-db

binlog-ignore-db

log-slave-update

binlog_format

说明:

• max_binlog_size: 指定了单个二进制日志文件的最大值.如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到 .index文件,默认值 1073741824 ,代表1G,

• binlog_cache_size : 使用事务的表存储引擎,所有未提交的二进制文件会被记录到一个缓存中去,等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件.该缓冲的大小由binlog_cache_size决定,默认大小32K,

该参数是基于会话的,也就是说一个线程开始一个事务时,mysql会自动分配一个大小为该值的缓存;

查看缓存使用情况可以判断当前设置缓冲大小是否合适:

show global status where Variable_name like '%binlog_cache_use%' ;

show global status where Variable_name like '%binlog_cache_disk_use%' ;

binlog_cache_use: 记录缓冲写二进制日志的次数.

binlog_cache_disk_use: 记录使用临时文件写二进制日志的次数 ;

• sync_binlog

show variables like '%sync_binlog%' ;

sync_binlog=[N] 表示每写缓冲多少次就同步到磁盘,

如果N设置成1,表示采用同步写磁盘的方式来写二进制日志文件,这时写操作不使用操作系统的缓冲来写二进制日志;

如果设置成0,使用Innodb存储引擎进行复制并且想得到最大的高可用性,建议将值设置为ON,不过会对数据库IO系统带来一定的影响;

如果设置为1,还会有一种情况导致问题发生,当innodb在一个事务发出commit动作之前,由于sync_binlog为1,因此会将二进制日志立即写入磁盘,如果这时已经写入二进制日志,但是未提交,此时发生宕机,那么mysql下次秦东时,由于commit没有提交回滚,但是二进制日志已经记录了该事务信息,不能被回滚; 这个问题可以通过将参数innodb_support_xa=1来解决;

• binglog-do-db和binlog-ignore-db

表示需要写入或忽略写入哪些库的日志,默认为空,表示需要同步所有库的日志到二进制日志;

• log-slave-update: 如果当前数据库时复制中的slave角色,将不会从master取得并执行的二进制日志写入自己二进制日志文件中,如果需要写入,要设置log-slave-update, 如果需要搭建,master => slave => slave 架构复制,则必须设置改参数;

也就是说,该参数用于 更新从库的二进制日志文件;

• binlog_format : 影响二进制日志的格式, 可能会导致主从服务器数据的不一致问题;

show variables like '%binlog_format%' ;

可以设置的值STATEMENT,ROW和MIXED ;

1. STATEMENT : 二进制日志文件记录的是逻辑SQL语句;

2. ROW: 记录的不再是简单的SQL语句,而是记录表的行更改情况;

3. MIXED: 此模式下,mysql默认采用STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用row格式,可能情况有:

1. 1. 表的存储引擎为NDBM,这时对表的DML操作都会以ROW格式记录

   2. 使用uuid() ,USER(), CURRENT_USER() , FOUND_ROWS(), ROW_COUNT()等不去定函数

   3. 使用了insert delay语句

   4. 使用了用户自定义的函数

   5. 使用了临时表

binlog_format 是动态参数,因此可以在数据库运行环境下进行更改,例如可以将当前会话的binlog_format 设置为ROW;

通常情况下降binlog_format 设置为ROW,可以为数据库的恢复和复制带来更好的可靠性,但会带来二进制文件大小的增加,有些语句下的ROW格式可能需要更大的容量;

查看二进制文件命令:

mysqlbinlog binlog.000011

mysqlbinlog -vv binlog.000011 :加上-vv能看到具体的SQL信息;

例如 : insert一条记录

在ROW格式下,二进制日志文件信息会比较大;

 

3.3　  套接字文件

UNIX系统下本地连接mysql需要用到套接字方式.

show variables like 'socket'

--> /tmp/mysql.sock

 

3.4　  pid文件

mysql实例启动时,会将自己进程ID写入一个文件中, 文件参数由pid_file控制,默认是 主机名.pid,

show variables like 'pid_file' ;

--> /usr/local/mysql/data/VM-0-9-centos.pid

 

3.5　  表结构定义文件

每个表都又与之对应的文件,后缀名frm;

MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。

如何可以查看表结构信息，官方提供了一个工具叫做ibd2sdi，在安装目录下可以找到，可以离线的将ibd文件中的冗余存储的sdi信息提取出来，并以json的格式输出到终端。

用法示例：

ibd2sdi user.ibd

 

3.6  　InnoDB存储引擎文件

每个存储引擎都有自己的独有文件,这些包括重做日志文件,表空间文件;

 

3.6.1　表空间文件

innodb采用将存储的数据按表空间(tablespace) 进行存放的设计, 默认配置下有一个初始大小10MB,命名为ibdata1的文件(mysql8是12MB) ,一般在 data目录下;

Innodb_data_file_path:

用户可以通过 innodb_data_file_path 进行设置 ;

show variables like '%innodb_data_file_path%'

-- > ibdata1:12M:autoextend ,如果用完了大小会自动增长 ;

设置 该参数后,所有基于Innodb的表的数据都会记录到该共享表空间,

 

innodb_file_per_table :

如果设置 innodb_file_per_table,则会基于每个innodb表产生一个独立的表空间,命名规则为 表名.ibd ;

show variables like '%innodb_file_per_table%' ;

--> ON

 

3.6.2　重做日志文件

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

这就是重做日志文件,它们记录了对于innodb存储引擎的事务日志;

如果主机掉电导致实例失败,innodb会使用重做日志恢复到掉电钱的时刻,保证数据完整性;

每个innodb存储引擎至少有一个重做日志文件组group每个文件组下至少有2个重做日志文件;

用户可以设置多个的镜像组,将不同的文件组放在不同的磁盘上,以提高日志的高可用,

日志组中每个重做日志文件的大小一致,并以循环写入的方式运行;

下列参数应该重做日志文件的属性:

innodb_log_file_size : 指定每个重做日志文件的大小;

innodb_log_files_in_group : 指定日志镜像文件组中重做日志的数量,默认为2;

innodb_mirrored_log_group: 指定日志镜像文件组的数量,默认为1,表示只有一个日志文件组,没有镜像;

innodb_log_group_home_dir : 指定日志文件组所在路径,默认 ./,表示mysql数据库的数据目录下;

重做日志不能设置的太大,恢复时间需要很长;

也不能设置太小,否则导致一个事务的日志需要多次切换redo日志文件,此外太小会导致频繁发生async checkpoint,导致性能的抖动;

 

capacity变量:

redo日志有一个capacity变量,该值代表了最后一个检查点不能超过这个阈值;

如果超过则必须将缓冲池中的脏页列表(flush list)中的部分脏数据页写会磁盘,这会导致用户线程的阻塞;

 

redo日志和二进制日志的区别:

1. 二进制日志会记录所有与mysql数据库有关的日志记录,包括innodb,myisam,heap等其他存储引擎的日志, 而innodb存储引擎的redo日志只记录有关该存储引擎(innodb)本身的事务日志;

2. 记录内容不同.二进制日志文件记录都是关于一个事务的具体操作内容,即该日志是逻辑日志, 而innodb的redo日志记录的是关于每个页(page)的更改物理情况;

3. 写入时间也不同, 二进制文件仅在事务提交前进行提交,即只写磁盘一次,不论这时该事务多大,而事务进行过程中却不断有重做日志条目(redo entry)被写入到重做日志文件中;

 

重做日志条目结构:

 

redo日志写入过程:

不是直接写,而是先写入一个redo log buffer(缓冲)中,然后按照一定的条件顺序地写入日志文件(日志组),

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

 

写入的一定条件:

主线程每秒会将重做日志缓冲写入磁盘的redo日志文件中,不论事务是否提交;

另一个触发是参数 innodb_flush_log_at_trx_commit 控制,表示commit操作时处理redo日志的方式;有效值有0,1,2;

0代表当事务提交时,并不将事务的redo日志写入磁盘的日志文件,而是等待主线程每秒的刷新;

1和2不同在于: 1表示执行commit时将redo log buffer同步写到磁盘,即伴有fsync的调用 ,

2表示将redo日志异步写到磁盘,即写到文件系统的缓存中,因此不能完全保证在执行commit时会写入redo日志文件,只是有这个动作发生;

为了保证事务的ACID持久性,必须将其设置为1,也就是事务commit时,必须保证事务都已经写入redo log文件, 那么当数据库宕机时可以通过redo日志文件恢复,并保证恢复已经提交的事务;

 

3.7　  小结

mysql数据库文件中错误文件和二进制文件非常重要,DBA可以从文件提示内容中找出问题所在;

错误文件中也记录了警告内容,用于优化;

二进制日志作用非常关键,可以用进行point-in-time的恢复以及复制环境的搭建.

二进制日志文件支持STATEMENT,ROW,MIX三种格式,这样可以保证数据库主从数据一致性;

Innodb相关文件,包括表空间文件和redo日志文件;

表空间文件是用来管理innodb存储引擎的存储,分享共享表空间和独立表空间;

重做日志非常重要,用来记录innodb存储引擎的事务日志;