进阶篇(4) 逻辑架构

長夜難明
209 阅读13分钟

1. 逻辑架构剖析

MySQL是典型的C/S架构，服务端程序使用mysqld

1.1 服务器处理客户端请求

服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例： image.png

架构图：

image.png

1.2 connectors

不同语言与SQL的交互，我们可以编写代码，和 MySQL Server 建立TCP连接，再按照定义好的协议进行交互，或调用SDK。通过SDK访问MySQL本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。

1.3 第1层：连接层

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

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

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

TCP连接池采用长连接模式 复用TCP连接

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

1.4 第2层：服务层

  • SQL Interface: SQL接口
    • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT 就是调用SQL Interface
  • Parser: 解析器
    • 对 SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构 传递到后续步骤，以后SQL语句的传递和处理基于这个结构的。分解中遇到错误 -> SQL语句不合理。
    • SQL命令被解析器验证和解析，并为其创建 语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
  • Optimizer: 查询优化器
    • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划 。
    • 这个执行计划表明应该 使用哪些索引 进行查询，表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将 查询结果返回给用户。
    • 它使用 选取-投影-连接 策略进行查询。例如：
    SELECT id,name FROM student WHERE gender = '女';
    • 这个SELECT查询先根据WHERE语句进行选取 ，而不是将表全部查询出来以后再进行gender过滤。这个SELECT查询先根据id和name进行属性投影 ，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
  • Caches & Buffers： 查询缓存组件
    • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行整个过程，直接将结果反馈给客户端。
    • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
    • 这个查询缓存可以在 不同客户端之间共享 。
    • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

1.5 第3层：引擎层

插件式存储引擎层，负责MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。

1.6 存储层

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

1.7 小结

架构图简化版

image.png

简化为三层结构：

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

2. SQL 执行流程

2.1 MySQL 中的 SQL执行流程

image.png

1.查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，直接返回结果；没有则进入解析器阶段。查询缓存往往效率不高，MySQL8.0 之后抛弃了这个功能。

大多数情况查询缓存就是个鸡肋，为什么呢？

查询缓存提前把查询结果缓存起来，而不是缓存查询计划。这意味着查询匹配的 鲁棒性大大降低 ，只有相同的查询操作才会命中查询缓存 。两个查询请求在任何字符上的不同（例如：空格、注释、 大小写），都会导致缓存不会命中。命中率不高 。

如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、 information_schema数据库中的表，那这个请求就不会被缓存。以函数 NOW 举例 ，但如果调用查询缓存，时间的结果是错误的。

缓存有缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的 结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 等语句，那使用该表的所有缓存查询被删除！对于 更新压力大的数据库 来说，查询缓存的命中率会非常低。

一般建议在静态表(很少更新的表)里使用查询缓存

我们可以在 my.cnf文件中决定是否使用查询缓存

query_cache_type=2 # 0代表关闭，1代表开启，2代表使用语句时加相应关键字开启

SQL_CACHE 关键字开启查询缓存

SELECT SQL_CACHE * FROM test WHERE ID=5;

查看查询缓存是否开启/具体内容

show variables like '%query_cache_type';
show status like '%Qcache%';

2.解析器：在解析器中对 SQL 语句进行词法分析和语法分析。

image.png

分析器先做 词法分析。输入的SQL语句由多个字符串和空格组成，MySQL需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。把字符串“ID”识别成“列 ID”。

接着，要做 语法分析。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL语法 。

select username, ismale from userinfo where age > 20 AND level > 5 AND 1 = 1;

如果SQL语句正确，则会生成一个这样的语法树：

image.png

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

一条查询可以有很多种执行方式，最后返回相同结果，优化器的作用就是找到最好的执行计划。

举例：如下语句是执行两个表的 join：

select * from t1 join t2 using(ID)
where test1.name='lfd' and test2.name='worker';

