6

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

 3 years ago
source link: https://www.cnblogs.com/qiuzhiqiang/p/16040731.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
前言
我相信每一个程序员都避免不了和数据库打交道，其中Mysql以其轻量、开源成为当下最流行的关系型数据库。Mysql5.0以前以MyISAM作为默认存储引擎，在5.5版本以后，以InnoDB作为默认存储引擎，相比MyISAM，InnoDB完整的支持ACID事务特性,同时支持行级锁，支持事务这一特性也决定了InnoDB代替MyISAM成为主流存储引擎的一大决定性因素。

Mysql系统架构图

mysql整体包含四大部分：

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

Sql执行流程图

连接层

如上图(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默认引擎，巩固自己的知识点。

扫一扫，关注我的公众号

__EOF__


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK