MySQL 执行流程

MySQL 建立连接

服务端连接

通信类型

MySQL 在同一个连接中是同步执行的，一个sql发起之后，只有得到服务端响应之后才可以发起下一个sql语句。
想要使用异步，必须发起一个新的连接，但是大量连接会使服务端压力巨大。一般我们使用异步时都使用连接池，进行连接复用，避免资源浪费。

连接方式

MySQL 既支持长连接，也支持短连接。一般 JDBC 使用的是短连接，连接池使用的是长连接。
但是长连接会对服务端造成资源浪费，所以服务端会去检测一些长时间不使用的连接进行关闭。我们可以使用下面命令去查看服务端多久会将连接关闭，我们也可以去修改它的值。

SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; -- 非交互式
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout'; -- 非交互式

通讯协议

Unix Socket (在 linux 服务器上使用 mysql 不使用 -h 默认连接本机 mysql 时使用 Unix Socket 协议)
TCP/IP (在 使用mysql -h 时，或者使用程序连接时使用 TCP 协议)

通讯方式

1. 单工 （数据单向传输）
2. 半双工（同一时刻数据只能单项传输，不同时刻数据可以双向传输）
3. 全双工（同一时刻可以数据双向传输）
   
   而 MySQL 是半双工的通信方式，客户端与服务端可以在不同时刻互相发送数据。
   客户端发送数据到服务端时不能分块处理，所以当客户端发送数据超过 默认4M(可通过以下命令查看) 时，会报错。当然也可以在服务端通过修改默认值来增加或减小传输数据大小。

SHOW VARIABLES LIKE 'max_allowed_packet%';

服务端向客户端发送数据时，也同样不会分块处理，当数据库数据量比较大时，对于服务端内存，以及网络传输压力也是比较大的，所以我们在查询时需要避免 非limit 的查询。

MySQL 客户端与服务器的交互主要分为两个阶段，分别为握手认证阶段和命令执行阶段，详细来说一次正常的过程如下：

1. 三次握手建立 TCP 连接。

2. 建立 MySQL 连接，也就是认证阶段。
   服务端 -> 客户端：发送握手初始化包 (Handshake Initialization Packet)。
   客户端 -> 服务端：发送验证包 (Client Authentication Packet)。
   服务端 -> 客户端：认证结果消息。

3. 认证通过之后，客户端开始与服务端之间交互，也就是命令执行阶段。
   客户端 -> 服务端：发送命令包 (Command Packet)。
   服务端 -> 客户端：发送回应包 (OK Packet, or Error Packet, or Result Set Packet)。

4. 断开 MySQL 连接。
   客户端 -> 服务器：发送退出命令包。

5. 四次挥手断开 TCP 连接。

InnoDB 体系结构

下面是一张 InnoDB 的架构图，是从 MySQL 的的管网上拿出来的。

内存区域 In-Memory Structures

buffer pool

buffer pool 在 InnoDB 中是用于提升读写效率的。

buffer pool 中存储的是 数据页和索引页，默认大小为 128 M，它在内存中使用了 LRU 算法，将长期不使用的数据进行剔除，最终留下了热点数据。

当用户进行数据查询时，将数据放入到 buffer pool 中，然后当用户再次查询，发现查询的页在 buffer pool 中时，就可以直接从 buffer pool 中获取。

当用户做修改操作时也是先修改 buffer pool 中的数据，然后此时 buffer pool 中的数据和磁盘中的数据就会存在不一致，就是所谓的脏页，mysql 的后台线程会定时的将脏页数据刷到磁盘中。

Change Buffer

如果我们更新的数据没有唯一性确认，不需要去磁盘进行唯一性校验，那么就可以直接放入到 Change Buffer 中，最终让后台线程进行刷脏，将修改后的数据刷入磁盘中去。这个刷脏的过程叫做 merge，触发 merge 的方式有以下几种：

• 访问数据页。
• 后台线程定时 merge。
• MySQL shut down 时。
• Redo Log 写满时。

Log Buffer

从上面的结构来看，Change Buffer 和磁盘之间会存在一定的数据差异，以及数据延迟，那么此时如果数据库挂了，那是就会造成数据不一致的情况，所以 InnoDB 专门对这种对于页更新的操作用 Redo Log 来记录更新的操作日志，而Log Buffer 来专门作为写入日志的缓冲池，默认 16M。

Log Buffer 刷盘也有三种方式：

• 0 buffer pool 将更新数据存入 Log Buffer，每秒会将 Log Buffer 中已经commit 的数据刷入到 Redo Log 中。
• 1 buffer pool 将更新数据存入 Log Buffer，commit 会将数据刷入到 Redo Log 中，也是默认的方式。
• 2 buffer pool 将更新数据存入 Log Buffer，commit 会将数据刷入到 操作系统的缓冲池 os cache / buffer 中，每秒将 操作系统缓冲池中的数据刷入到 磁盘中。
  

Adaptive Hash Index

buffer pool 中也会存部分索引的缓存。

磁盘区域 On-Disk Structures

Redo Log

当 MySQL 重启时也会读 Redo Log 来尝试恢复数据，做异常恢复。
在 MySQL 中对于这种先写日志，后把内存数据同步到磁盘的操作叫做 WAL(Write Ahead Loggin) 。
其实有很多人有疑问，写日志也是 I/0 操作，而更新磁盘数据也是 I/O 操作，为什么要先写日志呢？这不是多了一步么？

