mysql大表如何删除_MySQL如何优雅的删除大表

喜欢ヅ旅行 2023-01-11 13:02 164阅读 0赞

前言

删除表，大家下意识想到的命令可能是直接使用DROP TABLE "表名"

，这是初生牛犊的做法，因为当要删除的表达空间到几十G，甚至是几百G的表时候。这样一条命令下去，MySQL可能就直接夯住了，外在表现就是QPS急速下降，客户请求变慢。

解决办法

1.业务低峰时间手动执行删除

这个可能就需要DBA不辞辛劳，大晚上爬起来删表了。

2.先清除数据，最后再删除的方式

譬如1000万条数据，写脚本每次删除20万，睡眠一段时间，继续执行。这样也能做到对用户无感知。

3.对表文件(idb文件)做一个硬链接来加速删除

这个方法利用了linux下硬链接的知识，来进行快速删除，不记得话可以回去翻一下《鸟哥的linux私房菜》ln data\_center\_update\_log.ibd data\_center\_update\_log.ibd.hdlk

\[root@mysql01 sports\_center\]\# ll

总用量 19903792

\-rw-r----- 1 mysql mysql       9076 10月 17 13:15 data\_center\_update\_log.frm

\-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data\_center\_update\_log.ibd

\-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data\_center\_update\_log.ibd.hdlk执行上面命令后，我们就多了一个data\_center\_update\_log.ibd.hdlk文件。此操作实际上不会占用磁盘空间，只是增加了一次对磁盘上文件的引用。

当我们删除其中任何一个文件时，都不会影响磁盘上真实的文件，只是将其引用数目减去1。当被引用的数目变为1的时候，再去删除文件，才会真正做IO来删除它。

正是利用这个特点，将由原来mysql来删除大文件的操作，转换为一个简单的操作系统级的文件删除，从而减少了对mysql的影响。

4.登陆mysql，执行drop表操作很快，200万条数据只用了1秒完成，此操作是在创建硬链接后执行的

mysql> drop tables data\_center\_update\_log;

Query OK, 0 rows affected (1.02 sec)

mysql> exit

Bye

退出来，再次查看数据目录，发现就只剩data\_center\_update\_log.ibd.hdlk硬链接文件了

\[root@mysql01 sports\_center\]\# ll

总用量 19903792

\-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data\_center\_update\_log.ibd.hdlk

5.如何正确删除ibd.hdlk

硬链接文件呢虽然drop table

之后，剩下的硬链接文件已经和mysql没有关系了。但如果文件过大，直接用rm

命令来删除，也是会造成IO开销飙升，CPU负载过高，进而影响到MySQL。

这里我们用到的方法，可以循环分块删除，慢慢地清理文件，通过一个脚本即可搞定

Truncate命令通常用于将文件缩小或扩展到指定的大小。如果文件大于指定的大小，则会丢失额外的数据。如果文件较短，则会对其进行扩展，并且扩展部分的读数为零字节。

5.1 安装truncate

命令\[root@mysql01 ~\]\# cruncate

\-bash: cruncate: 未找到命令

通常操作系统会安装truncate命令，该命令在coreutils安装包里面，如果没有安装可以使用下面命令安装

\[root@mysql01 ~\]\# yum provides truncate

coreutils-8.22-24.el7.x86\_64 : A set of basic GNU tools commonly used in shell scripts

源    ：base

匹配来源：

文件名    ：/usr/bin/truncate

可以看到truncate由coreutils安装包提供，下面安装coreutils安装包：

\[root@mysql01 ~\]\# yum install -y coreutils

5.2 truncate

常用选项-c, --no-create --> 不创建任何文件

\-o, --io-blocks --> 将大小视为存储块的数量，而不是字节

\-r, --reference=RFILE --> 参考指定的文件大小

\-s, --size=SIZE --> 按照指定的字节设置文件大小

5.3 truncate\_bigfile.sh脚本原理：使用truncate -s

选项可以指定文件大小，通过脚本指定每次文件减少的大小，并sleep睡眠一定时间，从而达到可控的删除文件

附：truncate\_bigfile.sh脚本\#! /bin/bash

\#

