MySQL :通过 SQL 执行过程掌握 MySQL 架构

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

不知道大家有没有思考这样一个问题,一条 SQL 查询语句是如何执行的?一条 SQL 更新语句又是如何执行的?这些执行过程都涉及哪些模块,他们的工作原理又是怎样的? 本文希望通过说明 SQL 语句的执行…

不知道大家有没有思考这样一个问题,一条 SQL 查询语句是如何执行的?一条 SQL 更新语句又是如何执行的?这些执行过程都涉及哪些模块,他们的工作原理又是怎样的?

本文希望通过说明 SQL 语句的执行流程和数据处理流程,和大家一起学习和掌握 MySQL 的逻辑架构和物理架构。 InnoDB 作为系统默认存储引擎,这也将是本 Chat 的讨论的重点。

本文将包括以下内容:

  1. MySQL 语句的执行流程是什么样的?
  2. MySQL 的物理架构和逻辑架构都包括什么?
  3. InnoDB 存储引擎的架构都包括什么模块?


SQL 执行流程

一般我们应用程序连接器(Connector)/ ODBC 与 MySQL 服务器的交互包括以下操作(图片源自 MySQL 官网):

在这里插入图片描述

1. 配置 connector/ODBC 数据源

2. 连接到 MySQL 服务器

包括:分配环境句柄,设置 ODBC 版本,分配连接句柄,连接到 MySQL Server 以及设置可选的连接属性。

3. 初始化语句

包括:分配语句句柄,设置可选的语句属性。

4. 执行 SQL 语句

包括:准备 SQL 语句并执行 SQL 语句,或直接执行 SQL 语句。

5. 检索结果,具体取决于语句类型

对于 SELECT/SHOW/Catalog API,结果可能包括:获取列数、获取列信息、获取行以及将数据获取到缓冲区。对于“ DELETE/UPDATE/INSERT”,结果包括受影响的行数。

6. 进行事务,执行提交或回滚

7. 断开与服务器的连接

包括:断开连接,释放连接和环境句柄。

我们通过上述过程大致描述了一次 SQL 语句的过程,但其中的细节还需要进一步分析。

第一步,对于使用 MySQL 客户端库的程序(例如 mysql 和mysqldump),MySQL 服务器支持多种传输协议的连接,包括:TCP/IP,Unix 套接字(socket)文件,命名管道和共享内存。TCP/IP 传输支持与本地或远程 MySQL 服务器的连接,套接字文件、命名管道和共享内存传输仅支持与本地 MySQL 服务器的连接。

--protocol 使用的传输协议 适用平台
TCP TCP/IP 全部
SOCKET Unix 套接字文件 Unix 或类 Unix 系统
PIPE 命名管道 视窗
MEMORY 共享内存 Windows

TCP/IP

真实环境中,服务器进程和客户端进程可能运行在不同的主机中,它们之间必须通过网络来进行通讯。MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议。

Unix 套接字(socket)文件

如果服务器进程和客户端进程都运行在同一台操作系统为类 Unix 的机器上的话,可以使用 Unix 域套接字文件来进行进程间通信。通过在启动客户端程序的时候指定主机名为 localhost,或指定 --protocol=socket 的启动参数,那服务器程序和客户端程序之间就可以通过 Unix socket 文件来进行通信了。MySQL 默认监听的 Unix 套接字文件路径为 /tmp/mysql.sock。

命名管道和共享内存

如果是使用 Windows,那么客户端进程和服务器进程之间可以考虑使用命名管道或共享内存进行通信。使用命名管道来进行进程间通信需要在启动服务器程序的命令中加上 --enable-named-pipe 参数,然后在启动客户端程序的命令中加入–pipe 或者 --protocol=pipe 参数。

使用共享内存来进行进程间通信需要在启动服务器程序的命令中加上 --shared-memory 参数,在成功启动服务器后,共享内存便成为本地客户端程序的默认连接方式,不过也可以在启动客户端程序的命令中加入 --protocol=memory 参数来显式的指定使用共享内存进行通信。

注意:使用共享内存的方式进行通信的服务器进程和客户端进程必须在同一台 Windows 主机中。

不论客户端进程和服务器进程是采用哪种方式进行通信,最后实现的效果都是:客户端进程向服务器进程发送一段文本(MySQL 语句),服务器进程处理后再向客户端进程发送一段文本(处理结果)。这里先以查询请求为例展示一下大致的过程:

