MySQL查询和更新语句的执行过程_mysql更新数据时是先查出数据再更新吗-CSDN博客

本文链接：https://blog.csdn.net/javaanddonet/article/details/109904550

微信搜索“coder-home”或扫一扫下面的二维码，关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我
在这里插入图片描述

文章目录

MySQL整体架构

要想了解MySQL数据库查询语句的执行过程，首选我们要了解MySQL整体的架构是什么样子的。然后才能进一步知道查询语句的执行过程是怎么样子的。

MySQL整体架构从宏观上可以分为如下两大部分：

Server层
存储引擎层

Server层

Server层提供了MySQL核心的服务功能。

所有垮存储引擎的功能都在Server层来实现。比如：存储过程、函数、视图、触发器等都是在这一层实现的。

这一层又包含如下几个功能组件

连接器：负责建立和维护客户端发起的连接请求。
查询缓存：负责缓存查询结果，便于提高查询效率。
分析器：用来解析SQL语句，分析词法和语法是否正确。通过它，MySQL知道你要做什么。
优化器：用来生成SQL语句的执行计划，决定SQL语句采用哪种方式来执行效率比较高。通过它，MySQL知道该如何做。
执行器：真正用来执行SQL语句的功能模块。通过它，MySQL才会真正的做，也就是去执行SQL语句。

连接器

建立连接，验证用户权限。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是 8 小时。

查询缓存

该功能比较有局限，MySQL8.0版本中已经废除该功能，因为该功能的弊大于利。查询缓存的失效率特别高，只要表中的数据有任何更新，对于这个表的查询语句对应的缓存将全部失效。

查询缓存中的大概原理是使用SQL语句作为key，使用查询得到的结果集合作为value。所以，查询的SQL需要一模一样，多一个空格都不可以使用到缓存。

解析器(分析器)

解析SQL语句中的语法，生成解析树。验证关键词是否正确，比如：select、from、where、group by、order by等关键词是否正确。

预处理器

根据上一步生成的解析树，再进一步分析SQL语句的语义是否正确，验证数据表是否存在，查询的字段是否存在。比如：

select a.id, a.name from table1 as a;

这个语句中的 a.id是否存在，a.name是否写错等验证是在这里进行验证的。

优化器

根据SQL语句，生成执行计划。有可能有多种执行方式。给予成本cost计算，来决定采用哪一种执行计划。

执行器

真正负责执行SQL语句的功能模块。

在真正开始执行SQL语句之前，会再次判断一下当前登录的用户，是否对相关的表有查询权限，如果有，会按照执行计划去执行SQL，否则直接返回权限不足。它会调用存储引擎层的API接口，然后接受存储引擎层返回的结果集，最后把结果返回给客户端。

疑问点：为什么这里还要做权限的验证？没有在建立连接的时候去做？

在连接器上验证的是客户端有没有连接到MySQL服务端的权限，而这里说的权限验证指的是验证用户有没有操作具体某张表的权限。这里网上找到一个比较好的比喻：譬如说你去景区旅游，先在景区门口验票，看你有没有进入里面的权限，进去后可能某个景点也是收费的，想进去也要验票！

存储引擎层

它只负责数据的存储和读取，以插件的方式提供给MySQL来使用。

包括但不限于如下几种常见的存储引擎：

InnoDB
MyISAM
Memory
CSV
Archive

查询语句执行过程

了解完MySQL的整体架构和架构内各个功能组件之后，我们就可以大概了解MySQL的查询语句到底是怎么执行的了。

下面画出了查询SQL语句的执行过程，如下所示：

在这里插入图片描述

简单根据上图的流程，描述一下查询语句的执行过程。

