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

朱雀 2023-09-24 10:25 10阅读 0赞

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

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

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

![3c341da786ee02af13ae5a916fbd58c7.png][]

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

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来查看下表中总数据记录)

![22a392633d717676e1bbdd1794d6d73a.png][]

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

![c66a8e2d2b54e1792b36c66b5c6f3962.png][]

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

![9495fede17252f6aa6cc2204c2b15b8e.png][]

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

通过上面确认【广告请求数据备份表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方式！

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

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

先在测试数据库创建一个与现网【广告请求数据备份表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”问题

切换到mysql的data目录下，执行删除命令：

rm -rf t\_ad\_req\_log\_back.\*

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

![5412bb6eb3f21975d8655ffba802145d.png][]

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

lsof -n|grep deleted

发现删除程序还存活

![f0363499308c8e3703254d7d929773fe.png][]

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

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

![b8de6542a727d3cd4a5bfb03f00740d6.png][]

于是想继续建表，发现报错，报错信息一直是“Table \`t\_ad\_req\_log\_back\` already exists”

![79546ea4c31b681a76fe58ed44597747.png][]

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的目录下，发现表存在了

![543dc817ccd26e66d74c6be2b8b67533.png][]

执行desc命令发现又报错“Table \`t\_ad\_req\_log\_back\` already exists”，这是怎回事呢？

再一看用户权限不对，

![f970e65771f5c2697a3492f0aea53435.png][]

改变用户权限

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了

![ca9efbac57d3736b2d301e249e678f6d.png][]

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

![Image 1][]

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

[3c341da786ee02af13ae5a916fbd58c7.png]: https://img-blog.csdnimg.cn/img_convert/3c341da786ee02af13ae5a916fbd58c7.png
[22a392633d717676e1bbdd1794d6d73a.png]: https://img-blog.csdnimg.cn/img_convert/22a392633d717676e1bbdd1794d6d73a.png
[c66a8e2d2b54e1792b36c66b5c6f3962.png]: https://img-blog.csdnimg.cn/img_convert/c66a8e2d2b54e1792b36c66b5c6f3962.png
[9495fede17252f6aa6cc2204c2b15b8e.png]: https://img-blog.csdnimg.cn/img_convert/9495fede17252f6aa6cc2204c2b15b8e.png
[5412bb6eb3f21975d8655ffba802145d.png]: https://img-blog.csdnimg.cn/img_convert/5412bb6eb3f21975d8655ffba802145d.png
[f0363499308c8e3703254d7d929773fe.png]: https://img-blog.csdnimg.cn/img_convert/f0363499308c8e3703254d7d929773fe.png
[b8de6542a727d3cd4a5bfb03f00740d6.png]: https://img-blog.csdnimg.cn/img_convert/b8de6542a727d3cd4a5bfb03f00740d6.png
[79546ea4c31b681a76fe58ed44597747.png]: https://img-blog.csdnimg.cn/img_convert/79546ea4c31b681a76fe58ed44597747.png
[543dc817ccd26e66d74c6be2b8b67533.png]: https://img-blog.csdnimg.cn/img_convert/543dc817ccd26e66d74c6be2b8b67533.png
[f970e65771f5c2697a3492f0aea53435.png]: https://img-blog.csdnimg.cn/img_convert/f970e65771f5c2697a3492f0aea53435.png
[ca9efbac57d3736b2d301e249e678f6d.png]: https://img-blog.csdnimg.cn/img_convert/ca9efbac57d3736b2d301e249e678f6d.png
[Image 1]: