MySQL高级篇03【逻辑架构】

1. 逻辑架构剖析

1.1 服务器处理客户端请求

客户端进程对服务器进程发送请求做了哪些处理&＃xff0c;才能获得正确的结果呢&＃xff1f;

图示&＃xff08;请求过程粗略图&＃xff09;&＃xff1a;

MySQL具体请求流程图示&＃xff1a;

1.2 Connectors

Connectors&＃xff0c;指的是不同语言中与SQL的交互。MySQL首先是一个网络程序&＃xff0c;在TcP之上定义了自己的应用层协议。所以要使用MySQL,我们可以编写代码&＃xff0c;跟MySQL Server建立TCP连接&＃xff0c;之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK,比如Native C API、.JDBC、PHP等各语言MySQL Connector,或者通过ODBC。但通过SDK来访问MySQL,本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。

1.3 第一层&＃xff1a;连接层

系统&＃xff08;客户端&＃xff09;访问 MySQL 服务器前&＃xff0c;做的第一件事就是建立 TCP 连接。
经过三次握手建立连接成功后&＃xff0c; MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

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

TCP 连接收到请求后&＃xff0c;必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池&＃xff0c;去走后面的流程。每一个连接从线程池中获取线程&＃xff0c;省去了创建和销毁线程的开销。

1.4 第二层&＃xff1a;服务层

• SQL Interface: SQL接口

  • 接收用户的SQL命令&＃xff0c;并且返回用户需要查询的结果。比如SELECT ... FROM就是调用 SQL Interface
  • MySQL支持DML&＃xff08;数据操作语言&＃xff09;、DDL&＃xff08;数据定义语言&＃xff09;、存储过程、视图、触发器、自定义函数等多种SQL语言接口

• Parser: 解析器

  • 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构&＃xff0c;并将这个结构传递到后续步骤&＃xff0c;以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误&＃xff0c;那么就说明这个SQL语句是不合理的。
  • 在SQL命令传递到解析器的时候会被解析器验证和解析&＃xff0c;并为其创建语法树&＃xff0c;并根据数据字典丰富查询语法树&＃xff0c;会验证该客户端是否具有执行该查询的权限。创建好语法树后&＃xff0c;MySQL还会对SQl查询进行语法上的优化&＃xff0c;进行查询重写。

• Optimizer: 查询优化器

  • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径&＃xff0c;生成一个执行计划。
  • 这个执行计划表明应该使用哪些索引进行查询&＃xff08;全表检索还是使用索引检索&＃xff09;&＃xff0c;表之间的连接顺序如何&＃xff0c;最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询&＃xff0c;并将查询结果返回给用户。
  • 它使用“ 选取-投影-连接”策略进行查询。例如&＃xff1a;

    SELECT id,name FROM student WHERE gender &＃61; &＃39;女&＃39;;
这个SELECT查询先根据WHERE语句进行选取&＃xff0c;而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影&＃xff0c;而不是将属性全部取出以后再进行过滤&＃xff0c;将这两个查询条件连接起来生成最终查询结果。

• Caches & Buffers&＃xff1a; 查询缓存组件

  • MySQL内部维持着一些Cache和Buffer&＃xff0c;比如Query Cache用来缓存一条SELECT语句的执行结果&＃xff0c;如果能够在其中找到对应的查询结果&＃xff0c;那么就不必再进行查询解析、优化和执行的整个过程了&＃xff0c;直接将结果反馈给客户端。
  • 这个缓存机制是由一系列小缓存组成的。比如表缓存&＃xff0c;记录缓存&＃xff0c;key缓存&＃xff0c;权限缓存等 。
  • 这个查询缓存可以在不同客户端之间共享。
  • 从MySQL 5.7.20开始&＃xff0c;不推荐使用查询缓存&＃xff0c;并在MySQL 8.0中删除。

1.5 第三层&＃xff1a;引擎层

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

MySQL 8.0.25默认支持的存储引擎如下&＃xff1a;

1.6 存储层

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

1.7 小结

简化为三层结构&＃xff1a;

1. 连接层&＃xff1a;客户端和服务器端建立连接&＃xff0c;客户端发送 SQL 至服务器端&＃xff1b;
2. SQL 层&＃xff08;服务层&＃xff09;&＃xff1a;对 SQL 语句进行查询处理&＃xff1b;与数据库文件的存储方式无关&＃xff1b;
3. 存储引擎层&＃xff1a;与数据库文件打交道&＃xff0c;负责数据的存储和读取。

2. SQL执行流程

2.1 MySQL的执行流程

1. 查询缓存&＃xff1a;Server 如果在查询缓存中发现了这条 SQL 语句&＃xff0c;就会直接将结果返回给客户端&＃xff1b;如果没有&＃xff0c;就进入到解析器阶段。需要说明的是&＃xff0c;因为查询缓存往往效率不高&＃xff0c;所以在 MySQL8.0 之后就抛弃了这个功能。

大多数情况查询缓存就是个鸡肋&＃xff0c;为什么呢&＃xff1f;

SELECT employee_id,last_name FROM employees WHERE employee_id &＃61; 101;


• 查询缓存是提前把查询结果缓存起来&＃xff0c;而不是缓存查询计划&＃xff0c;这就意味着查询匹配的鲁棒性大大降低&＃xff0c;只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同&＃xff08;例如&＃xff1a;空格、注释、大小写&＃xff09;&＃xff0c;都会导致缓存不会命中。因此 MySQL 的查询缓存命中率不高。
• 当使用系统函数时&＃xff0c;可能在相同SQL情况下获得的查询结果不一致&＃xff0c;例如NOW函数&＃xff0c;所以是个鸡肋。
• 既然是缓存&＃xff0c;那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表&＃xff0c;只要该表的结构或者数据被修改&＃xff0c;如对该表使用了INSERT 、 UPDATE 、DELETE 、TRUNCATE TABLE 、ALTER TABLE 、DROP TABLE 或 DROP DATABASE 语句&＃xff0c;那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除&＃xff01;对于&＃96;更新压力大的数据库来说&＃xff0c;查询缓存的命中率会非常低。

2. 解析器&＃xff1a;在解析器中对 SQL 语句进行语法分析、语义分析。

• 分析器先做“ 词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句&＃xff0c;MySQL 需要识别出里面的字符串分别是什么&＃xff0c;代表什么。 MySQL 从你输入的"select"这个关键字识别出来&＃xff0c;这是一个查询语句。它也要把字符串“T”识别成“表名 T”&＃xff0c;把字符串“ID”识别成“列 ID”。
• 接着要做“ 语法分析”。根据词法分析的结果&＃xff0c;语法分析器&＃xff08;比如&＃xff1a;Bison&＃xff09;会根据语法规则&＃xff0c;判断你输入的这个 SQL 语句是否满足 MySQL 语法。

select department_id,job_id,avg(salary) from employees group by department_id;


如果SQL语句正确&＃xff0c;则会生成一个这样的语法树&＃xff1a;

3. 优化器&＃xff1a;在优化器中会确定 SQL 语句的执行路径&＃xff0c;比如是根据全表检索&＃xff0c;还是根据索引检索等。
举例&＃xff1a;如下语句是执行两个表的 join&＃xff1a;

select * from test1 join test2 using(ID)
where test1.name&＃61;&＃39;zhangwei&＃39; and test2.name&＃61;&＃39;mysql高级课程&＃39;;


• 方案1&＃xff1a;可以先从表 test1 里面取出 name&＃61;&＃39;zhangwei’的记录的 ID 值&＃xff0c;再根据 ID 值关联到表 test2&＃xff0c;再判断 test2 里面 name的值是否等于 ‘mysql高级课程’。
• 方案2&＃xff1a;可以先从表 test2 里面取出 name&＃61;‘mysql高级课程’ 的记录的 ID 值&＃xff0c;再根据 ID 值关联到 test1&＃xff0c;再判断 test1 里面 name的值是否等于 zhangwei。

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

4. 执行器&＃xff1a;
截止到现在&＃xff0c;还没有真正去读写真实的表&＃xff0c;仅仅只是产出了一个执行计划。于是就进入了执行器阶段。

在执行之前需要判断该用户是否具备权限。如果没有&＃xff0c;就会返回权限错误。如果具备权限&＃xff0c;就执行 SQL查询并返回结果。在 MySQL8.0 以下的版本&＃xff0c;如果设置了查询缓存&＃xff0c;这时会将查询结果进行缓存。

select * from test where id&＃61;1;


比如&＃xff1a;表 test 中&＃xff0c;ID 字段没有索引&＃xff0c;那么执行器的执行流程是这样的&＃xff1a;

调用 InnoDB 引擎接口取这个表的第一行&＃xff0c;判断 ID 值是不是1&＃xff0c;如果不是则跳过&＃xff0c;如果是则将这行存在结果集中&＃xff1b;
调用引擎接口取“下一行”&＃xff0c;重复相同的判断逻辑&＃xff0c;直到取到这个表的最后一行。

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

