mysql执行一条sql的过程！没有比这更容易懂的了！

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

我会从一张流程图慢慢深入去讲解

---

图一

这是我在别人那边扣的一张图，主要流程就是：
1：客户端与mysql之间建立连接（三次握手），建立连接后就可以输入密码了，断开的话就要经过四次挥手
2：sql查询语句通过查询缓存中找，如果找到一样的sql就返回结果（类型Map那种)，但是因为sql查询太多样了，命中率很低，在mysql8.0之后就取消了这个缓存（毕竟内存也是要钱的）
3：然后经过解析器判断sql写的有没有错，比如 select from 写成了 form 就会报错，而且是经常见到的一种错误

you have an error in your SQL syntax

4：执行sql ，每条select 查询语句流程主要可以分为下面三个阶段

• prepare阶段，预处理阶段
• optiimize阶段，优化阶段
• excute ，执行阶段

预处理阶段：如果我们查询的表不存在，在这个阶段就会报错。

优化阶段：可以通过explain命令去查询这条sql的执行计划（关于索引这块的使用我就不讲了，有兴趣可以去搜别的地方了解）。因为索引有比较多的种类（主键索引、普通二级索引），如果能触发覆盖索引的话就用覆盖索引，可以避免回表，选择一个最好的索引去查询，这个就是优化阶段所做的事

执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

图二

这张图对应图一的

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

这三个日志都是基于事务的开启，而我们的mysql会隐式开启事务来执行“增删改”语句的

一、第一步的Buffer Pool必要性

第一步为什么需要从磁盘文件（mysql储存数据的文件）中加载数据到缓冲池呢？

原因：
读：当读取数据时，在缓存中就从缓存中读取，不然从磁盘读取
写：因为缓冲池是一段内存，当我们执行修改的sql时，不可能去磁盘找到指定的数据然后去修改值，这样会很慢，不如直接把磁盘数据缓存到缓冲池（内存）中；然后在缓冲池中找到对应的数据，修改值即可。这个时候就会把当前页变成脏页（需要同步到磁盘中，下面会讲到，也就是WAL，就是第8步）

在MYSQL启动的时候，InnoDB会为BufferPool申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个一个页，Buffer Pool中的页就叫缓存页，（为什么会划分页，是因为Mysql是以页作为内存和磁盘的交互单位），此时这些缓存是空闲的，之后会随着程序的运行（sql的执行），把磁盘上的页数据缓存到缓冲池中，

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

二、第二步的undo日志

存储内容：undo日志记录的是更新前的数据（也就是事务提交之前的数据）

场景：一个事务在运行中出现问题了（比如有几条sql，其中一条sql执行出现问题），如果想要回滚，就需要undo日志，它保证了ACID特性中的原子性，Mysql崩溃也是一样的操作

操作方法：在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；

涉及到MVCC解决幻读（并未完全解决）：

事务结束后才会释放锁，所以表锁容易冲突

三 第四步redo日志

redo log主要就是防止断电时导致缓存修改的数据丢失，这个时候还没有写到磁盘上，
所以就很好理解WAL 技术指的是，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上 。

为什么说：不直接把数据同步到磁盘呢，而是数据先记录到redo.log，然后才同步到磁盘，因为redo.log是追加的形式，所以同步到磁盘是顺序写。

问题：如果提交一次，redo log就进行持久化到磁盘，会很耗性能和很慢，下面是mysql的3种redo.log同步到磁盘的策略

除此之外，InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

描述：当我们更新语句时，Innodb引擎会启动一个事物，在执行更新语句时，生成redo.log先写到redo log buffer中去，然后再将buffer中的redo.log按组的方式【顺序写】写到磁盘中去。写到磁盘中间有一个过程就是 page cache，这个还是属于内存（ Page Cache 的本质是由 Linux 内核管理的内存区域），这个缓存就是专门缓存文件数据的，更加方便同步到磁盘上。

当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。

当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。

当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（如果你想了解 Page Cache，可以看这篇 (opens new window)），Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

