mysql学习笔记-底层原理详解

虚幻大学 xuhss 421℃ 0评论

Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

https://edu.csdn.net/course/detail/35475

前言
我相信每一个程序员都避免不了和数据库打交道,其中Mysql以其轻量、开源成为当下最流行的关系型数据库。Mysql5.0以前以MyISAM作为默认存储引擎,在5.5版本以后,以InnoDB作为默认存储引擎,相比MyISAM,InnoDB完整的支持ACID事务特性,同时支持行级锁,支持事务这一特性也决定了InnoDB代替MyISAM成为主流存储引擎的一大决定性因素。

Mysql系统架构图

0b1e9267f039f29f61c36d691b173731 - mysql学习笔记-底层原理详解

mysql整体包含四大部分:

连接层:包含各种组件和连接交互接口(Connectors:各种语言可以操控SQL的基础,支持各种语言)
核心层:也被称作SQL Laye,包括安全、权限、sql解析、缓存、执行优化。我们常用的视图、存储过程、触发器等功能的实现也集中在这一层
存储引擎层:也被称为Storage Engine Layer,由多种存储引擎共同组成,负责存储和获取数据。服务器是通过存储引擎API来与它们交互的。MySQL中的存储引擎可以实现插件式管理,它提供了一系列标准的管理和服务支持。
系统层:实际存储一些数据库文件以及日志文件等

Sql执行流程图

fb20bdebc0f9fbd5460c47164f31ad4b - mysql学习笔记-底层原理详解

连接层

如上图(Sql执行流程)所示,我们Sql执行首先是客户端向MySQL服务器发送一条查询请求,与connectors交互,连接池认证相关处理。
  • 半双工通信:Mysql客户端/服务端通信协议是一种半双工的通信信道,什么叫半双工呢?半双工指的是允许客户端和服务端双方相互通信,但是同一时刻只允许存在单向通信。对于查询sql来讲,大多数都是客户端发送的查询数据包较小且为单个数据包,服务端返回的数据包较大且较大时大多拆分为多个数据包,多个数据包都需要被客户端完整接收才算是查询结束,这也是为什么在实际开发中要求我们避免使用select *以及增加limlt查询条件的原因之一。
  • Connectors(连接器)的职责就是维护上述过程中的连接通到,包括建立连接、权限表验证、维持连接和关闭连接。新建和关闭应该不用过多描述;权限表验证是从user、host、db等表查出权限,放置在连接的上下文中(也就是说已经打开的链接不受权限变更影响)。维持连接则指的是Connectors需要保障完全接收服务器响应的数据包,不能出现丢包的情况。
  • Connection Pool(连接池) :作为一个单进程多线程的应用,mysql连接也参照实现一种池化

对于mysql连接,任何时刻都有一个状态,该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。下面是这些状态的解释:

  • sleep:线程正在等待客户端发送新的请求;
  • query:线程正在执行查询或者正在将结果发送给客户端;
  • locked:在mysql服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
  • analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划;
  • copying to tmp table:线程在执行查询,并且将其结果集复制到一个临时表中,这种状态一般要么是做group by操作,要么是文件排序操作,或者union操作。如果这个状态后面还有on disk标记,那表示mysql正在将一个内存临时表放到磁盘上。
  • sorting Result:线程正在对结果集进行排序。
  • sending data:线程可能在多个状态间传送数据,或者在生成结果集,或者在想客户端返回数据。

核心层

我们之前说过,核心层包括安全权限(Management Services & Utilities 、)、sql解析(Parser)、缓存(cache)、执行优化(Optimizer)四块,不过还应该再加上SQL Interface(Sql接口),这一块主要是承担和连接层的交互的作用。所以归纳而言
名称 说明
Management Services & Utilities MySQL 的系统管理和控制工具,包括备份恢复、MySQL 复制、集群等。
SQL Interface(SQL 接口) 用来接收用户的 SQL 命令,返回用户需要查询的结果。
Parser(查询解析器) 包含验证和解析两部分,以便可以转换为MySQL优化器可以识别的数据结构或返回 SQL 语句的错误。
Optimizer(查询优化器) 验证权限和优化查询。举个例子 SELECT id, name FROM student WHERE sex = "女";,SELECT 查询先根据 WHERE 语句进行选取,而不是将表全部查询出来以后再进行sex过滤。这就属于一种优化。 SELECT 查询先根据 id 和 name 进行属性投影,而不是将属性全部取出以后再进行过滤,将这两个查询条件连接起来生成最终查询结果。所以说Mysql是使用“选取-投影-连接”策略进行查询。
Caches & Buffers(查询缓存) 查询的时候如果发现缓存中有(hash实现),就直接返回缓存中的结果。这个缓存机制是由一系列小缓存组成的,比如表缓存、记录缓存、key 缓存、权限缓存等。

