MYSQL执行过程和顺序详解

一、前言

1.1、说明

就MySQL在执行过程、sql执行顺序,以及一些相关关键字的注意点方面的学习分享内容。

在参考文章的基础上,会增加自己的理解、看法,希望本文章能够在您的学习中提供帮助。

如有错误的地方,欢迎指出纠错,互相学习,共同进步。

1.2、参考文章

十分感谢以下文章提供的帮助

https://www.cnblogs.com/wyq178/p/11576065.html

https://juejin.cn/post/7002604517913001997

https://blog.csdn.net/qq_48157004/article/details/128590851

https://cloud.tencent.com/developer/article/1882003

https://cloud.tencent.com/developer/article/1115019

二、MySQL的执行过程

2.1、MySQL数据库架构的两个主要层次

2.1.1、Server层
  • 这是 MySQL 的核心,负责管理客户端的连接、SQL 查询处理、权限管理、事务处理、数据字典等。
  • Server 层负责接收来自客户端的 SQL 查询,并将它们发送到存储引擎层进行处理。
  • 这一层是 MySQL 的核心逻辑,独立于任何特定的存储引擎,因此提供了对不同存储引擎的统一接口。
2.1.2、存储引擎层
  • 存储引擎层负责存储和检索数据。MySQL 支持多种存储引擎,每种引擎都有其独特的特性和适用场景。
  • 每个表可以根据需求选择不同的存储引擎,甚至在同一数据库中的不同表也可以使用不同的存储引擎。
  • 一些常见的 MySQL 存储引擎包括 InnoDB、MyISAM、MEMORY、CSV、ARCHIVE 等。

2.2、MySQL数据库架构图

架构图(具体详情分析可见2.3部分):

请添加图片描述

对存储引擎描述比较详细的一张架构图(可用于参考,本文章的侧重点不在存储引擎,不同的存储引擎内部实现不同)

请添加图片描述

比较官方的一张架构图

请添加图片描述

2.3、 各执行过程详情说明

2.3.1 连接器

1、建立和维护连接

连接器负责与客户端的通信,其使用的是半双工模式(一种通信方式,其中通信双方可以交替地发送和接收数据,但不能同时进行发送和接收)。首先是与访问的客户端建立TCP连接,服务器有专门的TCP连接池,采用长连接模式复用TCP连接,经过三次握手建立连接成功后,之后会对TCP传输过来的账号密码做身份认证、权限获取。在服务器内部,每个client连接都有自己的线程,即TCP连接都会分配给一个线程去执行后续的流程。这些线程轮流运行在某一个CPU内核(多核CPU)或者CPU中,缓存了线程,因此不需要为每个client连接单独创建和销毁线程 。

2、认证和权限获取

完成与访问客户端的TCP连接后,会对TCP传递过来的账号密码进行身份认证,如果账户和密码错误,会报错Access denied for user 'root'@'localhost' (using password: YES)

如果用户的账户和密码验证通过,会在MySQL自带的权限表中查询当前用户的权限(管理员可以通过GRANT和REVOKE语句来修改用户的权限),MySQL中存在4个控制权限的表,分别为user表,db表,tables_priv表,columns_priv表:

  • user表:存放用户账户信息以及全局级别(所有数据库)权限,决定了来自哪些主机的哪些用户可以访问数据库实例
  • db表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库
  • tables_priv表:存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表
  • columns_priv表:存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

验证授权过程(MySQL会在用户进行操作时,动态地检查其权限信息,以决定是否允许该操作)如下所示:

  • 先从user表中的Host,User,Password这3个字段中判断连接的IP、用户名、密码是否存在,存在则通过验证。
  • 通过身份认证后,进行权限分配,按照user,db,tables_priv,columns_priv的顺序进行验证。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db,tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表,取得表中的权限Y,以此类推。
  • 如果在任何一个过程中权限验证不通过,都会返回相应的错误。

