逻辑架构

扩展（c/s架构和 b/s 架构）

c/s 架构，即 Client / Server 架构：需要安装专用的客户端软件，然后联网请求服务器获取数据。
b/s 架构，即 Brower / Server 架构：需要安装浏览器，然后联网请求服务器获取数据

c/s架构和b/s架构的区别

标准：BS开发更标准一些，而CS需要在不同的系统上执行,BS只需要在浏览器上执行；
效率：CS效率更高，CS属于安装的软件，很多内容已经安装到客户端中了，直接联网获取数据即可；而BS运行在浏览器上，所有的数据必须经过下载才能使用；
升级：BS无缝升级，CS需要删除老版本，再安装新版本；
安全性：CS更为安全，因为必须安装软件才能使用；BS安全度较低，只要有浏览器就可以使用；
开发成本：CS开发成本更高，程序员必须精通各个系统；BS开发成本很低，只要浏览器能够正常运行即可；

1. 逻辑架构剖析

1.1 服务器处理客户端请求

首选 MySQL 是典型的 C/S 架构，即 Client / Server 架构，服务器端程序使用的 mysqld。

执行流程的简单概述：客户端"进程"向服务器"进程"发送一段文本（SQL语句），服务器"进程"处理后再向客户端"进程"发送一段文本（处理结果）、

注意哦：一个进程必定包含多个线程。

服务器进程对客户端进程发送的请求的简洁的处理流程图：

连接管理：进行用户密码的校验以及权限的校验。
解析和优化：解析 SQL 语句，生产一个解析树。然后对 SQL 语句进行优化处理。
存储引擎：调用存储引擎查询结果。文件系统会先把数据加载到缓冲池（buffer Pool）中。

MySQL 架构图：

1.2. Connectors（客户端连接器）

Connectors（客户端）：指的是不同语言中与SQL的交互或者是不同语言对 MySQL连接协议的实现。

作用：跟 MySQL服务器建立 TCP 连接，然后通过定义好的协议进行交互。

MySQL Server 的结构可以分为如下三层：

连接层
服务层
引擎层

1.3 连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事件就是建立 TCP 连接。

职责：负责用户认证，管理连接，获取权限信息。

作用：在 TCP 连接成功之后，对请求连接的用户密码进行验证，管理TCP连接池和线程池，以及获取相应的权限信息。

用户名或密码不对，会收到一个 Access denied for user 错误，客户端程序结束执行。
用户名密码验证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。

思考一个问题？

一个系统只能和MySQL服务器建立一个连接吗？；MySQL服务器只能和一个系统建立连接吗？

当然不是，系统和MySQL服务器之间是多对多的关系。但会频繁出现 TCP 连接创建销毁带来的资源耗尽，性能下降问题。所以 MySQL 服务器针对以上问题设置了专门的 TCP连接池来限制连接数，采用长连接模式复用TCP连接。这也就是 MySQL中最大连接数参数的由来吧！

MySQL中除了有 TCP连接池还有线程池。

当连接收到请求后，必须要分配给一个线程专门负责与客户端的交互。那么有了线程池之后，每一个连接就可以直接从线程池中获取线程，省去了创建和销毁线程的开销。

1.4 服务层

该层是核心服务层。如 SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如存储过程，存储函数，触发器，视图。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行计划。

SQL Interface（SQL 接口）：
- 接受用户的 SQL 语句，并且返回用户需要查询的结果。比如：select ... from ...
- MySQL 支持DML（数据库操作语言）DDL（数据库定义语言），存储过程，自定义函数，视图，触发器等多种SQL语言接口。
Parser（解析器）：
- 解析器会对接受的 SQL 语句进行语法分析，语义分析。将 SQL 语句分解成数据结构。后续的步骤的执行和处理都是基于这个数据结构的。如果在分解构成数据结构时遇到错误，那么就说明这个 SQL 语句时不合理的。
- 当 SQL 语句被解析器验证和解析成功后，并为其创建语法树，并根据数据字典查询语法树，验证该客户端是否具有执行该操作的权限。注意，当创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
Optimizer（查询优化器）：
- SQL 语句在经过解析器语法解析后，在查询之前还会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
- 该执行计划用于表明使用那些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序等。最后按照执行计划中的步骤调用存储引擎提供的方法进行查询数据，并将查询结果返回给用户（客户端）。
- 它使用 "选取 - 投影 - 连接"策略进行查询。例如：
```
select id,name from dual where id = 1;
```
选取：根据 where 语句中的条件进行选取，可不是将表全部查询出来以后再进行过滤的。

