执行 - Mysql语句执行流程
执行流程图
MySQL 的架构共分为两层:Server 层和存储引擎层,
Server 层:负责建立连接、分析和执行SQL。主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能。
存储引擎层:负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎,不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB,从 MySQL 5.5 版本开始, InnoDB 成为了 MySQL 的默认存储引擎。
连接器
客户端需要通过连接器访问MySQL Server,连接器主要负责身份认证和权限鉴别的工作。也就是负责用户登录数据库的相关认证操作,例如:校验账户密码,权限等。在用户名密码合法的前提下,会在权限表中查询用户对应的权限,并且将该权限分配给用户。
如何查看有多少连接?
执行 show processlist 命令进行查看
其中”Command”列返回的内容中,“Sleep”表示MySQL相同中对应一个空闲连接。而“Query”表示正在查询的连接。
连接状态:
Command | 含义 |
---|---|
sleep | 线程正在等待客户端发数据 |
query | 连接线程正在执行查询 |
locked | 线程正在等待表锁的释放 |
sorting result | 线程正在对结果进行排序 |
sending data | 向请求端返回数据 |
空闲连接是否一直存在
从上图可以看出有许多空闲连接,MySQL 定义了空闲连接的最大空闲时长,由 wait_timeout 参数控制的,默认值是 8 小时(28880秒),如果空闲连接超过了这个时间,连接器就会自动将它断开。
手动断开空闲的连接,使用的是 kill connection + id 的命令
最大连接数
长连接和短连接
- 长连接是指连接成功后,客户端请求一直使用是同一个连接。
- 短连接是指每次执行完SQL请求的操作之后会断开连接,如果再有SQL请求会重新建立连接。
由于短连接会反复创建连接消耗相同资源,因此多数情况下会选择长连接。但是为了保持长连接,会占用系统内存,而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案:
- 定期断开长连接,每隔一段时间或者执行一个占用内存的大查询以后断开连接,从而释放内存,当查询的时候再重新创建连接。
- 客户端主动重置连接。MySQL 5.7 或者更高的版本,通过执行 mysql_reset_connection 来重新初始化连接。此过程不会重新建立连接,但是会释放占用的内存,将连接恢复到刚刚创立连接的状态。
查询缓存
在建立与数据库的连接以后就可以执行SQL语句了
如果 SQL 是查询语句(select 语句),MySQL 就会先去查询缓存( Query Cache )里查找缓存数据,看看之前有没有执行过这一条命令,并且将执行结果按照key-value的形式缓存在内存中了。
Key 是查询的SQL语句,Value 是查询的结果。如果缓存 Key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,执行完SQL仍旧会把结果缓存起来,方便下一次调用。
Mysql的机制是只要一个表有更新操作,那么这个表的查询缓存就会被清空。如果张表不断地被使用(更新、查询),那么查询缓存会频繁地失效,获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表。例如:系统配置、或者修改不频繁的表。
缓存的淘汰策略是先进先出,适用于查询远大于修改的情况下, 否则建议使用Redis或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0 版本后删除了查询缓存的功能,官方认为该功能应用场景较少,所以将其删除。
这里说的查询缓存是 server 层的,与Innodb 存储引擎中的 buffer pool的缓存无关。也就是 MySQL 8.0 版本移除的是 server 层的查询缓存,
解析 SQL
在正式执行 SQL 查询语句之前, MySQL 会先对 SQL 语句做解析,这个工作交由「解析器」来完成。
解析器会做两件事情
- 词法分析。MySQL 会根据你输入的字符串识别出关键字出来,构建出 SQL 语法树,这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
- 语法分析。根据词法分析的结果,语法解析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。
如果输入的 SQL 语句语法不对,就会在解析器这个阶段报错。比如把 from 写成了 form,这时 MySQL 解析器就会给报错.
执行SQL语句
每条SELECT 查询语句流程可以分为三个阶段:
- prepare 阶段,也就是预处理阶段;
- optimize 阶段,也就是优化阶段;
- execute 阶段,也就是执行阶段;
预处理器
预处理器的作用:
- 检查 SQL 查询语句中的表或者字段是否存在;
- 将 select * 中的 * 符号,扩展为表上的所有列;
优化器
在优化器中会确定sql语句的执行路径,比如是根据全表检索 还是根据索引检索等,使用索引检索那使用哪个索引
在查询优化器中,分为逻辑查询优化和物理查询优化两个大块
- 物理查询优化是通过索引和表连接方式等技术进行优化
- 逻辑查询优化是通过sql等价变换提升查询效率,直白点说就是换一种执行效率高的写法
执行器
当解析器生成查询计划,并且经过优化器以后,就到了执行器。执行器会选择执行计划开始执行,但在执行之前会校验请求用户是否拥有查询的权限,如果没有权限,就会返回错误信息,否则将会去调用MySQL引擎层的接口,执行对应的SQL语句并且返回结果。
查询语句执行流程
查询语句的执行流程如下:权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子,查询语句如下:
select * from user where id > 1 and name = 'seven';
- 首先检查权限,没有权限则返回错误;
- MySQL8.0以前会查询缓存,缓存命中则直接返回,没有则执行下一步;
- 词法分析和语法分析。提取表名、查询条件,检查语法是否有错误;
- 两种执行方案,先查 id > 1 还是 name = 'seven',优化器根据自己的优化算法选择执行效率最好的方案;
- 校验权限,有权限就调用数据库引擎接口,返回引擎的执行结果。
更新语句执行过程
更新语句执行流程如下:分析器、权限校验、执行器、引擎、redo log(prepare状态)、binlog、redo log(commit状态)
举个例子,更新语句如下:
update user set name = 'seven' where id = 1;
- 先查询到 id 为1的记录,有缓存会使用缓存。
- 拿到查询结果,将 name 更新为seven,然后调用引擎接口,写入更新数据,innodb 引擎将数据保存在内存中,同时记录redo log,此时redo log进入 prepare状态。
- 执行器收到通知后记录binlog,然后调用引擎接口,提交redo log为commit状态。更新完成。
为什么记录完redo log,不直接提交,而是先进入prepare状态?
这里涉及到两阶段提交问题。假设先写redo log直接提交,然后写binlog,写完redo log后,机器挂了,binlog日志没有被写入,那么机器重启后,这台机器会通过redo log恢复数据,但是这个时候binlog并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
总结
- 连接器:建立连接,管理连接、校验用户身份;
- 查询缓存:查询语句如果命中查询缓存则直接返回,否则继续往下执行。MySQL 8.0 已删除该模块;
- 解析 SQL,通过解析器对 SQL 查询语句进行词法分析、语法分析,然后构建语法树,方便后续模块读取表名、字段、语句类型;
- 执行 SQL:执行 SQL 共有三个阶段:
- 预处理阶段:检查表或字段是否存在;将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段:基于查询成本的考虑, 选择查询成本最小的执行计划(选择使用哪个索引);
- 执行阶段:根据执行计划执行 SQL 查询语句,从存储引擎读取记录,返回给客户端;