方案1：可以先从表 t1 里面取出 name='lfd'的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 name的值是否等于 'worker'。
方案2：可以先从表 t2 里面取出 name='worker' 的记录的 ID 值，再根据 ID 值关联到 t1， 再判断 t1 里面 name的值是否等于 lfd。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。
优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

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

4. 执行器：

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

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

select * from test where id=1;

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取 下一行，重复相同的判断逻辑，直到最后一行。 
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器

image.png

2.2 MySQL8中SQL执行

  1. 确认profiling 是否开启
select @@profiling; #收集在SQL执行时所使用的资源情况

image.png

set profiling=1; # 开启
  1. 多次执行相同SQL查询
select * from employees;
  1. 查看profiles
 show profiles; # 显示最近的几次查询
  1. 查看profile
show profile; # 显示最后一次查询执行计划，查看程序的执行步骤

image.png

当然你也可以查询指定的 Query ID，比如：

show profile for query 4;

此外，还可以查询更丰富的内容：

show profile cpu,block io for query 4;

2.3 MySQL5.7中SQL执行原理

  1. 配置文件中开启查询缓存 在 /etc/my.cnf 中新增一行：
query_cache_type=1
  1. 重启mysql服务
systemctl restart mysqld
  1. 开启查询执行计划
set profiling=1;
  1. 多次执行相同语句
  2. 查看profiles

image.png
6. 查看 query 1 和 2 的profile

show profile for query 1;

image.png

show profile for query 2;

image.png
执行编号2时，直接从缓存获取数据

2.4 SQL语法顺序

image.png

3. 数据库缓冲池(buffer pool)

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

好处:让磁盘活动最小化，从而 减少与磁盘直接进行 I/O 的时间 。访问成本降低很多

3.1 缓冲池 vs 查询缓存

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

  1. 缓冲池（Buffer Pool）
    InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种 数据的缓存，如下图所示：

image.png

缓存池的重要性：
对于 InnoDB来说，所有的数据和索引的存储都是以 页 的方式存储在 表空间，即还是存储在磁盘中。缓冲池可以帮我们消除CPU和磁盘的鸿沟。访问某条记录时，将该记录的页全部加载到内存中，在进行读写访问后不急着释放，而是将其缓存起来，若再次访问该页面的内容时，省去了IO开销

缓存原则：
位置 * 频次 这个原则，可以帮我们对 I/O 访问效率进行优化。 位置决定效率，缓冲池让我们可以在内存中直接访问数据。 频次决定优先级顺序。缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，优先对使用频次高的热数据进行加载 。

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

3.2 缓冲池如何读取数据

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

缓存在数据库中的结构和作用如下图所示： image.png

如果我们执行SQL语句时更新了缓冲池的数据，它们会马上同步到磁盘上吗？
以一定频率刷新到磁盘上，缓冲池使用checkpoint机制将数据写回磁盘。比如当缓冲池不够用时，采用checkpoint 方式将 不常用的脏页 写回磁盘。 脏页 指的是缓冲池中修改过的页面

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

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

show variables like 'innodb_buffer_pool_size';

image.png
InnoDB 的缓冲池大小只有 134217728/1024/1024=128MB。我们可以修改缓冲池大小，比如 改为256MB，方法如下：

set global innodb_buffer_pool_size = 268435456;

或者：

[server] 
innodb_buffer_pool_size = 268435456

3.4 多个Buffer Pool实例

多用户访问 buffer pool时要加锁，我们可以设置多个buffer pool，则避免了加锁问题，提高并发能力。

[server]
innodb_buffer_pool_instances = 2

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

show variables like 'innodb_buffer_pool_instances';

那每个 Buffer Pool 实例实际占多少内存空间呢？其实使用这个公式算出来的：

innodb_buffer_pool_size/innodb_buffer_pool_instances

管理各个 buffer pool 也需要性能开销。

InnoDB规定 innodb_buffer_pool_size < 1G时只能1个实例

3.5 引申问题

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

image.png 我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复？

答案：Redo Log & Undo Log(以后再讲)

avatar
文章
阅读
粉丝