一条sql语句在mysql中如何执行（查询+更新）

本篇文章会分析一下一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。

在分析之前我会先介绍下 MySQL 的基础架构，知道了 MySQL 由哪些组件组成以及这些组件的作用是什么，可以帮助我们理解和解决这些问题。

一 MySQL 基础架构

1.1 MySQL 基本架构

从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

先简单介绍一些组件的基本作用帮助大家理解这幅图

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器: 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
• 存储引擎： 主要负责数据的存储和提取，其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自己的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

1.2 Server 层基本组件介绍

1) 连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，那么连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。也就是一个用户已经成功建立连接之后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

2) 查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

• 如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端。
• 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

但MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，只要有对一个表的更新，那么这个表上所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。MySQL 8.0 版本后删除了缓存的功能。

3) 分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么。那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串和空格组成，MySQL 需要识别出里面的字符串分别是什么，代表什么。比如 select，提出查询的表，提出字段名，提出查询条件等等。

例如输入：

select ID from T 

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符
串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID

做完这些操作后，就会进入第二步。

第二步，语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

4) 优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。然后进入执行器阶段。

5) 执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

二 语句分析

2.1 查询语句

通过上面的介绍大家应该对执行一条sql的基本流程已经清楚了，接下来详细介绍一条sql是如何执行的。我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from sys_student  A where A.age=18 and A.name='Bruce';

结合上面的说明，我们分析下这个语句的执行流程：

• 在连接器登录认证通过后，先检查该用户是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是 select 查询操作，提取需要查询的表名为 sys_student ,需要查询所有的列，查询条件是这个表的 age=‘18’ and name=‘Bruce’。然后进行语法分析，判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

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

  那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行前先进行权限校验，如果没有权限就会返回错误信息，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口，返回引擎的执行结果。

2.2 更新语句

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

update sys_student  A set A.age=19 where A.name=' Bruce ';

我们来给Bruce修改下年龄，这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

• 先查询到name=Bruce这一条或多条数据，。如果name=Bruce 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 然后拿查询得到的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
• 执行器收到通知后，记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
• 更新完成。

为什么要用两个日志模块，用一个日志模块不行吗?

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是
MyISAM 没有 crash-safe 的能力(crash-safe 的能力：即使数据库发生异常重启，之前提交的记录都不会丢失)。，binlog 日志只能用于归档（用于记录数据库执行的写入性操作信息，记载的是update/delete/insert这样的SQL语句，数据的逻辑变化）。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力（redo log记载的是物理修改的内容：xxxx页修改了xxx。redo log的作用是为持久化而生的。写完内存，如果数据库挂了，那我们可以通过redo log来恢复内存还没来得及刷到磁盘的数据，将redo log加载到内存里边，那内存就能恢复到挂掉之前的数据了。）

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。

为什么必须有“两阶段提交”呢？

这是为了让两份日志之间的逻辑一致。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，binlog还没写完，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所有恢复后，这一行age的值是19。
  但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。同时主从同步也会丢失这一条数据。
• 先写 binlog，然后写 redo log，假设写完了 binlog，MySQL 进程异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，当写入redo log 处于prepare状态，binlog 也已经写完了，这个时候发生了异常重启MySQL 的处理过程如下：

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

这样就解决了数据一致性的问题。

三 总结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存（MySQL 8.0 版本后移除）、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
• 查询语句的执行流程如下：连接数据库----》权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
• 更新语句执行流程如下：连接数据库----》权限校验—》分析器----》优化器----》权限校验----》执行器—》引擎—》写入redo log(prepare 状态）—》写binlog—》提交事务，redo log处于commit状态