逻辑架构

1. 逻辑架构剖析

1.1 服务器处理客户端请求

展开：

1.2 Connectors

Connectors：不同语言与 SQL 交互。MySQL 是网络程序，在 TCP 上定义了自己的应用层协议。所以在使用 MySQL时，我们可以编写代码，跟 MySQL Server 建立 TCP 连接 ，后按照定义好的协议进行交互。或者调用 SDK ，例如 Native C API、JDBC、PHP 等 通过 SDK 访问 MySQL，本质上还是在 TCP 连接上通过 MySQL 协议与 MySQL 进行交互

MySQL Server 结构可以分成三层

1.3 第一层：连接层

客户端 成功访问 MySQL 服务器前，应建立 TCP 连接。

经过三次握手建立连接成功后，MySQL服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

• 用户名或密码不对，会收到 Access denied for user 错误
• 用户名密码认证通过，会从权限表中查询出账号拥有的权限与连接关联，之后用户做的操作，都按照读取到的权限进行判断

TCP 连接收到请求后，必须分配一个线程专门与这个客户端进行交互，所以还有一个线程池，去完成以下流程。每一个连接都会从线程池中获取线程，省去创建和销毁线程的开销。

1.4 第二层：服务层

• SQL Interface：SQL 接口
  • 接收到 SQL 语句后，返回查询出的结果。
  • MySQL 支持 DML(数据操作语言)、DDL(数据定义语言)、存储过程、视图、触发器、自定义函数等多种 SQL 语言接口
• Parser：解析器
  • 对 SQL 语句进行语法分析、语义分析。将 SQL 语句分解成 数据结构，并将这个结构传递到后续步骤，以后SQL 语句的传递和处理都是基于这个结构的。若在分解过程中遇到错误，则说明这个 SQL 语句是不合理的。
  • SQL 语句传递到解析器时会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
• optimizer：查询优化器
  • SQL 语句在语法解析后、查询之前会用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划
  • 执行计划表明应使用哪些索引进行查询(全表索引还是使用索引检索)，表之间的连接顺序，最后按照执行计划中的步骤调用存储引擎提供的方法来真正执行查询，并将查询结果返回给用户。
  • 使用 选取-投影-连接 策略进行查询

举例： SELECT id,name FROM student WHERE gender = '男'; 这个SELECT 语句，先根据 where 进行选取，而不是全部查询出来后再过滤； 根据 id 和 name 进行属性的投影，而不是所有属性取出来后再过滤； 将这两个查询条件连接 起来生成最终的查询结果。

• Caches & Buffers：查询缓存组件
  • MySQL 内部会维持一些 Cache 和 Buffer，例如 Query Cache 用来缓存一条 SELECT 语句查询的结果，若可以在 Cache 中找到对应的查询结果，则不用再进行查询解析、优化和指定的整个 过程，直接返回结果给客户端
  • 这个缓存机制由很多个小缓存组成。例如：表缓存，key 缓存，权限缓存等
  • 这个查询缓存可以在 不同客户端之间共享
  • 在 MySQL 5.7.20 开始，不推荐使用查询缓存，并在 MySQL 8.0 中删除

1.5 第三层：引擎层

插件式存储引擎层（Storage Engines），负责 MySQL 数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过 API 与存储引擎通信。不同的存储引擎具有的功能不同

MySQL 8.0.25 默认支持的存储引擎如下：

1.6 存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

1.7 小结

• 连接层：客户端和服务器建立连接，客户端发送 SQL 至服务器端
• SQL层(服务层)：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
• 存储引擎层：与数据库文件交互，负责数据的存储和读取

2.SQL 执行流程

2.1 MySQL 中 SQL 的执行流程

MySQL 查询流程：

1. 查询缓存：若 Server 在查询缓存中发现了这条 SQL 语句，则会直接将结果返回给客户端；若没有，则进入解析器阶段。注意：因为查询缓存的效率不高，MySQL 8.0 删除了这个功能

举例：

• 必须要相同的查询 SQL 语句才可以命中查询缓存。(任何字符的不同都不可以有：空格、注释、大小写)，这些都会导致不命中缓存。因此命中查询缓存的效率不高
• 调用 NOW() 获取当前时间，若缓存了，则第二次调用，结果与第一次相同
• 若在两次查询期间，数据被修改了，则该表的所有查询缓存都会变为无效。

建议：在静态表(极少更新)的表中使用查询缓存

有三个值，0表示关闭查询缓存(即OFF)，1表示开启(即ON)，2(DEMAND)按需加载(即只有在SELECT 语句中添加了 SQL_CHACE 才使用)
query_cache_type=2

按需加载： SELECT SQL_CACHE * FROM test WHERE id = 5; 开启状态下不想使用： SELECT SQL_NO_CACHE * FROM test WHERE id = 5;

2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析 解析器(分析器)： 先进行词法分析：识别出哪些是关键字，哪些是表名等； 后进行语法分析：解析器会根据语法规则，判断 SQL 语句是否满足 MySQL 语法 select department_id,job_id,avg(salary) from employees group by department_id;

若 SQL 语句正确，则生成一个语法树

3. 优化器：在优化器中确定 SQL 语句的执行路径；例如：根据全表检索、还是根据索引检索

举例：

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';

方案1：可以先从表 test1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 'mysql高级课程'。
方案2：可以先从表 test2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name的值是否等于 zhangwei。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

优化器是怎么选择索引的，有没有可能选择错等。索引篇章中有所展开

