MySQL一条SQL语句执行过程，讲得通俗易懂！

最近在看MySQL相关的教程，准备好好深入了解一下MysQL这个数据库，研究一下如何删库跑路…..咳咳咳，删库这种事研究一下就行了，不要应用于公司生产环境，不然还是跑不了的…..

首先，MySQL是一个开源的关系型数据库，免费开源让这款数据库深受广大开发者的热捧，而在开发者面试时，凡是涉及到数据库使用，几乎都是问MySQL相关的。

当谈到一条SQL语句在底层是如何执行时，相信很多人是知道答案的，但是如果再往细节里问，很多人也只能支支吾吾地了，因为很多东西看过后虽然知道个大概，但是因为没有真正转化成自己的知识，所以过段时间就会忘记掉，这是人的记忆特性导致的。

所以本人将此文写下来一是希望自己可以吸收新学到的知识，二是希望给不知道的朋友拓宽知识，三是给知道的朋友重新熟悉一遍，温故而知新。

首先有一张表（stu_score）数据结构是这样的:

 当我执行下列语句后:UPDATE  stu_score  SET  total_score=total_score+2  WHERE id=2

Id =2的那条数据的total_score变成了102了，MySQL是如何执行这条语句的呢？

在说明一条SQL更新语句之前，先了解一下MySQL的架构：

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎，现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

也就是说，你执行建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。

不同存储引擎的表数据存取方式不同，支持的功能也不同，据说阿里巴巴主要用的数据库也是MySQL，但是将其中优化器，引擎等组件都改写了，所以阿里用的MySQL版本性能是大大优于市面上的开源版本的，所以有能力的也可以将其中的组件去改写替换成自己的。

从上图中不难看出，不同的存储引擎共用一个Server 层，也就是从连接器到执行器的部分。你可以先对每个组件的名字有个印象，接下来我会结合开头提到的那条 SQL 语句，带你走一遍整个执行流程，依次看下每个组件的作用。

01连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

02查询缓存

连接建立完成后，如果是 select 查询语句了。执行逻辑就会来到第二步：查询缓存。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。值得注意的是，MYSQL在8.0版本后移除了缓存这一功能，也就是8.0版本后不再出现这一功能，当然未来如果能把上述问题解决，更高的版本会重新出现也不一定。

03分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

比如MySQL 会从你输入的”select”、”update”这些SQL语句关键字识别出来，这是一个查询语句还是更新语句。它也要把字符串“stu_score”识别成“表名stu_score”，把字符串“ID”识别成“列 ID”，MYSQL的语句中是不区分大小写的。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法规则，只有遵循它的规则，才能获取到在它规则管理内的数据，如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，那是语句错误

04优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段，优化器是如何优化的，这个就需要去深入优化器的源码学习了，这里不展开做过多表述！

05执行器

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

开始执行的时候，要先判断一下你对这个表 有没有执行对应操作的权限，如果没有，就会返回没有权限的错误；如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

如果表没有索引，会从第一行一行一行地读取，根据where后面的条件是否满足，如果有索引则会根据索引的规则去寻找，然后执行生成结果集。

现在大概对MySQL底层架构逻辑有了一定的认识，那一条SQL更新语句是如何执行的呢，上述的各个组件除了缓存外，是每一条SQL必须经历的流程，但如果我们需要更深一步了解，更新语句是如何持久化到硬盘中呢，同时保持数据一致性呢？

redo log（重做日志）和 binlog（归档日志）相互配合共同实现了这个功能，如果接触 MySQL，那这两个词肯定是绕不过的，值得注意的是redo log是InnoDB引擎所独有的，binlog是MYSQL共有的，上面分层架构中提到，引擎层的引擎可以有多种选择。

redolog日志

Redo log日志模块是Innodb独有的，它是有着固定大小的，可以把它比喻成我们上学时教室里的黑板，当黑板写完字后，可以擦除再反复写粉笔字，redo log日志也是一样，如果把redolog日志固定为4G大小，然后分成4个文件，每个文件为1G，它会依次从第一个文件开始写，当写完最后一个后，又会从第一个开始，反复循环。

不知道大家还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜一般有两种做法：

一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；

另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。

这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？

同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

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

要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

binlog日志

前面我们讲过，MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

我想你肯定会问，为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 原先自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两种日志有以下三点不同。

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

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的  total_score 字段加2 ”。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

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

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

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

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

这里为什么需要两阶段提交呢？

1.先写 redo log ，后写 binlog，假设写完 redo log 后，机器发生重启或者宕机，binlog 日志还未来得及写入，机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据的变化，就会丢失这一条数据的更新。

2.先写 binlog，后写 redo log，如果在 binlog 写完之后，机器发生重启或者宕机，由于 redo log 还没写，恢复以后这个事务无效，但是 binlog 又有记录，就会产生数据不一致的情况。

可以看到，如果不使用“日志两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。