MySQL执行过程及相关组件浅析

目录

基本结构视图

MySQL的内部组件结构:

执行过程图解:

组件浅析：

Server 层和存储引擎层

连接器:

长连接积累的问题？

查询缓存:

词法分析器:

词法分析器原理

Bison语法解析形成语法数

优化器

执行器

bin-log归档(Servier层):

存储引擎（InnoDB）层:

mysql执行过程中相关组件介绍及流程图解说明如下，部分基础知识一笔带过。

基本结构视图

MySQL的内部组件结构:

执行过程图解:

组件浅析：

Server 层和存储引擎层

• Server层主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数 （如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在 最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

连接器:

关闭非交互连接之前等待活动的秒数(默认8小时)

Show global variables like ‘wait_timeout’;

查看当前sql server连接状态:

show processlist;

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接则是指每次执行完很少的几次 查询就断开连接，下次查询再重新建立一个。

长连接积累的问题？

大多数时候用的都是长连接,把连接放在Pool内进行管理，但是长连接有些时候会导致 MySQL 占用内存涨得特别快，因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如 果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。 怎么解决这类问题？

1、定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资 源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存:

#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存

show global variables like "%query_cache_type%";

show status like'%Qcache%'; //查看运行的缓存信息

• Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越 理想。
• Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行 查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理 想。当然系统刚启动后，查询缓存是空的，这很正常。

关键字: SQL_CACHE

select SQL_CACHE * from scm_material where `name` = '罗浮山风湿膏药';

以sql语句为key,结果集为value.缓存;每次该条数据更新之后会清空该条数据缓存.再次查询不会命中缓存,会重新插入缓存, Qcache_inserts值越高越不理想.

mysql8.0已经移除了查询缓存功能

词法分析器:

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句 是否满足 MySQL 语法。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 from 写成了 "form"。

词法分析器原理

SQL语句的分析分为词法分析与语法分析，语法分析由Bison生 成。

开源的词法结构分析工具插件例如Antlr v4，ANTLR从语法生成一个解析器.

词法分析器分成6个主要步骤完成对sql语句的分析

1. 词法分析 2、语法分析 3、语义分析 4、构造执行树 5、生成执行计划 6、计划的执行

Bison语法解析形成语法数

当我们执行查询的时候，MySQL会自动生成执行计划query  plan.

优化器

MySQL使用基于成本的优化器，它尝试预测一个查询使用某一种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

示例中的结果表示优化器认为大概需要做10个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但是MySQL则选择它认为成本小的，但成本小并不意味着执行时间短）。

这里last_query_cost的值是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。

执行器

开始执行前, 执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

执行器会调用对应的存储引擎执行 sql。主流的是MyISAM 和 Innodb。

bin-log归档(Servier层):

因为SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中.

binlog是Server层实现的二进制日志,他会记录我们更新的操作。Binlog有以下几个特点：

1、Binlog在MySQL的Server层实现（引擎共用）.

2、Binlog为逻辑日志,记录的是一条语句的原始逻辑.

3、Binlog不限大小,追加写入,不会覆盖以前的日志.

如果误删了数据库,可以使用binlog进行归档.

存储引擎（InnoDB）层:

1.  undo log 与 MVCC

undo log是 Innodb 引擎专属的日志，是记录每行数据事务执行前的数据。主要作用是用于实现MVCC版本控制，保证事务隔离级别的读已提交和读未提交级别。

redo log:

InnoDB 内部维护了一个缓冲池，用于减少对磁盘数据的直接IO操作，并配合 redo log、内部的 change buffer 来实现异步的落盘，保证程序的高效执行。redo log 大小固定，采用”循环写”.

什么是循环写呢？事务提交时，会先写到redo log里面去，当写完一个事务时，writepos会往前移动(表示当前正在记录的位置)，当redo log中的记录被更新到数据库中时，checkpoint 会往前(顺时针)移动(表示数据落盘的边界-日志序列号即LSN)。

也就是checkpoint与 writepos中间是已记录的, 当writepos写完 id_logfile_3后，会回到id_logfile_0循环写，而追上checkpoint后则需要先等数据进行落盘，等待 checkponit向前面移动一段距离再写.

