我的许多同事(几乎所有的同事都比我经验丰富)通常这样做:
TRUNCATE TABLE #mytemp
DROP TABLE #mytemp
我通常在脚本中使用单个
DROP TABLE
。是否有充分的理由在
TRUNCATE
之前紧接着做DROP
?#1 楼
否。TRUNCATE
和DROP
的行为和速度几乎相同,因此完全不需要在TRUNCATE
之前执行DROP
。注意:我写了这个从SQL Server的角度回答,并假定它同样适用于Sybase。似乎并非完全如此。
注意:当我第一次发布此答案时,还有其他几个获得高度评价的答案-包括当时接受的答案-做出了一些错误的声明,例如:
TRUNCATE
未记录; TRUNCATE
无法回滚; TRUNCATE
比DROP
快;等。既然此线程已被清理,则随后的反驳似乎与原始问题是相切的。我将它们留在这里,以供其他寻求揭穿这些神话的人参考。
有一些流行的谬论-甚至在经验丰富的DBA中普遍存在-可能激发了这种
TRUNCATE-then-DROP
模式。它们是:神话:
TRUNCATE
未记录,因此无法回滚。神话:
TRUNCATE
比DROP
快。 br /> 让我反驳这些谬论。我正在从SQL Server的角度编写这种反驳,但是我在这里所说的一切都应该同样适用于Sybase。
TRUNCATE已记录下来,并且可以回滚。
TRUNCATE
是已记录的操作,因此可以回滚。只需将其包装在事务中即可。USE [tempdb];
SET NOCOUNT ON;
CREATE TABLE truncate_demo (
whatever VARCHAR(10)
);
INSERT INTO truncate_demo (whatever)
VALUES ('log this');
BEGIN TRANSACTION;
TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;
SELECT *
FROM truncate_demo;
DROP TABLE truncate_demo;
但是请注意,这对Oracle而言并非如此。尽管
TRUNCATE
和其他DDL语句由Oracle的撤消和重做功能记录和保护,但用户无法回滚TRUNCATE
和其他DDL语句,因为Oracle会在所有DDL语句之前和之后立即发出隐式提交。与完全记录相反,
TRUNCATE
的记录最少。这意味着什么?说您一张桌子。而不是将每个已删除的行放在事务日志中,TRUNCATE
只是将它们所在的数据页标记为未分配。这就是为什么它这么快。这就是为什么您无法使用日志读取器从事务日志中恢复TRUNCATE
-ed表的行的原因。您将找到所有对已释放数据页面的引用。 将此与
DELETE
进行比较。如果您对表中的所有行进行DELETE
并提交事务,从理论上讲,您仍然可以在事务日志中找到已删除的行并从那里恢复它们。这是因为DELETE
将每个删除的行都写入事务日志。对于大型表,这将使其比TRUNCATE
慢得多。DROP的速度与TRUNCATE一样快。
TRUNCATE
,DROP
是最少记录的操作。这意味着DROP
也可以回滚。这也意味着它的工作方式与TRUNCATE
完全相同。 DROP
不会删除单独的行,而是将适当的数据页标记为未分配,并另外将表的元数据标记为已删除。因为
TRUNCATE
和DROP
的工作方式完全相同,所以它们的运行速度非常快。在TRUNCATE
-ing之前没有指向表的指向。如果您不相信我,请在您的开发实例上运行此演示脚本。在具有高速缓存的本地计算机上,得到的结果如下:
table row count: 134,217,728
run# transaction duration (ms)
TRUNCATE TRUNCATE then DROP DROP
==========================================
01 0 1 4
02 0 39 1
03 0 1 1
04 0 2 1
05 0 1 1
06 0 25 1
07 0 1 1
08 0 1 1
09 0 1 1
10 0 12 1
------------------------------------------
avg 0 8.4 1.3
因此,对于1.34亿行表,
DROP
和DROP
根本不需要时间。 (在冷高速缓存中,它们第一次运行或两次运行大约需要2-3秒。)我还认为,TRUNCATE
和TRUNCATE
操作的平均持续时间较长是由于本地计算机上的负载变化所致,而不是因为某种原因组合了魔术上比单个操作差一个数量级。毕竟,它们几乎是完全一样的东西。 如果您对这些操作的日志记录开销的详细信息感兴趣,Martin对此有一个简单的说明。
#2 楼
先测试TRUNCATE
,然后再测试DROP
,而不是直接测试DROP
,这表明第一种方法实际上会稍微增加日志记录开销,因此甚至可能适得其反。通过查看各个日志记录,可以看到
TRUNCATE ... DROP
版本几乎与DROP
版本相同,除了具有这些附加条目。+-----------------+---------------+-------------------------+
| Operation | Context | AllocUnitName |
+-----------------+---------------+-------------------------+
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_LOCK_XACT | LCX_NULL | NULL |
+-----------------+---------------+-------------------------+
因此,
TRUNCATE
第一个版本最终浪费了一些精力,对各种系统表进行了一些更新,如下所示更新
rcmodified
中所有表列的sys.sysrscols
更新
rcrows
中的sysrowsets
缩小
pgfirst
,pgroot
,pgfirstiam
,pcused
, pcdata
,pcreserved
中的sys.sysallocunits
这些系统表行仅在下一条语句中删除表时最终被删除。
TRUNCATE
与DROP
进行的日志记录如下。为了比较起见,我还添加了DELETE
。+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| | | | Bytes | Count |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Operation | Context | AllocUnitName | Truncate / Drop | Drop Only | Truncate Only | Delete Only | Truncate / Drop | Drop Only | Truncate Only | Delete Only |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| LOP_BEGIN_XACT | LCX_NULL | | 132 | 132 | 132 | 132 | 1 | 1 | 1 | 1 |
| LOP_COMMIT_XACT | LCX_NULL | | 52 | 52 | 52 | 52 | 1 | 1 | 1 | 1 |
| LOP_COUNT_DELTA | LCX_CLUSTERED | System Table | 832 | | 832 | | 4 | | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | System Table | 2864 | 2864 | | | 22 | 22 | | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | T | | | | 8108000 | | | | 1000 |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 36 | 72 | | 3 | 1 | 2 | |
| LOP_LOCK_XACT | LCX_NULL | | 336 | 296 | 40 | | 8 | 7 | 1 | |
| LOP_MODIFY_HEADER | LCX_PFS | Unknown Alloc Unit | 76 | 76 | | 76 | 1 | 1 | | 1 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | System Table | 644 | 348 | 296 | | 5 | 3 | 2 | |
| LOP_MODIFY_ROW | LCX_IAM | T | 800 | 800 | 800 | | 8 | 8 | 8 | |
| LOP_MODIFY_ROW | LCX_PFS | T | 11736 | 11736 | 11736 | | 133 | 133 | 133 | |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 92 | 92 | 92 | | 1 | 1 | 1 | |
| LOP_SET_BITS | LCX_GAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_IAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_PFS | System Table | 896 | 896 | | | 16 | 16 | | |
| LOP_SET_BITS | LCX_PFS | T | | | | 56000 | | | | 1000 |
| LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 168 | 224 | 168 | | 3 | 4 | 3 | |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Total | | | 36736 | 35552 | 32220 | 8164260 | 456 | 448 | 406 | 2003 |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
测试是在具有完全恢复模型的数据库中针对1000行表(每页一行)进行的。由于根索引页和3个中级索引页,该表总共消耗1,004页。
这些页面中有8个是混合范围中的单页分配,其余部分分布在125个统一范围内。 8个单页取消分配显示为8个
LOP_MODIFY_ROW,LCX_IAM
日志条目。 125个范围的重分配为LOP_SET_BITS LCX_GAM,LCX_IAM
。这两个操作都还需要更新关联的PFS
页面,因此需要组合133个LOP_MODIFY_ROW, LCX_PFS
条目。然后,在实际删除表时,需要从各个系统表中删除有关该表的元数据,因此22个系统表LOP_DELETE_ROWS
日志条目(如下所示)+----------------------+--------------+-------------------+-------------------+
| Object | Rows Deleted | Number of Indexes | Delete Operations |
+----------------------+--------------+-------------------+-------------------+
| sys.sysallocunits | 1 | 2 | 2 |
| sys.syscolpars | 2 | 2 | 4 |
| sys.sysidxstats | 1 | 2 | 2 |
| sys.sysiscols | 1 | 2 | 2 |
| sys.sysobjvalues | 1 | 1 | 1 |
| sys.sysrowsets | 1 | 1 | 1 |
| sys.sysrscols | 2 | 1 | 2 |
| sys.sysschobjs | 2 | 4 | 8 |
+----------------------+--------------+-------------------+-------------------+
| | | | 22 |
+----------------------+--------------+-------------------+-------------------+
以下完整脚本
DECLARE @Results TABLE
(
Testing int NOT NULL,
Operation nvarchar(31) NOT NULL,
Context nvarchar(31) NULL,
AllocUnitName nvarchar(1000) NULL,
SumLen int NULL,
Cnt int NULL
)
DECLARE @I INT = 1
WHILE @I <= 4
BEGIN
IF OBJECT_ID('T','U') IS NULL
CREATE TABLE T(N INT PRIMARY KEY,Filler char(8000) NULL)
INSERT INTO T(N)
SELECT DISTINCT TOP 1000 number
FROM master..spt_values
CHECKPOINT
DECLARE @allocation_unit_id BIGINT
SELECT @allocation_unit_id = allocation_unit_id
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.hobt_id = a.container_id
WHERE p.object_id = object_id('T')
DECLARE @LSN NVARCHAR(25)
DECLARE @LSN_HEX NVARCHAR(25)
SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)
SELECT @LSN_HEX=
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)
BEGIN TRAN
IF @I = 1
BEGIN
TRUNCATE TABLE T
DROP TABLE T
END
ELSE
IF @I = 2
BEGIN
DROP TABLE T
END
ELSE
IF @I = 3
BEGIN
TRUNCATE TABLE T
END
ELSE
IF @I = 4
BEGIN
DELETE FROM T
END
COMMIT
INSERT INTO @Results
SELECT @I,
CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END,
Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END,
COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
COUNT(*) AS Cnt
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
GROUP BY GROUPING SETS((Operation, Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END),())
SET @I+=1
END
SELECT Operation,
Context,
AllocUnitName,
AVG(CASE WHEN Testing = 1 THEN SumLen END) AS [Truncate / Drop Bytes],
AVG(CASE WHEN Testing = 2 THEN SumLen END) AS [Drop Bytes],
AVG(CASE WHEN Testing = 3 THEN SumLen END) AS [Truncate Bytes],
AVG(CASE WHEN Testing = 4 THEN SumLen END) AS [Delete Bytes],
AVG(CASE WHEN Testing = 1 THEN Cnt END) AS [Truncate / Drop Count],
AVG(CASE WHEN Testing = 2 THEN Cnt END) AS [Drop Count],
AVG(CASE WHEN Testing = 3 THEN Cnt END) AS [Truncate Count],
AVG(CASE WHEN Testing = 4 THEN Cnt END) AS [Delete Count]
FROM @Results
GROUP BY Operation,
Context,
AllocUnitName
ORDER BY Operation, Context,AllocUnitName
DROP TABLE T
#3 楼
好吧,以为我会尝试做一些不依赖任何“热缓存”的基准测试,以便希望它们是一个更现实的测试(也使用Postgres,以查看其是否与其他发布的答案的特征相同) :我的基准测试使用的Postgres 9.3.4和大型数据库(希望足够大以至于不能容纳在RAM缓存中): ://gist.github.com/rdp/8af84fbb54a430df8fc0
1000万行:
truncate: 1763ms
drop: 2091ms
truncate + drop: 1763ms (truncate) + 300ms (drop) (2063ms total)
drop + recreate: 2063ms (drop) + 242ms (recreate)
100M行: br />
truncate: 5516ms
truncate + drop: 5592ms
drop: 5680ms (basically, the exact same ballpark)
因此,我推测以下内容:drop与截断+ drop一样快(或更快)(至少对于现代的Postgres版本而言),但是,如果您计划的话在还转过身来并重新创建表的同时,您最好坚持执行直接截断,这比drop + recreate更快(有意义)。 FWIW。
注1:https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886(说Postgres 9.2的截断速度可能比以前的版本快)。与往常一样,使用您自己的系统进行基准测试以查看其特性。
注2:如果在事务中,则截断可以在postgres中回滚:
http://www.postgresql.org /docs/8.4/static/sql-truncate.html
注释3:带有小表的truncate有时可能比删除慢:https://stackoverflow.com/questions/11419536/postgresql -truncation-speed / 11423886#11423886
#4 楼
添加历史记录...删除表需要更新多个系统表,而这又通常需要在单个事务中对这些系统表进行更改(请考虑“开始tran,删除syscolumns,删除sysobject,提交”。
还需要取消分配与该表关联的所有数据/索引页。'
很多很多年前...事务中包括空间重新分配过程,该事务也更新了系统表;最终结果是,分配的页面数越多,取消分配所述页面所花费的时间越长,事务(在系统表上)就越长保持打开状态,因此有更大的机会阻止(在系统表上)尝试在tempdb中创建/删除表的其他进程(尤其是较讨厌的allpages == page-level锁定和潜在的表级锁定升级)。 br />
使用一种早期方法(可以追溯到那时)来减少sy上的争用茎表是为了减少在系统表上持有锁的时间,一种(相对)简单的方法是在删除表之前取消分配数据/索引页。
truncate table
不会不会取消分配所有数据/索引页,而是会取消分配除一个8页(数据)范围之外的所有扩展区;另一个“黑客”是在删除表之前先删除所有索引(是的,在sysindexes上使用单独的txn,但在删除表上使用较小的txn)。考虑到这一点(很多很多年前) )只有一个“ tempdb”数据库,并且某些应用程序大量使用了该“ tempdb”数据库,任何可以减少“ tempdb”中系统表争用的“黑客”都是有益的;随着时间的推移,情况有所改善...多个临时数据库,系统表上的行级锁定,更好的释放方法等。
同时,如果将
truncate table
留在原处,也不会造成任何伤害代码。#5 楼
对具有外键的表执行TRUNCATE是有意义的。但是,对于临时表,只需DROP就足够了
#6 楼
truncate
的目的是简单,不可撤销地删除表中的所有内容(基于数据存储引擎的某些技术规格可能会略有不同)–跳过繁重的日志记录等。正在进行更改。因此,为了使日志记录最少并减少无用的系统用户流失,我怀疑可能会先截断一个非常大的表,然后再删除它。
drop table
可能包装在事务中(这是最安全的选择)当然,这将使您回滚操作。
评论
TRUNCATE是否可以避免外键冲突?怎么样?
–peterh-恢复莫妮卡
19年8月16日在20:18
会写一个有外键的错误
– Evgeniy Gribkov
19年8月17日在9:56