客户端发起连接MySQL服务的请求。
连接器接受客户端的请求，验证其账号密码信息是否正确。
1. 如果正确运行登录MySQL数据库。
2. 如果不正确拒绝登录，并返回错误信息。
如果开启了MySQL的查询缓存的功能，会先根据查询的SQL语句去查询缓存中查看是否有这样的SQL对应的缓存结果。
1. 如果发现缓存结果，则直接把缓存结果返回个客户端。
2. 如果没有缓存结果，则跳出查询缓存模块，走向解析器模块。
解析器解析SQL语句是否正确，语法词法是对。
1. 如果词法或者语法有问题，则返回错误信息给客户端。
2. 如果词法语句正确，则继续往后面的组件“预处理器”中走。
预处理器会根据验证后SQL语句生成语法树。
查询优化器根据预处理生成的语法树，生成一个最优的执行计划。
执行器根据优化器提供的执行计划去执行调用存储引擎层的接口执行SQL语句。
存储引擎层接受到执行器的请求后，去查询缓存中去查询是否有对应的数据。
1. 如果有对应的数据则直接返回给Server层的执行器。
2. 如果没有对应要查询的数据，则根据对应的执行计划去觉得如何去磁盘中扫描对应要查询的数据。这里查询的时候，可能使用到索引，也可能不使用索引，具体就看执行计划是怎么样的。
3. 这里的查询缓存并不是Server层的查询缓存，而是存储引擎内部的查询缓存。它是有每一个存储引擎自己来实现的。
执行器接受存储引擎层的结果，返回给客户端。

更新语句执行过程

MySQL更新语句的执行过程是包含查询语句的执行过程的，因为你要更新一条记录，首先都查询到这条记录，然后才能基于查询出来的数据再做更新的操作。

基于前面MySQL查询语句执行过程的流程图，我们把存储引擎内部的需要执行步骤也添加上，就得到了如下更新语句的流程图，如下所示：

在这里插入图片描述

简单根据上图的流程，描述一下更新语句的执行过程。

客户端发起连接MySQL服务的请求。
连接器接受客户端的请求，验证其账号密码信息是否正确。
1. 如果正确运行登录MySQL数据库。
2. 如果不正确拒绝登录，并返回错误信息。
如果开启了MySQL的查询缓存的功能，会先根据查询的SQL语句去查询缓存中查看是否有这样的SQL对应的缓存结果。
1. 如果发现缓存结果，则直接基于缓存的结果进行更新语句的操作，进入下面的第9步。
2. 如果没有缓存结果，则跳出查询缓存模块，走向解析器模块。
解析器解析SQL语句是否正确，语法词法是对。
1. 如果词法或者语法有问题，则返回错误信息给客户端。
2. 如果词法语句正确，则继续往后面的组件“预处理器”中走。
预处理器会根据验证后SQL语句生成语法树。
查询优化器根据预处理生成的语法树，生成一个最优的执行计划。
执行器根据优化器提供的执行计划去执行调用存储引擎层的接口执行SQL语句。
存储引擎层接受到执行器的请求后，去查询缓存中去查询是否有对应的数据。
1. 如果有对应的数据则直接返回给Server层的执行器。
2. 如果没有对应要查询的数据，则根据对应的执行计划去觉得如何去磁盘中扫描对应要查询的数据。这里查询的时候，可能使用到索引，也可能不使用索引，具体就看执行计划是怎么样的。
3. 这里的查询缓存并不是Server层的查询缓存，而是存储引擎内部的查询缓存。它是有每一个存储引擎自己来实现的。
执行器接受存储引擎层的结果，进行更新语句的操作，然后把更新后的数据，发送给存储引擎层。
存储引擎层接收到执行器发送过来的更新数据后，做了如下几个动作
1. 首选去更新缓存中的数据，避免后续有再次查询此次被修改的数据的时候，从缓存中返回给执行器的数据是旧的数据。
2. 在日志缓存中记录redolog，并且把redolog的状态标记为待提交prepare状态。
3. 返回消息给执行器，告知上面的操作已经完成。
执行器在收到存储引擎层的更新消息后，做了如下几个动作
1. 在binlog日志中记录此次的修改语句，便于以后通过binlog恢复数据的时候，不丢失此次修改操作。
2. 发型消息给存储引擎层，告诉它可以把redolog的状态有prepare改为commit状态。
存储引擎层接收到执行器的消息后，做了如下几个动作
1. 更新日志缓存中的redolog的状态为commit状态。
2. 发送消息通知执行器，redolog已经提交。
执行器接收到存储引擎层的消息后，通知客户端，更新成功。至此，整个更新语句就执行完成了。

