mysql row format下主库端大批量修改数据造成从库复制延时问题
实验版本：5.7.31-log
mysql中的每张表都需要创建索引，并且因为防止索引B+树 page split的原因，通常规范都创建 AUTO_INCREMENT的自增型主键。
如果表上没有主键或者选择性很高的索引，在 binlog_format= ROW 时，主库端大批量修改表中的数据在从库端会有大批量行的events回放，这种情况下如果表上没有主键或索引，每个行的操作都会变成table scan，会造成主从复制延时问题。这也是每张表都需要创建主键的另外一种原因。
--默认的slave_rows_search_algorithms
mysql> select @@global.slave_rows_search_algorithms;
+---------------------------------------+
| @@global.slave_rows_search_algorithms |
+---------------------------------------+
| TABLE_SCAN,INDEX_SCAN |
+---------------------------------------+
1 row in set (0.00 sec)
mysql>
--表结构，没有主键和索引
mysql> show create table tb01;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb01 | CREATE TABLE tb01 (id int(11) DEFAULT NULL,name varchar(30) DEFAULT NULL,addr varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
--表中有100万行数据
mysql> select count() from tb01;
+----------+
| count() |
+----------+
| 1000000 |
+----------+
1 row in set (0.19 sec)
mysql>
--主库端删除数据
mysql> delete from tb01;
Query OK, 1000000 rows affected (2.01 sec)
mysql>
--从库马上开始延时
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.57
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000028
Read_Master_Log_Pos: 28901411
Relay_Log_File: mysql5702-relay-bin.000010
Relay_Log_Pos: 401
Relay_Master_Log_File: binlog.000028
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 28901913
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 427
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 54c4ff93-0adf-11eb-b3bd-0800270418e5
Master_Info_File: /usr/local/my3306/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 54c4ff93-0adf-11eb-b3bd-0800270418e5:61-1000065
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
--通过mysqlbinlog翻译对应的relaylog mysql5702-relay-bin.000010
[mysql@mysql5702 my3306]$ /usr/local/mysql/bin/mysqlbinlog -vv --base64-output=DECODE-ROWS /usr/local/my3306/mysql5702-relay-bin.000010 > relaylog_results.sql
看到relaylog中
在row format下，生成了1000000个DELETE语句：
[mysql@mysql5702 my3306]$ cat relaylog_results.sql | grep DELETE | wc -l
1000000
[mysql@mysql5702 my3306]$
===============================表上有主键========================
--添加主键
mysql> alter table tb01 add primary key pk_tb01 (id);
Query OK, 0 rows affected (2.69 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
--主库端删除数据
mysql> delete from tb01;
Query OK, 999999 rows affected (1.93 sec)
从库端瞬间完成同步，同步正常
===============================表上有索引========================
--添加索引
mysql> alter table tb01 add index idx_tb01 (name);
Query OK, 0 rows affected (1.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
--主库端删除数据
mysql> delete from tb01;
Query OK, 999999 rows affected (7.52 sec)
mysql>
从库端也可以很快完成同步，同步正常。
结论：在默认的slave_rows_search_algorithms下，如果大批量update/delete master上的数据，表上一定要有主键或选择性高的索引，否则会立马导致从库复制延时并且很难追上。这也是mysql的表上都要建主键的另外一个原因。
注:slave_rows_search_algorithms 除了 TABLE_SCAN和INDEX_SCAN，还有一种 HASH_SCAN，但并不稳定，所以通常都不会配置 HASH_SCAN。
