MySql 入门到精通-sql查询语句的执行过程,你真的知道吗？

首先，我们先来看看MySQL的基础架构，我们再平时写的最多的也就是 sql 查询语句，那么，对于一条简单的查询语句，你可否有想过它是如何执行的，期间又经历了哪些步骤呢？如下sql 查询：

mysql> select * from T where ID=10；

对于这条查询语句，我们再脑海里面肯定能知道它能返回 T 表内 ID=10 的数据，但是，我们并不知道它在 MySQL 内部是怎么执行的。

因此，今天我们就一起来拆解 MySQL ，看看其中的各个部件，希望通过这个拆解过程，让我们能更深入的认识 MySQL ，并且能去更好的使用它，从而方便我们在日常开发中对于遇到的各种 MySQL 异常也能迎刃而解。

MySQL 基本架构

首先，我们来看一下 MySQL 的基本架构图，通过这个示意图我们就能很清楚的知道 sql 语句在 mysql 的各个模块中是如何执行的。

如上图所示，我们能看到 MySql 主要有两大块组成：

• Server 层
• 存储引擎层

Server 层主要由五大核心模块组成，分别是：查询缓存、分析器、优化器以及执行器。MySql 的大部分核心功能都在这一层完成的，像它的所有内置函数，比如时间函数和日期函数等，也就是说所有的跨存引擎的功能均是在 Server 这层去实现，比如，视图、触发器以及存储过程等。

存储引擎层，顾名思义就是主要用来做数据存储以及提取相关操作的，它是插件式的架构模式，支持InnoDB、MyISAM、Memory等多个存储引擎，自 MySQL 5.5.5 版本之后，就使用了 InnoDB 作为默认存储引擎，如今我们开发中使用最多的也是InnoDB 引擎。

我们有一些开发同学在创建表的时候并没有指定存储引擎，直接使用 create table 语句，最终生成的还是 InnoDB 引擎，这就是MySql 默认给咱们选好了，你也可以使用 engine = Memory 来指定特定的存储引擎，值得提醒的一点是，不同的存储引擎表的数据存储方式以及支持的功能也会不同，后面我们会详细说明。

接下来，我们就对于最开始提到的 sql 查询语句结合 Server 层的各个组件进行剖析下执行过程，并且借此来了解其各个组件的作用。

连接器

首先，我们在使用 MySQL 数据库的时候，是不是必须得连接上它去登录，在这个时候，就是连接器在接待我们，它负责和客户端建立连接、获取权限、维持和管理连接操作，一般我们会使用如下命令进行连接：

mysql -h$ip -P$port -u$user -p

enter 回车之后，就会让我们输入密码了，当然，为了防止密码泄密，我们一般不会将密码直接卸载 -p 上。如上连接命令中， mysql 即为客户端，当完成了 TCP 三次握手之后，连接器就要开始验证我们的身份了，主要是依靠验证我们输入的用户名和密码。

• 用户名或密码不对，我们就会收到一个”Access denied for user”的错误，然后，客户端程序结束执行。
• 用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。然后，这个连接里面的所有权限判断逻辑，都将依赖于此时读到的权限。

也就是说，用户一旦成功的建立了连接，之后即使你用 root 管理员账号对其设置其他权限，也不会影响这个用户当前的权限，权限被重新赋予之后，只有新建立的连接才会使用新设置的权限。

当成功建立连接之后，我们不做任何操作的话，这个连接就会处于空闲状态，这个时候我们可以使用命令 show processlist 看到它，如下图所示，Command 列有 3 个sleep 连接，则表示有三个空闲连接。

如果客户端什么也不操作啥动静没有的话，连接器就会在默认 8 小时之后将其断开，当然这个 8 小时是可以修改的，是由参数 wait_timeout 决定的。

如果连接器将其断开之后，客户端再发起相关请求操作的话，就会收到如下错误信息，要想继续操作你就得重新连接，你才能发起请求。

Lost connection to MySQL server during query

我们在日常开发中经常遇到长连接与短链接的问题，对于数据库层面，长连接即为建立连接之后，如果客户端持续有请求进来，就会一直使用同一个连接；短链接即为客户端每次就执行很少的请求就断开连接，下次再请求就会重新建立连接去发起请求。

我们知道连接的建立过程很复杂且耗时，因此，我们在开发中尽量不要频繁建立连接，尽量使用长连接操作数据库。

这个时候有些同学可能就会有疑问，由于 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，如果全部使用长连接的话，有时就会出现 MySql 内存紧张，如果内存占用太大就会出现 OOM ，表现出来的就是出现 MySql 异常重启了。

那么，我们该怎么去解决这种问题呢？我们可以参考如下两种方案：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. MySQL 5.7之后，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

通过上面连接器，我们已经成功建立了连接，这个时候我们就可以用 select 语句了，此时就会进入到我们的第二步：查询缓存。

MySql 在收到 select 查询请求之后，他会先去查询缓存组件中看看，看看这条语句之前有没有被查询过，如果有被查询过，其会以 key-value 对的形式缓存在查询缓存组件内，key 即为查询语句，value 即为查询结果集。此时，如果我们的查询语句能命中缓存中的key，那么其 value 就会立刻返回给我们客户端，速度是很快的。

但是，一般情况下，我不建议查询缓存，因为查询缓存往往是弊大于利的

