Loading...
墨滴

杨过

2021/11/23  阅读:14  主题:默认主题

【MySQL原理解析】01. 一条SQL查询语句是如何执行的

这是【MySQL原理解析】的第一篇文章,MySQL我看了很多的书与教程,对其原理有一定的理解,一直想写一系列的文章来把MySQL的原理给讲清楚,一直没有时间写,今天算是个开头吧。万事开头难,咱们先破了这个开头!

MySQL基本架构

我们常说,看一件事不要直接陷入细节,应该先从整体框架与流程上把握住,先从最高的维度理解问题,然后再逐步深入各个模块。学习MySQL也是一样,在使用MySQL的过程中,我们最开始都是从一条简单的查询语句开始。如在学生表student中按照学生的id来查学生的信息:

  select * from student where id = 100;

在我们程序员眼里,这就是一条sql语句,执行之后,返回一条结果。这看起来很简单的过程,实际在MySQL内部,却是一个非常复杂的过程。今天我们就来把这个过程拆解出来。 下面给出的是MySQL的基本架构示意图,从中可以看出SQL语句在MySQL的各个功能模块的执行过程。

MySQL的基本架构可以分为两大块,一块是Server层,一块是存储引擎层。

Server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖了MySQL大多数的核心服务功能、以及所有的内置函数,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器。视图等。

存储引擎层负责数据的存储与提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memmory等多个存储引擎。最常用的是InnoDB存储引擎。所以我们的系列文章也主要是以InnoDB存储引擎为主。

连接器

在客户端执行上面的查询语句时,客户端首先会与服务端建立一个TCP连接,每个客户端连接都会在服务端进程中拥有一个线程。这个连接的查询只会在这个单独的线程中执行,该线程只能轮流在某个CPU中运行。服务器会负责缓存线程,因此不需要为每一个新建的连接创建或者销毁线程。在连接的过程中还涉及到一些安全校验与权限校验等。

连接成功后,如果没有后续的动作,这个连接就处于空闲的状态,你可以使用 show processlist 命令来查看线程的各个状态。客户端如果长时间没有动静,连接器会自动将它断开。这个时间由参数wait_timeout控制,一般是8小时。如果在连接被断开后,客户端再次发送请求的话,就会收到一个错误提醒:Lost connection to MySQL server during query。这时候如果还要继续查询,就要重新建立连接,然后再执行请求。

在数据库领域,长连接是连接成功后,如果客户端有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

我们知道TCP建立连接的过程都是非常复杂的,所以应该降低建立连接这个动作,也就是尽量使用长连接。但是全部使用长连接后,有可能也会导致MySQL占用内存特别快,这是因为MySQL在执行的过程中,临时使用的内存都是管理在连接对象里的。这些临时使用的内存在断开连接才会被释放。所以如果长连接积累下来,可能导致占用内存太大,被系统强行杀掉(OOM),异常重启。

想要使用长连接,并且还要解决这种OOM问题的话,可以考虑以下两种方案。

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

查询缓存

连接建立成功后,就可以进行select查询语句了。MySQL拿到一个查询请求后,会先到查询缓存看看之前是否有过一样的查询,如果有则直接返回结果。如果没有,才会进行后面的操作。

我们可以看到,查询缓存如果命中,就不会走后面的分析器、优化器以及存储引擎提取数据了。但是存储引擎在最新的MySQL8.0版本中已经不再支持查询缓存的功能了。为什么呢?

因为查询缓存失效非常频繁,只要有对一个表的更新操作,这个表上所的查询缓存都会被清空。因此很可能你费劲的把缓存建立起来,还没使用呢,就被一个更新全清空了。对于更新压力大的数据库来说,查询缓存的命中率会非常低。

分析器

解析器说白了就是对你输入的sql语句的解析,解析成MySQL这个服务端程序能够识别的代码。解析的过程中肯定会有判断sql语句是否正确的语法解析的过程。如果你的语法不对,肯定会报错。这个过程的原理涉及到词法分析树与语法分析树,较为复杂,这里暂时不深究。

优化器

经过分析器解析出MySQL能够识别出的代码后,优化器会对这部分查询代码做一系列的算法优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器,影响它的决策过程。也可以请求优化器解释(explain)优化过程的各个因素,使用户可以知道服务器是如何进行优化决策的。

对于优化器有哪些优化方法来优化查询,在后面的章节我们会详细说明。

执行器

MySQL通过分析器知道了你要做什么,通过优化器知道了该如何做,接下来就该真正的开始执行了。

在开始执行的时候,MySQL会再次判断本次查询是否对要查询的表有执行查询的权限,如果没有会报错。如果有权限就打开表继续执行。打开表的时候,执行器会根据这个表所使用的存储引擎,选择对应的存储引擎接口。

小结

本篇文章主要讲解了MySQL的基本架构,MySQL的整体架构还是非常复杂的,我们能够将MySQL的基本架构搞懂就行。这次主要学会一条查询语句,大致需要经历哪些流程,经过了哪些模块,每一个模块的细化流程都相当复杂,我们也不必把每一个模块都搞懂。作为一名后端开发人员,将优化器与存储引擎层的相关原理搞懂就可以。

在后面的章节,我们会深入学习优化器与存储引擎相关原理!

杨过

2021/11/23  阅读:14  主题:默认主题

作者介绍

杨过