AliSQL大量数据删除最佳实践

问题描述

在数据库运维中，删除表中部分数据、清空表或删除整个表是常见的操作。对于小规模的数据集，这些操作对数据库实例的影响可以忽略。然而，当涉及数百万乃至上亿行记录，或者数据文件达到几百GB甚至数TB时，这类操作可能会引发如下严重问题。

实例卡顿或HA切换

• 问题1：Binlog提交阶段，在单个大事务中删除大量数据时，生成的临时Binlog文件可能达到数十GB甚至数百GB。提交这些Binlog时会阻塞其他写事务，导致短暂卡顿或长时间延迟，严重时会因HA探测超时引发主备切换。

• 问题2：AHI清理阶段，InnoDB需要遍历Buffer Pool清理相关AHI记录，此过程需持有全局锁dict_sys->mutex，耗时较长。详情请参见MySQL官方说明（MySQL Bugs: #91977: Dropping Large Table Causes Semaphore Waits; No Other Work Possible）。

• 问题3：文件删除阶段，大表文件的删除可能导致实例瞬间及后续一段时间内IO资源被完全占用，影响正常请求的处理。

实例抖动

问题4：文件删除阶段，InnoDB会遍历Buffer Pool以清理该表的相关页面。若buffer_pool_size非常大，清理过程将耗时较长，会长时间独占单个Buffer Pool的Mutex，阻碍其他事务正常读写页面，进而引起实例性能波动。

解决方案

参数调整

删除数据操作

删除部分数据

分批操作，建议单次删除的数据量不超过一万行，不要一次性删除大量数据。

删除所有数据

• MySQL 5.7版本推荐使用RENAME + DROP操作

  未采用TRUNCATE是因为其在5.7中通过清空原表空间实现，而非重建新表。

  # 重建一张相同的表
  CREATE TABLE t1_new LIKE t1;
  # 检查重建表的表定义是否符合预期
  SHOW CREATE TABLE t1_new;
  # 新表和老表交换
  RENAME TABLE t1 TO t1_bak, t1_new TO t1;
  # 删除原表
  DROP TABLE t1_bak;
  # 检查表空间文件清理进展
  SELECT * FROM information_schema.innodb_purge_files;

  说明

  • 在MySQL 5.7版本中，DDL操作不具备原子性，实例崩溃可能导致文件残留。

  • RENAME过程可能触发重命名文件失败导致实例崩溃问题，详情请参见MySQL官方说明（bug#108087 InnoDB：crash because of failure to rename file in DDL），此问题已在AliSQL 5.7 20220731及更高版本中修复。

• MySQL 8.0版本推荐使用RENAME + DROP或TRUNCATE操作

  • 使用RENAME + DROP参考如下SQL：

    # 重建一张相同的表
    CREATE TABLE t1_new LIKE t1;
    # 检查重建表的表定义是否符合预期
    SHOW CREATE TABLE t1_new;
    # 提前锁定新表和老表
    lock TABLE t1 write, t1_new write;
    # 新表和老表交换
    RENAME TABLE t1 TO t1_bak, t1_new TO t1;
    # 释放表锁
    unlock TABLE;
    # 删除原表
    DROP TABLE t1_bak;
    # 检查表空间文件清理进展
    SELECT * FROM information_schema.innodb_purge_files;

    说明

    • RENAME过程可能触发重命名文件失败导致实例崩溃问题，详情请参见MySQL官方说明（bug#108087 InnoDB：crash because of failure to rename file in DDL），此问题已在AliSQL 5.7 20220731及更高版本中修复。

    • MySQL 8.0增加了lock TABLE，可以选择是否执行，具体参考8.0.13新增的能力8.0 rename table。

    • RENAME + DROP避免了TRUNCATE过程中潜在的卡顿情况。

  • 使用TRUNCATE，参考如下SQL：

    # truncate表
    TRUNCATE TABLE t1;
    # 检查表空间文件清理进展
    SELECT * FROM information_schema.innodb_purge_files;

删除大表

直接使用DROP。

# 删除表
DROP TABLE t1;
# 检查表空间文件清理进展
SELECT * FROM information_schema.innodb_purge_files;