一条执行24s的SQL产生7小时主备延时（案例详解）

数据库性能诊断DBdoctor

已于 2024-08-28 09:49:48 修改

阅读量1.2k

点赞数 30

文章标签： sql 数据库 MySQL 高可用主从延时全表扫描

于 2024-08-26 11:41:22 首次发布

本文链接：https://blog.csdn.net/DBdoctor_off/article/details/141558622

版权

前言

在之前的文章《一条执行24s的SQL竟产生7小时数据延时，数据库高可用做了个寂寞》中，我们描述了一个问题现象：一条在主库执行24秒的SQL语句，却导致了主从延时长达7小时。文章发布后，引发了小伙伴的热烈讨论，很多小伙伴私信我们，希望我们可以再出一篇案例详解。为了帮助大家更直观的理解这一技术问题，本文将通过一个案例深入剖析，希望能给大家带来更清晰的认识。

为了方便清晰的进行跟因分析说明，我们根据客户在生产环境中遇到的实际问题，在我们的开发环境中采用小数据量快速模拟了这一案例。

现象

表结构如下：

CREATE TABLE `task_info` (`name` varchar(32) DEFAULT NULL,`task_params` varchar(128) DEFAULT NULL,`status` varchar(32) DEFAULT NULL,`task_detail` text) ENGINE=InnoDB DEFAULT CHARSET=utf8

通过执行计划，看到表中有80多万条数据，此SQL语句会进行全表扫描。主库上执行此条语句，耗时5.07秒。

从库上执行show salve status\G 可以看到延时达到65秒。

用户分析从库上各种监控指标都很正常，也不存在错误日志锁事件，最终通过DBdoctor看到主从延时开始的时候有一条DELETE语句耗时5秒，感觉比较可疑，猜测可能与这条SQL语句有关系。

原因分析

MySQL的binlog（二进制日志）有三种格式：STATEMENT，ROW和MIXED。

STATEMENT：这是MySQL最早的复制格式，所有的操作都会以SQL语句的形式记录在binlog中。这种格式的优点是binlog文件较小，但是在有些情况下可能会出现主从数据不一致的情况。
ROW：这种格式会记录行的变化，每一行的变化都会记录在binlog中。这种格式的优点是可以避免主从数据不一致的问题，但是binlog文件会非常大。
MIXED：这种格式是STATEMENT和ROW的混合，MySQL会根据操作类型自动选择使用STATEMENT还是ROW。这种格式结合了前两种格式的优点，但是可能会使得binlog文件变得比较大。

主从同步的本质是基于主库的binlog来实现的，主库记录binlog，然后从库将binlog在自己的服务器上重放，从而保证了主、从的数据一致性。

现在使用最广的是ROW格式，并且一些数据同步工具也要求必须将binlog的格式设置为ROW格式，我们这也是使用的ROW格式。

使用MySQL的binlog工具查看，主库上执行的这条删除语句，一共删除了10行数据，在binlog中每行数据分别对应一条日志。

主从同步每行数据在备库进行重放，由于表没有索引，每行数据重放时都会做一次全表扫描，这里10条数据就进行了10次全表扫描，最终导致主从延时65秒。而客户环境一条DELETE语句删除了1000多条数据，则放大了1000多倍，最终导致主从延时7小时。

使用DBdoctor进行SQL审核

接下来我们使用DBdoctor进行SQL审核，发现此表存在严重问题：没有设置主键。设置主键可以提升性能，有效的避免主从延时问题。

总结

小伙伴们，通过本文深入的案例分析，您是否对数据库高可用有了更深刻的理解呢？SQL语句作为数据库操作的核心，其性能优化对系统的稳定性与安全性至关重要。DBdoctor首创的SQL性能审核功能，可重点解决代码开发阶段SQL性能无法真实评估的难题，创新性地提出基于eBPF技术的外置Cost优化器方案。无需在生产环境中执行SQL，即可准确预测其在生产环境中的性能表现，并提供全局最优索引，提前规避故障发生。

***********************************************************************************************************

DBdoctor下载/在线试用：

https://dbdoctor.cn