当执行一条 select 语句时，MySQL 到底做了啥？

短命女 2022-03-01 03:12 310阅读 0赞

原文链接：[https://blog.csdn.net/bruce\_6/article/details/84383878\#commentBox][https_blog.csdn.net_bruce_6_article_details_84383878_commentBox]

也许，你也跟我一样，在遇到数据库问题时，总时茫然失措，想重启解决问题，又怕导致数据丢失，更怕重启失败，影响业务。

就算重启成功了，对于问题的原因仍不知所以。

本文开始，记录学习[《MySQL实战45讲》][MySQL_45]专栏的过程。

也许有人会问，你记录有什么意义？直接看专栏不就行了吗？你这不是啃别人的剩骨头吗？

是的，这个系列，我只是基于专栏学习，但是我会尽量从我的角度搞懂每一个知识点，遇到不懂得也会将知识点进行拆分。

我知道关注公众号的小伙伴也有很多购买了这个专栏的，我希望大家都能够利用好这个机会，把 MySQL 吃透！

看大家的反馈情况吧，若有需要，可以建个小群，大家互相讨论学习！

下面开始正文。

大家或多或少都用过 MySQL，起码 select 还是会用的吧，但是 select 执行后，MySQL 内部到底发生了什么，你知道吗？

比如，我们有个简单的表 T，它有个 ID 字段，那么我们可以执行下面的语句：

mysql> select * from T where ID=10;
    
     
      
     
      
       1

语句执行很简单，但是具体到 MySQL 内部，其实是一个完整的执行流程。

# MySQL 的基本架构 #

从下图就可以清楚地看出 MySQL 的命令执行流程：

![MySQL 的基本架构][MySQL]

从该图可以看出，MySQL 主要分为 server 层和存储引擎层。

*  server 层中包含连接器，查询缓存，分析器，优化器，执行器，大多数核心功能以及内置函数，存储过程，触发器，视图等。
 *  存储引擎层主要负责最终数据的存储和提取，例如常用的存储引擎 InnoDB、MyISAM 等。

好了，下面开始梳理一次完整的查询流程。

![MySQL 执行流程][MySQL 1]

# 1 连接 #

首先通过连接器连接到数据库。

连接器的主要作用是**建立连接，获取用户权限，维持连接，管理连接**。

连接的一般命令就是我们常用的登陆数据库的命令：

mysql -u$username -h$host -p$port -P
    
     
      
     
      
       1

命令执行后，若用户名或者密码不对，或者数据库做了登录 ip 限制，都会收到异常信息。

若登陆成功，那么就代表连接成功建立。

之后连接器会维持当前连接，接下来连接器会查询出该用户的权限，后面所有的操作都会基于该权限，即使操作过程中有其他进程修改了该用户的权限。

连接完成后，若没有任何操作，连接就处于休眠状态，用命令 `show processlist;` 查看，就是 `Sleep` 状态的进程：

![睡眠状态][20181220105243444]

当然，连接器不会让你一直握着连接不动，若休眠时间超过 `wait_timeout`（默认为 8 小时），则会断开当前连接。

若要再用，对不起，请重新连接~

## 长连接和短连接 ##

其实这里的长短连接不是 MySQL 层面的概念。

*  长连接：长连接是相对于短连接来说的。长连接指在一个连接上可以连续发送多个数据包，在连接保持期间，如果没有数据包发送，需要双方发链路检测包。我理解 MySQL 默认的超时时间 8 小时，就属于一个长链接。

客户端连接--创建 socket 认证连接--维护连接--数据传输--维护连接--数据传输.....-关闭连接
    
     
      
     
      
       1

*  短连接：是指通讯双方有数据交互时，就建立一个连接，数据发送完成后，则断开此连接，即每次连接只完成一项业务的发送。

客户端连接--创建 socket 认证连接--维护连接--数据传输--关闭连接
    
     
      
     
      
       1

长连接主要用于在**少量客户端**与服务端的**频繁通信**，因为这时候如果用短连接频繁通信常会发生 Socket 出错，并且频繁创建 Socket 连接也是对资源的浪费。

专栏中老师是建议使用长链接的，因为建立连接的过程比较复杂，应该尽量减少建立连接的动作。

## 长连接的管理 ##

使用长连接后，随着连接数不断增加，会导致内存占用升高，因为 MySQL 在操作过程中会占用内存来管理连接对象，只有等到连接断开后才会释放。

如果连接一直堆积，就会导致内存占用过大，被系统强行杀掉，也就是会出现 MySQL 重启。

如何解决这个问题？

1、定期断开长连接；  
2、MySQL 5.7+ 的版本中提供了 `mysql_reset_connection` 来重新初始化连接资源，这时不需要重新连接，就可以将连接恢复到刚刚创建完时的状态；

*  mysql\_reset\_connection

对于 `mysql_reset_connection` ，MySQL 官网的描述是这样的：

> 将连接重置，清空连接状态。  
> 类似于重新连接，但是不会关闭当前连接，也不会进行重新鉴权。

会产生如下影响：

1、会回滚所有活动事务，并重置自动提交模式；  
2、会释放所有的锁表；  
3、所有的临时表会被关闭并清除；  
4、Session 系统变量会被重新初始化为相应的全局系统变量的值；  
5、用户自定义变量会丢失；  
6、会释放 Prepared statements；  
7、`HANDLER` 变量会被关闭；  
8、`LAST_INSERT_ID()` 函数的值会被重置为 0；  
9、通过 `GET_LOCK()` 函数获得的锁会被释放；

以上影响，翻译自官方文档，有些可能不太准确，有兴趣的可以到官网自行查阅原文。

*  数据库连接池？

另外，不少实际的应用框架中，大都使用**连接池**来维护连接数。

> 数据库连接池，就是服务器应用建立多个连接到数据库，还没有用的连接就放到连接池上，要的时候就向连接池取，这样比没有连接时再建立新的连接（TCP 建立连接是需要时间的）时要快很多，从而提高传输效率。

如 Spring 框架中，它实现了一个持久连接池，允许其他程序、客户端来连接，这个连接池将被所有连接的客户端共享使用，连接池可以加速连接，也可以减少数据库连接，降低数据库服务器的负载。

# 2 查询缓存 #

**缓存**，就是提前预备好的数据，数据库查询缓存也是缓存的一种。

在解析一个查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前 MySQL 会检查一次用户权限。若权限没有问题，MySQL 会跳过所有其他阶段（解析、优化、执行等），直接从缓存中拿到结果并返回给客户端。

这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

## 缓存哪里来的？ ##

查询时如果没有命中查询缓存，MYSQL 会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存。

mysql query cache 的内容为 select 的结果集，在内存中是以 HASH 结构来进行映射。

cache 会使用完整的 sql 字符串做 key，并**区分大小写**，空格等。即两个 sql 必须完全一致才会导致 cache 命中。

## 缓存何时失效？ ##

在表的结构或数据发生改变时，查询缓存中的数据不再有效。

所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

a) 一旦表数据进行**任何一行**的修改，基于该表相关 cache 立即全部失效，并且从缓冲区中移出；  
b) 为什么不做聪明一点判断修改的是否 cache 的内容？因为分析 cache 内容太复杂，服务器需要追求最大的性能。

