MySQL进阶【1】 —— SQL的执行流程

前言

在阅读完MySQL初阶系列的博客之后，读者们应该明白如何去编写SQL代码了吧？或许你还写得不是很熟练，但我相信大家起码能看明白了。在知道如何编写SQL语句之后，部分读者反馈：“很好奇SQL是怎么执行下去的，MySQL是怎么做到的？”。

于是，MySQL进阶系列的博客应运而生！我会用好几篇进阶系列的博客，从执行流程到数据存储，再到索引机制与事务机制，最后谈谈MySQL的三大日志。接下来的内容，我将向大家介绍执行流程这一部分的内容，希望大家读得愉快，学得愉快！

一、SQL执行流程图

我先放上一张执行流程图，或许各位读者现在根本看不懂这张图，但没关系，待会我将逐一讲解，等回过头再来看这张图时就会觉得神清气爽了！

二、各层功能分析

我们简单分析一下上面这张图，它其实是由三部分构成的：客户端、Server层、存储引擎层。其实这个客户端就是咱们自己，我们编写完SQL语句后一旦点击执行，就会向MySQL的服务器发送信息，MySQL服务器接收到我们的SQL语句经过一顿开始分析就会将结果，也就是我们想要的数据，返回给我们。

整个MySQL服务器主要由两部分组成：Server层 与 存储引擎层。那么这两层的作用分别是什么呢？

Server层：负责建立连接、分析SQL、执行SQL

存储引擎层：负责对数据进行存储与提取

1. Service层

（1）连接器

顾名思义，连接器的作用就是：使得客户端能与服务器连接。那么我们如何在Linux操作系统上连接到MySQL服务器呢？首先是要安装MySQL吧…（这里博主假定大家已经安装好并设置好密码了，没有安装的读者不妨先安装，不过没安装也能向下继续读），我们只需要输入下方的代码，再按要求输入密码，连接其实就建立成功了！这里博主额外对有计网基础的读者说一下：MySQL是基于 TCP 协议进行传输的。

# 建立连接
mysql -h ip -u user -p

• -h用于指定MySQL服务的IP地址。如果MySQL服务器就在本机，可以不用这个参数
• -u用于指定用户名，一般来说管理员角色名为root
• -p用于指定密码

在完成连接的建立后，咱们还能一起看看MySQL的一些东西。

比如，我们可以用show processlist;查看：本机的MySQL当前有多少人在连接。

然而，为什么会有这个问题呢？本机上的肯定是一个人连呀！其实不然，在公司项目开发过程中，随着项目增加，经常会把每个项目的平台部署成开发、测试环境，此时MySQL数据库就有可能是多个平台共用一个了，尤其是现在基本上都是用微服务架构，那么数据库连接就更加不够用了。如果本地修改了代码启动的时候经常会连不上数据库，就会提示：too many connections。

那么，我们能使用的最大连接数是多少呢？我们可以通过show variable like 'max_connnections';指令来查看。

如果遇到了因为数据库连接限制而无法连接上的情况，我们可以通过set global max_connections=150;去修改当前连接数，不过这种通过SQL修改的办法是有风险的，在重启MySQL之后就会恢复成原来的值。那么，一劳永逸的办法是什么呢？需要去修改配置文件中max_connections的值。

当然，如果我们在查看连接的时候，发现某些连接的State为Sleep的话，我们可以通过kill connect id_numbers指令去断开连接，不过就算我们不手动断开，这些Sleep的空闲连接也会在show variables like 'wait_timeout';指定的时间后断开连接。

短连接 与 长连接

其实对于MySQL来说也有短连接与长连接的概念。一般来说，我们会推荐使用长连接。为啥呢？这就需要咱们知道 短连接和长连接 是什么样的了：

我们能明显地看到，短连接只能执行一条SQL语句，而长连接则可以在一次连接中执行多条SQL语句。长连接有一个很明显的优点：减少了建立连接与断开连接的过程。不过，长连接也有它的缺点：长连接可能会导致内存增多。因为MySQL在执行查询过程中使用内存管理“连接对象”，这些连接对象只有在连接断开时才会释放。那么要咋解决呢？对于服务端来说：定期断开长连接即可；对于客户端来说：需要主动重置连接。

---

（2）查询缓存

当客户端向MySQL服务发送SQL语句后，MySQL就会马上解析出SQL语句的第一个字段，看看是什么类型的语句。如果SQL是select 语句，MySQL就会先去“查询缓存”Query Cache里查找缓存的数据，看看之前有没有执行过这一条命令。如果查询的语句命中查询缓存，那么就会直接返回value给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

返回value是啥意思？其实，查询缓存是以key-value形式保存在内存中的，key为SQL查询语句，value为SQL语句查询的结果。不过，MySQL 8.0版本已经将查询缓存删掉了，也就是说MySQL 8.0开始，执行一条SQL查询语句，不会再走到查询缓存这个阶段了。而对于MySQL 8.0之前的版本，如果想关闭查询缓存，我们可以通过将参数query_cache_type设置成DEMAND。为什么要删除呢？感觉查询缓存还挺有用。

我们之所以觉得查询缓存会有用，是因为我们误以为查询缓存的命中率很高。其实，只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

---

（3）解析器

词法解析器

词法解析器会根据：输入的字符串识别出关键字，我们以select username from userinfo这个语句为例，这句SQL在经过词法解析器分析之后，就会得到4个Token，其中有2个Keyword，分别为select和from：

语法解析器

语法解析器会根据：词法分析的结果，在结合语法规则，判断输入的SQL语句是否满足MySQL语法，如果没问题就会构建出SQL语法树，这样方便后面模块获取SQL类型、表名、字段名、where条件等等。

如果我们输入的SQL语句语法不对，在解析器这个阶段就报错了。比如，我下面这条查询语句，把from写成了 form，这时MySQL解析器就会给报错。

但是注意，如果表不存在或者字段不存在，解析器并不会报错，解析器只负责检查语法和构建语法树，但是不会去查表或者字段存不存在。那到底谁来做检测表和字段是否存在的工作呢？别急，接下来就是了。

---

（4）三阶段执行

prepare 阶段：预处理器

主要有两个工作：

• 检查SQL查询语句中的表或者字段是否存在
• 将select * 中的*符号，扩展为表上的所有列

optimize 阶段：优化器

优化器会基于查询成本， 选择查询成本最小的执行计划。

这就很奇怪了，为什么会有最小的查询成本呢？难道不都是直接执行的么？的确不是直接执行的，因为我们能通过添加索引的操作提高我们的查询速度。（索引可以理解为一本书的目录，我们要找某个内容，一般来说肯定是根据目录找比较快的嘛，而索引就相当于数据的目录）。

有了索引这个概念，自然而然就能有最小的比较了。数据表的索引可不是真正完全和书本的目录一样，写好目录之后就固定的，因为我们可以对同一张表设置多个索引，这就相当于数据有好几种不同的目录。当表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

execute 阶段：执行器

根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

我们可以从三个角度去理解执行器的作用：主键索引查询、全表扫描、索引下推，这里暂时先不做详细的介绍，想专研的读者可以读这篇文链接: 关于执行器的详情

2. 存储引擎层

存储引擎是数据库的核心组成部分，负责数据的存储和检索。不同的存储引擎有不同的特点和适用场景。在选择存储引擎时，需要根据应用的需求和特点来决定，例如:数据量的大小、安全性要求、并发访问等。

下面只是简单介绍一下了InnoDB、MyISAM这两种存储引擎，想了解其他存储引擎的读者可以自行搜索。

• InnoDB：是MySQL的默认存储引擎，支持事务、行级锁和外键约束，具有高可靠性和高性能。底层存储结构是B+Tree，每个节点对应一个page（页），一般一个页大小为16KB。非叶子节点仅有键值，叶子节点包含完整数据（数据存储在叶子节点，且叶子节点以双向链表的形式保存）。每张表都有一个表空间文件xxx.ibd，用来存储表的结构（frm、sdi）、数据、索引。
• MyISAM：是MySQL8.0之前的默认存储引擎，查询效率高，但支持事务和行级锁的能力较弱。

上方提到的B+Tree 与 事务 以及 行级锁等词，不理解的话没有关系，博主之后的会为大家一一介绍，想了解的读者可以关注博主。

三、总结

此时，我们再一起看看文章开头的那张图吧！不知各位读者是否有神清气爽的感觉？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL8.0已删除该模块；
• 解析SQL，通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行SQL：执行SQL共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将select * 中的* 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端；

结语

如果觉得博主写得不错的话，请给我点个赞支持一下吧~ 如果有任何不理解的内容可以在评论区留言，如果文章有任何错误的地方也欢迎大家给我指正！感谢读到此处的所有读者，加油！