【高级篇04】MySQL逻辑架构

第四章：逻辑架构

逻辑架构

• 第一层：连接层。客户端访问MySQL服务器，首先建立TCP连接，经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。
• 第二层：服务层。
  • SQL Interface ：SQL接口。接收SQL命令、返回查询结果。
  • Parser：解析器。对SQL语句进行语法分析、语义分析。创建语法树。
  • Optimizer：查询优化器。生成执行计划，表明应该使用哪些索引进行查询。
  • Caches & Buffers：查询缓存组件。从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。
• 第三层：插件式存储引擎层。负责MySQL中数据的存储和提取。与数据库文件进行通信。
  

SQL执行流程

1. 查询缓存。如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端。

   但MySQL的查询缓存命中率不高。因为查询缓存所缓存的是查询计划，所以只有完全相同的查询操作才会命中查询缓存，所以在MySQL8.0之后抛弃。

2. 解析器。分析器先做“词法分析”，分析你输入的字符串代表什么；之后做“语法分析”，根据语法规则，判断输入的SQL语句，是否满足MySQL语法，生成语法树。
   

3. 优化器：在优化器中确定SQL语句的执行路径，交出执行计划给执行器。

4. 执行器：执行之前判断该用户是否有权限。如果没有，就返回权限错误。如果具备权限，就执行SQL查询（调用存储引擎接口）并返回结果。

   
   图：SQL的执行流程

MySQL中的Profiling工具，通过该工具可以获取一条SQL语句在执行过程中多种资源的消耗情况，如CPU、IO、程序的执行步骤等等。

set profiling=1; # 设置工具打开
select * from employees; # 执行查询
show profile; # 显示执行计划，查看程序的执行步骤

图：select * from employees 的执行步骤

数据库缓冲池

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作基本都是在访问页面。

MySQL会申请占用内存来作为数据库缓冲池，将磁盘中的页缓存到内存中的数据库缓冲池之后才可以访问，大大减少了I/O时间，提高了查询效率。

• 缓冲池是什么？

  InnoDB存储引擎中一部分数据会放到内存中，缓冲池则占了这部分内存的大部分。

  

​ 图：InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

• 缓存原则？

  “位置 * 频次”。数据存放在内存中，使用频次高的数据优先加载到缓存池中。

• 缓存池如何读取数据？

• 执行SQL修改了缓冲池中的数据，数据会马上同步到磁盘上吗？

  当对数据库中的记录进行修改时，会先修改缓冲池中的页里的记录信息，然后会以一定的频率刷新到磁盘上，并不是立刻回写。缓冲池会采用checkpoint机制将数据回写到磁盘上。

  比如，当缓冲池不够用时，可以强行采用checkpoint的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。脏页：缓冲池中被修改过的页，与磁盘的数据页不一致。

• 查看/设置缓冲池大小

  show variables like 'innodb_buffer_pool_size';
  

  set global innodb_buffer_pool_size = 268435456;