这是在网上找到的一张流程图，写的比较好，大家可以先看图，然后看详细阅读下面的各个步骤。



执行流程：

1.连接验证及解析

客户端与MySQL Server建立连接，发送语句给MySQL Server，接收到后会针对这条语句创建一个解析树，然后进行优化，（解析器知道语句是要执行什么，会评估使用各种索引的代价，然后去使用索引，以及调节表的连接顺序）然后调用innodb引擎的接口来执行语句。

2.写undo log

innodb 引擎首先开启事务，对旧数据生成一个反向的UPDATE的语句(如果是INSERT会生成反向的DELETE语句)，用于提交失败后回滚，写入undo log，得到回滚指针，并且更新这个数据行的回滚指针和版本号(会设置为更新的事务id)。

3.从索引中查找数据

根据查询条件去B+树中找到这一行数据（如果是唯一性索引，查到第一个数据就可以了（因为有唯一性约束），如果是普通索引，会把所有数据查找出来。）

4.更新数据

首先判断数据页是否在内存中？

4.1 如果数据页在内存中

先判断更新的索引是普通索引还是唯一性索引？

4.1.1 普通索引

如果更新的索引是普通索引，直接更新内存中的数据页

4.1.2 唯一性索引

如果更新的索引是唯一性索引，判断更新后是否会破坏数据的唯一性，不会的话就更新内存中的数据页。

4.2 如果数据页不在内存中

先判断更新的索引是普通索引还是唯一性索引？

4.2.1 普通索引

如果是更新的索引是普通索引，将对数据页的更新操作记录到change buffer，change buffer会在空闲时异步更新到磁盘。

4.2.2 唯一性索引

如果是更新的索引是唯一性索引，因为需要保证更新后的唯一性，所以不能延迟更新，必须把数据页从磁盘加载到内存，然后判断更新后是否会数据冲突，不会的话就更新数据页。

5.写undo log（prepare状态）

将对数据页的更改写入到redo log，将redo log设置为prepare状态。

6.写bin log（commit状态），提交事务

通知MySQL server已经更新操作写入到redo log 了，随时可以提交，将执行的SQL写入到bin log日志，将redo log改成commit状态，事务提交成功。（一个事务是否执行成功的判断依据是是否在bin log中写入成功。写入成功后，即便MySQL Server崩溃，之后恢复时也会根据bin log， redo log进行恢复。具体可以看看下面的崩溃恢复原则）

补充资料：

二段提交制是什么？

更新时，先改内存中的数据页，将更新操作写入redo log日志，此时redo log进入prepare状态，然后通知MySQL Server执行完了，随时可以提交，MySQL Server将更新的SQL写入bin log，然后调用innodb接口将redo log设置为提交状态，更新完成。

如果只是写了bin log就提交，那么忽然发生故障，主节点可以根据redo log恢复数据到最新，但是主从同步时会丢掉这部分更新的数据。

如果只是写binlog，然后写redo log，如果忽然发生故障，主节点根据redo log恢复数据时就会丢掉这部分数据。

MySQL崩溃后，事务恢复时的判断规则是怎么样的？（以redolog是否commit或者binlog是否完整来确定）

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

undo log是什么？

undo log主要是保证事务的原子性，事务执行失败就回滚，用于在事务执行失败后，对数据回滚。undo log是逻辑日志，记录的是SQL。（可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。）

在事务提交后，undo log日志不会立即删除，会放到一个待删除的链表中，有purge线程判断是否有其他事务在使用上一个事务之前的版本信息，然后决定是否可以清理，简单的来说就是前面的事务都提交成功了，这些undo才能删除。

change buffer是什么（就是将更新数据页的操作缓存下来）

在更新数据时，如果数据行所在的数据页在内存中，直接更新内存中的数据页。

如果不在内存中，为了减少磁盘IO的次数，innodb会将这些更新操作缓存在change buffer中，在下一次查询时需要访问这个数据页时，在执行change buffer中的操作对数据页进行更新。

适合写多读少的场景，因为这样即便立即写了，也不太可能会被访问到，延迟更新可以减少磁盘I/O，只有普通索引会用到，因为唯一性索引，在更新时就需要判断唯一性，所以没有必要。