在这里插入图片描述

连接管理

客户端与服务端建立连接,每当有一个客户端进程连接 到服务器进程时,服务器进程都会创建一个线程(pre-threaded)来专门处理与这个客户端的交互,当该客户端退出时会与服务器断开连接,服务器并不会立即把与该客户端交互的线程销毁掉,而是缓存起来(连接池技术),在另一个新的客户端再进行连接时,把这个缓存的线程分配给该新客户端。

查询缓存

MySQL 在处理查询请求,会将查询请求和结果进行缓存,再遇到相同的请求过来,直接从缓存返回查询结果。MySQL 对查询请求的缓存采用的对 SQL 语句的 Hash 值的处理方式,这就要求两个查询请求在任何字符上的不能有任何不同(例如:空格、注释、大小写)。

另外,如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表,如 mysql、information_schema、 performance_schema 数据库中的表,那这个请求也不会被缓存。

聊到了缓存的有效性,那就有缓存失效的时候。MySQL 的缓存系统会监测涉及到的每张表,只要该表的结构或者数据被修改,如对该表使用了 INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE 或 DROP DATABASE 语句,那该表的所有高速缓存查询都将变为无效。

PS:基于 MySQL 缓存的鸡肋,从 MySQL 5.7.20 开始,不推荐使用查询缓存,并在 MySQL 8.0 中移除了缓存模块。

通过设置 query_cache_type 设置成 DEMAND。可以在 SQL 语句中 SQL_CACHE 显式指定使用缓存。如以下示例所示:

SELECT SQL_CACHE * FROM T where id = 5

语法解析

对 SQL 语句进行词法和语法分析,判断请求的语法是否正确,一般来说,一条正确的 SQL 语句则会通过语法分析生成一颗解析树。

在这里插入图片描述

查询优化

经过语法解析后,MySQL 服务器知道我们所需要做的事情,离最终执行的还需要经过优化器处理得到最终的执行计划。执行计划表明了应该使用哪些索引进行查询,表之间的连接顺序。我们可以使用 EXPLAIN 语句来查看某个语句的执行计划。

我们可能经常听说 MySQL 执行一个查询可以有不同的执行方案,那么 MySQL 是如何选择的呢?答案就是:基于成本。这里的成本包括 I/O 成本 和 CPU 成本。

存储引擎

一条 SQL 语句经过上面层次关卡,到现在还没有真正访问到真实的数据表,MySQL 采用了一直插件式存储引擎,用来支持不同的存储要求。MySQL server 层提供统一存储引擎 API,来帮助不同的存储引擎开发者实现是数据表的底层物理结构。

可插拔的存储引擎

通过前面的了解,我们了解到 MySQL 为我们提供了可插拔的存储引擎,MySQL 可插拔存储引擎体系结构使数据库专业人员可以为特定的应用程序需求选择专用的存储引擎,同时完全不用对应用程序存在特定编码需求。MySQL 体系结构提供了一致的应用程序模型和 API。

可插拔的含义为存储引擎可以加载到正在运行的 MySQL 服务器或从正在运行的 MySQL 服务器上卸载。

# 插入存储引擎 exampleINSTALL PLUGIN example SONAME \'ha_example.so\'; 
# 拔出存储引擎UNINSTALL PLUGIN example;

为了进一步加深理解,我在这里找到 MySQL 官网上关于 MySQL 的体系结构图(原图来自 MySQL 官网 Overview of MySQL Storage Engine Architecture)。

在这里插入图片描述

MySQL Connectors

不同语言中与 SQL 的交互的接口,包括 Python、PHP、Node.js、Java。

MySQL Shell

MySQL Shell 是 MySQL Server 的高级客户端和代码编辑器。除了提供的类似于 MySQL 的 SQL 功能外, MySQL Shell 还提供 JavaScript 和 Python 脚本功能,并包括与 MySQL 一起使用的 API。MySQL Shell 是可以单独安装的组件。

MySQL Server Process(mysqld)

MySQL 服务器守护程序,在后台运行。管理客户端请求。mysqld 是一个多线程的进程,允许多个会话连接,端口监听连接,管理 MySQL 实例。

NoSQL Interface

MySQL 服务器 的文档存储接口(NoSQL)。

SQL Interface

接受用户的 SQL 命令,并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface。

Parser

