drop table if exists 多张表_mysql实验--DROP 大表时的注意事项

本文介绍了在MySQL中删除大表时的注意事项,特别是使用DROP TABLE IF EXISTS命令可能导致的问题及解决方案。通过创建硬链接来加速大表的删除过程,以减少对其他操作的影响。此外,文章提供了删除大文件的策略,以降低磁盘IO开销。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

概述

我们很多时候都会去drop一些大表,特别是生产环境做操作时,这里主要提一些注意事项,仅供参考。


01

相关语法

1、删表

DROP TABLE SyntaxDROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

可一次删除一张或多张表。需具有所删除表上的DROP权限。表定义文件和数据文件均被移除。表被删除后表上的用户权限不会被自动删除。参数里表中指定的表名不存在则报错,但对于存在的表仍会删除。可通过指定IF EXISTS阻止表不存在时引发的错误(此时对于不存在的表仅产生一个NOTE)。

对于分区表,除了移除表定义,分区、数据外还移除与之关联的分区定义文件(.par)。

在MySQL5.6中参数[RESTRICT | CASCADE]不做任何事情。[TEMPORARY] 关键字表明只删临时表,语句不会结束正在进行的事务(MySQL中DDL语句会隐式提交),不会进行权限检查。

2、删库

DROP DATABASE SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name...

删除指定库中的表之后删除库。需具有库上的DROP权限。库被删除后库上存在的用户权限不会被自动删除。IF EXISTS用于阻止库名不存在时引起的错误。库被删除后默认库会被重置。若在使用了符号链接的库上执行DROP DATABASSE 连接和原始数据库都会被删除。命令返回被移除的表数量。

该命令会从指定的数据库目录中移除常规操作时MySQL自己产生的文件和目录,如:.BAK .DAT .HSH .MRG. MYD .MYI .TRG .TRN .db.frm .ibd .ndb .par,若存在db.opt也同样会删除。若数据库目录中存在其他非MySQL本身产生的文件或目录,则整个数据库目录不会被移除。此时,需手动清理剩余的文件并重新运行DROP DATABASE语句。

删除数据库并不会移除库中创建的临时表。临时表在SESSION结束时自动被清理或者显示的通过DROP TEMPORARY TABLE 删除。


02

删除大表的注意事项

对于表的删除,因为InnoDB引擎会在table cache层面维护一个全局独占锁一直到DROP TABLE完成为止,这样,对于表的其他操作会被HANG住。对于较大的表来说,DROP TABLE操作可能需要很长的时间,因此需要一种有效的办法来提升大表的删除速度,以尽可能降低HANG住的时间。这里可以通过设置硬链接来达到此目的。

这里用一个简单实验来测试一下:

1、创建测试表

表名:no_part_tab,1.8亿数据量,表大小5.4G。

delimiter //CREATE PROCEDURE load_no_part_tab()begindeclare v int default 0; while v < 1000000 do insert into no_part_tab values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while; end //mysql> delimiter ;mysql> call load_no_part_tab();mysql> insert into no_part_tab select * from no_part_tab;.....mysql> create table t2 as select * from no_part_tab nologging;
1155b98635e435fec15e5d0f5c9e716c.png

使用InnoDB引擎且指定innodb_file_per_table=ON时在数据目录中与该表对应的有如下两个文件,分别为表定义文件和数据文件。

ee94ddac1e2208dbd13ee613adb6be6d.png
27b2a79708577e12a1934a5d6093127d.png

2、drop table

直接使用DROP TABLE来完成删表动作,正常这条语句要执行很长时间。因为数据量只有5G,所以不是很明显。(这台服务器空间不够了,所以数据量没加上去..)

7ed427a5ba492315fd478d20c1775a8c.png

3、建立硬链接

此时便可以通过在该表对应的数据文件上设置硬链接来进行删除。

#ln /data/datafile/test/no_part_tab.MYD /data/datafile/test/no_part_tab.MYD.hdlk#ls -lh /data/datafile/test/
5f7de169b9606a99421774e7b681a6a0.png

