Mysql架构原理
文章内容输出来源:拉勾教育Java高薪训练营;
01 | Mysql逻辑架构
网络连接层
客户端连接器:提供与Mysql服务器监理连接的支持。各个语言使用各自的API技术与MySQL建立连接。
服务层
服务层是MySQL的核心,主要包含以下六个部分:
- 连接器:管理连接,权限验证
- 系统管理和控制工具: 提供备份恢复、安全管理、集群管理等功能。
- SQL接口:接收客户端发送的各种SQL命令,并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
- 解析器:将客户端发送的SQL进行语法解析,生成"解析树"。预处理器根据规则进一步检查“解析树”是否合法,最终生成新的“解析树”。
- 查询优化器:当“解析树”通过解析器语法检查后,将交由优化器将其转换成执行计划,然后与存储引擎交互。
- 缓存:缓存机制是由一系列小缓存组成,比如表缓存、记录缓存、权限缓存、引擎缓存等(注:mysql8.0之后的版本移除了缓存)。
存储引擎层
存储引擎负责MySQL中数据的存储和提取,与底层系统文件进行交互。MySQL存储引擎是插件式的,服务器中的查询执行引擎通过接口与存储引擎进行通信,屏蔽了不同引擎之间的差异。
系统文件层
系统文件层主要负责将数据库的数据和日志存储在文件系统之上,并完成与存储引擎的交互,是文件的物理存储层。
02 | Mysql查询处理过程
① 建立连接
通过客户端/服务器通信协议与MySQL建立连接,客户端和服务端的通信方式为“半双工”。对于一个MySQL连接,时刻都有一个线程来标识当前连接正在做什么。
通信机制:
- 全双工:能同时发送和接收数据
- 半双工:某一个时刻,要么发送数据,要么接收数据,但不能同时进行
- 单工:只能发送数据或只能接收数据
查看线程状态:
建立连接之后可以通过以下语句查看正在运行的线程信息
show processlist; // 查看用户正在运行的线程信息
字段说明:
- id: 线程ID
- User:启动这个线程的用户
- Host:发送请求的客户端IP和端口号
- db:当前命令在哪个库执行
- Command:该线程正在执行的操作命令
- Create DB:正在创建库操作
- Drop DB:正在删除库操作
- Execute:正在执行一个PreparedStatement
- Close Stmt:正在关闭一个PreparedStatement
- Query:正在执行一个语句
- Sleep:正在等待客户端发送语句
- Quit:正在退出
- Shutdown:正在关闭服务器
- Time:表示该线程处于当前状态的时间,单位是秒。
- State:线程状态
- Updating:正在搜索匹配记录,进行修改
- Sleeping:正在等待客户端发送新请求
- Starting:正在执行请求处理
- Checking table:正在检查数据表
- Closing table : 正在将表中数据刷新到磁盘中
- Locked:被其他查询锁住了记录
- Sending Data:正在处理Select查询,同时将结果发送给客户端
- Info:记录线程执行的语句,默认显示前100个字符。想查看完整的使用show full processlist;
客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时
② 查询缓存
如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句,则将查询结果直接返回给客户端;如果没有开启查询缓存或者没有查询到完全相同的SQL语句则会由解析器进行语法语义分析,并生成“解析树”。
查看缓存是否开启:
show variables like '%query_cahce%'; //查看查询缓存是否启用,空间大小,限制等
show status like 'Qcache%'; //查看更详细的缓存参数,可用缓存空间,缓存块,缓存多少等
③ 分析器
MySQL需要知道做什么,因此需要对SQL进行解析,分析器会将客户端发送的SQL进行语法解析,生成“解析树”。预处理器根据一些MySQL规则进一步检查其合法性,最终生成新的“解析树”。
④ 查询优化器
根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划,可以分为两类:静态优化(编译时)和动态优化(运行时)
- 等价变换策略: 如基于联合左右,调整条件位置等
- count、min、max等函数优化:InnoDB引擎min函数只需要找索引的最左边max函数只需要找最右边
- 提前终止查询:使用了limit查询,如过去了limit所需的数据,则不在继续遍历后续数据
- in优化:MySQL对in查询,会先进行排序,再采用二分法查找数据。比如where id in (2,1,3),变成 in (1,2,3)
⑤ 执行器
执行SQL语句,此时查询执行引擎会根据 SQL 语句中表的存储引擎类型,以及对应的API接口与底层存储引擎缓存或者物理文件的交互,得到查询结果并返回给客户端。若开启用查询缓存,这时会将SQL 语句和结果完整地保存到查询缓存(Cache&Buffer)中,以后若有相同的 SQL 语句执行则直接返回结果。
03 | MySQL存储引擎
InnoDB和MyISAM对比
- 事务和外键
- InnoDB支持事物和外键,具有安全性和完整性,适合大量insert或update操作
- MyISAM不支持事物和外键,提供高速存储和检索,适合大量的select查询操作
- 锁机制
- InnoDB支持行级锁,锁定指定记录。基于索引来加锁实现
- MyISAM支持表级锁,锁定整张表
- 索引结构
- InnoDB使用聚集索引(聚簇索引),索引和记录在一起存储,既缓存索引,也缓存记录。
- MyISAM使用非聚集索引(非聚簇索引),索引和记录分开。
- 并发处理能力
- MyISAM使用表锁,会导致写操作并发率低,读之间并不阻塞,读写阻塞。
- InnoDB读写阻塞可以与隔离级别有关,可以采用多版本并发控制(MVCC)来支持高并发
- 存储结构
- InnoDB表对应两个文件,一个.frm表结构文件,一个.ibd数据文件。InnoDB表最大支持64TB
- MyISAM表对应三个文件,一个.frm表结构文件,一个MYD表数据文件,一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。
- 使用场景
MyISAM
- 不需要事务支持(不支持)
- 并发相对较低(锁定机制问题)
- 数据修改相对较少,以读为主
- 数据一致性要求不高
InnoDB
- 需要事务支持(具有较好的事务特性)
- 行级锁定对高并发有很好的适应能力
- 数据更新较为频繁的场景
- 数据一致性要求较高
- 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,减少磁盘IO
本文地址:https://blog.csdn.net/soul_code/article/details/107178944
推荐阅读
-
MySQL5.7完全卸载步骤详解
-
概述MySQL统计信息
-
mysql 5.7以上版本安装配置方法图文教程(mysql 5.7.12mysql 5.7.13mysql 5.7.14)
-
MySQL常见内存不足启动失败的完美解决方法
-
随机生成八位优惠码并保存至Mysql数据库
-
Mysql5.7中使用group concat函数数据被截断的问题完美解决方法
-
Mysql5.7.17 winx64.zip解压缩版安装配置图文教程
-
MySQL SQL语句分析与查询优化详解
-
Windows 64 位 mysql 5.7以上版本包解压中没有data目录和my-default.ini及服务无法启动的快速解决办法(问题小结)
-
解决mysql ERROR 1045 (28000)-- Access denied for user问题