SQL 语句在 MySQL 中的流程是&＃xff1a; SQL语句→查询缓存→解析器→优化器→执行器。

2.2 MySQL8.0的执行流程

1. 确认 profiling 是否开启

mysql> select &＃64;&＃64;profiling;


mysql> show variables like &＃39;profiling&＃39;;


profiling&＃61;0 代表关闭&＃xff0c;我们需要把 profiling 打开&＃xff0c;即设置为 1&＃xff1a;

mysql> set profiling&＃61;1;


2. 多次执行相同SQL查询

执行一个 SQL 查询&＃xff08;你可以执行任何一个 SQL 查询&＃xff09;&＃xff1a;

mysql> select * from employees;


3. 查看profiles

查看当前会话所产生的所有 profiles&＃xff1a;

mysql> show profiles; # 显示最近的几次查询


4. 查看profile

显示执行计划&＃xff0c;查看程序的执行步骤&＃xff1a;

mysql> show profile;


当然你也可以查询指定的 Query ID&＃xff0c;比如&＃xff1a;

mysql> show profile for query 7;


查询 SQL 的执行时间结果和上面是一样的。

此外&＃xff0c;还可以查询更丰富的内容&＃xff1a;

mysql> show profile cpu,block io for query 6;


mysql> show profile cpu,block io for query 7;


2.3 MySQL5.7的执行流程

上述操作在MySQL5.7中测试&＃xff0c;发现前后两次相同的sql语句&＃xff0c;执行的查询过程仍然是相同的。不是会使用缓存吗&＃xff1f;这里我们需要显式开启查询缓存模式。在MySQL5.7中如下设置&＃xff1a;

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

在 /etc/my.cnf 中新增一行&＃xff1a;

query_cache_type&＃61;1


2. 重启mysql服务

systemctl restart mysqld


3. 开启查询执行计划

由于重启过服务&＃xff0c;需要重新执行如下指令&＃xff0c;开启profiling。

mysql> set profiling&＃61;1;


4. 执行语句两次&＃xff1a;

mysql> select * from locations;
mysql> select * from locations;


5. 查看profiles

6. 查看profile

显示执行计划&＃xff0c;查看程序的执行步骤&＃xff1a;

mysql> show profile for query 1;


mysql> show profile for query 2;


结论不言而喻。执行编号2时&＃xff0c;比执行编号1时少了很多信息&＃xff0c;从截图中可以看出查询语句直接从缓存中获取数据。

2.4 SQL语法顺序

需求&＃xff1a;查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人&＃xff0c;显示人数最多的第一名部门信息下面是经常出现的查询顺序&＃xff1a;

2.5 Oracle的SQL执行流程&＃xff08;了解&＃xff09;

Oracle 中采用了共享池来判断 SQL 语句是否存在缓存和执行计划&＃xff0c;通过这一步骤我们可以知道应该采用硬解析还是软解析。

我们先来看下 SQL 在 Oracle 中的执行过程&＃xff1a;

从上面这张图中可以看出&＃xff0c;SQL 语句在 Oracle 中经历了以下的几个步骤。
1.语法检查&＃xff1a;检查 SQL 拼写是否正确&＃xff0c;如果不正确&＃xff0c;Oracle 会报语法错误。
2.语义检查&＃xff1a;检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候&＃xff0c;列名写错了&＃xff0c;系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
3.权限检查&＃xff1a;看用户是否具备访问该数据的权限。
4.共享池检查&＃xff1a;共享池&＃xff08;Shared Pool&＃xff09;是一块内存池&＃xff0c;最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划&＃xff0c;来判断进行软解析&＃xff0c;还是硬解析。那软解析和硬解析又该怎么理解呢&＃xff1f;

• 在共享池中&＃xff0c;Oracle 首先对 SQL 语句进行 Hash 运算&＃xff0c;然后根据 Hash 值在库缓存&＃xff08;Library Cache&＃xff09;中查找&＃xff0c;如果存在 SQL 语句的执行计划&＃xff0c;就直接拿来执行&＃xff0c;直接进入“执行器”的环节&＃xff0c;这就是软解析。

• 如果没有找到 SQL 语句和执行计划&＃xff0c;Oracle 就需要创建解析树进行解析&＃xff0c;生成执行计划&＃xff0c;进入“优化器”这个步骤&＃xff0c;这就是硬解析。

