MySQL（高级特性篇）04 章——逻辑架构

发布于：2025-02-10 ⋅ 阅读:(36) ⋅ 点赞:(0)

profiling的作用：

  1. 它可以精确显示出SQL语句在语法解析、查询优化、实际数据检索等不同阶段分别花费了多少时间，帮助开发者清晰地了解语句执行的瓶颈所在
  2. 可以将SQL语句执行过程拆分成多个细小的步骤，并记录每个步骤的耗时。例如，在进行复杂的多表连接查询时，能看到连接操作、过滤操作、排序操作等各自的执行时间，便于针对性地进行优化

一、逻辑架构剖析

（1）服务器处理客户端请求

  1. 服务器对客户端发送的请求做了什么处理，才能产生最后的处理结果呢？
  2. 下面具体展开看一下：

（2）Connectors

  1. Connectors 是不同语言与SQL交互的方式。MySQL是基于TCP的网络程序，有自己的应用层协议。使用 MySQL 有两种途径：一是编写代码建立 TCP 连接，按其协议交互；二是调用如Native C API、JDBC、PHP等各语言的MySQL Connector或通过ODBC这类 SDK。不过，借助SDK访问MySQL，本质仍是基于TCP连接并通过MySQL协议进行交互
  2. 接下来的MySQL Server结构可以分为如下的三层：

（3）第1层：连接层

  1. 连接层概念：连接层即Connection Pool
  2. 建立连接：系统（客户端）访问MySQL服务器前，首先要建立TCP连接，通过三次握手来完成
  3. 身份认证与权限获取：
    1. 连接建立成功后，MySQL服务器对TCP传输的账号密码进行身份认证并获取权限
    2. 若用户名或密码错误，客户端会收到 “Access denied for user” 错误，程序结束执行
    3. 若用户名和密码认证通过，服务器会从权限表查出账号权限并与连接关联，后续权限判断依赖此时读取的权限
  4. 线程分配：
    1. TCP连接收到请求后，需分配一个线程专门与客户端交互
    2. 存在线程池，每个连接从线程池中获取线程，避免了创建和销毁线程的开销，之后按流程继续处理

（4）第2层：服务层

  1. 服务层就是SQL Interface、Caches & Buffers、Parser、Optimizer
  2. SQL Interface（SQL接口）：接收用户SQL命令并返回查询结果，支持DML、DDL、存储过程、视图、触发器、自定义函数等多种SQL语言接口，例如SELECT...FROM语句就会调用该接口
  3. Caches & Buffers（查询缓存组件）：MySQL内部有多种Cache和Buffer，如Query Cache 可缓存SELECT语句执行结果，若命中缓存可直接反馈结果，无需再进行查询解析、优化和执行。缓存机制由表缓存、记录缓存等小缓存组成，且可在不同客户端间共享，但从MySQL 5.7.20开始不推荐使用，在MySQL8.0中已删除
  4. Parser（解析器）：对SQL语句进行语法和语义分析，将其分解成数据结构用于后续处理。解析时会验证和解析SQL命令，创建语法树，根据数据字典丰富语法树，验证客户端执行查询的权限，还会进行语法优化和查询重写。若分解过程出错，则表明SQL语句不合理
  5. Optimizer（查询优化器）：在SQL语句语法解析后、查询前确定执行路径，生成执行计划，包括使用的索引（全表检索或索引检索）和表连接顺序，最后按计划调用存储引擎方法执行查询并返回结果

（5）第3层：引擎层

  1. 引擎层就是Pluggable Storage Engines
  2. 插件式存储引擎层（Storage Engines），该层负责MySQL数据的存储与提取，对物理服务器底层数据执行操作，服务器借助API与存储引擎通信。因不同存储引擎功能存在差异，用户可根据实际需求进行选择

（6）存储层

  1. 存储层就是File System和File & logs
  2. MySQL里的各种数据，像数据库、表的具体定义、表里面每一行的内容还有索引这些，都是以文件的形式放在文件系统里的，而且这些数据会和存储引擎相互配合着工作。有些存储引擎，像InnoDB，它可以不依靠文件系统，直接去用存储设备存数据。但现在的文件系统功能很强，能很好地完成数据存储管理的工作，所以没必要让InnoDB 直接去管理存储设备

（7）小结

  1. MySQL架构图我们可以简化如下：
  2. 简化为三层结构：
    1. 连接层：客户端和服务器端建立连接
    2. SQL层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关
    3. 引擎层：与数据库文件打交道，负责数据的存储和读取

二、SQL执行流程

（1）MySQL中的SQL执行流程

图示：

