数据库
MySQL
MySQL DBA

mysql执行流程是怎么样的?

关注者
15
被浏览
9,802

6 个回答

MySQL 提供了多种存储引擎来支持事务。支持事务的存储引擎有 InnoDB 和 BDB,其中,InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现,MyISAM 存储引擎不支持事务。

为了维护 MySQL 服务器,经常需要在 MySQL 数据库中进行日志操作:

  • UNDO 日志:复制事务执行前的数据,用于在事务发生异常时回滚数据。
  • REDO 日志:记录在事务执行中,每条对数据进行更新的操作,当事务提交时,该内容将被刷新到磁盘。

默认设置下,每条 SQL 语句就是一个事务,即执行 SQL 语句后自动提交。为了达到将几个操作做为一个整体的目的,需要使用 BEGIN 或 START TRANSACTION 开启一个事务,或者禁止当前会话的自动提交。

执行事务的语法和流程

SQL 使用下列语句来管理事务。

1) 开始事务

BEGIN;

START TRANSACTION;

这个语句显式地标记一个事务的起始点。

2) 提交事务

MySQL 使用下面的语句来提交事务:

COMMIT;

COMMIT 表示提交事务,即提交事务的所有操作,具体地说,就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中,事务正常结束。

提交事务,意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分,因此也标志着一个事务的结束。一旦执行了该命令,将不能回滚事务。只有在所有修改都准备好提交给数据库时,才执行这一操作。

3) 回滚(撤销)事务

MySQL 使用以下语句回滚事务:

ROLLBACK;

ROLLBACK 表示撤销事务,即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

当事务执行过程中遇到错误时,使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处。同时,系统将清除自事务起点或到某个保存点所做的所有的数据修改,并且释放由事务控制的资源。因此,这条语句也标志着事务的结束。

总结

BEGIN 或 START TRANSACTION 语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中,直至遇到 ROLLBACK 语句或 COMMIT 语句。如果事务中某一操作失败且执行了 ROLLBACK 语句,那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。如果事务中的所有操作都全部正确完成,并且使用了 COMMIT 语句向数据库提交更新数据,则此时的数据又处在新的一致状态。

注意事项

MySQL 事务是一项非常消耗资源的功能,大家在使用过程中要注意以下几点。

1) 事务尽可能简短

事务的开启到结束会在数据库管理系统中保留大量资源,以保证事务的原子性、一致性、隔离性和持久性。如果在多用户系统中,较大的事务将会占用系统的大量资源,使得系统不堪重负,会影响软件的运行性能,甚至导致系统崩溃。

2) 事务中访问的数据量尽量最少

当并发执行事务处理时,事务操作的数据量越少,事务之间对相同数据的操作就越少。

3) 查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据,因此应尽量不使用事务查询数据,避免占用过量的系统资源。

4) 在事务处理过程中尽量不要出现等待用户输入的操作

在处理事务的过程中,如果需要等待用户输入数据,那么事务会长时间地占用资源,有可能造成系统阻塞。

本文分享自华为云社区《MySQL执行事务的语法和流程》,原文作者:运气男孩。


点击关注,第一时间了解华为云新鲜技术~

发布于 2021-01-20 14:49

先来张官方图的中文版


Mysql 体系简单来说四层:

连接服务层Connectors

Mysql Server

存储引擎

文件和日志层


至于执行SQL的时候流程:


  • 查缓存
  • 解析
  • 预处理
  • 查询优化器
  • 查询执行计划
  • 查询存储引擎

SQL解析顺序来张图:


(图侵删)

编辑于 2020-03-06 10:42
每日一道面试题-MYSQL复制原理及其流程?
484 播放
发布于 2022-04-21 08:21· 293 次播放

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。