在查询优化器中，可以分为逻辑查询优化阶段和 物理查询优化阶段

优化器后只生成了一个执行计划 4. 执行器：

在执行之前先判断是否拥有权限，若没有，则返回权限错误。若有则执行 SQL 操作并返回结果。若在 MySQL 8.0 以下的版本，如果设置了查询缓存，则会将查询结果进行缓存

举例： SELECT * FROM test WHERE id = 1; 表 test 中，ID 字段没有索引，则执行器执行流程如下：

调用 InnoDB 引擎接口取该表的第一行，判断 id 值是否为1，若不是则跳过，若是则将这行存储到结果集中；调用引擎接口读取下一行，重复一样的逻辑，直到取到表的最后一行

执行器会将上述过程中所有满足条件的行组成的记录集作为结果集返回给客户端

总结：SQL 语句在 MySQL 中流程：SQL 语句 -> 查询缓存 -> 解析器 -> 优化器 -> 执行器

2.2 MySQL8 中 SQL 执行原理

1.开启 profiling

select @@profiling;

show cariables like 'profiling';

profiling=0 表示关闭 开启语法：set profiling=1;

2. 多次执行相同的 SQL 查询

select * from employees;

3. 查看 profiles

查看当前会话所产生的所有 profiles; show profiles; #显示最近几次查询

4.查看 profile

查看执行计划 show profile[for query Query_ID]; //若不指定Query_ID则默认为最后的Query_ID也就是最近的一次 SQL 语句

还可以查询其他的内容 show profile cpu,block io

2.3 MySQL 5.7 中 SQL 执行原理

上述操作在 MySQL 5.7 中执行，发现相同的 SQL 语句，执行的查询过程仍然相同，并没有使用查询缓存，我们需要显式开启查询缓存模式。

1. 在配置文件中开启查询缓存

在 /etc/my.cnf 中 query_cache_type=1

2. 重启 mysql 服务

systemctl restart mysqld

3 .开启查询执行计划

set profiling=1;

4. 执行完全相同语句两次

select * from employees;

select * from employees;

5. 查看 profiles

6. 查看 profile

显示执行计划，查看程序的执行步骤 show profile for query 1; show profile for query 2;

在截图中可以看出，在 MySQL 5.7 中开启了查询缓存，两次完全的相同查询语句直接从缓存中获取数据

3. 数据库缓冲池(Buffer Pool)

在 InnoDB 存储引擎中是以页为单位来管理存储空间的，增删改查操作其本质是对 页 的增删改查。 若不断的 进行磁盘 I/O 需要消耗的时间过多，而在内存中进行操作，效率更高。 DBMS 会申请 占用内存来作为数据缓冲池，在真正的访问 页 之前，会将磁盘的页 缓存到 内存中的 Buffer Pool 之后再访问。

好处：减少与磁盘直接 I/O 的次数

3.1 缓冲池 vs 查询缓存

本质上是不同的东西

1. 缓冲池(Buffer Pool)

InnoDB 存储引擎会将一部分数据存放在内存中，缓冲池则占用了内存的大部分，用来存储各种数据的缓存。

缓存原则： 位置 * 频次，可以帮我们将 I/O 访问效率进行优化

位置 决定效率，若数据在内存中则访问的效率会大幅提升

频次决定优先级顺序。缓存池的大小有限，会优先将使用频次高的热数据加载到缓存池中

缓冲池的预读特性：

在我们使用了一张表的一些数据时，有极大概率会使用它周围的一些数据。因此使用了 "预读" 机制进行提前加载，减少可能对 磁盘的 I/O 操作

2. 查询缓存

将查询结果提前缓存起来，下次就无需再次执行语句也可拿到结果。 注意：查询缓存 是将对应的查询结果 缓存下来，而不是查询计划。

3.2 缓冲池如何读取数据

缓冲池管理器会将经常使用的数据保存在缓存池中，对 页 进行读操作时，会先判断该页面是否在缓冲池中，若存在则直接读取。若不存在，则通过内存或者磁盘 将 页 存放在缓冲池中再进行读取

注意：执行 SQL 语句更新缓存池中的数据，数据并不会马上同步到磁盘上

我们对数据库中的记录进行修改操作时，首先会修改缓冲池中的 页 中的记录，后数据库 会以 特定的频率刷新 到磁盘上，缓冲池会使用一种 叫 checkpoint 的机制将磁盘中的数据修改，好处：提升数据库整体的性能

脏页(dirty page)：缓冲池中被修改过的页，与磁盘上的数据不一致

当缓存池不够用时，需要释放一些不常用的页，可以强行使用 checkpoint 方式，将不常用的脏页回写到磁盘中，后再从缓冲池将这些页都释放掉。

3.3 查看/设置缓冲池的大小

若使用 InnoDB 存储引擎,则查看变量 innodb_buffer_pool_size 查看缓冲池的大小 show variables like 'innodb_buffer_pool_size';

修改： set global innodb_buffer_pool_size = 268435456

或者在配置文件中修改

[server]
innodb_buffer_pool_size = 268435456

3.4 多个 Buffer Pool 实例

[server]
innodb_buffer_pool_instances = 2

查看缓冲池的个数 show variables like 'innodb_buffer_pool_instances';

每个 Buffer Pool 占用内存空间为：总共大小/实例个数

3.5 中断问题

若在修改数据的过程中出现一个错误，想要回滚：Redo Log & Undo Log