投影：根据select中指定的字段名进行属性投影，可不是将属性全部取出以后再进行过滤的。

连接：将这两个查询条件连接起来生成最终查询结果。
Caches & Buffers （查询缓存组件 8.0 已删除）：
- MySQL 内部有一些 Caches 和 Buffer，比如 Query Cache 就是用来缓存一条 select 语句的执行结果的，如果能在缓存中找到对应的查询结果，那么就不必在进行查询解析，优化和执行的整个过程了。
- 这个缓存机制是有由一些小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
- 这个查询缓存可以在不同客户端之间共享的。
- 从 5.7.20 开始，不推荐使用查询缓存，并在 8.0 中删除。因为查询缓存的命中率极低（必须完全一样的 SQL 语句才能命中，多个空格都不行）。

1.5 引擎层

插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务需求和实际需要选择合适的存储引擎。同时还支持开发人员设置自己的存储引擎。

插件式存储引擎层（Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎的功能不同，我们可以根据自己的实际需要进行选取。

8.0 默认使用 InnoDB 存储引擎。

SHOW ENGINES;

1.6 存储层

所有的数据，数据库，表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。

当然有些存储引擎，也支持不使用文件系统直接管理数据，比如 InnoDB。

查询数据库服务文件系统目录命令：

SELECT @@global.datadir;
# 或
SHOW VARIABLES LIKE '%datadir%';

1.7 小结

简化为三层结构：

连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层（服务层）：对 SQL 语句进行解析优化执行查询处理，并返回查询的结果；与数据库文件的存储方式无关；
存储引擎层：与数据库文件打交道，负责数据的存储和读取；

2. SQL执行流程

2.1 MySQL 中的 SQL 执行流程

2.1.1 查询缓存

做什么的：存储 SQL 语句的查询结果。

作用：缓存查询结果，提高查询效率。

概述：

数据库服务器（Server）如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；反之如果没有，就进入到解析器阶段。

补充说明：因为查询缓存的命中率不高（效率不高），所以 8.0 之后抛弃了这个功能。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以 key - value 的形象，被直接存储在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在缓存找到，那么直接返回给客户端。反之如果缓存查不到，就会继续后面的执行阶段。当执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，就不需要后面复杂的操作，效率会很高。

缺点：

部分函数无法缓存，now()。
数据发生改变时，高效缓存会无效，更新操作频繁的数据库来说，查询缓存的命中率会非常低。

5.7.版本中开启查询缓存

查询查询缓存状态

SELECT @@query_cache_type;
# 或者
show VARIABLES LIKE '%query_cache_type%';

查询缓存状态说明

query_cache_type=2
# 有3个值 0代表关闭查询缓存 OFF，1代表开启ON，2（DEMAND）

设置查询缓存状态

SET query_cache_type = 2;

监控查询缓存的命中率

SHOW STATUS LIKE '%Qcache%';

2.1.2 解析器

做什么的：在解析器中对 SQL 语句进行语法分析，语义分析。

作用：解析 SQL 语句，判断 SQL 语句是否合法，生成语法树。

概述：

当 SQL 语句进入到解析器时，就表示要真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。SQL 语句的分析分为词法分析和语法分析

词法分析：解析器先做 "词法分析"。SQL 语句是由多个字符串和空格组成，MySQL 需要识别出字符串分别是什么，代表什么。例如：从头开始识别，识别出 "select" 这个关键字，就表示这一条查询语句。把后续字符串"ID"识别成"列ID"，"from" 后面的字符串为"表"等...。
语法分析：紧接着要做 "语法分析"。根据 "词法分析" 的结果，语法分析器会根据语法规则，判断该 SQL 语句是否满足MySQL语法。

当SQL语句的语法不对时，就会收到 "You have an error in your SQL syntax; " 的错误提醒

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rom pw_sample_point WHERE id = '1516611230470189057'' at line 1

注意：在错误提醒中被引号 '' 引起的信息是主要的错误信息！！！