TRUNCATE=/usr/bin/truncate

FILE=$1

if \[ x"$1" = x \];then

echo "Please input filename in"

exit 1;

else

SIZE\_M=$(du -sm "$1" | awk '\{print $1\}')

for i in $(seq "$\{SIZE\_M\}" -100 0)

do

sleep 1

echo "$\{TRUNCATE\} -s $\{i\}M $\{FILE\}"

$\{TRUNCATE\} -s "$\{i\}"M "$\{FILE\}"

done

fi

if \[  $? -eq 0 \];then

\\rm -f "$\{FILE\}"

else

echo "Please check file"

fi

因为有悔，所以披星戴月；因为有梦，所以奋不顾身！个人博客首发：easydb.net

微信公众号：easydb

关注我，不走丢！![cf5995cff25ae02e9f263c75a4bf3bef.png][]

[cf5995cff25ae02e9f263c75a4bf3bef.png]: /images/20221119/afb1c0067e284c1aa3c92b026c083fea.png

发表评论取消回复

表情：

评论列表（有 0 条评论，164人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql 如何删除数据库中所有的表

　　SELECT concat('DROP TABLE IF EXISTS ', table\_name, ';') 　　FROM information\_schema.t

àì夳堔傛蜴生んèń/ 2023年09月24日 10:27/ 0 赞/ 140 阅读

相关 MySql 如何优化大表

[MySQL 如何优化千万级大表][MySQL] [MySQL]: https://blog.csdn.net/yangjianrong1985/article/de

淩亂°似流年/ 2023年09月23日 17:54/ 0 赞/ 132 阅读

相关 mysql大表如何删除_MySQL如何优雅的删除大表

前言删除表，大家下意识想到的命令可能是直接使用DROP TABLE "表名" ，这是初生牛犊的做法，因为当要删除的表达空间到几十G，甚至是几百G的表时候。这样一条命令下去

喜欢ヅ旅行/ 2023年01月11日 13:02/ 0 赞/ 165 阅读

相关 mysql删除或者创建表_mysql创建和删除表

创建表简单的方式 CREATE TABLE person ( number INT(11), name VARCHAR(255), birthday DATE )

爱被打了一巴掌/ 2022年10月31日 12:25/ 0 赞/ 253 阅读

相关 mysql大表删除数据为什么慢_mysql innodb删除大主表数据过慢，单表数据过亿

问题描述 mysql innodb删除大主表数据过慢，主表nms\_managed\_resource，下面有个各种不同的几十张字表用外键关联到nms\_managed\_r

我会带着你远行/ 2022年10月26日 14:22/ 0 赞/ 228 阅读

相关 MySQL删除InnoDB大表

> 在使用MySQL时，如果有大表的存储引擎是InnoDB，并且系统参数innodb\_file\_per\_table设置为1，即每个文件对应一个独立的表空间，当对这些大表进

ゞ浴缸里的玫瑰/ 2022年09月08日 11:53/ 0 赞/ 268 阅读

相关 mysql表的删除

创建一个表 CREATE TABLE tb\_student( id INT PRIMARY KEY AUTO\_INCREMENT, sname VARCHAR(2

痛定思痛。/ 2022年06月16日 05:40/ 0 赞/ 167 阅读

相关如何优雅地删除Redis大键

如何优雅地删除Redis大键转载 2016年08月26日 15:59:49 关于Redis大键(Key)，我们从\[空间复杂性\]和访问它的

比眉伴天荒/ 2022年06月03日 08:00/ 0 赞/ 145 阅读

相关 mysql存储过程定时删除大表数据

1、查看Events是否开启 select @@event_scheduler; show variables like 'event_scheduler';

喜欢ヅ旅行/ 2022年03月16日 10:10/ 0 赞/ 327 阅读

相关 MySQL大表删除正确的姿势

1、背景在生产环境有可能有删除某个不重要大表的需求，因为大表占用的大量磁盘空间，如果我们直接drop掉此表，通常需要20秒以上的时间，总会觉得会卡主MySQL，现在给大家一

刺骨的言语ヽ痛彻心扉/ 2022年01月15日 08:21/ 0 赞/ 237 阅读