【MySQL 系列】MySQL 架构篇

在我们开始相识 MySQL 焦点功能之前，起首我们需要站在一个全局的视角，来看 SQL 是怎样运作执行的。通过这种方式，我们可以在头脑中构建出一幅 MySQL 各组件之间的协同工作方式，有助于我们加深对 MySQL 服务器的理解。
  

---

  

---

1、MySQL 的逻辑架构

MySQL 的架构共分为两层：Server 层和存储引擎层
Server 层：负责建立毗连、分析和执行 SQL。MySQL 大多数的焦点功能模块都在这实现，主要包括毗连池，执行器、优化器、解析器、预处置惩罚器、查询缓存等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现；
存储引擎层：负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，差别的存储引擎共用一个 Server 层。如今最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的。

---

2、SELECT 语句执行原理

2.1、毗连器

当我们通过客户端访问 MySQL 服务器前，要做的第一步就是需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的
毗连的过程需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的。
TCP 网络毗连建立乐成后，服务端与客户端之间会建立一个 session 会话，紧接着会对登录的用户名和密码进行效验，起首会查询自身的用户表信息，判定输入的用户名是否存在，如果存在则会判定输入的密码是否正确。密码正确后，会从毗连池中分配一条空闲线程维护当前客户端的毗连；如果没有空闲线程，则会创建一条新的工作线程。之后线程会查询用户所拥有的权限，并对其授权，后续 SQL 执行时，都会先判定是否具备相应的权限。
空闲毗连在凌驾最大空闲时长（wait_timeout）之后，毗连器会自动将它断开。
一个处于空闲状态的毗连被服务端自动断开后，客户端并不会马上知道，比及客户端在发起下一个哀求的时候，才会收到报错。

2.2、毗连池

Connection Pool，是步调启动时建驻足够的数据库毗连，并将这些毗连构成一个毗连池，由步调动态地对池中的毗连进行申请、使用、释放。主要是为了复用线程、管理线程以及限制最大毗连数。
当一个客户端实行与 MySQL 建立毗连时，MySQL 内部都会派发一条线程负责处置惩罚该客户端接下来的所有工作。
线程的频繁创建和烧毁都会耗费大量资源，通过复用线程的方式，不仅能减少开销，还能避免内存溢出等问题。
数据库毗连池可以设置最小毗连数和最大毗连数：

• 最小毗连数：是毗连池一直保持的数据库毗连，如果应用步调对数据库毗连的使用量不大，将会有大量的数据库毗连资源被浪费；
  
• 最大毗连数：是毗连池能申请的最大毗连数，如果数据库毗连哀求凌驾次数，反面的数据库毗连哀求将被参加到等待队列中。
  

2.3、查询缓存

如果查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条下令，这个查询缓存是以 key-value 情势保存在内存中的，key 为 SQL 查询语句的哈希值，value 为 SQL 语句查询的效果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继承执行，等执行完后，查询的效果就会被存入查询缓存中。
查询缓存每每弊大于利，因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。
   这里说的查询缓存是 server 层的，也就是 MySQL8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。
  2.4、解析 SQL

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由解析器来完成。解析器可以将输入的 SQL 语句转换为计算机可以理解的情势（语法树，Syntax Tree）。
解析器会做如下两件事情：

• 词法解析：MySQL 会根据输入的字符串辨认出关键字出来，构建出 SQL 语法树；
  
• 语法解析：根据词法分析的效果，语法分析器会根据语法规则，判定输入的 SQL 语句是否满足语法规则。
  

语法树大致结构如下：

当词法分析和语法分析出错时，分析器会抛出非常。好比语法结构出错、出现了无法辨认的字符等。
   表大概字段不存在，并不是在分析器里做的，而是在预处置惩罚阶段完成。
  2.5、执行 SQL

每条 SQL 语句主要可以分为以下这三个阶段：① prepare ，预处置惩罚阶段；② optimize ，优化阶段；③ execute ，执行阶段。
预处置惩罚器：检查 SQL 查询语句中的表大概字段是否存在；将 select * 中的 * 符号，扩展为表上的所有字段；
优化器：化器会根据语法树制定多个执行计划，然后确定最优的执行计划。

• 在表里存在多个索引的时候，决定使用哪个索引；
  
• 在一个语句有多表关联（join）的时候，决定各个表的毗连次序。
  

执行器：判定用户权限，然后根据执行计划执行 SQL 语句。
2.6、SELECT 查询过程

总结一下一条查询 SQL 语句的执行流程：

• 客户端通过毗连器毗连 MySQL 服务；
  
• 毗连乐成后向 SQL 接口发送 SQL 语句哀求；
  
• SQL 接口吸收到 SQL 查询语句会先去缓存查询，如果命中返回给客户端，否则交给解析器；
  
• 解析器在拿到 SQL 语句后会判定语法是否正确，正确会生成 SQL 语法树交给优化器，否则报错给客户端；
  
• 优化器会根据 SQL 语法树生成一个最优的执行计划交给执行器执行；
  
• 执行器拿到执行计划调用存储引擎来获取数据响应给客户端；
  
• 完成！！！
  

---

3、UPDATE 语句执行原理

在数据库里面，我们说的 update 操纵其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery() 和 doUpdate() 的方法，没有 doDelete() 和 doInsert()。
3.1、缓冲池

