大数据量删除指导

大数据量删除原则

• 在设计业务大表时，需考虑采用分表或分区策略，以便在需要删除数据时可以直接删除整个表或分区。
• 删除操作应在业务低峰期执行，并通过控制每次删除的数据量来降低影响。删除过程中需持续监控CPU、内存、复制延迟及IO等指标，一旦发现某项指标出现明显上升，应立即中断操作。删除完成后，请更新统计信息，并按需进行碎片整理。

具体删除操作

• 删除表

  DROP TABLE table_name;

• 删除表中所有数据

  TRUNCATE TABLE table_name;

• 删除表中大部分数据
  1. 重建一张相同的表。

     CREATE TABLE table_name_new LIKE table_name;

  2. 检查重建表的表定义是否符合预期。

     SHOW CREATE TABLE table_name_new;

     如果不符合预期，删除并重建表。

  3. 将需要的数据插入新表。

     INSERT INTO table_name_new SELECT * FROM table_name WHERE id >= @start_id AND id < @end_id;

  4. 交换新表和老表。

     RENAME TABLE table_name TO table_name_bak, table_name_new TO table_name;

  5. 检查table_name表中数据。
  6. 删除原表。

     DROP TABLE table_name_bak;

  7. 检查表空间文件清理进度。

     SELECT * FROM information_schema.rds_innodb_purge_files;

• 删除表中少部分数据

  小批量分批删除，删除的条件必须走主键或唯一索引并加limit限制。

  DELETE FROM table_name WHERE id >= @start_id AND id < @end_id AND create_time < '2024-01-01' LIMIT 1000;

• 更新统计信息

  ANALYZE TABLE table_name;

• 按需整理表碎片（注意该操作会锁表）

  OPTIMIZE TABLE table_name;

典型案例

• 案例一：

  在Binlog提交阶段，当单个大事务中删除大量数据时，生成的临时Binlog文件会达到几十到几百GB。此时提交Binlog会阻塞其他写事务，导致主节点出现短暂卡顿，备节点回放时产生复制时延。

  解决方案：

  • 减少Binlog的产生。
  • 优先使用drop或truncate删除数据，使用delete删除时控制单个事务删除的行数。

• 案例二：

  在文件删除阶段，大表文件的删除会导致实例的IO资源被大量占用，影响正常请求处理。

  解决方案：将实例版本升级到8.0.32，使用异步删除大表功能。

• 案例三：

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

  解决方案：将实例版本升级到8.0.23以上版本，MySQL官方在8.0.23解决了此问题，详情请参见MySQL官方说明：WL#14100: InnoDB: Faster truncate/drop table space