MySQL 中 SQL 语句的执行过程

一个 MySQL 请求的处理流程图：

从上图可以看出，MySQL 的处理流程主要分为 4 个步骤：客户端与服务端通信、查询优化处理过程、查询执行引擎、返回结果给客户端。

1、客户端与服务端通信

一般通信方式有 3 种：单工，半双工，全双工。单工就是只能单向传输，要么 A 端给 B 端传输，要么 B 端给 A 端传输；半双工是可以双向传输的，但是同一时间只能是一个方向传输，也就是说 A 端给 B 端传输的时候，B 端只能等待，反过来也一样，B 端给 A 端传输的时候，A 端也只能等待；全双工是双向随便传输。

MySQL 客户端与服务器的通信方式是半双工的，也就是说，我们的一个数据库连接在向数据库发送数据的时候，此时这个数据库连接是不能给客户端返回数据的，一定是数据返回完毕以后，客户端才能再次发起查询操作。这也就是我们在做数据查询的时候用 where 条件 和 limit 限制数据结果行数的原因，否则客户端连接需要等到数据库把所有的查询结果返回之后，才能进行下一个操作。

从上面的分析可以看出，MySQL 数据库半双工通信模式的一个重要特点是：客户端一旦开始发送指令，服务端需要接收完毕才能响应，客户端只有在完全接收到服务端响应的数据后，才能再次发送指令。有点像对讲机，这就是为什么电视里看到两个人对讲时，最后要说一句 over 的原因，当听到 over 的时候，另一端的人就可以按对讲键进行说话了。我们在程序开发中，一般会用多个连接进行数据交互，通过数据库连接池来进行管理，因此对这块体会可能不够深刻。

其实 MySQL 的每一个连接都有其对应的状态来标识它目前所处的阶段，和线程类似，我们可以通过下面的命令查看数据库连接的状态：

SHOW [FULL] PROCESSLIST

常用的几个状态描述：

当发现数据库连接长时间占用的时候，可以用 kill 命令杀死线程：

kill processlist_id

2、查询优化处理过程

解析器解析 sql 语句：通过 lex 词法分析器（就是把一个完整的 SQL 语句分析成独立的单词 ）、yacc 语法分析器（就是分析是否符合语法规则，比如单引号是否闭合等）进行分析，将 sql 语句按 sql 标准解析成 解析树（select_lex）对象，主要功能是把一个 sql 语句的字符串解析成数据库服务器可以处理的解析树对象，便于后续进行预处理和生成执行计划。

预处理：预处理会根据 mysql 的语法规则对解析树对象进行合法性检查，比如检查表名列名是否存在、检查名字和别名，保证没有歧义，预处理之后得到一个新的解析树。

优化器生成执行计划：优化器的主要作用就是把这个 sql 语句找到最优的执行计划，MySQL 的查询优化器和 Oracle 类似，都是基于成本的计算，优化器会尝试使用不同的执行计划，以便于找到一个最优的执行计划（一般随机读取 4K 的数据库进行分析）。

可以使用以下的命令查看查询的成本：

show status like 'Last_query_cost';

优化器最终会把解析树变成一个查询执行计划。MySQL 提供了一个执行计划的工具，我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

我们在做 sql 调优的时候主要也就是对这部分进行处理，在《掌握 SQL 语句调优中 explain 的用法》中会详细介绍。

3、查询执行引擎

查询执行模块，也就是查询执行引擎，根据优化器生成的最优执行计划调用对应存储引擎的 API 的进行执行计划的执行，并获取查询应该返回的结果集。

4、返回结果给客户端

如果没有开启缓存，把查询到的结果集返回到客户端；如果开启了缓存，执行缓存操作，把结果集存入缓存，然后把结果返回给客户端，即使结果集是空的，也要返回。

MySQL 的缓存介绍

一般情况下，我们不会用到数据库自带的缓存，所以 MySQL 默认是不开启缓存的。只有以读为主的业务，数据不变化的情况下，可以开启数据库的缓存。

查看缓存是否开启：

show variables like 'query_cache%';

queyrcachetype:on，表示缓存开启，默认是关闭的，可以通过修改 MySQL 配置文件 my.cnf 进行调整，重启服务后生效。

querycachelimit:1048576，表示单词查询缓存的结果集大小 1M，超过 1M 则不会缓存。

querycachesize，表示缓存开辟的空间大小

查看缓存操作情况：

show status like 'Qcache%';

Qcache_hits:表示缓存命中次数

Qcache_inserts:表示缓存写入次数

缓存生效的条件是在缓存开启的情况下，执行的 sql 语句字符串一模一样的时候，可以从缓存直接读取数据，但是当缓存数据相关的表存在数据变化的时候，原有的缓存就会失效，需要重新写人缓存。

MySQL 的缓存开启后，当 sql 查询语句带有 sqlnocache 关键字或者带有函数操作或者单次查询结果集超过 querycachelimit 的设置的值或者查询系统表时，不会用到缓存。

我们在开发中，最好不要开启缓存，将 querycachetype 设置为 off，querycachesize 设置为 0；缓存一般会用 Redis 方案来替代。

一条SQL是如何在MySQL中执行的，你get 到了吗？