MySQL数据库清理3个月前数据

【MYSQL】清理主库存量日志，引发主备延迟报警

事件描述

技管科去年2月份正式投产使用API平台，上周六、本周三连续两次清理主库的日志表，虽然清理过程十几分钟就结束了，但这导致了主备之间的延迟高达3小时。MYSQL数据库有一套监控系统，每小时去检测一次数据库的性能，包括主备延迟，并短信通知相关我维护人员。

排查思路

作为新晋MYSQL DBA处理这种问题并不是很有经验，但基本的职业素养还是要有的：

• 首先，联系应用人员了解基本情况。确定他们是否有做相关的DML操作，比如更新、删除数据。
• 其次，验证应用人员的说法是否属实，并获取更具体的信息。从慢查询日志中得知他们执行的具体操作语句。
• 然后，基于个人经验和专业知识，提出相关的优化建议，包括技术或管理建议。
• 最后，复盘总结形成文档式资料。

爆破实操

第一步：

我收到报警后，立即联系应用人员，了解到他们正在清理主库的存量数据，这是应用上线后的首次清理，共清理约43w条数据，分5次，一次清10w。并得知他们执行清理方案的初衷是：有个SQL查询该表比较慢，怀疑是数据量的问题，企图减少表数据，避免全局扫描，加快查询速度。

第二步：

我从慢查询日志中分析出，他们描述的确实属实。日志显示他们使用delete语句删除5次api_statistics数据，每次耗时5秒左右。但这么快的速度怎么引起主备延迟3小时呢？

第三步

估算他们的数据量并确认下他们有没有建分区、索引。一个月43万表数据，一年就是近500万数据，两年就是千万级别了。从经验上来说，这个表必须要分区、索引、主键的。

#查分区
select * from information_schema.partitions where table_name=‘api_statistics’

#查建表语句，看索引、看主键
show create table api_statistics;
或
desc api_statistics;

从这一步可以看出，该表两年来近千万数据，虽然建了索引和主键，但没有建分区，这是非常危险的事情，好在应用人员想起来要清理了（可能是文件系统撑满报警了），为时不晚，不然轻则应用宕机、重则地球爆炸。

优化建议

最终给他们的建议是建个分区，每个季度建个分区，这样单个分区里就有100万条数据了，大大减少了操作风险、提高了应用稳定性与执行效率。