参数0 利用后台线程（每隔一秒）通过调用write（）写到操作系统的Page cache当中，然后调用 f’sync（）持久化到磁盘中，所以参数0的缺点：Mysql如果进程崩溃会导致上一秒的所有事务数据丢失。
参数2：缺点：只有当操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。也就是page cache的数据丢失

这三个参数的性能比较：
数据安全性：参数1>参数2》参数0
性能：参数0》参数2》参数1

问题2：当redo log文件写满了怎么办？

redo log文件由两个ib_logfile0 跟ib_logfile1组成，当ib_logfile0 文件写满了会切换到ib_logfile1文件
当我们脏页的数据同步到磁盘后，redo log对应的记录也就没有用了，需要删除，腾出空间。

redo log 是循环写入的，write pos 记录写到的位置，check point 是需要检查删除的位置。两个都是顺时针方向，如果write pos 追上了 check point 说明 redo log 文件满了，这时Mysql就不能执行更新操作（阻塞），所以在高并发下需要设计好redo log 的文件大小。此时会停下来将脏页刷新到磁盘中，然后check point 往后移，中间（write pos 到 check point ）就可以插入最新的数据。

第五步bin log日志

bin log 跟 redo log这两个日志都是innodb存储引擎生成的
Mysql在完成一条更新操作后，Server层还会生成一条binlog，等之后事务提交后，把该事务所产生的binlog写道bin log文件中去。

作用：
1: 用于数据库数据的回复，比如数据库数据删除完了，就使用这个去恢复数据
2：用于主从复制的实现

主从复制实现原理：
主库：当新生成的bin log写到bin log文件后，会返回给客户端操作成功响应
从库：会创建一个专门的I/O线程，连接到主库的log dump 线程，来接收主库 bin log文件日志，再把bin log信息写入到relay log中继日志中。 从库还会创建另一个线程用于读取relay log日志，用于回放binlog 更新存储引擎中的数据，实现数据一致性。

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。因为读数据是在从库读取，这个时候主库异步返回同步成功，实际从库不是最新数据，也就是数据丢失

半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

刷磁盘的时机
mysql提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。所以下面会有说 bin log 在提交事务之后 一定是被持久化到磁盘中了，如下图：

1. sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
2. sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
3. sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync

问题：
执行 id = 1这行数据 ，age字段值从60变为90

• 如果redo log 先刷到磁盘中，但是bin log没有刷到磁盘中，这个时候Mysql宕机了，redo log 中执行了一个 id = 1 这行数据 的age字段恢复到90，但是bin log 因为没有刷到磁盘，从表同步的时候值还是 age = 60，主从数据不一致
• 如果bin log先刷到磁盘中，这个时候Mysql宕机了, redo log没有写到磁盘中，主库的 id = 1这行数据的age 字段不变，因为没有同步到磁盘，数据丢失，但是从库会复制主库的bin log 数据，age值为90，主从数据不一致

解决：两阶段提交
prepare 阶段：将XID（内部XA事务的ID）写到redo log 中去，然后把redo log的状态变为 prepare状态，然后将redo log 持久化到磁盘中。
commit阶段：把XID写入到 bin log日志中，然后将bin log持久化到磁盘中，接着把redo log 状态变为 commit状态，此时这个状态不需要持久化到磁盘中，只要bin log持久化到磁盘中即可。

redo log就可以在事务提交之前 去持久化到磁盘，问题：如果宕机之后会不会导致主从数据不一致呢？ 不会的 ，因为在mysql宕机之后，重启会重新扫描redo log 会找到redo log 对应的事务状态设置为prepare的，然后拿到redo log中XID去bin log中查看有没有这个XID，如果没有说明该事务没有提交，就进行回滚（redo log）。

必须 事务在提交之后 必须让bin log日志持久化到磁盘中。

两阶段提交缺点

• 磁盘I/O次数高，每次事务提交 都要经过 prepare、commit阶段的两次fsync（刷盘）
• 锁竞争激烈问题：因为需要在多事务的情况下要保证两个日志提交的顺序一致，在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作。

解决办法:使用组的方式，当多个事务提交时，将多个bin log 刷盘操作合并成一个即可，从而减少I/O操作。

---