一条SQL是如何在mysql执行的

要了解一条SQL是如何在mysql中执行的，首先需要了解mysql的架构。

mysql架构

MySQL的架构分为两层：Server层与存储引擎层。

• Server 层负责建立连接、分析和执行SQL。
  MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。
  另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。

• 存储引擎层负责数据的存储和提取。
  支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
  我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

1. 客户端：客户端（如 MySQL 命令行工具、Navicat、MySQL Workbench 或其他应用程序）发送 SQL 查询到 MySQL 服务器。

2. 连接器：连接器负责与客户端建立连接、管理连接和维护连接。当客户端连接到 MySQL 服务器时，连接器验证客户端的用户名和密码，然后分配一个线程来处理客户端的请求。

3. 查询缓存：查询缓存用于缓存先前执行过的查询及其结果。当收到新的查询请求时，MySQL 首先检查查询缓存中是否已有相同的查询及其结果。
   如果查询缓存中有匹配的查询结果，MySQL 将直接返回缓存的结果，而无需再次执行查询。但是，如果查询缓存中没有匹配的查询结果，MySQL 将继续执行查询。查询缓存在 MySQL 8.0 中已被移除。

4. 分析器：

   • 解析查询语句，检查语法。
   • 验证表名和列名的正确性。
   • 生成查询树。

5. 优化器：分析查询树，考虑各种执行计划，估算不同执行计划的成本，选择最佳的执行计划。

6. 执行器：根据优化器选择的执行计划，向存储引擎发送请求，获取满足条件的数据行。

7. 存储引擎（如 InnoDB）：
   负责实际执行索引扫描，在使用非聚簇索引时访问全部列数据会涉及回表访问磁盘。
   在访问磁盘之前，先检查 InnoDB 的缓冲池（Buffer Pool）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。

8. 执行器：
   对于每个找到的记录，会再次判断记录是否满足索引条件。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次索引条件，满足索引条件则继续判断其他过滤条件。

连接器

MySQL 是基于TCP协议的，所以连接需要先经过TCP三次握手。TCP建立连接成功之后会验证用户名和密码，验证不通过会收到Access denied for user的错误提示；
如果验证通过会继续获取用户的权限，然后保存起来，此后任何在此连接的操作都会基于连接时读取到的权限进行鉴权。
所以一个连接建立后，即使管理员修改了用户权限，也不会影响已经存在的连接权限，之后重新连接才会使用新的权限设置。

执行show processlist命令可以查看多少客户端连接了本MySQL服务端。

其中id为6的用户Command列的状态为 Sleep 意味着用户连接完MySQL服务之后就没有在执行过任何命令，也就是一个空闲连接，并且时长为 736s。

空闲连接不会一直占用着，MySQL 中参数 wait_timeout 定义了空闲连接的最大空闲时长，默认是 8小时(28880秒)，如果操过了这个时间连接器会自动断开。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

也可以手动断开连接，使用命令:kill connection + id

mysql> kill connection +6;
Query OK, 0 rows affected (0.00 sec)

连接断开后客户端不会立马收到通知，会在客户端发起下一个请求时候收到 ERROR 2013 (HY000): Lost connection to MySQL server during query 报错。

MySQL的连接数是有限制的，由参数 max_connections 控制，如果操作这个连接数，服务会拒接后续连接请求，并提示： _Too many connections_。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

MySQL也存在短链接与长链接的概念：

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。
但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。
如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

有两种解决方式：

第一种，定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。

第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口(注意这是接口函数不是命令)，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

因为只要表中的数据有更新，表的查询缓存就会被清空，如果表的更新比较频繁，可能缓存没有被使用就又被清空了，导致查询缓存比较鸡肋。所以在 MySQL 8.0 版本直接将查询缓存删除了。

对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND。

注意： 这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。

解析器

在正式执行SQL之前，MySQL会对SQL做语句的解析，这个工作是由解析器完成的。
解析器核心会做两件事：

第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

注意，表不存在或者字段不存在的校验并不是在解析器里做的，看 MySQL 源码（5.7和8.0）结论是解析器只负责构建语法树和检查语法，但是不会去查表或者字段是否存在。（预处理阶段做的）

预处理器

预处理器负责：

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

MySQL 8.0 源码来证明表或字段是否存在的判断，不是在解析器里做的，而是在 prepare 阶段：

优化器

经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

执行器

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

名词解释

B+ 树

虽然 平衡二叉树 查找的效率非常高，但是我们的表数据与索引数据都是存储在磁盘这些外围设备中的，其读取数据相比内存慢上成千上万倍，所以应该减少从磁盘读取数据的次数。
另外从磁盘读取的数据都是按磁盘块读取的，并不是一条条读取的。我们应该把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

平衡二叉树的高度会随着数据量而增长，这样会导致在查找数据的时候会进行过多的磁盘IO，查找效率低下。
为了解决平衡二叉树的这个弊端，应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是接下来要说的 B 树。

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：

B+ 树是对 B 树的进一步优化。让我们先来看下 B+ 树的结构图：

根据上图我们来看下 B+ 树和 B 树有什么不同：

① B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。
如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。
一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

② B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。
B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
其实上面的 B 树我们也可以对各个节点加上链表。这些不是它们之前的区别，是因为在 MySQL 的 InnoDB 存储引擎中，索引就是这样存储的。
也就是说上图中的 B+ 树就是 InnoDB 中 B+ 树索引真正的实现方式，准确的说应该是聚集索引。
通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

聚集索引、非聚集索引

B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

①聚簇索引：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。
这是因为 InnoDB 是把数据存放在 B+ 树中，B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。 这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

②非聚簇索引：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。
非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

参考

一条 SQL 如何被 MySQL 架构中的各个组件操作执行的？

执行一条 select 语句，期间发生了什么？

b+树详解