1.MySQL的逻辑架构
在开始MySQL的学习之前,先来绘制一副MySQL的基本逻辑架构图是有助于我们学习MySQL的。
以下是由林晓斌(网名丁奇)在《MySQL实战45讲》中提供的MySQL逻辑架构示意图修改后的结果,删除了缓存查询的部分(MySQL8.0后移除该模块)。
2.Server层的组件
2.1.连接器
连接器是大多数基于网络的客户端/服务器的工具都有的架构。主要负责客户端建立连接,获取权限,授权认证和管理连接等。
客户端发起连接命令到MySQL的服务端,会进行权限验证(用户名,密码等),验证完成后,服务端建立起一个线程供该连接使用。
在连接建立起来后,连接器会在权限表里查询拥有的权限,此时权限已经确定,之后的权限判定,都依赖于此时的权限,即便是管理员账户修改了权限,也不会影响已经建立的连接的权限,直到下次重新连接后,权限发生变化。
短连接和长连接
经常会听到短连接和长连接,本质上MySQL不提供这两种连接,这两种连接也是基于你保持连接时间长短而定义的。
短连接的执行流程:
长连接的执行流程:
保持一个连接是为了避免频繁权限验证,权限查询等带来的性能损耗,如果你的业务会频繁与数据库进行交互,那么建立起连接后,便置于连接池中,等需要的时候再拿出来使用。
如果你只需要每天查询一次(如调度任务),那么查询完毕后,及时断开连接,可以降低空闲连接带来的性能损耗。
空闲连接
在连接建立后,如果长时间不使用,则连接处于Sleep的状态,这时就产生了一个空闲连接。
可以使用如下命令查看当前的链接:
show processlist;
此时我的MySQL中保持了3个root用户的连接,其中处于活跃状态的是ID=459的连接,至于另外两个连接,则处于Sleep的状态,这就是两个空闲连接。
至于ID=5的event_scheduler保持的连接,从Daemon也可以看出,这是一个守护进程。event_scheduler,事件调度器,MySQL 5.1中新增的功能,取代部分只能在操作系统任务调度器中完成的调度任务。
自动断开连接
如果不主动断开连接,它会永远保持吗?
MySQL的连接器会自动断开长时间未使用的连接,这个时间参数由wait_timeout决定。
可以使用如下命令来查看wait_timeout:
show variables like 'wait_timeout';
连接的优化
建立MySQL的连接是一个复杂的过程,如果频繁的创建连接,则会大量的消耗内存。
这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的,这些资源会在连接断开的时候才释放,如果长连接积累下来,可能会导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了。 – 林晓斌,《MySQL实战45讲》
同时林晓斌提供了两个建议:
- 定时断开空闲的长连接,重新建立连接,以释放资源;
- 当执行完一个占用内存较大的查询后,使用mysql_reset_connection(MySQL 5.7后提供的指令)来重置连接资源,这个过程不需要重连和重新做权限验证,但是会将连接恢复到初始状态。
2.2.分析器
在MySQL 8.0之前,MySQL提供了一个缓存查询,只有缓存无法命中的的SQL语句才会进入分析-优化-执行的流程,但是数据这个东西,通常频繁查询的数据会经常变化,变化较少的又不会频繁查询,所以缓存的命中率是非常低的,MySQL官方也是意识到了这点,所以在MySQL 8.0中删除了缓存模块(既然你们都不开启缓存,那么我就删掉它)。
分析器用来解析SQL语句并构建内部数据结构的(解析树)。 分析器可以分为两部分:词法分析器和语法分析器。
词法分析器用来构建解析树。 例如:
select user_name from user;
在解析器的工作下,会识别出:
- select,查询语句;
- from user,查询表user;
- user_name,获取列user_name的值。
语法分析器用来校验输入的SQL语句是否正确。
通常我们见到的“You have an error in your SQL syntax”便是由语法解析器发现并提示的。
2.3.优化器
分析器的工作完成后,紧接着就是优化器进行工作。优化器主要负责:重写查询,表的读取顺序,选择合适的索引等。
通常,经过优化器后,MySQL已经拿到了一个最优的执行计划(但这不是绝对的),随后则交由执行器去执行这条SQL语句。
如果我们想知道优化器是如何决策的,可以请求优化器解释,如:
-- 表结构
CREATE TABLE `t_user` (
`user_id` bigint NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` varchar(2) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 查看执行计划
explain select * from user where id = 1;
可以看到MySQL经过优化器的优化后选择了使用主键(具体的关于执行计划,会在后续分析)。
但极小概率下优化器的选择并不是最佳的,我们可以通过查看执行计划,查看优化器的选择,并根据结果去“诱导”优化器重新选择。
虽然优化器并不关心底层的存储引擎,但实际上,优化器的选择会受到存储引擎的影响。
在优化器进行优化的过程中,会请求存储引擎,提供容量,操作开销,表数据的统计信息等。并作为优化器的选择维度,来对SQL语句进行优化。
2.4.执行器
在经历了分析器,优化器后,到了SQL语句真正执行的环节了。
在开始执行时,执行器会对权限进行判断,如果没有执行权限,则返回没有权限的错误(MySQL 8.0之前,查询缓存同样会对权限进行验证)。
执行器通过调用存储引擎的接口来实现查询,修改和删除。