Mysql大表数据清理方案

发布时间：2024-11-30 00:49

理解数据库原理（SQL、MySQL） #生活知识# #编程教程#

需求：表t_user_login_log中存储了上千条数据，我们需要对该表做数据清理，只保留近三个月的数据。

数据量<1000条时推荐使用

DELETE方法

推荐力度：极不推荐

是否会影响线上业务：严重影响

会影响线上业务，一般情况会造成其它服务操作mysql卡顿，严重时会造成其它服务业务无法正常进行。

使用select查询出需要清理的最小id和最大id，使用delete删除即可，示例场景：

#查询最小id与最大id

SELECT MIN(id),MAX(id) FROM t_user_login_log;

#删除数据

DELETE FROM t_user_login_log WHERE id>=minId and id <= maxId;

方案缺陷

数据量较大时，会造成mysql服务进程CPU飙升，缓冲区不足的情况下失败。

分流DELETE方法

推荐力度：不推荐

是否会影响线上业务：影响

该方法是网上比较流行的方法，但是遇到表数据比较大时，删除数据仍然比较吃力，mysql的主进程CPU占用比较高，会影响线上业务。

例如我们查出需要删除100W条数据，通过编写脚本代码，将这100W条数据分100次进行删除。

INSERT+RENAME TO+DROP TABLE

推荐力度：推荐

是否会影响线上业务：不影响

例如t_user_login_log表中有5000W条数据，我们只需要保留最近三个月的数据，三个月以内的数据1500W条，过期数据3500W条。

步骤一:拷贝表结构，生成临时表

#通过下面的msyql命令完成表结构拷贝

CREATE TABLE IF NOT EXISTS t_user_login_log_backup_tmp (LIKE t_user_login_log);

步骤二：计算出需要保留的数据步骤三：将需要保留的数据分批次INSERT临时表中步骤四：INSERT完成后，通过RENAME TO将源表修改为备份表步骤五：将临时表修改为正式表

注：进行步骤四和步骤五时，线上业务运行期间执行时，可能造成小量的数据丢失。

如何确保数据不丢失：线上业务在运行时，先走一、二、三的流程，需要保留的数据INSERT差不多时，抽时间将线上业务维护，再走四和五的流程，这样即可确保数据不会丢失。

笔者处理了一张5000W条的表数据，保留的数据1000多W左右，整个用时10来分钟就处理完了。

网址：Mysql大表数据清理方案 https://www.yuejiaxmz.com/news/view/318230