更新语句注意项目

binlog日志何时刷盘

执行器在记录binlog的时候，也是把binlog写到缓存中的，那么这个数据什么时候持久化到binlog二进制日志文件中呢？

sync_binlog这个参数是用来决定binlog什么时候从缓冲中把binlog持久化到磁盘。它的置为 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync，具体什么时候刷盘，是由操作系统决定何时刷盘。
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；建议配置此选项，保证不丢失提交的事务。
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。这样会有丢数据的风险。

redolog日志何时刷盘

存储引擎层在更新数据的时候，是更新的缓存中的数据，写redolog的时候，也是写入的日志缓存中的。那么这些数据什么时候从缓冲中更新到磁盘呢？

innodb_flush_log_at_trx_commit这个参数是决定redolog是什么时候从log buffer中持久化到磁盘的。它的值为1的时候，表示每一个事务提交后，都马上把log buffer中的redolog持久化到磁盘。建议把这个参数的值设置为1，这样可以保证MySQL掉电等异常重启后数据不丢失。

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在redo log buffer中
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

redolog的两阶段提交

上面更新语句的执行过程中，redolog分为两个状态：prepare和commit两个状态，这就是我们平时所说的redolog的“两阶段提交”。那么为什么必须有“两阶段提交”呢？这是为了让redolog和binlog两份日志之间的逻辑一致。

redolog是有存储引擎层实现的，也就是我们平时经常使用的innodb存储引擎实现的，其他存储引擎中没有这个redolog的功能。它的存在保证了MySQL具有crash-safe的能力，其他的存储引擎是不具备这个能力的。

binlog是有server层实现的，所有的存储引擎都可以使用这个功能。它起到归档备份的作用，在主从复制的时候，可以作为从库数据复制的来源。

如果不使用两阶段提交，那么就是先写binlog在写redolog，或者先写redolog在写binlog。这样会导致什么结果呢？我们就拿要更新某张表某一行数据来举例说明情况吧。

先写binlog再写redolog

binlog写完后，还没有来的及写redolog，此时如果MySQL掉电了，情况会是怎么样的？binlog中记录了修改的数据记录，但是redolog中没有记录修改数据的内容，此时崩溃恢复的时候，因为redolog中没有记录，所以掉点前修改的记录无效，数据库中仍然是修改前的数据内容。但是binlog中已经记录了对数据的修改记录。如果此时我们使用历史备份的数据文件和此binlog去恢复一个新的数据库环境，那么在新还原的数据库中，就有对某一行的数据的更改。这与我们现在正在使用的数据库中的数据是不匹配的，于是就导致了数据不一致的现象。
先写redolog再写binlog

redolog写完后，还没有来得及写binlog，此时如果MySQL掉电了，异常重启了。由于redolog已经写完，innodb引擎的表有crash-safe的能力，所以在崩溃恢复之后，数据库中的这行记录是我们修改之后的内容。但是在binlog中并没有记录对这一行数据的修改。而此时如果我们那历史备份数据和此binlog去还原一个新的数据库环境，那么在还原出来的数据库中，这一行数据是修改之前的数据内容，这和我们现在的数据库环境是不匹配的，于是就有了数据不一致的现象。

综上两种方式，不管先写哪一个日志文件都会导致数据不一致的现象发生，而如果分为两节点提交redolog，那么久可以避免这样的现象发生。保证了两份日志数据在逻辑上的一致性。在还原数据的时候，也会保持数据的一致性。

binlog和redolog的区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“update t set a=‘yes’ where id=1”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。因此binlog有归档的作用。

为什么要有binlog

binlog有归档的作用，它里面的日志是顺序写的，不会被覆盖。MySQL 系统依赖于 binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方，例如：MySQL系统高可用的基础，就是基于binlog的复制。

为什么要有redolog

如果MySQL在修改数据的时候，直接去修改磁盘中的数据，那么需要进行磁盘的随机IO查找并写入。这样是很慢的。所以才因为了redolog循环可擦写的日志模块，顺序写入的IO效率要比随机块很多，可以和内存相媲美。先写入日志后，等待空闲的时候，在写入磁盘。这就是MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

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