MySQL - 一条SQL在MySQL中是如何被执行的?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL - 一条SQL在MySQL中是如何被执行的?

1. 前言

MySQL 中针对表的操作可以分为增、删、改、查四种操作,也就是我们所说的 CRUD 大法,根据类型分为DML(增删改)和DQL(查),不管是 DML 和 DQL 都要经过连接器、查询缓存、分析器、优化器、执行器调用存储引擎的API执行最优路径。前四个阶段流程都是一样的,接下来我们一起学习执行器在存储引擎上是如何进行查询和更新(增删改查)的。

PS:查询缓存在 MySQL5.7.20 版本已过时,在 MySQL8.0 版本中被移除,这里不再单独介绍查询缓存这一流程;连接器、查询缓存、分析器、优化器、执行器的具体作用见:一文了解MySQL的基础架构及各个组件的作用。

2. MySQL是如何执行查询语句的?

如下SQL:实现查询名字为 “javaBoy001” 和年龄为 18 的用户

select id, name, sex, age from user where name = 'javaBoy001' and age = 18;

2.1 连接器

先检查该语句是否有权限,如果没有权限,直接返回错误信息。

2.2 分析器

分析器会通过词法分析,提取 SQL 语句的关键字,比如提取上面 SQL 语句的 “select”、“from”、"where"等,并可以知道这是一条查询语句,提取查询的表名为 user,提取查询的条件为 name = ‘javaBoy001’ and age = 18。

然后根据词法分析的结果,语法分析器会根据语法规则,判断你输入的SQL语句是否满足MySQL语法,没问题的执行下一步。

2.3 优化器

MySQL判断出了一条SQL语句要做什么之后,对其进行各种优化,包括重写查询语句、选择合适的索引、表的读取顺序等确定执行方案。上面的 SQL 语句存在两种执行方案:

方案1. 先查询名字为javaBoy001的所有用户,再查询年龄为18的用户;

方案2. 先查询年龄为18的用户,再查询名字为javaBoy001的所有用户;

因此,优化器需根据自己的优化算法选择自己认为执行效率最高的一个方案(优化器认为不一定是最好)。

2.4 执行器

语句经过优化后,就要进入执行阶段,开始执行的时候,要先判断权限,如果没有,就返回没有权限的错误。如果有权限,就调用存储引擎的API,返回查询结果。

3. MySQL是如何执行更新语句的?

如下SQL:实现更新名字为 “javaBoy001” 和年龄为 18 用户的手机号为 “15211111111”

update user set phone = '15211111111' where name = 'javaBoy001' and age = 18;

3.1 连接器

先检查该语句是否有权限,如果没有权限,直接返回错误信息。

3.2 分析器

分析器会通过词法分析和语法分析知道这是一条更新语句。

3.3 优化器

MySQL判断出了一条SQL语句要做什么之后,对其进行各种优化,选择合适的索引等。

3.4 执行器

语句经过优化后,就要进入执行阶段,开始执行的时候,要先判断权限,如果没有,就返回没有权限的错误。如果有权限,就调用存储引擎的API操作数据。

3.5 存储引擎更新数据

  1. InnoDB 存储引擎有一个缓冲池(Buffer Pool),查询时缓冲池里有数据就不去读取磁盘,没有就会把数据直接从磁盘里加载到缓冲池(Buffer Pool),同时加独占锁
  2. 写入数据的旧值(原有的值)到 undo_log,实现事务的原子性,提供回滚操作。
  3. 更新 Buffer Pool 中的缓存数据为新数据,此时这个新值为脏数据,因为磁盘为旧值。
  4. 为了防止 MySQL 服务器宕机,Buffer Pool 内存中的数据丢失,把 Buffer Pool 更新新值的操作写入 Redo log Buffer,这时的 redo_log 还仅仅停留在内存缓冲里。

如果还没提交事务,如果 MySQL 宕机,必然会导致 Buffer Pool 中的数据丢失,同时写入 Redo Log Buffer 中的redo_log 也会丢失。

而此时丢失的数据其实无关紧要,因为事务没有提交,则代表此次更新操作没有成功,MySQL 宕机导致内存里的数据丢失,但磁盘上的数据还是原来的值,重启 MySQL 后,数据并没有改变。

  1. 如果想要提交一个事务,此时就会根据一定的策略把 redo_log 从 redo log buffer 里刷入到磁盘,这个策略是根据 innodb_flush_log_at_trx_commit 来配置的:

(1)值为0:提交事务时,不会把 redo log buffer 里的数据刷入磁盘,MySQL 宕机,内存中的数据和 redo_log 都会丢失。

(2)值为1:提交事务时,一定会把 redo log buffer 从内存刷入到磁盘,MySQL 宕机,可以通过 redo_log 进行数据恢复。

(3)值为2:提交事务时,把 redo_log 写入磁盘文件对应的 os cache 缓存里,每隔1秒后才会把 os cache 里的数据写入到磁盘文件里。MySQL 宕机,会丢失 1s 内的更新数据。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
1月前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
8天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
8天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
29天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
28天前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
179 11
|
14天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
29天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
14天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
52 3
|
20天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。

推荐镜像

更多