起首，InnnoDB 的数据都是放在磁盘上的，InnoDB 操纵数据有一个最小的逻辑单元，叫做页（索引页和数据页）。我们对于数据的操纵，不是每次都直接操纵磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool.
下一次读取相同的页，先判定是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。
修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不同等的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 BufferPool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
BufferPool 是 InnoDB 里面非常紧张的一个结构，它的内部又分成几块区域。这里我们乘隙到官网来认识一下 InnoDB 的内存结构和磁盘结构。
3.2、InnoDB 内存结构和磁盘结构

BufferPool 主要分为3个部分：Buffer Pool、Change Buffer、AdaptiveHash Index，另外还有一个（redo）logbuffer。

3.2.1、BufferPool

BufferPool 缓存的是页面信息，包括数据页、索引页。检察服务器状态，里面有很多跟 BufferPool 相关的信息：

1. SHOW STATUS LIKE '%innodb_buffer_pool%';

复制代码

这些状态都可以在官网查到详细的含义，用搜索功能。
BufferPool 默认大小是 128M（134217728字节），可以调解。检察参数（系统变量）：

1. SHOW VARIABLES like' %innodb_buffer_pool%';

复制代码

这些参数都可以在官网查到详细的含义，用搜索功能。
内存的缓冲池写满了怎么办？InnoDB 用 LRU 算法来管理缓冲池（链表实现，不是传统的 LRU，分成了Younf 和 Old），经过淘汰的数据就是热门数据。
内存缓冲区对于提升读写性能有很大的作用。思考一个问题：当需要更新一个数据页时，如果数据页在 BufferPool 中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操纵。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢？
3.2.2、ChangeBuffer

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判定命据是不是重复（唯一性检查）。这种情况下可以先把修改记载在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。
这一块区域就是 ChangeBuffer。5.5 之前叫 InsertBuffer 插入缓冲，如今也能支持 Delete 和 Update。
末了把 ChangeBuffer 记载到数据页的操纵叫做 merge。什么时候发生 merge？有几种情况：在访问这个数据页的时候，大概通过后台线程、大概数据库 shutdown、redolog 写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 ChangeBuffer（写缓冲）。写多读少的业务，调大这个值：

1. SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

复制代码

代表 ChangeBuffer 占 BufferPool 的比例，默认 25%。
3.2.3、Log Buffer

思考一个问题：如果 BufferPool 里面的脏页还没有刷入磁盘时，数据库宕机大概重启，这些数据丢失。如果写操纵写到一半，以致可能会破坏数据文件导致数据库不可用。
为了避免这个问题，InnoDB 把所有对页面的修改操纵专门写入一个日记文件，并且在数据库启动时从这个文件进行恢复操纵（实现 crash-safe）——用它来实现事件的持久性。

这个文件就是磁盘的 Redo Log（叫做重做日记），对应于 /var/lib/mysql/ 目录下的 ib_logfile0 和 ib_logfile1，每个 48M。
这种日记和磁盘配合的整个过程 ，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日记，再写磁盘。

1. show variables like 'innodb_log%';

复制代码

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日记再写磁盘？
我们先来相识一下随机 I/O 和次序 I/O 的概念：磁盘的最小构成单元是扇区，通常是 512 个字节。操纵系统和磁盘打交道，读写磁盘，最小的单元是块 Block。

如果我们所需要的数据是随机分散在差别页的差别扇区中，那么找到相应的数据需要比及磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才气找到我们所需要的一块数据，依次进行此过程直到找完所有数据，这个就是随机 IO，读取数据速度较慢。
假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫次序 IO。
刷盘（将内存中的数据写入磁盘）是随机 I/O，而记载日记是次序 I/O，次序 I/O 效率更高。因此先把修改写入日记，可以耽误刷盘时机，进而提升系统吞吐。
固然 Redo Log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日记文件的数据，认 16M，它一样可以节省磁盘 IO.

需要留意：Redo Log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 bufferpool。Redo Log 写入磁盘，不是写入数据文件。那么，Log Buffer 什么时候写入 log file？在我们写入数据到磁盘的时候，操纵系统本身是有缓存的。flush 就是把操纵系统缓冲区写入到磁盘。
Redo Log 的特点：

• Redo Log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有；
  
• 不是记载数据页更新之后的状态，而是记载这个页做了什么改动，属于物理日记；
  
• Redo Log 的大小是固定的，前面的内容会被覆盖。
  

除了 Redo Log之外，还有一个跟修改有关的日记，叫做 Undo Log（打消日记或回滚日记），记载了事件发生之前的数据状态，分为 insert Undo Log 和 update Undo Log。如果修改数据时出现非常，可以用 Undo Log 来实现回滚操纵（保持原子性）。
3.3、UPDATE 更新过程

有了 Redo Log 和 Undo Log，我们来总结一下一个 Update 操纵的流程。

1. UPDATE user set name = 'lizhengi' where id=1;

复制代码

• 在执行前需要：① 毗连器毗连数据库；② 分析器通过词法分析和语法分析知道这是一条更新语句；③ 优化器决定要使用的索引等；④ 执行器负责具体的执行过程；
  
• 事件开始，从内存（buffer poll）或磁盘（data file）取到包含这条数据的数据页，返回给 Server 的执行器；
  
• Server 的执行器修改数据页的这一行数据的值为 lizhengi；
  
• 记载 name=lisa（原值）到 Undo Log；
  
• 记载 name=lizhengi 到 Redo Log；
  
• 调用存储引擎接口，记载数据页到 buffer pool（修改 name= lizhengi）；
  
• 事件提交。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。