MySQL技术内幕InnoDB存储引擎阅读相关笔记-文件

一、Mysql日志

​ 关于日志的记录，Mysql并不是直接将日志写入文件中，而是想将其写入到日志缓冲中,然后再根据一定的规则将其写入到日志文件中，例如InnoDB的重做日志

1、日志地址

​ windows下的目录一般是在：C:\ProgramData\MySQL\MySQL Server 5.7\Data (具体地址会不同)

二、参数相关

1、设置参数

​ 对于参数的设置可以通过配置文件，也可以通过set语句

1）、会话级别

​ set log_queries_not_using_indexes=1，是这种语法，但这条命令会提示这个参数是全局的，需要设置全局级别。

2）、全局级别

​ set global log_queries_not_using_indexes=1;

2、查看参数命令

​ show variables [ like ‘%xxxx%’ ] [\G]

三、Mysql慢日志

1、慢日志相关参数

1）、slow-query-log

slow-query-log表示是否开启日志。1：表示开启日志

2）、 long_query_time

​ show variables like 'long_query_time'，也就是说，如果一个查询sql超过这个事件就会记录到慢日志文件：

.............
# Time: 2021-01-13T14:48:34.834562Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.010025  Lock_time: 0.000000 Rows_sent: 2749  Rows_examined: 2749
SET timestamp=1610549314;
SELECT * from general_log;

​ 可以看到这里记录了时间戳、查询时间Query_time: 0.010025、锁时间等。Rows_sent表示服务器最终返回的数据行数、Rows_examined表示这条sql总共检索了多少条数据

​ 参数修改：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件对应参数：

slow-query-log=1        					# 为1表示开启慢查询记录
slow_query_log_file="E:/slow.202010.log"	# 日志文件的位置 (可以自动修改)
long_query_time=0.001						# 超过这个时间就记录 (5.1版本后单位为毫秒)

​ 需要重启mysql服务生效，(我在测试的时候，启动服务的时候将日志文件删除，在查询sql，发现其并不会再主动创建这个文件去记录信息)。

3）、log_output

​ 同时这里还可以通过参数log_output设置对应的日志存储格式：FILE表示以日志文件的方式、table表示存储到表里面，这两种也可以同时存在，可以通过设置变量set global log_output = 'FILE,TABLE',也可以直接修改my.ini文件。

4）、log_queries_not_using_indexes

​ log_queries_not_using_indexes=1;，设置如果查询没有使用索引也记录到慢日志：

# Time: 2021-01-13T15:14:00.799024Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    12
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 21  Rows_examined: 21
SET timestamp=1610550840;
select * from student;
# Time: 2021-01-13T15:14:00.811017Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    12
# Query_time: 0.001032  Lock_time: 0.000000 Rows_sent: 15  Rows_examined: 306
SET timestamp=1610550840;
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID;
# Time: 2021-01-13T15:14:00.811980Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    12
# Query_time: 0.000963  Lock_time: 0.000000 Rows_sent: 16  Rows_examined: 310
SET timestamp=1610550840;
SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.000284*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=30 GROUP BY STATE ORDER BY SEQ;

​ 这里是我已经将慢查询的时间设置为了1，但由于sql没有使用索引，其也记录了下来select * from student。，提示可以看到其也有一些统计sql。

四、Mysql二进制日志

​ 二进制文件会记录对数据库执行更改的所有操作，不包括select、show这种查询日志。当使用事务性表存储引擎如InnoDB时，所有未提交的二进制日志是被记录再一个缓存中，等事务提交的时候直接将缓存中的日志写入二进制文件中。

1、作用

​ 二进制日志可以对数据进行复制、也可以用于mysql服务器之间数据的同步。根据官方数据，开启二进制日志对性能会造成1%的下降。

2、参数

1）、 log-bin

​ log-bin[=文件名]用于开启二进制日志记录，例如 log-bin=E:/log

log.index文件中记录的是

E:\log.000001

2）、max_binlog_size

​ max_binlog_size：超过该值会起一个新文件继续进行记录。这里需要注意：最开始是会产生两个文件，即xxx.index文件&xxx.000001文件，其中以.index文件结尾的文件是记录真正记录信息文件(以xxx.000001名称结尾)名。

3）、binlog_cache_size

​ binlog_cache_size：这个值是与会话相关，当一个线程开始一个事务的时候，Mysql会自动分配默认大小为32KB的值，如果当一个事务超过这个值，mysql就会将缓存中的日志内容写到二进制文件中。

