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

您可以点击原文链接以获得更佳阅读体验：
http://www.prchen.com/2020/10/27/MySQL%E5%88%A0%E9%99%A4%E5%A4%A7%E8%A1%A8%E5%AE%9E%E8%B7%B5(%E5%A4%A7%E4%BA%8E150GB)/#more

实际情况概述

生产环境数据库磁盘快满了，此时：

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

truncate table huge_table;

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

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

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

sql

-- 原表重命名为备份表
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。
• 出问题未必是坏事，有时通过问题可以更快地成长，经过这次发布事故，自己的头发少了一点，知识又多了一点。