主页 / dba / 问题 / 4163
Accepted
user606723
Asked: 2011-07-28 09:36:39 +0800 CST

为什么同时使用 TRUNCATE 和 DROP？

  • 772

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

我的许多同事（几乎所有人都比我更有经验）通常这样做：

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp

DROP TABLE我通常在我的脚本中使用一个。

TRUNCATE在 a之前立即执行 a 有什么好的理由DROP吗？

sql-server sybase

7 个回答

  1. Best Answer

    不。

    TRUNCATE并且DROP在行为和速度上几乎相同，因此根本不需要在 aTRUNCATE之前执行正确的操作。DROP

    注意：我从 SQL Server 的角度编写了这个答案，并假设它同样适用于 Sybase。看来，情况并非完全如此。

    注意：当我第一次发布这个答案时，还有其他几个评价很高的答案——包括当时接受的答案——提出了几个错误的声明，例如：TRUNCATE未记录；TRUNCATE无法回滚；TRUNCATE比DROP;快 等等

    既然已经清理了这个线程，那么接下来的反驳似乎与原始问题相切。我把它们留在这里作为其他人想要揭穿这些神话的参考。

    有一些流行的谎言——甚至在有经验的 DBA 中也很普遍——可能促成了这种TRUNCATE-then-DROP模式。他们是：

    • 误区：TRUNCATE没有记录，因此无法回滚。
    • 误区：TRUNCATE比DROP。

    让我反驳这些谎言。我是从 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。尽管由 Oracle 的撤消和重做功能记录和保护，但TRUNCATE其他 DDL 语句不能由用户回滚，因为 Oracle在所有 DDL 语句之前和之后立即发出隐式提交。

    • TRUNCATE是最小记录，而不是完全记录。这意味着什么？说你TRUNCATE一张桌子。无需将每个已删除的行都放入事务日志中，TRUNCATE只需将它们所在的数据页标记为未分配。这就是为什么它这么快。这也是您无法TRUNCATE使用日志阅读器从事务日志中恢复 -ed 表的行的原因。您会发现所有这些都是对已释放数据页的引用。

      将此与DELETE. 如果您DELETE将表中的所有行都提交并提交事务，理论上您仍然可以在事务日志中找到已删除的行并从那里恢复它们。那是因为DELETE将每个已删除的行写入事务日志。对于大型表，这将使其比TRUNCATE.

    DROP与 TRUNCATE 一样快。

    • 像TRUNCATE,DROP是一个最少记录的操作。 这意味着DROP也可以回滚。这也意味着它的工作方式与TRUNCATE. DROP将相应的数据页标记为未分配，并将表的元数据标记为已删除，而不是删除单个行。

    • 因为TRUNCATE和DROP工作方式完全相同，它们运行速度一样快。 在-ing 表格之前-ing 表格是没有意义的。TRUNCATEDROP如果您不相信我，请在您的开发实例上运行此演示脚本。

      在我的本地机器上，有一个温暖的缓存，我得到的结果如下：

      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，TRUNCATE实际上根本不需要任何时间。（在冷缓存上，第一次或两次运行大约需要 2-3 秒。）我还认为TRUNCATEthenDROP操作的平均持续时间较长是由于我的本地机器上的负载变化，而不是因为这种组合在某种程度上是神奇的比单个操作差一个数量级。毕竟，它们几乎完全相同。

      如果您对这些操作的日志记录开销的更多细节感兴趣，Martin 对此有一个简单的解释。

    • 142

  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, pcreservedinsys.sysallocunits

    只有在下一条语句中删除表时，这些系统表行才会最终被删除。

    TRUNCATE下面是对 vs执行的日志记录的完整分解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        |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+

    该测试是在具有完全恢复模型的数据库中针对每页一行的 1,000 行表进行的。由于根索引页和 3 个中间级索引页，该表总共消耗了 1,004 页。

    其中 8 个页面是混合范围中的单页面分配，其余的分布在 125 个统一范围中。8 个单页取消分配显示为 8LOP_MODIFY_ROW,LCX_IAM个日志条目。125 个盘区解除分配为LOP_SET_BITS LCX_GAM,LCX_IAM. 这两个操作还需要更新相关PFS页面，因此合并了 133LOP_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
    • 54

  3. 好的，我想我会尝试做一些不依赖任何“热缓存”的基准测试，这样希望它们会是一个更现实的测试（也使用 Postgres，看看它是否与其他发布的答案的相同特征相匹配） ：

    我的基准测试使用带有大型数据库的 postgres 9.3.4，（希望足够大，不适合 RAM 缓存）：

    使用这个测试数据库脚本：https ://gist.github.com/rdp/8af84fbb54a430df8fc0

    有 10M 行：

    truncate: 1763ms
drop: 2091ms
truncate + drop: 1763ms (truncate) + 300ms (drop) (2063ms total)
drop + recreate: 2063ms (drop) + 242ms (recreate)

    有 100M 行：

    truncate: 5516ms
truncate + drop: 5592ms
drop: 5680ms (basically, the exact same ballpark)

    因此，据此我推测如下： drop 与 truncate+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：截断可以，小表，有时比删除慢：https ://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886

    • 2

  4. 添加一些历史观点...

    删除一个表需要更新多个系统表，而这通常需要在单个事务中进行这些系统表更改（想想“开始传输、删除 syscolumns、删除 sysobjects、提交”）。

    “删除表”中还包括需要解除分配与表关联的所有数据/索引页。

    很多很多很多年前......空间释放过程包含在还更新系统表的事务中；最终结果是分配的页面数量越大，释放所述页面所需的时间越长，时间越长。事务（在系统表上）保持打开状态，因此更有可能阻塞（在系统表上）试图在 tempdb 中创建/删除表的其他进程（特别是对于旧的 allpages== 页级锁定和表的可能性）级锁升级）。

    一种用于减少系统表争用的早期方法（当时）是减少在系统表上持有锁的时间，而一种（相对）简单的方法是在删除之前释放数据/索引页桌子。

    虽然truncate table不会释放所有数据/索引页，但它会释放除一个 8 页（数据）范围之外的所有数据；另一个“hack”是在删除表之前删除所有索引（是的，在 sysindexes 上单独的 txn，但删除表的 txn 较小）。

    当您考虑到（再次，很多很多年前）只有一个“tempdb”数据库时，一些应用程序大量使用了那个“ tempdb ”数据库，任何可以减少系统表争用的“黑客” 'tempdb' 是有益的；随着时间的推移，情况有所改善……多个临时数据库、系统表上的行级锁定、更好的释放方法等。

    同时，truncate table如果留在代码中，使用不会有任何伤害。

    • 1

  5. 可能有一个历史和暂时的原因。在 Postgres 9.3 之前，事件触发器不存在，并且您不能“ON DROP”触发器。

    但是从 Postgres 8.4 开始可以做TRIGGER ON TRUNCATE 所以也许有些人会做一些特殊的事情TRIGGER ON TRUNCATE来模拟ON DROP和确保触发器的激活他们做 TRUNCATE 和 DROP 之后。

    • -1

  6. 对具有外键的表执行 TRUNCATE 是有意义的。但是，对于临时表，只需 DROP 就足够了

    • -2

  7. 要点truncate是简单且不可撤销地删除表中的所有内容（基于数据存储引擎的一些技术细节可能略有不同）——跳过大量日志记录等。

    drop table在进行更改时记录所有更改。因此，为了减少日志记录并减少无用的系统流失，我怀疑可能会先截断一个非常大的表，然后再删除。

    truncate可能包含在事务中（这将是最安全的选择），当然，这将允许您回滚操作。

    • -8

相关问题