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 ;
-
STATEMENT : 二进制日志文件记录的是逻辑SQL语句;
-
ROW: 记录的不再是简单的SQL语句,而是记录表的行更改情况;
-
MIXED: 此模式下,mysql默认采用STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用row格式,可能情况有:
-
表的存储引擎为NDBM,这时对表的DML操作都会以ROW格式记录
-
使用uuid() ,USER(), CURRENT_USER() , FOUND_ROWS(), ROW_COUNT()等不去定函数
-
使用了insert delay语句
-
使用了用户自定义的函数
-
使用了临时表
-
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日志和二进制日志的区别:
-
二进制日志会记录所有与mysql数据库有关的日志记录,包括innodb,myisam,heap等其他存储引擎的日志, 而innodb存储引擎的redo日志只记录有关该存储引擎(innodb)本身的事务日志;
-
记录内容不同.二进制日志文件记录都是关于一个事务的具体操作内容,即该日志是逻辑日志, 而innodb的redo日志记录的是关于每个页(page)的更改物理情况;
-
写入时间也不同, 二进制文件仅在事务提交前进行提交,即只写磁盘一次,不论这时该事务多大,而事务进行过程中却不断有重做日志条目(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存储引擎的事务日志;
