mysql如何优雅删除大表? 看这篇就够了

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中需要被搜索并删除的页面过多,遍历时间就会增大,就会导致其他事务操作被阻塞,严重时会导致数据库锁住。

具体过程如下图:

2023-03-24-vkq33X
2023-03-24-vkq33X

在删除数据文件时,如果数据文件过大,删除过程中会产生大量的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 多平台发布