【作业2】DELETE vs TRUNCATE 区别及大表删除影响

【作业2】DELETE vs TRUNCATE 区别及大表删除影响

1. 二者的区别

2. 大表删除表数据，对OS的影响？

使用 DELETE 删除大表的影响：

使用 TRUNCATE 删除大表的影响：

3. 大表数据删除的最佳实践

方案1：分批删除 (推荐)

-- 使用LIMIT分批删除，减轻事务压力DELETEFROMlarge_tableWHEREconditionLIMIT1000;-- 循环执行，直到删除完成

方案2：创建新表替换

-- 1. 创建新表（保留需要的数据结构）CREATETABLEnew_tableLIKElarge_table;-- 2. 插入需要保留的数据INSERTINTOnew_tableSELECT*FROMlarge_tableWHEREkeep_condition;-- 3. 重命名表（快速切换）RENAMETABLElarge_tableTOold_table,new_tableTOlarge_table;-- 4. 稍后删除旧表DROPTABLEold_table;

方案3：分区表删除

-- 如果表已分区，直接删除分区ALTERTABLElarge_tableDROPPARTITIONp2023;-- 比删除数据快几个数量级

方案4：优化TRUNCATE

-- 1. 降低影响，在低峰期执行SETSESSIONlock_wait_timeout=300;SETSESSIONinnodb_lock_wait_timeout=300;-- 2. 使用TRUNCATETRUNCATETABLElarge_table;

4. 预防措施和监控

执行前检查：

-- 1. 评估表大小SELECTtable_nameAS`表名`,ROUND(((data_length+index_length)/1024/1024),2)AS`大小(MB)`FROMinformation_schema.tablesWHEREtable_schema='your_database'ANDtable_name='large_table';-- 2. 检查锁等待时间SHOWVARIABLESLIKE'innodb_lock_wait_timeout';SHOWVARIABLESLIKE'lock_wait_timeout';

执行中监控：

-- 监控删除进度SHOWPROCESSLIST;SHOWENGINEINNODBSTATUS\G-- 监控空间使用SELECTtable_schemaAS'数据库',table_nameAS'表名',ROUND(((data_length+index_length)/1024/1024),2)AS'当前大小(MB)'FROMinformation_schema.tablesORDERBY(data_length+index_length)DESC;

OS层面监控：

# 监控磁盘空间df-h /var/lib/mysql# 监控I/O压力iostat -x1# 监控内存和CPUtop-u mysqlhtop

5. 特殊情况处理

有外键约束的表：

-- 1. 禁用外键检查SETFOREIGN_KEY_CHECKS=0;-- 2. 执行删除TRUNCATETABLEparent_table;TRUNCATETABLEchild_table;-- 3. 重新启用SETFOREIGN_KEY_CHECKS=1;

InnoDB优化：

-- 调整Buffer Pool，减少刷盘频率SETGLOBALinnodb_flush_log_at_trx_commit=2;SETGLOBALsync_binlog=0;-- 执行删除操作-- ...-- 恢复设置SETGLOBALinnodb_flush_log_at_trx_commit=1;SETGLOBALsync_binlog=1;

总结对比表

核心建议：生产环境大表删除，优先考虑分批DELETE或新表替换方案，避免使用一次性TRUNCATE或DELETE，除非在明确维护窗口且评估过风险。