全面了解MySQL的执行原理

葉来叶好

全面了解MySQL的执行原理

我们每天都在访问各种网站、App，这些东西上面都存在这大量的数据，那么这些数据都是存储在哪里呢？数据库。
当我们对网站或者App做出操作去访问数据时，其实归根到底是一条SQL语句的执行，那么一条SQL语句到底是怎么执行的呢？
一、体系结构

其中涉及到很多方面的知识，那么，下面就让我带着大家一起去了解SQL语句执行背后的原理~

❝ 在开始将之前我们先来看几张图
❞

上面这张图是MySQL官方提供的MySQL架构图，我们可以清楚的看到一个连接是要经过很多个步骤最后才获得结果的。
对于英文水平不是很好的人来说（比如我），看到上面这张图应该会是一脸懵逼的（其实认真看还是看得懂一些的），所以我在博客园找到了一张更加清晰的图，我们看下面：


看到这张图，是不是更加清晰一点呢？很多人看到这张图其实还是无从下手，就跟我一开始学习一下，不知道哪里分析起，要怎么个分析法，所以下面我画了一个更加简显易懂的体系结构图。


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

• Server层：主要包括连接器、查询缓存（MySQL8.0移除）、分析器、优化器、执行器等，所有的跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用的日志模块binglog
  
• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnnoDB有属于自己的日志模块（下文会介绍到）。「现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始被当做默认的存储引擎了。」
  

二、组件的详细介绍

「1.连接器」（验证身份+权限）
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。
主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的。
「2.查询缓存」（MySQL8.0后移除）
查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 语句是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询语句，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

❝ 为什么MySQL8.0后要移除呢？
❞

因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。
MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。
「3.分析器」（词法分析+语法分析）
MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
「4.优化器」（选取MySQL认为最优的方案执行）
优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，在以后的文章《慢SQL分析》会说到），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
「5.执行器」（开始执行）
当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。
说了这么多都是介绍，下面来张图了解整个过程


三、语句分析

上面说了那么多，都是介绍大致的一个步骤，那么究竟一条sql语句是怎么执行的呢？其实sql语句大致上可以分为两种，一种是查询语句，一种是更新语句（增加、更新、删除）。
「1. 查询语句」
select * from tb_student  A where A.age='18' and A.name=' 张三 ';
结合刚刚上面的说明，我们来分析一下这条语句的具体执行流程：

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

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

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

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
  

由于更新语句设计到日志操作，所以先介绍一下日志。
「2. 日志」
binlog（归档日志）是MySQL的Server层有的。 逻辑日志
主要记录用户对数据库操作的SQL语句

❝ binlog 属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠 binlog 是没有 crash-safe 能力的。
binlog 有两种模式，statement 格式的话是记 sql 语句，row 格式会记录行的内容，记两条，更新前和更新后都有。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
❞

正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原。
undo log（回滚日志）

❝ undo log顾名思义，主要就是提供了回滚的作用，但其还有另一个主要作用，就是多个行版本控制(MVCC)，保证事务的原子性。在数据修改的流程中，会记录一条与当前操作相反的逻辑日志到undo log中（可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录），如果因为某些原因导致事务异常失败了，可以借助该undo log进行回滚，保证事务的完整性，所以undo log也必不可少。
❞

redo log （重做日志）是 InnoDB 引擎特有的日志。物理日志
记录的是数据库中每个页的修改，可以用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置，因为修改会覆盖之前的）。

❝ 在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率。
而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（redolog buffer）里面，并更新内存（buffer pool），这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候（如系统空闲时），将这个操作记录更新到磁盘里面（刷脏页）。
❞

redo log 是 InnoDB 存储引擎层的日志，又称重做日志文件，redo log 是顺序循环写的，相比于更新数据文件的随机写，日志的写入开销更小，能显著提升语句的执行性能，提高并发量。

❝ 在redo log满了到擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿。（所以针对并发量大的系统，适当设置redo log的文件大小非常重要）
❞

redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么日志总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。
redo log 和 binlog 区别：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  
• redo log 是物理日志，记录的是在某个数据页上做了什么修改，记录了数据；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
  
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  

tips：crash-safe 指MySQL服务器宕机重启后，能够保证：

• 所有已经提交的事务的数据仍然存在。
  
• 所有没有提交的事务的数据自动回滚。
  

「3. 更新语句」
说完日志，我们再来看一下更新语句，我们看下面这条语句：
UPDATE` ``test` ``SET` ``c` = `c` + 1 ``WHERE` ``id` = 1;
语句的意思是找到id为1的那条数据，将c这列的值+1。
这条语句的操作顺序是怎么样的呢？容我一一道来。
「操作顺序」

• 查找记录：执行器先找引擎取id=1这一行。ID是主键，引擎直接用树搜索找到这一行。如果id=1这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回；
  
• 「执行器」拿到引擎返回的行数据，把c+1，得到新的一行数据，再调用引擎接口写入这行新数据；
  
• 引擎将这行新数据更新到「内存」中，同时「将这个更新之后的数据记录到redo log里面」，此时redo log处于「prepare」状态；
  
• 引擎告知执行器，我执行完成了，你随时可以调我的接口提交事务了；
  
• 执行器生成这个操作的binlog，并把「binlog」写入磁盘。
  
• 执行器调用引擎的提交事务接口「，引擎把刚刚写入的redo log改成」提交commit状态，更新完成。
  

下面看个图缕清一下思路：

❝ 那么问题来了，「为什么redo log要分两步写，中间再穿插写binlog呢？」
❞

在上图中我们可以看到redolog分为两个阶段，那为什么要这样呢？如果不这样会发生什么问题？下面我们用反证法进行论述：

• 「先写redolog并提交，然后再写binlog」，假设redolog写完后，机器宕机了，这个时候binlog没有写入，机器重新启动之后，由于redo log已经写完了，系统重启后会通过redo log将数据恢复回来，但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的数据与原库的值不同。造成了主从不一致
  
• 「先写 binlog，然后写 redo log」，如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以没有记录到redolog。但是binlog里面已经记录了日志。所以，在之后用binlog来恢复的时候，恢复出来的临时库中的数据就与原库的值不同。
  

END

至此对MySQL 的执行原理就基本讲完了，用一张脑图简单回顾一下：


参考资料

MySQL 的 crash-safe 原理解析
极客时间-MySQL实战45讲

海黄神奇木

可以，还比较详细