MySQL架构与SQL执行流程

Love The Way You Lie 2023-10-12 23:04 30阅读 0赞

## MySQL语句执行流程 ##

1.  **连接器（Connector）：** 当客户端发送一个连接请求时，连接器负责接受并建立与MySQL服务器的连接。它进行身份验证、权限验证等操作，并为客户端分配一个会话（Session）。
2.  **查询缓存（Query Cache）：** 在连接建立后，MySQL可以使用查询缓存来提高查询性能。查询缓存会检查当前查询是否已经被缓存过，如果是，则直接返回缓存的结果，避免了后续的查询处理。然而，由于查询缓存的效果受到多个因素的影响，如缓存的大小、查询的复杂性等，有时候禁用查询缓存可能会获得更好的性能。
3.  **解析器（Parser）：** MySQL解析器接收到待执行的SQL语句后，进行语法解析和语义分析，生成相应的解析树。
4.  **预处理器：** 它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是 否存在，检查名字和别名，保证没有岐义。
5.  **优化器（Optimizer）：** 在解析阶段完成后，优化器会分析查询语句，考虑索引、表的顺序、连接类型等因素，生成最优的查询执行计划。（比如有多个索引的情况下使用那个索引）
6.  **执行计划：** 比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？可以使用\*EXPLAIN select name from user where id=1:\*该语句查看执行计划
7.  **执行引擎（Execution Engine）：** 执行引擎根据优化器生成的执行计划，负责实际的查询操作。它与存储引擎进行交互，从磁盘或内存中读取数据，并进行数据过滤、排序、连接等操作。
8.  **存储引擎（Storage Engine）：** 存储引擎是MySQL的核心组件之一，负责实际的数据存储和访问操作。它根据执行引擎的请求，读取或写入数据，并返回相应的结果。
9.  **返回结果：** 执行引擎将查询的结果返回给客户端，客户端可以获取到查询结果集、受影响的行数或错误信息等。

**这里需要注意的是在MySQL 8中，查询缓存功能已经被移除。在之前的MySQL版本中，查询缓存允许将查询结果缓存在内存中，以便在后续相同的查询请求中直接返回缓存结果，从而提高查询性能。** 主要的原因有以下几点：

1.  **高并发写入下的性能问题：** 查询缓存需要对查询进行精确匹配，一旦查询发生变化，缓存就会失效。在高并发写入负载下，频繁的更新操作会导致大量查询缓存的失效，造成额外的开销和性能下降。这使得查询缓存在具有高写入负载的场景下效果有限。
2.  **内存资源消耗：** 查询缓存需要占用大量内存来存储查询结果，特别是在具有大量查询和结果集的情况下。对于大型数据库系统来说，维护和管理查询缓存所需的内存资源会占用宝贵的系统资源，可能导致内存不足和性能下降。
3.  **不一致性和复杂性：** 查询缓存的存在可能导致数据不一致性的问题。当数据库中的数据发生变化时，缓存的查询结果可能仍然返回旧的结果，而不是最新的数据。此外，查询缓存的复杂性和维护成本也是考虑因素之一。

具体执行流程图如下：

![format_png][]

### 存储引擎 ###

存储引擎（Storage Engine）是数据库管理系统（DBMS）中负责数据存储和访问的组件。它负责将数据持久化到磁盘上，并提供数据的读取、写入、更新和删除等操作。

在MySQL中，存储引擎是可插拔的，这意味着可以根据需求选择适合的存储引擎来管理数据。MySQL提供了多种存储引擎，每个存储引擎都有其独特的特点、性能特征和适用场景。

以下是一些常见的MySQL存储引擎：

1.  **InnoDB：** InnoDB是MySQL的默认存储引擎，它支持事务、行级锁定和外键约束等特性。InnoDB通过使用多版本并发控制（MVCC）来提供高度并发性和数据完整性。
2.  **MyISAM：** MyISAM是MySQL的另一个常见存储引擎，它不支持事务和行级锁定。MyISAM适合于读写比例低、对并发性要求不高的应用场景。它具有较高的插入和查询速度。
3.  **Memory：** Memory（也称为Heap）存储引擎将所有数据保存在内存中，适用于对速度要求极高的临时表和缓存等场景。但是，由于数据存储在内存中，数据库重启后数据将丢失。
4.  **Archive：** Archive存储引擎适用于存储大量历史数据和归档数据。它以高压缩率存储数据，但不支持索引，只能进行顺序读取。
5.  **CSV：** CSV存储引擎将数据存储在逗号分隔值（CSV）格式的文件中，适合于导入和导出数据。
6.  **NDB Cluster：** NDB Cluster存储引擎是一个分布式存储引擎，用于构建高可用性和可伸缩性的集群。它具有高度的冗余和故障转移能力。

