MySQL实战45讲-第1-2讲-一条SQL查询语句是如何执行的？ 一条SQL更新语句是如何执行的

大体来说，MySQL可以分为Server层和存储引擎层两部分
Server层：Server层包括连接器、查询缓存、分析器、优化器、执行器等。以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层：负责数据的存储和提取。其架构模式是插件式的，最常用的存储引擎是InnoDB
在这里插入图片描述

一条SQL查询语句是如何执行的？

使用一条查询语句走完执行过程

mysql> select * from T where ID=10；

连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接

数据库的长连接与短链接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
尽量使用长连接，但是全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快，这是因为
MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM）
解决方案：

  1. 定期断开长连接。
  2. 通过执行mysql_reset_connection来重新初始化连接资源

查询缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。
但是大多数情况下不建议使用查询缓存
**理由：**查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。除非你的业务就是有一张静态表，可以用查询缓存
可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定

mysql> select SQL_CACHE * from T where ID=10；

！MySQL 8.0版本直接将查询缓存的整块功能删掉了

分析器

如果没有命中查询缓存，就要开始真正执行语句了。
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。以mysql> select * from T where ID=10；为例。MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。
接下来就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了

注：表索引（Table Index）是数据库中用于提高查询速度的一种数据结构
类型：常见的索引类型包括主键索引、唯一索引、复合索引等。

主键索引：保证表中每行数据的唯一性。
唯一索引：确保索引列的所有值都是唯一的。CREATE UNIQUE INDEX
复合索引：包含两个或更多列。
语法：

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: 用于创建普通索引的关键字。 index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • table_name: 指定要在哪个表上创建索引。 (column1, column2, …):
  • 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。 ASC和DESC（可选）:
  • 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

数据结构： 索引通常使用特定的数据结构来存储，例如B树（B-Tree）和B+树（B+Tree）。这些数据结构优化了数据的检索速度，而不是顺序查找

执行器

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有
权限的错误
如果没有错误，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

  1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则
    将这行存在结果集中；
  2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
    至此，这个语句就执行完成了。

一条SQL更新语句是如何执行的？

更新语句示例：

mysql> update T set c=c+1 where ID=2；

执行语句前要先连接数据库，这是连接器的工作。
前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会
把表T上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。
接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。
然后，执行器负责具体执行，找到这一行，然后更新。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主
角：redo log（重做日志）和 binlog（归档日志）。

redo log（参考《孔乙己》这篇文章酒店掌柜的粉板和账本）

在MySQL里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题提出WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账
本。
具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里
面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作
记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎
么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把
这些记录从粉板上擦掉，为记新账腾出空间。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
在这里插入图片描述
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。
checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文
件。write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果write pos
追上checkpoint，表示“粉板”满了

crash-safe

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个
能力称为crash-safe。要理解crash-safe这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

binlog

一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）
这两种日志有以下三点不同。

  1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的
    是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件
    写到一定大小后会切换到下一个，并不会覆盖以前的日志。

这时，我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。

  1. 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一
    行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然
    后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行
    数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处
    于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更
    新完成。
    在这里插入图片描述

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致
从**怎样让数据库恢复到半个月内任意一秒的状态？**说起
如果数据库半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数
据，那你可以这么做：
首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备
份恢复到临时库；
然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时
刻。
这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢
复到线上库去。
那么为什么需要“两阶段提交”？
反证法：
由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写
binlog，或者采用反过来的顺序。

mysql> update T set c=c+1 where ID=2；

假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash
1.先写redo log后写binlog。
假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

  1. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日
    志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
    可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的
    状态不一致    。

结语：
redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，
表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证
MySQL异常重启之后数据不丢失。
sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建
议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

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

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

相关文章

添加cpack install功能

添加cpack install功能

修改最外层的CMakeLists.txt, 添加几行代码： # If GNUInstallDirs is not included, CMAKE_INSTALL_BINDIR is empty. include(GNUInstallDirs)# it must go before project in order to work set(CMAKE_INSTALL_PREFIX "${PROJECT_SOURCE_DIR}" CACHE …
阅读更多...
记录一下快速上手Springboot登录注册项目

记录一下快速上手Springboot登录注册项目

