关于数据库:mysql如何优雅删除大表-看这篇就够了

有次线上用 drop table xxx 删除 200G 的大表，导致 MySQL 连接数暴涨，业务呈现大量 5XX，“喜提”一个事变报告。看来还是疏忽了一行命令背地产生的“蝴蝶效应”，当初让咱们一起来具体扒一扒 drop table 背地的原理，以及怎么优雅的删除 MySQL 大表。

MySQL 的 drop 命令次要干了两件事件：

清理 buffer pool 数据块页面；
删除对应磁盘数据文件 ibd；

在 drop table 时，innodb 引擎会清理该表每个 buffer pool 实例中对应数据块页面。这里的革除操作并不是真正的 flush，而是将波及到的页面从 flush 队列中去除。在去除过程中，删除过程会持有每个buffer pool 的全局锁，而后搜寻这个 buffer pool 里对应的页面以便从 flush list 中删除。如果在 buffer pool 中须要被搜寻并删除的页面过多，遍历工夫就会增大，就会导致其余事务操作被阻塞，重大时会导致数据库锁住。

具体过程如下图：

在删除数据文件时，如果数据文件过大，删除过程中会产生大量的 IO，造成磁盘 IO 飚升，CPU 负载过高。

如果线上须要删除 100G 以上的大表，倡议依照上面的步骤进行：

rename table: 防止删错了表导致业务受影响，先 rename 下如果有问题能够很快改回来；须要确保 rename 的表没有事务在运行，否则会阻塞；
```
rename table xxx to xxx;
```
表做硬链接：防止产生高 IO，影响数据库性能；当有多个文件名同时指向同一个 inode 时，删除其中任何一个文件名都很快，因为其间接的物理文件块没有被删除，只是删除了一个指针而已；
```
ln xxx.ibd  xxx.ibd.bak
```
drop table: 在业务低峰期执行；
```
drop table xxx;
```
删除物理文件：100G 文件，每秒删除 1G 内容，暂停 1s，直到最初文件只剩下 1G，删除文件。能够在业务低峰期执行。
```
$  for i in `seq 100 -1 1 ` ;   do sleep 1;  truncate  -s  ${i}G  xxx.ibd.bak; done 
$  rm  -f  table_test.ibd.bak
```

通过下面步骤，曾在线上删除 500G 的大表做到了数据库各项性能指标很安稳，业务无感知。

本文由 mdnice 多平台公布

MySQL 大表删除

原理

优雅删除大表