为什么不直接写到Mysql中去？这是因为数据写到Mysql中去，需要找到磁盘的Mysql的对应的页，涉及磁盘的随机I/O访问(寻道时间、旋转时间、传输时间。)，是非常消耗时间的一个过程，相比这个时间，先写入redo log记录修改操作，防止断电丢失写操作，降低随机写消耗（转成顺序写）, 后面再找合适的时机刷盘;

。

2. 缓冲池 Buffer Pool:

Buffer Pool 的执行原理,在使用索引时就加载对应的数据页(磁盘管理的最小单位，每个页默认16KB)到缓冲池中操作，如果没有用到索引会进行全表扫描，将所有数据都加载到缓冲池中查找、操作，如果数据量大会分批依次传入Buffer Pool进行查询。也就是说磁盘上的数据都是通过缓冲池来筛选读取的,通过其可以避免对磁盘频繁的IO操作,存储算法是改进版的 LRU 算法。

 Change Buffer这个是用InnoDB 的 "写"缓存，而外面的是 InnoDB 的"读"缓存。

预读缓存(BufferPool):

如果多次sql语句操作的是相邻的记录，那么就会多次进行磁盘读取，导致速度降低.

InnoDB 引擎在预读时， 有两种预读算法。线性预读和随机预读。

线性预读（innodb_read_ahead_threshold）

    选择是否预读下一个Extent(区)的数据。有一个重要的参数 innodb_read_ahead_threshold，如果当前 Extent 中连续读取的数据页超过规定值，就会将下一个 Extent 的数据也读到缓冲池中。innodb_read_ahead_threshold 的范围是 0-64（因为一个 Extent 也就64页）。

随机预读（innodb_random_read_ahead）

用来设置是否将当前 Extent 的剩余页也预读到缓冲池中，由于这种预读性能不稳定，所以MySQL 5.5开始默认关闭。

数据存储单位:

所有数据都被逻辑地存放在一个空间内，称为表空间，而表空间由段（sengment）、区（extent）、数据页（page）组成。

传统LRU问题:

预读失效

无用数据页把热点数据页移动至尾部,甚至移除缓存.

解决:

当从磁盘的数据页移入缓冲池中时，首先是放入老年代的头部，然后进行筛选，使用到的数据页会移入新生代的头部，未使用的数据页会随着时间流逝而慢慢移入老年代的尾部，直至淘汰。

缓冲池污染

在执行时虽然会先加载到老年代的头部，但是如果加载大量的数据 ,且每条数据都需要筛选, 所以都会移入新生代头部, 导致新生代热点数据页被挤到老年代甚至移除。

解决:

使用了 "老年代停留时间窗口" 机制，是设置一个时间，如果在老年代的数据页被调用后还需要去检查它在老年代的停留时间是否达到了这个规定时间，达到了才能移入新生代头部，否则只会移到老年代头部。

写缓存(Change Buffer):

因为直接对磁盘进行IO操作会比较耗时，并发时某段时间写操作非常多，那么如果直接更新到磁盘上数据库的压力就会非常大。为了避免这种情况，可以错峰，让数据在系统空闲时再更新到磁盘，那么该如何实现，Change Buffer就起到这样的作用。

Merge：将 change buffer 中保存的 SQL 更新到缓冲池，并将 change buffer 的变更记入 redo log。

Flush：刷脏页，也就是将未应用到磁盘的修改更新到磁盘。在 flush 时会阻塞其他的操作,类似STW。其本质是通过缓冲池中的脏页替换掉磁盘上对应的数据页。

脏页：内存中与磁盘数据不同的数据页称为 "脏页"。

举例:

在写操作语句进来时，首先会判断缓冲池是否存在这条写操作对应的数据页，如果存在直接更新数据页中对应的数据，然后将对数据页的操作逻辑记入 redo log；如果不存在，那么会将对数据页的修改逻辑写入 Change Buffer 以及 redo log，等到下次读取该数据到缓冲池中会触发Merge,将Change Buffer 中对应的写操作更新至该数据页。（如果是插入操作且包含唯一索引那么当缓冲池中不存在对应数据页时直接将数据页读取到缓冲池然后判断唯一性，然后再把对数据页的修改逻辑写入redo log）

Change Buffer 是为了将写操作延迟更新到缓冲池，降低随机读的消耗（不需要频繁从磁盘读数据页）。

Buffer 参数调优:

Buffer Pool :

1、innodb_buffer_pool_size：缓冲池大小，在内存足够的条件下，越大越好。

