InnoDB底层原理与MySQL日志机制深入剖析

最新推荐文章于 2024-05-31 14:43:45 发布

.番茄炒蛋

最新推荐文章于 2024-05-31 14:43:45 发布

阅读量821

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43135259/article/details/137249019

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

MySQL内部组件结构

在这里插入图片描述

大体来说,MySQL可以分为Server层和存储引擎层两部分

Server层

主要包括连接器,查询缓存,分析器,优化器,执行器等,涵盖MySQL的大多数核心服务功能,以及所有内置函数(如日期,时间,数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程,触发器,视图等

连接器

我们知道由于MySQL时开源的,他有非常多种类的客户端,navicat,jdbc等非常丰富的客户端,包括各种编程语言实现的客户端连接程序,这些客户端要向MySQL发起通信都必须要先跟Server端建立通信连接,而建立连接的工作就是由连接器完成的.

第一步,你会先连接到这个数据库上,这个时候接待你的就是连接器,连接器负责跟客户端建立连接,获取权限,维持和管理连接.连接命令一般是这么写的:

mysql -h 127.0.0.1(数据库地址) -uroot(用户名) -proot(密码) -P 3306(端口)

连接命令中的mysql是客户端工具,用来跟服务端建立连接,在完成经典的TCP握手后,连接器就开始认证你的身份,这个时候用的就是你输入的用户名和密码.

如果用户名/密码不对,你就会收到一个"ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)"的错误,然后客户端程序结束执行
如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限.之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限.(这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限.修改完成后,只有再新建的连接才会使用新的权限设置)

查询缓存

连接完成后,就可以执行select语句了,执行逻辑就会来到第二步:查询缓存.

MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句.之前执行过的语句及其结果可能会以key-value的形式存在.被直接缓存在内存中.key是查询语句,value是查询的结果.

如果查询语句能够直接在这个缓存中找到对应的key.那么这个value就会被直接返回给客户端.
如果语句不在查询缓存中,就会继续后面的执行阶段.执行完成后,执行结果会被存入查询缓存中.如果命中缓存.MySQL不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高.

大多数情况查询缓存就是个鸡肋,为什么呢?

因为查询缓存往往弊大于利.查询缓存的失效非常频繁,只要又对一个表的更新,这个表上所有的查询缓存都会被清空.对于写多读少的数据库来说,查询缓存的命中率会非常低

一般建议在静态表(比如系统配置表,字典表)使用查询缓存,这种表极少更新.查询缓存的命中率会很高.MySQL也提供了这种"按需使用"的方式.可以将my.cnf参数query_cache_type设置为DEMAND.

query_cache_type=2

query_cache_type有3个值

0(默认值)(OFF):关闭查询缓存
1(ON):开启查询缓存
2(DEMAND):当sql语句中有SQL_CACHE关键词时才缓存

这样对于默认的SQL语句都不使用查询缓存,而对于确定使用查询缓存的语句,可以用SQL_CACHE显示指定.如下:

select SQL_CACHE * from employees where id = 5;

查看mysql是否开启缓存机制

show global variables like "%query_cache_type%";

在这里插入图片描述

MySQL8.0已经移出了查询缓存

分析器

如果没有命中查询缓存,就要开始真正执行语句了.首先,MySQL需要知道你做什么,因此需要对sql语句做解析.

分析器会先做"词法分析".你输入的是由多个字符串和空格组成的一条sql语句,MySQL需要识别出里面的字符串分别是什么,代表什么

MySQL从你输入的"select"这个关键字识别出来,这是一个查询语句.它也要把字符串"T"识别成"表名 T",把字符串"ID"识别成"列ID".

做完这些识别以后,就要做"语法分析".根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个sql语句是否满足MySQL语法.如果你的语句不催,就会收到错误提醒,

select * fro employees where id = 5;

在这里插入图片描述

分析器对sql的分析过程步骤:

在这里插入图片描述

SQL语句经过分析器分析之后,会生成一个这样的语法树

在这里插入图片描述

优化器

经过了分析器,MySQL就知道你要做什么了.在开始执行之前,还要经过优化器的处理.

优化器是在表里面有多个索引的时候,决定使用那个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接数据;以及MySQL自己内部的优化机制

执行器

开始执行的时候,要先判断一下你对这个表T有没有执行查询的权限,如果没有,就会返回没有权限的错误

如果有权限,就打开表继续执行,打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口.

存储引擎层

存储引擎层负责数据的存储和提取.其架构是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎.现在最常用的存储引擎是InnoDB,它从MySQL5.5.5版本开始成为了默认的存储引擎.也就是说如果我们在create table时不指定表的存储引擎类型.默认会给你设置存储引擎为InnoDB.

Innodb底层原理与MySQL日志机制

在这里插入图片描述

redo日志

innodb_log_buffer_size:设置redo log buffer大小参数,默认16M,最大值是4096M,最小值为1M.

show variables like '%innodb_log_buffer_size%';

在这里插入图片描述

innodb_log_group_home_dir:设置redo log文件存储位置参数,默认为"./",即innodb数据文件存储位置,其中的ib_logfile0和ib_logfile1即为redo log文件.

show variables like '%innodb_log_group_home_dir%';

在这里插入图片描述

innodb_log_files_in_group:设置redo log文件的个数,命名方式如:ib_logfile0,ib_logfile1…ib_logfileN.默认2个,最大100个.

show variables like '%innodb_log_files_in_group';

在这里插入图片描述

innodb_log_file_size:设置单个redo log文件大小,默认值为48M,最大值为512G,注意最大值指的是整个redo log系列文件之和,即(innodb_log_files_group*innodb_log_file_size)不能大于最大值512G.

show variables like '%innodb_log_file_size%';

在这里插入图片描述

redo log写入磁盘过程分析:

redo log从头开始写,写完一个文件继续写另一个文件,写到最后一个文件末尾又回到第一个文件开头循环写,如下图所示:

在这里插入图片描述

write pos:是当前记录的位置,一边写一边后移,写到第3号文件末尾后,就回到0号文件开头.

check point:是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件里.

write pos和check point之间的部分就是可写部分,用来记录新的操作.如果write pos追上check point表示redo log写满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把check point推进一下.

innodb_flush_log_at_trx_commit:这个参数控制redo log的写入策略:

0:表示每次事务提交时都只是把redo log留在redo log buffer中,数据库宕机可能会丢失数据.
1(默认值):表示每次事务提交时都将redo log直接持久化到磁盘,数据最安全,不会因为数据库宕机丢失数据,但是效率稍微差一点(生产环境推荐设置).
2:表示每次事务提交时都只是把redo log写到OS的缓存page cache里,这种情况如果数据库宕机是不会丢失数据的,但是操作系统如果宕机了,page cache里的数据还没来得及写入磁盘文件的话就会丢失数据.

Innodb有一个后台线程,每隔1秒,就会把redo log buffer中的日志,调用OS函数write写到文件系统的page cache,然后调用OS函数fsync持久化到磁盘文件

redo log写入策略:

在这里插入图片描述

-- 查看innodb_flush_log_at_trx_commit参数值
show variables like 'innodb_flush_log_at_trx_commit';
-- 设置innodb_flush_log_at_trx_commit参数值(也可以再my.cnf文件里配置);
set global innodb_flush_log_at_trx_commit = 1;

在这里插入图片描述

binlog二进制归档日志

binlog二进制日志记录保存了所有执行过的 修改操作语句,不保存查询操作,如果MySQL服务意外停止,可通过二进制文件排查,用户操作或表结构操作,从而来恢复数据库数据.

启动binlog记录功能,会影响服务器性能.但如果需要恢复数据或主从复制功能,则好处大于对服务器的影响.

show variables like '%log_bin%';

在这里插入图片描述

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

MySQL5.7版本中,binlog默认是关闭的,8.0版本默认是打开的.上图log_bin的值为OFF就代表binlog是关闭状态,打开binlog功能,需要修改my.cnf.然后重启数据库.再my.cnf增加如下配置

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 日志文件格式
binlog_format = row 
# 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
expire_logs_days = 15 
# 单个binlog日志文件的大小限制，默认为 1GB
max_binlog_size = 200M

更新完配置后重启MySQL

在这里插入图片描述

binlog的日志格式

用参数binlog_format可以设置binlog日志的记录格式,MySQL支持三种格式类型:

STATEMENT:基于SQL语句的复制,每一条会修改数据的sql都会记录到master机器的bin-log中,这种方式日志量小,节约IO开销,提升性能,但是对于一些执行过程中才能确定结果的函数,比如UUID(),SYSDATE()等函数如果随sql同步到slave机器去执行,则结果跟master机器执行的不一样.会有数据不一致的风险
ROW:基于行的复制,日志会记录成每一行数据被修改的形式,然后再slave端再对相同的数据进行修改记录下每一行数据修改的细节,可以解决函数,存储过程等再slave机器的复制问题,但这种方式日志量较大,性能不如STATEMENT,举个例子假设update语句更新10行数据,STATEMENT方式就记录这条update语句,ROW方式会记录被修改的10行数据
MIXED:混合模式复制,实际就是千种模式的结合,再MIXED模式下,MySQL会根绝每一条具体的sql语句来区分对待记录的日志形式,也就是再STATEMENET和ROW之间选择一种,如果sql里有函数或一些执行时才知道结果的情况,会选择Row,其他情况选择STATEMENT,推荐使用这一种.

binlog写入磁盘机制

binlog写入磁盘机制主要通过sync_binlog参数控制

0(默认值):表示每次提交事务都只write到page cache,由系统自行判断什么时候fsync写入磁盘.虽然性能得到提升,但是机器宕机,page cache里面的binlog会丢失
1:表示每次提交事务都会执行fsync写入磁盘,这种方式最安全
N(N>1):折中方式,表示每次提交事务都会write到page cache,但累计N个事务后才fsync写入磁盘,但如果机器宕机会丢失N个事务的binlog

发生一下任何时间时,binlog日志文件会重新生成:

服务器启动或重新启动
服务器刷新日志,执行命令flush logs
日志文件大小达到max_binlog_size值,默认值为1GB

删除binlog日志文件:

删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before 'yyyy-MM-dd HH:mm:ss';

查看binlog日志文件

可以用mysql自带的命令工具mysqlbinlog查看日志内容

# 查看bin-log二进制文件(命令行方式,不用登录mysql)
mysqlbinlog --no-defaults -v --base64-output=decode-rows /usr/local/mysql/data/mysql-binlog.000003
# 查看bin-log二进制文件(带查询条件)
mysqlbinlog --no-defaults -v --base64-output=decode-rows /usr/local/mysql/data/mysql-binlog.000003 start-datetime="2024-04-01 00:00:00" stop-datetime="2024-04-02 00:00:00" start-position="439" stop-position="917"

不带条件查询binlog日志内容如下

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240401 21:48:37 server id 1  end_log_pos 123 CRC32 0x1e6e9aff 	Start: binlog v 4, server v 5.7.31-log created 240401 21:48:37
# Warning: this binlog is either in use or was not closed properly.
# at 123
#240401 21:48:37 server id 1  end_log_pos 154 CRC32 0x994b76b6 	Previous-GTIDs
# [empty]
# at 154
#240401 21:48:58 server id 1  end_log_pos 219 CRC32 0x1aa8b22e 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240401 21:48:58 server id 1  end_log_pos 291 CRC32 0x08a53f57 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1711979338/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#240401 21:48:58 server id 1  end_log_pos 347 CRC32 0x35ccfeaf 	Table_map: `test`.`account` mapped to number 108
# at 347
#240401 21:48:58 server id 1  end_log_pos 408 CRC32 0x54d54879 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=6
###   @2='李四'
###   @3=5000
# at 408
#240401 21:48:58 server id 1  end_log_pos 439 CRC32 0x5941a5be 	Xid = 43
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语句以及执行时的相关情况.

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql,举一个数据恢复的例子

-- 先执行刷新日志的命令生成一个binlog文件mysql-binlog.000008,后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
-- 执行两条插入语句
INSERT INTO account (name, balance) VALUES ('王五',666);
INSERT INTO account (name, balance) VALUES ('赵四',888);

数据库现有的表数据:

在这里插入图片描述

-- 模拟误删数据,执行一条删除sql
delete from account where id > 1;

在这里插入图片描述

现在需要恢复被删除的数据,先查看binlog日志文件

mysqlbinlog --no-defaults -v --base64-output=decode-rows /usr/local/mysql/data/mysql-binlog.000008

binlog日志文件内容如下

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240401 22:17:33 server id 1  end_log_pos 123 CRC32 0xe7f6fe56 	Start: binlog v 4, server v 5.7.31-log created 240401 22:17:33
# Warning: this binlog is either in use or was not closed properly.
# at 123
#240401 22:17:33 server id 1  end_log_pos 154 CRC32 0xc662b17b 	Previous-GTIDs
# [empty]
# at 154
#240401 22:17:50 server id 1  end_log_pos 219 CRC32 0xf42d7493 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#240401 22:17:50 server id 1  end_log_pos 291 CRC32 0x43c5f8f1 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1711981070/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#240401 22:17:50 server id 1  end_log_pos 347 CRC32 0xa4d50321 	Table_map: `test`.`account` mapped to number 108
# at 347
#240401 22:17:50 server id 1  end_log_pos 408 CRC32 0x4c699323 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=9
###   @2='王五'
###   @3=666
# at 408
#240401 22:17:50 server id 1  end_log_pos 439 CRC32 0x92bd0865 	Xid = 240
COMMIT/*!*/;
# at 439
#240401 22:17:50 server id 1  end_log_pos 504 CRC32 0x20985e64 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 504
#240401 22:17:50 server id 1  end_log_pos 576 CRC32 0x51ae49d0 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1711981070/*!*/;
BEGIN
/*!*/;
# at 576
#240401 22:17:50 server id 1  end_log_pos 632 CRC32 0x5dcb4191 	Table_map: `test`.`account` mapped to number 108
# at 632
#240401 22:17:50 server id 1  end_log_pos 693 CRC32 0x49583dd4 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=10
###   @2='赵四'
###   @3=888
# at 693
#240401 22:17:50 server id 1  end_log_pos 724 CRC32 0x1361cec7 	Xid = 241
COMMIT/*!*/;
# at 724
#240401 22:18:49 server id 1  end_log_pos 789 CRC32 0x873c807f 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 789
#240401 22:18:49 server id 1  end_log_pos 861 CRC32 0xbf538d8d 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1711981129/*!*/;
BEGIN
/*!*/;
# at 861
#240401 22:18:49 server id 1  end_log_pos 917 CRC32 0xe6cbd807 	Table_map: `test`.`account` mapped to number 108
# at 917
#240401 22:18:49 server id 1  end_log_pos 1004 CRC32 0x9968e33d 	Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `test`.`account`
### WHERE
###   @1=9
###   @2='王五'
###   @3=666
### DELETE FROM `test`.`account`
### WHERE
###   @1=10
###   @2='赵四'
###   @3=888
# at 1004
#240401 22:18:49 server id 1  end_log_pos 1035 CRC32 0xe83e7eac 	Xid = 254
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,每条sql的上下都有BEGIN和COMMIT,我们找到第一条sql BEGIN前面的时间戳240401 22:17:50,再找到第二条sql COMMIT后面的时间戳240401 22:18:49;

我们根据时间来恢复数据,执行如下:

-- 输入数据库密码回车 恢复对应数据
mysqlbinlog --no-defaults --start-datetime="2024-04-01 22:17:50" --stop-datetime="2024-04-01 22:18:49" --database=test /usr/local/mysql/data/mysql-binlog.000008 | mysql -uroot -p -v test

数据库数据已恢复

在这里插入图片描述

还可以根据文件的偏移量来恢复数据;第一条sql BEGIN前面的 at 219,再找到第二条sql的COMMIT后面的 at 724

-- 输入数据库密码回车 恢复对应数据
mysqlbinlog --no-defaults --start-position=219 --stop-position=724 --database=test /usr/local/mysql/data/mysql-binlog.000008 | mysql -uroot -p -v test

注意: 如果要恢复大量数据,比如经常说的 删库跑路话题,假设我们把数据库所有数据都删除了要怎么恢复了,如果数据库之前没有备份,所有的binlog日志都在的话,就从binlog第一个文件开始逐个恢复每个binlog文件里的数据,这种一般不太可能,因为binlog日志比较大,早起的binlog文件会定期删除的,所以一般不可能用binlog文件来恢复整个数据库.

一般推荐的是每天(在空闲时间)需要做一个全量数据库备份,那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据.

备份数据库一般可以用mysqldump命令工具

# 备份整个数据库
mysqldump -u -p root 数据库名>备份文件名;
# 备份整个表
mysqldump -u root -p 数据库名 表>备份文件名
# 恢复整个数据库,test为数据库名称,需要自己先建一个数据库test
mysql -u root -p test < 备份文件名

备份文件内容如下

-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.31-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `balance` decimal(30,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `account`
--

LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES (1,'张三',3000),(9,'王五',666),(10,'赵四',888);
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-04-01 22:25:40

为什么会有redo log和binlog两份日志呢?

因为最开始MySQL里并没有innoDB引擎.MySQL自带的引擎是MyISAM,但是MyISAM没有crash-safe的能力,binlog日志只能用于归档.而InnoDB是另一个公司以插件形式引入MySQL的,既然只依靠binlog是没有crash-safe能力的,所以InnoDB使用另外一套日志系统–也就似乎redo log来实现crash-safe能力.

有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前的提交的记录都不会丢失,这个能力成为crash-safe.

undo log回滚日志

InnoDB对undo log文件的管理采用段的方式,也就是回滚段(rollback segment).每个回滚段记录了1024个undo log segment,每个事务只会使用一个undo log segment.

再MySQL5.5的时候,只有一个回滚段,那么最大同时支持的事务数量为1024个.再MySQL5.6开始,InnoDB支持最大128个回滚段,故其支持同时在线的事务限制提高到了128*1024.

show variables like '%innodb_undo%';

在这里插入图片描述

innodb_undo_directory:设置undo log文件所在的路径.该参数默认值为"./",即innodb数据文件存储位置,目录下ibddata1文件就是undo log存储位置.
innodb_undo_log_truncate:控制undo log的截断行为.默认OFF,当为ON时,InnoDB会尝试再每个事务提交之后进行undo log的截断操作.这样可以减少undo表空间的占用
innodb_undo_logs:设置undo log文件内部回滚段的个数,默认值为128
innodb_undo_tablespaces:设置undo log文件的数量,这样回滚段可以较为平均地分布再多个文件中.设置该参数后,会在路径innodb_undo_directory看到undo为前缀的文件

undo log日志什么时候删除

新增类型的,再事务提交之后就可以清除掉了

修改类型的,事务提交之后不能立即清除掉,这些日志会用于mvcc.只有当没有事务用到该版本信息时才可以清除

为什么MySQL不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行sql?

因为来一个请求就直接对磁盘文件进行随机读写,然后更新磁盘文件里的数据性能可能极差.

因为磁盘随机读写的性能是非常差的,所以直接更新磁盘文件是不能让MySQL抗住很高并发的.

MySQL这套机制看起来非常复杂,但它可以保证每个更新请求都是 更新内存BufferPool,然后 顺序写日志文件,同时还能保证各种异常情况下的数据一致性.

更新内存的性能是极高的,然后顺序写磁盘上的日志文件的性能也是非常高的,要远高于随机读写磁盘文件

正是通过这套机制,才能让我们MySQL数据库再较高配置的机器上每秒可以抗下几千甚至上万的读写请求.

错误日志

MySQL还有一个比较重要的日志是错误日志,它记录了数据库的启动和停止,以及运行过程中发生任何严重错误时的相关信息.当数据库出现任何故障导致无法正常使用时,建议首先查看此日志.再MySQL数据库中,错误日志功能是默认开启的,而且无法被关闭.

-- 查看错误日志存放位置
show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的 所有操作,包括启动和关闭MySQL服务,所有用户的连接开始时间和截止时间,发给MySQL数据库服务器的所有sql指令等,如select,show等,无论sql语法正确还是错误,也无论sql执行成功还是失败,mysql都会将其记录下来

通用查询日志用来还原操作时的具体场景,可以帮我们准确定位一些疑难问题,比如重复支付等问题

show variables like '%general_log%';

在这里插入图片描述

general_log:是否开启日志参数,默认为OFF,处于关闭状态,因为开启会消耗系统资源并且占用磁盘空间,一般不建议开启,只有再需要调试查询问题时开启
```
-- 打开通用查询日志
SET GLOBAL general_log=on;
```
general_log_file:通用查询日志的位置参数