其实我们在更新磁盘数据时发生的是随机 I/O，读取磁盘空间时，需要磁头转动到存储数据这一块时才能进行读写，效率低下。而在写日志时，发生的则是顺序 I/O ，值需要找到需要写数据的位置，一直往后写即可，不需要重新寻址。

Redo Log 主要是作为数据恢复时使用，将数据写入到内存中，最终由内存再刷脏到磁盘中。

Redo Log 是 InnoDB 专有的物理日志，它存储的不是数据页，而是记录对数据页做了什么样的变更，默认大小为 48M。

System Tablespace（系统表空间）

System Tablespace 是一个共享表空间，表以及系统数据都会存在这个空间。

• InnoDB Data Dictionary 数据字典，存放表的元数据。
• Doublewrite Buffer 双写缓冲。
• Changer Buffer
• Uodo Logs

File-Per-Table Tablespace（独占表空间）

File-Per-Table Tablespace 是独占的表空间 对于 InnoDB 中存储的每一张表都有一个独立的空间，存放其数据及索引。

General Tablespace（通用表空间）

我们可以建立通用的表空间，并且在创建表时需要指定这个表空间，而且这个表空间删除时，需要将表空间中的所有表都删除才可以继续删除表空间。

Undo Tablespace

Undo Tablespace 用于存放 undo log，它可以放在 System Tablespace 中，也可以建立独立的表空间，undo log 用于数据回滚。

Temporary Tablespace（临时表空间）

Temporary Tablespace 存放临时数据，包括查询时创建的临时表。

MySQL 查询sql如何执行

我们在日常开发中经常使用sql去查询数据库数据，但是一条sql是如何去在数据库中执行查询的呢？下面就来聊聊sql在服务端的执行过程。

服务端 SQL 执行过程

缓存

MySQL 的缓存是默认关闭的,可以使用下面语句进行查询。

SHOW VARIABLES LIKE 'query_cache%';

MySQL不推荐使用自己默认的缓存，在8.0以后，缓存模块已被删除。

1. 因为缓存是一个 key-value 的一个模式，所以导致查询 sql 必须完全一模一样才可以使用到 MySql 的缓存，在关键字后加入空格都不能使用缓存。
2. 一张表中的一条数据发生变换，整张表所有缓存的数据都会失效。

解析器

1. 词法解析
   词法解析，将一条sql 拆解成一个个词，并按照关键字，非关键字进行分类。
2. 语法解析
   语法解析，将词法解析后后一个个词，组装成一个解析树，并且检查是否组成一条完整的sql语句。
   

预处理器

语义解析
语义解析主要解析的内容是 Tables 中的表是否存在，Fields 中的 列字段是否存在 以及 Conditions 中条件字段是否存在。

查询优化器

SQL优化通常包括两项工作:一是逻辑优化、二是物理优化。

这两项工作都要对语法分析树的形态进行修改，把语法分析树变为查询树。其中，逻辑查询优化将生成逻辑查询执行计划。在生成逻辑查询执行计划过程中，根据关系代数的原理，把语法分析树变为关系代数语法树的样式，原先SQL语义中的一些谓词变化为逻辑代数的操作符等样式，这些样式是一个临时的中间状态，经过进一步的逻辑查询优化，如执行常量传递、选择下推等(如一些节点下移，一些节点上移)，从而生成逻辑查询执行计划。

在生成逻辑查询计划后，查询优化器会进一步对查询树进行物理查询优化。物理优化会对逻辑查询进行改造，改造的内容主要是对连接的顺序进行调整。SQL语句确定的连接顺序经过多表连接算法的处理，可能导致表之间的连接顺序发生变化，所以树的形态有可能调整。

物理查询优化除了进行表的连接顺序调整外，还会使用代价估算模型对单个表的扫描方式、两表连接的连接算法进行评估，选择每一项操作中代价最小的操作为下一步优化的基础。

物理查询优化的最终结果是生成最终物理查询执行计划。
更详细的介绍可以看下面这篇文章。
https://blog.csdn.net/weixin_33878457/article/details/89544606

执行计划

我们可以通过 explain 进行查询，可以看到执行计划的id，索引，走了哪个索引，扫描行数等参数，具体参数可以看官网介绍。（传送门）

执行引擎

执行引擎会根据 执行成本选择一条较优（不一定是最优）的执行计划进行执行。执行成本包括以下两种：

• IO成本：与读取的页数有关，用于从磁盘读取表的数据到内存，成本计算为 页数*1。（读取时会读取数据所在的页，即表现了程序的 空间局部性）
• CPU成本：与表行数有关，用于条件的比较或排序，成本计算为 行数*0.2

存储引擎

MySQL 更新语句如何执行

• 从内存 buffer pool 或者磁盘读取到数据。
• 在 buffer pool 中将需要修改的值进行修改。
• 写入 undo log 日志，用于事务回滚。
• 写入 redo log 日志，用于数据恢复，一旦数据库挂了，可以用于快速恢复。
• 提交事务时将 buffer pool 中的数据刷入到磁盘中。