Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >一条SQL的奇妙旅行

一条SQL的奇妙旅行

作者头像
可可的测试小栈
发布于 2020-09-14 08:46:40
发布于 2020-09-14 08:46:40
5040
举报
文章被收录于专栏:全栈测试全栈测试

工作中我们经常查询数据库,用一个查询,得到想要的数据。可有想过,我们得到答案经过了哪些磨难?经历了哪些诱惑?

以下将以一条SQL的执行过程来了解 MySQL 整体架构,对MySQL有一个全面,清晰的认知,For造航母。

开始旅行

第1关

连接器

客户端发送一条查询给服务器,包含客户端相关信息(IP、用户、密码),服务器完成验证。

报告太君,自己人,可以放行!

第2关

查询缓存

执行查询语句的时候,会先查询缓存,我们会发现某个查询,查询第二次的时候非常快便是这个原因(MySQL8.0 废除这个功能,太鸡肋)。

......

第3关

解析器

第一步:解析你的语法,主要是关键字;

单词别写错了,写错了,我可不会干活。

第二步:解析涉及到的对象是否存在;

人都没有,跟空气聊个啥呢?

第三步:涉及到的对象用户是否有对应的权限。

哎呀,不给钱就不给看,不给看。

第4关

优化器

当语法与语义都没有问题权限也匹配,此时数据库便开始真正为你服务了,根据一定得算法规则,对你的查询进行优化,寻找最优的执行计划。

国家分配的跟自己找的肯定还是不一样的,多数情况下,还是自己找的好。

第5关

执行

先判断数据是否在缓冲池中,若在,直接返回,若不在,则先从磁盘文件中加载到内存。

嘻嘻,反正今天要定了。

第6关

数据返回

数据返回是一边查询,一边返回,并不是一次返回,虽然看上去是一下突然返回的。

BTW,你看见的不一定是真的。

旅行图如下:

MySQL体系结构

最外层客户端:各种语言API连接数据库。

第1层

连接层

包含连接池,身份验证,查询缓存。

第2层

核心服务层

解析器,优化器,跨存储引擎的函数,存储过程,触发器,视图,SQL接口,管理服务工具组件。

第3层

存储引擎层

不同存储引擎即数据的存取方式不同。

第4层

文件系统

文件系统,底层存储数据的磁盘。

MySQL体系架构图如下:

InnoDB存储引擎三大特性

特性1

自适应hash索引

B+树的高度一般为3~4层,故需要3~4次的查询,如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index,AHI) AHI是通过缓冲池的B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引 --来自INNODB 技术内幕(人工智能赶脚有没有)。

缺点: 跟普通索引一样需要额外开销维护。

特性2

插入缓冲

对于非聚集类索引的插入和更新操作,不是每一次都直接插入到索引页中,而是先插入到内存中。具体做法是:如果该索引页在缓冲池中,直接插入;否则,先将其放入插入缓冲区中,再以一定的频率和索引页合并,这时,就可以将同一个索引页中的多个插入合并到一个IO操作中,大大提高写性能(一定是非聚集索引)。

缺点:可能导致数据库宕机后实例恢复时间变长,占用太多缓冲池内存。

特性3

双写

当MySQL将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ,通过double write buffer再分2次,每次写入1MB到共享表空间,然后马上调用fsync函数,同步到磁盘上,避免缓冲带来的问题(前俩个是提升性能,双写主要保证数据页的可用性)。

InnoDB存储引擎内存结构图如下:

今日问题

你知道MySQL索引的用途,以及主键索引与二级索引的区别是什么吗?

