MySQL基础，SQL架构篇

最新推荐文章于 2023-05-31 09:40:02 发布
最新推荐文章于 2023-05-31 09:40:02 发布
阅读量266 收藏
点赞数
分类专栏： MySQL 文章标签： mysql sql 架构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/isjiaye/article/details/130886630
版权

文章目录

5 SQL架构

5.1 整体架构

在这里插入图片描述

  • 由图可知，Mysql为典型的sc（server/client）结构，其中server的数据管理系统由连接层，服务层和引擎层组成。

  1. 连接层：

    主要用于接受客户端的一系列相关服务，如连接者的授权认证、限权查询、线程池分配的问题，说白了就是给查询他是否有资格访问服务器，能访问哪些数据库，如果能的话就给他分配一个线程。（线程池：由客户端连接就分配一下，没有就回到线程池）

  2. 服务层：服务层由sql接口、解析器、优化器、查询缓存组成

    • sql接口：用于接受命令，并返回结果
    • 解析器：将客户端的sql命令解析成可以执行的内部逻辑，解析的结果就可以执行了
    • 优化器：对sql语句进行优化，包括物理优化和逻辑优化
    • 查询缓存：记录有着之前查询的结果，若出现重复查询时可以迅速访问（在mysql8中被删除了）

  3. 引擎层：

    存储着不同的引擎，真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同 的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

5.2 SQL的执行流程

在这里插入图片描述

  • mysql执行流程大致为：client --> 连接层 --> 查询缓存 --> 解析器 --> 优化器 --> 查询引擎

    1. 客户端申请连接MySQL服务器
    2. (连接层)server连接层对申请进行权限查询，若权限通过则给予其一个线程进行执行。
    3. (服务层：查询缓存)首先sql语句会先在查询缓存中查看是否存在有缓存，若有则直接返回结果（查询缓存在MySQL8就被删除，原因见下文）
    4. (服务层：解析器)若没有在查询缓存中发现相同的sql语句，则将sql语句传入解析器，进行词法分析和语法分析，若没有通过则会报错，返回给客户端；反之，生成" 语法树 "，传入优化器
    5. (服务层：优化器)对传入的语法树进行优化，包括物理优化（添加索引）和逻辑优化（更换查询思路），最后生成" 执行计划 "，传入引擎层
    6. (引擎层)根据传入的执行计划进行执行。

  • 查询缓存：

    • 查询缓存会将之前的查询sql与语句和结果以key-value的形式进行储存，当新的sql语句进入时，会在查询缓存中进行查找，若能找到完全相同的sql语句，则直接返回结果。记住是完全相同，从他的机制可以看出，查询缓存存在有如下弊端：
    • 命中率不高，只有完全相同的sql语句才会被储存上，显然这样很低效
    • 会出现缓存失效的情况，如now()函数，很明显两个时间点的now()返回值不一，但缓存没法做到这一点。除此之外，还会出现数据被删除了，然后缓存中仍然保存的情况。

  • 解析器：

    • 进行语法分析：检查sql的拼写是否正确

    • 进行语义分析：检查sql中的访问对象是否存在，比如列名写错了，就会报错。

    • 若两者都通过，则会将sql语句解析为如下树：

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I1rOnHw3-1685083518520)(…/img/MySQL/image-20230409101511707.png)]

  • 优化器：

    • 物理优化（添加索引）
    • 逻辑优化（更换查询思路）
    • 返回执行计划

5.3 执行情况记录

  • MySQL中可以记录我们的历史执行命令，并查看每一个执行记录的内部情况，以及消耗资源，通过profile开启。

  1. 检查是否开始profile，若没有则开启：

    • 检查：查看变量profiling的值，为0则表示未开启

      mysql> select @@session.profiling;	
+---------------------+
| @@session.profiling |
+---------------------+
|                   0 |		
+---------------------+

    • 开启：修改变量的值

      mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

  2. 查看历史执行记录，show profiles;

    mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration   | Query                                   |
+----------+------------+-----------------------------------------+
|        1 | 0.00017825 | select @@session.profiling              |
|        2 | 0.00025075 | SELECT DATABASE()                       |
|        3 | 0.00013550 | SELECT DATABASE()                       |
|        4 | 0.00459875 | select * from employees                 |
|        5 | 0.00078525 | select * from employees                 |
|        6 | 0.00011525 | show profile cpu, block, io for query 6 |
|        7 | 0.00008125 | show @@profiling                        |
|        8 | 0.00018650 | select @@session.profiling              |
|        9 | 0.00012475 | select @@session.profiling              |
|       10 | 0.00019875 | set profiling=1                         |
|       11 | 0.00016475 | select @@profiling                      |
+----------+------------+-----------------------------------------+
11 rows in set, 1 warning (0.00 sec)

  3. 查看某个记录的执行情况。

    show profile cpu,block io for query n;

    通过此命令可以获得sql语句的执行情况，其中Duration表示耗时。

    mysql> show profile cpu,block io for query 5;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000123 | 0.000000 |   0.000000 |         NULL |          NULL |
| Executing hook on transaction  | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| starting                       | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables                 | 0.000085 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                           | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock                    | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing                     | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics                     | 0.000029 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing                      | 0.000024 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing                      | 0.000207 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                            | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end                      | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables                 | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items                  | 0.000206 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up                    | 0.000015 | 0.000000 |   0.000000 |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

5.4 数据库缓冲池(buffer pool)

5.4.1 缓冲池 & 查询缓存

缓冲池

  • 缓冲池的作用：将数据页提取到内存中去，减少磁盘io次数

    众所周知，数据库的数据是放在磁盘中的，倘若每次读取同样的数据时，都从磁盘中进行提取的话，磁盘io次数很多，执行效率不高。缓冲池的出现就是来解决这个问题的：对于innodb引擎，所有数据（真实记录，索引，系统数据等）的储存都是以"页"的形式进行储存的，那么当读取某一条数据时，会先将该数据所在的整个数据页进行读取到缓冲池中去，再进行读写操作，当读写操作结束后，并不会直接释放掉，而是进行储存，将来又需要访问到该数据页的信息时，就不用再进行磁盘io了

  • 缓冲池存放的内存：

在这里插入图片描述

  • 缓冲原则：频次原则

    缓冲池是存放于内存中的，内存有限，因此应该对访问频次较高的数据页进行缓冲.

  • 预读特性：

    在读取数据页时，会大概率将其周围的数据页也一起存放于缓冲中去

查询缓存

  • 查询缓存和缓冲池是两种东西：
    1. 缓冲池在所有的sql中都会存在，而查询缓存在MySQL8.0之后就已经取消了
    2. 查询缓存储存的是查询语句和对应的执行结果，而不是数据

5.4.2 缓冲池怎么读取数据

缓冲池 --> 引擎

  • 缓冲池会尽量将需要访问的数据储存起来，当每次进行读写操作时，数据库会先去缓冲池中检查是否有对应的数据页，如果有则直接对缓冲池中的数据进行读写操作。如果没有，则再进入磁盘将对应数据页读取到缓冲池中。

当修改了缓冲池中的数据后，会直接同步到磁盘中去吗？

  • 不会，缓冲池中的数据会以一定频次同步到磁盘中去，比如有checkpoint机制：

    当checkpoint时间到了后，会将缓冲池中的数据全部同步到磁盘中去。

    当读取新数据页时，发现缓冲池内存不够用，则checkpoint会将不常用的数据页同步到磁盘中去。

5.4.3 存在问题

  • buffer pool的存在，使得每次读写都会先经过buffer pool，再以一定频次同步到磁盘中去。那么就存在一个问题：当修改完buffer pool中的数据后，还没同步到磁盘中去，MySQL服务就挂机了，数据无法进行同步。那该怎么办？

    答案：事务回滚

