在我工作的系统中,有很多使用临时表的存储过程和SQL脚本。使用这些表后,删除它们是一个好习惯。

我的许多同事(几乎所有的同事都比我经验丰富)通常这样做:

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp


我通常在脚本中使用单个DROP TABLE

是否有充分的理由在TRUNCATE之前紧接着做DROP

#1 楼

否。

TRUNCATEDROP的行为和速度几乎相同,因此完全不需要在TRUNCATE之前执行DROP


注意:我写了这个从SQL Server的角度回答,并假定它同样适用于Sybase。似乎并非完全如此。

注意:当我第一次发布此答案时,还有其他几个获得高度评价的答案-包括当时接受的答案-做出了一些错误的声明,例如:TRUNCATE未记录; TRUNCATE无法回滚; TRUNCATEDROP快;等。

既然此线程已被清理,则随后的反驳似乎与原始问题是相切的。我将它们留在这里,以供其他寻求揭穿这些神话的人参考。


有一些流行的谬论-甚至在经验丰富的DBA中普遍存在-可能激发了这种TRUNCATE-then-DROP模式。它们是:



神话:TRUNCATE未记录,因此无法回滚。

神话:TRUNCATEDROP快。 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一样快。



TRUNCATEDROP是最少记录的操作。这意味着DROP也可以回滚。这也意味着它的工作方式与TRUNCATE完全相同。 DROP不会删除单独的行,而是将适当的数据页标记为未分配,并另外将表的元数据标记为已删除。

因为TRUNCATEDROP的工作方式完全相同,所以它们的运行速度非常快。在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亿行表,DROPDROP根本不需要时间。 (在冷高速缓存中,它们第一次运行或两次运行大约需要2-3秒。)我还认为,TRUNCATETRUNCATE操作的平均持续时间较长是由于本地计算机上的负载变化所致,而不是因为某种原因组合了魔术上比单个操作差一个数量级。毕竟,它们几乎是完全一样的东西。

如果您对这些操作的日志记录开销的详细信息感兴趣,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

缩小pgfirstpgrootpgfirstiampcusedpcdatapcreserved中的sys.sysallocunits


这些系统表行仅在下一条语句中删除表时最终被删除。

TRUNCATEDROP进行的日志记录如下。为了比较起见,我还添加了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就足够了

评论


TRUNCATE是否可以避免外键冲突?怎么样?

–peterh-恢复莫妮卡
19年8月16日在20:18

会写一个有外键的错误

– Evgeniy Gribkov
19年8月17日在9:56

#6 楼

truncate的目的是简单,不可撤销地删除表中的所有内容(基于数据存储引擎的某些技术规格可能会略有不同)–跳过繁重的日志记录等。

正在进行更改。因此,为了使日志记录最少并减少无用的系统用户流失,我怀疑可能会先截断一个非常大的表,然后再删除它。

drop table可能包装在事务中(这是最安全的选择)当然,这将使您回滚操作。