今日总结 – 【云+社区年度征文】测试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的主从复制架构中,若存在大表,那么一定要有主键或唯一索引,否则将导致很大的主从延迟。

正文完