面试官:一条SQL是如何执行的?

Mysql整体架构

MySQL整体架构如下图所示:

图片

MySQL逻辑系统架构分为4层:

  • 应用层

  • MySQL服务层

  • 存储引擎层

  • 系统文件层

下面将对各层的功能和组件进行介绍,并探讨一条语句的执行过程。

应用层

应用层是MySQL体系架构的最上层,它处理与客户端的交互,主要包含连接处理、用户鉴权和安全管理。

  1. 连接处理:负责管理客户端与MySQL服务器之间的连接。它管理连接的建立、维护和关闭,并分配和释放连接资源以提高性能和可扩展性。

  2. 用户鉴权:用于验证客户端的身份。MySQL支持多种身份验证方法,如基于用户名和密码的验证、SSL/TLS证书验证等,以提高数据的安全性。

  3. 安全管理:涉及访问控制、权限管理和数据加密等措施。它定义用户的权限和访问级别,并防止未经授权的访问、网络攻击和数据泄露。

MySQL 服务层

该层是MySQL Server的核心层,提供了MySQL Server数据库系统的所有逻辑功能,该层可以分为如下不同的组件:

  • NoSQL Interface(NoSQL 接口)

  • SQL Interface(SQL 接口)

  • SQL Parser(SQL 解析器)

  • Optimizer (查询优化器)

  • Caches & buffers(缓存)

  1. NoSQL Interface(NoSQL 接口):通过NoSQL接口,MySQL Server可以像操作关系型数据一样操作非结构化数据,而不需要遵循传统的表格和列的模式。

  2. SQL Interface(SQL 接口) SQL接口,接收用户的SQL命令并进行处理,得到用户所需要的结果,具体处理功能如下:

    • Data Manipulation Language (DML).

    • Data Definition Language (DDL).

    • 存储过程

    • 视图

    • 触发器

  3. SQL Parser(SQL 解析器) 解析器的作用主要是解析查询语句,最终生成语法树。首先解析器会对查询语句进行语法分析,如果语句语法有错误,则返回相应的错误信息。语法检查通过后,解析器会查询缓存,如果缓存中有对应的语句,就直接返回结果不进行接下来的优化执行操作。

  4. Optimizer(查询优化器) 优化器的作用主要是对查询语句进行优化,包括选择合适的索引,数据的读取方式。

  5. Caches & buffers(缓存) 包括全局和引擎特定的缓存,提高查询的效率。如果查询缓存中有命中的查询结果,则查询语句就可以从缓存中取数据,无须再通过解析和执行。这个缓存机制是由一系列小缓存组成,如表缓存、记录缓存、key缓存、权限缓存等。查询缓存默认是关闭的,可以通过set query_cache_type=1开启。

存储引擎层

存储引擎是MySQL中具体与文件打交道的子系统,也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。常用的四种存储引擎分别是: 

  • MyISAM存储引擎

  • InnoDB存储引擎

  • MEMORY存储引擎

  • ARCHIVE存储引擎

MySQL将这些存储引擎提供为可插拔的,可以在表级别使用各种存储引擎。一个数据库可以包含使用多个存储引擎的表。"SHOW ENGINES"命令将列出服务器支持的所有存储引擎。

mysql>SHOW ENGINES;

几种存储引擎的对比:

功能MYISAMMemoryInnoDBArchive
存储限制256TBRAM64TBNone
支持事物NoNoYesNo
支持全文索引YesNoNoNo
支持数索引YesYesYesNo
支持哈希索引NoYesNoNo
支持数据缓存NoN/AYesNo
支持外键NoNoYesNo

InnoDB:支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)。

MyISAM:插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低,也可以使用。如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率

MEMORY:所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY。它对表的大小有要求,不能建立太大的表。所以,这类数据库只使用在相对较小的数据库表。如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果

Archive:如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用。

注意,同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表,可以选择MEMORY存储引擎。

物理文件层

