Mysql主从同步的坑,主库没问题、从库卡死
mysql主从同步原理
mysql自带的主从同步机制,是主库定期给从库传输binlog日志,从库通过binlog日志来做出相应的增删改操作,进而保证数据一致性。
什么是binlog日志
binlog是记录mysql更新操作的二进制日志文件,除SELECT外的DDL、DML语句都会在binlog文件中以事件形式记录。
binlog的格式
binlog有三种格式:
- Statement:记录会修改数据的 SQL 。
- Row:仅记录被修改的行数据。
- Mixed:Statement 和 Row 混合使用。
mysql5.7.7及之后的版本,binlog的默认格式都是Row。
Row格式具体是怎么样记录修改的呢,看个例子
mysql> CREATE TABLE student (
-> student_id TINYINT ( 4 ) NOT NULL,
-> NAME VARCHAR ( 50 ),
-> age TINYINT ( 4 ),
-> PRIMARY KEY ( student_id ) USING BTREE );
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSERT INTO student VALUES
(1,111,18),(2,222,19),(3,333,19),(4,444,19),(5,555,19);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
+------------+------+------+
| student_id | NAME | age |
+------------+------+------+
| 1 | 111 | 18 |
| 2 | 222 | 19 |
| 3 | 333 | 19 |
| 4 | 444 | 19 |
| 5 | 555 | 19 |
+------------+------+------+
5 rows in set (0.00 sec)
这里我在数据库新建了一张表student,主键是student_id,并插入了五条数据。
下面执行一下全表的delete
mysql> DELETE FROM student;
Query OK, 5 rows affected (0.00 sec)
没问题。
现在看一下binlog日志:
show master status 获取binlog文件名
打开cmd命令行
C:\WINDOWS\system32>>cd C:\ProgramData\MySQL\MySQL Server 8.0
C:\ProgramData\MySQL\MySQL Server 8.0>mysqlbinlog -vv Data/DESKTOP-8L8SKT8-bin.000017 > text.txt
打开text.txt瞅瞅:
BEGIN
/*!*/;
# at 93579
#230228 22:20:34 server id 1 end_log_pos 93645 CRC32 0xbc443685 Table_map: `cable_db`.`student` mapped to number 103
# at 93645
#230228 22:20:34 server id 1 end_log_pos 93715 CRC32 0xe3699ef1 Delete_rows: table id 103 flags: STMT_END_F
BINLOG '
sg3+YxMBAAAAQgAAAM1tAQAAAGcAAAAAAAEACGNhYmxlX2RiAAdzdHVkZW50AAMBDwECyAAGAQEA
AgP8/wCFNkS8
sg3+YyABAAAARgAAABNuAQAAAGcAAAAAAAEAAgAD/wABAzExMRIAAgMyMjITAAMDMzMzEwAEAzQ0
NBMABQM1NTUT8Z5p4w==
'/*!*/;
### DELETE FROM `my_db`.`student`
### WHERE
### @1=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @2='111' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=18 /* TINYINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `my_db`.`student`
### WHERE
### @1=2 /* TINYINT meta=0 nullable=0 is_null=0 */
### @2='222' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=19 /* TINYINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `my_db`.`student`
### WHERE
### @1=3 /* TINYINT meta=0 nullable=0 is_null=0 */
### @2='333' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=19 /* TINYINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `my_db`.`student`
### WHERE
### @1=4 /* TINYINT meta=0 nullable=0 is_null=0 */
### @2='444' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=19 /* TINYINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `my_db`.`student`
### WHERE
### @1=5 /* TINYINT meta=0 nullable=0 is_null=0 */
### @2='555' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3=19 /* TINYINT meta=0 nullable=1 is_null=0 */
# at 93715
#230228 22:20:34 server id 1 end_log_pos 93746 CRC32 0x5b50a2b6 Xid = 160
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
可以看出来,全表的delete命令在binlog文件中被分成了5个单行的delete命令。
也就是说:
主库一个命令可以完成的操作,从库在同步的时候,需要执行多次命令,执行的次数,取决于这个命令影响的行数
这个结论对delete、update、insert也适用
问题
那么这种同步机制会带来什么问题呢。
想象student表不是只有5条数据,而有100w条数据,当全表删除时,从库会执行100w次delete。
听起来很吓人?其实问题不大,在有主键的情况下,这样的同步逻辑基本不会有什么问题。
问题是,当没有主键的时候呢?
当没有主键的时候,从库在执行某一条delete时,会先去表里进行一次全表查询,然后删除查询到的数据,每删除一条,就要全表查询一次,直到全部删除。
删除和更新操作都会有这样的问题,在从库性能低到不行,几十w体量的表,可能让从库卡住几个小时不动。
解决办法
建表的时候设置主键即可