SQL 语句生成的语法树结构：

2.1.3 优化器

做什么的：在优化器中确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。

作用：生成最好的执行计划。

概述：

经过了解析器，MySQL 就知道你要做什么了。在开始之前，还要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

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

逻辑查询优化：通过改变 SQL 语句的内容来使得 SQL 查询更高效
物理查询优化：是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，物理计算往往对应着多种算法。所以需要使用索引，提升查询

2.1.4 执行器

做什么的：根据执行计划，找到对应的表，调用存储引擎API 对表进行读写（真正的查询）。

作用：判断用户权限，执行执行计划读写数据。

概述：

在执行之前需要判断该用户是否具备权限。如果没有，就返回权限错误。

如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，调用存储引擎API 对表进行读写（查询）。存储引擎 API 是抽象接口，具体实现还是要根据表选择的存储引擎。

2.1.5 SQL 语句的执行流程：SQL语句 --> 查询缓存 --> 解析器 --> 优化器 --> 执行器

2.2 MySQL8中SQL 执行原理

SQL的执行原理，不同的 DBMS 的 SQL 的执行原理是相通的，只是在不同的软件中，各有各的实现路径、

一条 SQL 语句会经历不同的模块，那么在不同的模块中，SQL 执行所使用的资源（时间）又是怎样的呢。下面可以通过一个系统参数进行分析。

2.2.1 开启profiling（开启资源收集）

全局/会话共有系统变量：0 关闭，1 开启

查看状态

# 全局 会话 共有系统变量
SELECT @@profiling;
# 或者
SHOW SESSION VARIABLES LIKE '%profiling%';
# 或者
SHOW GLOBAL VARIABLES LIKE '%profiling%';

修改状态

# 会话系统变量
SET SESSION profiling = 1;
# 全局系统变量
SET GLOBAL profiling = 1;

2.2.2 查询 SQL 各个模块执行所使用的资源（时间）信息

查看所有 SQL 执行的记录

SHOW PROFILES;

查看上一条 SQL 各个模块执行的记录

SHOW PROFILE;

查看指定 SQL 各个模块执行的记录

SHOW PROFILE FOR QUERY 36; 
# 36 对应所有 SQL 执行记录列表中的 Query_Id 字段
# 语法说明
show profile [type,type,...] for query id
-- ALL 显示所有参数的开销信息
-- block io 显示IO的相关开销信息
-- cpu 显示就cpu相关开销信息
....

SQL 各个模块执行记录查询结果说明：

Status （阶段）
- starting：启动
- Executing hook on transaction：执行挂接事务
- starting：启动
- checking permissions：检查权限
- Opening tables：打开表
- init：初始化
- System lock：系统锁
- optimizing：优化
- statistics：统计
- preparing：准备
- Creating tmp table：创建 tmp 表
- executing：执行
- Sending data ：发送数据
- Sorting result ：排序
- end：结束
- query end：查询结束
- waiting for handler commit：正在等待处理程序提交
- removing tmp table：删除 tmp 表
- closing tables：关闭表／去除TMP 表
- freeing items：释放物品
- cleaning up：清理
Duration（时间）

2.2.3 SQL语法顺序

手写：

SELECT DISTINCT
	字段,
	字段 ,....
FROM
	表名 (LEFT / RIGHT)
JOIN 表连接 ON [AND/OR] 连接条件
WHERE
AND
OR IN 过滤条件
GROUP BY
	分组
HAVING
AND
OR IN 分组条件
ORDER BY
	排序
LIMIT 分页

机读：

-- 第一阶段
FROM
	表名 (LEFT / RIGHT)
JOIN 表连接 ON [AND / OR] 连接条件
WHERE
AND
OR IN 过滤条件
GROUP BY
	分组
HAVING
AND
OR IN 分组过滤条件 
-- 第二阶段
SELECT DISTINCT
	字段,
	字段,....
-- 第三阶段
ORDER BY
	排序
LIMIT 分页

3. 数据库缓冲池（buffer pool）

InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面，写页面，创建新页面等操作）。而磁盘 I/O 需要消耗的很多时间。那么为了能让数据表或索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，会把磁盘上的页缓存到内存中 Buffer Pool 之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。这种策略对提升 SQL 性能至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3.1 缓冲池与查询缓存

