搜索
查看: 1639|回复: 0

【MySql】DROPTABLE操作以及DROP大表时的注意事项

[复制链接]

183

主题

8

回帖

820

积分

高级会员

积分
820
发表于 2014-10-29 17:03:29 | 显示全部楼层 |阅读模式

语法:

删表

  1. DROP TABLE Syntax
  2. DROP [TEMPORARY] TABLE [IF EXISTS]
  3.     tbl_name [, tbl_name] ...
  4.     [RESTRICT | CASCADE]
复制代码
可一次删除一张或多张表。需具有所删除表上的DROP权限。表定义文件和数据文件均被移除。表被删除后表上的用户权限不会被自动删除。参数里表中指定的表名不存在则报错,但对于存在的表仍会删除。可通过指定IF EXISTS阻止表不存在时引发的错误(此时对于不存在的表仅产生一个NOTE)。对于分区表,除了移除表定义,分区、数据外还移除与之关联的分区定义文件(.par)。在MySQL5.6中参数[RESTRICT | CASCADE]不做任何事情。[TEMPORARY] 关键字表明只删临时表,语句不会结束正在进行的事务(MySQL中DDL语句会隐式提交),不会进行权限检查。

删库

  1. DROP DATABASE Syntax
  2. DROP {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 删除。

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

使用InnoDB引擎且指定innodb_file_per_table=ON时在数据目录中与该表对应的有如下两个文件,分别为表定义文件和数据文件。
  1. sudo ls  -lh /data/mysql/testdb
  2. -rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm
  3. -rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd
复制代码
该表有100G这么大,直接使用DROP TABLE来完成删表动作,那么这条语句要执行很长时间。此时便可以通过在该表对应的数据文件上设置硬链接来进行删除。
  1. sudo ln /data/mysql/testdb/example_table.ibd /data/mysql/testdb/example_table.ibd.hdlk
  2. sudo ls  -lh /data/mysql/testdb
  3. -rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm
  4. -rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd
  5. -rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk
复制代码
发现多了一个example_table.ibd.hdlk文件,且example_table.ibd.hdlk和example_table.ibd的innode均为2。也即当有多个文件名(如硬链接)指向同一innode时,这个innode的引用数大于1,此时,删除其中任何一个文件名都只会删除指向innode的指针而并不会直接删除物理文件块,因此会非常快,直至innode的引用计数等于1时才会真正删除对应的物理文件块,真正删除物理文件块时才会比较耗时。

在建立了硬链接后再执行DROP TABLE操作:
  1. DROP TABLE example_table;
复制代码
发现会很快的完成,查看对应的表定义和数据文件:
  1. sudo ls  -lh /data/mysql/testdb
  2. -rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk
复制代码
只剩下example_table.ibd.hdlk,且innode的引用计数变为了1。也即刚才的DROP TABLE操作实施删除了物理文件的一个指针example_table.ibd ,因而非常快。

剩下的任务就是删除真正的物理文件了,因为此时innode的引用计数已经变为了1,直接删除example_table.ibd.hdlk便会真正的删除物理文件。但因为物理文件较大,删除大文件仍会引起较高的磁盘IO开销。因此可以使用少量逐次删除的方式来删除大的数据文件。truncate工具可以用于增加或缩减指定文件的尺寸,可以用于此目的:
  1. for i in `seq 100 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/mysql/testdb/example_table.ibd.hdlk;done
  2. sudo rm -rf /data/mysql/testdb/example_table.ibd.hdlk;
复制代码
从100G开始,每次缩减1G,停2秒,继续,直到文件只剩1G,最后使用rm命令删除剩余的部分。

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


您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

 
 
大数据行业交流
大数据行业交流
大数据求职招聘
大数据求职招聘
站长电话:
15010106923
微信联系:
hb-0310
站长邮箱:
ab12-120@163.com
大数据中国微信

QQ   

版权所有: Discuz! © 2001-2013 大数据.

GMT+8, 2024-4-30 20:20 , Processed in 0.060960 second(s), 24 queries .

快速回复 返回顶部 返回列表