MySQL面试常问：一条语句提交后，数据库都做了什么？-技术圈

今天大部分程序需要处理的数据，都来自数据库，尤其是关系型数据库，那么一条 SQL 提交到数据库之后，数据库都做了些什么？如果不懂这些问题，就无法更好的使用数据库，更无法回答好面试官的问题。现在流行的开源数据库，非 MySQL 莫属，面试中 MySQL 也是必问，于是我就学习了专栏《MySQL实战45讲》，今天的文章试着回答以下两个问题：

1、一条 SQL 语句提交到数据库之后，数据库都会执行哪些动作？

2、MySQL 是如何恢复到某一天的某一秒的状态？

先来看看一条读操作 SQL 的查询过程：

连接器

客户端在提交 SQL 语句之前，你需要先连接上数据库，也就是说要提供用户名密码登陆，这便是连接器发挥作用的时候。

连接上去后，MySQL 就创建了一个连接对象放在了内存中，连接对象里有用户的相关权限信息，此时如果管理员修改了用户权限，只要用户不退出重新连接，就不会被影响。

内存资源是比较昂贵的，不用的话就要被清理。如果不做任何操作，在一定的时间之后（默认是 8 小时），连接器会自动断开，此时再查询就会报错。

一个比较好的方案是使用数据库连接池。Python 编程可以使用第三方库 DBUtils 来管理数据库连接池。

查询缓存

缓存可以快速返回命中的查询，在使用上的感受就是同一个 SQL，第二次查询时结果是立刻显示的。查询缓存中以 SQL 语句作为 KEY，查询结果作为 VALUE。

如果你的查询能够直接在这个缓存中找到 key，并且具有对该表的相应的权限，那么这个 value 就会被直接返回给客户端。

如果没有找到，会走接下来流程，一旦查到结果，结果还是会保存在查询缓存中。

分析器

如果没有命中查询缓存，SQL 语句就会传给分析器进行词法分析，分析是否有语法错误，解析中表名，字段名等等，其实不仅仅数据库有分析器，很多开源的工具也有分析 SQL 的功能，比如 Python 可以使用 python-sqlparse，JAVA 可以使用 druid(阿里巴巴开源)。

解析出表名之后，检查一下用户对表的权限，如果权限符合就进行下一步优化器。

优化器

经过了分析器，MySQL 就知道你要做什么了。

在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。

也许你会问，权限验证前面不是已经做了吗？为什么这里还要进行权限验证，因为除了sql 还可能有存储引擎，触发器等，在这些对象中，也可能需要调用其它表去获取数据，也需要权限验证，前面的阶段对于触发器，存储引擎这种对象的执行是做不到的。

比如说：

select * from T where ID=10;

如果 ID 字段没有索引，那么执行器的执行流程是这样的：调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

说到存储引擎，MySQl 支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同。

接下来，看一看写操作的执行过程，其中 redo log 和 binlog 又起到了什么作用？

写操作

首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是redo log（重做日志）和 binlog（归档日志）。如果接触 MySQL，那这两个词肯定是绕不过的，redo log 和 binlog 在设计上有很多有意思的地方，这些设计思路也可以用到你自己的程序里。

以更新操作为例，假如 SQL 语句为：

update table_a set count = count + 1 where id = 2

执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行。如果 id=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

这里得说明一下，redo log 和 binlog 都是日志文件，为了防止异常重启、掉电、恢复数据等场景，这些日志文件都会持久化到磁盘上。为了防止频繁的访问磁盘，写 redo log 前会先写到内存中的 redo log buffer，再定期一起写到磁盘。

但是这两个 log 文件又有所区别：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 id=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log 用于回滚，binlog 用于恢复。

如果将 MySQL 恢复到某一天的某一秒

要做到这一点有个前提，就是要对 MySQL 数据库定期做整库备份。这里的定期取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。这样你的临时库就跟误删之前的线上库一样了。
最后，你可以把表数据从临时库取出来，按需要恢复到线上库去。

为什么要两阶段提交

前面写操作中的提到，写磁盘前先写 redo log，此时 redo log 状态为 prepare，然后再写 binlog，写完 binlog 后，再提交，redo log 才处于 commit 状态。

为什么要等 binlog 写完才能提交呢？这是因为假如 binlog 没写完就提交，此时如果异常重启，那么 binlog 就没有这条记录，在后续的主从复制时，将该 binlog 重放之后，从库的数据与主库的数据就产生了不一致。

如果先写 binlog，再写 redo log，假如写完 binlog 系统异常重启，那么重启恢复后由于 redo log 还没有写，因此事务回滚，但是由于 binlog 已经成功写入，在后续的主从复制后仍然导致主从不一致。

MySQL 如何回滚与恢复数据的？

前面提到 InnoDB 有个日志文件叫 redo log，就可以持久化存在磁盘上的，但是在内存中也有一份对应的缓冲区，叫 redo log buffer，为了应对异常重启，InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

也就是 redo log buffer -> page cache -> 磁盘 这一过程，每秒都在进行，一旦发生异常重启，从 redo log 中恢复就可以了。那具体是怎么恢复的呢？

事务提交之前，先写入 redo log，状态是 prepare，表示已经准备好了，随时可以提交。

事务提交之后，redo log 对应的状态是 commit，表示已经提交。

如果是 prepare 时发生异常重启，mysql 在恢复后对状态为 prepare 状态的事务进行回滚。

如果是 commit 状态，表示本来已经写完了，重启也没关系。

如果是 prepare 之前崩溃了，也无所谓，本来就没有开始写数据，重启也没有任何损失。

现在有了 redo log，只能保证数据不丢，但还无法保证数据可以恢复到之前的某一时刻的状态。

这就需要 binlog，binlog 是 mysql 自带的归档日志。

假如在写 binlog 前异常重启，mysql 在恢复后对状态为 prepare 状态的事务进行回滚。

假如在写 binlog 后异常重启，则判断对应的事务 binlog 是否存在并完整：

a. 如果是，则提交事务；b. 否则，回滚事务。

你可能会问，处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?

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

还有一个问题，就是为什么不让 redo log 也承担 binlog 的功能？

这是因为，redo log 是循环写的，写完后会从开头继续写，这样 redo log 就无法记录一段时间内的完整操作，这样历史日志没法保留，redo log 也就起不到归档的作用。

另一个原因就是就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。

最后的话

MySQL 的奥妙就在于 redo log 和 binlog 的完美配合，这样的模式保证了系统可以应对异常重启，也保证了数据可以恢复到某一天的任意一秒的状态，当然这是在有完整备份的前提下，其实这样的设计可以迁移到平时软件设计上，比如说涉及用户输入的系统，在发生异常重启、掉电的情况下，如何让用户的输入不丢失，系统的配置文件比较复杂被改乱了，如何快速恢复到某一天之前的配置状态等。

此外 MySQL 是面试必问，找工作的同学建议多准备下，《MySQL实战45讲》我已经全部学完，对 MySQL 的原理了解得更加深入，课程质量高，学起来不累，推荐给你，可以扫下方二维码直接购买，加个好友，和我一起学习 MySQL 吧。

如有问题欢迎留言讨论。