MySQL create table t2 as select t1 导致的主从同步延迟问题

背景

数据库配置:binlog_format = row

故障原因

在主库执行了以下语句导致延迟

create table t2 as select t1
update t2 set column1=value where column2=value2

分析

  1. 关于复制原理
    MySQL的复制是基于binlog的,对于binlog_format=row的数据库,binlog里面记录的是每行数据的更改,也就是说,当执行一个更新语句时,语句更新了N行,就会在日志中存在N行记录。
    在从库应用日志时,会对binlog中的每一条记录进行应用,也就是说,主库上的一条更新语句=从库上的N条更新语句
  2. 关于数据更新
    数据更新时要先查找到要更新的数据,如果表不存在索引,会进行全表扫描,找到要更新的数据并进行更新
    当表数据量很大时,全表扫描会消耗大量资源。

在这个问题中,使用create table t2 as select t1复制了表t1的数据到t2中,但是,使用这个语句复制的表t2,是不包括t1的索引信息的,也就是说,后续更新t2时,是在一个无索引的大表中执行更新,在从库上要执行多次全表扫描来更新数据,所以最终导致的从库同步延迟

后续规避方法

create table t2 as select t1

替换为

create table t2 like t1
insert into t2 select * from t1

使用这种方法,也能够创建一个和表t1内容相同的表,并且t2会复制t1的索引信息

MySQL create table t2 as select t1 导致的主从同步延迟问题

上一篇:MySql常用日期时间函数


下一篇:ps如何让背景模糊?ps照片背景虚化教程