2021-08-09 MySQL架构与SQL执行流程

查询语句执行流程？

查询语句执行流程.png

连接

客户端与服务端连接：

• 通信类型：同步/异步

• 连接方式：长链接/短连接

• 协议：TCP/Unix Socket

MySQL V5.7 默认连接数是151 最大是10万。最大连接时长是8小时，如果8小时连接没有活动自系统自动断开。

查询缓存

因为比较死板默认关闭，可通过命令开启。

词法解析和预处理

目的：保证SQL语句的正确性

词法、语法解析：对SQL语句格式进行校验。（语法解析阶段会生成解析树）

预处理：对SQL语句内容进行校验。

解析树.png

查询优化器

目的：优化SQL生成执行计划。

优化器：根据解析树生成不同的执行计划，然后基于开销选择最优的执行计划

可以通过关键词Explain 查询执行计划。

存储引擎——存储数据形式

存储引擎.png

分类：

InnoDB(默认) ——适用场景：读写并发、数据一致性场景

Memony——适用场景：查询速度快，不需要持久化的临时表场景。

MyIASM——适用场景：历史、压缩不需要索引的数据。

MySQL支持自定义的存储引擎，只需要用户根据MYSQL规范来实现相关接口即可，存储引擎可以切换的原因是：所有的存储引擎都是基于相同的规范来实现的。。

执行引擎

执行引擎利用存储引擎提供的API完成执行操作。

MYSQL体系结构总结

MYSQL体系结构.png

总体，我可以把MySQL分成三层，跟客户端对接的连接层、真正执行操作的服务层、和硬件打交道的存储引擎层。

InnoDB总体架构

InnoDB总体架构分为内存结构和磁盘结构，通过线性预读机制（系统默认，也叫顺序读）将磁盘数据缓存到内存中；操作系统默认Page = 4K，InnoDB默认Page = 16K；

InnoDB架构.png

内存结构

内存的作用是：

Buffer Pool

Buffer Pool缓存的是页面信息包括数据页和索引页，默认大小是128M

LRU算法

内存写满按照最近最少使用LRU策略来更新数据。

buffer pool LRU.png

change Buffer（写缓存）

作用：将修改记录在缓存池中，待缓存满了同一写入数据文件，从而提升更新语句的执行速度，默认占比： 25%

Adaptive Hash Index

用户无法自行创建Hash索引，hash索引是InnoDB 为热点数据(5/8buffer pool)自行创建的内存索引。

Redo Log Buffer

作用：用来保存即将写入redo.log日志的数据，默认16M。

注意：change Buffer缓存的是数据文件中的内容，Redo Log Buffer 缓存的是redo log日志文件中的内容。

磁盘结构

表空间可以看做是InnDB存储引擎逻辑结构的最高层，所有的数据都存在表空间中，表空间：分为5大类分别是：系统表空间、独占表空间（用于保存用户表的索引和数据）、通用表空间、临时表空间、undo表空间。

系统表空间

系统表空间是InnoDB存储引擎的一个共享表空间，包括：数据字典、双写缓冲区、Change Buffer 和 Undo Logs。

• 数据字典：存储内部系统表的表结构和索引的元数据（定义信息）

• 双写缓冲作用：防止写入过程中，page 被部分写失效，通过设置page的副本，保证page被破坏的情况下，通过副本恢复page中的数据（注意：副本保存的是原内容）

• undo log：也叫回滚日志，通过记录事务发生之前的数据状态，来保证回滚操作，通过随机I/O（磁盘地址不固定）来读取和写入数据（注意：因为系统表空间不会自动收缩，建立单独创建undo表空间）。

Redo.log

默认大小是48M，InnoDB独有，通过顺序I/O 记录InnoDB对数据的修改操作，

redo log.png
redo log为什么是两个.png

后台线程

作用：负责刷新内存池中的数据、把修改的数据页刷新到磁盘。

Bin.log

Bin log 是Server层 的日志文件，记录了DDL和DML语句（SQL语句）属于逻辑日志，被所有的存储引擎使用，作用：主从复制、数据恢复。

更新语句执行流程？

更新过程-简化.png 更新语句执行流程.png

崩溃恢复处理过程

崩溃恢复处理过程.png