mysql修改从库会影响复制_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。