MySQL学习— SQL 语句是如何执行的

1.1 MySQL 逻辑架构图

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

1.1.1 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
连接器执行流程：
1. 连接到服务器，输入连接命令、账号密码。
2. 连接器认证身份，连接器会到权限表查询账户权限，之后的权限判断都依赖于此时读取到的权限（意味着连接之后修改权限不会影响已经存在的连接）。
3. 连接完成后，没有后续动作，这个连接就处于空闲状态。
4. 空闲时间超过参数 wait_timeout，自动断开连接，默认是8小时。

长连接：连接成功后，如果客户端持续有请求，则一致使用同一个连接。

短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放，长连接累计下来可能会导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

解决方案：

定期断开长连接。使用一段时间或在程序里判断执行过一个占用内存的大查询后，断开连接，之后查询再重连。
执行 mysql_reset_connection。如果使用的是MySQL 5.7或更新版本，可以每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。

1.1.2 查询缓存

查询缓存执行流程：

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果，如果能直接在这个缓存中找到 key，那么这个 value 会被直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。

查询缓存往往弊大于利：

查询缓存的失效非常频繁。只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
对于更新压力大的数据库来说，查询缓存的命中率非常低。只有静态表，很久才会更新一次的才适合使用查询缓存。

MySQL 提供了“按需使用的方式”。可以将参数 query_cache_type 设置成 DEMAND，默认的 SQL 语句都不使用查询缓存，需要使用的语句可以使用 SQL_CACHE 显示指定，如下：

select SQL_CACHE * from T where ID=10；

注：从 MySQL 8.0版本开始，彻底将查询缓存的整块功能删除了。

1.2 MySQL 查询语句执行流程

首先通过连接器连接到服务器，输入连接命令、账号密码，连接器进行身份认证、权限查询，通过后完成连接。
然后到查询缓存查看之前是否执行过相关语句，如果命中缓存 key 就直接将相应的 value 返回，否则就继续执行以下的执行流程。
接着分析器会做词法和语法分析，判断出字符串代表什么，根据语法规则判断 SQL 语句是否满足 MySQL 语法，是否有输出错误。
然后优化器决定使用哪个索引和各个表的连接顺序（如果有多表 join 的话）。
最后执行器开始执行时，先判断操作用户对表是否有执行权限，若无则返回无权限错误，若有则打开表继续执行。打开表时会根据表的引擎定义使用引擎提供的接口。
最后返回查询结果。

1.3 MySQL 更新语句执行流程

更新语句执行和查询语句基本相同，以下为简化版：

通过连接器连接数据库。
将更新的表的所有查询缓存清空。
分析器通过词法和语法知道这是一条更新语句。
优化器决定使用索引。
执行器执行更新。

与查询流程不同的地方在于，更新流程涉及 redo log 和 binlog。

1.3.1 物理日志 redo log

redo log 是 InnoDB 引擎特有的日志，也就是引擎层的日志。redo log 使用 WAL 技术进行数据记录的更新。

MySQL 的 WAL（Write-Ahead Logging）技术：关键点就是先写日志，再写磁盘。

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

1.3.2 逻辑日志 binlog

binlog 是 Server 层的日志。

binlog 的两种模式：

statement 格式，binlog 在这种模式会记录 sql 语句。
row 格式，记录行的内容，记录两条，一条更新前的数据，一条更新后的数据。

binlog 会记录所有的逻辑操作，并且采用“追加写”的形式，备份系统会保存一定时间内的所有 binlog，同时系统也会定期做整库备份。

如何恢复数据到指定的某一秒？执行操作如下：

首先，找到最近的一次全量备份，从这个备份恢复到临时库。
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到指定的时刻。

需要恢复数据的场景：

误删大量数据。
对数据库进行扩容，也就是需要再多搭建一些备库来增加系统的读能力，这种也是通过全量备份加上应用 binlog 来实现的。

1.3.3 更新语句如何 redo log 和 binlog

redo log 和 binlog 的三点不同：

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

执行器和 InnoDB 引擎在执行更新语句时的内部流程：

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

例子：

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

下图为这条 update 语句的执行流程，图中浅色框表示在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

1.3.4 两阶段提交

如上图所示，事务的状态分为 prepare 阶段和 commit 阶段。这就涉及了两阶段提交。

两阶段提交是为了让两份日志之间的逻辑一致。
如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

例子：

以上面的 update 语句为例，如果不用两阶段提交会发生什么？

先写 redo log 后写 binlog。
- 假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于 redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
- 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
- 然后会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。
- 如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1 ”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

1.3.5 日志参数设置

innodb_flush_log_at_trx_commit：这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog：这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数建议设置成 1 ，这样可以保证 MySQL 异常重启之后 binlog 不丢失。