redo log 是什么？

redo log就是为了保证事务的持久性。因为change buffer是存在内存中的，万一机器重启，change buffer中的更改没有来得及更新到磁盘，就需要根据redo log来找回这些更新。

优点是减少磁盘I/O次数，即便发生故障也可以根据redo log来将数据恢复到最新状态。

缺点是会造成内存脏页，后台线程会自动对脏页刷盘，或者是淘汰数据页时刷盘，此时收到的查询请求需要等待，影响查询。

（尊重劳动成果，转载请注明出处：https://yangwenqiang.blog.csdn.net/article/details/90574620冷血之心的博客）


关注微信公众号（文强的技术小屋），学习更多技术知识，一起遨游知识海洋~




快速导航：

 MySQL原理与实践（一）：一条select语句引出Server层和存储引擎层

MySQL原理与实践（二）：一条update语句引出MySQL日志系统

MySQL原理与实践（三）：由三种数据结构引入MySQL索引及其特性

MySQL原理与实践（四）：由数据库事务引出数据库隔离级别

MySQL原理与实践（五）：数据库的锁机制

MySQL原理与实践（六）：自增主键的使用

目录

前言：

正文：

redo log （重做日志模块）

磁盘IO和内存读写效率的关系：

WAL（Write-Ahead Logging技术）：

 redo log示意图：

redo log存储的数据结构如下所示：

为什么redo log写入效率比直接写磁盘效率高？

redo log和buffer pool的关系：

binlog（归档日志模块）

为什么我们还需要binlog日志？

redo log和binlog日志的不同点如下：

binlog恢复数据库：

一条update语句在MySQL内部是如何执行的：

两阶段提交协议：

redo log 和 binlog 是怎么关联起来的?

为什么处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复：

如果 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以，这种设计可以吗？

日志模块总结：

结束语：

前言：

        这是MySQL原理与实践的第二篇文章，通过一条update更新语句来引出MySQL日志系统，主要包括redo log（重做日志）模块和binlog（归档日志）模块。虽然，MySQL中还有undo log（回滚日志）的概念，但是回滚日志主要用来一致性视图中实现MVCC，所以undo log的相关介绍，我们留在后边介绍学习事务相关知识点时再行介绍。本文整理总结于极客时间 -《MySQL实战45讲》，欢迎大家订阅学习，干货满满。

正文：

       假设现在有一张表，建表语句如下：

mysql> create table T(ID int primary key, c int);

       我们再来看一个简单的更新语句：

mysql> update T set c=c+1 where ID=2;

       通过上一篇文章的学习，我们知道了MySQL的内部逻辑架构图，包括Server层的连接器，查询缓存，分析器，优化器以及执行器；存储引擎层的存储引擎。如下图所示：

                                             

        查询语句和更新语句执行的内部逻辑基本上都是一样的，但是更新语句涉及到了数据的更改，所以必不可少的需要引入日志模块，即redo log重做日志模块和binlog归档日志模块。接下来，我们依次介绍这两个重要的日志模块。

redo log （重做日志模块）

磁盘IO和内存读写效率的关系：

       在介绍redo log之前，我们先来了解下磁盘和内存的区别与联系。我们知道，MySQL是一种关系型数据库，数据持久化保存在磁盘上。但是我们都知道，磁盘的IO效率很低，与之对应的是内存的IO效率远高于磁盘IO。

       在MySQL中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL采用了另外一种更新机制，先更新保存在内存中，写入内存成功，即表示该数据更新成功，给客户端返回。随后，在一个数据库空闲的时间段或者是内存占满之后，将内存中的数据刷到磁盘上。

WAL（Write-Ahead Logging技术）：

        这么做有什么问题吗？当然有问题，如果发生异常重启的现象，那么内存中的数据将会丢失，出现数据不一致的情况。那么如何解决呢？这个时候，我们的redo log重做日志就该闪亮登场了。在更新数据写入内存的同时，我们会记录redo log，并且持久化到磁盘，当数据库异常宕机之后，我们可以根据redo log重做日志来恢复数据，保证之前提交的数据不会丢失，也就是拥有了crash-safe的能力。同时这就是我们常说的WAL技术，即Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

 redo log示意图：

         InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示：



        write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

        write pos 和 checkpoint 之间的是redo log还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

       有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log存储的数据结构如下所示：




	
