MySQL 案例：无主键表引发的同步延迟

问题表现

腾讯云的灾备实例，备库，只读实例等均出现巨大的同步延迟，表现如下：

binlog 落后的 size 可能是 0 或者比较小

按照如下方式找到主从延迟时间的监控，会看到主从延迟的时间不为 0，且表现为稳定上升的趋势。

主从延迟时间的监控

而只读从库上又没有什么查询的话，有可能就是无主键、索引的表引起的主从延迟。

解决办法

推荐方案：趁着业务空闲期间，在主库上为表加上主键或者唯一索引，然后再重建受影响的灾备实例，备库，只读实例等。

可以使用如下的语句检查无主键的表：

select table_schema,table_name,TABLE_ROWS 
  from information_schema.tables 
  where (table_schema,table_name) not in 
      (select distinct table_schema,table_name 
        from information_schema.columns 
        where COLUMN_KEY='PRI') 
      and table_schema not in ('sys','mysql','information_schema','performance_schema')
      and table_type='BASE TABLE';

主键可以使用自增列，也可以使用业务上具有唯一性的其他列。

例如：

 alter table tmp1 add column id int unsigned not null AUTO_INCREMENT primary key before columnname;

PS：before 表的第一列，比较美观和符合常识的表结构写法。

问题分析

腾讯云数据库 MySQL 的 binlog 默认使用了 row 模式，binlog 会记录所有的数据变更，这意味着一个 update 或者 delete 语句如果修改了非常多的数据，那么每一行数据的变化都会记录到 binlog 中，最终会产生非常多的 binlog 日志。

Binlog 内容

从库在处理这些日志时，每一行数据的操作都会去尝试定位具体的数据，然后再判断是不是需要执行操作来完成数据变更。如果在某张大表上 update 或者 delete 一些数据，而这张表没有索引，那么定位数据的时候就会变成全表扫描，且 update 或者 delete 的每一行数据都会触发一次全表扫描，从库会产生非常大的延迟。

通过修改参数可能会加速追同步的速度，但是最好的办法还是加上主键或者唯一索引，索引搜索数据的效率还是远高于 HASH 算法的。