从Mysql架构看一条查询sql的执行过程

alt

1. 通信协议

我们的程序或者工具要操作数据库,第一步要做什么事情? 跟数据库建立连接。

首先,MySQL必须要运行一个服务,监听默认的3306端口。在我们开发系统跟第三方对接的时候,必须要弄清楚的有两件事。

  • 第一个就是通信协议,比如我们是用HTTP还是WebService还是TCP?
  • 第二个是消息格式,比如我们用XML格式,还是JSON格式,还是定长格式?报文头长度多少,包含什么内容,每个字段的详细含义。

MySQL是支持多种通信协议的,可以使用同步/异步的方式,支持长连接/短连接。我们分别来看:

同步通信

同步通信依: 赖于被调用方,受限于被调用方的性能。也就是说,应用操作数据库,线程会阻塞,等待数据库的返回。一般只能做到一对一,很难做到一对多的通信。

异步通信

异步可以避免应用阻塞等待,但是不能节省SQL执行的时间。

如果异步存在并发,每一个SQL的执行都要单独建立一个连接,避免数据混乱。但是这样会给服务端带来巨大的压力(一个连接就会创建一个线程,线程间切换会占用大量CPU资源)。另外异步通信还带来了编码的复杂度,所以一般不建议使用。如果要异步,必须使用连接池,排队从连接池获取连接而不是创建新连接。

长连接与短连接

MySQL既支持短连接,也支持长连接。短连接就是操作完毕以后,马上close掉。长连接可以保持打开,减少服务端创建和释放连接的消耗,后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。

保持长连接会消耗内存。长时间不活动的连接,MySQL服务器会断开。

showglobalvariableslike'wait_timeout';--非交互式超时时间,如JDBC程序
showglobalvariableslike'interactive_timeout';--交互式超时时间,如数据库工具

默认都是28800秒,8小时。

可以用showstatus命令:showglobalstatuslike'Thread%';

Threads_cached:缓存中的线程连接数。
Threads_connected:当前打开的连接数。
Threads_created:为处理连接创建的线程数。
Threads_running:非睡眠状态的连接数,通常指并发连接数。

MySQL支持哪些通信协议呢?

UnixSocket

比如我们在Linux服务器上,如果没有指定-h参数,它就用socket方式登录(省略了-S/var/lib/mysql/mysql.sock)。

它不用通过网络协议,也可以连接到MySQL的服务器,它需要用到服务器上的一个物理文件(/var/lib/mysql/mysql.sock)。

select @@socket;
TCP/IP 协议

我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的,比如 mysql-connector-java-x.x.xx.jar

alt

2. 语法解析和预处理

为什么一条 SQL 语句能够被识别呢?假如我随便执行一个字符串 penyuyan,服务器报了一个 1064 的错, 它是怎么知道我输入的内容是错误的?

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near 'penyuyan' at line 1

这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。 这一步主要做的事情是对语句基于 SQL 语法进行词法和语法分析和语义的解析。

词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。 比如一个简单的 SQL 语句:

select name from user where id = 1;

它会打碎成 8 个符号,每个符号是什么类型,从哪里开始到哪里结束。

语法解析

语法分析会对 SQL 做一些语法检查,比如单引号有没有闭合,然后根据 MySQL 定义的语法规则,根据 SQL 语句生成一个数据结构。这个数据结构我 们把它叫做解析树(select_lex)

alt 任何数据库的中间件,比如Mycat,Sharding-JDBC(用到了DruidParser),都必须要有词法和语法分析功能,在市面上也有很多的开源的词法解析的工具(比如LEX,Yacc).

预处理器

如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?是在数据库的执行层还是解析器?比如:

select * from xxx;

解析器可以分析语法,但是它怎么知道数据库里面有什么表,表里面有什么字段呢?实际上还是在解析的时候报错,解析SQL的环节里面有个预处理器。 它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树。

3.查询优化(QueryOptimizer)与查询执行计划

得到解析树之后,是不是执行SQL语句了呢?这里我们有一个问题,一条SQL语句是不是只有一种执行方式?或者说数据库最终执行的SQL是不是就是我们发送的SQL?

这个答案是否定的。一条SQL语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?