Caches & Buffers(查询缓存)

对select查询结果做缓存,这个缓存可能包含多个小缓存,缓存的key值是通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释),都会导致缓存不会命中。

MySQL 8.0版本中查询缓存的功能已经被删除

Management Services & Utilities

主要为Mysql的管理服务和一些工具组件,主要作用是对数据的恢复、回滚,以及数据迁移、复制、元数据的管理。主要为以下功能
  • 数据库备份和恢复
  • 数据库安全管理,如用户及权限管理
  • 数据库复制管理
  • 数据库集群管理
  • 数据库分区,分库,分表管理
  • 数据库元数据管理

SQL Interface(SQL 接口)

主要是用来接收Sql信息和返回执行结果.大体可以分以下几类:
  • Data Manipulation Language (DML).
  • Data Definition Language (DDL).
  • 存储过程
  • 视图
  • 触发器

Parser(查询解析器)

  主要是对传递过来sql的分解,先对语法进行验证检查。语法检查通过后,解析器会查询缓存,如果缓存中有对应的语句,就直接返回结果不进行接下来的优化执行操作。

ps: 缓存中数据被修改,会被清出缓存。

Optimizer(查询优化器)

  Optimizer阶段主要就是对sql的优化了,通过系统规则选定最优的执行方案。这个过程包括选择语法、常量转换与计算、无效代码排除、AND/OR等等,必要时还可能查询存储引擎,获得最优策略。

Pluggable Storage Engine(存储引擎层)

作为Mysql最具有特色的一块地方,Mysql将存储引擎作为一个抽象类,InnoDB、MyISAM、BDB、Memory等等都是其子类。5.5版本以后以InnoDB作为其默认实现。目前除mysql以外其他大多都是单一存储引擎。

ps:存储引擎是基于表的,而不是数据库

InnoDB(默认存储引擎)

  • 完整的支持ACID事务
  • 支持行级锁
  • 支持外键
  • 使用聚集索引,索引和数据绑在一起在一个逻辑空间上,b+树叶子节点对于主键索引存储的是数据,对于辅助索引(二级索引)对应的则是主键的值
  • count扫全表
  • 必须存在唯一索引(主键)

MyISAM

  • 不支持事务,但是插入和更新更快
  • 支持表级锁
  • 不支持外键
  • 使用非聚集索引,索引和数据分离的,b+树叶子结点均存储的是实体数据文件地址的指针
  • count走变量不走全表
  • 唯一索引非必要

对于存储引擎的分析在下一篇博客会着重介绍,这里先介绍整个Mysql的架构,通过架构可以反应出一个sql的执行流程

文件系统层

 磁盘最小单位是512字节,操作系统是4KB,mysql里最小的是page(页面)有16K

因为基于存储引擎的不同,底层文件结构也会有些不同,比如InnoDB:frm是表定义文件,ibd是数据文件,而MyISAM:myd是数据文件,myi是索引文件。InnoDB还有redo Log、undo Log

结尾

本篇博客作为一个Mysql全局概览的介绍,由连接层自上而下的说明了整个Mysql的结构组成,但是Mysql核心还是在存储引擎上,所以后面会专门拿出一篇博文来介绍InnoDB默认引擎,巩固自己的知识点。

扫一扫,关注我的公众号
bc576d3b5252addd75470c521f9d660e - mysql学习笔记-底层原理详解

转载请注明:xuhss » mysql学习笔记-底层原理详解

喜欢 (0)

您必须 登录 才能发表评论!