南京建设工程交易中心网站,怎么给网站备案,网页设计如何把照片作为背景,巧家县住房和城乡建设局网站文章目录Sql执行流程与日志文件Sql的执行流程Redo LogBin logUndo logSql执行流程与日志文件
Sql的执行流程
mysql的内部组件结构如下图所示 连接器 与客户端建立连接#xff0c;检验登录密码#xff0c;分配相应权限 查询缓存 执行sql语句时会先从这里找一下#xff0c;…
文章目录Sql执行流程与日志文件Sql的执行流程Redo LogBin logUndo logSql执行流程与日志文件
Sql的执行流程
mysql的内部组件结构如下图所示 连接器 与客户端建立连接检验登录密码分配相应权限 查询缓存 执行sql语句时会先从这里找一下这里数据的存储格式是key-valuekey是sql语句value是查询结果。只有当sql语句和查询缓存中的sql语句完全一致才会匹配成功并且只要对表进行了更新操作就会清除查询缓存中的内容。mysql8.0已经移除了查询缓存 词法分析器 对sql语句进行解析进行相应语法校验一条select查询语句经过分析后的结果如下图所示 优化器 Mysql自动对sql语句进行相应的优化存在多个索引时决定使用哪一个多个表连接查询时决定谁驱动谁 执行器 开始执行的时候要先判断一下你对这个表 T 有没有执行查询的权限如果没有就会返回没有权限的错误如果有权限就打开表继续执行。打开表的时候执行器就会根据表的引擎定义去使用这个引擎提供的接口。
当使用InnoDB存储引擎执行一条update语句的大致流程如下 经过server层后就到存储引擎层了
首先去Buffer pool中查询当前数据所在的页是否在缓存池中如果在就直接操作如果不在就去磁盘中找到并把整页数据赋值到Buffer Pool中在进行更新操作前先生成Undo log文件更新内存中的数据更新完成后会写Redo log到redo log buffer中redo log buffer就经过它相应的刷盘策略 持久化到磁盘的Redo log文件中事务提交前默认情况下会对Redo log进行持久化操作还会生成bin log文件写入磁盘中redo log 与 bin log此时都已经持久化到磁盘中了此时会写一个commit标记到redo log日志文件中 Redo Log
当进行更新操作时就会生成redo log文件它是用来保证事务的持久性的事务提交前就会把内存中的redo log写入到磁盘中
redo log刷盘策略 mysql线程以秒为单位将内存中的日志刷新到磁盘 Redo log Buffer缓存池使用了1/2就触发刷盘机制 默认情况下事务提交前由innodb_flush_log_at_trx_commit这个变量的值来决定提交时是否持久化redo log 当磁盘中Redo log文件不可重写部分占了75%容量时就触发刷新Buffer pool中脏页的机制
关键参数 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, iblogfile1… iblogfileN。默认2个最大100个。 show variables like %innodb_log_files_in_group%;innodb_log_file_size 设置单个redo log文件大小默认值为48M。最大值为512G注意最大值指的是整个 redo log系列文件之和即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。 show variables like %innodb_log_file_size%;innodb_flush_log_at_trx_commit 这个参数控制 redo log 的写入策略它有三种可能取值 设置为0表示每次事务提交时都只是把 redo log 留在 redo log buffer 中等Mysql线程去进行刷盘操作数据库宕机可能会丢失数据。设置为1 默认值每次提交事务时都将redo log buffer中同步写到磁盘当然这里是先写到操作系统的page cache中在写到磁盘设置为2每次提交事务时都将redo log buffer中异步写到磁盘这里是写到操作系统的page cache中如果数据的宕机但是操作系统没有挂数据还是存在的 Redo log 写入磁盘文件大致过程
是交替循环写多个文件写满一个文件后写下一个文件但最后一个文件写满后又写第一个文件大致如下图所示 write pos 是当前记录的位置一边写一边后移写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件里。
write pos 和 checkpoint 之间的部分就是空着的可写部分可以用来记录新的操作。如果 write pos 追上checkpoint表示redo log写满了这时候不能再执行新的更新得停下来先擦掉一些记录把 checkpoint 推进一下。 Bin log
当数据库进行更新操作时就会记录相应操作到Bin log文件中它的作用是数据恢复、主从复制
在mysql5.7中Binlog默认是关闭的在Mysql8.0版本中默认是开启的
# 查看binlog相关参数
show variables like %log_bin%;log_bin的值是OFF就代表binlog是关闭状态打开binlog功能需要修改配置文件my.cnf然后重启数据库。
# log-bin设置binlog的存放位置可以是绝对路径也可以是相对路径这里写的相对路径则binlog文件默认会放在data数据目录下
log-binmysql-binlog
# Server Id是数据库服务器id随便写一个数都可以这个id用来在mysql集群环境中标记唯一mysql服务器集群环境中每台mysql服务器的id不能一样不加启动会报错
server-id1
# 其他配置
binlog_format row # 日志文件格式下面会详细解释推荐使用MIXED混合模式
expire_logs_days 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数 默认为0 表示不自动删除
max_binlog_size 200M # 单个binlog日志文件的大小限制默认为 1GB重启mysql服务后再通过上面的命令查看结果如下此时就表示binlog已经开启了 Binlog的日志格式
mysql支持三种
STATEMENT直接报错执行的sql语句日志量小节约IO开销提高性能如果此时执行UUID()、SYSDATE()等函数就会造成主从数据不一致问题ROW日志会记录每一行具体修改的值但对于范围型的更新操作则会生成非常多的数据比如更新id10这个时候就会为每一行数据都生成一条binlogMIXED混合模式MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式也就是在Statement和Row之间选择一种如果sql里有函数或一些在执行时才知道结果的情况会选择Row其它情况选择Statement推荐使用这一种。 binlog写入磁盘机制
binlog写入磁盘机制主要通过 sync_binlog 参数控制默认值是 0。
为0的时候表示每次提交事务都只 write 到page cache由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升但是机器宕机page cache里面的 binlog 会丢失。也可以设置为1表示每次提交事务都会执行 fsync 写入磁盘这种方式最安全。还有一种折中方式可以设置为N(N1)表示每次提交事务都write 到page cache但累积N个事务后才 fsync 写入磁盘这种如果机器宕机会丢失N个事务的binlog。 binlog 文件重新生成策略
执行flush logs;命令mysql服务重启单个文件记满 删除binlog日志
删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件下面这个是删除6之前的所有日志文件当前这个文件不删除
purge master logs to mysql-binlog.000006;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before 2023-01-21 14:00:00;查看 binlog 日志文件
binlog文件中默认记录的是二进制的数据可以使用如下方式进行查询其中的内容
# 查看bin-log二进制文件命令行方式不用登录mysql
mysqlbinlog --no-defaults -v --base64-outputdecode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 # 查看bin-log二进制文件带查询条件
mysqlbinlog --no-defaults -v --base64-outputdecode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime2023-01-21 00:00:00 stop-datetime2023-02-01 00:00:00 start-position5000 stop-position20000在binlog文件中每一个 BEGIN 或者是COMMIT 上下都有一个表示位置的数比如 \# at 766可以拿到这个偏移量去进行数据恢复 binlog日志文件恢复数据
思路是首先通过上面的命令查看到binlog文件的内容找到要恢复的一个起始和结束位置在执行数据恢复命令或者是直接执行整个binlog文件
mysqlbinlog --no-defaults --start-position219 --stop-position701 --databasetest D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v 数据库名Undo log
InnoDB对undo log文件的管理采用段的方式也就是回滚段rollback segment 。每个回滚段记录了 1024 个 undo log segment 每个事务只会使用一个undo log segment。
在MySQL5.5的时候只有一个回滚段那么最大同时支持的事务数量为1024个。在MySQL 5.6开始InnoDB支持最大128个回滚段故其支持同时在线的事务限制提高到了 128*1024 。 undo log日志什么时候删除
新增类型的在事务提交之后就可以清除掉了。
修改类型的事务提交之后不能立即清除掉这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。
