图文详解mysql架构原理

本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于架构原理的相关内容，mysql server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，下面一起来看一下，希望对大家有帮助。

推荐学习：mysql视频教程

Mysql 架构原理

1、Mysql体系架构

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

网络连接层

服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

存储引擎层（Pluggable Storage Engines）

系统文件层（File System）

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

2、MySQL运行机制

建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。 通讯机制： 全双工：能同时发送和接收数据，例如平时打电话。 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机 单工：只能发送数据或只能接收数据。例如单行道； 线程状态：show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的； id：线程ID，可以使用kill xx； 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; 查询缓存（Cache&Buffer），这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的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 5 and a=5 基于联合索引，调整条件位置等 优化count、min、max等函数 InnoDB引擎min函数只需要找索引最左边 InnoDB引擎max函数只需要找索引最右边 MyISAM引擎count(*)，不需要计算，直接返回 提前终止查询使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据 in的优化MySQL对 in 查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)； 查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffffer）中，以后若有相同的 SQL 语句执行则直接返回结果。 如果开启了查询缓存，先将查询结果做缓存操作 返回结果过多，采用增量模式返回 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限）。 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。执行器的执行流程是这样的： select * from test where age > 10; 调用 InnoDB 引擎接口取这个表的第一行，判断 age 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

3、Mysql存储引擎

存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。

使用show engines命令，就可以查看当前数据库支持的引擎信息。

在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

InnoDB和MyISAM对比

InnoDB存储结构

从MySQL 5.5版本开始默认使用InnoDB作为引擎，它擅长处理事务，具有自动崩溃恢复的特性。下面是官方的InnoDB引擎架构图，主要分为内存结构和磁盘结构两大部分。

InnoDB内存结构

内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。

InnoDB磁盘结构

InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs。

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空 间ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1 表空间

新版本结构演变

InnoDB线程模型

InnoDB数据文件

InnoDB文件存储结构

select * from information_schema.innodb_sys_tables;

ALTER TABLE 表名 ROW_FORMAT=格式类型;

#相关参数命令
show variables like '%innodb_undo%';

show variables like '%innodb_log%';

show variables like 'log_bin';

set global log_bin = mysqllogbin; 
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

#log-bin=ON 
#log-bin-basename=mysqlbinlog 
binlog-format=ROW 
log-bin=mysqlbinlog

show binary logs; //等价于show master logs; 
show master status; 
show binlog events; 
show binlog events in 'mysqlbinlog.000001';

mysqlbinlog "文件名" 
mysqlbinlog "文件名" > "test.sql"

//按指定时间恢复 
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 
//按事件位置号恢复 
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件 
purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 
reset master; //清除所有文件