一.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/21078…（复制链接至浏览器或点击文末浏览原文查看)
对于作者
陈家睿，云和恩墨 MySQL 技术顾问, 领有 MySQL OCP、PGCE、OBCA、SCDP 证书，长期服务于电信行业。现负责公司 MySQL 数据库、分布式数据库运维方面的技术工作；热衷于运维故障解决、备份复原、降级迁徙、性能优化的学习与分享。