实验前提
一个主从复制的环境 ：主库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回放更新的时候，再次使用到了主键匹配。