​ show global status like 'binlog_cache%'：这个会返回

​ Blnlog_cache_disk_use：记录使用临时文件写二进制日志的次数、Binlog_cache_use：记录使用缓存写二进制文件的次数。

​ 这里我使用的demo是使用事务，开启事务循环插入创建的数据500条，同时set global binlog_cache_size = 1;，之后

​ 可以看到这里使用了2次。

4）、sync_binlog

​ sync_binlog=[N]表示每写多少次缓存就同步到磁盘，当N设置为1采用同步写磁盘的的方式来写二进制日志，set global sync_binlog=1;在前面的例子中，我改为循环插入数据1000此，每次都有一个事务：

​ 可以看到这里的Binlog_cache_use是1005(之所以多了3次，是我有测试了其他的)。

5）、binlog_format

​ binlog_format：记录二进制文件的格式，有3种取值：

​ STATE：表示直接按SQL语句的格式记录日志。

​ ROW：并不是简单的记录SQL语句，而记录表的行改变情况(这种情况日志文件会更大)

​ MIXED：前面两种的混合模式，由Mysql自己决定使用情况。

​ 同时对于二进制日志的查看也不能直接打开查看，需要使用Mysql提供的工具mysqlbinlog。 mysqlbinlog --start-position=592939 log.000002

BEGIN
/*!*/;
# at 1813
#210116 12:08:02 server id 1  end_log_pos 1867 CRC32 0x3f8a3d51         Table_map: `myte`.`user` mapped to number 130
# at 1867
#210116 12:08:02 server id 1  end_log_pos 1945 CRC32 0x1acc5f85         Update_rows: table id 130 flags: STMT_END_F

BINLOG '
omYCYBMBAAAANgAAAEsHAAAAAIIAAAAAAAEABG15dGUABHVzZXIABAMDDw8ElgA8AA5RPYo/
omYCYB8BAAAATgAAAJkHAAAAAIIAAAAAAAEAAgAE///0BAAAAAwAAAAJ6IKh5Lic5Lya8AQAAAAM
AAAAA0pLRgnogqHkuJzkvJqFX8wa
'/*!*/;
# at 1945
#210116 12:08:02 server id 1  end_log_pos 2018 CRC32 0xda32d22c         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1610770082/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

​ 可以看到这里中间是一串看不明白的内容：我们加一个参数：--base64-output=decode-rows，mysqlbinlog --base64-output=decode-rows log.000002

BEGIN
/*!*/;
# at 1813
#210116 12:08:02 server id 1  end_log_pos 1867 CRC32 0x3f8a3d51         Table_map: `myte`.`user` mapped to number 130
# at 1867
#210116 12:08:02 server id 1  end_log_pos 1945 CRC32 0x1acc5f85         Update_rows: table id 130 flags: STMT_END_F
# at 1945
#210116 12:08:02 server id 1  end_log_pos 2018 CRC32 0xda32d22c         Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1610770082/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

​ 我们可以看到这里是关于myte`.`user表的修改。

上面的是ROW格式的，我们通过set global binlog_format='STATEMENT';来将其改为STATEMENT格式：

BEGIN
/*!*/;
# at 2468
#210116 12:25:19 server id 1  end_log_pos 2591 CRC32 0xd5d04728         Query   thread_id=10    exec_time=0      error_code=0
use `myte`/*!*/;
SET TIMESTAMP=1610771119/*!*/;
UPDATE `user` SET `addr`='JKF2' WHERE (`id`='14')
/*!*/;
# at 2591
#210116 12:25:19 server id 1  end_log_pos 2671 CRC32 0xbe57cf1c         Query   thread_id=10    exec_time=0      error_code=0
SET TIMESTAMP=1610771119/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

​ 可以看到这种模式是直接的SQL语句。同时可以通过--start-position=2468来选择打印的开始位置：

E:\>mysqlbinlog --base64-output=decode-rows --start-position=2468 log.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 2468
#210116 12:25:19 server id 1  end_log_pos 2591 CRC32 0xd5d04728         Query   
       .................
# at 2591
#210116 12:25:19 server id 1  end_log_pos 2671 CRC32 0xbe57cf1c         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1610771119/*!*/;
COMMIT
/*!*/;