因为，查询缓存失效是很频繁的，表只要有更新操作，就会清空这个表上的所有查询缓存。所以，结果就是，我们好不容易将数据缓存起来了，就被一条更新操作给全部清空了。这样的话，对于我们频繁更新的数据库来说，其实查询缓存的命中率是相当低的。当然，如果我们的表是一张静态表，比如配置表，更新操作间隔时间很长，那这样的表还是很试合使用查询缓存的。

那么，我们该怎么去控制使用查询缓存呢？MySql 在这方面做的很友好，我们可以将参数 query_cache_type 设置成 DEMAND ，这样我们的 SQL 语句默认就是不使用查询缓存，而对于我们想要使用查询缓存的语句，只需要使用 SQL_CACHE 显示指定就行了，如下所示：

mysql> select SQL_CACHE * from T where ID=10；

注意：MySQL 8.0 之后就已经将查询缓存这个模块整个彻底删除了

分析器

当没有命中查询缓存的时候，MySQL 就要开始真正执行查询语句了。首先，MySQL 它需要知道我们要做什么，所以，他就需要对 SQL 语句进行解析。

• 分析器首先要做 “词法分析”，因为我们输入的一条 SQL 语句中，是由字符串和空格组成的，MySQL 需要分析出这里面的字符串分别是什么，又代表了什么。
• MySQL 会将我们输入的 select 关键词识别出来，就会代表是一个查询语句，然后回将字符串 T 识别成 “表名 T ”，将字符串 ID 识别成 “列名 ID”。
• 接着 MySQL 就会做 “语法分析”，根据上述词法分析的结果，语法分析器会根据语法规则，判断我们输入的 SQL 语句是否满足 MySQL 语法。

如果我们的 SQL 语法写的不对的话，就会收到这样的一条错误提示 “You have an error in your SQL syntax”，如下，我 select 写成了elect：

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般出现语法错误的 SQL 语句，它会提示出第一个出现错误的地方，所以，我们要着重关注它提示的 “use near” 的地方。

优化器

经过分析器分析之后，MySQL 就知道了我们要做什么了，但是在具体的执行之前，还是需要经过优化器的处理。那么优化器是如何切入进来的呢？

我们在创建表的时候，通常会建立多个索引，而决定该使用哪个索引的时候就是优化器的责任；还有就是我们时常也会写 连表 join 的语句，而决定各个表的连接顺序的也是优化器。如下展示了两个表 join 的操作：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 如上，mysql 可以从 t1 表中取出 c = 10 记录的 ID 值，去关联 t2 表，然后判断 t2 表的字段 d 是否等于20；
• mysql 也可以从 t2 表中取出 d = 20 记录的 ID 值，去关联 t1 表，然后判断 t1 表字段 c 是否等于10。

这两种方法，最终执行逻辑的结果均是一样的，但是执行的效率可能会不同，而这个时候优化器就会出来选出最好的方案去执行。

优化器进行之后，这条 SQL 语句的执行方案就会确定下来，接着就会进入到执行器阶段。

执行器

如上，MySQL 通过分析器知道了我们需要做什么，然后通过优化器知道了自己该如何更好的去做，最后，就会来到执行器过程，开始真正执行我们的 sql 语句。

在执行语句之前，它会判断我们对于 T 表有没有查询的权限，如果没有权限，就会给我们返回没有权限的错误提示，如下：

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果判断出我们有查询的权限，它就会打开表继续操作。在打开表的时候，MySQL 会根据这个表的存储引擎定义，去使用引擎提供的接口。

以我们的查询语句为例，现在我们的 T 表中 ID 字段没有索引，那么，她的执行流程是这样子的：

• 首先，调用InnoDB引擎接口，取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
• 然后，取 “下一行”，重复之前的判断逻辑，一直取到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

这样，这条 SQL 语句就执行结束了。

对于有索引的表，其执行逻辑也是差不多的，调用的接口都是引擎中定义好的接口，这个语句主要设计两个接口：

• 取满足条件的第一行
• 循环取满足条件的下一行

当我们在数据库的慢查询日志中会看到 rows_examined 字段，其表示当前语句在执行的时候一共扫描了多少行。

不过，在有一些场景下，执行器执行一次，而在引擎内部却扫描了多行，所以，引擎扫描行数跟 rows_examined 并不是完全相同的，我们后面会在 MySQL 的存储引擎内部机制中进行讲解。

总结

今天，我们主要学习了 MySQL 的逻辑架构，知道了其主要有两大部分 Server 和存储引擎层组成，然后，我们通过对于一条 SQL 语句的分析知道了其在MySQL 中的所有执行过程，主要从连接器、查询缓存、分析器、优化器以及执行器各个组件进行了解析，更多内容留给后面吧，希望今天的内容对你有所帮助，谢谢。

往期热门文章：

1，架构的本质：如何打造一个有序的系统？ 2，分布式高可靠之负载均衡，今天看了你肯定会 3，分布式数据之缓存技术，一起来揭开其神秘面纱 4，分布式数据复制技术，今天就教你真正分身术 5，数据分布方式之哈希与一致性哈希，我就是个神算子 6 ，分布式存储系统三要素，掌握这些就离成功不远了 7 ，想要设计一个好的分布式系统，必须搞定这个理论 8 ，分布式通信技术之发布订阅，干货满满 9，分布式通信技术之远程调用：RPC 10 ，秒杀系统每秒上万次下单请求，我们该怎么去设计