缓冲池和查询缓存是一个东西吗？不是。

3.1.1 缓冲池（Buffer Pool）

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

InnoDB 缓冲池包括了数据页，索引页，插入缓冲，锁信息，自适应 Hash 和数据字段信息等。

缓冲池的重要性：

InnpDB 存储引擎在处理客户的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就说即使我们只需要访问一条数据，那也需要先把整个页的数据加载到内存中。当数据加载到内存后就可以进行读写访问了。但进行读写访问之后并不着急把数据对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页数据时，就可以省去磁盘IO了。

缓存原则：

" 位置 * 频次 "这个原则，可以帮我们对 I/O 访问效率进行优化。

位置：位置决定效率，位置指的是磁盘和内存，缓冲池就是为了在内存中可以直接访问数据。

频次：因为缓冲池的大小是有限的，无法将所有的数据都加载到缓冲池中，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载、

缓冲池的预读特性：

当在内存空间充足的情况下，会把我们正在使用的数据，以及它周围的一些数据，采用预读的机制提前加载，可以减少未来可能的磁盘 I/O 操作。

注意："查询缓存" 是提前将查询的结果缓存了起来，而 "缓冲池" 服务于数据库整体的 I/O 操作。

3.2 缓冲池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或者磁盘将页面存放到缓冲池中再进行读取。

问题：如果我们执行 SQL 语句的时候更新了缓冲池中的数据，那么这些数据会马上同步到磁盘上吗？

答：当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。注意：并不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用 checkpoint的机制将数据回写到磁盘上。

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

如果你使用的是 MySQL MyISAM 存储引擎，它只缓存索引，不缓存数据，对应的键缓存参数为 "key_buffer_size"，你可以用它进行查看。

如果你使用的是 InnoDB 存储引擎，可以通过查看 ""innodb_buffer_pool_size"" 变量来查看缓冲池的大小 - （全局系统变量），默认：128MB。

查看 InnoDB 存储引擎缓冲池的大小

SELECT @@global.innodb_buffer_pool_size;
# 或
SHOW GLOBAL VARIABLES like '%innodb_buffer_pool_size%';

设置 InnoDB 存储引擎缓冲池的大小

SET GLOBAL innodb_buffer_pool_size = 1073741824; # 设置为 1GB

或者：配置文件，重启 MySQL 服务后才会生效

[server]
innodb_buffer_pool_size = 1073741824 #设置 InnoDB 的缓冲池大小为 1GB

3.4 多个 Buffer Pool实例

Biffer Pool本质是 InnoDB 向操作系统申请的一块连续的内存空间，在多线程环境下，访问 Buffer Pool中的数据都需要（多线程）加锁处理。那么在多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把它拆分成若干个小的 Buffer Pool，每个 Buffer Pool都称为一个实例，它们都是独立的，独立大的去申请内存空间，独立的管理各种链表。所以在多线程并发访问时并不会相互影响，从而提高高并发处理能力。总结一句话就是：多线程高并发情况下，需要开启多个 Buffer Pool 实例来提高效率。

（InnoDB 引擎）可以通过设置 "innodb_buffer_pool_instances" 的值来修改Buffer Pool实例的个数（全局系统变量），默认：1个实例

查看 InnoDB 存储引擎的Buffer Pool 实例数

SELECT @@global.innodb_buffer_pool_instances;
# 或
SHOW GLOBAL VARIABLES like '%innodb_buffer_pool_instances%';

或者：配置文件，重启 MySQL 服务后才会生效

[server]
innodb_buffer_pool_instances = 2 #设置 InnoDB 的缓冲池实例为 2个

每个 Buffer Pool 实例占用多少内存空间呢？

# 也就是缓冲池总共的大小 除以 缓冲池实例的个数，结果就是每个缓冲池实例占用的大小
innodb_buffer_pool_size/innodb_buffer_pool_instances

注意：InnoDB规定：当 "innodb_buffer_pool_size" 的值小于 1G 的时候设置多个实例是无效的，InnoDB 会默认把 "innodb_buffer_pool_instances" 的值修改为 1。所以建议在 Buffer Pool 大于或等于 1G 的时候再设置多个Buffer Pool实例。