并且MySQL支持多种身份认证方式,包括基于密码的认证、SSL/TLS证书认证等,其中,最常用的是基于密码的认证方式。

2.3.2 缓存

缓存主要是针对MySQL的查询语句进行的,如果是查询语句,MySQL服务器会将查询字符串作为key,查询结果作为value缓存到内存中。经过连接器,此时MySQL服务器已经获得到了SQL字符串,如果是查询语句,服务器会使用该查询字符串作为key,去缓存中获取,如果命中缓存,直接返回结果(返回前需要做权限的验证),未命中则执行后面的逻辑。并且,在匹配的缓存的过程中,查询字符串需要完全与key匹配才算命中(即空格、注释、大小写、某些系统函数)。当所取的数据的基表发生任何数据变化后,MySQL服务器会自动使对应的缓存失效。在读写比例非常高的应用系统中, 缓存对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。从MySQL 5.6的版本中已经默认关闭,5.7.20开始,不推荐使用查询缓存,并在MySQL 8.0中删除。

2.3.3 分析器

因为客户端发送过来的只是一段文本字符串,因此MySQL服务器还需要对该文本字符串进行解析,这个解析过程就是在分析器中完成的。分析器对客户端发过来的SQL语句进行分析,包括预处理与解析过程,在这个阶段会解析SQL语句的语义,并进行关键词和非关键词进行提取、解析,并创建一个内部数据结果(解析树)。具体的关键词包括不限定于以下:select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误,会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.。除了提取关键词外,还会对其中的表进行校验,如果不存在该表,同样也会报错:unknown column in field list.。通过了分析器,那么就说明客户端发送过来的文本字符串是符合SQL标准语义规则,之后MySQL服务器就要开始执行SQL语句了。

2.3.4 优化器

优化器不仅会生成SQL执行的计划,还会帮助优化SQL语句。如外连接转换为内连接、表达式简化、子查询转为连接、连接顺序、索引选择等一堆东西,优化的结果就是执行计划。MySQL会计算各个执行方法的最佳时间,最终确定一条执行的SQL交给最后的执行器。

2.3.5 执行器

开始执行SQL的时候,要先判断一下对这个表有没有相应的权限,如果没有,就会返回权限错误。如果权限校验通过后,会调用存储引擎的API,API会调用存储引擎(存储引擎API只是抽象接口,下面还有个存储引擎层,具体实现还是要看表选择的存储引擎),主要有以下存储的引擎(常用的还是myisam和innodb):

存储引擎myisaminnodbmemoryarchive
存储限制256TB64TB
事务/支持//
索引支持支持支持/
锁的粒度表锁行锁表锁行锁
数据压缩支持//支持
外键/支持//

存储引擎,之前又叫表处理器,负责对具体的数据文件进行操作,对SQL的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中。

关于binlog和redolog两个日志的说明:

binlog:

  • 作用:Binlog记录了对MySQL数据库进行的所有更改操作,例如INSERT、UPDATE、DELETE等,以二进制格式记录在磁盘上。
  • 功能:主要用于数据恢复、数据复制和数据迁移。通过分析Binlog,可以重放数据库中的更改操作,从而实现数据的恢复或者将更改操作应用到其他MySQL实例上。
  • 使用场景:常用于数据库备份、故障恢复、数据同步等场景。

redolog:

  • 作用:Redo Log记录了数据库引擎执行的每个事务的修改操作,以便在数据库发生崩溃时,可以通过重放Redo Log来恢复事务的更改。
  • 功能:主要用于保证数据库的持久性和事务的原子性,即使在发生故障时也能确保事务的完整性。
  • 使用场景:主要用于崩溃恢复和事务回滚。

三、SQL执行顺序说明

SQL并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用,SQL在执行的过程中会有不同的临时中间表,一般是按照如下顺序:

  • from
  • on
  • join
  • where
  • goup by
  • having + 聚合函数
  • select
  • order by
  • limit

