做外贸营销网站销售咋样,郑州电力高等专科学校学费多少,wordpress播放代码,网站降权前言
SQL执行流程图文分析#xff1a;从连接到执行的全貌_一条 sql 执行的全流程?-CSDN博客文章浏览阅读1.1k次#xff0c;点赞20次#xff0c;收藏12次。本文探讨 MySQL 执行一条 SQL 查询语句的详细流程#xff0c;从连接器开始#xff0c;逐步介绍了查询缓存、解析 S…前言
SQL执行流程图文分析从连接到执行的全貌_一条 sql 执行的全流程?-CSDN博客文章浏览阅读1.1k次点赞20次收藏12次。本文探讨 MySQL 执行一条 SQL 查询语句的详细流程从连接器开始逐步介绍了查询缓存、解析 SQL、执行计划优化以及执行器等各个功能模块的作用。同时还解释了 MySQL 的内部架构包括 Server 层和存储引擎层并讨论了短连接与长连接、索引下推等相关概念。通过本文读者能够全面了解 MySQL 在执行 SQL 查询时的内部工作原理以及如何优化查询性能。_一条 sql 执行的全流程?https://blog.csdn.net/weixin_73077810/article/details/137524227以上文章是针对查询操作的流程剖析这一套流程更新语句也是同样会走一遍不过更新语句的流程会涉及到 undo log、redo log 、binlog 这三种日志 undo log是 Innodb 存储引擎层生成的日志实现了事务中的原子性主要用于事务回滚和 MVCC。 redo log是 Innodb 存储引擎层生成的日志实现了事务中的持久性主要用于掉电等故障恢复 binlog 是 Server 层生成的日志主要用于数据备份和主从复制 下面就让我们好好深入的来学习一下这三类日志的细节促进对于事务型SQL的执行过程的理解、 undo log undo log 是一种用于撤销回退的日志。在事务没提交之前MySQL 会先记录更新前的数据到 undo log 日志文件里面当事务回滚时可以利用 undo log 来进行回滚。另外undo log 还有一个作用通过 ReadView undo log 实现 MVCC多版本并发控制。 redo log 为了防止断电导致数据丢失的问题当有一条记录需要更新的时候InnoDB 引擎就会先更新内存同时标记为脏页然后将本次对这个页的修改以 redo log 的形式记录下来这个时候更新就算完成了。MySQL 的写操作并不是立刻写到磁盘上而是先写日志然后在合适的时间再写到磁盘上。在事务提交时只要先将 redo log 持久化到磁盘即可可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时虽然脏页数据没有持久化但是 redo log 已经持久化接着 MySQL 重启后可以根据 redo log 的内容将所有数据恢复到最新的状态。 redo log 要写到磁盘数据也要写磁盘为什么要多此一举 写入 redo log 的方式使用了追加操作 所以磁盘操作是顺序写而写入数据需要先找到写入位置然后才写到磁盘所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多因此 redo log 写入磁盘的开销更小。 产生的 redo log 是直接写入磁盘的吗 不是的。实际上 执行一个事务的过程中产生的 redo log 也不是直接写入磁盘的因为这样会产生大量的 I/O 操作而且磁盘的运行速度远慢于内存。 redo log 什么时候刷盘 主要有下面几个时机 MySQL 正常关闭时 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时会触发落盘 InnoDB 的后台线程每隔 1 秒将 redo log buffer 持久化到磁盘。 redo log 文件写满了怎么办 默认情况下 InnoDB 存储引擎有 1 个重做日志文件组「重做日志文件组」由有 2 个 redo log 文件组成每个 redo log File 的大小是固定且一致的重做日志文件组是以循环写的方式工作的从头开始写写到末尾就又回到开头相当于一个环形。
所以 InnoDB 存储引擎会先写 ib_logfile0 文件当 ib_logfile0 文件被写满的时候会切换至 ib_logfile1 文件当 ib_logfile1 文件也被写满时会切换回 ib_logfile0 文件。 redo log 是循环写的方式相当于一个环形InnoDB 用 write pos 表示 redo log 当前记录写到的位置用 checkpoint 表示当前要擦除的位置如下图 如果 write pos 追上了 checkpoint就意味着 redo log 文件满了这时 MySQL 不能再执行新的更新操作也就是说 MySQL 会被阻塞此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中然后标记 redo log 哪些记录可以被擦除接着对旧的 redo log 记录进行擦除等擦除完旧记录腾出了空间checkpoint 就会往后移动图中顺时针然后 MySQL 恢复正常运行继续执行新的更新操作。
binlog MySQL 在完成一条更新操作后Server 层还会生成一条 binlog写到 binlog cacheServer 层的 cache等之后事务提交的时候再把 binlog cache 写到 binlog 文件中。在此期间事务中的binlog如果超过了binlog cache的大小就要暂存到磁盘。虽然每个线程有自己 binlog cache但是最终都写到同一个 binlog 文件
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志不会记录查询类的操作比如 SELECT 和 SHOW 操作。 如果不小心整个数据库的数据被删除了能使用 redo log 文件恢复数据吗 不可以使用 redo log 文件恢复只能使用 binlog 文件恢复。因为 redo log 文件是循环写是会边写边擦除日志的只记录未被刷入磁盘的数据的物理日志已经刷入磁盘的数据都会从 redo log 文件里擦除。 binlog 文件保存的是全量的日志也就是保存了所有数据变更的情况理论上只要记录在 binlog 上的数据都可以恢复所以如果不小心整个数据库的数据被删除了得用 binlog 文件恢复数据。
两阶段提交 事务提交后redo log 和 binlog 都要持久化到磁盘但是这两个是独立的逻辑可能出现半成功的状态这样就造成两份日志之间的逻辑不一致。如果出现半成功状态就会造成主从环境的数据不一致性。这是因为 redo log 影响主库的数据binlog 影响从库的数据所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。 MySQL 为了避免出现两份日志之间的逻辑不一致的问题使用了「两阶段提交」来解决两阶段提交其实是分布式事务一致性协议它可以保证多个逻辑操作要不全部成功要不全部失败不会出现半成功的状态。 两阶段提交把单个事务的提交拆分成了 2 个阶段分别是「准备Prepare阶段」和「提交Commit阶段」每个阶段都由协调者和参与者共同完成。注意不要把提交阶段和 commit 语句混淆了commit 语句执行的时候会包含提交阶段。 举个拳击比赛的例子两位拳击手参与者开始比赛之前裁判协调者会在中间确认两位拳击手的状态类似于问你准备好了吗 准备阶段裁判协调者会依次询问两位拳击手参与者是否准备好了然后拳击手听到后做出应答如果觉得自己准备好了就会跟裁判说准备好了如果没有自己还没有准备好比如拳套还没有带好就会跟裁判说还没准备好。 提交阶段如果两位拳击手参与者都回答准备好了裁判协调者宣布比赛正式开始两位拳击手就可以直接开打如果任何一位拳击手参与者回答没有准备好裁判协调者会宣布比赛暂停对应事务中的回滚操作。
两阶段提交的过程是怎样的
为了保证这两个日志的一致性MySQL 使用了内部 XA 事务内部 XA 事务由 binlog 作为协调者存储引擎是参与者。
当客户端执行 commit 语句或者在自动提交的情况下MySQL 内部开启一个 XA 事务分两阶段来完成 XA 事务的提交如下图 prepare 阶段将 XID内部 XA 事务的 ID 写入到 redo log同时将 redo log 对应的事务状态设置为 prepare然后将 redo log 持久化到磁盘innodb_flush_log_at_trx_commit 1 的作用 commit 阶段把 XID 写入到 binlog然后将 binlog 持久化到磁盘sync_binlog 1 的作用接着调用引擎的提交事务接口将 redo log 状态设置为 commit此时该状态并不需要持久化到磁盘只需要 write 到文件系统的 page cache 中就够了因为只要 binlog 写磁盘成功就算 redo log 的状态还是 prepare 也没有关系一样会被认为事务已经执行成功 在两阶段提交的不同时刻MySQL 异常重启会出现什么现象 不管是时刻 Aredo log 已经写入磁盘 binlog 还没写入磁盘还是时刻 B redo log 和 binlog 都已经写入磁盘还没写入 commit 标识崩溃此时的 redo log 都处于 prepare 状态。
在 MySQL 重启后会按顺序扫描 redo log 文件碰到处于 prepare 状态的 redo log就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID 如果 binlog 中没有当前内部 XA 事务的 XID说明 redolog 完成刷盘但是 binlog 还没有刷盘则回滚事务。对应时刻 A 崩溃恢复的情况。 如果 binlog 中有当前内部 XA 事务的 XID说明 redolog 和 binlog 都已经完成了刷盘则提交事务。对应时刻 B 崩溃恢复的情况。 可以看到对于处于 prepare 阶段的 redo log即可以提交事务也可以回滚事务这取决于是否能在 binlog 中查找到与 redo log 相同的 XID如果有就提交事务如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。
所以说两阶段提交是以 binlog 写成功为事务提交成功的标识因为 binlog 写成功了就意味着能在 binlog 中查找到与 redo log 相同的 XID。 事务没提交的时候redo log 会被持久化到磁盘吗 会的。事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。
修改操作流程 三个日志讲完了至此我们可以先小结下update 语句的执行过程。 当优化器分析出成本最小的执行计划后执行器就按照执行计划开始进行更新操作。
具体更新一条记录 UPDATE t_user SET name xiaolin WHERE id 1; 的流程如下: 执行器负责具体执行会调用存储引擎的接口通过主键索引树搜索获取 id 1 这一行记录 如果 id1 这一行所在的数据页本来就在 buffer pool 中就直接返回给执行器更新 如果记录不在 buffer pool将数据页从磁盘读入到 buffer pool返回记录给执行器。 执行器得到聚簇索引记录后会看一下更新前的记录和更新后的记录是否一样 如果一样的话就不进行后续更新流程 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层让 InnoDB 真正的执行更新记录的操作 开启事务 InnoDB 层更新记录前首先要记录相应的 undo log因为这是更新操作需要把被更新的列的旧值记下来也就是要生成一条 undo logundo log 会写入 Buffer Pool 中的 Undo 页面不过在内存修改该 Undo 页面后需要记录对应的 redo log。 InnoDB 层开始更新记录会先更新内存同时标记为脏页然后将记录写到 redo log 里面这个时候更新就算完成了。为了减少磁盘I/O不会立即将脏页写入磁盘后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术MySQL 的写操作并不是立刻写到磁盘上而是先写 redo 日志然后在合适的时间再将修改的行数据写到磁盘上。 至此一条记录更新完了。 在一条更新语句执行完成后然后开始记录该语句对应的 binlog此时记录的 binlog 会被保存到 binlog cache并没有刷新到硬盘上的 binlog 文件在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。 事务提交剩下的就是「两阶段提交」的事情了