2.1.1查询缓存

  1. Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在MySQL8.0之后就抛弃了这个功能，因为效率不高
  2. MySQL的查询缓存多数情况下实用性不佳，原因如下：
    1. 匹配要求苛刻：MySQL查询缓存存储的是查询结果而非查询计划，只有查询操作完全相同时才能命中缓存，查询请求中任何字符差异（如空格、注释、大小写）都会导致缓存不命中，使得命中率降低
    2. 部分查询不缓存：若查询请求包含系统函数、用户自定义变量和函数以及某些系统表（如mysql、information_schema、performance_schema数据库中的表），则不会被缓存。因为部分系统函数多次调用结果可能不同，缓存结果会导致后续查询结果错误
    3. 缓存易失效：一旦表的结构或数据被修改（如执行INSERT、UPDATE等语句），使用该表的所有缓存查询都会失效并被删除。对于更新压力大的数据库，查询缓存命中率极低
  3. 看看是否开启了查询缓存（值为0代表关闭查询缓存，值为1代表开启了查询缓存，值为2代表DEMAND），由于8.0版本已经删掉了查询缓存，因此8.0版本中执行该语句也没有用 
  4. DEMAND代表“按需使用”。此时，对于我们确定要使用查询缓存的语句，可以使用SQL_CACHE显式指定，像下面这个语句一样 
    SELECT SQL_CACHE * FROM test WHERE ID = 5;
  5.  确定该语句不需要使用查询缓存 
    SELECT SQL_NO_CACHE * FROM test WHERE ID = 5;
  6.  监控查询缓存的命中率：
  7. Qcache_free_blocks：表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理
  8. Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整
  9. Qcache_hits：表示有多少次命中缓存
  10. Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小
  11. Qcache_queries_in_cache：当前缓存中缓存的查询数量
  12. Qcache_not_cached：表示因为query_cache_type的设置而没有被缓存的查询数量
  13. Qcache_inserts： 表示多少次未命中缓存,然后插入
  14. Qcache_total_blocks：当前缓存的block数量
  15. 如何开启查询缓存？（修改配置文件后，要重启MySQL服务，才会生效）

2.1.2解析器

  1. 在解析器中对 SQL 语句进行语法分析、语义分析
  2. 分析器先做“词法分析”。将由多个字符串和空格组成的SQL语句进行识别，明确其中各个字符串的含义，例如识别出 “select” 为查询语句关键字，“T” 为表名，“ID” 为列名
  3. 接着，要做“语法分析”。基于词法分析的结果，语法分析器（如 Bison）依据语法规则判断输入的SQL语句是否符合MySQL语法
  4. 以此为例： 
    select department_id,job_id,avg(salary) from employees group by department_id;
#该语句显然在语法上有问题
  5. 如果SQL语句正确，则会生成一个这样的语法树：

2.1.3优化器

  1. 优化器作用：确定SQL语句的执行路径，如选择全表检索或索引检索等
  2. 执行方案示例：对于 select * from test1 join test2 using(ID) where test1.name='zhangwei' and test2.name='mysql高级课程' 语句，存在两种执行方案。方案一是先从 test1 表取name='zhangwei'记录的ID值，再关联test2表并判断name是否为 'mysql高级课程'；方案二则是先从test2表取name='mysql高级课程'记录的ID值，再关联test1表并判断 name 是否为'zhangwei'。两种方案逻辑结果相同，但执行效率不同，优化器负责选择更优方案
  3. 执行流程：优化器阶段完成后，确定语句执行方案，随后进入执行器阶段
  4. 优化器阶段划分：查询优化器可分为逻辑查询优化阶段和物理查询优化阶段

2.1.4执行器

  1. 截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了执行器阶段
  2. 在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存
  3. 例如在该语句中，ID字段没有索引，那么执行器的执行流程是这样的： 
    select * from test where id=1;
  4. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是1，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行
  5. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
  6. 至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多
  7. SQL 语句在MySQL中的流程是： SQL语句→查询缓存→解析器→优化器→执行器

（2）MySQL8中SQL执行原理

  1. 确认profiling是否开启，开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下： 
    select @@profiling;

或者

show variables like 'profiling';
  2. profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1： 
    set @@profiling = 1;

  3. Profiling功能由MySQL会话变量：profiling控制。默认是OFF（关闭状态）
  4. 多次执行相同SQL查询： 
    select * from employees;
  5. 查看当前会话所产生的所有profiles： 
    show profiles; # 显示最近的几次查询

  6. 查看profile，显示执行计划，查看程序的执行步骤（直接show profile，默认情况下是最后一条查询语句）：
  7. 当然你也可以查询指定的Query ID，比如：
  8. 此外，还可以查询更丰富的内容： 
    show profile cpu,block io for query 6;
  9. 如图：