在MySQL中,查询语句中子查询的执行顺序可以根据具体情况而有所不同,但一般来说,以下是一个常见的执行顺序:

  • 内部子查询:MySQL通常会首先执行子查询,生成临时结果集,然后将其用于外部查询。内部子查询是指嵌套在主查询中的子查询,通常作为主查询的一部分。
  • 关联子查询:如果子查询是一个关联子查询(即与外部查询相关联),MySQL可能会根据优化器的策略选择在何处执行子查询。有时候,MySQL可能会将关联子查询转换为连接查询来提高性能。
  • 外部查询:一旦子查询的结果可用,MySQL会将其与外部查询组合起来执行,产生最终的结果集。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/544580.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

vue中使用axios获取不到响应头Content-Disposition的解决办法

项目中,后端返回的文件流; 前端需要拿到响应头里的Content-Disposition字段的值,从中获取文件名 在控制台Headers中可以看到相关的字段和文件名,但是在axios里面却获取不到 如果想要让客户端访问到相关信息,服务器不仅要在head…

嵌入式单片机 TTL电平、232电平、485电平的区别和联系

一、简介 TTL、232和485是常见的串口通信标准,它们在电平和通信方式上有所不同, ①一般情况下TTL电平应用于单片机外设,属于MCU/CPU等片外外设; ②232/485电平应用于产品整体对外的接口,一般是片外TTL串口转232/485…

<计算机网络自顶向下> CDN

视频服务挑战 规模性异构性:不同用户有不同的能力(比如有线接入和移动用户;贷款丰富和受限用户)解决方法是:分布式的应用层面的基础设施CDN 多媒体:视频 视频是固定速度显示的一系列图像的序列&#xff…

【ubuntu20.04】安装GeographicLib

下载地址 GeographicLib: Installing GeographicLib 我们是ubuntu20.04 ,所以下载第一个 GeographicLib-2.3.tar.gz 接着跟着官方步骤安装,会出错!!!!马的 官方错误示例:tar xfpz Geographi…

YOLOV5 + 双目相机实现三维测距(新版本)

文章目录 YOLOV5 双目相机实现三维测距(新版本)1. 项目流程2. 测距原理3. 操作步骤和代码解析4. 实时检测5. 训练6. 源码下载 YOLOV5 双目相机实现三维测距(新版本) 本文主要是对此篇文章做一些改进,以及解释读者在…

微软搭建零售新媒体创意工作室大举抢占数字营销广告市场

“微软新零售创意工作室新平台利用生成式人工智能,在几秒钟内轻松定制横幅广告。零售媒体预计到2026年将成为一个价值1000亿美元的行业。” 零售媒体在过去几年中发展迅速。根据eMarketerOpens在新窗口的数据,预计到2024年,仅美国的零售媒体…

基于粒子群优化的配电网重构

一、配电网重构原理 配电网重构是指在满足配电网运行基本约束的前提下,通过改变配电网中一个或多个开关的状态对配电网中一个或多个指标进行优化。通过配电网重构,可以在不增加设备投资的情况下,充分发挥配电系统的潜力,提高系统…

【C语言】每日一题,快速提升(1)!

调整数组使奇数全部都位于偶数前面 题目: 输入一个整数数组,实现一个函数 来调整该数组中数字的顺序使得数组中所有的奇数位于数组的前半部分 所有偶数位于数组的后半部分 解题思路: 给定两个下标left和right,left放在数组的起始…

【Web】VS Code 插件及快捷键

专栏文章索引:Web 有问题可私聊:QQ:3375119339 目录 一、安装步骤 二、插件 1.Chinese (Simplified) (简体中文) 2.open in browser 3.vscode-icons 4.Live Server 5.Live Server Preview 6.翻译(英汉词典) 三、快捷键 1.缩放代码…

Vivado ILA Capture Control 模式与 Advanced Trigger的功能使用以及TSM(触发状态机)的编写

