丢掉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可以这样修改亿级数据表结构