MySQL数据库架构

MySQL架构自顶向下大致可以分为连接层 , SQL层 , 存储引擎层 , 物理文件层。架构如下

连接层

-- 查看最大连接数
show variables like '%max_connections%';

客户端连接器，MySQL向外提供交互接口连接各种不同的客户端。

1. 客户端/应用程序：客户端是与MySQL系统进行交互的组件，它可以是命令行工具、图形用户界面 (GUI) 或各种编程语言中的数据库连接库。客户端负责发送SQL查询请求给MySQL服务器，并接收返回的结果。
2. 连接管理器：连接管理器负责处理客户端连接请求。它接收来自客户端的连接请求，并将它们分配给MySQL服务器线程池中的可用线程。连接管理器还负责验证用户的身份、管理连接池和控制连接数等。

特点:

（1）提供连接协议：TCP/IP 、SOCKET
（2）提供验证：用户、密码，IP，SOCKET
（3）提供专用连接线程：接收用户SQL，返回结果

 通过以下语句可以查看到连接线程基本情况
 mysql> show processlist;

SQL层

MySQL的核心,主要分为连接池(Connection Pool) , 系统管理和控制工具(Management Serveices & Utilities) , SQL接口(SQL Interface) , 解析器(Parser) , 查询优化器(Optimizer) , 查询缓存(Caches&Buffers)。

• 连接池(Connection Pool)

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

  在服务器内部，每个client连接都有自己的线程。这个连接的查询都在一个单独的线程中执行。这些线程轮流运行在某一个CPU内核(多核CPU)或者CPU中。服务器缓存了线程，因此不需要为每个client连接单独创建和销毁线程 。

• **系统管理和控制工具(Management Serveices & Utilities) **

  备份恢复、安全管理、集群管理等

• SQL接口(SQL Interface)

  用于接受客户端发送的SQL命令，并且返回查询的结果。

  例如:DDL,DML,DCL,DQL等

• 解析器(Parser)

  SQL命令传递到解析器的时候会被解析器验证和解析。

• 查询优化器(Optimizer)

  SQL语句在查询之前会使用查询优化器对查询进行优化。(当【解析树】通过解析器语法检查后，将交由优化器将其转化成执行计划与存储引擎交互。一般执行sql脚本会遵循【选取–>投影–>联接】的策略)

• 查询缓存(Caches&Buffers)

  如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据.

# 查询缓存配置情况
show variables like '%query_cache%';

• 接收上层传送的SQL语句
• 语法验证模块：验证语句语法,是否满足SQL_MODE
• 语义检查：判断SQL语句的类型
  DDL ：数据定义语言
  DCL ：数据控制语言
  DML ：数据操作语言
  DQL： 数据查询语言
  …
• 权限检查：用户对库表有没有权限
• 解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
• 优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划
  代价模型：资源（CPU IO MEM）的耗损评估性能好坏
• 执行器：根据最优执行计划，执行SQL语句，产生执行结果
  执行结果：在磁盘的xxxx位置上
• 提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
• 提供日志记录（日志管理章节）：binlog，默认是没开启的。

存储引擎层

负责根据SQL层执行的结果，从磁盘上拿数据。将16进制的磁盘数据，交由SQL结构化化成表，连接层的专用线程返回给用户。

物理文件层

主要是将数据和日志存储在运行设备的文件系统之上，并完成于存储引擎的交互，是文件的物理存储层。 主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

• 日志文件

  • 错误日志

    show variables like '%log_error%'  --默认开启
    

  • 二进制日志

    记录对数据库执行的操作 , 如:更改操作,语句的发生时间,执行时长。主要用于数据库恢复和主从复制。(不记录select,show等不修改数据库的SQL)

    show variables like '%log_bin%'; --是否开启
    show variables like '%binlog%'; --参数查看
    show binary logs;--查看日志文件
    

  • 通用查询日志

    show variables like '%general%';--记录一般查询语句
    

  • 慢查询日志

    记录所有执行时间超时的查询SQL，默认是10秒。

    show variables like '%slow_query%'; //是否开启
    show variables like '%long_query_time%'; //超时时间
    

  • 事务日志

    包含重做日志和撤销日志

• 数据文件

  • db.opt 文件

    记录当前库默认使用的字符集和校验规则。

  • frm 文件

    存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。

  • MYD 文件

    MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。

  • MYI 文件

    MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。

  • ibd文件和 IBDATA 文件

    存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。

    独享表空间使用 .ibd 文件来存放数据，且每张InnoDB 表对应一个 .ibd 文件。

    共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。

  • ibdata1 文件

    系统表空间数据文件，存储表元数据、Undo日志等 。

  • ib_logfile0、ib_logfile1 文件

    Redo log 日志文件。

• 配置文件

  用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

• pid文件

  pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，存放着自己的进程 id。

• socket文件

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

pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，存放着自己的进程 id。

• socket文件

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