(欢迎在下方留言区发表你的看法)

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-09-10,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 ITester软件测试小栈 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL InnoDB架构深度解析
MySQL的InnoDB存储引擎是现代Web应用中最常用的数据库存储引擎之一,它以其强大的事务支持、外键约束和并发控制能力而著称。InnoDB的高性能特性很大程度上得益于其精心设计的内存架构,特别是Buffer Pool、Change Buffer、自适应哈希索引和Log Buffer等关键组件。本文将深入探讨这些核心组件的设计原理、实现机制以及对数据库性能的影响,帮助读者全面理解InnoDB的内存管理机制。
javpower
2025/05/30
870
MySQL InnoDB架构深度解析
MySQL探秘(三):InnoDB的内存结构和特性
 常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特点就是其插件式的表存储引擎。而在众多存储引擎中,InnoDB是最为常用的存储引擎。从MySQL5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。  InnoDB存储引擎支持事务,其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持非锁定读,即默认读操作不会产生锁。  InnoDB通过使用多版本并发控制(MVCC)来获取高并发性,并且实现了SQL标准的4中隔离级别,默认为REPEATABLE级别。同时,使用一种被称为next-key-locking的策略来避免幻读现象的产生。除此之外,InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。
程序员历小冰
2018/08/27
5670
MySQL探秘(三):InnoDB的内存结构和特性
MySQL之InnoDB关键特性
在InnoDB存储引擎中,主键是唯一的标识符。应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚簇索引(Primary key)一般是顺序的,不需要磁盘随机读写。比如定义一下SQL表:
CBeann
2023/12/25
2070
MySQL之InnoDB关键特性
【MySQL 系列】MySQL 架构篇
Server 层:负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现,主要包括连接池,执行器、优化器、解析器、预处理器、查询缓存等。另外,所有的内置函数(如日期、时间、数学和加密函数等)和所有跨存储引擎的功能(如存储过程、触发器、视图等)都在 Server 层实现;
栗筝i
2024/03/19
2.4K0
【MySQL 系列】MySQL 架构篇
字节三面:详解一条 SQL 的执行过程
天天和数据库打交道,一天能写上几十条 SQL 语句,但你知道我们的系统是如何和数据库交互的吗?MySQL 如何帮我们存储数据、又是如何帮我们管理事务?....是不是感觉真的除了写几个 「select * from dual」外基本脑子一片空白?金三银四读者福利:整理好的MySQL实战笔记,金三银四面试资料集锦。
Java程序猿
2021/02/23
4000
不知道 Insert Buffer 的请举手
在讲插入缓冲这个振奋人心的 InnoDB 新特性之前,我们有必要先来回顾下 Buffer Pool(缓存池)的概念。
飞天小牛肉
2022/02/23
2300
不知道 Insert Buffer 的请举手
重要,知识点:InnoDB的插入缓冲
今天我们的主题就是 插入缓冲(Insert Buffer),由于InnoDB引擎底层数据存储结构式B+树,而对于索引我们又有聚集索引和非聚集索引。
鲁大猿
2020/10/29
7840
InnoDB 存储引擎.
InnoDB 存储引擎有多个内存块,可以认为这些内存块组成了一个大的内存池,负责如下工作:
JMCui
2020/07/15
5980
MySQL的逻辑架构--逻辑架构剖析、SQL执行流程、数据库缓冲池(buffer pool)
TCP连接收到请求后,必须分配给一个线程专门与这个客户端的交互,所以还有个线程池,每一个连接从线程池中获取线程,省去了创建和销毁线程的开销 所以连接管理的职责就是负责认证、管理连接、获取权限信息
才疏学浅的木子
2022/11/22
6990
MySQL的逻辑架构--逻辑架构剖析、SQL执行流程、数据库缓冲池(buffer pool)
MySQL 技术非懂不可
在MySQL数据库中,实例与数据库的关系通常是一一对应的,即一个实例对应一个数据库,一个数据库对应一个实例。但是,在集群情况下可能存在一个数据库被多个数据实例使用的情况。
BUG弄潮儿
2021/06/25
7960
MySQL 技术非懂不可
面试必问的 MySQL,你懂了吗?
面试必问的 MySQL,你懂了吗?
Java架构师必看
2021/06/17
5860
面试必问的 MySQL,你懂了吗?
面试官:你说说一条更新SQL的执行过程?
在上一篇《面试官:你说说一条查询SQL的执行过程?》中描述了Mysql的架构分层,通过解析器、优化器和执行引擎完成一条SQL查询的过程,那这一篇续上继续说明一条更新SQL的执行过程。
艾小仙
2021/08/25
4580
面试官:你说说一条更新SQL的执行过程?
sqk
对于非聚集索引的插入或者更新,不是每一次都直接插入到索引页中,而且先判断插入的非聚集索引页是否在缓存池中。若在,则直接插入;若不在,则先放入到一个Insert Buffer对象中去。数据库这个非聚集的索引已经插入到叶子节点了,而实际上没有,只是存放到了一个位置,然后以一定的频率和情况刷新进行Insert Buffer和辅助索引叶子节点的merge操作。这时通常能将多个插入操作合并到一个操作中(因为在一个索引页中),这就大大的提高了对于非聚集索引插入的性能。
Michel_Rolle
2024/09/13
2.9K0
一条查询SQL是如何执行的?更新、新增、删除呢?
大部分朋友估计都只知道写sql然后执行,但是并不知道MySQL背后到底是怎么实现的。
田维常
2023/12/26
4750
一条查询SQL是如何执行的?更新、新增、删除呢?
头条二面: 详解一条 SQL 的执行过程|文末送书
天天和数据库打交道,一天能写上几十条 SQL 语句,但你知道我们的系统是如何和数据库交互的吗?MySQL 如何帮我们存储数据、又是如何帮我们管理事务?....是不是感觉真的除了写几个 「select * from dual」外基本脑子一片空白?这篇文章就将带你走进 MySQL 的世界,让你彻底了解系统到底是如何和 MySQL 交互的,MySQL 在接受到我们发送的 SQL 语句时又分别做了哪些事情。
kunge
2021/01/28
8500
头条二面: 详解一条 SQL 的执行过程|文末送书
Innodb是如何运转的
Master Thread是非常核心的后台线程,主要负责脏页的刷新,合并插入缓冲,UNDO页回收等。
大忽悠爱学习
2022/12/07
3870
Innodb是如何运转的
InnoDB 内存结构及其原理
InnoDB 是 MySQL 的默认存储引擎,以其强大的事务支持、崩溃恢复能力和高并发处理性能著称。要深入理解 InnoDB 的内存结构及其工作原理,有必要探讨其内存使用的各个方面,包括缓冲池(Buffer Pool)、内存分配、锁结构(Lock Structure)和其他内部内存使用机制。
Michel_Rolle
2024/07/31
2.9K0
第04章_逻辑架构
🧑个人简介:大家好,我是 shark-Gao,一个想要与大家共同进步的男人😉😉
程序员Leo
2023/08/02
2810
第04章_逻辑架构
MySQL性能调优 – 你必须了解的15个重要变量
1.DEFAULT_STORAGE_ENGINE 如果你已经在用MySQL 5.6或者5.7,并且你的数据表都是InnoDB,那么表示你已经设置好了。如果没有,确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB。 为什么?简而言之,因为InnoDB是MySQL(包括Percona Server和MariaDB)最好的存储引擎 – 它支持事务,高并发,有着非常好的性能表现(当配置正确时)。 2.INNODB_BUFFER_POOL_SIZE 这个是InnoDB最
老七Linux
2018/05/31
4.2K0
MySQL的内存结构与物理结构
MySQL的数据存储结构主要分两个方面:物理存储结构与内存存储结构,作为数据库,所有的数据最后一定要落到磁盘上,才能完成持久化的存储。内存结构为了实现提升数据库整体性能,主要用于存储临时数据和日志的缓冲。本文主要讲MySQL的物理结构,以及MySQL的内存结构,对于存储引擎也主要以InnoDB为主。
搬砖俱乐部
2019/08/30
8.4K0
相关推荐
MySQL InnoDB架构深度解析
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档