mysql超过1t表删除_优雅的drop掉mysql库中1TB大表

最新推荐文章于 2023-07-15 22:09:21 发布

未知方程无解

最新推荐文章于 2023-07-15 22:09:21 发布

阅读量422

点赞数

文章标签： mysql超过1t表删除

本文链接：https://blog.csdn.net/weixin_31419153/article/details/113383340

版权

删除MySQL中1TB的大表可能导致长时间的业务中断甚至数据库崩溃。本文探讨了`drop table`操作背后的原理，包括Buffer Pool的清理和磁盘数据文件的删除，并提出了优化方案。建议通过设置`innodb_buffer_pool_instances`和关闭AHI（Adaptive Hash Index）来优化Buffer Pool，利用Linux硬链接来快速删除物理文件，并使用`truncate`命令优雅地分布删除大文件，确保在业务低峰期执行这些操作。

摘要由CSDN通过智能技术生成

要是问大家，知道怎么从mysql数据库中drop掉业务表，很多人肯定会说，so easy，用drop table t_test语句不就完事了，这是初生牛犊不怕虎，你要是如此简单，去线上业务库中drop掉一张1TB大小的表，造成长时间的业务无法访问数据库，更严重，导致数据库崩溃，宕机都是可能的。

下面就先聊聊，drop table语句背后的事情，语句执行之后，主要做2两件事情

1、清除Buffer Pool缓冲

在drop table时，innodb引擎会清理该表在每个buffer pool实例中中对应的数据块页面，为了避免对系统的影响，这里的清除操作并不是真正的flush，而是将涉及到的页面从flush队列中摘除。但在摘除过程中，删除进程会持有每个buffer pool的全局锁，然后搜索这个buffer pool里对应的页面以便从flush list中删除。如果在buffer pool中需要被搜索并删除的页面过多，那么遍历时间就会增大，这就导致了其他事务操作被阻塞，严重时可导致数据库夯住。

在这里还需要注意一件事情，如果数据库的buffer pool设置很大，就会导致遍历时间变长

清理buffer pool时，还包含清理AHI包含此表的数据，AHI的功能在这里就不多说了，主要是当b+tree的层级变高时，为避免b+tree逐层搜索，AHI能根据某个检索条件，直接查询到对应的数据页，跳过逐层定位的步骤。其次AHI会占用1/16的buffer pool的大小，如果线上表数据不是特别大，不是超高并发，不建议将开启AHI，可以考虑关闭AHI功能

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';+----------------------------+-------+| Variable_name | Value || innodb_adaptive_hash_index | ON |1 row in set (0.01 sec)mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;Query OK, 0 rows affected (0.00 sec)| innodb_adaptive_hash_index | OFF |

2、删除对应的磁盘数据文件ibd

在删除数据文件时，如果数据文件过大，删除过程会产生大量的IO并耗费更多的时间，造成磁盘IO开销飙升,CPU负载过高，影响其他程序运行。我的一个好伙伴，就曾在线上库删除了一张1TB大小的表，结果20分钟，数据库无响应，最后库崩溃，重启了。

既然知道drop table做了2件事情，那就针对以上2个事情进行优化

在清除Buffer Pool缓冲上，为减少当个buffer pool的大小，可以合理设置innodb_buffer_pool_instances参数，减少buffer pool数据块列表扫描时间，同时关闭AHI功能

在步骤2上，可以巧妙地利用linux的硬连接特性，延迟删除真正的物理文件。

首先看看linux系统的硬链接示意图

当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1,删除其中任何一个文件名都会很快.因为其直接的物理文件块没有被删除.只是删除了一个指针而已;当INODE的引用数N=1时,删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;

如果给数据库表的.ibd文件创建一个硬链接，当删除表时，删除物理文件时，其实删除的就是物理文件的一个指针，所以删除操作响应速度会非常快，大约不到1秒左右

下面就来演示一下具体的操作

先创建表文件的硬链接ln t_test.ibd t_test.ibd.bak删除表drop table t_test;

最后就是要真正删除掉物理文件，释放文件所占用的磁盘空间，那么问题来了，如果优雅的删除物理文件呢，在这里推荐大家coreutils工具集中的truncate命令

当然需要你先安装相关的软件包

wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.29.tar.xz使用非root进行解压tar -xvJf coreutils-8.29.tar.xzcd coreutils-8.29./configuremake使用root进行make install

安装好之后，就可以写一个脚本，非常优雅的分布删除大文件，${i}G 表示，每次删除10G

#!/bin/bashTRUNCATE=/usr/local/bin/truncatefor i in `seq 2194 -10 10 `;dosleep 2$TRUNCATE -s ${i}G /data/mysql/t_test.ibd.hdlkdonerm -rf /data/mysql/t_test.ibd.hdlk ;

最后，给大家一个建议，不要在业务高峰期做drop table操作，一定要在业务低峰期做。