引起 MySQL 数据库性能抖动的原因有很多，比如大事务、定时批量查询等，而这些原因我们一般都会注意到。但是，有一个引起性能抖动的原因却经常被我们忽视，那就是在生产环境删除无用的大表，即 DROP TABLE。

一、为什么要 DROP TABLE？

生产环境中，为什么要 DROP TABLE？相信绝大部分原因是为了释放空间。

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

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

不过，当我们谈一个数据库多大的时候，单纯讨论存储容量意义并不大，我们更希望得到的是活跃记录有多少。

银行系统的 Oracle 数据库，虽然单库都可以数 T，但大部分都是日志数据或流水数据。

生产环境的流水表，单表容量可能几百 G，上 T，怎样在不影响业务的情况下，优雅地 DROP TABLE 呢 （这里优雅的定义为业务耗时上升不超过20%） ？

DROP TABLE 本来是一个非常简单的操作，但是要做到在生产环境中，对业务耗时影响很小，基本无感知，简直就是一件艺术。

或许大部分数据库系统都没有这么高的要求，但是海量并发的互联网业务有！

二、关于MySQL版本

在 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 拆分，5.7 版本支持多个脏页列表，因此对于上述 DROP TABLE 的处理，又得到进一步优化，即持有大锁的时间越来越少，对业务的影响也就越来越少。

（一）IO优化

上面的这些优化是针对内存中的锁进行优化，在 DROP TABLE 的过程中，让持有的大锁尽可能的时间缩短，从而避免 QPS 掉底的问题。

然而，想要对业务操作的耗时响应时间控制在 20%，还需要考虑物理 I/O 的影响。

例如，删除一张 100G 的表，意味着要删除 100G 的物理文件，在删除过程中，I/O 的开销也将会是非常巨大，从而影响业务的耗时。

常见优化的思想是在 DROP TABLE 前，对 ibd 文件创建硬链接，这样在 DROP TABLE 时，只会删除 ibd 文件，不会真正删除 ibd 文件，也就不会释放空间。

这样 DROP TABLE 的操作速度就能得到进一步提升。不过，这时存储空间并没有第一时间释放，还是需要选择低峰期删除文件，方能最终释放存储空间。

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

三、自适应哈希功能

自适应哈希（Adaptive Hash Index，下简称AHI）特性作为一个几乎透明的功能，其实一般用户无需关心，基本可以认为 AHI 是即开即用的功能。

默认 AHI 参数的设置也是比较合理的，例如参数 innodb_adaptive_hash_index_parts 设置为 8 。然而，AHI 存在一个副作用：当删除大表，且缓冲池（Buffer Pool，下简称 BP）比较大，如超过 32G，则 MySQL 数据库可能会有短暂被 hang 住的情况发生。

这时会对业务线程造成一定影响，从而导致业务系统的抖动。

产生这个问题的原因是在删除表的时候，InnoDB 存储引擎会将该表在 BP 中的内存都淘汰掉，释放可用空间。这其中包括数据页、索引页、自适应哈希页等。

当 BP 比较大时，扫描 BP 中 flush_list 链表需要比较长的时间，因此会产生系统的抖动。

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

• 业务低峰期删除大表；

• 删除表前禁用 AHI 功能；

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

• 删除表后启用 AHI 功能。

不过，这么麻烦的处理在 MySQL 8.0.23 版本之后，就都不再需要了。因为官方已经彻底修复了这个问题。

四、终极删除优化

除了前面的这些对于版本的选择、I/O 的优化、自适应哈希算法的调整，还可以有进一步减小删除大表时发生抖动的可能性。

当前 MySQL 数据库对于删除时内存的控制已经做的很好了，接下去的问题在于还需要进行 I/O操作，从磁盘上删除操作。ionice 等命令依然有 I/O 开销，性能抖动依然不可避免。

如果根据第一性原理，那是不是可以实现不删除表文件，也能释放存储空间呢？为什么要这么辛苦地去释放空间？只需要把原来的物理存储空间给新表使用不就好了吗？

更推荐在初始化时，就完成 ibdata1 的空间分配，即固定单个 MySQL 实例能使用到存储空间上限。这样可以省去后续磁盘空间的申请释放，进一步提升在业务高峰期，数据库耗时的平稳性。

innodb_file_per_table = 0

innodb_data_file_path=ibdata1:500G

大家可能觉得这样不够灵活，因为用户可能只想日志表、流水表等数据放在一个表空间中，这样 DROP TABLE 时不需要删除物理空间。

这样的话，可以使用 MySQL 中的通用表空间（General Tablespace）：

CREATE TABLESPACE `ts_log` ADD DATAFILE 'ts_log.ibd'


CREATE TABLE log_2020_01 ( ... ) tablespace = ts_log;


CREATE TABLE log_2020_02 ( ... ) tablespace = ts_log;

作者：我爱娃哈哈
链接：https://juejin.cn/post/7380179109290688550
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。