图解MySQL是如何运行的

admin 2022年3月22日01:52:58安全闲碎评论25 views4672字阅读15分34秒阅读模式

来自:数据和云

链接:https://www.modb.pro/db/210787?sjhy

一.MySQL的一条查询语句是怎么运行的
图解MySQL是如何运行的

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎

假如在MySQL中有一个查询会话请求,那么大概流程如下:

(1)MySQL客户端对MySQL Server的监听端口发起请求。

(2)在连接者组件层创建连接、分配线程,并验证用户名、密码和库表权限。

(3)如果打开了query_cache,则检查之,有数据直接返回,没有继续往下执行。

(4)SQL接口组件接收SQL语句,将SQL语句分解成数据结构,并将这个结构传递到后续步骤中(将SQL语句解析成MySQL认识的语法)。

(5)查询优化器组件生成查询路径树,并选举一条最优的查询路径。

(6)调用存储引擎接口,打开表,执行查询,检查存储引擎缓存中是否有对应的缓存记录,如果没有就继续往下执行。

(7)到磁盘物理文件中寻找数据。

(8)当查询到所需要的数据之后,先写入存储引擎缓存中,如果打开了query_cache,也会同时写进去。

(9)返回数据给客户端。

(10)关闭表。

(11)关闭线程。

(12)关闭连接。

图解MySQL是如何运行的

作用:

  • 连接层

(1)提供连接协议:TCP/IP 、SOCKET方式等连接验证。

(2)提供验证:用户、密码验证。

(3)提供专用连接线程:接收用户SQL,返回结果。

  • Server层

(1)接收上层传送的SQL语句。

(2)语法验证模块:验证语句语法,是否满足SQL_MODE。

(3)语义检查:判断SQL语句的类型:

DDL :数据定义语言

DCL :数据控制语言

DML :数据操作语言

DQL:数据查询语言

...

(4)权限检查:用户对库表有没有权限。

(5)解析器:对语句执行前,进行预处理,生成解析树(执行计划),说白了就是生成多种执行方案。

(6)优化器:根据解析器得出的多种执行计划,进行判断,选择最优的执行计划。

        代价模型:资源(CPU IO MEM)的耗损评估性能好坏。

(7)执行器:根据最优执行计划,执行SQL语句,产生执行结果。

(8)提供查询缓存(默认是没开启的),会使用redis tair替代查询缓存功能。

(9)提供日志记录(日志管理章节):binlog,默认是没开启的。

二.MySQL的一条更新语句是怎么运行的
图解MySQL是如何运行的

0、数据更新时执行器先找buffer pool缓存池中,如果在缓冲池中,同时返回给执行器。

1、如果未命中缓存,需要先从磁盘读入内存,然后再返回给执行器。

2、不管是否命中缓存,都需要将更新前的旧数据写入到undo中。

3、更新内存,此时变成脏数据,后续会调用接口将数据落盘。

4.5、同时将这个更新操作记录到redo log里面,此时redo log处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。

6.7、执行器生成这个操作的binlog,并把binlog写入磁盘。

8、执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成。

9.10.11、数据落盘。

图解MySQL是如何运行的

三.MySQL的数据是如何保证不丢的
图解MySQL是如何运行的

从上面的流程图可以看出,MySQL采用了wal机制。

只要redo log和binlog保证持久化到磁盘,就能确保MySQL异常重启后,数据可以恢复。

1.redo和binlog的落盘策略

redo和binlog的落盘还涉及一个操作系统缓存。

innodb_flush_log_at_trx_commit = 0/1/2

1: 表示每次事务提交时都将redo log直接持久化到磁盘。

0:表示每次事务提交时都只是把redo log留在redo log buffer中 ,然后每秒刷新redo buffer到OS cache,再fsync到磁盘,异常宕机时,会有可能导致丢失一秒内事务。

2:表示每次事务提交时都只是把redo log写到OS cache,再每秒fsync()磁盘。异常宕机时,会有可能丢失1秒内的事务。数据库宕机不丢失。

sync_binlog= 0/1/n

0:表示每次提交事务都只write,不fsync,每过一秒fsync到磁盘,每一秒刷一次磁盘。

1:表示每次事务提交都刷一次磁盘,也就是每次提交事务都会执行fsync。

n:(100 200 500)表示每次提交事务都write到OS cache,但累积 N 个事务后才 fsync 到磁盘。

innodb_flush_log_at_trx_commit=1sync_binlog=1

双1配置,数据库的安全性是最高的,不会丢事务。

其中redo和脏数据的落盘策略涉及如下参数:

innodb_flush_method
  • fsync的特性:

buffer pool的数据写磁盘的时候,需要先经历OS cache然后在写磁盘。

redo buffer的数据写磁盘的时候,需要先经历OS cache然后在写磁盘。

  • O_DSYNC:

buffer pool的数据写磁盘的时候,需要先经历OS cache然后在写磁盘。

redo buffer的数据写磁盘的时候,穿过OS cache直接写到磁盘。

  • O_DIRECT:

buffer pool的数据写磁盘的时候,跨过OS cache然后在写磁盘。

redo buffer的数据写磁盘的时候,需要先经历OS cache然后在写磁盘。

2.二阶段提交

步骤:

  • 更新操作记录到redo log里面,此时redo log处于prepare状态。

  • 告知执行器执行完成了,随时可以提交事务。执行器生成这个操作的binlog,并把binlog写入磁盘。

  • 执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)

图解MySQL是如何运行的

redo log和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。

在两阶段提交的不同时刻,MySQL异常重启会出现什么现象。

