MYSQL体系结构_简述mysql数据库的组成-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_46091713/article/details/136415150

MYSQL的特点

1.安装简单，部署迅速，适合大批量快速部署
2.易于扩展，扩展性能极佳
3.架构灵活，可以根据业务特点配置适合自己的mysql集群
4.开源，可以根据自己的业务需求进行二次开发
5.使用广泛，几乎所有的互联网公司都在使用mysql数据库
6.对于OLTP业务，可以进行良好的支撑

MYSQL官方版本

mysql8.0下载地址：
https://dev.mysql.com/downloads/mysql/8.0.html#downloads

MYSQL服务器结构

mysql由以下几个部分组成：
1.连接层（连接池+连接器）
2.管理服务和工具组件
3.sql借口组件（sql interface）
4.查询分析器（Parser）
5.优化器组件（Optimizer）
6.缓冲组件（Cache & Buffer）
7.插件式存储引擎（Pluggable Storage Engines）
8.数据库物理文件（File Sysgtem & Logs）
MYSQL从概念上分为四层，这四层自顶向下分别时网络连接层，服务层（核心层），存储引擎层，系统文件层，我们开始逐一分析。
在这里插入图片描述
网络连接层
主要负责连接管理，授权认证，安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到mysql服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登陆认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是mysql所特有的技术。
mysql主要提供两种连接方式：TCP/IP,SOCKET建立一个与SQL层连接的线层。

服务层（MYSQL SERVER）
第二层服务层时mysql的核心，mysql的核心服务层都在这一层，查询解析，sql执行计划分析，sql执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过层，触发器，视图等。
连接池：管理，缓冲用户的连接，线层处理等需要缓存的需求
管理服务和工具组件：系统管理和控制工具，例如备份恢复，mysql复制，集群等。
SQL接口：接受用户的sql命令，并返回用户需要查询的结果
查询解析器：SQL命令传递到解析器的时候会被解析器验证和解析（权限，语法结构）
查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化
缓存：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
而我们延伸一下SQL接口，查询解析器，查询优化器，缓存又组成了独立的一层叫SQL层。
那么这一层的主要工作职责再细化一些就是：
1.接受连接层传来的SQL语句
2.检测语法
3.检测语义（DML DDL DCL和基本MYSQL命令）
4.解析器：解析整条SQL语句生成多种执行计划
5.优化器：选择解析器生成的多种执行计划中最高效的一种
6.执行器：将优化器的最优一种执行方式执行
1）和存储引擎层建立连接–提供一个线程
2）接收存储引擎层传来的结构化成表的数据
7.将数据写入缓存
8.记录相关日志-binlog

存储引擎层
负责mysql中数据的存储与提取。服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。mysql采用插件式的存储引擎。mysql为我们提供了许多存储引擎，每种存储引擎又不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码得到自己想要达到的性能。
各个存储引擎的描述参考下文。
特点：
1.mysql采用插件式的存储引擎。
2.存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
3.不管表采用什么样的存储引擎，都会在数据区，产生对应的文件（表结构睇你描述文件）

系统文件层
系统文件层，主要是数据库系统在操作系统的文件系统上生成的各种文件，这些文件里主要包含了容纳表数据，日志（redo，undo，binlog），索引数据，错误日志，查询记录，慢查询信息，pid信息，socket信息等的文件。

该层将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

show engines;
结果：
Engine			Support			Comment			Transactions	XA		Savepoints
InnoDB			DEFAULT								YES			YES			YES
MRG_MYISAM		YES									NO			NO			NO
MEMORY			YES									NO			NO			NO
BLACKHOLE		YES									NO			NO			NO
MyISAM			YES									NO			NO			NO
CSV				YES									NO			NO			NO
ARCHIVE			YES									NO			NO			NO
....					....						....				.....

InnoDB存储引擎
mysql 5.5及以后版本的默认存储引擎。设计目标主要面向OLTP应用
特点：
1.支持事务（ACID），支持4个事务隔离级别，支持多版本读。
2.行级锁定（更新时一般锁定当前行）
3.读写阻塞与事务隔离级别相关
4.具有非常高效的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键以Cluster（聚集）方式存储，组成一个平衡树。
6.所有Secondary Index 都会保存主键信息
7.支持分区，表空间，类似oracle数据库
8.支持外键约束，5.6之后支持全文索引。

InnoDB存储引擎
在这里插入图片描述
在技术上，InnoDB是一套放在MySQL后台的完整数据库系统，InnoDB在主内存中建立其专用的缓冲池，用于高缓冲数据和索引。InnoDB把数据和索引存放在表空间里，可能包含多个文件，这与其他的不一样，举例来说，在MyISAM中，表被存放在单独的文件中。InnoDB表的大小只受限于操作系统的文件大小，每个表可以使用独立的表空间，只需要启用选项 innodb_file_table.
由于innodb使用磁盘作为持久化介质，那么在数据处理的过程中，就有可能无法将所有的数据，都从磁盘读入到内存缓冲区，它只是把最近常用的数据缓存到内存中，然后在特定的时间对内存和硬盘数据进行同步，来保证双向的数据一致性。所以在这种情况下，磁盘和内存的交互就成了数据库主要的性能瓶颈。
前面已经说过，InnoDB存储引擎是基于磁盘存储的，也就是说数据都是存储在磁盘上的，由于内存读写速度和磁盘读写速度之间的巨大差异，所以InnoDB引擎使用缓冲池技术来提高数据库的整体性能。
INNODB缓冲池
缓冲池简单来说就是一块内存区域，在数据库中进行读取页的操作，首先将从磁盘读到的页存放到缓冲池中，下一次读取相同的页时，首先判断该页是不是在缓冲池中，若在，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。对于数据库中页的修改操作，首先修改在缓冲池中页然后再以一定的频率刷新到磁盘，并不是每次发生改变就刷新回磁盘。这是通过“CHECKPOINT”机制实现的。