## 缓存可以提高查询效率的？ ##

当有大量的查询和大量的修改时，cache 机制可能会造成性能下降。

因为每次修改会导致系统去做 cache 失效操作，这就会造成不小的开销。

另外系统 cache 的访问由一个单一的全局锁来控制，这时候大量的查询将被阻塞，直至锁释放。

所以不要简单认为设置 cache 必定会带来性能提升。

参考：[https://www.cnblogs.com/duanxz/p/4385733.html][https_www.cnblogs.com_duanxz_p_4385733.html]

其实，在 8.0 版本开始，缓存功能被直接删除。

# 3 解析器 #

## 词法解析 ##

词法分析的作用是将整个查询分解为多个元素。

我们输入的 MySQL 命令，不过是一串长长的字符串，MySQL 的**分析器**会对其进行词法解析。

select * from T where ID=1;
    
     
      
     
      
       1

比如，上述语句是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的 `select` 这个关键字识别出来，这是一个查询语句。

它也要把字符串 `T` 识别成一个表名，把字符串 `ID` 识别成一个列。

其实，大家也可以思考一下，若让你手写一个词法分析的工具，你该如何实现呢？

## 语法分析 ##

做完初步的词法分析后，就要做**语法分析**。

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

如果你的语句不对，就会收到 `You have an error in your SQL syntax` 的错误提醒。

解析器的最终执行结果就是**解析树**，提供给优化器使用。

# 4 优化器 #

当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。

## 优化器到底干啥的？ ##

MySQL 的优化器有几个重要任务：

1、选择最合适的索引；  
2、选择表扫还是走索引；  
3、选择表关联顺序；  
4、优化 where 子句；  
5、排除管理中无用表；  
6、决定 order by 和 group by 是否走索引；  
7、尝试使用 inner join 替换 outer join；  
8、简化子查询，决定结果缓存；  
9、合并试图；

MySQL 查询优化器有几个目标，但是其中最主要的目标是**尽可能地使用索引**，并且使用最严格的索引来消除尽可能多的数据行。

优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。

## 优化器是如何工作的？ ##

到底优化器是如何进行选择的？如果每个点都展开，那都需要很长的篇幅，我再网上翻阅了一些资料，看得也是云里雾里，后面结合专栏老师的讲解再学习吧。

这里举几个优化的示例：

*  示例 1

假设你的查询检验了两个数据列，每个列上都有索引：

SELECT col3 FROM mytable
    WHERE col1 = 'value1' AND col2 = 'value2';
    
     
      
     
      
       1
       2

假设 col1 上的测试匹配了 900 个数据行，col2 上的测试匹配了 300 个数据行，而同时进行的测试只得到了 30 个数据行。

先测试 col1 会有 900 个数据行，需要检查它们找到其中的 30 个与 col2 中的值匹配记录，其中就有 870 次是失败了。

先测试 col2 会有 300 个数据行，需要检查它们找到其中的 30 个与 col1 中的值匹配的记录，只有 270 次是失败的，因此需要的计算和磁盘 I/O 更少。

其结果是，优化器会先测试 col2，因为这样做开销更小。

*  示例 2

尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。

比如，下面的 WHERE 子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：

WHERE mycol < 4 / 2
    WHERE mycol * 2 < 4
    
     
      
     
      
       1
       2

对于第一行，优化器把表达式 4/2 简化为 2，接着使用 mycol 上的索引来快速地查找小于 2 的值。

对于第二个表达式，MySQL 必须检索出每个数据行的 mycol 值，乘以 2，接着把结果与 4 进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

优化器的内容还可以有很多，这个专栏老师说后续会还有讲。

# 5 执行器 #

下面就到了最终的执行阶段，执行开始之前，会先判断是否有操作权限，若没有，会抛出相关异常。

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

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

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

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是**取满足条件的第一行**这个接口，之后循环取**满足条件的下一行**这个接口，这些接口都是引擎中已经定义好的。

可以看出，是否有索引，执行效率区别还是很大的，没有索引需要取出所有数据，一个个进行比较；而有索引则是直接取满足条件的数据；

# 课后题目 #

问题：

> 如果表 T 中没有字段 k，而你执行了这个语句 select \* from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答案：分析器阶段。

网友回答：

> [《高性能mysql》][mysql]里提到解析器和预处理器。  
> 解析器处理语法和解析查询, 生成一课对应的解析树。  
> 预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

> 文中讲解分析器阶段时提到，MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。  
> 所以应该是分析器。

> 我猜测应该在分析阶段，根据文章介绍分析器的作用是让mysql知道你要做什么，对语法的分析应该是第一部，语法词法分析完成后应该是解析这条sql到底要执行什么操作，插入还是更新还是建表还是查询，这时mysql应该已经知道你想操作那个表而这个表存不存在，从而才能匹配不同的优化器类

> 最后就作者的问题，分析为什么是分析器，因为文章中说了词法分析的时候会解析出查询的表，列等等，所以此时就应该能知道表列的存在性。而且从我个人的拙见来看，如果先一步判断出这种无法查询的错误，避免后续执行，则可以避免无谓的性能开销。而表列的数据较少，完全可以这里判断。  
> 当然，也可以在句法分析的步骤判断，个人数据库不太熟悉，只能从程序设计的角度考虑，望各位大佬真诚的评论

> 问：  
> 丁老师，既然在链接阶段已经通过权限表获取了这个该连接所具有的权限，那么在执行阶段再检查一次的意义何在，谢谢！  
> 作者回复：  
> 执行器阶段会碰到需要再判断权限的情况，这时候读内存中事先存好的权限，而这个权限是在连接器阶段算出来存进去的

> 问：  
> 长连接占用内存猛涨的情况下,您提供两种解决方案,您倾向于在生产环境使用什么方案呢? 为什么呢? 或者你评价这两种方案在生产环境有什么优劣呢?  
> 作者回复：  
> 5.7以上就建议用mysql\_reset\_connection 方法，低版本就定期断开重连

[https_blog.csdn.net_bruce_6_article_details_84383878_commentBox]: https://blog.csdn.net/bruce_6/article/details/84383878#commentBox
[MySQL_45]: https://www.baidu.com/s?wd=%E3%80%8AMySQL%E5%AE%9E%E6%88%9845%E8%AE%B2%E3%80%8B&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[MySQL]: /images/20220301/0e4d78b56bee4c4ca93e55db1bc39c6d.png
[MySQL 1]: /images/20220301/b50c5e8309d249fcb6bc8ea962842c8d.png
[20181220105243444]: /images/20220301/870397187ce04d6baeaacfd4900240fd.png
[https_www.cnblogs.com_duanxz_p_4385733.html]: https://www.cnblogs.com/duanxz/p/4385733.html
[mysql]: https://www.baidu.com/s?wd=%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDmysql%E3%80%8B&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd