(一)MySQL全解----MySQL架构

前言

对于程序员，相信MySQL这个词肯定不陌生。MySQL是面试必问的一个知识点，所以MySQL是必须要掌握的核心技术

本专栏是基于MySQL基础知识的进阶和MySQL底层实现的剖析，专栏中涉及到的MySQL基础知识以及基础概念不过多介绍，读者可自行查阅文档

本篇主要介绍MySQL的架构和架构中各层的主要职责

一 MySQL架构体系

开局先上图，MySQL架构体系图如下：

从上往下，可分为客户端、连接层、服务层、存储引擎层、文件系统层。客户端与MySQL交互时，都遵守这个架构

简单介绍一下每个部分
1 客户端：可以是各类编程语言，如：Python, Java, C/C++, Golang, Rust, PHP …；可以是可视化软件，如：Navicat；也可以是命令行工具。即只要能与MySQL建立连接的都可以被称为是MySQL的客户端
2 连接层：主要是指MySQL的连接池，负责管理所有客户端与MySQL的连接
3 服务层：主要包含系统管理和控制工具、SQL接口、解析器、优化器、缓存和缓冲
4 存储引擎层：指MySQL支持的存储引擎，如InnoDB、MyISAM等
5 文件系统层：数据文件、索引文件、日志文件等各类MySQL运行时所需的文件，位于物理磁盘上

二 连接层

负责管理所有客户端与MySQL的连接，以及连接处理、身份认证、权限控制、安全处理等操作

请求时，如果身份验证通过，即用户名和密码都正确，MySQL还会进行授权操作：查询用户所拥有的权限，并对其授权。当后续执行SQL语句时，会先判断是否具备执行SQL语句的权限，然后再执行SQL语句

一) MySQL的连接池(Connection Pool)

所有客户端与MySQL的连接都需要一个线程去维护，线程的创建和销毁都会消耗系统资源；且系统资源有限，不允许无限的创建线程。所以MySQL使用了连接池，主要是为了复用线程、管理线程以及限制最大连接数

最大连接数通过 max_connections 参数控制。如果超出该值时，后续所有客户端的连接都会被拒绝
当一个客户端连接断开后，处理本次连接的线程不会立马销毁，而是会放入到缓存连接池中，待有新连接时直接复用该线程，避免了创建线程、分配栈空间等操作，提升了性能

二) MySQL使用的通信协议

客户端与MySQL连接时使用的协议，MySQL支持多种通信协议

1 Unix Socket

登录MySQL命令中，如果没有指定-h参数，使用的是socket方式登录，需要用到sock文件(即mysql.sock文件)

2 TCP/IP协议

登录MySQL命令中，如果指定-h参数，使用的是TCP/IP协议

三) MySQL使用的通信类型

一般情况连接数据库都是同步连接；如果要异步，建议使用连接池，排队从连接池获取连接而不是创建新连接

1 同步

同步通信依赖于被调用方，受限于被调用方的性能。即应用线程操作数据库时会阻塞，直到数据库的返回数据
一般只能做到一对一，很难做到一对多的通信

2 异步

异步可以避免应用线程阻塞等待，但是不能节省 SQL执行的时间
异步并发时，为了避免数据混乱，每一个请求都要单独建立一个连接，给MySQL服务端带来压力
异步会提升编码的复杂度，所以一般不建议使用

四) MySQL使用的通信方式

MySQL 使用半双工的通信方式

1 单工

通信时，数据的传输是单向的，即只能A方向B方传输数据

2 半双工

通信时，数据传输是双向的，但同一时间只能有一方在发送数据

3 全双工

通信时，数据传输是双向的，双方可以同时传输数据，互不影响

五) MySQL使用的连接方式

MySQL 既支持短连接，也支持长连接。一般使用的是长连接，一般情况下，连接池中的连接也都是长连接

1 长连接

