mysql没有主键导致延迟_mysql无主键无索引表导致同步延迟

Mysql主从同步延迟发生

现象：

Show slave status\G;

Relay_Master_Log_File: mysql_bin.002597

.

Exec_Master_Log_Pos: 645248292

.

Seconds_Behind_Master: 2447

.

Slave_SQL_Running_State: Reading event from the relay log

pos一直保持不变，并且behind一直在增加，

备库执行：

Show processlist;

SQL thread State列状态如下：

Reading event from the relay log

代表 线程已经从中继日志读取一个事件，可以对事件进行处理了。

查看binlog：

/home/mysql/mysql3502/bin/mysqlbinlog --start-position="645248292" --base64-output=decode-rows -v ./mysql_bin.002597 >mysqlbinlog.log

vi mysqlbinlog.log

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 645248292

#170226 2:28:35 server id 5123502 end_log_pos 645248340 CRC32 0x081bbaca GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '5c580bf9-0161-4e14-943b-751a3a09508b:5817253'/*!*/;

# at 645248340

.

.

BEGIN

/*!*/;

# at 645248419

#170226 2:28:35 server id 5123502 end_log_pos 645248550 CRC32 0xbf8a6c8c Table_map: `****`.`****` mapped to number 41501

# at 645248550

#170226 2:28:35 server id 5123502 end_log_pos 645256612 CRC32 0x7c9e1837 Delete_rows: table id 41501

# at 645256612

#170226 2:28:35 server id 5123502 end_log_pos 645264682 CRC32 0xa1e4d6e7 Delete_rows: table id 41501

# at 645264682

#170226 2:28:35 server id 5123502 end_log_pos 645272881 CRC32 0xa1d560d0 Delete_rows: table id 41501

# at 645272881

#170226 2:28:35 server id 5123502 end_log_pos 645281037 CRC32 0x6e9df23b Delete_rows: table id 41501

# at 645281037

#170226 2:28:35 server id 5123502 end_log_pos 645289102 CRC32 0x8bb31581 Delete_rows: table id 41501

# at 645289102

#170226 2:28:35 server id 5123502 end_log_pos 645297192 CRC32 0xcc940bb0 Delete_rows: table id 4150

.

.

### DELETE FROM `****`.`****`

### WHERE

### @1=****

### @2=****

### @3=****

### @4=****

### @5=****

### @6=****

### @7=****

### @8=****

### DELETE FROM `****`.`****`

### WHERE

### @1=****

### @2=****

### @3=****

### @4=****

### @5=****

### @6=****

### @7=****

### @8=****

.

.

查看表结构发现没有主键和索引。

mysql> show create table `****`.`****`\G;

*************************** 1. row ***************************

Table: ****

Create Table: CREATE TABLE `****` (

`uuid` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`loginname` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

****

****

****

`edittime` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

延迟发生原因：

首先mysql主从是基于行的复制。

举例解释下什么是基于行的复制，假设主库执行以下sql删除了表A中的100条数据：

delete from a where id<101；

这时mysql会把这个SQL按照每条记录，拆分成100条delete SQL在备库上执行，mysql这么做的目的也是最大程度的保证同步数据的可靠性。

但是可靠性的提升伴随而来的便是日志量的增多，同步过程会占用大量带宽。

其次，该表即无主键，也没有索引。

假设还是以上对A表的删除操作，拆成的100条delete SQL传递并且在备库执行，因为表即无主键，也没有索引，所以每执行一次都要做全表扫描才能定位到要删除的那一条数据，可想而知同步效率会低很多。

解决方案：

1 表设计时就要有主键；

2 如果延迟已经发生，并且表不是特别大的情况下，在备库上为该表创建索引或是主键。