reod_log_type: 占用1字节，表示重做日志类型。各种不同操作有不同的重做日志格式，但有基本的格式
	
	

	
space：表空间的ID，采用压缩的方式，占用空间可能小于4字节
	
	

	
page_no：页的偏移量，同样采用压缩方式
	
	

	
redo_log_body：每个重做日志的数据部分，恢复时需要调用相应的函数解析。
	
为什么redo log写入效率比直接写磁盘效率高？

       我们知道，redo log是一种日志文件，必然也是持久化到磁盘上的。那为什么先更新内存，然后写redo log会比数据直接写磁盘效率高呢？原因在于redo log可以循环并且顺序写入磁盘，数据直接写入磁盘多了查找源数据和随机写入的过程，自然效率会低下很多。

redo log和buffer pool的关系：

      我们知道，更新数据其实就是先写内存，同时记录redo log，那么就会返回当前数据已经更新成功。此时，内存中的数据页和磁盘的数据页是不一致的，称此为脏页。正常情况下，数据库会在适当的时候将buffer pool里边的脏页刷新到磁盘上。整个过程其实和redo log是没有任何关系的。只有在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，之后会被刷新到磁盘。

 

binlog（归档日志模块）

        MySQL的架构包括Server层和引擎层。前者是MySQL功能层面的事情，引擎层负责存储相关的具体事宜。redo log是InnoDB引擎特有的日志模块；binlog是Server层自带的日志模块。

为什么我们还需要binlog日志？

        最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log和binlog日志的不同点如下：


	
redo log是InnoDB引擎特有的日志模块；binlog是Server层自带的日志模块
	
	

	
redo log是物理日志，记录了某个数据页上所做的修改；binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是记录了修改数据的SQL语句
	
	

	
redo log是循环写的形式，空间固定会被用完；binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。
	
        我们可以通过mysqlbinlog可以解析查看binlog日志，在MySQL中binlog的日志格式有statement，row以及mixed三种方式，为了可以更加准确的记录归档日志，我们一般选择row格式做为binlog的日志格式。特性对比如下：



binlog恢复数据库：

        binlog是一种归档日志，只要binlog存在，我们就可以进行数据库的恢复。假如说，在今天中午12点的时候，发现上午10点执行了错误的SQL语句，想把数据库状态恢复到上午10点，错误语句执行之前。那么该怎么办呢？

数据恢复步骤如下：

首先你要拿到最近一次的备份库
	
拿到最近备份库开始到出错时候的所有binlog（binlog日志保留时间可以自定义）
	
使用binlog重放到错误发生之前。
一条update语句在MySQL内部是如何执行的：

在简单介绍了redo log和binlog之后，我们回到文章开头，分析一条update语句在MySQL内部是如何执行的：

执行器要先找存储引擎找到这一行数据，从内存中或者磁盘中，返回给执行器
	
执行器拿到数据之后进行更新操作，再调用引擎接口写入这行新数据
	
引擎将这行数据更新到内存中，并且将操作记录写入redo log中，此时redo log处于prepare状态。然后告知执行器执行完成随时可以提交事务了
	
执行器生成这个操作的binlog，并且将binlog写入磁盘
	
执行器调用引擎的提交事务接口，引擎将刚刚写入的redo log改成提交（commit）状态，更新完成
update语句的执行逻辑图如下所示：

                                    

两阶段提交协议：

由执行逻辑图可以看出，这里其实是使用了两阶段提交，如果不适用两阶段提交会有如下的问题：

先写redo log，后写binlog。会导致异常重启后redo log多了，binlog缺失。使用binlog恢复的备份库会缺少一条事务
	
先写binlog，后写redo log。会导致多一条事务出来
	
