Mysql大表数据清理方案
需求:表t_user_login_log中存储了上千条数据,我们需要对该表做数据清理,只保留近三个月的数据。方案一数据量<1000条时推荐使用DELETE方法推荐力度:极不推荐是否会影响线上业务:严重影响会影响线上业务,一般情况会造成其它服务操作mysql卡顿,严重时会造成其它服务业务无法正常进行。使用select查询出需要清理的最小id和最大id,使用delete删除即可,示例场景:#查询
需求:表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来分钟就处理完了。
更多推荐
所有评论(0)