文章目录 一、前言二、ILA的基本功能使用以及局限性2.1 ILA的调用2.2 ILA波形窗口观察2.3 ILA基本功能的局限性:如何观测低频的数据? 二、Capture Control 功能介绍三、Advanced Trigger功能以及TSM编写3.1 触发状态机的写法3.2 设置Advanced Trigger3.3…

MYSQL中的ER图

1.首先学习如何设计数据,在此之前我们先学会画出我们的规划图 利用Axure RP 9 然后进行以后的操作。其实非常简单看看就会

NODE MCU (ESP8285-ESP8266)用Arduino lDE 2.3.2烧录系统后串口监控不打印问题

问题: Arduino lDE 2.3.2,集合DOIT ESP-Mx DevKit板子,烧录代码后,串口监视器 打印不出来调试数据 分析: Arduino lDE 2.3.2工具提示,不支持调试 板载flash按钮无需按下,即可烧录系统,由于烧录和调试共用串口,所以怀疑是Arduino lDE 2.3.2在烧录时设置了串口的配置…

Hello 算法10:搜索

https://www.hello-algo.com/chapter_searching/binary_search/ 二分查找法 给定一个长度为 n的数组 nums ,元素按从小到大的顺序排列,数组不包含重复元素。请查找并返回元素 target 在该数组中的索引。若数组不包含该元素,则返回 -1 。 # 首…

Ubuntu 22.04 开机自动挂载webdav - 设置开机自启脚本 - 解决坚果云webdav无写入权限

效果图: 前言: 1)亲测/etc/fstab的办法没有成功自动挂载,换成传统的rc.local可以解决; 2)rc-local.service是系统自带的一个开机自启服务,但是在 ubuntu 20.04 上,该服务默认没有开…

基于物理原理的p-GaN HEMT动态导通电阻SPICE建模

来源:Physics-Based SPICE Modeling of Dynamic ON-State Resistance of p-GaN HEMTs(TPEL 23年) 摘要 这封快报介绍了一种新型基于物理学原理的SPICE建模方法,专门针对氮化镓基p型门极高电子迁移率晶体管(p-GaN HEM…

route路由命令、ip route命令、default默认路由(0.0.0.0 )

文章目录 概述3. route语法3.1 查看路由表3.1 参数解释 3.2 添加路由记录3.2.1 添加到达单个目标主机的路由3.2.2 添加到达目标网络的路由3.2.3 添加默认路由 3.3 删除路由记录 4. ip route4.1 查看路由4.1.1 不带条件4.1.2 带条件4.1.3 字段解释4.1.3 字段解释 4.2 添加路由4.…

基于Springboot+Vue的Java项目-高校心理教育辅导系统开发实战(附演示视频+源码+LW)

大家好!我是程序员一帆,感谢您阅读本文,欢迎一键三连哦。 💞当前专栏:Java毕业设计 精彩专栏推荐👇🏻👇🏻👇🏻 🎀 Python毕业设计 &am…

发布 Chrome/Edge浏览器extension扩展到应用商店

Chrom Extension发布流程 创建和发布自定义 Chrome 应用和扩展程序:https://support.google.com/chrome/a/answer/2714278?hlzh-Hans 在 Chrome 应用商店中发布:https://developer.chrome.com/docs/webstore/publish?hlzh-cn 注册开发者帐号&#…

Eclipse+Java+Swing实现图书信息管理系统-TXT存储信息

一、系统介绍 1.开发环境 操作系统:Win10 开发工具 :Eclipse2021 JDK版本:jdk1.8 存储方式:Txt文件存储 2.技术选型 JavaSwingTxt 3.功能模块 4.工程结构 5.系统功能 1.系统登录 管理员可以登录系统 2.查看图书 管理员…

软件无线电安全之HackRF One初探

HackRF介绍 HackRF是一款开源软件无线电(SDR)平台,由Great Scott Gadgets公司推出。它具有广泛的频率覆盖范围,从1 MHz到6 GHz,支持大部分常见的无线通信频段。采用软件定义无线电技术,HackRF提供了自定义…