文章基于林晓斌的实战45讲的一些理解:
我们创建了一个简单的表 t,并插入一行,然后对这一行做修改。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL primary key auto_increment,
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1,2);
这时候,表 t 里有唯一的一行数据 (1,2)。假设,我现在要执行:
mysql> update t set a=2 where id=1;
你会看到这样的结果:
mysql> update t set a=2 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
结果显示,匹配 (rows matched) 了一行,修改 (Changed) 了 0 行
仅从现象上看,MySQL 内部在处理这个命令的时候,可以有以下三种选择:
1、更新都是先读后写的,MySQL 读出数据,发现 a 的值本来就是 2,不更新,直接返回,执行结束;
2、MySQL 调用了 InnoDB 引擎提供的“修改为 (1,2)”这个接口,但是引擎发现值与原来相同,不更新,直接返回;
3、InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作,该加锁的加锁,该更新的更新。
你觉得实际情况会是以上哪种呢?你可否用构造实验的方式,来证明你的结论?进一步地,可以思考一下,MySQL 为什么要选择这种策略呢?
解答:
一:binlog_format=statement的情况:
当 MySQL 去更新一行,但是要修改的值跟原来的值是相同的,这时候 MySQL 会真的去执行一次修改吗?还是看到值相同就直接返回呢?
这是第一次我们课后问题的三个选项都有同学选的,所以我要和你需要详细说明一下。
第一个选项是,MySQL 读出数据,发现值与原来相同,不更新,直接返回,执行结束。这里我们可以用一个锁实验来确认。
假设,当前表 t 里的值是 (1,2)。
图 12 锁验证方式
session B 的 update 语句被 blocked 了,加锁这个动作是 InnoDB 才能做的,所以排除选项 1。
第二个选项是,MySQL 调用了 InnoDB 引擎提供的接口,但是引擎发现值与原来相同,不更新,直接返回。有没有这种可能呢?这里我用一个可见性实验来确认。
假设当前表里的值是 (1,2)。
图 13 可见性验证方式
session A 的第二个 select 语句是一致性读(快照读),它是不能看见 session B 的更新的。
现在它返回的是 (1,3),表示它看见了某个新的版本,这个版本只能是 session A 自己的 update 语句做更新的时候生成。(如果你对这个逻辑有疑惑的话,可以回顾下第 8 篇文章《事务到底是隔离的还是不隔离的?》中的相关内容)
所以,我们上期思考题的答案应该是选项 3,即:InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作,该加锁的加锁,该更新的更新。
然后你会说,MySQL 怎么这么笨,就不会更新前判断一下值是不是相同吗?如果判断一下,不就不用浪费 InnoDB 操作,多去更新一次了?
其实 MySQL 是确认了的。只是在这个语句里面,MySQL 认为读出来的值,只有一个确定的 (id=1), 而要写的是 (a=3),只从这两个信息是看不出来“不需要修改”的。
作为验证,你可以看一下下面这个例子。
图 14 可见性验证方式 -- 对照
二:binlog_format=row并且 binlog_row_image=FULL的情况:
时刻 1 session 1 ,开启一个事务,然后查询表的数据,
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
1 row in set (0.00 sec)
时刻2 session 2,更新a=3
mysql> update t set a=3 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
时刻3 回到session1,同样更新a=3, #注意此时这里的这个update不记录到binlog中
mysql> update t set a=3 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
时刻 4紧接着查询数据!发现依旧是2,没查询到session2更新的数据!
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
1 row in set (0.00 sec)
时刻5 回到session 2,继续执行update,发现处于blocked状态,因为session1的update还没有更新!
mysql> update t set a=46 where id=1;
blocked。。。。。
三:binlog_format=row并且 binlog_row_image=minimal的情况:
时刻1 session 1
mysql> set binlog_row_image ='minimal';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
1 row in set (0.00 sec)
时刻 2 session 2
mysql> update t set a=3 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
时刻3 ,回到session 1,更新a=3,#注意此时这里的这个update也不记录到binlog中
mysql> update t set a=3 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
时刻4,再查看发现能查看a=3,
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 3 |
+----+------+
1 row in set (0.00 sec)
说明:如果是 binlog_format=row 并且 binlog_row_image=FULL 的时候,由于 MySQL 需要在 binlog 里面记录所有的字段,所以在读数据的时候就会把所有数据都读出来了。这样就能判断是否是一样的了,进而也就能判断是否需要再
更新了!
根据上面说的规则,“既然读了数据,就会判断”, 因此在这时候,select * from t where id=1,结果就是“返回 (1,2)”。
同理,如果是 binlog_row_image=NOBLOB, 会读出除 blob 外的所有字段,在我们这个例子里,结果还是“返回 (1,2)”。
对应的代码如图 15 所示。这是 MySQL 5.6 版本引入的,在此之前我没有看过。所以,特此说明。
图 15 binlog_row_image=FULL 读字段逻辑
类似的 如果涉及到timestamp 字段的问题。结论是:如果表中有 timestamp 字段而且设置了自动更新的话,那么更新“别的字段”的时候,MySQL 会读入所有涉及的字段,这样通过判断,就会发现不需要修改。
关于binlog中内容,如果某个update影响的行数为0,那么binlog中就不记录!因为对数据库没有做任何修改,不影响主从同步一致性,记录这个数据反而对数据库带来没必要的负担!
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+------+
| id | a |
+----+------+
| 1 | 45 |
+----+------+
1 row in set (0.00 sec)
mysql> update t set a=45 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
具体binglog内容如下,没有发现,确实没有记录这个更新操作!
[root@t1-28-88 data]# vim 10
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220204 18:05:12 server id 1383306 end_log_pos 123 CRC32 0xbee70783 Start: binlog v 4, server v 5.7.28-log created 220204 18:05:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
WPr8YQ+KGxUAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYMH574=
'/*!*/;
# at 123
#220204 18:05:12 server id 1383306 end_log_pos 194 CRC32 0xf904aaa1 Previous-GTIDs
# 69f8d757-e53a-11eb-8280-005056bdc6db:3344-104296
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
~
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2854683/,如需转载,请注明出处,否则将追究法律责任。