时刻 A ,也就是写入redo log处于prepare阶段之后、写binlog之前,发生了崩溃(crash),由于此时binlog还没写,redo log也还没提交,所以崩溃恢复的时候,这个事务会回滚。这时候,binlog还没写,所以也不会传到备库。

时刻 B,也就是binlog写完,redo log还没commit前发生crash,崩溃恢复的时候根据reod和binlog有一个共同的数据字段,叫XID。崩溃恢复的时候,会按顺序扫描redo log:如果碰到既有prepare、又有commit的redo log,就直接提交;如果碰到只有parepare、而没有commit 的redo log,就拿着XID去binlog 找对应的事务,如果找到有,则提交,没有则回滚。

3.组提交

图解MySQL是如何运行的

  • redo的组提交:

日志写到redo log buffer是很快的,wirte到page cache也差不多,但是持久化到磁盘的速度就慢多了。让更多的事务,同时能够进行fsync就是redo的组提交。

在并发更新场景下,第一个事务写完 redo log buffer 以后,接下来这个fsync越晚调用,组员可能越多,节约IOPS的效果就越好。

  • binlog的组提交:

在执行图中第 4 步把binlog fsync到磁盘时,如果有多个事务的binlog已经写完了,也是一起持久化的,这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快,所以 binlog 的 write 和 fsync 间的间隔时间短,导致能集合到一起持久化的binlog比较少,因此binlog的组提交的效果通常不如redo log的效果那么好。

如果你想提升binlog组提交的效果,可以通过设置如下两个参数来实现:

  • binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync

  • binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。

这两个条件是或的关系,也就是说只要有一个满足条件就会调用 fsync。所以,当 binlog_group_commit_sync_delay 设置为 0 的时候,binlog_group_commit_sync_no_delay_count 也无效了。

这两个参数目的是减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。

从日志先行和组提交得出结论,WAL机制主要得益于两个方面:

  • redo log和binlog都是顺序写,磁盘的顺序写比随机写速度要快;

  • 组提交机制,可以大幅度降低磁盘的IOPS消耗。

4.脏页落盘的时机

数据在内存被更新后,由于wal机制,redo和binlog会先落盘,而数据脏页也会在后续选择一定的时机落盘。

  • redo写满

redo log大小是固定的,写完后会循环覆盖写入。当有新的内容要写入时,系统必须停止所有的更新操作,将checkpoint向前推进到新的位置,但是在推进之前必须将覆盖部分的所有脏页都flush到磁盘上。

此时整个系统不能再更新了,TPS会降为0,所以这种情况要尽量避免。

  • 内存不足需要淘汰数据页

当系统内存不足,又有新的数据页要更新,就需要淘汰一些数据页,如果淘汰的是脏页,就需要flush到磁盘(如果是干净页就直接释放出来复用)。

  • 系统空闲的时候后台会定期flush适量的脏页到磁盘

  • MySQL正常关闭(shut down)时会把所有脏页都flush到磁盘

  • 脏页比例到达设定参数

innodb_max_dirty_pages_pct默认75%,LRU内的脏块如果超过75%,强制性的刷脏。

其中系统后台会有如下操作:

在loop主循环中又包含两种操作,分别是1S和10S的操作

  • 每1秒:

(1)日志缓冲刷新到磁盘,即使这个事务还没有提交。

(2)刷新脏页到磁盘。

(3)执行合并插入缓冲的操作。

(4)产生checkpoint。

(5)清除无用的table cache。

(6)如果当前没有用户活动,就可能切换到background loop。

  • 每10秒:

(1)日志缓冲刷新到磁盘,即使这个事务还没有提交。

(2)刷新脏页到磁盘。

(3)执行合并插入缓冲的操作。

(4)删除无用的undo页。

(5)产生checkpoint。

5.doublewrite的实现机制

另外从更新流程图里面也可以看出数据不是直接落盘的。

double write分为两部分:一部分是内存中的double write buffer ,大小为2MB(16k一个页,一共128个页),第二部分是磁盘共享表空间的128个数据页,在对脏页进行落盘的时候,并不是直接进行落盘,而是先复制到double write buffer,然后再分别写入到共享表空间,然后再写入表空间。

图解MySQL是如何运行的

为什么要有双写机制?

部分写的问题:

页面的刷新会遇到部分写的问题,也就是说对于只写了其中一个页面,只写了一部分的内容,在数据库崩溃后,传统的数据库会使用redo log进行恢复,恢复的原理是通过redo对数据也进行重新进行物理操作,但是如果这个数据页本身发生了损坏,那innodb的页面大小是16KB,但是写入过程中只写了4KB(操作系统仅仅保证512字节写入的完整性),这个是时候因为页面不是完整的,因此不能通过redo来进行恢复。redo恢复的前提条件是页是完整的。那么redo对其进行重做也是没有作用的,innodb的二次写,在写入的时候,创造了一个关于页的副本,这样即使在发生写失效后,也可以通过副本页,对还原重做。

--- EOF ---


推荐↓↓↓

原文始发于微信公众号(数据库开发):图解MySQL是如何运行的

特别标注: 本站(CN-SEC.COM)所有文章仅供技术研究,若将其信息做其他用途,由用户承担全部法律及连带责任,本站不承担任何法律及连带责任,请遵守中华人民共和国安全法.
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
admin
  • 本文由 发表于 2022年3月22日01:52:58
  • 转载请保留本文链接(CN-SEC中文网:感谢原作者辛苦付出):
                  图解MySQL是如何运行的 http://cn-sec.com/archives/835837.html

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: