用几张图来了解MySQL是怎么运行的

进击的CJR 2023-03-28 原文

导读，本文通过图解，主要说明如下三个问题

（1）MySQL的一条查询语句是怎么运行的

（2）MySQL的一条更新语句是怎么运行的

（3）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）关闭连接。

查询流程图

作用

连接层
（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的一条更新语句是怎么运行的

更新的执行流程

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采用了wal机制

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

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=1
sync_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

二阶段提交

提交的步骤

1、更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。

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

3、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）

提交的流程图

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 找对应的事务，如果找到有，则提交，没有则回滚。

组提交流程图

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 消耗。

脏页落盘的时机

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

（1）redo写满

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

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

（2）内存不足需要淘汰数据页

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

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

（4）MySQL正常关闭（shut down）时会把所有脏页都flush到磁盘

（5）脏页比例到达设定参数

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

doublewrite的实现机制

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

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

为什么要有双写机制？

部分写的问题：

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

MySQL 用几 section br binlog mysql 体系结构 innodb 内核MySQL 数据库yyds干货盘点

有关用几张图来了解MySQL是怎么运行的的更多相关文章

ruby - 如何从 ruby 中的字符串运行任意对象方法？ - 2
总的来说，我对ruby还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用
ruby - 如何每月在 Heroku 运行一次 Scheduler 插件？ - 2
在选择我想要运行操作的频率时，唯一的选项是“每天”、“每小时”和“每10分钟”。谢谢!我想为我的Rails3.1应用程序运行调度程序。最佳答案这不是一个优雅的解决方案，但您可以安排它每天运行，并在实际开始工作之前检查日期是否为当月的第一天。关于ruby-如何每月在Heroku运行一次Scheduler插件？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/8692687/
ruby-on-rails - 如何在 ruby 中使用两个参数异步运行 exe？ - 2
exe应该在我打开页面时运行。异步进程需要运行。有什么方法可以在ruby中使用两个参数异步运行exe吗？我已经尝试过ruby命令-system()、exec()但它正在等待过程完成。我需要用参数启动exe，无需等待进程完成是否有任何rubygems会支持我的问题？最佳答案您可以使用Process.spawn和Process.wait2:pid=Process.spawn'your.exe','--option'#Later...pid,status=Process.wait2pid您的程序将作为解释器的子进程执行。除
ruby - 无法运行 Rails 2.x 应用程序 - 2
我尝试运行2.x应用程序。我使用rvm并为此应用程序设置其他版本的ruby:$rvmuseree-1.8.7-head我尝试运行服务器，然后出现很多错误:$script/serverNOTE:Gem.source_indexisdeprecated,useSpecification.Itwillberemovedonorafter2011-11-01.Gem.source_indexcalledfrom/Users/serg/rails_projects_terminal/work_proj/spohelp/config/../vendor/rails/railties/lib/r
ruby - Sinatra:运行 rspec 测试时记录噪音 - 2
Sinatra新手；我正在运行一些rspec测试，但在日志中收到了一堆不需要的噪音。如何消除日志中过多的噪音？我仔细检查了环境是否设置为:test，这意味着记录器级别应设置为WARN而不是DEBUG。spec_helper:require"./app"require"sinatra"require"rspec"require"rack/test"require"database_cleaner"require"factory_girl"set:environment,:testFactoryGirl.definition_file_paths=%w{./factories./test/
ruby-on-rails - 无法让 rspec、spork 和调试器正常运行 - 2
GivenIamadumbprogrammerandIamusingrspecandIamusingsporkandIwanttodebug...mmm...let'ssaaay,aspecforPhone.那么，我应该把“require'ruby-debug'”行放在哪里，以便在phone_spec.rb的特定点停止处理？(我所要求的只是一个大而粗的箭头，即使是一个有挑战性的程序员也能看到:-3)我已经尝试了很多位置，除非我没有正确测试它们，否则会发生一些奇怪的事情:在spec_helper.rb中的以下位置:require'rubygems'require'spork'
ruby - Ruby 中的隐式返回值是怎么回事？ - 2
所以我开始关注ruby，很多东西看起来不错，但我对隐式return语句很反感。我理解默认情况下让所有内容返回self或nil但不是语句的最后一个值。对我来说，它看起来非常脆弱(尤其是)如果你正在使用一个不打算返回某些东西的方法(尤其是一个改变状态/破坏性方法的函数!)，其他人可能最终依赖于一个返回对方法的目的并不重要，并且有很大的改变机会。隐式返回有什么意义？有没有办法让事情变得更简单？总是有返回以防止隐含返回被认为是好的做法吗？我是不是太担心这个了？附言当人们想要从方法中返回特定的东西时，他们是否经常使用隐式返回，这不是让你组中的其他人更容易破坏彼此的代码吗？当然，记录一切并给出
ruby - 怎么来的(a_method || :other) returns :other only when assigning to a var called a_method? - 2
给定以下方法:defsome_method:valueend以下语句按我的预期工作:some_method||:other#=>:valuex=some_method||:other#=>:value但是下面语句的行为让我感到困惑:some_method=some_method||:other#=>:other它按预期创建了一个名为some_method的局部变量，随后对some_method的调用返回该局部变量的值。但为什么它分配:other而不是:value呢？我知道这可能不是一件明智的事情，并且可以看出它可能有多么模棱两可，但我认为应该在考虑作业之前评估作业的右侧...我已经在R
ruby-on-rails - before_filter 运行多个方法 - 2
是否有可能:before_filter:authenticate_user!||:authenticate_admin! 最佳答案 before_filter:do_authenticationdefdo_authenticationauthenticate_user!||authenticate_admin!end 关于ruby-on-rails-before_filter运行多个方法，我们在StackOverflow上找到一个类似的问题： https://
ruby-on-rails - 我该怎么办 :remote location validation with CarrierWave? - 2
我在我的Rails3示例应用程序上使用CarrierWave。我想验证远程位置上传，因此当用户提交无效URL(空白或非图像)时，我不会收到标准错误异常:CarrierWave::DownloadErrorinImageController#createtryingtodownloadafilewhichisnotservedoverHTTP这是我的模型:classPaintingtrue,:length=>{:minimum=>5,:maximum=>100}validates:image,:presence=>trueend这是我的Controller:classPaintingsC