发现多了一个sys_user.ibd.hdlk文件,且no_part_tab.MYD.hdlk和no_part_tab.MYD的innode均为2。也即当有多个文件名(如硬链接)指向同一innode时,这个innode的引用数大于1,此时,删除其中任何一个文件名都只会删除指向innode的指针而并不会直接删除物理文件块,因此会非常快,直至innode的引用计数等于1时才会真正删除对应的物理文件块,真正删除物理文件块时才会比较耗时。

4、drop table

在建立了硬链接后再执行DROP TABLE操作:

DROP TABLE no_part_tab;
a13c0288e22116d84d34540abf353780.png

可以看到基本没耗时,查看对应的表定义和数据文件:

49084e53e1d3b00c2561eef6b9cac55c.png

只剩下no_part_tab.MYD.hdlk,且innode的引用计数变为了1。也即刚才的DROP TABLE操作实施删除了物理文件的一个指针no_part_tab,因而非常快。

5、删除真正的物理文件

因为此时innode的引用计数已经变为了1,直接删除no_part_tab.MYD.hdlk便会真正的删除物理文件。但因为物理文件较大,删除大文件仍会引起较高的磁盘IO开销。因此可以使用少量逐次删除的方式来删除大的数据文件。truncate工具可以用于增加或缩减指定文件的尺寸,可以用于此目的:

#for i in `seq 6 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/datafile/test/no_part_tab.MYD.hdlk;done#rm -rf /data/datafile/test/no_part_tab.MYD.hdlk;

从6G开始,每次缩减1G,停2秒,继续,直到文件只剩1G,最后使用rm命令删除剩余的部分。

a94baff441037f82789973a82f00382c.png

如果是整个数据库的删除可以先删除其中较大的表,最后在执行DROP DATABASE删除整个库,对大表的删除可参见上面的方法。


后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下!

3d3233718ed6597f220eafb778338f95.png
### MySQL 数据库基本操作教程 #### 创建数据库 要创建一个新的数据库,可以使用 `CREATE DATABASE` 语句。如果希望避免重复创建已有的数据库,则可以在命令中加入 `[IF NOT EXISTS]` 子句。 ```sql CREATE DATABASE IF NOT EXISTS example_db; ``` 这行代码会尝试创建名为 `example_db` 的新数据库;如果有同名数据库存在则不会执行任何操作[^2]。 #### 删除数据库 当不再需要某个特定的数据库,可以通过下面这条 SQL 语句将其移除: ```sql DROP DATABASE IF EXISTS example_db; ``` 此命令用于删除指定名称的数据库实例及其内部的所有对象(如格)。同样地,通过添加 `[IF EXISTS]` 可防止因目标不存在而引发错误。 #### 查看现有数据库列 为了获取当前服务器上所有可用数据库的信息,可运行如下查询: ```sql SHOW DATABASES; ``` 该指令返回一个包含各个数据库名字的结果集,便于用户了解环境中有哪些资源可供访问。 #### 使用选定数据库 一旦建立了所需的数据库之后,就需要告诉 MySQL 客户端程序应该在哪一个上下文中工作。为此目的服务的就是 `USE` 关键字: ```sql USE `example_db`; ``` 这里建议总是采用反引号包裹住数据库的名字,以防万一它包含了特殊字符或是保留关键字。 #### 创建数据 定义好要用的工作区——即选择了具体哪个数据库以后,就可以着手建立存储实体信息的数据结构了。一张典型的关系型格由若干字段组成,每一个都代一类属性并规定其允许取值范围以及约束条件等特性。 ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password CHAR(64) ); ``` 上述脚本片段展示了如何构建一个简单的用户账户记录集合,其中设置了自增主键机制以确保每条记录都有唯一的标识符。 #### 修改已有结构 随着项目的发展变化,有可能需要调整之前设定好的模式。这便需要用到诸如增加/减少列、更改类型或设置默认值之类的 DDL(Data Definition Language) 功能。 ```sql ALTER TABLE users ADD COLUMN email VARCHAR(100); ``` 这段示例说明怎样向现有的 `users` 追加一栏电子邮件地址输入框。 #### 移除整个单 最后,在某些情况下或许有必要彻底清除某张连同里面保存的一切内容一起销毁掉。此应当谨慎行事,并确认确实不需要那些历史遗留下来的东西后再采取行动。 ```sql DROP TABLE IF EXISTS old_table_name; ``` 以上就是针对 MySQL 中有关于管理数据库和操纵的一些基础知识点介绍。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值