本教程需要安装以下工具，如果不清楚怎么安装的可以看下我的这篇文章 链接: https://blog.csdn.net/qq_30627241/article/details/134804675 管理工具： maven IDE： IDEA 数据库： MySQL 测试工具： Postman 打开IDE…
阅读更多...
若依角色与权限字符串

若依角色与权限字符串

文章目录 一、简介1.基于权限字段串2.基于角色 二、若依的权限控制1.介绍2.实践 一、简介 基于权限字段串和基于角色的访问控制是两种不同的权限管理模型，它们各自有其优点和应用场景。下面是这两种模型的基本概念： 1.基于权限字段串 基于权限字段串&…
阅读更多...
产品成本收集器流程演示

产品成本收集器流程演示

感谢大佬的文章，我只是一个翻译搬运工，原文地址：产品成本收集器 概述 SAP 令人兴奋的部分之一是它在不同操作模块之间的集成程度。使用产品成本收集器来跟踪生产就是一个很好的例子。在本博客中，我计划遵循产品成本收集器流程&a…
阅读更多...
Unity中C#如何访问并修改Shader材质

Unity中C#如何访问并修改Shader材质

文章目录 前言一、我们用点击按钮来改变Shader传入的颜色值1、在渲染GUI时，绘制一个按钮2、我们使用一个公共的成员变量存储需要进行修改的游戏对象3、最后给绘制的按钮点击增加逻辑即可 二、测试使用的代码1、Shader代码：2、C#脚本 前言 我们写好Shade…
阅读更多...
揭秘C语言结构体：通往内存对齐的视觉之旅

揭秘C语言结构体：通往内存对齐的视觉之旅

揭秘C语言结构体：通往内存对齐的视觉之旅 引言 在C语言的编程旅程中，结构体（structs）是一个关键而强大的概念。结构体不仅允许我们组织和存储不同类型的数据，而且通过深入了解内存对齐，我们可以更好地优化…
阅读更多...
[b01lers2020]Life on Mars 一个接口的sql schema.schemate表

[b01lers2020]Life on Mars 一个接口的sql schema.schemate表

这里还是很简单的 啥也没有 然后抓包看看 发现传递参数 直接尝试sql 然后如果正确就会返回值 否则 返回1 chryse_planitia union select database(),version() 发现回显 直接开始注入 chryse_planitia union select database(),version()chryse_planitia union select data…
阅读更多...
在UniApp中使用uni.makePhoneCall方法调起电话拨打功能

在UniApp中使用uni.makePhoneCall方法调起电话拨打功能

目录 1.在manifest.json文件中添加权限 2. 组件中如何定义 3.如何授权 4.相关知识点总结 1.在manifest.json文件中添加权限 {"permissions": {"makePhoneCall": {"desc": "用于拨打电话"}} }2. 组件中如何定义 <template>…
阅读更多...
【论文合集】在非欧空间中的图嵌入方法（Graph Embedding in Non-Euclidean Space）

【论文合集】在非欧空间中的图嵌入方法（Graph Embedding in Non-Euclidean Space）

文章目录 1. Hyperbolic Models1.1 Hyperbolic Graph Attention Network1.2 Poincar Embeddings for Learning Hierarchical Representations.1.3 Learning Continuous Hierarchies in the Lorentz Model of Hyperbolic Geometry1.4 Hyperbolic Graph Convolutional Neural Net…
阅读更多...
推荐一个FL Studio最适配的midi键盘?

推荐一个FL Studio最适配的midi键盘?

Hello大家好&#xff01;好消息&#xff01;好消息&#xff01;特大好消息&#xff01; 水果党们&#xff01;终于有属于自己的专用MIDI键盘啦&#xff01; 万众期待的Novation FLKEY系列 正式出炉&#xff01; 做编曲和音乐制作的朋友们&#xff0c;对水果软件FLSTUDIO应该…
阅读更多...
CopyOnWriteArraySet怎么用

CopyOnWriteArraySet怎么用

简介 CopyOnWriteArraySet是一个线程安全的无序集合&#xff0c;它基于“写时复制”的思想实现。它继承自AbstractSet&#xff0c;可以将其理解成线程安全的HashSet。 CopyOnWriteArraySet在读取操作比较频繁、写入操作相对较少的情况下可以提高程序的性能和可靠性。它的线程…
阅读更多...
Rook-ceph(1.12.9最新版)

