《进大厂面试系列》-MySQL基本架构、SQL执行过程

大家好，我是牛牛哥。
这世上，没有谁活得比谁容易，只是有人在呼天抢地，有人在默默努力。　
点赞再看，养成习惯，每一个赞都是牛牛哥更新下去的最大动力。

 

前言

MySQL客户端/服务端通信协议是 半双工 的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

注意

文末有：2176页互联网大厂面试题~~

1）客户端用一个单独的数据包，将查询请求，发送给服务器。

当查询语句很长的时候，需要设置 max_allowed_packet参数。如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

2）服务器响应给用户的数据通常会很多，由多个数据包组成。

当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT*以及加上 LIMIT限制的原因之一。

MySQL 分为两部分：

1. Server 层

包括：

1. 1. 连接器
   2. 查询缓存
   3. 分析器
   4. 执行器等
   5. 所有的内置函数（如日期、时间、数学和加密函数等）
   6. 跨存储引擎的功能（如存储过程、触发器、视图）

2.存储引擎层（负责数据的存储和提取）

支持 InnoDB（MySQL 5.5.5 版本后默认）、MyISAM、Memory 等多个存储引擎。

和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

连接器（Connector）

在查询 SQL 语句前，肯定要先建立与 MySQL的连接，这就是由连接器来完成的。

连接器负责

1. 跟客户端建立连接
2. 获取权限
3. 维持和管理连接

连接命令为：

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

1. 输入密码，验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限（一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置）
2. 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。

数据库长的连接和短连接：

1. 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
2. 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。

但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢？可以考虑以下两种方案：

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

查询缓存（Query Cache，MySQL 8.0 版本将查询缓存的功能删除了）

在建立连接后，就开始执行 select 语句了，执行前首先会查询缓存，看是不是执行过这条语句。

执行过的语句及其结果会以 key-value 对的形式保存在一定的内存区域中。key 是查询的语句，value 是查询的结果。

MySQL将缓存存放在一个引用表（类似于 HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数 NOW()或者 CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含 CURRENT_USER或者 CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

1. 如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
2. 如果语句不在查询缓存中，就会继续后面的执行阶段。
3. 执行完成后，执行结果会被存入查询缓存中。

但是查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

对于更新压力大的数据库来说，查询缓存的命中率会非常低。如果业务中需要有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 提供了这种按需使用的方式。可以将参数 query_cache_type 设置成 DEMAND，对于默认的 SQL 语句都将不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，如下：

mysql> select SQL_CACHE * from user_info where id = 1;

为什么删除查询缓存功能？因为查询缓存往往弊大于利。

1. 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务有一张静态表，很长时间更新一次，比如系统配置表，那么这张表的查询才适合做查询缓存。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：
   1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存。
   2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。
2. 查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

分析器（Analyzer）

如果查询缓存未命中，就要开始执行语句了。这个过程解析器主要通过语法规则来验证和解析，并生成一颗对应的解析树。

MySQL 需要对 SQL 语句进行解析：

1. 先做词法分析。

SQL 语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的 select 这个关键字识别出来，这是查询语句。它也要把字符串 user_info 识别成表名，把字符串 id 识别成列名。

2.后做语法分析。

根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足 MySQL 语法。如果你 SQL 语句不对，就会收到 You have an error in your SQL syntax 的错误提醒。

优化器（Optimizer）

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

1. 优化器是在表里面有多个索引的时候，决定使用哪个索引
2. 或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

在MySQL可以通过查询当前会话的 last_query_cost的值来得到其计算当前查询的成本：

mysql> select * from t_message limit 10; mysql> show status like 'last_query_cost'; | Variable_name | Value | Last_query_cost | 6391.799000 |

示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

1. 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
2. 优化 MIN()和 MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）
3. 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
4. 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

执行器（Actuator）

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个 handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

MySQL 通过分析器知道了要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表 user_info 有没有执行查询的权限：

1）如果没有，就会返回没有权限的错误，如下所示（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限）。

mysql> select * from user_info where id = 1;
ERROR 1142 (42000): SELECT command denied to user 'wupx'@'localhost' for table 'user_info' 

2）如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表 user_info 中，id 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 1：
   1. 如果不是则跳过。
   2. 如果是则将这行存在结果集中。
2. 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

对于有索引的表，第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口。

数据库的慢查询日志中有 rows_examined 字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如改查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端，是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

感谢您的点赞，就是牛牛哥的动力噢~

我们下期再见~

阅读原文：高清 2176 页大厂面试题 PDFhttp://mp.weixin.qq.com/s?__biz=Mzg4NTcyNzE0OA==&mid=2247483730&idx=1&sn=d427de1152475d4a9ad8f0c7f21297dc&chksm=cfa5cb91f8d24287053cc133ddb3e6b151777b7979751bfd65ca5d75e00e473aba792fea8323&scene=21#wechat_redirect