mysql 整体架构

mysql 一共分为四层，分别是网络层、服务层、存储引擎层、文件系统层，客户端 不属于 mysql_server 的范畴，它可以是各种变成语言，也可以是各种可视化工具，即一切能和 MySQLl server 连接成功的模块都为 “客户端”。

一、网络层。

• mysql 使用 tcp/ip 来进行网络链接 理论上所有能访问 tcp/ip 的编程语言都可以与它进行连接

• mysql 还支持使用 socket 进行连接 一般使用较少

连接流程

clientserver发起tcp请求 三次握手建立session 连接查询用户 鉴权鉴权成功 授权连接建立成功clientserver

连接之后

mysql 会安排一条线程维护当前的客户端连接，这条线程表示这当前连接正在进行什么工作 可以通过 show processlist 来查看当前正在运行的所有线程 默认已半双工的通讯机制进行工作，此外还有全双工和单工机制

• Id：当前线程的 ID 值，可以利用这个 ID，使用 kill 强杀线程。

• User：当前线程维护的数据库连接，与之对应的用户是谁。

• Host：与当前线程保持连接关系的客户端地址（IP+Port）。

• db：目前线程在哪个数据库中执行 SQL。

• Command：当前线程正在执行的 SQL 类型，如： Create DB：正在执行创建数据库的操作。 Drop DB：正在执行删除数据库的操作。 Execute：正在执行预编译的 SQL（PreparedStatement）。 Close Stmt：正在关闭一个 PreparedStatement。 Query：正在执行普通的 SQL 语句。 Sleep：正在等待客户端发送 SQL 语句。 Quit：当前客户端正在退出连接。 Shutdown：正在关闭 MySQL 服务端。

• Time：表示当前线程处于目前状态的时间，单位是秒。

• State：表示当前线程的状态，有如下几种： Updating：当前正在执行 update 语句，匹配数据做修改操作。 Sleeping：正在等待客户端发送新的 SQL 语句。 Starting：目前正在处理客户端的请求。 Checking table：目前正在表中查询数据。 Locked：当前线程被阻塞，其他线程获取了执行需要的锁资源。 Sending Data：目前执行完成了 Select 语句，正在将结果返回给客户端。

• Info：一般记录当前线程正在执行的 SQL，默认显示前一百个字符，查看完整的 SQL 可以使用 show full processlist; 命令。

数据库连接池

作用：

用于线程复用使用 因为创建线程对于操作系统来说是一笔很大的开销,限制线程最大连接数量，复用线程，管理线程等。

具体使用：

可以通过参数 max-connections 对线程的最大连接数量进行限制 ，合理的限制可以使 mysql 的性能最大化

主要命令：

• show variables like '%max_connections%';：查询目前 DB 的最大连接数。

• set GLOBAL max_connections = 200;：修改数据库的最大连接数为指定值。

• show status like "Threads%" ：查看当前的连接数量 Threads_cached：目前空闲的数据库连接数。 Threads_connected：当前数据库存活的数据库连接数。 Threads_created：MySQL-Server 运行至今，累计创建的连接数。 Threads_running：目前正在执行的数据库连接数。

二、系统服务层

用于 mysql 的请求解析，语义分析，sql 优化，缓存以及存放内置函数，所有跨引擎(即所有搜索引擎都拥有的功能)，在这一层实现，如存储过程，触发器，视图等一系列服务。 ![[Pasted image 20221009141926.png]]

2.1、SQL 接口组件

sql 接口组件会作为客户端传递 sql 的入口和出口 主要是对 sql 的类型进行划分和触发器

sql 类型

• DML：数据库操作语句，比如 update、delete、insert 等都属于这个分类。

• DDL：数据库定义语句，比如 create、alter、drop 等都属于这个分类。

• DQL：数据库查询语句，比如最常见的 select 就属于这个分类。

• DCL：数据库控制语句，比如 grant、revoke 控制权限的语句都属于这个分类。

• TCL：事务控制语句，例如 commit、rollback、setpoint 等语句属于这个分类。

触发器

触发器是不需要外部进行手动调用，而是 mysql 触发某个时间之后会自动将调用对应的触发器 类似 aop 切面

2.2、SQL 解析器

类似 jdk 编译器 会对 sql 进行解析 分析 sql 错误等