5. 优化器&＃xff1a;优化器中就是要进行硬解析&＃xff0c;也就是决定怎么做&＃xff0c;比如创建解析树&＃xff0c;生成执行计划。
6. 执行器&＃xff1a;当有了解析树和执行计划之后&＃xff0c;就知道了 SQL 该怎么被执行&＃xff0c;这样就可以在执行器中执行语句了。

3. 数据库缓冲池Buffer Pool&＃xff08;了解&＃xff09;

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

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

3.1 缓冲池 vs 查询缓存

缓冲池和查询缓存是一个东西吗&＃xff1f; 不是。

1. 缓冲池&＃xff08;Buffer Pool&＃xff09;

在 InnoDB 存储引擎中有一部分数据会放到内存中&＃xff0c;缓冲池则占了这部分内存的大部分&＃xff0c;它用来存储各种数据的缓存&＃xff0c;如下图所示&＃xff1a;

从图中&＃xff0c;你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

缓存池的重要性&＃xff1a;

缓存原则&＃xff1a;“ 位置 * 频次 ”这个原则&＃xff0c;可以帮我们对 I/O 访问效率进行优化。

• 首先&＃xff0c;位置决定效率&＃xff0c;提供缓冲池就是为了在内存中可以直接访问数据。
• 其次&＃xff0c;频次决定优先级顺序。因为缓冲池的大小是有限的&＃xff0c;比如磁盘有 200G&＃xff0c;但是内存只有 16G&＃xff0c;缓冲池大小只有 1G&＃xff0c;就无法将所有数据都加载到缓冲池里&＃xff0c;这时就涉及到优先级顺序&＃xff0c;会优先对使用频次高的热数据进行加载。

缓冲池的预读特性&＃xff1a;

2. 查询缓存

那么什么是查询缓存呢&＃xff1f;

查询缓存是提前把查询结果缓存起来&＃xff0c;这样下次不需要执行就可以直接拿到结果。需要说明的是&＃xff0c;在MySQL 中的查询缓存&＃xff0c;不是缓存查询计划&＃xff0c;而是查询对应的结果。因为命中条件苛刻&＃xff0c;而且只要数据表发生变化&＃xff0c;查询缓存就会失效&＃xff0c;因此命中率低。

3.2 缓冲池如何读取数据

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

缓存在数据库中的结构和作用如下图所示&＃xff1a;

如果我们执行 SQL 语句的时候更新了缓存池中的数据&＃xff0c;那么这些数据会马上同步到磁盘上吗&＃xff1f;
答案是不会的&＃xff0c;当更新数据后&＃xff0c;缓冲池的数据会更新&＃xff0c;但磁盘并不会更新&＃xff0c;需要等待缓冲池同步磁盘数据&＃xff0c;缓冲池每隔一段时间都会同步一次数据&＃xff0c;这样的操作称为刷盘。

3.3 查看/设置缓冲池大小

如果你使用的是 InnoDB 存储引擎&＃xff0c;可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小。命令如下&＃xff1a;

show variables like &＃39;innodb_buffer_pool_size&＃39;;


你能看到此时 InnoDB 的缓冲池大小只有 134217728/1024/1024&＃61;128MB。我们可以修改缓冲池大小&＃xff0c;比如改为256MB&＃xff0c;方法如下&＃xff1a;

set global innodb_buffer_pool_size &＃61; 268435456;


或者&＃xff1a;

[server]
innodb_buffer_pool_size &＃61; 268435456


然后再来看下修改后的缓冲池大小&＃xff0c;此时已成功修改成了 256 MB&＃xff1a;

3.4 多个Buffer Pool实例

[server]
innodb_buffer_pool_instances &＃61; 2


这样就表明我们要创建2个 Buffer Pool 实例。

我们看下如何查看缓冲池的个数&＃xff0c;使用命令&＃xff1a;

show variables like &＃39;innodb_buffer_pool_instances&＃39;;


那每个 Buffer Pool 实例实际占多少内存空间呢&＃xff1f;其实使用这个公式算出来的&＃xff1a;

innodb_buffer_pool_size/innodb_buffer_pool_instances


也就是总共的大小除以实例的个数&＃xff0c;结果就是每个 Buffer Pool 实例占用的大小。

3.5 引申的问题

Buffer Pool 是MySQL内存结构中十分核心的一个组成&＃xff0c;你可以先把它想象成一个黑盒子。

黑盒下的更新数据流程

我更新到一半突然发生错误了&＃xff0c;想要回滚到更新之前的版本&＃xff0c;该怎么办&＃xff1f;连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复&＃xff1f;
答案&＃xff1a;Redo Log & Undo Log