总结：不使用两阶段提交会导致数据库状态和用日志恢复出来的数据库状态不一致

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。


       既然两阶段提交可以保证数据的一致性，那么根据上图update执行示意图，我们来分析下异常发生的时刻对于数据一致性的影响：

若在写入binlog日志之前发生异常crash：由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。
	
若binlog日志写完之后发生了异常crash：
	
崩溃恢复时的判断规则：
		
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交。
			
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
			
a. 如果是，则提交事务
				
b. 否则，回滚事务
			
		
	
那么如何判断binlog日志是否完整？


其实每一种格式的binlog日志都是由固定格式的，并且在MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。


redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
	
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
为什么处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复：

       这和数据与备份的一致性有关。在 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

如果 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以，这种设计可以吗？

       事务的持久性问题，对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了。

日志模块总结：

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log都直接持久化到磁盘。这个参数设置成 1，可以保证MySQL 异常重启之后数据不丢失。
	
binlog是一种归档日志。sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘，可以保证 MySQL 异常重启之后 binlog 不丢失。
结束语：

        这篇文章是MySQL原理与实践系列文章的第二篇，结合一条update更新语句，主要介绍了MySQL中重要的日志相关模块，并且进行了相应的概念总结。之后会继续更新该系列文章，希望对大家有帮助。

 


如果对你有帮助，记得点赞哦~欢迎大家关注我的博客，可以进群366533258一起交流学习哦~

本群给大家提供一个学习交流的平台，内设菜鸟Java管理员一枚、精通算法的金牌讲师一枚、Android管理员一枚、蓝牙BlueTooth管理员一枚、Web前端管理一枚以及C#管理一枚。欢迎大家进来交流技术。



关注微信公众号（文强的技术小屋），学习更多技术知识，一起遨游知识海洋~

一条SQL语句的执行流程
前言
一条select语句的执行流程
建立连接
查询缓存
解析器和预处理器
词法解析和语法解析(Parser)
预处理器(Preprocessor)
查询优化器(Query Optimizer)
优化器可以做哪些优化
优化器并不是万能的
优化器如何得到查询计划
存储引擎查询
返回结果
一条update语句的执行流程
Buffer Pool
redo log
Write-Ahead Logging（WAL）
redo log是如何刷盘的
bin log
bin log和redo log的区别
update语句的执行流程
两阶段提交
假如不采用两阶段提交法
宕机后的数据恢复规则
总结

前言
本文基于MySQL5.7版本。
前面几篇MySQL系列的文章介绍了索引，事务和锁相关知识，那么今天就让我们来看看当我们执行一条select语句和一条update语句的时候,MySQL要经过哪些步骤，才能返回我们想要的数据。
一条select语句的执行流程
MySQL从大方向来说，可以分为 Server 层和存储引擎层。而Server层包括连接器、查询缓存、解析器、预处理器、优化器、执行器等，最后Server层再通过API接口形式调用对应的存储引擎层提供的接口。如下图所示(图片来源于《高性能MySQL》)：



根据流程图，一条select查询大致经过以下六个步骤：

1、客户端发起一个请求时，首先会建立一个连接

2、服务端会检查缓存，如果命中则直接返回，否则继续之后后面步骤

3、服务器端根据收到的sql语句进行解析，然后对其进行词法分析，语法分析以及预处理

4、由优化器生成执行计划

5、调用存储引擎层API来执行查询

6、返回查询到的结果
查询流程也可以通过如下图表示(图片来源于丁奇MySQL45讲)：


建立连接
第一步建立连接，这个很容易理解，需要特别指出的是MySQL服务端和客户端的通信方式采用的是半双工协议。
通信方式主要可以分为三种：单工，半双工，全双工，如下图：



单工：通信的时候，数据只能单向传输。比如说遥控器，我们只能用遥控器来控制电视机，而不能用电视机来控制遥控器。
半双工：通信的时候，数据可以双向传输，但是同一时间只能有一台服务器在发送数据，当A给B发送数据的时候，那么B就不能给A发送数据，必须等到A发送结束之后，B才能给A发送数据。比如说对讲机。
全双工：通信的时候，数据可以双向传输，并且可以同时传输。比如说我们打电话或者用通信软件进行语音和视频通话等。

