mysql c# 执行多条sql语句_【MySQL深入】一条SQL更新语句的执行过程_白夜行-CSDN博客...

原文作者：白夜行515

原文标题：【MySQL深入】一条SQL更新语句的执行过程

发布时间：2021-01-23 11:23:58

1.MySQL逻辑架构示意图

对于一条update语句来说，也会执行上图中的流程，如果您对于上图中的连接器、分析器等名词不太熟悉，请查看上一篇文章：MySQL逻辑架构中各名词详解

2.一条示例update语句执行过程简析

# 建表SQL

create table `article` (

`id` bigint(20) unsigned NOT NULL DEFAULT 0,

`commentcnt` int(11) unsigned NOT NULL DEFAULT 0,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据

insert into `article` (`id`, `commentcnt`) values(1, 11);

insert into `article` (`id`, `commentcnt`) values(2, 22);

更新数据sql

update `article` set `commentcnt` = `commentcnt` + 1 where `id` = 2;

该SQL的执行过程：

连接器会先检查权限，若没有权限就直接返回错误信息，如果有权限就会清除查询缓存(MySQL 8.0版本之前)，将article表所有缓存结果都清空(一般不建议使用查询缓存)。接下来执行下一步。

通过分析器先进行词法分析，提取sql语句里面的关键字。示例SQL取的update，然后提取要更新的表名article ，更新条件是id= 2 ，然后进行语法分析，判断sql语句是否正确，如果有错会返回报错信息，否则执行下一步。

优化器确定执行方案。优化器根据自己的优化算法选择一个执行效率最好的一个方案。比如：优化器会去找id字段有没有索引，使用id主键索引，执行计划确定后就会执行下一步。

执行器首先会判断当前用户对article表是否有更新的权限，如果没有权限就会返回权限错误，若有权限会打开表执行，根据表的引擎定义调用引擎提供的接口，返回引擎执行结果。

与查询SQL流程不同的是，更新SQL流程还涉及两个重要的日志模块redo log 和 binlog

3. MySQL日志

3.1 redo log

前言：之前MySQL每一条更新记录，都需要写入磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了WAL技术解决该问题。

WAL(Write-Ahead Logging)预写日志：先写日志，再写磁盘，将随机写转换成了顺序写，大大提升了数据库的性能。WAL技术的典型应用就是redo log

redo log的定义：一块固定大小的重做物理日志文件，可以循环写。redo log记录的是“在某个数据页上做了什么修改”，是InnoDB引擎特有的日志。

redo log的工作流程：当有一条记录更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 会在适当的时候，将这个操作记录更新到磁盘

redo log写示例：比如MySQL配置redo log一组4个1G的文件，写redo log的流程示意图如下

write pos：当前记录的位置，边写边往后移动，直到移动到ib_logfile_3的末尾，然后会回到ib_logfile_0文件的开头

checkpoint：当前要擦除的位置，和write pos一样也是往后移动且会循环，擦除记录前要把记录保存到磁盘中

write pos和checkpoint之前的空间：记录新的操作，如果write pos追上checkpoint，就表明redo log已经没有空间来记录新的操作了，这时就需要把擦除当前记录保存到磁盘中，从而保证新的操作可以被记录。

crash-safe：因为有redo log，所以InnoDB可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

3.2 binlog

binlog (归档日志)定义：server层日志，是逻辑日志，记录语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。可以追加写入，即 binlog 文件写到一定大小后会切换到下一个binlog文件，并不会覆盖以前的日志。

binlog的三种模式

模式

statement

row

mixed

定义

记录的是SQL语句

记录行的内容(记两条, 更新前和更新后都有)

statement和row的结合，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式

优点

更新时只需要记录一条SQL，减少日志量

便于恢复数据

MySQL自动选择最优模式

缺点

主从复制时某些函数sleep()或功能不能正确复制，导致出现bug

数据更新时产生大量文件，特别是alter table语句，全表数据变更

表结构变更等需要修改大量数据时使用statement，update或delete操作还是使用row模式记录

注：一般采用row模式，因为遇到时间，从库可能会出现不一致的情况，但是row更新前后都有

binlog保证完整性的方法：statement格式的binlog, 最后会有Commit标识。row格式的binlog, 最后会有一个XID event标识，在MySQL5.6.2版本以后, 还引入了binlog-checksum参数, 用来验证binlog内容的正确性。

3.3 redo log和binlog的区别

redo是物理日志，binlog是逻辑日志

redo log是InnoDB引擎特有的，binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log循环写，空间固定会用完；binlog可以追加写入。

3.4 出现两份日志的原因

binlog没有能力恢复“数据页”，redo log 来实现 crash-safe 能力。

redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。binlog 日志来实现归档

MySQL系统依赖于binlog，例如：MySQL系统高可用的基础，就是binlog复制。

4. update语句执行过程详解

update `article` set `commentcnt` = `commentcnt` + 1 where `id` = 2;

有了redo log 和 binlog的知识储备后，再来看看这条update语句的执行原理，

4.1 update语句的执行流程图

(图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的)

更新SQL执行流程：

连接数据库，清空查询缓存(MySQL8.0之前)，分析词法和语法后知道这是一条update语句，优化器决定使用ID这个索引

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。

如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器，否则，需要先从磁盘读入内存，然后再返回。

执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，更新完成

4.2 两阶段提交

定义：更新流程中写入redo log的过程拆成了两个步骤prepare和commit。如果不使用两阶段提交，数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

作用：让redo log和binlog逻辑上保持一致. 如果在commit时崩溃了, 虽然没有commit, 但是prepare和binlog完整, 所以重启之后会自动commit

MySQL异常重启后的崩溃恢复规则:

如果redo log里面的事务是完整的, 也就是有了commit标识, 则直接提交

如果redo log里面的事务只有完整的prepare, 则判断对应的事务是否存在完整的binlog，如果是, 则提交事务，否则,回滚事务

redo log和binlog的如何关联

它们有一个共同的数据字段XID. 崩溃恢复的时候, 会按顺序扫描redo log

如果碰到既有prepare, 又有commit的redo log, 就直接提交

如果碰到只有prepare, 而没有commit的redo log, 就拿着XID去binlog找对应的事务

双1配置保证不丢数据：

innodb_flush_log_at_trx_commit=1 每次事务的redo log都直接持久化到磁盘。可以保证MySQL异常重启之后数据不丢失。

sync_binlog=1 每次事务的binlog都持久化到磁盘。可以保证MySQL异常重启之后binlog不丢失。

Post Views:

9