【云+社区年度征文】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况
一、简介
二、环境准备
三、实验过程
3.1 主库创建表
3.2 主库做更新操作
3.3 分析主库的binlog日志
3.4 分析从库的中继日志
四、结论
一、简介
在这里，我们先简单概述一下复制逻辑：
1、主库将对数据库实例的变更记录到主库自身的binlog文件中。
2、主库会有binlog dump线程实时监测binlog的变更并将这些新的events事件推给从库（Master has sent all binlog to slave; waiting for more updates）
3、从库的IO Thread接收这些events，并将其写入从库relaylog中继日志。
4、从库的SQL Thread读取relaylog的events，并将这些events应用（或称为重放）到从库实例。
上述为默认的异步复制逻辑，半同步复制又有些许不同，此处不再赘述。
此外，判断从库有延迟是十分简单的一件事：在从库上通过SHOW SLAVE STATUS，检查Seconds_Behind_Master值即可获取主从复制延迟的秒数。
主从复制延迟，可能的原因有主库和从库方面：
① 主库写binlog不及时
参数sync_binlog控制binlog从内存写入磁盘的控制开关，5.6默认为0，从5.7开始默认为1。
sync_binlog=1：每次事务提交都立即刷新binlog到磁盘(双一标准中的其一)。
sync_binlog=0：每次事务提交不立即写入磁盘，靠操作系统判断什么时候写入。
磁盘IO不行，binlog写入比较慢。建议binlog使用ssd。
② dump线程压力大
从库越多，压力越大。
5.6以后开启了GTID功能，dump在传输时可以并发了。
5.7之后不开启GTID，也会有匿名的GTID。dump也可以并发传输。
但是，如果出现大事务比较多，锁等待，死锁严重的话，依然会有比较高的传输延时。
③ IO线程阻塞
大事务------ >拆成小事务
事务量大---->group commit
④ SQL线程慢(Classic replication)
1.默认只有一个SQL线程，从库中的事务都是一个一个来执行的
2.如果主库的并发事务数很多
3.大事务都会造成从库延时
5.6多线程复制(多sql线程)，有局限性，针对不同库的事务进行并发，在有些情况下可以解决大事务问题，只能在主库方面，将大事务拆成小事务
默认情况下。只有一个SQL线程，所以说执行relay只能串行操作。
在5.6版本以后，GTID模式下，可以开启多个SQL线程并发回放relaylog。但是只能针对Database级别并行。
在5.7版本以后，GTID模式下，可以基于事务级别进行多SQL线程回放relaylog。
⑤ 表缺乏主键或唯一索引
binlog_format=row的情况下，如果表缺乏主键或唯一索引，在UPDATE、DELETE的时候可能会造成从库延迟骤增。此时Slave_SQL_Running_State为Reading event from the relay log。并且SHOW OPEN TABLES WHERE in_use=1的表一直存在。mysqld进程的cpu几近100%（无读业务时），io压力不大
主库产生的日志记在binlog里是row格式的，不是一条sql，而是这条sql所影响的具体值的修改。简单点：若主库没有主键，主库执行一条sql修改了10万行，只需要全表扫描一次，但是，备库就要执行10万条sql语句，全表扫描10万次。备库在回放binlog的时候回放的是一行一行更新的sql，从库只能使用全表扫描来同步，所以会比较耗时，导致延迟。
导致MySQL主从复制延迟的原因有很多，其中一个原因就是大表缺失主键或唯一索引。
今天我们就通过实验的方式来验证这种情况。
二、环境准备
主库：IP为192.168.68.168，端口3306，版本为8.0.20
从库：IP为192.168.68.168，端口3306，版本为8.0.20
[root@docker35 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ebe3b62a2358 mysql:8.0.20 "docker-entrypoint.s…" 8 days ago Up 8 days 33060/tcp, 0.0.0.0:3319->3306/tcp mysql8020S1
76140b04e2fd mysql:8.0.20 "docker-entrypoint.s…" 8 days ago Up 8 days 33060/tcp, 0.0.0.0:3318->3306/tcp mysql8020M1
-- 主库
MySQL [lhrdb1]> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 80203319 | | 3306 | 80203318 | e12dfcd2-1e40-11eb-b2f0-0242c0a844a9 |
+-----------+------+------+-----------+--------------------------------------+
MySQL [lhrdb1]> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
-- 从库
MySQL [(none)]> show slave status\\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql8020M1-bin.000007
Read_Master_Log_Pos: 19665393
Relay_Log_File: mysql8020S1-relay-bin.000008
Relay_Log_Pos: 19665620
Relay_Master_Log_File: mysql8020M1-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
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: 19665393
Relay_Log_Space: 19665928
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: 0
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: 80203318
Master_UUID: dcccf122-1e40-11eb-8ca0-0242c0a844a8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:20-160037
Executed_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:1-160037
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)三、实验过程
3.1 主库创建表
主库先创建一张8万行的大表：
MySQL [lhrdb1]> DELIMITER $$
MySQL [lhrdb1]> drop procedure if exists `t_pro`$$
Query OK, 0 rows affected (0.01 sec)
MySQL [lhrdb1]> create procedure `t_pro`(num int)
-> begin
-> declare i int unsigned default 1;
-> set i=1;
-> while i <= num do
-> insert into `t` (`id`,`name`)
-> values(i,concat('主键测试',i));
-> set i=i + 1;
-> end while;
-> end$$
Query OK, 0 rows affected (0.01 sec)
MySQL [lhrdb1]> DELIMITER ;
MySQL [lhrdb1]> call `t_pro`(80000);
--- .... 这里插入8万行数据，比较慢从库查询延迟：
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 11
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql8020M1-bin.000007
Read_Master_Log_Pos: 26029948
Relay_Log_File: mysql8020S1-relay-bin.000008
Relay_Log_Pos: 25067097
Relay_Master_Log_File: mysql8020M1-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
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: 25066870
Relay_Log_Space: 26030483
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: 12
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: 80203318
Master_UUID: dcccf122-1e40-11eb-8ca0-0242c0a844a8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: waiting for handler commit
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:20-181149
Executed_Gtid_Set: dcccf122-1e40-11eb-8ca0-0242c0a844a8:1-177960
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 19
MySQL [(none)]> select count(*) from lhrdb1.t;
+----------+
| count(*) |
+----------+
| 41613 |
+----------+
1 row in set (0.01 sec)
MySQL [(none)]> select count(*) from lhrdb1.t;
+----------+
| count(*) |
+----------+
| 41941 |
+----------+
1 row in set (0.01 sec) 发现，从库延迟越来越高，主要原因是主库正在进行大批量的数据插入操作。
大约5分钟后，主库执行完毕，
MySQL [lhrdb1]> call `t_pro`(80000);
Query OK, 1 row affected (5 min 3.37 sec)
MySQL [lhrdb1]> select count(*) from lhrdb1.t;
+----------+
| count(*) |
+----------+
| 80000 |
+----------+
1 row in set (0.01 sec)从库查询，数据已同步完成：
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 30
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 0
MySQL [lhrdb1]> select count(*) from lhrdb1.t;
+----------+
| count(*) |
+----------+
| 80000 |
+----------+
1 row in set (0.01 sec) 3.2 主库做更新操作
MySQL [lhrdb1]> flush logs;
Query OK, 0 rows affected (0.02 sec)
MySQL [lhrdb1]>
MySQL [lhrdb1]> show master logs;
+------------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------------+-----------+-----------+
| mysql8020M1-bin.000001 | 179 | No |
| mysql8020M1-bin.000002 | 179 | No |
| mysql8020M1-bin.000003 | 179 | No |
| mysql8020M1-bin.000004 | 53350509 | No |
| mysql8020M1-bin.000005 | 1155468 | No |
| mysql8020M1-bin.000006 | 1345290 | No |
| mysql8020M1-bin.000007 | 45018719 | No |
| mysql8020M1-bin.000008 | 196 | No |
+------------------------+-----------+-----------+
8 rows in set (0.00 sec)
MySQL [lhrdb1]> update t set name=concat('主键测试，结果验证',t.id) where id <=60000;
Query OK, 40000 rows affected (1.88 sec)
Rows matched: 60000 Changed: 40000 Warnings: 0可以看出，主库基本在2s就更新完成，变化的行数为4万行。
从库查询延迟，
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 0
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 0
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 4
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 5
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 7
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 8
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 10
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 11
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 13
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 14
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 16
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 17
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 18
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 19
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 20
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 21
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 0
D:\\Program Files\\MySQL\\mysql-8.0.15-winx64\\bin>mysql -uroot -plhr -h192.168.1.35 -P3319 -e "show slave status \\G" | grep Seconds_Behind_Master
mysql: [Warning] Using a password on the command line interface can be insecure.
Seconds_Behind_Master: 0可以发现，最长延迟21秒左右。
3.3 分析主库的binlog日志
root@mysql8020M1:/var/lib/mysql# mysqlbinlog mysql8020M1-bin.000008 --base64-output=decode-row -vv | grep UPDATE | wc -l
40000
root@mysql8020M1:/var/lib/mysql# mysqlbinlog mysql8020M1-bin.000008 --base64-output=decode-row -vv | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201112 11:25:24 server id 80203318 end_log_pos 125 CRC32 0x11d2d479 Start: binlog v 4, server v 8.0.20 created 201112 11:25:24
# Warning: this binlog is either in use or was not closed properly.
# at 125
#201112 11:25:24 server id 80203318 end_log_pos 196 CRC32 0x443aeae4 Previous-GTIDs
# dcccf122-1e40-11eb-8ca0-0242c0a844a8:1-240042
# at 196
#201112 11:25:33 server id 80203318 end_log_pos 276 CRC32 0xa8eb8112 GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1605151535412817 immediate_commit_timestamp=1605151535412817 transaction_length=2451018
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1605151535412817 (2020-11-12 11:25:35.412817 CST)
# immediate_commit_timestamp=1605151535412817 (2020-11-12 11:25:35.412817 CST)
/*!80001 SET @@session.original_commit_timestamp=1605151535412817*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'dcccf122-1e40-11eb-8ca0-0242c0a844a8:240043'/*!*/;
# at 276
#201112 11:25:33 server id 80203318 end_log_pos 362 CRC32 0x0b667ff4 Query thread_id=26 exec_time=0 error_code=0
SET TIMESTAMP=1605151533/*!*/;
SET @@session.pseudo_thread_id=26/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
-- 省略部分 ..................
#201112 11:25:33 server id 80203318 end_log_pos 2446999 CRC32 0x6e0e09e8 Update_rows: table id 105
# at 2446999
#201112 11:25:33 server id 80203318 end_log_pos 2451183 CRC32 0x30bfc23e Update_rows: table id 105 flags: STMT_END_F
### UPDATE `lhrdb1`.`t`
### WHERE
### @1=20001 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试20001' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
### @1=20001 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试，结果验证20001' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### UPDATE `lhrdb1`.`t`
### WHERE
### @1=20002 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试20002' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
### @1=20002 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试，结果验证20002' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
-- 省略部分 ..................
root@mysql8020M1:/var/lib/mysql# mysqlbinlog mysql8020M1-bin.000008 --base64-output=decode-row -vv | tail -n 20
### @1=59999 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试59999' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
### @1=59999 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试，结果验证59999' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### UPDATE `lhrdb1`.`t`
### WHERE
### @1=60000 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试60000' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
### @1=60000 /* INT meta=0 nullable=0 is_null=0 */
### @2='主键测试，结果验证60000' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
# at 2451183
#201112 11:25:33 server id 80203318 end_log_pos 2451214 CRC32 0x3d5db696 Xid = 720239
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;可以看出，在ROW模式下，在主库上执行了一条UPDATE语句，更新了4万行记录，但是在binlog中，记录了4万行的UPDATE语句。
3.4 分析从库的中继日志
root@mysql8020S1:/var/lib/mysql# mysqlbinlog mysql8020S1-relay-bin.000010 --base64-output=decode-row -vv | grep UPDATE | wc -l
40000可以看出，在从库上也是4万行的UPDATE语句，也是一条一条的进行更新。由于没有主键和索引，所以，就会导致在从库进行4万次的全表扫描，这样也就拖慢了从库APPLY的效率。
四、结论
在MySQL的主从复制架构中，若存在大表，那么一定要有主键或唯一索引，否则将导致很大的主从延迟。
THE END
0
二维码
海报
【云+社区年度征文】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况
2、主库会有binlog dump线程实时监测binlog的变更并将这些新的events事件推给从库（Master has sent all binlog to...