具体如何选择这存储引擎就需要根据自己的业务来选择，一般情况下我们都使用的是InnoDB

## MySQL的架构与内部模块 ##

根据上文的描述我们大致可以将MySQL的架构分为三层

![format_png 1][]

## InnoDB存储引擎的磁盘与内存结构 ##

上文中说到的流程仅仅是一个查询语句的流程，如果是更新呢？大体上的流程都是差不多的区别就在于拿到符合条件数据之后的操作这里我们就要首先说一下缓冲池（Buffer Pool）

### 缓冲池 Buffer Pool ###

首先，对于 InnoDB 存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到内存呢？比如我要读6个字节。 磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的I/O操作。所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。那么这样，我们干脆每次多读取一点，而不是用多少读多少。InnoDB 设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系 统也有页的概念。操作系统的页大小一般是4K，而在InnoDB 里面，这个最小的单位默认是16KB 大小。如果要修改这个值的大小，需要清空数据重新初始化服务。

读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。如下图所示

![format_png 2][]

既然有了这个缓存区那就会有数据同步的问题。修改数据的时候，也是先写入到 buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。那脏页什么时候才同步到磁盘呢？ InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

### Redo log ###

因为刷脏不是实时的，如果 Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。所以数据库必须有个持久化措施为了解决这个问题InnoDB 把所有对页面的修改操作专门写入一个日志文件。 如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现 crash-safe)。我们说的事务的ACID 里面D（持久性），就是用它来实现的。

![format_png 3][]

**注意点：**

1.  redo log是在磁盘中的不是在内存，所以这里很多人疑问为什么要多这一步，是因为Redo log在磁盘中我们是顺序往后写的，假设现在又多个数据需要写到数据库，因为这些数据在磁盘中的地址是乱序的所以要多次寻址然后写入，而log只需要一次寻址然后顺序写入即可，这样就提高了性能.
2.  redo log 不是记录数据页更新之后的状态，而是记录的是”在某个数据页上做了什么修改，输入物理日志
3.  redo log 的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发 buffer pool到磁盘的同步，以便腾出空间记录后面的修改。

### undo log ###

除了 redo log 之外，还有一个跟修改有关的日志，叫做 undo log.redo log 和 undo log 与事务密切相关，统称为事务日志。undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态，如果数据出现异常可以用undo log进行回滚

### 更新语句的具体更新过程 ###

1.  事务开始，从内存(buffer pool)或磁盘(data file)取到包含这条数据的数据页，返回给 Server 的执行器；
2.  Server 的执行器修改数据页的这一行数据的值为 penyuyan;
3.  记录 name=qingshan 到 undo log:
4.  记录 name=penyuyan 到 redo log；
5.  调用存储引擎接口，记录数据页到 Buffer Pool（修改 name=penyuyan)：
6.  事务提交

### InnoDB总体架构 ###

![format_png 4][]

#### 内存结构： ####

##### Buffer pool #####

Buffer Pool缓存的是页面信息，包括数据页、索引页。 内存的缓冲池写满了怎么办？（Redis 设置的内存满了怎么办？）InnoDB 用 LRU 算法来管理缓冲池（链表实现，不是传统的 LRU，分成了 young 和 old)，经过淘汰的 数据就是热点数据、

###### LRU ######

传统 LRU，可以用 Map+链表实现。value 存的是在链表中的地址。  
如果写 buffer pool的时候发现没有空闲页了,就要从 buffer pool 中淘汰数据页了， 它要根据 LRU 链表的数据来操作。

