SQL 中delete与truncate的区别

背景

前段时间，运维那边反馈说，我们开发这边的调度任务，老是卡死他们的数据库备份，要求我们自己核查。经过校验查看 ，发现是清空数据表的时候导致的问题，原来我们采用的是delete，后来重新替换成truncate的形式就解决了这个问题。然后梳理了一下delete与truncate的区别以及优缺点。enjoy~~

1，delete

语法：delete from 表名；

2，truncate

语法：truncate table 表名；

3，相同点

delete与truncate都是用来删除表数据

4，区别

（1）delete是DML命令，truncate是DDL命令。
（2）truncate可以删除整张表的数据而不维护表的完整性，delete可以删除指定的数据， delete不能绕过完整性强制机制。
（3）delete语句支持where条件的筛选和过滤，删除特定的数据；truncate 不支持where条件的筛选，只可以清空全表的数据。
（4）delete语句是行级锁定，表中的每一行都被锁定以进行删除；truncat语句是表级锁定，锁定了整个表以删除所有记录。
（5）delete命令维护日志，执行速度较慢，但涉及到日志记录，可以回滚恢复；truncate命令在事务日志中维护最少的日志记录，执行速度较快，效率高，但是无法回滚恢复。
（6）delete命令比truncate命令使用更多的事务空间。delete删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退；truncate删除数据是删除缓存，不涉及缓存事务处理。
（7）delete命令可以与索引视图一起使用；而，truncate命令不能与索引视图一起使用。

5，应用场景

（1）在业务中，无论是使用delete语句还是使用truncate语句，都要考虑数据的备份。
（2）根据一定的业务条件删除数据时、且数据量、性能可控的情况下，可以考虑使用delete语句。
（3）在业务中需要删除大批量数据时，同时要求速度快，效率高并且无需撤销时，可以使用truncate语句。
（4）在业务中考虑事务提交、撤销的管理机制时，使用delete语句。

总结

在我们的工作中，需要根据不同的业务场景，去选择合适的删除语句，并且在使用删除语句的时候，要做好数据的备份，以防数据丢失。