（3）MySQL5.7中SQL执行原理

  1. 先在配置文件中开启查询缓存，在 /etc/my.cnf 中新增一行： 
    query_cache_type=1
  2. 重启mysql服务 
    systemctl restart mysqld
  3. 开启查询执行计划，由于重启过服务，需要重新执行如下指令，开启profiling
  4. 执行语句两次： 
    select * from locations;
  5. 查看profiles：
  6. 分别查看6和7的profile，显示执行计划，查看程序的执行步骤：
  7. 可以看到，开启查询缓存后，多了一行：query cache for query
  8. 结论不言而喻。执行编号2时，比执行编号1时少了很多信息，从截图中可以看出查询语句直接从缓存中获取数据

（4）SQL语法顺序

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

三、数据库缓冲池

InnoDB存储引擎以页为单位管理存储空间，增删改查操作本质是对页面的访问。由于磁盘I/O耗时多，内存操作效率高，DBMS会申请内存作为数据缓冲池（Buffer Pool），在访问页面时先将磁盘上的页缓存到Buffer Pool中。这样做能使磁盘活动最小化，减少磁盘I/O时间，对提升SQL语句查询性能至关重要，若索引数据在缓冲池中，访问成本将大幅降低

（1）缓冲池vs查询缓存

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

3.1.1缓冲池（Buffer Pool）

  1. 如下图所示
  2. 缓冲池的重要性：
    1. ​​​​​​​InnoDB处理客户端请求时，访问页数据需将整个页加载到内存，即便只访问一条记录
    2. 读写访问后，页数据会缓存起来，后续再次访问该页可省去磁盘I/O开销
  3. 缓存原则：
    1. “位置 * 频次” 原则可优化I/O访问效率
    2. 位置方面，缓冲池使数据能在内存中直接访问，内存操作效率高于硬盘
    3. 频次方面，因缓冲池大小有限，无法加载所有数据，会优先加载使用频次高的热数据
  4. 缓冲池的预读特性：
    1. 缓冲池用于提升I/O效率
    2. 依据“局部性原理”，使用某些数据后大概率会使用其周围数据，因此采用“预读”机制提前加载，减少未来可能的磁盘I/O操作

3.1.2查询缓存

MySQL的查询缓存是将查询结果提前缓存，下次查询时可直接获取结果，而非缓存查询计划。但由于命中条件苛刻，且数据表发生变化时缓存即失效，导致其命中率较低

（2）缓冲池如何读取数据

  1. 缓冲池会尽力留存常用数据。数据库进行页面读操作时，（1）先判断页面是否在缓冲池，（2）若在则直接读取；（3）若不在，会通过内存或磁盘将页面存入缓冲池后再读取
  2. 缓存在数据库中的结构和作用如下图所示：
  3. 执行SQL语句更新缓存池数据时，数据不会马上同步到磁盘。对数据库数据进行修改时，首先修改缓冲池中页的记录信息，之后数据库会按一定频率将修改后的数据刷新到磁盘，并非每次更新操作都会立刻进行磁盘回写，这种方式有助于提升数据库整体性能

（3）查看/设置缓冲池的大小

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

  2. 你能看到此时 InnoDB 的缓冲池大小只有134217728/1024/1024=128MB。我们可以修改缓冲池大小，比如改为256MB，方法如下： 
    set global innodb_buffer_pool_size = 268435456;
  3. 或者： 
    [server]
innodb_buffer_pool_size = 268435456

（4）多个Buffer Pool实例

  1. 这样就表明我们要创建2个Buffer Pool 实例： 
    [server]
innodb_buffer_pool_instances = 2
  2. 我们看下如何查看缓冲池的个数，使用命令： 
    show variables like 'innodb_buffer_pool_instances';

  3. Buffer Pool基础及并发问题：
    1. Buffer Pool本质是InnoDB向操作系统申请的连续内存空间，多线程访问其中数据需加锁
    2. 当Buffer Pool特别大且多线程并发访问高时，单一Buffer Pool会影响请求处理速度
  4. Buffer Pool拆分策略：可将大的Buffer Pool拆成若干小的Buffer Pool实例，每个实例独立申请内存、管理链表，多线程并发访问时互不影响，能提高并发处理能力
  5. 实际内存大小计算：每个Buffer Pool实例占用的内存大小等于Buffer Pool总共的大小除以实例的个数
  6. InnoDB相关规定：
    1. 当InnoDB_buffer_pool_size小于1G时，设置多个实例无效，InnoDB 会默认将innodb_buffer_pool_instances的值修改为1
    2. 鼓励在Buffer Pool大于或等于1G时设置多个Buffer Pool实例

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布