php做电影网站,推广引流渠道有哪些,wordpress 文章 页面,纳森网络做网站多少钱一条查询语句的执行过程一般是经过连接器、 分析器、 优化器、 执行器等功能模块#xff0c; 最后到达存储引擎。
那么#xff0c; 一条更新语句的执行流程又是怎样的呢#xff1f;
下面我们从一个表的一条更新语句进行具体介绍#xff1a;
假设这个表有一个主键ID和一个…一条查询语句的执行过程一般是经过连接器、 分析器、 优化器、 执行器等功能模块 最后到达存储引擎。
那么 一条更新语句的执行流程又是怎样的呢
下面我们从一个表的一条更新语句进行具体介绍
假设这个表有一个主键ID和一个整型字段c
mysql create table T(ID int primary key, c int);
如果要将ID2这一行的值加1 SQL语句就会这么写
mysql update T set cc1 where ID2;
首先 可以确定的说 查询语句的那一套流程 更新语句也是同样会走一遍。 更新语句的执行流程
1连接器连接数据库
2分析器通过词法分析和语法分析直到这是一条更新语句
3优化器决定1使用ID这个索引
4执行器负责具体执行找到这一行然后更新。
注在一个表上有更新的时候 跟这个表有关的查询缓存会失效 所以这条语句就会把表T上所有缓存结果都清空。
与查询流程不一样的是更新流程还涉及两个重要的日志模块redo log重做日志和 binlog归档日志这也是本文的重点下面对这两个模块做具体介绍。
重要日志模块redo log redo log是重做日志保证crash-safe。
相关示例 不知道你还记不记得《孔乙己》 这篇文章 酒店掌柜有一个粉板 专门用来记录客人的赊账记录。 如果赊账的人不多 那么他可以把顾客名和账目写在板上。 但如果赊账的人多了 粉板总会有记不下的时候 这个时候掌柜一定还有一个专门记录赊账的账本。 如果有人要赊账或者还账的话 掌柜一般有两种做法一种做法是直接把账本翻出来 把这次赊的账加上去或者扣除掉另一种做法是先在粉板上记下这次的账 等打烊以后再把账本翻出来核算。在生意红火柜台很忙时 掌柜一定会选择后者 因为前者操作实在是太麻烦了。 首先 你得找到这个人的赊账总额那条记录。 你想想 密密麻麻几十页 掌柜要找到那个名字 可能还得带上老花镜慢慢找 找到之后再拿出算盘计算 最后再将结果写回到账本上。这整个过程想想都麻烦。 相比之下 还是先在粉板上记一下方便。 你想想 如果掌柜没有粉板的帮助 每次记账都得翻账本 效率是不是低得让人难以忍受 同样 在MySQL里也有这个问题 如果每一次的更新操作都需要写进磁盘 然后磁盘也要找到对应的那条记录 然后再更新 整个过程IO成本、 查找成本都很高。 为了解决这个问题 MySQL的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。
而粉板和账本配合的整个过程其实就是MySQL里经常说到的WAL技术 WAL的全称是WriteAhead Logging 它的关键点就是先写日志 再写磁盘目的是保证crash-safe 也就是先写粉板 等不忙的时候再写账本。
具体来说 当有一条记录需要更新的时候 InnoDB引擎就会先把记录写到redo log粉板 里面 并更新内存 这个时候更新就算完成了。 同时 InnoDB引擎会在适当的时候 将这个操作记录更新到磁盘里面 而这个更新往往是在系统比较空闲的时候做 这就像打烊以后掌柜做的事。 如果今天赊账的不多 掌柜可以等打烊后再整理。 但如果某天赊账的特别多 粉板写满了 又怎么办呢 这个时候掌柜只好放下手中的活儿 把粉板中的一部分赊账记录更新到账本中 然后把这些记录从粉板上擦掉 为记新账腾出空间。 与此类似 InnoDB的redo log是固定大小的 比如可以配置为一组4个文件 每个文件的大小是1GB 那么这块“粉板”总共就可以记录4GB的操作。 从头开始写 写到末尾就又回到开头循环写 如下图所示。 write pos是当前记录的位置 一边写一边后移 写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置 也是往后推移并且循环的 擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的是“粉板”上还空着的部分 可以用来记录新的操作。 如果write pos追上checkpoint 表示“粉板”满了 这时候不能再执行新的更新 得停下来先擦掉一些记录 把checkpoint推进一下。
redo log可以看做是一个基于文件的环形缓冲区如下图所示可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash-safe 要理解crash-safe这个概念 可以想想我们前面赊账记录的例子。 只要赊账记录记在了粉板上或写在了账本上 之后即使掌柜忘记了 比如突然停业几天 恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。 redo log写入逻辑
1redo log写入逻辑
事务执行过程中生成的redo log会先写到redo log buffer内存redo log buffer中的日志并不一定会立即持久化到磁盘当结点宕机或者系统掉电后redo log buffer中的日志丢失但此时事务没有提交binlog日志未落盘丢失数据并不影响一致性InnoDB有一个后台线程每隔1秒会把redo log buffer中的日志flush到文件系统的page cache中然后调用fsync持久化到磁盘
注redo log buffer是全局共用的。
2redo log可能的状态
存在redo log buffer中在进程内存中即图中红色部分写到磁盘(flush)但未持久化fsync)即在文件系统的page cache里即图中的黄色部分持久化到磁盘对应的是hard disk即图中的绿色部分。 3innodb_flush_log_at_trx_commit配置控制redo log的sync时机
设置为0表示每次事务提交时都只是把redo log留在redo log buffer中设置为1表示每次事务提交时都将redo log直接持久化到磁盘设置为2表示每次事务提交时都只是把redo log写到page cache可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去
4redo log的fsync时机
事务提交时根据innodb_flush_log_at_trx_commit执行fsync1s定时任务后台线程每隔1s执行一次flush和syncredo log buffer使用率过半时占用空间即将达到innodb_log_buffer_size的一半时后台线程会主动flush但不会sync其它并行的事务提交时顺带将这个事务的redo log buffer持久化到磁盘则其中其它事务的日志也一并flush并sync到磁盘
注flush是把redo log buffer中的redo log刷到page cache但不会持久化到磁盘sync是把redo log buffer中的redo log持久化到磁盘。
刷脏
1刷脏No Stale No Force流程的后半段用于持久化已提交的数据WAL为前半段
脏页当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为“脏页”干净页内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页”刷脏将脏页落盘的过程即为刷脏
2刷脏时机
Redo log文件写满系统停止更新操作向前推进并记录检查点腾出redo log空间系统内存不足当需要新的内存页而内存不够用的时候就要淘汰一些数据页空出内存给别的数据页使用。如果淘汰的是“脏页”就要先将脏页写到磁盘定时刷脏在空闲时定时刷脏正常关闭正常关闭mysql时需要将所有脏页flush到磁盘
3检查点的理解
所有的Redo Log文件可以看成是一个环形缓冲区红色区域为当前可写入区域绿色区域为待刷脏区域把checkpoint位置从CP推进到CP’需要将两个点之间的日志浅绿色部分对应的所有脏页都flush到磁盘上。之后从write pos到CP’之间就是可以再写入的redo log的区域。
4缓冲池内存使用策略缓冲池中的内存页有三种状态
还没有使用的使用了并且是干净页使用了并且是脏页
InnoDB的策略是尽量使用内存因此对于长时间运行的库来说未被使用的页面很少。
5数据页读取策略当要读取的数据不在内存的时候就需要到缓冲池中申请一个数据页。若没有未使用的数据页则需要根据LRU算法淘汰
如果要淘汰的是一个干净页就直接释放出来复用如果是脏页就必须将脏页先刷到磁盘变成干净页后才能复用
6InnoDB刷脏相关参数
innodb_io_capacity磁盘的io能力推荐设置成磁盘的IOPSinnodb_max_dirty_pages_pct脏页百分比默认75%引擎据此以及innodb_io_capacity控制刷脏的速率innodb_flush_neighbors刷脏时是否连带相邻的脏页一起刷新1表示开启
规范1innodb_flush_neighbors参数可能导致刷脏时刷新更多的脏页从而使得查询语句执行更慢建议使用SSD等IOPS较高的磁盘时关闭该选项
规范2一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例建议将redo log设置成4个1GB的文件
测试磁盘IOPS
fio -filename$filename -direct1 -iodepth 1 -thread -rwrandrw -ioenginepsync -bs16k -size500M-numjobs10 -runtime10 -group_reporting -namemytest
重要日志模块binlog binlog是归档日志保证数据可恢复实现主从复制。
binlog写入逻辑
事务执行过程中先把日志写到binlog cache事务提交的时候再把binlog cache写到binlog file中并清空binlog cache。在这个过程中系统给binlog cache分配了一片内存线程私有参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小就要暂存到磁盘。
注
一个事务的binlog是不能被拆开的因此不论这个事务多大也要确保一次性写入每个线程有自己binlog cache但是共用同一份binlog file flush和fsync
1binlog flush是指把binlog file flush到文件系统的page cache并没有把数据持久化到磁盘所以速度比较快掉电会丢数据
2binlog sync执行fsync将数据持久化到磁盘。一般情况下我们认为fsync才占磁盘的IOPS掉电不丢数据
3sync_binlog配置控制binlog sync的执行时机
sync_binlog0每次提交事务都只flush不执行fsyncsync_binlog1每次提交事务都会执行fsyncsync_binlogN(N1)的时候表示每次提交事务都flush但累积N次提交后才执行fsync
注在出现IO瓶颈的场景里将sync_binlog设置成一个比较大的值可以提升性能但可能在掉电时丢失没有sync的binlog日志
redo log和binlog的区别
1redo log是InnoDB引擎特有的binlog是MySQL的Server层实现的所有引擎都可以使用
2redo log是物理日志记录的是“在某个数据页上做了什么修改”binlog是逻辑日志记录的是执行语句的原始逻辑即SQL语句比如“给ID2这一行的c字段加1 ”
3redo log是循环写的空间固定会用完binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个并不会覆盖以前的日志
4redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候表示每次事务的redo log都直接持久化到磁盘。建议设置成1保证异常重启后数据不丢失
5binlog用于保证可恢复性。 sync_binlog这个参数设置成1的时候表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1这样可以保证MySQL异常重启之后binlog不丢失。
update语句执行流程 有了对这两个日志的概念性理解 我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。
执行器先找引擎取ID2这一行。 ID是主键 引擎直接用树搜索找到这一行。 如果ID2这一行所在的数据页本来就在内存中此处内存是指InnoDB中的BuffPool 就直接返回给执行器 否则 需要先从磁盘读入内存 然后再返回。执行器拿到引擎给的行数据 把这个值加上1 比如原来是N 现在就是N1 得到新的一行数据 再调用引擎接口写入这行新数据。引擎将这行新数据更新到内存中 同时将这个更新操作记录到redo log里面 此时redo log处于prepare状态。 然后告知执行器执行完成了 随时可以提交事务。执行器生成这个操作的binlog 并把binlog写入磁盘。执行器调用引擎的提交事务接口 引擎把刚刚写入的redo log改成提交commit 状态 更新完成。
这里我给出这个update语句的执行流程图 图中浅色框表示是在InnoDB内部执行的 深色框表示是在执行器中执行的。 你可能注意到了 最后三步看上去有点“绕” 将redo log的写入拆成了两个步骤 prepare和commit 这就是两阶段提交。
保证日志的一致性 两阶段提交
问为什么必须有“两阶段提交”呢
这是为了让redo log和binlog之间的逻辑一致。 由于redo log和binlog是两个独立的逻辑如果不用两阶段提交就无法保证两份日志的一致性比如仍然用前面的update语句来做例子 假设当前ID2的行 字段c的值是0
先写redo log后写binlog则redo log多数据。假设在redo log写完binlog还没有写完MySQL进程异常重启。redo log写完之后系统即使崩溃仍然能够把数据恢复回来所以恢复后这一行c的值是1。由于binlog没写完就crash了之后备份存起来的binlog里面就没有这条语句。此时如果要用binlog来恢复临时库的话由于这个语句的binlog丢失这个临时库就会少了这一次更新恢复出来的这一行c的值就是0与原库的值不同先写binlog后写redo log则binlog多数据。如果在binlog写完之后crash由于redo log还没写崩溃恢复以后这个事务无效所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以在之后用binlog来恢复的时候就多了一个事务出来恢复出来的这一行c的值就是1与原库的值不同
可以看到 如果不使用“两阶段提交” 那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。你可能会说 这个概率是不是很低 平时也没有什么动不动就需要恢复临时库的场景呀
其实不是的 不只是误操作后需要用这个过程来恢复数据。 当你需要扩容的时候 也就是需要再多搭建一些备库来增加系统的读能力的时候 现在常见的做法也是用全量备份加上应用binlog来实现的 这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
Crash Recovery逻辑
还用前面的update语句为例进行说明update语句的执行流程图如下所示 图中浅色框表示是在InnoDB内部执行的 深色框表示是在执行器中执行的。 问在update语句执行过程中如果在时刻A、时刻B发生崩溃分别会造成什么后果
答
1时刻A写入redo log 处于prepare阶段之后、写binlog之前发生了崩溃crash。由于此时binlog还没写redo log也还没提交所以崩溃恢复的时候这个事务会回滚
2时刻B也就是binlog写完redo log还没commit前发生crash
如果redo log里面的事务是完整的也就是已经有了commit标识说明binlog已写完且完整则直接提交如果redo log里面的事务只有完整的prepare此时可能存在binlog不完整的情况则判断对应的事务binlog是否存在并完整 如果binlog完整则提交事务否则回滚事务
组提交
1双1配置MySQL的“双1”配置指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1在双1配置下一个事务完整提交前需要等待两次刷盘一次是redo logprepare 阶段一次是binlog为了减少事务提交产生的IOMySQL使用了组提交机制group commit
未完...后续接着看PPT 小结 问1MySQL怎么知道binlog是完整的?
一个事务的binlog是有完整格式的
1statement格式的binlog最后会有COMMIT
2row格式的binlog最后会有一个XID event。
3binlog-checksum用来验证binlog内容的正确性 MySQL 5.6.2 问2redo log和binlog是怎么关联起来的?
它们有一个共同的数据字段叫XID
1崩溃恢复的时候会先扫描最近的binlog获取所有已提交的XID列表
2之后按顺序扫描redo log
如果碰到既有prepare、又有commit的redo log就直接提交如果碰到只有parepare、而没有commit的redo log就拿着XID去binlog找对应的事务若有则提交反之回滚 问3处于prepare阶段的redo log加上完整binlog重启就能恢复MySQL为什么要这么设计?
为了保证crash recovery的一致性无论对主库还是从库 问4redo log一般设置多大
redo log太小的话会导致很快就被写满然后不得不强行刷redo log这样WAL机制的能力就发挥不出来了
所以如果是现在常见的几个TB的磁盘的话直接将redo log设置为4个文件、每个文件1GB 问5正常运行中的实例数据写入后的最终落盘是从redo log更新过来的还是从buffer pool更新过来的呢
redo log并没有记录数据页的完整数据所以它并没有能力自己去更新磁盘数据页也就不存在“数据最终落盘是由redo log更新过去”的情况
1如果是正常运行的实例数据页被修改以后跟磁盘的数据页不一致称为脏页。最终数据落盘就是把内存中的数据页写盘。这个过程基本与redo log毫无关系
2在崩溃恢复场景中InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新就会将它读到内存然后让redo log更新内存内容。更新完成后内存页变成脏页就回到了第一种情况的状态
注加载数据页的时候是以page为单位所以数据落盘的时候也是以page为单位。 问6执行update语句以后去执行hexdump命令直接查看ibd文件内容为什么没有看到数据有改变呢
这可能是因为WAL机制的原因。update语句执行完成后InnoDB只保证写完了redo log、内存可能还没来得及将数据写到磁盘 问7为什么binlog cache是每个线程自己维护的而redo log buffer是全局共用的
1MySQL这么设计的主要原因是binlog是不能“被打断的”如果binlog cache是全局的则可能会导致binlog交叉写入。一个事务的binlog必须连续写因此要整个事务完成后再一起写到文件里
2而redo log并没有这个要求中间有生成的日志可以写到redo log buffer中。redo log buffer中的内容还能“搭便车”其他事务提交的时候可以被一起写到磁盘中
3binlog存储是以statement或者row格式存储的而redo log是以page页格式存储的。page格式天生就是共有的而row格式只跟当前事务相关 问8如果binlog写完盘以后发生crash这时候还没给客户端答复就重启了。等客户端再重连进来发现事务已经提交成功了这是不是bug
不是。实际上数据库的crash-safe保证的是
1如果客户端收到事务成功的消息事务就一定持久化了
2如果客户端收到事务失败比如主键冲突、回滚等的消息事务就一定失败了
3如果客户端收到“执行异常”的消息应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部数据和日志之间主库和备库之间一致就可以了 问9sync_binlog和binlog_group_commit_sync_no_delay_count有什么区别
1sync_binlog N每个事务write后就响应客户端了。刷盘是N次事务后刷盘。N次事务之间宕机数据丢失。
2binlog_group_commit_sync_no_delay_countN 必须等到N个后才能提交。换言之会增加响应客户端的时间。但是一旦响应了那么数据就一定持久化了。宕机的话数据是不会丢失的。该配置先于sync_binlog执行
