MySQL-SQL语句执行流程

 

个人公众号『码农札记』，欢迎关注，查看更多精彩文章.

 

全面地了解SQL语句执行的每个过程，才能更好的进行SQL的设计和优化。

当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，很多查询优化工作实际上就是遵循一些原则能够按照预想的合理的方式运行。

MySQL 基础架构分析

MySQL 基本架构概览

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

• 查询缓存:  执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

• 分析器:  没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 优化器：  按照 MySQL 认为最优的方案去执行。

• 执行器: 执行语句，然后从存储引擎返回数据。

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

Server 层

主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有 binglog 日志模块。

存储引擎

主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的 redolog 模块。

Server层基本组件

连接器

SQL客户端与与服务器建立连接，该请求被发送到连接管理器，连接成功后会验证权限等，这过程其实就是一个TCP连接的过程。注意，MySQL服务器与客户端之间的通信是“半双工”的，意味着任意时刻，要么是服务器向客户端发送数据，要什么是客户端向服务器发送数据（请求），不能同时进行。查看连接状态：SHOW FULL PROCESSLIST命令来查看MySQL正在做什么，如：

• sleep：线程正在等待客户端发起新的请求

• query：正在执行查询或者将数据返回给客户端

• locked：该线程正在等待表锁（实现锁是在存储引擎中）

• analyzing and statistics：线程正在收集存储引擎的统计信息，并生产相应的查询计划

• copying to tmp table：线程正在查询，并将结果放到一个临时表中，这时候一般是在做group bu操作

• sorting result：对结果集进行排序

• sending data：线程可能在多个状态之间传递数据，或者正在生成结果集，或是在返回数据，有多种情况

查询缓存

查连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前 执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询 的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查 询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可 以直接返回结果，这个效率会很高。

但是大多数情况下建议不要使用查询缓存,因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清 空。因此很可能费时费力地把结果存起来，还没有使用，就被一个更新全清空了。对于更新 压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很 长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置 成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓 存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from T where ID=10;

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删除，也就是说 8.0 开始彻 底没有这个功能了。

分析器

首先通过mysql关键字将语句解析，会生成一个内部解析树，mysql解析器将对其解析，查看是否是有错误的关键字，关键字顺序是佛正确；预处理器则是根据mysql的规则进行进一步的检查，检查mysql语句是否合法，如，库表是否存在，字段是否存在，字段之间是否模棱两可等等，预处理器也会验证权限。

优化器

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

执行器

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和其他的关系型数据库那样生成对应的字节码。

语句分析

查询语句

select * from student where age='22' and name='test';

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

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

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

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

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

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

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

更新语句

update student set A.age='24' where A.name='test';

指定test修改下年龄，执行流程和查询类似，不过执行更新的时候会记录日志，这就涉及日志模块， MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，

InnoDB 引擎还自带了一个日志模块 redo log（重做日志）.

流程如下：

• 先查询到test这一条数据，如果有缓存，也是会用到缓存。

• 然后拿到查询的语句，把 age 改为 24，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。

• 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为commit状态。

• 更新完成。

redo log

当我们想要修改DB上某一行数据的时候，InnoDB是把数据从磁盘读取到内存的缓冲池上进行修改。这个时候数据在内存中被修改，与磁盘中相比就存在了差异，这种有差异的数据为脏页。InnoDB对脏页的处理不是每次生成脏页就将脏页刷新回磁盘，这样会产生海量的IO操作，严重影响InnoDB的处理性能。对于此，InnoDB有一套完善的处理策略，与我们这次主题关系不大，表过不提。既然脏页与磁盘中的数据存在差异，那么如果在这期间DB出现故障就会造成数据的丢失。为了解决这个问题，redo log就应运而生了。

已经存在binlog，为什么还需要redo log？

即使数据库发生异常重启，之前提交的记录都不会丢失的能力叫做crash-safe， 这里提交的概念是指，日志能够成功写入redo log,否则告之客户端提交失败

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

关于redolog还要了解以下概念

checkpoint

checkpoint本身是比较复杂的，所做的事就是把脏页给刷新回磁盘。所以，当DB重启恢复时，只需要恢复checkpoint之后的数据。这样就能大大缩短恢复时间。当然checkpoint还有其他的作用。

LSN(Log Sequence Number)

LSN实际上就是InnoDB使用的一个版本标记的计数，它是一个单调递增的值。数据页和redo log都有各自的LSN。我们可以根据数据页中的LSN值和redo log中LSN的值判断需要恢复的redo log的位置和大小

宕机恢复

DB宕机后重启，InnoDB会首先去查看数据页中的LSN的数值。这个值代表数据页被刷新回磁盘的LSN的大小。然后再去查看redo log的LSN的大小。如果数据页中的LSN值大说明数据页领先于redo log刷新回磁盘，不需要进行恢复。反之需要从redo log中恢复数据。

二阶段提交

### 假设原来id 为10 的记录age 为5
begin;
update student set age = 10 where id = 10;
commit;

一般情况下，事务提交涉及redo log 和 binlog。当commit 命令执行时，

• 先进入commit prepare 阶段，这个阶段事务中新生成的redo log 会被刷到磁盘，并将回滚段置为prepared状态。

• commit阶段：innodb释放锁，释放回滚段，设置redo log提交状态，binlog持久化到磁盘，然后存储引擎层提交。

为什么需要二阶段提交？

由于存在redo log 和 binlog ，而他们两是相互独立的。而事务提交必须确保两者同时有效。不然会出现不一致的情形。假如：redo log 有效，binlog 无对应记录在上述例子中如果服务器从事务中回复，由于redo log 有效所以id为10的记录age仍然会是10，但是由于binlog日志没有记录，所以如果通过binlog 做主从，或者主备那么就会导致主从，主备不一致。假如：redo log 失效，而binlog 有对应记录，上述例子中，服务器中对应的id为10的日志age就会是修改前的5，而binlog中的日志会被传到其他从服务器，也会导致主从，主备不一致。

流程

redolog 和binlog怎么联系起来：

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务.