MySql系列-整体架构解析

1、MySql架构

mysql整体架构图.png

MySql整体架构可细分为：网络连接层，服务层，存储引擎层和系统文件层，MySql整体架构与其它数据库架构有所不同，存储引擎是以插件的形式可插拔的，这就意味着可以定制各种存储引擎来满足更多的需求变化

2、客户端连接器

提供与MySql服务器进行连接的，目前满足绝大多数服务端编程，比如Java，.net , python等。他们都可以通过各自的API来与MySql服务器进行连接，例如java如下：

Class.forName(“com.MySQL.jdbc.Driver”);
Connection conn = DriverManager.getConnection(url, user, password); 
String id = "5";
String sql = "delete from table where id=" +  id;
Statement st = conn.createStatement();  
st.executeQuery(sql);

3、连接池

连接池就是负责与客户端进行连接与数据处理的，客户端发起连接或者SQL，那么MySql服务端必须要有一个线程去处理。

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

//临时设置最大连接数，永久修改需要修改配置文件
set GLOBAL max_connections = 200;

//查看当前连接线程的状态
SHOW FULL PROCESSLIST

执行SHOW FULL PROCESSLIST，依次对每个字段进行解释

id：线程ID，可以通过kill xxx
User：启动这个线程的用户
Host：发送请求的客户端IP和端口
db：访问的是哪个数据库
Command：该线程正在执行的命令,主要有以下几种
- Create DB：创建数据库
- Drop DB：删除数据库
- Execute：正在执行一个Statement
- Close Stmt：正在关闭一个Statement
- Query：正在执行一个语句
- Sleep：横正在等待客户端发送语句
- Quit：正在退出
- ShutDown：正在关闭服务器
Time：表示该线程处于当前状态的时间，单位是秒
State：线程状态
- Updating：正在搜索匹配记录进行修改
- Sleeping：正在等待客户端发送新请求
- Starting：正在执行请求处理
- Checking table：正在检查数据表
- Closing table：正在将表的数据刷新到磁盘中
- Locked：被其它查询锁住了记录
- Sending Data：正在处理select语句，同时将结果返回给客户端
Info：一般记录线程执行的语句

4、系统管理和控制工具

例如数据库备份，集群管理，安全管理等。数据库备份例如：mysqldump等等

5、SQL接口

用于接收客户端发送的各种SQL命令，以及返回用户需要查询的信息，比如DML，DDL，存储过程，视图，触发器等

6、解析器

将客户端发送的SQL解析成一棵树结构，然后根据MySql规则进行规则校验

select * from user where id = 1;

//大致的校验就类似以下过程，判断关键字是否在正确，表是否存在，表字段是否存在等等
select: 是查询操作，判断该select关键字是否正确
* : 根据表名获取所有字段，如果表不存在就会报错
from : 关键字，查询那张表，判断该关键字是否正确
user : 表名，判断该表是否存在，如果表不存在就会报错
where ：条件语句，判断where关键字是否正确
id = 1 : 判断id这个字段是否存在，如果不存在就报错

7、查询优化器

当SQL解析器验证通过之后，说明这条SQL是正常的。查询优化器会将SQL转为多个执行计划，选择其中一个最优的执行计划交给存储引擎。

select * from user where 1 = 1 and id = 1;
//会被优化成
select * from user where id = 1;

select * from user where a = 1 and id > a;
//会被优化成
select * from user where id > 1;

查询优化器就是要以最优的一种方式去执行该SQL，在这个过程涉及到索引的选取，但是也有可能查询优化器觉得你用这个索引还不如不用，你的索引没有用到也是在这里发生的

8、缓存

缓存客户端的请求结果，客户端发起SQL查询，如果缓存中有，那么就直接从缓存中拿，如果没有就从磁盘上获取，然后放入缓存。缓存的KEY是不仅要SQL语句一样，参数也要一样。但是从MySql8.0之后，缓存就没有了，因为只要这张表有数据变化，那么这张表相关的所有缓存都会失效，因为有Buffer Pool等内存机制，MySql在查询的时候也并不是每次都需要从磁盘上读取，可以直接从内存中读取数据，相比写缓存，还要删除缓存清理数据，倒不如不用缓存。

9、存储引擎

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

innodb：支持事务，奔溃恢复等能力，注重的是数据安全
MyISAM：不支持事物和外键，访问速度快
Memory：利用内存创建表，访问数据非常快，但是不安全，机器已重启数据全没了
Archive：归档类型引擎，仅能支持insert和select语句
Csv：以csv文件格式进行存储数据，由于文件限制，所有字段都必须是not null,不支持索引和分区
BlackHole：黑洞，只进不出，所有插入数据都不保存
Federated：可以访问远端的MySql数据库中的表，一个本地表，不保存数据，访问远程表内容

最常见的是innodb和MyISAM，后续会对innodb的结构以及各日志文件做详细的解释

10、系统文件层

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

日志文件：错误日志(Error log)，通用查询日志(General query log)，二进制日志(binary log), 慢查询日志(Slow query log)等
- 错误日志是否开启：show variables like '%log_error%'
- 通用查询日志是否开启：show variables like '%general%'
- 二进制文件是否开启：show variables like '%log_bin%'
- 二进制文件参数查看：show variables like '%binlog%'
- 查看日志文件：show binary logs
- 慢查询日志是否开启：show variables like '%slow_query%'
- 记录满查询日志的时长：show variables like '%long_query_time%'
配置文件：用户存放与MySql服务器配置相关的文件,比如my.ini,my.cnf等
pid文件：pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。
socket文件：socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL

数据文件：

db.opt：记录这个数据库的默认字符集和校验规则
frm：存储与表相关的元数据信息，包括表结构定义，每张表都会有一个frm文件
MYD：MyISAM存储引擎专用，存放表的数据，每一个表都会有一个.MYD文件
MYI：MyISAM存储引擎专用，存放表索引相关的数据，每张表都会有一个对应的.MYI文件
ibd/IBDATA：存放innodb的数据文件，包括索引信息。innodb有二种表空间方式：独享表空间/共享表空间，独享表空间用的是.ibd文件存放数据,一张表对应一个.ibd文件，共享表空间使用的是.ibdata来存放数据，所有表公用一个(或多个，自行配置).ibdata文件