一条MySQL语句是如何执行的？(浅谈MySQL架构与语句执行流程)

叁歲伎倆 2022-12-14 13:50 203阅读 0赞

**目录**

一条查询寻语句是如何执行的？

1.建立链接

1.1 通讯协议

1.2 通讯方式（半双工）

2.查询缓存（数据库默认关闭，并在8.0版本之后彻底抛弃）

3.语法解析、预处理

3.1 语法解析

3.2 预处理

4.查询优化

4.1 什么是优化器（执行的SQL--->优化器--->多个执行计划--->根据cost(开销)最少的执行）

5.存储引擎

6.执行引擎

一条更新语句是如何执行的？

1.缓冲池(Buffer Pool)

buffer Pool

Change Buffer

Log Buffer

--------------------

# 一条查询寻语句是如何执行的？ #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70][]

## 1.建立链接 ##

### 1.1 通讯协议 ###

MySQL是支持多种通信协议的:

同步/异步的方式(一般是同步的)

*  同步：阻塞，依赖被调用方，也就是数据库在执行完SQL之前，线程是阻塞的。
 *  异步：虽然能解决线程阻塞，但是SQL执行时间还是一定的，每一个SQL执行都需要创建连接，但是无形中增加了服务器压力，CPU资源被占用过多。

支持长连接/短连接。

*  短连接，操作完成之后，立马close掉
 *  长连接，在操作完成后，连接仍然保持打开状态，后面的程序访问的时候我们仍然可以使用这个。连接池中一般使用的是长连接。 MySQL在使用长连接的时候，如果长时间不操作当前连接，会断开，默认是 8 小时。、
 *  Unix Socket
 *  TCP/IP 协议

### 1.2 通讯方式（半双工） ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 1][]

单工：

在两台计算机通信的时候，数据的传输是单向的。生活中的类比：遥控器。

半双工：

在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，

但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你

要给我发的话，也必须等我发给你完了之后才能给我发。生活中的类比：对讲 机。

全双工：

数据的传输是双向的，并且可以同时传输。生活中的类比：打电话

## 2.查询缓存（数据库默认关闭，并在8.0版本之后彻底抛弃） ##

简单来说，就是数据库会将查询的数据按照KV的形式存储在内存中，当相同SQL重复查询的时候，就会首先从缓存中拿取相同的数据。但是为什么要摒弃这个功能呢，

首先，如果有大量的查询存储内存中，这样足够多的SQL查询会大大的增加内存压力。

其次，就是查询的K，也就是SQL必须相同，也就是一个空格都不能差，否则就查询不出结果。

而且，如果某表的数据发生变化，那就必须全部删除该表的查询缓存，这样对于一个变更频繁的表来说，是非常不友好的。

## 3.语法解析、预处理 ##

### 3.1 语法解析 ###

这个就是MySQL的Parser解析器和Preprocessor预处理模块。

这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。

第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合，

然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我

们把它叫做解析树（select\_lex）。

### 3.2 预处理 ###

比如我们某个查询语句如果没有错误，但是这个表名压根就不存在，那么这个查询报错是在SQL执行的时候，还是预处理的时候？

答案是预处理的时候，预处理器会在对解析完成的解析树进行再次解析，然后对解析器不能解析的语义记性解析，比如表名，列明，然后生成一个新的解析树。

## 4.查询优化 ##

### 4.1 什么是优化器（执行的SQL--->优化器--->多个执行计划--->根据cost(开销)最少的执行） ###

得到解析树之后，是不是执行SQL语句了呢？

这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终

执行的SQL是不是就是我们发送的SQL？

这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结

果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选

择哪一种去执行？根据什么判断标准去选择？

这个就是MySQL的查询优化器的模块（Optimizer）。查询优化器的目的就是根据解

析树生 成不同的执行计划（ExecutionPlan），然后选择一种最优的执行计划，MySQL

里面使用 的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种

## 5.存储引擎 ##

MyISAM 和InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前，

默认的存储引擎是MyISAM，它是MySQL自带的。我们创建表的时候不指定存储引擎，

它就会使用MyISAM作为存储引擎。

MyISAM的前身是ISAM（IndexedSequentialAccessMethod：利用索引，顺序

存取数据的方法）。

5.5版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。

为什么要改呢？最主要的原因还是InnoDB 支持事务，支持行级别的锁，对于业务一致

性要求高的场景来说更适合。

## 6.执行引擎 ##

他是在调用存储引擎的API实现SQL执行，不同存储引擎实现相同的API。

# 一条更新语句是如何执行的？ #

数据库里面，Update语句包括Insert，update，delete相关。

Mybatis源码中，策略执行器，只有doQuery(),doUpdate()方法。

其实，执行更新之前，首先会根据更新语句拿到符合条件的数据。那么拿到数据之后呢？

# 1.缓冲池(Buffer Pool) #

首先，InnnoDB的数据都是放在磁盘上的，InnoDB操作数据有一个最小的逻辑单

位，叫做（Page）页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作

磁盘，因为磁盘的速度太慢了。 InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的

页放到一块内存区域里面。这个内存区域就叫Buffer Pool。

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再

次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，

我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，

每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

BufferPool是InnoDB里面非常重要的一个结构，它的内部又分成几块区域。这里

我们趁机到官网来认识一下InnoDB的内存结构和磁盘结构。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 2][]

BuffPool包含三部分 : Buffer Pool、Change Buffer、Adaptive Hash

Index，另外还有一个（redo）log buffer

### buffer Pool ###

Buffer Pool缓存的是页面信息，包括数据页、索引页。

内存的缓冲池写满了怎么办？（Redis 设置的内存满了怎么办？）InnoDB 用 LRU

算法来管理缓冲池（链表实现，不是传统的LRU，分成了young和old），经过淘汰的

数据就是热点数据。

### Change Buffer ###

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索

引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲

池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

这一块区域就是 Change Buffer。5.5 之前叫Insert Buffer 插入缓冲，现在也能支

持delete和update。

最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge？

有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、

redo log写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立

刻读取，就可以使用Change Buffer（写缓冲）。

### Log Buffer ###

思考一个问题：如果BufferPool里面的脏页还没有刷入磁盘时，数据库宕机或者重

启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。

为了避免这个问题， InnoDB把所有对页面的修改操作专门写入一个日志文件，并且

在数据库启动时从这个文件进行恢复操作（实现crash-safe）——用它来实现事务的持

久性。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 3][]

如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需

要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块

数据，一次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么

就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。

刷盘是随机I/O，而记录日志是顺序I/O，顺序I/O效率更高。因此先把修改写入日

志，可以延迟刷盘时机，进而提升系统吞吐。

需要注意： redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer

pool。redo log写入磁盘，不是写入数据文件。

redo log有什么特点？

1、redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。

2、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。

3、redo log的大小是固定的，前面的内容会被覆盖。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70]: /images/20221123/b493f892555d46f7bd6046a5b8a84b7d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 1]: /images/20221123/f172cf27d6984de0a5258fee1c943041.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 2]: /images/20221123/d67269f8b9a74a00bf5869ee747fc8dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzNDkwNDQ3NDA2_size_16_color_FFFFFF_t_70 3]: /images/20221123/4c56c155b1f94a92b682c5db822c62aa.png