半双工协议让MySQL通信简单快速，但是也在一定程度上限制了MySQL的性能，因为一旦从一端开始发送数据，另一端必须要接收完全部数据才能做出响应。所以说我们批量插入的时候尽量拆分成多次插入而不要一次插入太大数据，同样的查询语句最好也带上limit限制条数，避免一次返回过多数据。
MySQL单次传输数据包的大小可以通过参数max_allowed_packet控制,默认大小为4MB
SHOW VARIABLES LIKE 'max_allowed_packet';


查询缓存
连接上了之后，如果缓存是打开的，那么就会进入查询缓存阶段，可以通过如下命令查看缓存是否开启：
SHOW VARIABLES LIKE 'query_cache_type';



我们可以看到，缓存默认是关闭的。这是因为MySQL的缓存使用条件非常苛刻，是通过一个大小写敏感的哈希值去匹配的，这样就是说一条查询语句哪怕只是有一个空格不一致，都会导致无法使用缓存。而且一旦表里面有一行数据变动了，那么关于这种表的所有缓存都会失效。所以一般我们都是不建议使用缓存，MySQL最新的8.0版本已经将缓存模块去掉了。
解析器和预处理器
跳过了缓存模块之后，查询语句会进入解析器进行解析。
词法解析和语法解析(Parser)
这一步主要的工作就是检查sql语句的语法对不对，在这里，首先会把我们整个SQL语句打碎，比如：select  name from test where id=1，就会被打散成select，name，from，test，where，id，=，1 这8个字符，并且能识别出关键字和非关键字，然后根据sql语句生成一个数据结构，也叫做解析树(select_lex)，如下图：


预处理器(Preprocessor)
经过了前面的词法和语法解析，那么至少我们一条sql语句的语法格式是满足要求了，接下来我们还需要做什么呢？自然是检查表名，列名以及其他一些信息等是不是真实存在的，预处理就是做一个表名和字段名等相关信息合法性的检测。
查询优化器(Query Optimizer)
经过上面的步骤，到这里就得到了一句有效的sql语句了。而对一个查询语句，尤其是复杂的多表查询语句，我们可以有很多种执行方式，每种执行方式的效率也不一样，所以这时候就需要查询优化器去选择一种它认为最高效的执行方式。
查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，哪种执行计划开销最小，就选择哪种。
我们可以通过变量Last_query_cost来查询开销：
SELECT * FROM test;
show status like 'Last_query_cost';



上图中展示的结果就表示MySQL认为SELECT * FROM test 查询语句需要做至少2个数据页的随机查找才能完成上面的查询。

这个结果是通过一系列复杂的运算得到的，包括每个表或者索引的页面个数，索引的基数，索引和数据行的长度，索引分布的情况。
优化器在评估成本的时候，不会考虑任何缓存的作用，而是假设读取任何数据都需要经过一次IO操作。
优化器可以做哪些优化
优化器可以替我们做很多优化，下面列举一些常用的优化：

重新定义关联的顺序。优化器并不一定按照我们写的查询关联语句中的关联顺序，而是会按照优化后的顺序进行查询。
将外连接转为为内连接。
使用等价转换原则。比如a<b and a=5会被转换为a=5 and b>5
优化COUNT(),MIN()和MAX()
预估并转化为常数表达式
覆盖索引扫描。想要详细了解覆盖索引的可以点击这里。
子查询优化。
提前终止查询。比如我们使用了一个不成立的条件，则会立刻返回空。
等值传播。
优化IN()语句。在其他很多数据库中in等同于or语句，但是MySQL中会讲in中的值先进行排序，然后按照二分查找的方法来确定是否满足条件。

实际当中优化器能做的优化远远比上面列举的更多，所以有时候我们不要觉得比优化器更聪明，所以大部分情况下我们都可以让优化器做出优化就可以了，如果有些我们确定优化器没有选择最优的查询方案，我们也可以在查询中通过添加hint提示告知到优化器，比如通过force index强制使用索引或者straight_join语句强制优化器按我们想要的表顺序进行关联。
优化器并不是万能的
MySQL优化器也并不是万能的，并不是总能把我们写的糟糕的sql语句优化成一个高效的查询语句，而且也有很多种原因会导致优化器做出错误的选择：

