【云+社区年度征文】测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况

小麦苗DBA宝典 • 2023-01-02 • 云技术社区 • 505 阅读

一、简介
二、环境准备
三、实验过程
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
分享
二维码
海报
<<上一篇
下一篇>>