今天大多数程序需要处理的数据都来自于数据库，尤其是关系型数据库。一条SQL提交到数据库后，数据库做了什么？如果不了解这些问题，就无法更好地使用数据库，也无法很好地回答面试官的问题。现在流行的开源数据库非MySQL莫属。mysql也是面试必问的，所以研究了一下专栏《MySQL实战45讲》。今天的文章试图回答以下两个问题：1、一条SQL语句提交到数据库后，数据库会执行哪些动作？2、MySQL如何恢复到某一天某一秒的状态？首先我们来看一个读操作SQL的查询过程：在连接器客户端提交SQL语句之前，需要先连接数据库。也就是说，提供用户名和密码登录，这个时候connector就派上用场了。连接后，MySQL创建一个连接对象，并将其放入内存中。连接对象包含用户的相关权限信息。此时如果管理员修改了用户权限，只要用户不注销重新连接，就不会受到影响。内存资源是比较昂贵的，如果不用就必须清理掉。如果什么都不做，一定时间后（默认8小时），连接器会自动断开，此时再查询会报错。更好的解决方案是使用数据库连接池。Python编程可以使用第三方库DBUtils来管理数据库连接池。querycache可以快速返回命中的query，使用的感觉就是同一条sql，第二次查询的结果马上显示出来。在查询缓存中，SQL语句作为KEY，查询结果作为VALUE。如果你的查询可以直接在这个缓存中找到key，并且你对该表有相应的权限，那么这个value就会直接返回给客户端。如果没有找到，则进入下一个过程。一旦找到结果，结果仍然会保存在查询缓存中。如果分析器没有命中查询缓存，会将SQL语句传递给分析器进行词法分析，分析分析中是否存在语法错误、表名、字段名等。其实不仅数据库有分析器，很多开源工具也有分析SQL的功能，比如Python可以用python-sqlparse，JAVA可以用druid（阿里巴巴开源）。解析完表名后，检查用户对该表的权限，如果权限匹配，则进入优化器的下一步。优化器通过了分析器，MySQL知道你要做什么。在开始执行之前，必须经过优化器的处理。当表中有多个索引时，优化器决定使用哪个索引；或者当一个语句有多个表关联（join）时，决定每个表的连接顺序。执行者MySQL通过分析器知道你要做什么，通过优化器知道你要做什么，于是进入执行者阶段，开始执行语句。在开始执行的时候，首先要判断自己是否有对这张表T执行查询的权限，如果没有，会返回没有权限的错误。你可能会问，之前不是做过权限验证吗？这里为什么要进行权限验证，因为除了SQL之外，可能还有存储引擎、触发器等，在这些对象中，还可能需要调用其他表来获取数据。还需要权限验证。在前一阶段，触发器、存储引擎等对象的执行是不可能的。例如：select*fromTwhereID=10;如果id字段没有索引，那么executor的执行流程如下：调用InnoDB引擎接口取这张表的第一行，判断id值是否为10，不是则跳过，如果是就是，将这一行存入结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到表的最后一行。执行器将上述遍历过程中满足条件的所有行组成的记录集作为结果集返回给客户端。至此，这条语句执行完毕。对于有索引的表，执行逻辑类似。首先调用的是“获取满足条件的第一行”接口，然后循环获取“满足条件的下一行”接口。这些接口已经在引擎中定义。说到存储引擎，MySQl支持InnoDB、MyISAM、Memory等多种存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5开始成为了默认的存储引擎。也就是说，当你执行createtable建表时，如果不指定引擎类型，默认使用InnoDB。但是也可以通过指定存储引擎的类型来选择其他引擎，比如在createtable语句中使用engine=memory来指定使用内存引擎建表。不同的存储引擎有不同的表数据访问方式，支持不同的功能。接下来我们看一下写操作的执行过程。redolog和binlog的作用是什么？首先，可以肯定地说，update语句和query语句会经过同一套过程。与查询过程不同，更新过程还涉及到两个重要的日志模块，分别是redolog（重做日志）和binlog（归档日志）。如果你接触过MySQL，这两个词肯定是绕不开的。redolog和binlog有很多有趣的设计点，这些设计思路也可以用在自己的程序中。以update操作为例，如果SQL语句为：updatetable_asetcount=count+1whereid=2，executor首先查找引擎去取id=2的行。id是主键，引擎直接使用树搜索找到这一行。如果rowid=2所在的数据页已经在内存中，则直接返回给executor；否则需要先从磁盘读入内存，再返回。executor获取引擎给的行数据，将这个值加1，比如之前是N，现在是N+1获取新的一行数据，然后调用引擎接口写入新的数据行。引擎将一行新数据更新到内存中，并将更新操作记录在redolog中，redolog处于prepare状态。然后通知执行者执行完成，可以随时提交交易。executor生成本次操作的binlog，并将binlog写入磁盘。执行器调用引擎的提交事务接口，引擎将刚刚写入的重做日志更改为提交状态，更新完成。这里要说明一下，redolog和binlog都是日志文件。为了防止异常重启、掉电、数据恢复等场景，这些日志文件会持久化到磁盘中。为了防止频繁访问磁盘，在写入redolog之前，会先写入内存中的redologbuffer，然后每隔一定时间写入磁盘。但是这两个日志文件是不同的：redolog是InnoDB引擎特有的；binlog由MySQL服务器层实现，所有引擎都可以使用。Redolog是物理日志，记录的是“对某个数据页做了什么修改”；binlog是逻辑日志，记录了这条语句的原始逻辑，比如“给id=2行的c字段加1”。redolog是循环写入的，空间总会被用完；可以附加二进制日志。“追加写入”是指binlog文件写入到一定大小后，会切换到下一个，不会覆盖之前的日志。Redolog用于回滚，binlog用于恢复。如果要将MySQL恢复到某一天的某一秒，这样做是有一个前提的，那就是必须定期备份整个MySQL数据库。这里的规律性取决于系统的重要性，可以是一天，也可以是一周。当你需要恢复到指定的秒数时，比如某天下午2点，你发现中午12点不小心删除了一张表，你需要找回数据，这时你可以这样做：首先，找到最新的完整备份，如果你运气好的话，可能是昨晚的备份，从这个备份恢复到临时库；然后从备份的时间点开始，把备份的binlog一个一个的取出来，重放到中午不小心删表前的那一刻。这样，您的临时图书馆将与意外删除前的在线图书馆相同。最后，可以将表数据从临时库中取出，根据需要恢复到在线库中。为什么需要分两个阶段提交？前面的写操作中提到，在写入磁盘之前先写入redolog。此时redolog的状态为prepare，然后写入binlog。写好binlog后，再次提交，redolog处于commit状态。为什么要等binlog写完了再提交？这是因为如果binlog还没写完就提交了，如果这个时候异常重启，那么binlog是不会有这条记录的。在后续的主从复制中，重放binlog后，从库中的数据与主库中的数据不一致。如果先写binlog，再写redolog，如果写完binlog后系统异常重启，重启恢复后事务会回滚，因为redolog还没有写，但是binlog已经写了写入成功，后续主从复制后仍会继续。导致主从不一致。MySQL如何回滚和恢复数据？前面提到，InnoDB有一个日志文件叫redolog，可以持久化到磁盘上，但是在内存中也有一个相应的缓冲区，叫redologbuffer，为了处理异常重启，InnoDB有一个后台线程，每1秒后，会调用write将redologbuffer中的log写入文件系统的pagecache，然后调用fsync持久化到磁盘。即redologbuffer->pagecache->disk这个过程每秒都会进行一次。一旦出现异常重启，从redolog中恢复就可以了。具体是怎么恢复的？事务提交前，先写入redolog，状态为prepare，表示已经准备好，可以随时提交。事务提交后，redolog对应的状态为commit，表示已经提交。如果prepare过程中出现异常重启，mysql恢复后会回滚prepare状态的事务。如果是commit状态，说明已经写好了，重启也无所谓。如果在prepare之前就crash了，没关系，没有开始写数据，重启的时候也没有丢失。现在有了redolog，只能保证数据不丢失，不能保证数据能恢复到之前某个时刻的状态。这个需要binlog，也就是mysql自带的归档日志。如果在写入binlog之前异常重启，mysql恢复后会回滚prepare状态的事务。如果写入binlog后异常重启binlog，则判断对应的事务binlog是否存在且是否完整：a．如果是，提交事务；b.否则，回滚事务。你可能会问，prepare阶段的redolog加上完整的binlog，重启后还能恢复吗？为什么MySQL要这样设计？答：MySQL写入binlog后crash。此时binlog已经写入，将从library（或者从这个binlog恢复的library）中删除。所以这个事务也必须在主库上提交。通过这种策略，保证了主库和备库的数据一致性。还有一个问题，为什么不让redolog也承担binlog的功能呢？这是因为redolog是循环写入的，写入后会从头继续写入，导致redolog在一段时间内无法记录完整的操作。这样就无法保留历史日志，重做日志也无法起到归档的作用。另一个原因是MySQL系统依赖binlog。binlog作为MySQL一开始的一个功能，在很多地方都用到了。其中，MySQL系统高可用的基础是binlog复制。还有很多公司的系统是异构的（比如一些数据分析系统），这些系统都是通过消费MySQL的binlog来更新自己的数据。如果关闭binlog，这些下游系统将无法输入。最后，MySQL的奥秘在于redolog和binlog的完美配合。这种模式保证了系统能够应对异常重启，也保证了数据能够恢复到某一天任意一秒的状态。当然，这是在有完整备份的前提下，其实这样的设计是可以移植到平时的软件设计中的。比如涉及到用户输入的系统，在异常重启或者掉电的情况下，如何防止用户的输入丢失？乱七八糟，如何快速恢复到某一天之前的配置状态等等。另外，MySQL是面试必问的问题。建议正在找工作的同学多做准备。《MySQL实战45讲》全部学会了，对MySQL的原理有了更深的理解。直接扫码购买，加好友，跟我学MySQL。本文转载自微信?“Python7号”，可通过以下二维码关注。转载本文请联系Python七号公众号。