统计信息不准确。MySQL评估成本依赖于存储引擎提供的的统计信息，然而存储引擎提供的统计信息有时候会有较大偏差。
执行计划的成本估算不等于实际的执行成本。比如估算成本的时候不考虑缓存，而实际执行有些数据在缓存中。
优化器认为的最优可能并不是我们需要的最优。比如有时候我们想要时间最短，但是优化器
优化器从不考虑其他并发的查询。
优化器并不总是基本成本的优化。有时候也会基于规则，比如当存在全文索引，查询时使用了match()子句时，即使选择其他索引更优，优化器仍然会选择全文索引。
优化器不将不受其控制的操作计算为成本。如执行存储过程或者用户自定义函数的成本。
优化器有时候无法估算所有的执行计划，所以也有可能错过最优执行计划。

优化器如何得到查询计划
优化器听起来比较抽象，给人一种看不见摸不着的感觉，但是实际上我们也可以通过参数打开优化器追踪，优化器追踪默认是关闭的，因为开启后会影响性能，所以建议是在需要定位问题的时候开启，并及时关闭。
SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';

接下来执行一句查询语句：
SELECT t1.name AS name1,t2.name AS name2 FROM test t1 INNER JOIN test2 t2 ON t1.id=t2.id

这时候优化器的分析过程已经被记录下来了，可以通过下面语句查询：
SELECT * FROM information_schema.optimizer_trace;

得到如下结果：



上面的图是为了看数据效果，如果需要自己操作的话，需要用shelll命令窗口去执行，sqlyog工具中直接查询出来TRACE列是空的，shell中返回的TRACE列信息如下：



从截图中的轮廓可以看出来这是一个json数据格式。
跟踪信息主要分为以下三部分(上图并未将全部内容展示出来，感兴趣的可以自己去尝试一下，开启之后记得及时关闭哦)：

准备阶段(join_preparation)：expanded_query中的查询语句就是优化后的sql
优化阶段(join_optimization)：considered_execution_plans中列出来所有的执行计划
执行阶段(join_execution)

considered_execution_plans中的执行计划我们也可以通过explain关键字去查看，想要详细了解explain的可以点击这里。
存储引擎查询
当Server层得到了一条sql语句的执行计划后，这时候就会去调用存储引擎层对应的API，执行查询了。因为MySQL的存储引擎是插件式的，所以每种存储引擎都会对Server提供了一些对应的API调用。

想要了解MySQL存储引擎相关知识的，可以点击这里。
返回结果
最后，将查询出得到的结果返回Server层，如果开启了缓存，Server层返回数据的同时还会写入缓存。
MySQL将查询结果返回是一个增量的逐步返回过程。例如：当我们处理完所有查询逻辑并开始执行查询并且生成第一条结果数据的时候，MySQL就可以开始逐步的向客户端传输数据了。这么做的好处是服务端无需存储太多结果，从而较少内存消耗(这个操作可以通过sql _buffer_result来提示优化器，和上文说的force index，straight_join一样都是人为强制优化器执行我们想要的操作)。
一条update语句的执行流程
一条更新语句，其实是增，删，查的综合体，查询语句需要经过的流程，更新语句全部需要执行一次，因为更新之前必须要先拿到(查询)需要更新的数据。
Buffer Pool
InnnoDB的数据都是放在磁盘上的，而磁盘的速度和CPU的速度之间有难以逾越的鸿沟，为了提升效率，就引入了缓冲池技术，在InnoDB中称之为Buffer Pool。
从磁盘中读取数据的时候，会先将从磁盘中读取到的页放在缓冲池中，这样下次读相同的页的时候，就可以直接从Buffer Pool中获取。
更新数据的时候首先会看数据在不在缓冲池中，在的话就直接修改缓冲池中的数据，注意，前提是我们不需要对这条数据进行唯一性检查（因为如果要进行唯一性检查就必须加载磁盘中的数据来判断是否唯一了）
如果只修改了Buffer Pool中的数据而不修改磁盘中数据，这时候就会造成内存和磁盘中数据不一致，这种也叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘， 每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
那么现在有一个问题，假如我们更新都需要把数据写入数据磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，InnoDB就有了redo log,并且采用了Write-Ahead Logging(WAL)方案实现。
redo log
redo log，即重做日志，是InnoDB引擎所特有,主要用于崩溃修复(crash-safe)。
Write-Ahead Logging（WAL）
Write-Ahead Logging，即先写日志，也就是说我们执行一个操作的时候会先将操作写入日志，然后再写入数据磁盘，那么有人就会问了，写入数据表是磁盘操作，写入redo log也是磁盘操作，同样都是写入磁盘，为什么不直接写入数据，而要先写入日志呢？这不是多次一举吗？
设想一下，假如我们所需要的数据是随机分散在不同页的不同扇区中，那么我们去找数据的时候就是随机IO操作，而redo log是循环写入的，也就是顺序IO。一句话：

刷盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日 志，可以延迟刷盘时机，进而提升系统吞吐
redo log是如何刷盘的
InnoDB中的 redo log是固定大小的，也就是说redo log并不是随着文件写入慢慢变大，而是一开始就分配好了空间，空间一旦写满了，前面的空间就会被覆盖掉，刷盘的操作是通过Checkpoint实现的。如下图：



check point 是当前要覆盖的位置。write pos是当前写入日志的位置。写日志的时候是循环写的，覆盖旧记录前要把记录更新到数据文件。如果write pos和 check point 重叠，说明redo log 已经写满，这时候需要同步redo log刷到磁盘中。
bin log
MySQL整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面讲的redo log是InnoDB 引擎特有的日志，而Server 层也有自己的日志，称为 binlog（归档日志），也叫做二进制日志。
可能有人会问，为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM是不支持事物的，也没有崩溃恢复(crash-safe)的能力，binlog日志只能用于归档。那么既然InnoDB是需要支持事务的，那么就必须要有崩溃恢复(crash-safe)能力，所以就使用另外一套自己的日志系统，也就是基于redo log 来实现 crash-safe 能力。
bin log和redo log的区别
1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的Server层实现的，所有引擎都可以使用。

2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给id=2 这一行的c字段加 1 ”。

3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
update语句的执行流程
前面铺垫了这么多，主要是想让大家先理解redo log和big log这两个概念，因为更新操作离不开这两个文件，接下来我们正式回到正题，一条update语句到底是如何执行的，可以通过下图表示：



上图可以大概概括为以下几步：

1、先根据更新语句的条件，查询出对应的记录，如果有缓存，也会用到缓存

2、Server端调用InnoDB引擎API接口，InnoDB引擎将这条数据写到内存，同时写入redo log，并将redo log状态设置为prepare

3、通知Server层，可以正式提交数据了

4、Server层收到通知后立刻写入bin log，然后调用InnoD对应接口发出commit请求

5、InnoDB收到commit请求后将数据设置为commit状态
上面的步骤中，我们注意到，redo log会经过两次提交，这就是两阶段提交。
两阶段提交
两阶段提交是分布式事务的设计思想，就是首先会有请求方发出请求到各个服务器，然后等其他各个服务器都准备好之后再通知请求方可以提交了，请求方收到请求后再发出指令，通知所有服务器一起提交。
而我们这里redo log是属于存储引擎层的日志，bin log是属于Server层日志，属于两个独立的日志文件，采用两阶段提交就是为了使两个日志文件逻辑上保持一致
假如不采用两阶段提交法
假如有一条语句id=1,age=18，我们现在要把这条数据的age更新为19：

先写 redo log 后写 binlog

假设在redo log 写完，binlog还没有写完的时候，MySQL发生了宕机(crash)。重启后因为redo log写完了，所以会自动进行数据恢复，也就是age=19。但是由于binlog没写完就宕机( crash)了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后某一天假如我们把数据丢失了，需要用bin log进行数据恢复就会发现少了这一次更新。
先写binlog后写redo log