Rook-ceph(1.12.9最新版)

官网的步骤 git clone --single-branch --branch v1.12.9 https://github.com/rook/rook.git cd rook/deploy/examples kubectl create -f crds.yaml -f common.yaml -f operator.yaml kubectl create -f cluster.yaml整理后的已经替换好的国内镜像的 git clone https://gite…
阅读更多...
C++——内部类

C++——内部类

class A { public:class B//内部类{private:int _b;}; private:int _a; }; int main() {cout << sizeof(A) << endl;return 0; } 概念及特征&#xff1a; 如果一个类定义在另一个类的内部&#xff0c;这个类就叫内部类。注意此时这个内部类是一个独立的类&#x…
阅读更多...
前缀和 LeetCode1423. 可获得的最大点数

前缀和 LeetCode1423. 可获得的最大点数

几张卡牌 排成一行&#xff0c;每张卡牌都有一个对应的点数。点数由整数数组 cardPoints 给出。 每次行动&#xff0c;你可以从行的开头或者末尾拿一张卡牌&#xff0c;最终你必须正好拿 k 张卡牌。 你的点数就是你拿到手中的所有卡牌的点数之和。 给你一个整数数组 cardPoi…
阅读更多...
图的广度优先搜索(数据结构实训)

图的广度优先搜索(数据结构实训)

题目&#xff1a; 图的广度优先搜索 描述&#xff1a; 图的广度优先搜索类似于树的按层次遍历&#xff0c;即从某个结点开始&#xff0c;先访问该结点&#xff0c;然后访问该结点的所有邻接点&#xff0c;再依次访问各邻接点的邻接点。如此进行下去&#xff0c;直到所有的结点都…
阅读更多...
Python实现的二叉树的先序、中序、后序遍历示例

Python实现的二叉树的先序、中序、后序遍历示例

一、先序、中序、后序遍历的次序&#xff1a; 创建好一棵二叉树后&#xff0c;可以按照一定的顺序对树中所有的元素进行遍历。按照先左后右&#xff0c;树 的遍历方法有三种&#xff1a;先序遍历、中序遍历和后序遍历。 其中&#xff0c;先序遍历的次序是&#xff1a;如果二叉…
阅读更多...
Javascript编程进阶 – 预定义函数

Javascript编程进阶 – 预定义函数

Javascript编程进阶 – 预定义函数 JavaScript Programming Advanced – Predefined Functions By JacksonML JavaScript引擎中包含了一组built-in functions(内建函数)。 本文简要介绍如何通过实践使用这些预定义函数并掌握传递参数和返回值。希望对您有所帮助。 JavaScri…
阅读更多...
C语言课程设计

C语言课程设计

内容与设计思想 1、系统功能与分析&#xff08;填写你所设计的菜单及流程图&#xff09;。 菜单&#xff1a; 日历打印 日历推算 日历间隔倒计时牌 退出程序 模块设计 根据功能需要&#xff1a; 源文件&#xff1a; #include<stdio.h> #include&…
阅读更多...
Light-Head R-CNN: In Defense of Two-Stage Object Detector（2017.11）

Light-Head R-CNN: In Defense of Two-Stage Object Detector（2017.11）

文章目录 Abstract1. Introduction2. Related works3. Our Approach3.1. Light-Head R-CNN3.1.1. R-CNN subnet3.1.2. Thin feature maps for RoI warping 3.2. Light-Head R-CNN for Object Detection Conclusion 原文链接 Abstract 在本文中&#xff0c;我们首先研究了为什么…
阅读更多...
参考信号速度变化存在跳跃时容易发生不稳定的阻抗调节

参考信号速度变化存在跳跃时容易发生不稳定的阻抗调节

问题描述 当参考信号速度存在跳跃变化时&#xff0c;阻抗调节系统容易发生不稳定。这是因为阻抗调节系统需要根据参考信号的速度来调整其输出阻抗&#xff0c;以匹配负载阻抗&#xff0c;从而保持系统的稳定性。 当参考信号速度突然变化时&#xff0c;阻抗调节系统可能无法及…
阅读更多...
最新文章

Copyright @ 2022~2023