5.4.4 缓冲池参数

查看、修改缓冲池大小(innodb引擎)

-- 查看
show  variables like 'innodb_buffer_pool_size'
-- 修改
set global innodb_buffer_pool_size = 268435496	-- 256M

缓冲池的个数

  • 缓冲池在多线程中时，需要为其添加锁，当并发较高时，又会影响性能。因此可以设置多个缓冲池
-- 查看
show variables like 'innodb_buffer_pool_instances'

-- 设置
set global  innodb_buffer_pool_instances = 2

也许我该长大了
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL Server整体架构（2）——数据库
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
11-30 3204
本文对SQL Server数据库及其各组件概念与机制进行了较为深入详细的说明和讲解。
MySql基础知识总结（SQL优化篇）
热门推荐
学Java，找哪吒
07-12 4万+
1、建表语句 + 联合索引 CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp .
SQL基本架构
u013821295的专栏
05-12 994
转载自：http://www.knowsky.com/4131.html 　我们总是把SQL Server 这个单词挂在嘴边，也许很少有人思考过 SQL Server 到底是什么东西。这个问题很难用几句话讲清楚。我们之所以要用连载的方式讨论 SQL Server，就是要从不同的角度阐述这个问题。  　　一、服务器组件 　　SQL Server 2000由两个部分组成：服务器组件和
一条SQL语句在MySQL中如何执行的
Kirito的博客
04-25 306
MySQL 基本架构概览 请先了解InnoDB引擎 •连接器： 身份认证和权限相关(登录 MySQL 的时候)。 •查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。 •分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。 •优化器： 按照 MySQL 认为最优的方案去执行。 •执行器: 执行语句，然后从存储引擎返回数据。 MySQL 主要分为 Server
SQL--架构、视图和索引
Ethan's Blog
05-20 719
架构、视图和索引 架构 架构是指用于存储数据库对象的一个命名空间。用于集中管理数据库对象子集，这样极大地方便并简化了管理数据库对象。 创建架构 CREATE SCHEMA schema_name_clause [<schema_element> [...n]] <schema_name_clause>::= { schema_name | AUTHORIZATION owner_name | schema_name AUTHORIZATION
SQL 架构剖析
飘着ing
10-14 994
关键字：T-SQL;架构架构（Schema）。微软的官方说明（MSDN）： "数据库架构是一个独立于数据库用户的非重复命名空间，您可以将架构视为对象的容器"，详细参考 http://technet.microsoft.com/zh-cn/library/ms190387.aspx.我们知道，在JAVA中，命名空 间名其实就是文件夹名。因此我们非常明确一点：一个对象只能属于一个架构，就像一个文件只能存放于一个文件夹中一样。与文件夹不同的是，架构是不能嵌套 的，如此而已。因此，我们要访问一个数据库对象的时候，通
MYSQL基础架构
Orange_green的博客
09-16 333
大部分情况下，我们使用数据库，看到的都是一个整齐。比如我们查询数据： mysql>select * from T where ID = 1; 我么看到的只是输入一条语句，返回给我们一个结果，却不知道这条SQL在数据库中是如何执行的。 今天我们就来看一下一条SQL在数据库中是如何执行的。 首先我们来看一下数据库的基本构成： 从图上可以看出 MySQL大体上可以分为Server层和存储引擎层两部分。 Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所
MySQL基础架构
qq_43352723的博客
09-13 392
前言 最近阅读林晓斌老师的《MySQL实战45讲》，感觉写的很好，但知识点有点杂乱，于是本人决定记录下学习这个专题的学习笔记。林老师这个专题写的蛮好的，推荐大家去阅读一下，丰富下自己的知识面。 这篇博客，是关于老师实战45讲的第一讲 ——《基础架构：一条SQL查询语句是如何执行的？》的学习笔记，话不多说，开写。 一、MySQL基本逻辑架构 假如，有个最简单的表，表里只有一个 ID 字段，在执行下面这个查询语句时： mysql> select * from T where ID=10； 我们看到的只是
Mysql基础架构：一条SQL查询语句执行过程.pdf
最新发布
08-01
mysql 基础架构：一条SQL查询语句执行过程.pdf
mysql深度学习者 MySQL架构与SQL执行流程
12-17
本篇文章将深入浅出 MySQL 架构和 SQL 执行流程，帮助读者更好地理解 MySQL 数据库的内部机理和工作流程。 MySQL 架构 ---------- MySQL 架构主要包括以下几个部分： 1. 连接器（Connector）：负责管理客户端...
SQL脚本框架
02-01
没有看我的文章的，不要下载这个脚本，没用，这个是我写的文章配套的
mysql基础架构教程之查询语句执行的流程详解
01-19
这篇笔记主要记录mysql的基础架构，一条查询语句是如何执行的。 下面话不多说了，来一起看看详细的介绍吧 比如，在我们从student表中查询一个id=2的信息 select * from student where id=2; 在解释这条语句执行流程...
全解MySQL之架构篇：自顶向下深入剖析MySQL整体架构
Huangjiazhen711的博客
10-25 858
一般来说，当你对数据库进行写操作时，都会先从缓冲区中查询是否有你要操作的页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后。的整体架构有一个基本认知，这也为咱们后续的文章打下了坚实的基础，因为毕竟想要深入研究一个技术，那定然不能如同管中窥豹一般，仅看一个细节点，而是更应该是先窥其全貌，再深入细节。如果你在连接数据库的过程中，出现了上述的错误信息，那绝对是你输入的用户名或密码错误导致的，当账号及密码正确时，此时就会进入。
SQL 框架
weixin_41298572的博客
03-14 1603
创建数据库并指定编码 create database 数据库名 default charcter set utf8 创建表 create table 表名{ 列名 类型 约束 comment ‘备注’ } 类库与框架的区别 类库就是名言警句 我们写作文的时候可以直接引用 框架是填空题 大概的内容都写好了，需要我们填写部分内容 框架需要使用XML解析技术与反射技术 ...
SQL学习框架
YiBochun-Blog
08-20 998
视频学习链接 https://www.bilibili.com/video/av59623481/?p=27 本单元目标 一、为什么要学习数据库 二、数据库的相关概念 DBMS、DB、SQL 三、数据库存储数据的特点 四、初始MySQL MySQL产品的介绍 MySQL产品的安装 ★ MySQL服务的启动和停止 ★ M...
数据库的部署和架构图
努力的空白的博客
02-07 815
数据库的部署
数据库--SQL架构基础
左岸人的博客
10-21 729
1,在我们的印象当中,我们给oracle发出一条命令,oracle根据我们的发出的命令做出相应的操作,但是在oracle操作的过程当中,我们是不知道oracle内部是如何操作的! 2,其实在通常我们说的oracle数据库既是指存储在电脑硬盘上的数据库文件,有的时候也指管理这些文件的内存结构.     这里"数据库"文件是属于硬盘上的数据文件,而"数据库实例"则属于oracle的内存结构.
SQL数据库的整体结构、索引、MVCC、锁、日志、查询优化，三大范式等
weixin_53344209的博客
05-31 1486
2、聚簇索引在增删改的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完成。，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
SQL语言学习框架
P_xiaojia的博客
08-15 578
SQL语言的简单框架，适合新手入门把握总体框架
"MySql数据库Sql调优技巧及基础架构分析
MySql的优化器主要负责将用户输入的SQL语句进行优化，从而提高查询的效率。 优化器的工作主要包括： 1. 查询重写：将用户输入的SQL语句进行重写，找到更加高效的查询方式。例如，将多个子查询合并为一个查询，或者...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值