一条 SQL 语句是如何执行的

SQL查询语句

select * from user where ID=10;

MySQL 的基本架构可以分为 Server 层和存储引擎两部分。Server 层又包含连接器、（查询缓存）、分析器、优化器和执行器。

连接器：连接器负责和客户端建立连接、获取权限、维持和管理连接。

查询缓存：建立连接后可以执行 SELECT 语句，执行逻辑来到第二步查询缓存。

MySQL 拿到查询请求后，会先到查询缓存中查看，是不是之前执行过的语句。查询缓存的存储形式是 key-value 形式，key是查询语句，value是查询结果。如果在缓存中则直接返回，不在缓存中则执行后续步骤，将结果放到缓存中。但不建议使用查询缓存，因为只要有对一个表的更新，这个表上的所有查询缓存都会被清空，因此很大的可能是把结果缓存起来之后，还没来的及用，就被清空了，利大于弊。MySQL8.0 彻底删除了查询缓存。

分析器：先进行词法分析在进行语法分析。

• 词法分析：MySQl 要识别出你输入的这条语句里的字符串分别是什么，代表什么。通过 “select” 关键字识别这是一条查询语句，通过 “user” 识别成 “表名 user”。
• 语法分析：语法分析器会根据 MySQL 的语法规则判断你输入的 SQL 语句是否符合 MySQL 的语法规范。

优化器：优化器负责当表里有多个索引的时候决定用哪一个，当语句中有多表关联时选择连接顺序

执行器：判断对要查询的表是否有执行查询的权限，没有返回权限错误，有权限则打开表，执行器根据表的引擎定义，去使用这个引擎提供的接口。

SQL 更新语句

update T set c=c+1 where ID=10;

查询语句走的流程更新语句也会走一遍，不再赘述。不同的是，更新语句执行的过程中会涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

redo log

redo log 可以使 InnoDB保证即时数据库发生异常重启，之前的记录都不会丢失（crash-safe）

如果每次更新操作都立刻写入磁盘，然后磁盘找到数据进行修改整个过程 IO 成本、查找成本都很高，为了解决这个问题 MySQL 应用了 WAL（Write-Ahead Logging 写前日志）先写日志在写磁盘。

具体来说，当有一条更新记录来的时候，InnoDB 会先把记录写到 redo log里，并更新内存，等到闲时再把操作写回磁盘。

redo log 是固定大小的。从头开始写，写到末尾就又从头开始写。

write pos：当前记录的位置，边写边后移，写到末尾则从头开始。

checkpoint：当前要擦除的位置，也是向后推并循环的。

write pos 和 checkpoint 之间空着的部分，可以用来记录新的操作，当 write pos 追上 checkpoint 重合时，这时就不能继续执行更新操作了，要停下来擦除一些记录，将 checkpoint 向后推。

binlog

redo log 是InnoDB 引擎特有的日志，binlog 是 MySQL 的日志，没有 crash-safe。

两种日志的不同点：

1. redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的，所有的引擎都可以用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑如“给 ID=10 这一行的 c 字段加 1”。
3. redo log 是循环写的，binlog 是追加写的，写满了不会覆盖，切换到下一个继续写。

update 语句的内部流程

update T set c=c+1 where ID=10;

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

两阶段提交

假设要把c=0更新成c=1

1. 先写 redo log 再写 binlog

   假设 redo log 写完了 binlog 还没写，这时候数据库挂了，重启后进行恢复，这时候通过redo log恢复后 c仍然是1。

   但binlog并没有写完就crash了，因此binlog里并没有这条语句，日后在用binlog进行恢复临时库时就会出现主从库不一致的现象。

2. 先写binlog 再写 redo log

   如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日 志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是 1，与原库的值不同。

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