mysql主要分为Server层和存储引擎层
Server层:主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog日志模块。
存储引擎: 主要负责数据的存储和读取,采用可以替换的插件式架构,支持InnoDB、MyISAM、Memory等多个存储引擎,其中InnoDB引擎有自有的日志模块redolog 模块。
InnoDB 5.5.5版本作为默认引擎。
连接器
主要负责用户登录数据库,进行用户的身份认证,包括校验账户密码,权限等操作,如果用户账户密码已通过,连接器会到权限表中查询该用户的所有权限,之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据,也就是说,后续只要这个连接不断开,即时管理员修改了该用户的权限,该用户也是不受影响的。
查询缓存
连接建立后,执行查询语句的时候,会先查询缓存,Mysql会先校验这个sql是否执行过,以Key-Value的形式缓存在内存中,Key是查询预计,Value是结果集。如果缓存key被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,完成后也会把结果缓存起来,方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限,是否有该表的查询条件。
Mysql 查询不建议使用缓存,因为对于经常更新的数据来说,缓存的有效时间太短了,往往带来的效果并不好,对于不经常更新的数据来说,使用缓存还是可以的,Mysql 8.0 版本后删除了缓存的功能,官方也是认为该功能在实际的应用场景比较少,所以干脆直接删掉了。
分析器
mysql 没有命中缓存,那么就会进入分析器,分析器主要是用来分析SQL语句是来干嘛的,分析器也会分为几步:
第一步,词法分析,一条SQL语句有多个字符串组成,首先要提取关键字,比如select,提出查询的表,提出字段名,提出查询条件等等。做完这些操作后,就会进入第二步。
第二步,语法分析,主要就是判断你输入的sql是否正确,是否符合mysql的语法。
完成这2步之后,mysql就准备开始执行了,但是如何执行,怎么执行是最好的结果呢?这个时候就需要优化器上场了。
优化器
优化器的作用就是它认为的最优的执行方案去执行(虽然有时候也不是最优),比如多个索引的时候该如何选择索引,多表查询的时候如何选择关联顺序等。
执行器
当选择了执行方案后,mysql就准备开始执行了,首先执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。
二、语句分析
2.1 查询语句
说了以上这么多,那么究竟一条sql语句是如何执行的呢?其实我们的sql可以分为2中,一种是查询,一种是更新(增加,更新,删除)。我们先分析下查询语句,语句如下:
select * from tb_student A where A.age='18' and A.name='张三';
结合上面的说明,我们分析下这个语句的执行流程:

  • 先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限,在mysql8.0版本以前,会先查询缓存,以这条sql语句为key在内存中查询是否有结果,如果有直接缓存,如果没有,执行下一步。
  • 通过分析器进行词法分析,提取sql语句的关键元素,比如提取上面这个语句是查询select,提取需要查询的表名为tb_student,需要查询所有的列,查询条件是这个表的id='1'。然后判断这个sql语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步。
  • 接下来就是优化器进行确定执行方案,上面的sql语句,可以有两种执行方案:



a.先查询学生表中姓名为“张三”的学生,然后判断是否年龄是18。 b.先找出学生中年龄18岁的学生,然后再查询姓名为“张三”的学生。

  • 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为,有时候不一定最好)。那么确认了执行计划后就准备开始执行了。
  • 进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口,返回引擎的执行结果。

2.2 更新语句
以上就是一条查询sql的执行流程,那么接下来我们看看一条更新语句如何执行的呢?sql语句如下:

update tb_student A set A.age='19' where A.name='张三';
我们来给张三修改下年龄,在实际数据库肯定不会设置年龄这个字段的,不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走,只不过执行更新的时候肯定要记录日志啦,这就会引入日志模块了,mysql 自带的日志模块式binlog(归档日志),所有的存储引擎都可以使用,我们常用的InnoDB引擎还自带了一个日志模块redo log,我们就以InnoDB模式下来探讨这个语句的执行流程。流程如下:

  • 先查询到张三这一条数据,如果有缓存,也是会用到缓存。
  • 然后拿到查询的语句,把 age 改为19,然后调用引擎API接口,写入这一行数据,InnoDB引擎把数据保存在内存中,同时记录redo log,此时redo log进入prepare状态,然后告诉执行器,执行完成了,随时可以提交。
  • 执行器收到通知后记录binlog,然后调用引擎接口,提交redo log 为提交状态。
  • 更新完成。

这里肯定有同学会问,为什么要用两个日志模块,用一个日志模块不行吗?这就是之前mysql的模式了,MyISAM引擎是没有redo log的,那么我们知道它是不支持事务的,所以并不是说只用一个日志模块不可以,只是InnoDB引擎就是通过redo log来支持事务的。那么,又会有同学问,我用两个日志模块,但是不要这么复杂行不行,为什么redo log 要引入prepare预提交状态?这里我们用反证法来说明下为什么要这么做?

  • 先写redo log 直接提交,然后写 binlog,假设写完redo log 后,机器挂了,binlog日志没有被写入,那么机器重启后,这台机器会通过redo log恢复数据,但是这个时候bingog并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
  • 先写binlog,然后写redo log,假设写完了binlog,机器异常重启了,由于没有redo log,本机是无法恢复这一条记录的,但是binlog又有记录,那么和上面同样的道理,就会产生数据不一致的情况。

如果采用redo log 两阶段提交的方式就不一样了,写完binglog后,然后再提交redo log就会防止出现上述的问题,从而保证了数据的一致性。那么问题来了,有没有一个极端的情况呢?假设redo log 处于预提交状态,binglog也已经写完了,这个时候发生了异常重启会怎么样呢? 这个就要依赖于mysql的处理机制了,mysql的处理过程如下:

  • 判断redo log 是否完整,如果判断是完整的,就立即提交。
  • 如果redo log 只是预提交但不是commit状态,这个时候就会去判断binlog是否完整,如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。
三、总结

  • Mysql 主要分为Server曾和引擎层,Server层主要包括连接器、查询缓存、分析器、优化器、执行器,同时还有一个日志模块(binlog),这个日志模块所有执行引擎都可以共用。
  • 引擎层是插件式的,目前主要包括,MyISAM,InnoDB,Memory等。
  • sql等执行过程分为两类,一类对于查询等过程如下:权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎
  • 对于更新等语句执行流程如下:分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit
原文作者: X-Wolf
原文链接: cnblogs.com/xingxia/p/m
文章来源:博客园
侵删
发布于 2021-01-27 10:38

mysql书写顺序和执行顺序不太一样,可以参考下图

发布于 2020-04-15 21:21

公众号猴子的技术专栏里有一篇文章有说到

发布于 2021-04-17 18:31