主页 / dba / 问题 / 212440
Accepted
Jérôme Verstrynge
Asked: 2018-07-18 05:44:45 +0800 CST

TRUNCATE 或 DROP 表会非常耗时吗？

  • 772

几个月前，我在一个（维护得不太好的）数据库中发现了一个完全无用的表，其中包含 4.3 亿个未索引的行，每天增长约 80 000 行。

它是移除的最佳候选者。但是，我从来没有机会实现这一目标（也不是优先事项）。我什至没有机会尝试在每周刷新的开发数据库中删除它。

我想知道一个简单的 TRUNCATE 或 DROP 是否会非常耗时？我的意思是，如果写得不好，这个操作会不会像 DELETE 那样阻塞生产环境几分钟。还是这些安全操作？

mysql blocking

3 个回答

  1. Best Answer

    我的意思是，[TRUNCATE OR DROP TABLE] 是否可以像 DELETE 一样阻塞生产环境几分钟

    对于 Microsoft SQL Server，在极少数情况下是的。

    TRUNCATE 和 DROP 都是“仅元数据”操作，这意味着它们只涉及对内部目录表的一些更改。因此，它们绝不是资源密集型的。但它们都需要表上的模式修改锁 (Sch-M)。

    当您请求 Sch-M 锁时，您必须等到所有不兼容的锁都被释放。每个引用该表的查询都需要一个不兼容的 Sch-S 锁。在您请求 Sch-M 锁之后，您将等待所有正在运行的查询完成，任何新的查询都将在您的 Sch-M 锁请求之后被阻止。

    在 SQL 2012 之前，这种行为略有不同，因为 NOLOCK/READ UNCOMMITTED 查询能够在您等待 Sch-M 时获取 Sch-S 锁，因此您可能不得不永远等待。

    在任何一种情况下，结果都是如果对表有大量长时间运行的查询，则 TRUNCATE 或 DROP 都将花费一些时间，并在等待时阻塞其他查询。

    通常这没什么大不了的。如果您正在删除或截断表，则其他会话不太可能对它运行查询，或者需要运行引用它的新查询。

    对于需要对繁忙的表执行元数据操作的场景，SQL Server 在 SQL 2014 中引入了“低优先级架构锁等待”。这允许 ALTER TABLE 或 ALTER INDEX 等待 Sch-M 锁而不阻塞新请求。会话只是等待活动的暂停，即没有会话拥有 Sch-S 锁的瞬间，然后进行更改。请参阅SQL Server 2014 中的新功能 - 第 3 部分 - 低优先级 等待功能的详细说明、SQL 2012 之前架构锁的行为历史以及增强的动机。

    无论如何，万一您需要低优先级等待 TRUNCATE 表，您可以使用 ALTER TABLE ... SWITCH 将其复制到临时表，然后删除临时表。例如

    create table t_stage ...;

alter table t   
switch to t_stage
with ( wait_at_low_priority ( max_duration = 4 minutes, abort_after_wait = self ) )  

drop table t_stage
    • 3
    • MyISAM——DROP就像文件删除一样。 TRUNCATE执行删除所有数据但保留文件存在的 C 文件操作。
    • InnoDB 有自己的表空间（cf innodb_file_per_table）——就像文件删除
    • InnoDB没有自己的表空间——需要释放很多块；这可能需要时间。

    文件删除可以快也可以不那么快，具体取决于操作系统。一些操作系统会立即释放块。你得等一等。而且，因为它是一个操作系统操作，这可能会干扰几乎任何其他事情。什么操作系统？

    “分钟”似乎很长。多少分钟？多少GB？

    • 3

  3. 对于 MySQL，TRUCNATE实现为DROP ... CREATE. 它需要一个锁DROP，它将等待。它不调用ON DELETE触发器。它会导致隐式提交。它是否是原子的取决于存储引擎。

    欲了解更多信息，请阅读，

    会不会很费时间？是的。它是否比 安全性低DELETE，但速度更快 - 是的，它是一种优化。

    • 2

相关问题