2、innodb_old_blocks_pct：老年代占整个LRU链长度的比例，默认是37，即整个LRU的新生代和老年代长度比例是63：37。（如果配置是100就变成普通的LRU了）

3、innodb_old_blocks_time：老年代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老年代停留时间超过1秒”两个条件，才会被插入到新生代头部。

4、show engine innodb status 结果 " Buffer pool hit rate " 显示内存命中率。

Change Buffer：

1、innodb_change_buffer_max_size：

　　配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

注：写多读少的业务，才需要调大这个值;

crash-safe

简述,以redo log的存在使得数据库具有crash-safe能力，即如果Mysql 进程异常重启了，系统会自动去检查redo log定位到上次checkpoint的位置，同时，每个数据页中也存在一个LSN，当redo log中的LSN大于数据页中的LSN时，说明重启前redo log中的数据未完全写入数据页中，那么将从数据页中记录的LSN开始，从redo log中恢复数据。

 redo log flush (落盘)

1、mysql系统后台会定期落盘

2、mysql 正常关闭时

3、redo log 满了时(redo log 是固定大小的，采用循环写)

4、缓冲池在读取数据页进来时内存不足需要淘汰部分数据页，而淘汰的数据页如果是脏页也会导致落盘。

 flush 会阻塞其他操作，所以如果频繁地进行 flush 就会对 SQL 执行造成影响，有时候在执行简单的 SQL 时却产生了卡顿，这就是因为正好触发了 flush。所以需要去控制 Flush 执行来减少影响。

binlog 与 redo log

WAL(Write Ahead Log)技术，也称为日志先行的技术，指的是对数据文件进行修改前，必须将修改先记录日志。

binlog是Mysql server层的日志，主要用于数据误删后进行数据恢复，另外，主从复制也需要依靠bin log。

redolog 是Mysql InnoDB引擎层的日志，为了保证两份日志最终恢复到数据库的数据是一致的，采用两阶段提交的机制。

举例:

在更新到数据页缓存或者 Change Buffer 后，首先进行 redo log 的编写，编写完成后将 redo log 设为 prepare 状态，随后再进行 binlog 的编写，等到 binlog 也编写完成后再将 redo log 设置为 commit 状态。这是为了防止数据库宕机导致 binlog 没有将修改记录写入，后面数据恢复、主从复制时数据不一致。

机器在重启后,会首先去验证redolog的完整性，如果redolog中没有prepare状态的记录，则记录是完整的，就提交。如果redolog中存在prepare记录，那么就根据XID(事务ID)去验证这条redolog对应的binlog记录，如果这条binlog是完整的，那么完整提交redolog，否则执行回滚逻辑

扩展：

索引下推

索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询.

• 当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行扫描，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。
• 使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件向下传递给存储引擎，然后存储引擎通过判断索引条件是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据出来返回给MySQL服务器.

ICP 就是把以上索引扫描和索引过滤合并在一起处理，过滤后的记录数据下推到存储引擎后的一种索引优化策略。这样做的优点如下：

1.减少了回表的操作次数。

2.减少了上传到 MySQL SERVER 层的数据。

适用情况:

1:需要整表扫描的情况。type比如：range, ref, eq_ref, ref_or_null .

2:对于InnDB引擎只适用于二级索引.

3:引用子查询的条件不能下推.

4:调用存储过程的条件不能下推.

简析:二级索引树

B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。

如果 name相同的时候再比较 phone。

失效原因：

但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

---

---

扩展了解知识：

索引下推

正是因为有了联合索引，才会有索引下推的优化，避免多次扫描磁盘读取数据。

分析：where name like ‘L%’ and phone = 17;

一般先根据name=xx 找到数据，叶子节点找到主键id,然后查询到主键索引叶子节点的数据，再来筛选phone=17的结果。

举例解释说明:

使用索引下推的情况下，存储引擎层还是先根据 name like ‘L%’ 这个条件，查找到 LiLei、Lili、Lisa、Lucy 四条索引数据，不过接下来不是直接进行回表操作，而是根据 phone = 17 这个条件，对四条索引数据进行判断筛选，将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。（也就是我们把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断了。这个下推的前提是索引中有 age 列信息，如果是其它条件，如 gender = 0，这个即使下推下来也没用）

索引下推原文地址:

https://blog.csdn.net/weixin_44734394/article/details/117320239