SQL 命令传递到解析器的时候会被解析器验证和解析。解析器是由 Lex 和 YACC 实现的,是一个很长的脚本。在 MySQL 中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query,在 MySQL Server 里面,连接线程接收到客户端的一个 Query 后,会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。

将 SQL 语句进行语义和语法的分析,分解成数据结构,然后按照不同的操作类型进行分类,然后做出针对性的转发到后续步骤,以后 SQL 语句的传递和处理就是基于这个结构的;如果在分解构成中遇到错误,那么就说明这个 SQL 语句是不合理的。

Optimizer

SQL 语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求 query,根据客户端请求的 query 语句,和数据库中的一些统计信息,在一系列算法的基础上进行分析,得出一个最优的策略,告诉后面的程序如何取得这个 query 语句的结果。

Caches & Buffers

全局的或者存储引擎用到的缓存或 Buffer。

Storage Engines

存储引擎模块,在目前各种数据库产品中,基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理

Files & Logs

数据文件、索引文件、日志文件等。

了解完 MySQL 存储引擎的体系结构后,我们还有必要对 MySQL 为我们提供的存储引擎特性做一定的了解。要确定服务器支持哪些存储引擎,请使用 SHOW ENGINES 语句。Support 列中的值表示是否可以使用该引擎,DEFAULT 表示当前被设置为默认的存储引擎。

mysql> SHOW ENGINES;*************************** 1. row ***************************      Engine: PERFORMANCE_SCHEMA     Support: YES     Comment: Performance SchemaTransactions: NO          XA: NO  Savepoints: NO*************************** 2. row ***************************      Engine: InnoDB     Support: DEFAULT     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES*************************** 3. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 4. row ***************************      Engine: BLACKHOLE     Support: YES     Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO          XA: NO  Savepoints: NO*************************** 5. row ***************************      Engine: MyISAM     Support: YES     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO

MySQL 5.7 支持的存储引擎:

  • InnoDB:MySQL 5.7 中的默认存储引擎。 InnoDB 是用于 MySQL 的事务安全(兼容 ACID)的存储引擎,具有提交,回滚和崩溃恢复功能。 InnoDB 行级锁定(无需升级为更粗粒度的锁定)和 Oracle 风格的一致非锁定读取可提高多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中,以减少基于主键的常见查询 的 I/O。 InnoDB 还支持 FOREIGN KEY 引用完整性约束。
  • MyISAM:这些表占用的空间很小。支持表级锁定,表锁在读/写工作负载中的性能不佳,因此通常在 Web、数据仓储和其他应用环境下使用。
  • Memory:将所有数据保存在 RAM 中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。
  • CSV:其表实际上是带有逗号分隔值的文本文件。CSV 表允许您以 CSV 格式导入或转储数据,以便与读取和写入相同格式的脚本和应用程序交换数据。通常 InnoDB 在导入导出阶段可使用 CSV。
  • Archive:为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
  • Blackhole:Blackhole 存储引擎可以接受数据但不存储数据,类似于 Unix/dev/null 设备。查询总是返回一个空集。可用于将 DML 语句发送到副本服务器的复制配置中,但源服务器不保留其自己的数据副本。
  • NDB(也称为 NDBCLUSTER):MySQL 的簇式数据库引擎,尤其适合于具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性。
  • Merge:允许 MySQL DBA 或开发人员将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为 1 个对象引用它们。对于诸如数据仓储等 VLDB 环境十分适合。
  • Federated:能够将多个分离的 MySQL 服务器链接起来,从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
  • Example:此引擎用作 MySQL 源代码中的示例,用于说明如何开始编写新的存储引擎。

大致了解了存储引擎的特性,我们还需要综合比较下各个存储引擎的特性:

eature MyISAM Memory InnoDB Archive NDB
B-tree indexes Yes Yes Yes No No
Backup/point-in-time recovery Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Clustered indexes No No Yes No No
Compressed data Yes No Yes Yes No
Data caches No N/A Yes No Yes
Encrypted data Yes Yes Yes Yes Yes
Foreign key support No No Yes No Yes
Full-text search indexes Yes No Yes No No
Geospatial data type support Yes No Yes Yes Yes
Geospatial indexing support Yes No Yes No No
Hash indexes No Yes No No Yes
Index caches Yes N/A Yes No Yes
Locking granularity Table Table Row Row Row
MVCC No No Yes No No
Replication support (note 1) Yes Limited Yes Yes Yes
Storage limits 256TB RAM 64TB None 384EB
T-tree indexes No No No No Yes
Transactions No No Yes No Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes

InnoDB 架构

通过对存储引擎的特性大致的了解,我们更应该关注的为 MySQL 默认的存储引擎 InnoDB,也是我们日常工作中最常用的存储引擎。首先我们来了解一下官网的介绍。

  • InnoDB 是一种兼顾了高可靠性和高性能的通用存储引擎,其 DML 操作遵循 ACID 模型,并具有提交,回滚和崩溃恢复功能的事务。
  • InnoDB 行级锁 和 Oracle 风格的一致读取 可提高多用户并发性和性能。
  • InnoDB 表将数据存储在磁盘上,每个 InnoDB 表都有一个称为聚集索引的主键索引,该索引以最小化 I/O 主键查找组织数据
  • InnoDB 支持 FOREIGN KEY 约束。
  • InnoDB 是一个多版本的存储引擎(MVCC)。它保留有关已更改行的旧版本的信息,以支持事务功能,例如并发和回滚。

我们还是首先了解一下 InnoDB 的结构,后续文章再进一步来学习和了解 InnoDB 的特性,下图显示了构成 InnoDB 存储引擎体系结构的内存中和磁盘上的结构(原图来自 MySQL 官网 InnoDB Architecture 一节(点击这里))

在这里插入图片描述

从 InnoDB 的结构中,可以看到 InnoDB 是将表中的数据存储到磁盘上,而真正处理数据的过程是发生在内存中,所以处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。InnoDB 采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB 中页的大小一般为 16 KB。

通过上图我们可以看到 Buffer Pool 主要分为 3 个部分:Buffer Pool、Change Buffer、Adaptive Hash Index,另外还有一个(redo) log buffer。

1. Buffer Pool

Buffer Pool 默认大小是 128M 可以调整,Buffer Pool 缓存的是页面信息,包括数据页、索引页,大小和在磁盘上默认的页大小是一样的。大致结构如下所示:

在这里插入图片描述

有了缓存,就会有缓存更新算法,InnoDB 采用链表实现的 LRU 算法更新缓存。此 LRU 算法相对于传统的 LRU 算法稍有不同,将页面分为 young 和 old 两个部分,如下图所示:

在这里插入图片描述

2. Chang Buffer 写缓冲

Change Buffer 是 Buffer Pool 的一部分,当一个数据不是唯一索引,也不存在数据重复的情况(不需要唯一性检查),可以先将修改记录记录在内存的缓冲池中,从而提升更新语句的执行速度。

在这里插入图片描述

通过前面大致的分析大致了解了 Buffer Pool 的作用,再来看看 InnoDB 在磁盘上的结构,我们知道了 InnoDB 通过页的方式存储数据。实际上 InnoDB 的多个页,组成区(Extents),4 个区组成一个段(Segments),多个段组成一个逻辑的表空间(Tablespace)。大概就是这样:

在这里插入图片描述

而总体上 InnoDB 的磁盘结构上的表空间分为 5 大类。

1. 系统表空间 System Tablespace

InndoDB 系统表空间包含了 InnoDB 数据字典和双写缓冲区、Change Buffer 和 Undo logs,如果没有指定 file-per-table,也包含用户创建的表和索引数据。

什么是双写缓冲,可在后续文章中持续关注学习。

2. 独占表空间 file-per-table Tablespaces

通过设置 innodb_file_per_table 开启(默认开启了),我们可以让每张表独占一个表空间。文件目录下的 ibd 文件存放了表的索引和数据。

3. 通用表空间 general Tablespaces

通用表空间也是一种共享表空间,通过 Create tablespace 创建,通过 Create table 时指定表空间进行共享。

4. 临时表空间 temporary Tablesoaces

存储临时表的数据,包括用户创建的临时表。

5. Redo log

redo log 日志文件。

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

免责声明: 1、本站信息来自网络,版权争议与本站无关 2、本站所有主题由该帖子作者发表,该帖子作者与本站享有帖子相关版权 3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和本站的同意 4、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责 5、用户所发布的一切软件的解密分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。 6、您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 7、请支持正版软件、得到更好的正版服务。 8、如有侵权请立即告知本站(邮箱:1099252741@qq.com,备用微信:1099252741),本站将及时予与删除 9、本站所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章和视频仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。