这个就是MySQL的查询优化器的模块(Optimizer)。 查询优化器的目的就是根据解析树生成不同的执行计划(ExecutionPlan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。

可以使用这个命令查看查询的开销:

showstatus like 'Last_query_cost';

优化器可以做什么?

举两个简单的例子:

1、当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。 2、有多个索引可以使用的时候,选择哪个索引。

实际上,对于每一种数据库来说,优化器的模块都是必不可少的,他们通过复杂的算法实现尽可能优化查询效率的目标。 如果对于优化器的细节感兴趣,可以看看《数据库查询优化器的艺术-原理解析与SQL 性能优化》。

alt 但是优化器也不是万能的,并不是再垃圾的SQL语句都能自动优化,也不是每次都能选择到最优的执行计划,大家在编写SQL语句的时候还是要注意。

优化器是怎么得到执行计划的?

首先我们要启用优化器的追踪(默认是关闭的):

SHOWVARIABLES LIKE 'optimizer_trace';
set optimizer_trace ='enabled=on';

注意开启这开关是会消耗性能的,因为它要把优化分析的结果写到表里面,所以不要轻易开启,或者查看完之后关闭它(改成off

接着我们执行一个SQL语句,优化器会生成执行计划:

select t.tcid from teacher t, teacher_contact tc wheret.tcid=tc.tcid;

这个时候优化器分析的过程已经记录到系统表里面了,我们可以查询:

select * from information_schema.optimizer_trace\G

它是一个JSON类型的数据,主要分成三部分,准备阶段、优化阶段和执行阶段。

alt
expanded_query是优化后的SQL语句。
considered_execution_plans里面列出了所有的执行计划。

优化器得到的结果

优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。当然,这个执行计划是不是一定是最优的执行计划呢?不一定,因为MySQL也有可能覆盖不到所有的执行计划。

我们怎么查看MySQL的执行计划呢?比如多张表关联查询,先查询哪张表?在执行查询的时候可能用到哪些索引,实际上用到了什么索引?

MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

EXPLAIN select name from user where id=1;

*注意Explain的结果也不一定最终执行的方式。

4.存储引擎

得到执行计划以后,SQL语句是不是终于可以执行了? 问题又来了:

1、从逻辑的角度来说,我们的数据是放在哪里的,或者说放在一个什么结构里面?
2、执行计划在哪里执行?是谁去执行?

存储引擎基本介绍

我们先回答第一个问题:在关系型数据库里面,数据是放在什么结构里面的?

放在表Table里面的 我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。

查看存储引擎

比如我们数据库里面已经存在的表,我们怎么查看它们的存储引擎呢?

show table status from `xxx`;
alt

在MySQL里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且,创建表之后还可以修改存储引擎。

我们说一张表使用的存储引擎决定我们存储数据的结构,那在服务器上它们是怎么存储的呢?我们先要找到数据库存放数据的路径:

showvariableslike'datadir';

默认情况下,每个数据库有一个自己文件夹,任何一个存储引擎都有一个frm文件,这个是表结构定义文件。 alt 不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb是1个,memory没有,myisam是两个。

这些存储引擎的差别在哪呢?

存储引擎比较

MyISAMInnoDB是我们用得最多的两个存储引擎,在MySQL5.5版本之前,默认的存储引擎是MyISAM,它是MySQL自带的。我们创建表的时候不指定存储引擎,它就会使用MyISAM作为存储引擎.

MyISAM的前身是ISAM(IndexedSequentialAccessMethod:利用索引,顺序存取数据的方法).

5.5版本之后默认的存储引擎改成了InnoDB,它是第三方公司为MySQL开发的。为什么要改呢?最主要的原因还是InnoDB支持事务,支持行级别的锁,对于业务一致性要求高的场景来说更适合。

MyISAM

应用范围比较小。表级锁定限制了读/写的性能,因此在Web和数据仓库配置中,它通常用于只读或以读为主的工作。 特点:

  • 支持表级别的锁(插入和更新会锁表)。不支持事务。
  • 拥有较高的插入(insert)和查询(select)速度。
  • 存储了表的行数(count速度更快)。
InnoDB

mysql5.7中的默认存储引擎。InnoDB是一个事务安全(与ACID兼容)的MySQL 存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级为更粗粒度的锁)和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性, InnoDB还支持外键引用完整性约束。 特点:

  • 支持事务,支持外键,因此数据的完整性、一致性更高。
  • 支持行级别的锁和表级别的锁。
  • 支持读写并发,写不阻塞读(MVCC)。
  • 特殊的索引存放方式,可以减少IO,提升查询效率。
  • 适合:经常更新的表,存在并发读写或者有事务处理的业务系统.
Memory

将所有数据存储在RAM中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少;InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中,而ndbcluster为大型分布式数据集提供了快速的键值查找。 特点:

  • 把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失。只适合做临时表。
CSV

它的表实际上是带有逗号分隔值的文本文件。csv表允许以csv格式导入或转储数据,以便与读写相同格式的脚本和应用程序交换数据。因为csv表没有索引,所以通常在正常操作期间将数据保存在innodb表中,并且只在导入或导出阶段使用csv表。 特点: 特点:不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之间导入导出。

Archive

这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。 特点:

  • 不支持索引,不支持updatedelete。

如何选择存储引擎?

  • 如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。
  • 如果数据查询多更新少,对查询性能要求比较高,可以选择MyISAM。
  • 如果需要一个用于查询的临时表,可以选择Memory。

5. 执行引擎

存储引擎分析完了,它是我们存储数据的形式,继续第二个问题,是谁使用执行计划去操作存储引擎呢?

这就是我们的执行引擎,它利用存储引擎提供的相应的API来完成操作。

为什么我们修改了表的存储引擎,操作方式不需要做任何改变?因为不同功能的存储引擎实现的API是相同的。

最后把数据返回给客户端,即使没有结果也要返回。

本文由 mdnice 多平台发布

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

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

相关文章

nodejs+vue人脸识别考勤管理系统的设计与实现-计算机毕业设计

根据分析,本系统主要有3个角色:管理员、用户、考勤系统。 (1)管理员:管理员信息的添加、删除、修改和查询,用户信息添加、删除、修改和查询。 (2)用户:用户的注册和登录&…

面试准备中........

一、Linux 计算机网络相关: 1.OSI七层模型 应用层 :给用户提供操作界面 表示层:数据的表示:将字符转化为2进制或将2进制转化为字符。加密:对称加密和非对称加密,ssh协议。压缩:将文件压缩。…

Android笔记(九):Compose组件的状态(一)

在使用Compose定义UI界面时,可以发现界面的变换往往与Compose组件内部的状态相关,当状态值发生变化时,Compose构成的可组合的界面也会刷新发生相应的变化。将在本笔记中将对可组合项的状态的定义、状态提升、状态丢失和状态的保存进行简单介绍…

开源3D激光(视觉)SLAM算法汇总(持续更新)

目录 一、Cartographer 二、hdl_graph_slam 三、LOAM 四、LeGO-LOAM 五、LIO-SAM 六、S-LOAM 七、M-LOAM 八、livox-loam 九、Livox-Mapping 十、LIO-Livox 十一、FAST-LIO2 十二、LVI-SAM 十三、FAST-Livo 十四、R3LIVE 十五、ImMesh 十六、Point-LIO 一、Cartographer Cartog…

NSS [鹤城杯 2021]EasyP

NSS [鹤城杯 2021]EasyP 直接给了源码 <?php include utils.php;if (isset($_POST[guess])) {$guess (string) $_POST[guess];if ($guess $secret) {$message Congratulations! The flag is: . $flag;} else {$message Wrong. Try Again;} }if (preg_match(/utils\.p…

[ACTF2020 新生赛]Include

【解题思路】 1.打开链接 发现好东西&#xff0c;进一步分析。 2.分析页面 发现网页得到一个GET请求-->?fileflag.php 可以推断&#xff0c;要解答该题目需要获取 flag.php 的源代码. 将flag.php文件进行base64编码&#xff08;将网页源代码转换为Base64编码&#xff…

电脑QQ如何录制视频文件?

听说QQ可以录制视频&#xff0c;还很方便&#xff0c;请问该如何录制呢&#xff1f;是需要先打开QQ才可以录制吗&#xff1f;还是可以直接使用快捷键进行录制呢&#xff1f;录制的质量又如何呢&#xff1f; 不要着急&#xff0c;既然都打开这篇文章看了&#xff0c;那小编今天…

【软考】系统集成项目管理工程师(十)项目质量管理【3分】

一、质量概念 1、定义 国际&#xff1a;反应实体满足主体明确和隐含需求的能力的特性总和 国内&#xff1a;一组固有特性满足要求的程度 2、质量与等级 质量&#xff1a;作为实现的性能或成果&#xff0c;是一系列内在特性满足要求的程度ISO9000 等级&#xff1a;作为设计意…

elementUI el-collapse 自定义折叠面板icon 和 样式 或文字展开收起

: :v-deep{.el-collapse-item__arrow {width: 40px;}.el-icon-arrow-right:before {content: "展开";font-size: 15px;font-family: heiti;color: #2295ff;font-weight: bold;}.el-collapse-item__arrow.is-active {transform: none;}.el-collapse-item__arrow.is-a…

【计算机毕设经典案例】基于微信小程序的图书管理系统

前言&#xff1a;我是IT源码社&#xff0c;从事计算机开发行业数年&#xff0c;专注Java领域&#xff0c;专业提供程序设计开发、源码分享、技术指导讲解、定制和毕业设计服务 &#x1f449;IT源码社-SpringBoot优质案例推荐&#x1f448; &#x1f449;IT源码社-小程序优质案例…

Azure - 自动化机器学习AutoML Azure使用详解

目录 一、AutoML是如何工作的&#xff1f;二、何时考虑AutoML&#xff1f;三、AutoML助力训练与集成过程四、实战案例五、总结 自动化机器学习&#xff0c;简称为AutoML&#xff0c;旨在将机器学习模型的开发中繁琐且重复的任务自动化。这使得数据科学家、分析师以及开发人员能…

Java中的volatile关键字

volatile是什么&#xff1f; "volatile"是一个关键字&#xff0c;用于修饰变量。它的作用是告诉编译器该变量可能会在意料之外的时候被修改&#xff0c;因此编译器在对该变量进行优化时需要特别小心。 具体来说&#xff0c;当一个变量被声明为"volatile"…

2023年上半年上午易错题(软件设计师考试)

计算机中&#xff0c;系统总线用于 &#xff08;1&#xff09; 连接。 A. 接口和外设 B. 运算器、控制器和寄存器 C. CPU、主存及外设部件 D. DMA控制器和中断控制器 在由高速缓存、主存和硬盘构成的三级存储体系中&#xff0c;CPU执行指令时需要读取数据&#xff0c;那…

C++数据结构X篇_23_快速排序(最快、不稳定的排序)

文章参考十大经典排序算法-快速排序算法详解进行整理补充。快速排序是最快的排序方法。 排序思路&#xff1a;分治法-挖坑填数&#xff1a;大问题分解为各个小问题&#xff0c;对小问题求解&#xff0c;使得大问题得以解决 文章目录 1. 什么是快速排序1.1 概念1.2 算法原理1.3 …

Python 框架学习 Django篇 (六) 数据表关联、ORM关联

在后端服务器开发中&#xff0c;特别是前后端分离的架构中数据库是非常重要的&#xff0c;后端主要就是负责管理数据&#xff0c;而我们经常使用的mysql、oracle 都是关系型数据库&#xff0c;什么是关系型数据库&#xff1f;就是建立在关系模型基础上的数据库&#xff0c;而最…

android studio启动Task配置

Android studio 高版本默认不开启Task配置&#xff0c;需要自己手动开启 1.低版本配置路径&#xff1a;&#xff08;复制他人图片&#xff09; 2.高版本路径&#xff1a;添加下图勾选配置即可 3.gradle task 3.1 初识task gradle中所有的构建工作都是由task完成的,它帮我们处…

Ubuntu中查看电脑有多少个核——lscpu

1. 使用lscpu命令: 打开终端并输入以下命令: lscpu你会看到与CPU相关的详细信息。查找"CPU(s)"这一行来看总的核心数。另外&#xff0c;“Core(s) per socket”表示每个插槽或每个物理CPU的核数&#xff0c;“Socket(s)”表示物理CPU的数量。将这两个值相乘即得到总…

重要环节不可忽视,CSS性能优化引领用户体验!

&#x1f3ac; 江城开朗的豌豆&#xff1a;个人主页 &#x1f525; 个人专栏 :《 VUE 》 《 javaScript 》 &#x1f4dd; 个人网站 :《 江城开朗的豌豆&#x1fadb; 》 ⛺️ 生活的理想&#xff0c;就是为了理想的生活 ! 目录 ⭐ 专栏简介 &#x1f4d8; 文章引言 一、前…

Java 浅拷贝会带来的问题

Java 浅拷贝会带来的问题 一&#xff0c;常见问题 Java 中的浅拷贝是指在对象拷贝时&#xff0c;只复制对象的引用&#xff0c;而不是对象本身。这意味着浅拷贝会导致多个对象共享同一块内存空间&#xff0c;当一个对象修改共享内存时&#xff0c;其他对象也会受到影响。 下…

ArcGIS笔记13_利用ArcGIS制作岸线与水深地形数据?建立水动力模型之前的数据收集与处理?

本文目录 前言Step 1 岸线数据Step 2 水深地形数据Step 3 其他数据及资料 前言 在利用MIKE建立水动力模型&#xff08;详见【MIKE水动力笔记】系列&#xff09;之前&#xff0c;需要收集、处理和制作诸多数据和资料&#xff0c;主要有岸线数据、水深地形数据、开边界潮位驱动数…