2.3、优化器

优化器会对 sql 进行优化 例如：选择合适的索引，选择合适的 join 方式等 优化完成以后 线程会调用存储引擎的 api 进行 sql 查询

2.4、缓存和缓冲

mysql 会对查询结果进行缓存，当下次查询结果相同的时候会直接在缓存中命中对应的查询结果，而不是直接走硬盘查询，效率更高！

使用方法（5.7 版本，高版本因为查询缓冲区(非缓冲区)命中不高已经移除）

• show global variables like "%query_cache_type%";：查询缓存是否开启。

• show global variables like "%query_cache_size%";：查询缓存的空间大小。

• show status like'%Qcache%'; ：查询缓存相关的统计信息 Qcache_free_blocks：查询缓存中目前还有多少剩余的 blocks。 Qcache_free_memory：查询缓存的内存大小。 Qcache_hits：表示有多少次查询 SQL 命中了缓存。 Qcache_inserts：表示有多少次查询 SQL 未命中缓存然后走了磁盘。 Qcache_lowmem_prunes：这个值表示有多少条缓存数据从内存中被淘汰。 Qcache_not_cached：表示由于自己设置了缓存规则后，有多少条数据不符合缓存条件。 Qcache_queries_in_cache：表示当前缓存中缓存的数据数量。 Qcache_total_blocks：当前缓存区中 blocks 的数量。

备注

缓冲区同时和存储引擎有关 不通的存储引擎有着不同的缓冲区 高版本移除了查询缓冲区 因为使用场景少 且更多是使用 redis 等手动缓存数据。但是并未移除缓冲区 存储引擎在查找的时候还是会先查找缓冲区的数据。

三、存储引擎层

负责具体的数据操作和 sql 执行工作

备注：

mysql 的存储引擎为热插拔式的 存在很多的存储引擎实现(被定义为一个一个接口) 常用的有：InnoDB 、MyISAM 可以通过 show variables like '%storage_engine%' 来查看当前使用的存储引擎 其他的闭源数据库大多只有厂家自己实现的闭源存储引擎

四、文件系统层

文件系统层本质上是计算机的一个文件系统 ， 还有一些 数据库运行使用需要的关键文件，配置文件，表结构定义文件，索引文件和日志文件等。 与上层的操作引擎交互，负责数据的最后落盘(存储到磁盘上)

4.1、日志模块

日志类型

• binlog：二进制日志，主要记录 MySQL 数据库的所有写操作（增删改）。

• redo-log：重做/重写日志，MySQL 崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB 专有的）。

• undo-logs：撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。

• error-log：错误日志：记录 MySQL 启动、运行、停止时的错误信息。

• general-log：常规日志，主要记录 MySQL 收到的每一个查询或 SQL 命令。

• slow-log：慢查询日志，主要记录执行时间较长的 SQL。

• relay-log：中继日志，主要用于主从复制做数据拷贝。

4.2、数据模块

数据模块即是 mysql 将我们的数据存放在具体的某种文件格式当中

类型

• db.opt 文件：主要记录当前数据库使用的字符集和验证规则等信息。

• .frm 文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。

• .MYD 文件：用于存储表中所有数据的文件（MyISAM 引擎独有的）。

• .MYI 文件：用于存储表中索引信息的文件（MyISAM 引擎独有的）。

• .ibd 文件：用于存储表数据和索引信息的文件（InnoDB 引擎独有的）。

• .ibdata 文件：用于存储共享表空间的数据和索引的文件（InnoDB 引擎独有）。

• .ibdata1 文件：这个主要是用于存储 MySQL 系统（自带）表数据及结构的文件。

• .ib_logfile0/.ib_logfile1 文件：用于故障数据恢复时的日志文件。

• .cnf/.ini：MySQL 的配置文件，Windows 下是 .ini，其他系统大多为 .cnf。

五、客户层

一般情况下 客户层也会自己维护一个线程池 因为同数据库建立连接，销毁链接是很大的资源浪费，一般来说常见的编程语言都会自己创建维护数据库连接的线程池

客户端线程池同服务端线程池之间的区别

MySQL连接池维护的是工作线程，客户端连接池则维护的是网络连接。两者都是利用池化技术复用资源。