优雅地删除生产环境中的大表

DROP TABLE

• 生产环境大多数是已经确定的库表，一般不会进行 DROP TABLE 这么重的操作，甚至大部分DBA 都不应该拥有 DROP 权限。

• 随着数据无限增长，在某些系统中，需要将数据归档到历史数据库，删除生产数据库中的表，从而释放存储空间，典型的就是偏日志、流水类的数据。

• “优雅的删除”定义为业务耗时上升不超过20%

优化

版本优化

在 MySQL 5.5.23 版本前

• 当删除一张表时，会去遍历 BP（Buffer Pool 缓冲池）中所有该表对应的页，这时需要持有 BP 的互斥锁，而这是一把巨大的锁，对于 BP 的访问都需要持有该锁才能继续。

• 随着硬件的发展，128G、256G 的内存都已是常态，若 BP 非常大，如 100G，则遍历 BP 所需要的时间就会非常长，可能是 5 秒，甚至更长。这时，业务对于数据库的请求会直接掉底，直接变为 0。显然，这不符合我们对于优雅的定义。

从MySQL 5.5.23 版本开始

• 遍历脏页列表（flush list）时，只持有一小段时间，之后就释放 BP 的大锁，并且不需要真正刷新脏页，这样的处理能让其他业务请求有机会获得大锁的可能，从而业务的请求不会掉底；

• 不处理 LRU 列表中干净的页。因为 LRU 中不使用的页会慢慢被淘汰，因此删除表，不用同步地去处理对应的页，可以通过 LRU 机制，异步最终淘汰机制。

MySQL 5.6 版本支持了多个 BP 拆分

MySQL 5.7 版本支持多个脏页列

I/O 优化

在 DROP TABLE 前，对 ibd 文件创建硬链接，这样在 DROP TABLE 时，只会删除 ibd 文件，不会真正删除 ibd 文件，也就不会释放空间。这样 DROP TABLE 的操作速度就能得到进一步提升。不过，这时存储空间并没有第一时间释放，还是需要选择低峰期删除文件，方能最终释放存储空间。

但同样的问题，虽然异步删除物理文件，但又如何做到最业务的耗时影响最小呢？这时，可以考虑使用 ionice 这个工具，选择在业务低峰期进行 ibd 文件的删除ionice -c 2 -n 6 rm xxx.ibd

自适应哈希算法的调整

一般用户无需关心，基本可以认为 AHI 是即开即用的功能。

默认 AHI 参数的设置也是比较合理的，例如参数 innodb_adaptive_hash_index_parts 设置为 8

AHI 存在一个副作用

当删除大表，且缓冲池（Buffer Pool，下简称 BP）比较大，如超过 32G，则 MySQL 数据库可能会有短暂被 hang 住的情况发生。会对业务线程造成一定影响，从而导致业务系统的抖动。

原因

在删除表的时候，InnoDB 存储引擎会将该表在 BP 中的内存都淘汰掉，释放可用空间。这其中包括数据页、索引页、自适应哈希页等。当 BP 比较大时，扫描 BP 中 flush_list 链表需要比较长的时间，因此会产生系统的抖动。

解决

MySQL 8.0.23 版本之前

在海量的互联网并发业务中，删除表操作需要做精细的逻辑控制

• 业务低峰期删除大表

• 删除表前禁用 AHI 功能

• 控制脏页链表长度，只有长度小于一定阈值，才发起删除操作

• 删除表后启用 AHI 功能

MySQL 8.0.23 版本之后，官方已彻底修复这个问题

终极删除优化

在生产系统上，将表存储在共享表空间文件中，这时删除表只是将存储空间释放，并不删除表空间文件本身