MySQL删除大表实践(大于150GB)

实际情况概述

生产环境数据库磁盘快满了,此时:

  1. 需要完全删除一个记录历史数据的大表(约160GB);
  2. 需要将一张大表中7月之前的数据全部删除。

基础知识

MySQL中,drop和truncate是DDL(data define language)操作,操作立即生效,原数据不放到 rollback segment中,无法回滚;而delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务可以回滚。执行效率方面,drop>truncate>delete。

问题解决

完全删除一个大表

我的实际情况是,要删除的表很大(约160GB),如果使用delete可能要执行数天,且删除表中数据后需要保留表结构,所以选择使用truncate table即可。

sql

1
truncate table huge_table;

将大表中指定日期之前的数据删除

由于表很大,排除delete方案(可能要执行数天)。可以分三步进行:

  1. 把原表重命名为备份表;
  2. 创建一个表结构和原表一样的新表;
  3. 提取原表中需要保留的数据,保存到新表中;
  4. 对备份表进行truncate。

sql

1
2
3
4
5
6
7
8
9
-- 原表重命名为备份表
rename table huge_table to huge_table_backup;
-- 创建新表
CREATE TABLE huge_table like huge_table_backup;
-- 提取需要的数据插入
INSERT INTO huge_table SELECT * FROM huge_table_backup where CREATE_TIME >= "2020-10-01 00:00:00";
INSERT INTO huge_table SELECT * FROM huge_table_backup where CREATE_TIME >= "2020-09-01 00:00:00" and CREATE_TIME < "2020-10-01 00:00:00";
-- truncate备份表
truncate table huge_table_backup;

注意点

  • insert into select曾经发生过生产事故,使用时要先看下执行计划,保证where条件命中索引,避免全表扫描
  • 如果有外键约束,truncate前需要禁用外键检查 SET foreign_key_checks = 0;
  • 我的实际需求是要保留9月以后至今的数据(当前日期为10月25日),为了避免直接直接插入10月后的数据量过大发生异常,因此把insert语句分成两次执行,先保存10月再保存9月的(此条有待商榷,欢迎讨论)。

后记

  • 结果还是出事了,由于害怕truncate160G大表的操作出事,把truncate操作放在了最后,而磁盘剩余空间只有20G,光十月的数据量就有1.2亿,导致还没执行到truncate这一步磁盘就满了,结果insert语句执行超时,报了超出max_binlog_cache_size的异常;
  • 后来把truncate160G大表的操作放在最前面执行(很快,2分钟不到就执行完成了),每次insert的数据量再细化,改为每天插入一次,约400W条数据,终于执行成功(整个过程耗时约3小时)。

总结

  • 在磁盘空间不多的情况下,需要insert大量数据,则必须先清理出足够的磁盘空间,防止超出max_binlog_cache_size;
  • insert大量数据时要结合具体数据量进行细分,切不可一次全部insert。
  • 出问题未必是坏事,有时通过问题可以更快地成长,经过这次发布事故,自己的头发少了一点,知识又多了一点。
0%