一.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数据库、分布式数据库运维方面的技术工作；热衷于运维故障解决、备份复原、降级迁徙、性能优化的学习与分享。