SQL执行原理以及InnoDB初探

1、SQL语句的简要执行流程

当你往mysql发送一条数据查询的时候，那么就会和mysql进行建立一个链接，会校验你的用户名和密码以及一些权限。

当权限通过后，你的链接已经建立完成了。那么这个时候我们可以思考一下，当你建立好了一个链接以后，下一个访问请求过来，那么你还要建立链接，这个时候开销是会很大的。因为涉及到网络通信，和资源开销。那么这个时候就可以通过连接池技术来进行减少开销，我先建立好一些链接，你来用就行了。

那么我们会想到这些问题，mysql当然也想到了会建立连接池来减少开销。Mysql也会维护和各个系统之间的数据链接。

这个时候我们发现，链接也建立好了，sql语句此时就可以发送给MySQL去执行了。那么一个网络链接肯定会有一个线程去处理

，那么首先这个网络链接会交给一个线程去执行，然后把我们的sql语句交给SQL接口去执行。SQL接口拿到我们的SQL，发现我不认识啊，我们人类肯定是认识的，但是我不认识，那么这个时候就会交给解析器执行解析，那么sql解析好了，我们这个时候就去执行吗。

当然不行，我们肯定要优化一下我们的SQL在进行执行，因为我们的sql千变万化，可能有些地方写的不好。这个时候我们的查询优化器就会生成一个最优的执行路径，然后进行执行。也就是查询优化器。当查询优化器收到请求之后，就会把执行路径交给执行器，去调用存储引擎的接口去执行SQL返回结果。当然存储引擎是有很多的，例如InnoDB、MyISAM、Memory等。然后交给存储引擎去执行。

2、InnoDB初探

2.1 redo 和 undo

我们此时要引入一个概念，也就是buffer pool，在InnoDB中有一个池子，也就是我们的buffer poll，缓存池。

接下来我们就根据一个更新语句来看看怎么运行的。

SQL：update users set name =‘xxx’ where id = 20

首先，会在缓存池中寻找是否存在users表的id=20这条数据，如果不存在，则会从磁盘中，把这个数据加载到buffer pool中。

然后会这行数据进行加独占锁，然后将这个数据写入到undo log ，用于回滚。此时会修改这个内存中id = 20的数据，将他的name修改为‘xxx’ ，这个时候会把这个数据记录到redo log buffer中。

此时就得看了innodb_flush_log_at_trx_commit的值。

innodb_flush_log_at_trx_commit=0

如果=0，那么就会把数据写入到redolog缓存中。提交事务后，就不做其他的了。假如此时宕机，数据也就没了。

innodb_flush_log_at_trx_commit=1

如果=1，那么在提交成功后，redolog buffer肯定会出现在日志文件中，因为哪怕此时宕机了，也会从redolog中恢复数据。

innodb_flush_log_at_trx_commit=2

如果=2，那么在提交的时候，会把redolog buffer写入到os cache中，然后返回提交成功，等一段时间就会刷写到磁盘上，但是还没刷写到磁盘上的时候，机器宕机了，那么数据也丢了。

所以对于数据库这样严格的系统而言，一般建议redo日志刷盘策略设置为1，保证事务提交之后，数据绝对不能丢失。

2.2 binlog

之前我们说的那个redolog的意思大概就是对那个数据页做了什么修改。

binlog是一种归档日志，例如我们更新了id=10的这个数据，然后更新后的值，是什么。

那么binlog是怎么执行的呢。

当在redolog写入磁盘之后，就会写入binlog。写完binlog之后会往redolog日志文件打入commit标记，这样不会造成数据不一致。

这个时候才可以提交事务。

当然提交之后，buffer pool中的数据和磁盘的数据还是不一致的，这个时候别担心，会有一个线程定时的把buffer pool的数据刷写进磁盘。这个时候磁盘和bufferpool中的数据也就是一致的了。

2.3 InnoDB 总结

大家通过一次更新数据的流程，就可以清晰地看到，InnoDB存储引擎主要就是包含了一些buffer pool、redo log

buffer等内存里的缓存数据，同时还包含了一些undo日志文件，redo日志文件等东西，同时mysql server自己还有

binlog日志文件。

在你执行更新的时候，每条SQL语句，都会对应修改buffer pool里的缓存数据、写undo日志、写redo log buffer几

个步骤；

但是当你提交事务的时候，一定会把redo log刷入磁盘，binlog刷入磁盘，完成redo log中的事务commit标记；最后

后台的IO线程会随机的把buffer pool里的脏数据刷入磁盘里去。