长连接就是操作执行完成之后，连接继续保持打开状态
长连接可以减少服务端创建和释放连接的消耗，之后访问MySQL可以复用
保持长连接会消耗内存，长时间不活动的连接，MySQL会自动断开

2 短连接

短连接就是操作执行完成之后，马上关闭连接

三 服务层

MySQL大多数核心功能都位于服务层，包括：语法、词法解析和校验、查询优化、查询缓存、内置函数、binlog日志、以及所有跨存储引擎的功能：存储过程、触发器和视图等

一) 系统管理和控制工具(Management Services & Utilities)

负责数据备份、备份恢复、MySQL 复制、安全管理、集群管理等

二) SQL接口(SQL Interface)

主要负责处理客户端的SQL语句
当MySQL接收到客户端发送的SQL命令后，SQL接口会将SQL命令分发给其他组件，然后等待接收执行结果，最后会将结果返回给客户端

SQL接口既是MySQL接收客户端SQL命令的入口，也是MySQL返回数据给客户端的出口

三) 解析器(Parser)

负责解析SQL语句，并校验语法是否合法：是否有执行权限；单/双引号是否闭合；表是否存在；字段是否存在；(表/字段)名字和(表/字段)别名是否有歧义等。如果检测到语法错误，会抛出对应的错误码和信息

解析器会将SQL语句解析并生成一个语法树

四) 优化器(Optimizer)

负责生成执行计划(执行计划可以有多个)，并选择其中最优的一个执行计划

MySQL使用基于开销(cost)的优化器，同一条 SQL可以有多种执行方式，但返回的结果相同，使用开销最小的执行计划
MySQL通过复杂的算法尽可能的优化查询效率，使用多种优化策略生成最优的执行计划，可以分为两类：静态优化(编译时优化)、动态优化(运行时优化)，如：多表关联查询时，以哪个表的数据作为基准表；多个索引可以使用时，选择哪个索引

看到网上很多资料提到了执行器，MySQL官方并没有列出这个部分，它是一个抽象的概念，实际上不存在。
可以认为维护客户端连接的线程就是执行器，优化器生成执行计划后，维护当前连接的线程根据执行计划去执行SQL，执行SQL实际上是调用存储引擎提供的API

五) 缓存和缓冲(Cache&Buffer)

缓存包括：表缓存，行记录缓存，key缓存，权限缓存等。缓存中主要是行记录缓存：存的是select语句的结果集，就是所谓的查询缓存

缓冲和存储引擎有关，不同的存储引擎缓冲区不同，如：InnoDB的缓冲区是innodb_buffer_pool，而MyISAM是key_buffer

简单展开讲一下有关缓冲的基本知识，后续文章会着重讲这个缓冲：在读取数据时，会先将从磁盘读到的数据对应的页存放在缓冲区；后续对数据进行写操作时，会先查询缓冲区中是否存在要操作数据对应的页，存在则直接对缓冲区的数据页操作；然后会给客户端返回成功；然后MySQL使用后台线程基于Checkpoint机制，将缓冲区中更新的数据页刷写到磁盘

MySQL8.0 已经删除了查询缓存：
a) 命中率不高：SQL语句必须要严格匹配。查询缓存是以key-value形式存储的，key：执行的SQL语句， value：SQL语句执行的结果
b) 可以使用其他中间件作为缓存，如：Redis

MySQL8.0 只是删除了查询缓存，缓存中的其他缓存并没有删除

四 存储引擎层

上面说的服务层包含了MySQL大多数核心功能，而存储引擎层主要负责具体的数据操作

MySQL存在多个不同的存储引擎，因此存储引擎层是插拔式的，即可以根据业务特性，选择不同的存储引擎

为了能够兼容不同的存储引擎，存储引擎的实现规范被定义成API接口，如果想要自己实现存储引擎，只需要实现对应的API接口，同时API接口也屏蔽了不同存储引擎之间的差异，即所有的存储引擎对服务层来说都是一样的操作，服务层不会感知到使用的是哪种存储引擎
所有与底层文件系统(即磁盘)交互的工作都会交给存储引擎层

