MySQL大表清空和删除正确方法
MySQL大表清空和删除正确方法
公司主营业务:成都网站设计、成都网站制作、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出桦南免费做网站回馈大家。
1 清空大表
1.1 truncate
删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行,而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。
truncate 清空表数据,50G的表大概需要20秒以上;
mysql> truncate table old_table;
1.2 rename
1.2.1 格式:
mysql> rename table 原表名 to 新表名;
当你执行 RENAME 时,你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限,以及对新表的 CREATE 和 INSERT 权限。
1.2.2 实例
# 新建一个空表(测试);
mysql> create table old_table(id int);
# new_table克隆old_table的表结构;
mysql> create table new_table like old_table;
# old_table改名为backup_table,new_table改名为old_table;
mysql> RENAME TABLE old_table TO backup_table, new_table TO old_table;
# 删除旧表及数据;
mysql> DROP TABLE old_table;
2 删除
2.1 背景
在生产环境有可能有删除某个不重要大表的需求,因为大表占用的大量磁盘空间,如果我们直接drop掉此表,通常需要20秒以上的时间,总会觉得会卡主MySQL,现在给大家一个正确的删除方法。
2.2 首先我们查看此大表
# shell
[root@ras221 db]# du -shc old_table*
12K old_table.frm
49G old_table.ibd
[root@ras221 db]# ls -l old_table*
-rw-r----- 1 mysql mysql 9075 11月 8 11:39 old_table.frm
-rw-r----- 1 mysql mysql 52273610752 1月 10 14:12 old_table.ibd
[root@ras221 db]# ln old_table.ibd old_table.ibd.bak # 硬链接;
[root@ras221 db]# ls -l old_table*
-rw-r----- 1 mysql mysql 9075 11月 8 11:39 old_table.frm
-rw-r----- 1 mysql mysql 52273610752 1月 10 14:12 old_table.ibd
-rw-r----- 1 mysql mysql 52273610752 1月 10 14:12 old_table.ibd.bak
[root@ras221 db]#
# mysql
mysql> drop table old_table;
Query Ok, 0 rows affacted(0.92 sec)
通过这样的操作,可以减少mysql drop大表hang住的时间,然后在业务低峰期再去删除真实的那个.b文.bak件。
至于原理:
就是利用OS HARD LINK的原理,
当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名都会很快.
因为其直接的物理文件块没有被删除.只是删除了一个指针而已;
当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;
对于大表操作操作的其实几个Tips:
可以先用rename table替代drop table
mysql> rename table old_table to old_table_bak;
这个动作也很快只是相当于改一个文件名。
另外也可以考虑使用XFS文件系统,对于Drop表动作也比较快。
本文题目:MySQL大表清空和删除正确方法
当前路径:http://scyanting.com/article/ppieoc.html