但是这里MySQL不是单纯的使用LRU算法就把尾部的指针对应的数据删除，而是在LRU的基础上加了热区和冷区靠近 head 的叫做 new sublist,用来放热数据(我们把它叫做热区）。靠近 tail 的叫做 old sublist，用来放冷数据(我们把它叫做冷区）。中间的分割线叫做 midpoint。也就是对 buffer pool 做 一个冷热分离。如下图所示:

![format_png 5][]

所有新数据加入到 buffer pool 的时候，一律先放到冷数据区的 head，不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在 old sublist(冷区）直接被淘汰。  
放到 LRU List 以后，如果再次被访问，都把它移动到热区的 head。 如果热区的数据长时间没有被访问，会被先移动到冷区的 head 部，最后慢慢在 tail 被淘汰。

**这样做解决的问题就是防止一次读取大量数据将很多热点数据都从内存删除掉了**（因为我们读数据不是说精准的读取某一条而是读取一页的数据，所以就有很多数据是预读出来的但是不用的，这样的设计最后就会把预读的数据慢慢剔除掉）

##### change Buffer写缓冲 #####

Change Buffer 是 Buffer Pool的一部分。 如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句 (Insert、 Delete、 Update）的执行速度。这一块区域就是 Change Buffer。5.5之前叫 Insert Buffer 插入缓冲，现在也能支 持 delete 和 update。最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge? 有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、redo log 写满时触发。 如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。

##### redo log buffer #####

Redo log 也不是每一次都直接写入磁盘，在 Burter Pool 里面有一块內存区城 (Log Buffer） 专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省磁盘 IO。

既然有Log Buffer那就又带来问题了什么时候持久化呢 也就是写入磁盘呢 log buffer 写入磁盘的时机，由一个参数控制，默认是1。

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0（延迟写）</td> 
   <td>log buffer 将每秒一次地与人 log file 中，开且 log file 的 flush 操作同时进行该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。</td> 
  </tr> 
  <tr> 
   <td>1（默认，实时写、实时刷）</td> 
   <td>每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且刷到磁盘中去。</td> 
  </tr> 
  <tr> 
   <td>2（实时写，延时刷）</td> 
   <td>每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file。但是 flush 操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush 操作。</td> 
  </tr> 
 </tbody> 
</table>

![format_png 6][]

#### 磁盘结构 ####

##### 系统表空间(system tablespace) #####

在默认情况下 InnoDB 存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。

InnoDB 系统表空间包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 Undo Logs) 如果没有指定 file-per-table，也包含用户创建的表和索引数据。

1.  上文说到过更改之前的值
2.  数据字典：由内部系统表组成，存储表和索引的元数据包
3.  双写缓冲（InnoDB 的一大特性）：

InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为16K，操作系统页 大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4次写。

![format_png 7][]

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4k，就宕机了，这种情况叫做部分写失效 (partial page write)能会导致数据丢失。 虽然说我们有redo log可以恢复但是问题是redolog里面这一页的数据是已经损坏的，所以用它恢复没有意义，所以在对于应用 redo log 之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log.这个页的副本就是 double write, InnoDB 的双写技术。通过它实现了数据页的可靠性。

跟 redo log一样，double write 由两部分组成，一部分是内存的 double write, 一个部分是磁盘上的 double write。因为 double write 是顺序写入的，不会带来很大的开销。

##### 独占表空间(system tablespace) #####

我们可以让每张表独占一个表空间。这个开关通过 innodb\_file\_per\_table 设置，默认开启。

开启后，则每张表会开辟—个表空间，这个文件就是数据目录下的 ibd 文件（例如 Nar/lib/mysql/gupao/user innodb.ibd)，存放表的索引和数据。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer）等还是存放在原来的共享表空间内。

**还有redo log、 undo log、临时表空间、通用表空间这些就不一一列举了**

## binlog ##

binlog 以事件的形式记录了所有的 DDL和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑志），可以用来做主从复制和数据恢复。

跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。 作重放一遍，来实现数据的恢复。

一条update语句的完整执行流程

![format_png 8][]

还有一个注意点是redo log是分两次写入的分别有两种状态

1.  prepare
2.  commit

只有事务提交了才会将redo log中的记录状态改成commit

## 崩溃恢复时如何判断事务是否需要提交 ##

1.  binlog无记录，redolog无记录：在redolog写之前crash，恢复操作：回滚事务
2.  binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务
3.  binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务
4.  binlog有记录，redolog状态commit：正常完成的事务，不需要恢复

[format_png]: https://img-blog.csdnimg.cn/img_convert/83dd5a83a9ea0a0235cf256537b0d357.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/7420272e8f3efc9daba7ea2ba5193f03.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/d2919a479562482c2e5db27c3fadec9b.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/544c2b258b27ae46100aae2ae86d59b9.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/84c1df537227ab25c118fbb6033c6010.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/ff763e420f6f9ba4dd75917dbc8377f2.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/7d629f6572c894c6843b134acebe14c9.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/1c7e1b68f66722f1d695b354c486c396.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/a6cc5f7cbbbbe1a85f4c0f472b0f4726.webp?x-oss-process=image/format,png