存储引擎有多种，每种都有各自的优缺点，最常见的存储引擎是MyISAM和InnoDB
以下仅列出常见的三种储存引擎，更多支持的储存引擎请查阅：MySQL8.0支持的储存引擎

一) MyISAM存储引擎

特点：
1) 不支持事务，也不支持外键
2) 支持表级锁
2) 访问速度较快

适合对事务完整性没有要求并以读为主的系统

二) InnoDB存储引擎

MySQL 5.5版本以后默认的存储引擎
特点：
1) 支持事务，支持外键
2) 支持行级锁和表级锁
3) 支持读写并发，写不阻塞读
4) 特殊的索引存放方式，可以减少IO，提升查询效率
5) 比MyISAM存储引擎占用更多的磁盘空间。

适合需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制的系统

三) MEMORY存储引擎

特点：
1) 数据存储在内存中，访问速度最快，数据库重启或者崩溃，数据会全部丢失
2) 适合做临时表

五 文件系统层

文件系统层主要负责与存储引擎层交互，以及数据的存储与持久化，是指MySQL的数据和日志存储的物理磁盘文件，主要包含日志文件，数据文件，索引文件，配置文件，pid 文件，socket 文件等各类MySQL运行时所需的文件

一) 日志文件

1 错误日志(error log)

记录MySQL启动、运行、关闭时发生的错误的相关信息，默认开启

2 查询日志(general query log)

记录MySQL接收到的每一个查询SQL，默认不开启

3 二进制日志(binary log)

记录对数据的修改操作，并且记录操作发生的时间、执行的时长，默认不开启。MySQL自带的日志
修改操作包括增删改，不会记录select、show等不修改数据库的SQL语句，主要用于数据库恢复和主从复制
简要介绍，后续会详细介绍

4 慢查询日志(slow query log)

log_query_time：默认值10，单位s
记录运行时间超过log_query_time的所有SQL语句以及信息：执行时刻、消耗的时间、执行的用户、连接主机等，默认不开启

5 重做日志(redo log)

记录对数据的修改操作，即MySQL某行表数据的某个字段值从x修改为y，用于MySQL宕机后恢复数据。InnoDB存储引擎独有日志
简要介绍，后续会详细介绍

6 回滚日志(undo log)

记录被修改前的原始数据，用于事务回滚
简要介绍，后续会详细介绍

二) 数据文件

1 库、表信息文件

db.opt文件：保存数据库使用的字符集和验证规则等信息的文件，MySQL8.0不再使用
.frm文件：表结构信息文件，每张表对应一个.frm文件，MySQL8.0不再使用

2 MyISAM存储引擎独有文件

.MYD文件：存放表数据，每张表对应一个.MYD文件
.MYI文件：存放表索引，每张表对应一个.MYI文件
.sdi文件：MySQL8.0使用的表结构信息文件，每张表对应一个sdi文件

3 InnoDB存储引擎独有文件

MySQL8.0不再使用frm文件，表结构信息合并到表空间文件中
ibdata1文件：系统表空间文件，即共享表空间。包含表的元数据、undo log、change buffer和doublewrite buffer。 默认1个，大小12M，路径在数据目录下，大小自动随着数据不断增加，可通过参数innodb_data_file_path配置
.ibd文件：独立表空间文件，只存放表数据、索引、insert buffer bitmap，其余信息依然存放在系统表空间，每张表对应一个.ibd文件
ib_logfile0, ib_logfile1文件：重做日志文件，即redo log file，日志个数默认2个

三) 配置文件

存放MySQL配置信息：my.cnf、my.ini等

四) pid 文件

Unix/Linux 系统中，MySQL启动后，会将自己的进程id入到pid文件

五) socket 文件

Unix/Linux 系统中，MySQL启动后，会产生一个socket文件，客户端可以不使用TCP/IP协议，而使用socket文件来连接 MySQL