测试MySQL主从复制中主库表缺失主键会导致主从延迟的情况

举报
小麦苗DB宝 发表于 2022/04/01 16:26:13 2022/04/01
【摘要】 一、简介 二、环境准备 三、实验过程 3.1 主库创建表 3.2 主库做更新操作 3.3 分析主库的binlog日志 3.4 分析从库的中继日志 四、结论 一、简介导致MySQL主从复制延迟的原因有很多，其中一个原因就是大表缺失主键或唯一索引。今天我们就通过实验的方式来验证这种情况。 二、环境准备主库：IP为192.168.68.168，端口3306，版本为8.0.20从库：IP为192....

一、简介

导致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的主从复制架构中，若存在大表，那么一定要有主键或唯一索引，否则将导致很大的主从延迟。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入