通过一条更新SQL语句看MYSQL的运行机制

前言：本文环境是Mysql5.7+ 版本。基于MYSQL的Innodb引擎讲解。

了解MYSQL的查询语句运行机制请查看我的另一篇博文 《通过一条查询SQL语句看MYSQL的运行机制》

---

1.MYSQL读与写异同点

MYSQL的读过程分为 创建连接 —> 查询缓存 —> 解析器 —> 预处理器 —> 查询优化器 —> 查询执行器 —> 返回结果 几个过程

MYSQL的写过程也会走上面几个流程， 创建连接 —> 缓存 —> 解析器 —> 预处理器 —> 优化器 —> 执行器 —> 返回结果 与读不同的是，写流程会清空查询缓存并且因为涉及到事务，所以还需要记录日志。Undo Log / Redo Log / Binlog

下面会说到 Undo Log / Redo Log / Binlog 日志的作用

---

2.执行更新操作清空查询缓存

执行 UPDATE / DELETE 操作真的会清空这个表所有的查询缓存结果吗，我们一起来验证一下。

开始验证之前，先看一下MYSQL缓存状态。

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

mysql> show status like 'Qcache%'; 
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

现在有一张用户表user，其中数据如下：

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | php  |
+----+------+
2 rows in set (0.00 sec)

执行一条查询语句 SELECT id, name FROM user WHERE id = 1;，让其结果被添加到查询缓存中

mysql> SELECT id, name FROM user WHERE id = 1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1029272 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 2       |	// 对比执行查询语句前，查询缓存中记录数更新为2了（等于2是因为执行了两个不一样的SELECT语句）
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 2       |
| Qcache_total_blocks     | 6       |
+-------------------------+---------+
8 rows in set (0.00 sec)

查询缓存中已经缓存了select * from user; 和 SELECT id, name FROM user WHERE id = 1;

这两个SELECT语句的结果，现在重复执行上面两天语句中任意一条，观察查询缓存命中情况。

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | php  |
+----+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1029272 |
| Qcache_hits             | 1       |	// 查询缓存名字次数+1
| Qcache_inserts          | 2       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 2       |
| Qcache_total_blocks     | 6       |
+-------------------------+---------+
8 rows in set (0.00 sec)

以上，经过测试可以验证重复执行相同的SELECT语句会准确命中查询缓存。

那么，我们接着验证当执行了对用户表的更新语句后是否查询缓存还能命中呢？

mysql> update user set name = 'html' where id = 2;
Query OK, 1 row affected (1.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 2       |	// 发现执行完UPDATE操作后，查询缓存中缓存的记录还是2
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | html |
+----+------+
2 rows in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |	// 重新执行重复查询语句后，查询缓存命中数还是1没有变化。说明本次查询没有走查询缓存
| Qcache_inserts          | 3       |	// 缓存记录数+1也可以间接看出来本次查询的结果再次放入到了查询缓存中
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

再次重复执行相同的SELECT语句 select * from user; 观察到缓存命中数 Qcache_hits 没有变化，缓存记录数 Qcache_inserts +1这种现象，可以确认是当执行完 update user set name = 'html' where id = 2; 更新语句后，第一次执行 select * from user; 的查询缓存结果被清除掉了。

还记得最开始查询缓存了两条SQL的查询结果吗，一条是 select * from user;，一条是 SELECT id, name FROM user WHERE id = 1; 。既然执行更新操作清空了 select * from user; 缓存结果，是否也清空了第二条SELECT的缓存结果呢？

再一次执行 SELECT id, name FROM user WHERE id = 1;

mysql> SELECT id, name FROM user WHERE id = 1;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1029272 |
| Qcache_hits             | 1       |	// 看到缓存命中数依然是1没有变化
| Qcache_inserts          | 4       |	// 缓存结果数又+1了
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 2       |
| Qcache_total_blocks     | 6       |
+-------------------------+---------+
8 rows in set (0.00 sec)

又观察到缓存命中数 Qcache_hits 没有变化，缓存记录数 Qcache_inserts +1这种现象，可以确认是当执行完 update user set name = 'html' where id = 2; 更新语句后，user用户表上的两条缓存结果均被清空了，由此，我们可以得出结论：在执行写操作过程中，会将该表上的所有查询缓存清空。

疑问？DELETE语句是否也会清空查询缓存呢，朋友们可以自己试一试。

---

2.Undo Log

撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。
撤消日志属于逻辑日志，根据每行记录进行记录。
撤消日志存在于系统表空间、撤消表空间和临时表空间中。

数据库事务开始时产生，将要更改的数据放到Undo日志里，当事务回滚或者数据库崩溃时，利用Undo日志撤销未提交事务对数据库产生的影响。

数据库事务提交时，并不会立即删除Undo日志。Innodb会将该事务对应的Undo日志放到删除列表中，然后后台线程Purge Thread会进行回收处理。

Undo Log作用

• 实现事务的原子性
  Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

• 多版本并发控制（MVCC）
  Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

---

3.Redo Log

属于Innodb引擎所特有的日志。被称为重做日志。
将事务中修改之后的数据记录到 Redo Log 中
以恢复操作为目的，在数据库发生意外重现操作。

随着数据库事务操作的进行，就会生成 Redo Log。事务提交时会将 Redo Log 写入到 Log Buffer，等到事务操作的脏页写入到磁盘后，Redo Log 所占用的空间就会被重用（覆盖写入）。

Redo Log作用

• 实现事务的持久性
  Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启MySQL服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo Log存储

• 每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组至少有2个重做日志文 件，默认为ib_logfile0和ib_logfile1。

• 可以通过下面一组参数控制Redo Log存储:

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
7 rows in set (0.00 sec)

Redo Buffer 持久化到 Redo Log 策略配置：

mysql> show variables like '%Innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.01 sec)

从 Redo Buffer 持久化到 Redo Log 的策略可选值有：

Mysql参数配置名称	参数值	参数含义
Innodb_flush_log_at_trx_commit	0	每秒提交。由后台Master线程每隔一秒将日志缓存区数据刷入磁盘
Innodb_flush_log_at_trx_commit	1	每次事务提交都将Redo Buffer数据刷入磁盘。最安全性能最差方式。（默认）
Innodb_flush_log_at_trx_commit	2	每次事务提交都将Redo Buffer数据写入到操作系统缓存，由后台Master线程再每隔一秒将操作系统缓存区数据刷入磁盘（建议配置）

---

4.Binlog

Redo Log 是Innodb引擎所特有的日志，Binlog是Mysql Server自己的日志。

它记录的是表结构及表数据变更的二进制日志。

任何一次变更操作都是以事件方式记录。包含事件类型，操作事件，执行的SQL等等

事务提交阶段写入Binlog日志。不同事务以串行方式将log event写入binlog文件中，一个事务包含的log event信息在binlog文件中是连续的。

开启Binlog日志有两个最重要的应用场景：

• 主从复制
• 数据恢复

Redo Log 与 Binlog 区别：

• Redo Log 属于Innodb引擎日志，Binlog是Mysql Server自带日志
• Redo Log 记录状态，Binlog记录过程
• Redo Log 日志是循环写，日志空间大小固定，Binlog 追加写入
• Redo Log 在服务器异常宕机重启后事务数据自动恢复使用，Binlog 作为主从复制和数据恢复使用，没有crash-safe能力