【作业2】DELETE vs TRUNCATE 区别及大表删除影响-酒店常州论坛

文章目录

- 【作业2】DELETE vs TRUNCATE 区别及大表删除影响
- - 1. 二者的区别
  - 2. 大表删除表数据，对OS的影响？
  - - 使用 DELETE 删除大表的影响：
    - 使用 TRUNCATE 删除大表的影响：
  - 3. 大表数据删除的最佳实践
  - - 方案1：分批删除 (推荐)
    - 方案2：创建新表替换
    - 方案3：分区表删除
    - 方案4：优化TRUNCATE
  - 4. 预防措施和监控
  - - 执行前检查：
    - 执行中监控：
    - OS层面监控：
  - 5. 特殊情况处理
  - - 有外键约束的表：
    - InnoDB优化：
  - 总结对比表

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

1. 二者的区别

特性	DELETE FROM table;	TRUNCATE table;
SQL 类型	DML (数据操作语言)	DDL (数据定义语言)
执行方式	逐行删除，记录每一行的删除日志	直接删除数据页，不记录行级日志
事务支持	可回滚 (ROLLBACK)	大部分情况下不可回滚 (MariaDB中部分版本支持事务性TRUNCATE)
触发器	触发DELETE触发器	不触发任何触发器
自增ID	不重置自增计数器	重置自增计数器为初始值
性能	慢 (逐行操作)	极快 (直接操作数据页)
锁机制	行级锁 (InnoDB) 或表级锁	表级锁
空间释放	不立即释放磁盘空间	立即释放磁盘空间
WHERE子句	支持条件删除	不支持，只能全表清空
返回值	返回删除的行数	返回0 (表示成功)

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

使用 DELETE 删除大表的影响：

影响点	具体表现
事务日志暴增	MariaDB会产生大量Undo/Redo日志，可能导致`/var/lib/mysql`空间耗尽
锁竞争	长时间持有行锁/表锁，阻塞其他查询，可能导致"Waiting for table metadata lock"
CPU/内存高负载	逐行删除需要解析、写入日志、更新索引，消耗大量CPU和内存
I/O 压力大	大量读写操作：读取数据页→写入Undo日志→更新数据页→写入Redo日志
主从延迟	二进制日志包含大量DELETE语句，从库需逐行执行，复制延迟严重
回滚灾难	如果中途终止或回滚，回滚时间可能比删除时间更长

使用 TRUNCATE 删除大表的影响：

影响点	具体表现
瞬间I/O峰值	立即释放大量磁盘空间，文件系统需更新元数据，产生短暂I/O冲击
文件系统碎片	释放大量不连续空间，可能导致磁盘碎片增加（对SSD影响较小）
缓存冲击	Buffer Pool中的相关数据页立即失效，可能影响后续查询性能
磁盘空间释放延迟	在InnoDB中，如果`innodb_file_per_table=OFF`，空间不会立即归还OS

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;

总结对比表

场景	推荐方法	理由
小表清空	TRUNCATE	快速、干净
大表清空	创建新表替换	对业务影响最小
条件删除	DELETE分批	可控、可监控
分区表	DROP PARTITION	秒级完成
开发环境	TRUNCATE	快速重置
生产环境	分批DELETE或新表替换	稳定性优先
紧急清空	TRUNCATE	最快见效

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

企业官网建设流程全解析

文章目录

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

1. 二者的区别

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

使用 DELETE 删除大表的影响：

使用 TRUNCATE 删除大表的影响：

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

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

方案2：创建新表替换

方案3：分区表删除

方案4：优化TRUNCATE

4. 预防措施和监控

执行前检查：

执行中监控：

OS层面监控：

5. 特殊情况处理

有外键约束的表：

InnoDB优化：

总结对比表

热门文章

文章分类

标签云

需要专业的网站建设服务？

企业官网建设流程全解析

文章目录

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

1. 二者的区别

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

使用 DELETE 删除大表的影响：

使用 TRUNCATE 删除大表的影响：

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

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

方案2：创建新表替换

方案3：分区表删除

方案4：优化TRUNCATE

4. 预防措施和监控

执行前检查：

执行中监控：

OS层面监控：

5. 特殊情况处理

有外键约束的表：

InnoDB优化：

总结对比表

热门文章

文章分类

标签云

相关文章

需要专业的网站建设服务？