物理文件包括:redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等 MySQL的物理文件层包括以下文件:

  1. Redo log:用于事务的持久性和故障恢复,确保在发生故障时数据的一致性。

  2. Undo log:存储撤消日志,支持事务的回滚和多版本并发控制。

  3. Binlog:二进制日志,用于主从复制和数据恢复。

  4. 数据文件:存储表的数据和索引,不同存储引擎使用不同的数据文件格式。

  5. 表定义文件:存储表的元数据信息,包括表结构、字段定义、索引信息等。

  6. 参数文件:用于配置MySQL服务器的参数和选项。

  7. 锁文件:记录正在使用的表的锁信息。

  8. 错误日志文件:记录MySQL服务器的错误和警告信息。

  9. 查询日志文件:记录MySQL服务器接收到的查询语句。

  10. 慢查询日志文件:记录执行时间超过阈值的查询语句。

  11. 临时文件:存储临时数据和排序操作的中间结果。

  12. 其他日志文件:包括日志文件组合、日志索引文件等。

这些物理文件在MySQL中起着不同的作用,支持数据库的正常运行、故障恢复、备份和数据复制等功能。需要注意的是,MySQL的物理文件层是存储引擎特定的,不同存储引擎可能使用不同的文件格式和组织方式。因此,具体的文件类型和文件组织方式可能因存储引擎而异。

一条语句的执行过程

结合整体架构,一条语句的执行过程如下所示:

图片

  1. 客户端(Client) 发送一个SQL查询请求到MySQL服务器。

  2. 连接处理器(Connection Handler) 接收请求并检查查询缓存(Query Cache) 看是否存在该查询的缓存结果。

  3. 如果查询结果在查询缓存中(缓存命中),查询缓存直接将结果返回给连接处理器,然后连接处理器将结果返回给客户端。

  4. 如果查询结果不在查询缓存中(缓存未命中连接处理器(Connection Handler) 接收请求并传递给**SQL解析器(SQL Parser)**。

  5. SQL解析器 解析SQL查询,确认查询的结构,并将解析的查询传递给**查询优化器(Query Optimizer)**。

  6. 查询优化器 对查询进行优化,生成一个最佳的执行计划,并将这个计划传递给**执行器(Executor)**。

  7. 执行器 根据优化器提供的计划与存储引擎(Storage Engine) 交互,执行查询。

  8. 存储引擎 返回查询结果给执行器。

  9. 执行器 判断是否需要将结果缓存,如果需要将查询结果存储到查询缓存中,并将结果返回给连接处理器。

  10. 连接处理器 最后将结果返回给客户端。

查询缓存满了怎么办?

从MySQL 8.0开始,查询缓存功能已被移除。对于MySQL 8.0之前的版本,如果一条查询的结果集大小超过了查询缓存的剩余空间,MySQL将不会缓存这个结果。查询缓存是基于整个结果集的,如果结果集太大,超过了query_cache_limit参数定义的值或者超过了查询缓存的总空间,那么这个结果集不会被缓存。此时,MySQL会直接从存储引擎获取数据,并返回给客户端,而不是将结果存储在查询缓存中。对于结果集大小超过缓冲池(例如InnoDB缓冲池)的情况,MySQL会采用以下策略处理:

  1. LRU算法:如果需要在缓冲池中加载新的数据页,而缓冲池空间不足,MySQL会根据LRU算法淘汰最不常用的数据页,为新数据页腾出空间。

  2. 脏页刷新:MySQL会尝试刷新脏页到磁盘,从而释放缓冲池中的空间。脏页是指已经被修改但还没有写回到磁盘的数据页。

  3. 分批读取:对于大查询,MySQL可能不会一次性将所有数据加载到内存中。它可能会分批次逐渐处理和加载数据,尽可能地减少对缓冲池空间的影响。

当一条SQL查询返回的数据量超过了剩余的查询缓存空间或缓冲池空间时,MySQL通过不缓存该查询结果、使用LRU算法淘汰不常用的数据页、刷新脏页到磁盘等方式来处理这种情况,确保查询可以成功执行并返回结果。对于MySQL 8.0及更高版本,由于移除了查询缓存功能,查询结果不会被缓存,但对于缓冲池,上述的缓冲池管理机制仍然适用。

  • 30
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值