MySQL 架构体系和运行机制

·  阅读 142

MySQL 架构

MySQL 的架构体系,如下图:

MySQL Server 架构自顶向下大致可以分为网络连接层、服务层、存储引擎层和系统文件层。

网络连接层

网络连接层要是客户端连接器(Client Connectors):它提供了与 MySQL 服务器建立连接的支持。目前支持几乎所有主流的服务端编程技术。例如常见的:Java/C/Python/.NET 等,它们通过各自的 API 技术与 MySQL 建立连接。

服务层(MySQL Server)

服务层是 MySQL Server 的核心,主要包含六个部分:

  • 系统管理
  • 控制工具
  • 连接池
  • SQL 接口
  • 解析器
  • 查询优化器
连接池

主要负责存储和管理客户端与数据库的连接,一个线程负责管理一个连接。

系统管理和控制工具

例如:备份恢复、安全管理、集群管理等。

SQL 接口(SQL Interface)

用于接受客户端发送的各种 SQL 命令,并且返回用户需要查询的结果集。比如:DML、DDL、存储过程、视图、解析器等。

解析器(Parser)

负责将请求的 SQL 解析生成一颗“解析树”。然后跟姐姐一些 MySQL 规则进一步检查解析树是否合法。

查询优化器(Optimizer)

当“解析树”通过解析器语法检查后,将交给优化器将其转化成执行计划,然后与存储引擎交互。

我们看个案例:

-- 查询的 SQL
select uid,name from user where gender=1;
-- 选取-》投影-》联接策略
复制代码

上面是一个查询的 SQL,它是如何执行的?

  • select 先根据 where 语句进行选取,并不是查询出全部数据再过滤
  • select 查询根据 uid 和 name 进行属性的投影,并不是取出所有的字段
  • 将前面选取和投影联接起来,最终生成查询结果

缓存

缓存机制是由一系列小缓存组成的。比如:表缓存、记录缓存、权限缓存、引擎缓存等。如果查询缓存有命中的结果,查询语句就可以直接去查询缓存中的数据。

存储引擎层(Pluggable Storage Engines)

MySQL 的存储引擎是插件式的。主要负责 MySQL 中数据的存储与提取以及与底层系统文件进行交互。服务器中的查询执行引擎会通过接口与存储引擎进行通信(接口自动屏蔽了不同存储引擎之间的差异)。

系统文件层(File System)

该层负责将数据库的数据和日志存储在文件系统上,并完成与存储引擎的交互,该层是文件的物理存储层。主要包含:

  • 日志文件
  • 数据文件
  • 配置文件
  • pid 文件
  • socekt 文件等
日志文件

