丢掉DDL,我用这招3分钟清空 MySQL 9亿记录数据表

摘要:最近由于福建开机广告生产环境的广告日志备份表主键(int类型)达到上限(21亿多),不能再写入数据,需要重新清空下该表并将主键重置,但由于表里有8亿多记录的数据量,使用重置命令及DDL命令执行地非常慢,所以采取删除物理表结构文件的方式来进行快速清空表表数据!

前言

1、本文介绍是在MySQL 5.5.29版本进行的操作,其他的版本的没有试过,有兴趣的可以自己尝试去试下!

2、本文介绍的是删除frm和idb文件,同时不破坏原表结构的清空数据的方式!

一、数据背景及系统介绍

 

为更好说明问题,首先介绍下我们系统的数据流转的过程

step1:日志入库。API向机顶盒/EPG提供广告接口,采集广告请求日志,当STB访问EPG,EPG调用广告请求接口获取广告策略,并写入到报表数据库的【广告请求数据原始表t_ad_req_log】中;

step2:存储过程备份日志表数据。由于每天产生的广告请求数据量有2000多万,对于etl汇总时抽取有压力,所以通过存储过程将7天以前的数据备份到【广告请求数据备份表t_ad_req_log_back】中,【广告请求数据原始表t_ad_req_log】只保留7天内的数据,约8000万~1亿2千万记录左右;

step3:etl抽取汇总。使用etl每小时抽取【广告请求数据原始表t_ad_req_log】的上一时段的数据,抽取,分析,汇总写入报表数据库的【广告/广告位按时段汇总表】里面;

step4:定时删除备份日志表数据。每隔一段时间检查下数据etl汇总后的数据是否有问题,确认无误数据没问题后才将【广告请求数据备份表t_ad_req_log_back】清空,因为该表占用空间实在太大了,不清空隔一段时间就会收到磁盘空间报警短信!

本次就是有近2个月没有清空数据,发现就有近9亿条记录(为什么这里不用select count(*) 语句来查询?是因为执行这样一条语句10来分钟都没出结果,才用的explain来查看下表中总数据记录)

占用磁盘空间也是高得吓人,120G

 当然不仅仅是因为占用磁盘过高,更重要的原因是,该表的主键值达到int类型的上限值2147483646了(21亿多),这使得最新的备份数据不能继续写入到该备份表了

所以,确认汇总表没有数据缺失后,急需清空该备份表的数据,并重置下主键

 

二、为什么不用DDL

通过上面确认【广告请求数据备份表t_ad_req_log_back】表数据可删后,当务之急就是要尽快清空数据,无疑最先想到的就是使用使用ALTER重置主键,执行命令:

ALTER TABLE t_ad_req_log AUTO_INCREMENT= 1;

开个小会,结果36分钟过去没有出现结果,尴尬了,执行线程查询命令:

show processlist;

发现一直在“ copy to tmp table”,无奈,停下来试试执行drop命令:

DROP TABLE t_ad_req_log;

喝杯咖啡,执行了40分钟还没删掉,快急出翔了。

什么叫无能为力?什么叫难以启齿的柔弱?大概就是MySQL DDL遇到9亿级表结构的时候了吧!

于是不得已另选它法——删除表文件ibd和frm方式!

 

三、3分钟删除

当然,找到该表的文件,一键rm -rf 删除还是贼爽的,轻轻松松不用3分钟。

不过因为不知道这么删会不会有什么关联影响,于是就先到测试服务器做了个测试,还是遇到了些问题,记录下步骤后,到现网删除9亿级记录数据清空数据也是用了不到3分钟?具体操作如下:

3.1 表结构备份

先在测试数据库创建一个与现网【广告请求数据备份表t_ad_req_log_back】一样的表,然后将备份表的表文件t_ad_req_log_back.frm和t_ad_req_log_back.ibd拷贝到现网/home目录下,这一步的操作主要是为了后面解决建表时出现的“Table `t_ad_req_log_back` already exists”问题

3.2 删掉现网表文件

切换到mysql的data目录下,执行删除命令:

rm -rf t_ad_req_log_back.*

再一看,磁盘空间并没有释放,还是276G

于是使用lsof命令查看文件信息:

lsof -n|grep deleted

发现删除程序还存活

继续使用kill -9 27713命令该进程,磁盘空间得到释放

3.3 重新建表

由于表文件已经被删掉,该表也就不存在,使用show命令查看也确实没有发现这个表了

于是想继续建表,发现报错,报错信息一直是“Table `t_ad_req_log_back` already exists”

 Google了一下,才找到原因,原来是:

由于直接删除了表的物理文件 但mysql的信息库 information_schema 或 mysql 库对该表的信息还存在,也就是说InnoDB格式的表索引都保存在ibdata1这个文件中,虽然物理文件被删除,但是ibdata1中的索引没有删除,所以数据库认为该表已经存在,导致创建失败,也就说直接rm -rf 表文件破坏了表结构。

 难道这个表名就无法再用了吗? 

当然可以,j记得吗?我们最先做了个表结构备份,现在它派上用场了,将/home目录下的两个表文件t_ad_req_log_back.frm和t_ad_req_log_back.ibd拷贝到数据库的data的目录下,发现表存在了

执行desc命令发现又报错“Table `t_ad_req_log_back` already exists”,这是怎回事呢?

再一看用户权限不对,

改变用户权限

chmod 660 t_ad_req_log_back.frm
chown -R mysql:mysql t_ad_req_log_back.frm

然后再drop表, 删除之后会发现该数据库的data目录下的物理文件夹中还存在t_ad_req_log_back.ibd文件, 删除该文件,然后再次建表就ok了

 

至此,9亿级表数据被清空了,同时表结构也没有被破坏!

 我的相关文章参考:不停机不停服务,MYSQL可以这样修改亿级数据表结构

每一个你不满意的当下,都有一个你不曾努力的过去
posted @ 2018-04-10 20:57  骑白马的菜鸟  阅读(3355)  评论(22编辑  收藏  举报