缓冲池中缓冲的数据页类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，InnoDB的锁信息，数据字典信息等。索引页和数据页占缓冲池的很大一部分，在InnoDB中，缓冲池中的页大小默认为16KB。而缓冲池中的页对应磁盘文件中的物理页。
在这里插入图片描述

在这里插入图片描述

MyISAM存储引擎
mysql 5.5版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到MyISAM存储引擎。
特点：
1.做count（*）查询的时候比InnoDB快。
2.数据（MYD）和索引（MYI）分开存储，只对索引进行缓存，虽然key_buffer可以大幅提高性能，减少磁盘IO，但对数据不缓存
3.表级锁
4.不支持事务
5.适合读业务比较多的生产环境，比如BLOG等，读的速度比较快
6.占用资源比较少，服务器硬件不好时，可以考虑使用。
7.数据恢复没有InnoDB引擎恢复的完美
8.支持全文索引，不支持外键约束。

MyISAM和InnoDB对比
1.InnoDB支持事务，MyISAM不支持，这一点非常重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2.MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
3.InnoDB支持外键，MyISAM不支持
4.MyISAM需要指定，InnoDB默认引擎
5.InnoDB中不保存表的行数，如 select count() from table 时，InnoDB需要扫描一边整个表来计算有多少行，但是MyISAM只要简单的独处保存好的行数即可。注意的是，当count()语句包含where条件时，MyISAM也需要扫描整个表。
6.对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是MyISAM表中可以和其他字段一起建立联合索引。
7.清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
8.InnoDB支持行锁而MyISAM不支持。

Memory存储引擎
数据都是存储在内存中，IO效率要比其他引擎高很多，服务器重启数据会丢失，内存数据表默认只有16M，一般我们不会使用到Memory存储引擎。
特点：
1.支持hash索引，Btree索引，默认hash（查找复杂度o（1））
2.字段长度都是固定长度 varchar（32）= char（32）
3.不支持大数据存储类型字段如blog，text
4.表级锁
应用场景：
等值查找热度较高数据
在排序，分组等操作中，当数据量小于16M（默认大小），由查询优化器建立的临时表就是Memory类型。
Archive存储引擎
压缩协议进行数据的存储，数据存储为ARZ文件格式。
特点：
1.只支持insert和select两种操作
2.只允许自增ID列建立索引
3.行级锁
4.不支持事务
5.数据占用磁盘少
应用场景：
1.日志系统
2.大量的设备数据采集
CSV存储引擎
数据存储以CSV文件。
特点：
1.不能定义索引，列定义必须为NOT NULL,不能设置自增列，不适用大表或者数据的在线处理
2.CSV数据的存储用逗号隔开，可直接编辑CSV文件进行数据的编排，数据安全性低。编辑之后，要生效使用 flush table xxx 命令
应用场景：
1.数据的快速导入
2.表格直接转换成CSV

文件结构大

物理文件主要包括：日志文件，数据文件，配置文件，PID文件 SOCKET文件

MYSQL5.7/8.0支持的几种日志文件

日志类型	日志信息
Error log(错误日志)	启动，运行或者停止mysql时遇到的问题
General query log(一般或者通用查询日志)	从已建立的客户端连接收到的语句
Binary log（二进制日志）	用来记录操作mysql数据库中的写入性操作（增删改，但不包括查询）
Relay log（中继日志）	从复制源服务器收到的数据更改
Slow query log（慢查询日志）	执行时间超过long_query_time秒的查询
DDL log（metadata log）（DDL语句日志）	DDL语句日志（元数据操作）

错误日志
mysql错误日志记录mysql运行过程中较为严重的警告和错误信息，以及mysql每次启动和关闭的详细信息。
mysql错误日志默认时开启的。可以通过mysql配置文件中的log-error=/var/log/mysqld.log配置，修改错误日志的配置信息。
可以通过如下sql查看错误日志的详细信息：

show variables like '%log_err%';
结果：
-------------------------------------------------
variable_name			|		value
------------------------------------------------
binlog_error_action		|	ABORT_SERVER
log_error				|	/data/mysql_log/mysql-error-3306.log
log_error_verbosity		|	3
------------------------------------------------

一般或通用查询日志
记录已连接MYSQL数据库的客户端所执行的语句。
可以通过如下sql查看当前通用日志是否开启：

SHOW VARIABLES LIKE '%general%';
结果：
--------------------------------------------------------
variable_name 		|	value
-------------------------------------------------------
general_log			| OFF
general_log_file	| /data/mysql_data/mysql57.log
-------------------------------------------------------

--开启通用查询日志
set global general_log = on;
--关闭通用查询日志：
set global general_log = off;

当日志时间和系统时间不一致时的处理方式
在mysql 5.7.2新增了 log_timestamps这个参数，该参数主要时控制error log，general query log等记录日志的显示时间参数ÿ