您可以点击原文链接以获得更佳阅读体验:
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
实际情况概述
生产环境数据库磁盘快满了,此时:
- 需要完全删除一个记录历史数据的大表(约160GB);
- 需要将一张大表中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方案(可能要执行数天)。可以分三步进行:
- 把原表重命名为备份表;
- 创建一个表结构和原表一样的新表;
- 提取原表中需要保留的数据,保存到新表中;
- 对备份表进行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。
- 出问题未必是坏事,有时通过问题可以更快地成长,经过这次发布事故,自己的头发少了一点,知识又多了一点。