MySQL数据库复制延迟

梦莱3年前 (2023-02-10)技术文章791

【问题处理记录】

1.查看资源情况，通过查找慢 SQL 发现，从 3:00 开始，存在较多 update 和 delete 操作。怀疑是表结构无主键，由于 RDS 日志模式默认采取的 row 模式，导致后续日志放到备库后，每更新或者删除一条记录，对应到备库都需要进行全表扫描，以此造成主备延迟现象。

2.通过查看表结构发现，表无任何索引，且存在较多 text 字段，具体表结构信息如下：

CREATE TABLE `XXX` (
..........
  `parent_label` text COLLATE utf8mb4_general_ci,
  `parent_data_version` int DEFAULT NULL,
  `parent_sn_path` text COLLATE utf8mb4_general_ci,
  `category_sn` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `category_label` text COLLATE utf8mb4_general_ci,
  `category_data_version` int DEFAULT NULL,
  `category_sn_path` text COLLATE utf8mb4_general_ci,
  `column1` text COLLATE utf8mb4_general_ci,
  `column2` text COLLATE utf8mb4_general_ci,
  `column3` text COLLATE utf8mb4_general_ci,
  `column4` text COLLATE utf8mb4_general_ci,
  `column5` text COLLATE utf8mb4_general_ci,
  `column6` text COLLATE utf8mb4_general_ci,
  `column7` text COLLATE utf8mb4_general_ci,
  `column8` text COLLATE utf8mb4_general_ci,
  `column9` text COLLATE utf8mb4_general_ci,
  `column10` text COLLATE utf8mb4_general_ci,
  `column11` text COLLATE utf8mb4_general_ci,
  `column12` text COLLATE utf8mb4_general_ci,
  `column13` text COLLATE utf8mb4_general_ci,
  `column14` text COLLATE utf8mb4_general_ci,
  `column15` text COLLATE utf8mb4_general_ci,
  `column16` text COLLATE utf8mb4_general_ci,
  `column17` text COLLATE utf8mb4_general_ci,
  `column18` text COLLATE utf8mb4_general_ci,
  `column19` text COLLATE utf8mb4_general_ci,
  `column20` text COLLATE utf8mb4_general_ci,
............
  `building` text COLLATE utf8mb4_general_ci,
  `pushId` text COLLATE utf8mb4_general_ci,
  `alias` text COLLATE utf8mb4_general_ci,
  `saleId` text COLLATE utf8mb4_general_ci,
  `saleName` text COLLATE utf8mb4_general_ci,
  `nature` text COLLATE utf8mb4_general_ci,
  `status` text COLLATE utf8mb4_general_ci,
  `series` text COLLATE utf8mb4_general_ci,
  `structure` text COLLATE utf8mb4_general_ci,
  `land` text COLLATE utf8mb4_general_ci,
  `stageId` text COLLATE utf8mb4_general_ci,
  `stageName` text COLLATE utf8mb4_general_ci,
  `businessId` text COLLATE utf8mb4_general_ci,
  `businessType` text COLLATE utf8mb4_general_ci,
  `remark` text COLLATE utf8mb4_general_ci,
  `jzzdmj` text COLLATE utf8mb4_general_ci,
  `zjzmj` text COLLATE utf8mb4_general_ci,
  `dszjzmj` text COLLATE utf8mb4_general_ci,
  `dxzjzmj` text COLLATE utf8mb4_general_ci,
  `zjrmj` text COLLATE utf8mb4_general_ci,
  `dsjrmj` text COLLATE utf8mb4_general_ci,
  `dxjrmj` text COLLATE utf8mb4_general_ci,
  `dsfjrmj` text COLLATE utf8mb4_general_ci,
  `dxfjrmj` text COLLATE utf8mb4_general_ci,
  `cdxqckjzmj` text COLLATE utf8mb4_general_ci,
  `dxzxckjzmj` text COLLATE utf8mb4_general_ci,
  `dxsbyfjzmj` text COLLATE utf8mb4_general_ci,
  `dxqtjzmj` text COLLATE utf8mb4_general_ci,
  `fjrjkczjzmj` text COLLATE utf8mb4_general_ci,
  `zksmj` text COLLATE utf8mb4_general_ci,
  `dsksmj` text COLLATE utf8mb4_general_ci,
  `dxksmj` text COLLATE utf8mb4_general_ci,
  `zzsmj` text COLLATE utf8mb4_general_ci,
  `dszsmj` text COLLATE utf8mb4_general_ci,
  `dxzsmj` text COLLATE utf8mb4_general_ci,
  `sjdj` text COLLATE utf8mb4_general_ci,
  `dsksb` text COLLATE utf8mb4_general_ci,
  `dscs` text COLLATE utf8mb4_general_ci,
  `dxcs` text COLLATE utf8mb4_general_ci,
  `dys` text COLLATE utf8mb4_general_ci,
  `lddts` text COLLATE utf8mb4_general_ci,
  `dxjzmj` text COLLATE utf8mb4_general_ci,
  `buildingId` text COLLATE utf8mb4_general_ci,
  `completeDate` text COLLATE utf8mb4_general_ci,
  `isHanding` text COLLATE utf8mb4_general_ci,
  `handingDate` text COLLATE utf8mb4_general_ci,
  `rfjzmj` text COLLATE utf8mb4_general_ci,
  `projectId` text COLLATE utf8mb4_general_ci,
  `projectName` text COLLATE utf8mb4_general_ci,
  `natureCode` text COLLATE utf8mb4_general_ci,
  `statusCode` text COLLATE utf8mb4_general_ci,
  `seriesCode` text COLLATE utf8mb4_general_ci,
  `structureCode` text COLLATE utf8mb4_general_ci,
  `landCode` text COLLATE utf8mb4_general_ci,
  `sfyx` text COLLATE utf8mb4_general_ci,
  `protypeCode` text COLLATE utf8mb4_general_ci,
  `protype` text COLLATE utf8mb4_general_ci,
  `timestamp` text COLLATE utf8mb4_general_ci,
  `planBulidCode` text COLLATE utf8mb4_general_ci,
  `xxrq` text COLLATE utf8mb4_general_ci,
  `fangkuantj` text COLLATE utf8mb4_general_ci,
  `jhxxdata` text COLLATE utf8mb4_general_ci,
  `jhjgdata` text COLLATE utf8mb4_general_ci,
  `jhjfdata` text COLLATE utf8mb4_general_ci,
  `jhjdfktj` text COLLATE utf8mb4_general_ci,
  `htydjfdate` text COLLATE utf8mb4_general_ci,
  `houseAmount` text COLLATE utf8mb4_general_ci,
  `cppx` text COLLATE utf8mb4_general_ci,
  `ksmj` text COLLATE utf8mb4_general_ci,
  `fwyckdsbyfmj` text COLLATE utf8mb4_general_ci,
  `kswymj` text COLLATE utf8mb4_general_ci,
  `ccsmj` text COLLATE utf8mb4_general_ci,
  `wcpjmj` text COLLATE utf8mb4_general_ci,
  `zcwymj` text COLLATE utf8mb4_general_ci,
  `gcldmc` text COLLATE utf8mb4_general_ci,
  `ccsgs` text COLLATE utf8mb4_general_ci,
  `ychgmj` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

3.核实表结构信息，并反馈如下内容

核实表结构，表是没有主键的。目前给到如下意见：
1.表最好都添加上主键索引，避免删除的时候造成大的延迟。
2.这边可以核实一下业务层面为什么每天要进行 delete 操作。是否可以使用truncate操作代替。但是使用 truncate 以后，如果操作失误，不能通过 binlog 进行数据恢复,只能通过时间节点进行备份恢复。truncate操作会比delete 操作要快，且不会产生大量日志。

返回列表

上一篇：Linux 文本三剑客 - Grep

下一篇：CDP实操--HDFS角色迁移