其中日志文件也分类别:

  • 错误日志(Error Log):默认是开启的,可以通过命令进行配置(show variables like '%log_error%'
  • 通用查询日志(General Query Log):记录一般的查询语句,可以通过命令进行配置(show variables like '%general%'
  • 二进制日志(Binary Log):该日志记录了对 MySQL 数据库执行的更改操作,并且记录了语句的发生时间、执行时长;但是它不记录 select、slow 等不修改数据库的 SQL。
    • 查看是否开启:show variables like '%log_bin%'
    • 查看参数:show variables like '%binlog%'
    • 查看日志文件:show binary logs
  • 慢查询日志(Slow Query Log):记录了所有执行时间超时的 SQL,默认是 10 秒
    • 查看是否开启:show variables like '%slow_query%'
    • 查看配置的超时的时间阈值:show variables like 'long_query_time'

配置文件

存放 MySQL 所有的配置信息文件,比如:my.cf、my.ini 等。

数据文件
  • db.opt 文件
    • 记录了这个库默认使用的字符集和校验规则。
  • frm 文件
    • 存储与表相关的元数据(meta)信息。包括:表结构的定义信息等,每一张表度会有一个 frm 文件。
  • MYD 文件
    • MyISAM 存储引擎专用的文件,存放表中的数据(data),每一张 MyISAM 表对应一个 .MYD 文件。
  • MYi 文件
    • MyISAM 存储引擎专用的文件,主要存放表的索引相关的信息,每一张 MyISAM 表对应一个 .MYI 文件。
  • ibd 文件和 .ibdata 文件
    • 存放 InnoDB 的数据文件(包括索引)。
    • InnoDB 有两种表空间方式:独享表空间、共享表空间
    • 独享表空间使用 .ibd 文件存放数据,每一张 InnoDB 表对应一个 .ibd 文件
    • 共享表空间使用 .ibdata 文件,所有表共同使用一个或多个 .ibdata 文件
  • ibdata1 文件
    • 系统表空间中的数据文件,主要存储元数据、Undo 日志等信息
  • ib_logfile0、ib_logfile1 文件
    • Redo log 日志文件
Pid 文件

pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件,主要存放自己的进程 id。

Socket 文件

socket 文件也是在 Unix/Linux 环境下才有的,用户在 Unix/Linux 环境下的客户端连接可以不通过 TCP/IP 网络,而直接使用 Unix Socket 来连接 MySQL。

MySQL 的运行机制

建立连接

通过客户端/服务端的通信协议与 MySQL 建立连接。MySQL 客户端和服务端的通信方式是“半双工”。对于每一个 MySQL 的连接,时刻都有一个线程状态来标识这个连接正在做什么。

通讯机制
  • 全双工:能同时发送和接收数据(例如:平时打电话)
  • 半双工:在某一时刻,要么发送数据,要么接收数据,不能同时进行。例如:早起对讲机
  • 单工:只能发送数据或只能接收数据。例如:单行道

上面提到了我们可以根据线程状态来看到数据库连接在做什么,我们可以通过命令 show processlist 、show full processlist( 查看用户正在运行的线程信息,root 用户可以查看所有线程,其它用户只能查看自己命令 进行查看,其相关的参数如下:

  • id:线程 ID
  • user:启动这个线程的用户
  • host:发送请求的客户端的 IP 和端口号
  • db:表示当前命令在哪个库执行
  • command:该线程正在执行的操作命令
    • Create DB 表示正在创建数据库
    • Drop DB 表示正在删除数据库
    • Execute 表示正在执行一个 Prepared Statement
    • Clost Stmt 表示正在关闭一个 Prepared Statement
    • Query 表示正在执行一个语句
    • Sleep 表示正在等待客户端发送语句
    • Quit 表示正在退出
    • Shutdown:表示正在关闭服务器
  • time:表示该线程处于当前状态的时间,单位是秒
  • state:表示的是线程状态
    • Updating:正在搜索匹配记录,进行修改
    • Sleeping:正在等待客户端发送新请求
    • Starting:正在执行请求处理
    • Checking table:正在检查数据表
    • Closing table:正在将表中数据刷新到磁盘中
    • Locked:被其他查询锁住了记录
    • Sending Data:正在处理 Select 查询,同时将结果返回给客户端
  • info:一般记录线程执行的语句,默认显示前 100 个字符。

查询缓存(Cache&Buffer)

如果开启了查询缓存且在查询缓存中查询到完全相同的 SQL 语句时,可以将查询到的结果直接返回给客户端。如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句,则会通过解析器进行语法语义的解析,生成“解析树”:

  • 缓存 Select 查询的结果和 SQL 语句
  • 执行 Select 查询时,先查询缓存,判断是否存在可用的记录集,看匹配条件是否完全相同(包括:参数值),这样才会命中缓存数据。
  • 即使开启查询缓存,以下 SQL 也不能缓存
    • 查询语句中使用了 :SQL_NO_CACHE
    • 查询的结果大于 query_cache_limit 的阈值
    • 查询中有一些不确定参数,比如:now()

查看查询缓存是否启用(包含:是否启用、空间大小、阈值等)

  • show variables like '%query_cache%'

查看更详细的缓存参数(包含:可用缓存空间、缓存块、缓存了多少等)

  • show status like 'QCache%'

解析器(Parser)

将客户端发送过来的 SQL 进行语法解析,生成“解析树”。然后,预处理器会根据 MySQL 规则进一步检查“解析树”是否合法,最后生成新的“解析树”。

例如:

  • 检查数据表和数据列是否存在
  • 数据列名称和别名,看看它们是否有歧义

查询优化器(Optimizer)

根据“解析树”生成最优的执行计划。MySQL 使用很多优化策略生成最优的执行计划,可以分为两类:

  • 静态优化(编译时优化)
  • 动态优化(运行时优化)

其中策略有如下几种:

等价变换策略
  • 5=5 and a>5 改成 a >5
  • a<b and a=5 改成 b>5 and a=5
  • 基于联合索引,调整查询条件的位置等
优化 count、min、max 等函数操作
  • InnoDB 引擎 min 函数 只需要查找索引的最左边(数据结构是树,树的特性左子树<root<右子树
  • InnoDB 引擎 max 函数 只需要查找索引的最右边
  • MyISAM 引擎 count(),不需要计算,可以直接返回(MyISAM 引擎中有 存放了 count() 结果的位置,可以直接拿到)
提前终止查询
  • 使用了 limit 查询,获取 limit 所需的数据,就不在继续遍历后面的数据
in 的优化
  • MySQL 对 in 查询,会先进行排序,再采用二分查找法查找数据。比如:where id in(2,1,3),会变成 where id in(1,2,3)

查询执行引擎

执行引擎负责执行 SQL 语句。此时查询执行引擎会根据 SQL 语句中表的存储引擎类型,以及对应的 API 接口与底层存储引擎缓存或物理文件的交互,得到查询结果并返回给客户端。

如果开启了查询缓存,这个时候引擎会将 SQL 语句以及对应的结果完整的保存到查询缓存中,后面如果有相同的 SQL 语句执行则会直接返回结果(返回的结果过多时,采用增量模式返回)。

以上简单介绍了 MySQL 的架构体系和运行机制,你学废了没。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改