MySQL记录一次因无主键大表大事务导致主备延迟的处理过程

最近在使用腾讯云云的mysql服务，因为大数据的同学创建的表都是无主键的，而且经常有大事务。在扩容的时候发现好几天没有完成，检查发现主备延迟了，备库一直追不上主库，扩容迟迟没有完成。记录一下处理的过程。

MySQL中因无主键大表和大事务导致的主从延迟问题，可以通过以下步骤进行定位和处理。

一、定位问题

1、检查主从复制状态

首先，可以使用

SHOW SLAVE STATUS;

命令来查看从库的状态，并检查Seconds_Behind_Master字段的值，这个值表示从库延迟了多少秒。当然也可以直接在云上查看监控。

2、查看binlog日志

使用mysqlbinlog工具查看binlog日志，找出可能导致大事务的SQL语句。

3、检查正在执行的SQL

在主库上执行

SHOW PROCESSLIST;

命令，查看正在执行的SQL语句，特别是那些执行时间较长的语句。云上一般也提供慢sql分析的地方。

4、检查表结构

确认导致问题的表是否没有主键，并且数据量很大。

二、处理问题

1、添加主键或唯一索引

对于没有主键的表，考虑添加一个主键或唯一索引。这可以帮助MySQL更有效地进行复制操作，并提高从库的APPLY效率。下面提供一个sql，直接查询整个实例下面那些表是没有主键的，根据数据量进行排序。在查询结果里面，大表添加一个自增的ID主键。

SELECT
	t.table_schema,
	t.TABLE_NAME,
	t.table_rows 
FROM
	information_schema.TABLES t
	LEFT JOIN ( SELECT CONSTRAINT_SCHEMA, table_name FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' ) p ON t.table_name = p.table_name 
	AND t.TABLE_SCHEMA = p.CONSTRAINT_SCHEMA 
WHERE
	t.table_schema NOT IN ( 'performance_schema', 'information_schema', 'mysql' ) 
	AND p.table_name IS NULL 
	AND t.TABLE_TYPE = 'BASE TABLE' 
ORDER BY
	t.table_rows DESC;

2、优化大事务

对于大事务，考虑将其拆分成多个小事务来执行。这可以减少从库的延迟，并提高系统的整体性能。

3、调整复制格式

MySQL支持三种复制格式：STATEMENT、ROW和MIXED。如果当前使用的是STATEMENT格式，并且存在无主键的表，考虑切换到ROW或MIXED格式。ROW格式会记录每一行的变化，这可能会减少从库的延迟。但是，请注意，ROW格式会增加binlog的大小，并可能增加网络传输的开销。

4、硬件升级

如果主从库的硬件配置不一致，考虑升级从库的硬件，以提高其处理能力。就是所谓的扩容了。

5、优化SQL语句

对于执行时间较长的SQL语句，考虑进行优化，例如添加索引、修改查询条件等。

三、总结

整个处理过程关键点就是把无主键的表找出来，加上自增的主键，然后找腾讯云的客服人工干预，重建备库。最终扩容完成！