假如在binlog写完，redo log还没有写完的时候，MySQL发生了宕机(crash)。重启后因为redo log没写完，所以无法进行自动恢复，那么数据就还是age=18了，然后某一天假如我们把数据丢失了，需要用binlog进行恢复又会发现恢复出来的数据age=19了。

通过以上的两个假设我们就会发现，假如不采用两阶段提交法就会出现数据不一致的情况，尤其是在有主从库的时候，因为主从复制是基于binlog实现的，如果redo log和bin log不一致，就会导致主从库数据不一致。
宕机后的数据恢复规则
1、如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

2、如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：如果是，则提交事务；否则，回滚事务。
总结
本文主要分析了select和update语句的执行过程，而在分析update语句执行过程中，又简单介绍了redo log和bin log相关概念，这一部分内容在本文中没有过多深入的讲解，仅仅只是为了让大家去理解更新流程而做了简单的介绍，像redo log和其对应的缓存之间的关系，redo log刷盘策略，bin log写入策略，有了bin log为何还需要redo log等等问题本文中并没有给出明确的解释，因为本文篇幅有限，深入之后就会涉及到InnoDB引擎的存储结构以及更底层的一些知识，关于这些内容将在《MySQL系列》的后续文章中都会进行一一介绍。请关注我，和孤狼一起学习

一、mysql架构分析

下面是mysql的一个简要架构图：



mysql主要分为Server层和存储引擎层

Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。

InnoDB 5.5.5版本作为默认引擎。

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。

完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

二、语句分析

2.1 查询语句

说了以上这么多，那么究竟一条sql语句是如何执行的呢？其实我们的sql可以分为2中，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name='张三';



结合上面的说明，我们分析下这个语句的执行流程：


	
先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在mysql8.0版本以前，会先查询缓存，以这条sql语句为key在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
	
	

	
通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询select，提取需要查询的表名为tb_student,需要查询所有的列，查询条件是这个表的id='1'。然后判断这个sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
	
	

	
接下来就是优化器进行确定执行方案，上面的sql语句，可以有两种执行方案：

	
  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是18。
  b.先找出学生中年龄18岁的学生，然后再查询姓名为“张三”的学生。


	
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
	
	

	
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
	
2.2 更新语句

以上就是一条查询sql的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql语句如下：

update tb_student A set A.age='19' where A.name='张三';


我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，mysql 自带的日志模块式binlog（归档日志），所有的存储引擎都可以使用，我们常用的InnoDB引擎还自带了一个日志模块redo log，我们就以InnoDB模式下来探讨这个语句的执行流程。流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存。
	
然后拿到查询的语句，把 age 改为19，然后调用引擎API接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交。
	
执行器收到通知后记录binlog，然后调用引擎接口，提交redo log 为提交状态。
	
更新完成。
这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗？这就是之前mysql的模式了，MyISAM引擎是没有redo log的，那么我们知道它是不支持事务的，所以并不是说只用一个日志模块不可以，只是InnoDB引擎就是通过redo log来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么redo log 要引入prepare预提交状态？这里我们用反证法来说明下为什么要这么做？

先写redo log 直接提交，然后写 binlog，假设写完redo log 后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候bingog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
	
先写binlog，然后写redo log，假设写完了binlog，机器异常重启了，由于没有redo log，本机是无法恢复这一条记录的，但是binlog又有记录，那么和上面同样的道理，就会产生数据不一致的情况。
如果采用redo log 两阶段提交的方式就不一样了，写完binglog后，然后再提交redo log就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设redo log 处于预提交状态，binglog也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于mysql的处理机制了，mysql的处理过程如下：

判断redo log 是否完整，如果判断是完整的，就立即提交。
	
如果redo log 只是预提交但不是commit状态，这个时候就会去判断binlog是否完整，如果完整就提交 redo log, 不完整就回滚事务。
这样就解决了数据一致性的问题。

三、总结

Mysql 主要分为Server曾和引擎层，Server层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用。
	
引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。
	
sql等执行过程分为两类，一类对于查询等过程如下：权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎
	
对于更新等语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit
四、参考

《一起构建Mysql知识网络》
转自 https://my.oschina.net/luozhou/blog/3027734