​ 同时也可以通过mysqlbinlog --base64-output=decode-rows --start-position=2468 --stop-position=2591 log.000002来设置对应的结束时间。还可以通过start_datetime来设置对应的开始时间，mysqlbinlog --base64-output=decode-rows -v --start-position=2468 --start-datetime="2021-01-16 00:00:00" log.000002。

​ binlog_do_db、binlog_ignore_db：这两个是表示需要写入或者忽略写入哪些库的日志。默认为空，表示所有库的日志都同步到二进制文件。但这两个参数我在Mysql的5.7版本并没有看到

五、InnoDB文件

1、InnoDB表空间文件

​ Innodb表空间文件是用来处理Innodb数据库表产生的对应数据的，例如本身存储的插入数据、对应建立的索引、插入缓冲等。默认文件名称是idbdata1。

1）、innodb_data_file_path

​ 可以通过innodb_data_file_path对其进行设置，例如innodb_data_file_path = 对应目录\ibddata1:200M;对应目录\ibddata1:200M:autoextend，这样就能产生两个文件，其会按顺序进行记录，其中200M这种是指明文件大小、autoextend表明该文件达到大小的时候是进行自增的。

2）、innodb_file_per_table

​ 可以通过innodb_file_per_table这个参，将默认的所有表的数据都记录在默认的ibddata1文件中(共享表空间)，改为每一个表都有一个单独的表空间(独立表空间)，其名称是跟的表名称：b_table.ibd(C:\ProgramData\MySQL\MySQL Server 5.7\Data\对应数据库名称)，以.ibd结尾。

​ 这里再提下：对应一个数据库表：有一个.frm结尾的文件，这个与存储引擎无关，其是用来存储表结构的内容。对于innodb引擎来说通过开启这个参数能让每个表都有一个对应的.ibd文件。对应MyISAM引擎来说，其还会有两个文件.MYD结尾的存储数据的文件、.MYI结尾的存储索引相关的文件。

​ 我们现在来验证下：

​ 可以看到我们关闭后创建Innodb引擎的表其只产生了一个.frm文件。

​ 需要注意的是：虽然通过innodb_file_per_table能让每个表产生独立的.ibb文件，但在默认的idbdata文件中还是会存储一些与此相关的其他内容如系统事务信息等。

​

2、重做日志文件(redo log file)

​ 重做日志其记录了对于Innodb存储引擎的事务日志，其的作用是用于恢复数据。如数据库突然挂了，就需要Innodb使用重做日志来恢复数据，来确保数据的完整性。

​ 每个Innodb至少有一个重做日志组(group) ，每个组至少有两个日志文件，如：ib_logfile0、ib_logfile1。日志组中每个重做日志的大小一致，并以循环方式使用，如先写第一个文件，满了再写第二个文件，当第二个文件满足再去写第一个文件(组文件大小是2的时候)。当有3个日志文件时：

1）、参数

​ innodb_log_file_size：指定日志文件的大小。

​ innodb_log_files_in_group：指定重做日志的数量，默认为2。

​ innodb_log_group_home_dir：指定日志文件所在的路径。

​ innodb_flush_log_at_trx_commit：在提交(commit)操作时处理事务的方式，其的值有0、1、2。0：表示提交事务的时候，并不将事务的重做日志写入磁盘中，而是等待Mysql的主线程每秒刷新(主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交了)、1：在commit时将重做日志缓冲同步到磁盘、2：重做日志缓冲异步写到磁盘，即不能保证commit时一定能写入到日志文件中。

​

​ 关于参数在数上还写了一个日志镜像文件组innodb_mirrored_log_groups，这个就是用来控制镜像文件组的数量的，默认为1，表示只有一个日志文件组、没有镜像(可以通过将镜像日志放在多个不同出磁盘来确保高可用性)，但整个产生我在Mysql 5.7版本没有看到。

六、二进制文件与重做日志文件关于事务事务记录的区别

1、二进制其本身是Mysql级别的日志，其虽然也会记录事务日志，但其也会记录其他引擎例如MyISAM。而重做日志redo log file是属于InnoDB存储引擎的，其只记录InnoDB本身的事务。

2、存储的方式不同：二进制日志是记录关于一个事务的具体操作，而InnoDB是记录的每个页Page的更改物理情况(下方是重做日志结构)

3、写入的时间不同：二进制日志文件是在事务提交前进行记录的，而InnoDB是在事务过程中不断有重做日志条目(redo entry)被写入日志文件中。