实验前提

一个主从复制的环境 ：主库A、从库B   ROW格式binlog的主备同步

主库A
测试表字段为 id ,a,b,c。 id是主键，a,b,c类型为int，测试表中预插入了一些数据。

create table table_t(id int primary key,a int ,b int ,c int);

mysql> select * from table_t;
+----+------+------+------+
| id | b | c | a |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
+----+------+------+------+

场景一

主从的slave_rows_search_algorithms参数值均为

slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN

从库上做了一个ddl alter table table_t modify a int after c;

从库B如下操作
mysql> alter table table_t modify  a int after c;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from table_t;
+----+------+------+------+
| id | b    | c    | a    |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
|  2 |    2 |    2 |    2 |
+----+------+------+------+
2 rows in set (0.00 sec)

这时在主库上执行 update table_t set b=999 where a=1;

mysql> update table_t set b=999 where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from table_t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |  999 |    1 |
|  2 |    2 |    2 |    2 |
+----+------+------+------+
2 rows in set (0.00 sec)

请问：在从库上 select * from table_t 结果是啥，或者说从库上id=1 这行 a,b,c分别变成了啥？

从库结果如下：

mysql> select * from table_t;
+----+------+------+------+
| id | b    | c    | a    |
+----+------+------+------+
|  1 |    1 |  999 |    1 |
|  2 |    2 |    2 |    2 |
+----+------+------+------+
2 rows in set (0.00 sec)

结论：

解析binlog

发现binlog记录的内容并没有列信息。

解析
主库
### UPDATE `test`.`table_t`
### WHERE
###   @1=1
###   @2=1
###   @3=1
###   @4=1
### SET
###   @1=1
###   @2=1
###   @3=999
###   @4=1


从库
### UPDATE `test`.`table_t`
### WHERE
###   @1=1
###   @2=1
###   @3=1
###   @4=1
### SET
###   @1=1
###   @2=1
###   @3=999
###   @4=1

binlog回放的原则 就是按顺序去修改 按主键去匹配.

场景二

主从的slave_rows_search_algorithms参数值均为

slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN

在从库上执行：update table_t set a=null where id=2;
在主库上执行：update table_t set b=9999 where a=2;

请问：在从库上 select * from ta结果是啥，或者说从库上id=3 这行 a,b,c分别变成了啥？

主库

mysql> select * from table_t;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 999 | 1 |
| 2 | 2 | 9999 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)

从库

mysql> select * from table_t;
+----+------+------+------+
| id | b | c | a |
+----+------+------+------+
| 1 | 1 | 999 | 1 |
| 2 | 2 | 9999 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)

结果和场景一一样

场景三

slave_rows_search_algorithms 设置成hash_Scan

主从初始值

mysql> select * from table_t;
+----+------+------+------+
| id | b | c | a |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
+----+------+------+------+

在从库上执行： update table_t set a=null where id=2;
在主库上执行： update table_t set b=9999 where a=2;

mysql从库> select * from table_t ;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | NULL | 2 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)


mysql主库> select * from table_t;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 9999 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)

主从报错

Last_SQL_Error: Could not execute Update_rows event on table test.table_t; Can't find record in 'table_t', Error_code: 1032;
Can't find record in 'table_t', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 1159

结论

只设置

slave_rows_search_algorithms 设置成hash_Scan 时，从库在回放binlog时会采用哈希扫描的方式来查找需要更新的数据行。结果就是没有找到需要修改的值对应行。

场景四

在mysql8.0中，该参数默认值为

slave_rows_search_algorithms | INDEX_SCAN,HASH_SCAN

再次进行场景三的实验

从库结果如下

mysql> select * from table_t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
|  2 |    2 | 9999 |    2 |
+----+------+------+------+

结论

说明binlog回放更新的时候，再次使用到了主键匹配。