图解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异常重启后，数据可以恢复。

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=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然后在写磁盘。

2.二阶段提交

步骤：

• 更新操作记录到redo log里面，此时redo log处于prepare状态。
• 告知执行器执行完成了，随时可以提交事务。执行器生成这个操作的binlog，并把binlog写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的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 找对应的事务，如果找到有，则提交，没有则回滚。

3.组提交

• 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，然后再分别写入到共享表空间，然后再写入表空间。

为什么要有双写机制？

部分写的问题：

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

墨天轮原文链接：https://www.modb.pro/db/210787?sjhy

关于作者

陈家睿，云和恩墨MySQL技术顾问,拥有MySQL OCP、PGCE、OBCA、SCDP证书，长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作；热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。