提高Sql删除的性能

Question

我们有一个查询，根据 id 字段（主键）从表中删除一些行。 这是一个非常简单的查询：

delete all from OUR_TABLE where ID in (123, 345, ...)

问题是 id 的数量可能很大（例如 70k），因此查询需要很长时间。 有什么办法可以优化这个吗？ （我们正在使用 sybase - 如果这很重要的话）。

Answer 1

考虑批量运行它。 一次运行 1000 条记录的循环可能比执行所有操作的一个查询要快得多，而且不会长时间将表锁定给其他用户。

如果您有级联删除（并且许多外键表受到影响）或涉及触发器，您可能需要以更小的批次运行。 您必须进行试验才能确定哪个数字最适合您的情况。 我有一些表，我必须批量删除 100 条记录，而另一些表则需要 50000 条记录（幸运的是，在这种情况下，我删除了 100 万条记录）。

但在任何情况下，我都会将我打算删除的键值放入临时表中并从那里删除。

Answer 2

有两种方法可以执行这样的语句：

1. 创建一个新表并复制除要删除的行之外的所有内容。 之后交换表（改变表名称...）我建议尝试一下，即使这听起来很愚蠢。 有些数据库的复制速度比删除速度快得多。

2. 对表进行分区。 创建 N 个表并使用视图将它们连接成一个。 将行排序到按删除条件分组的不同表中。 这个想法是删除整个表而不是删除单个行。

Answer 3

我想知道解析包含 70K 项的 IN 子句是否有问题。 您是否尝试过使用连接的临时表？

Answer 4

Sybase 可以处理 IN 子句中的 70K 参数吗？ 我使用的所有数据库对 IN 子句的参数数量都有一定的限制。 例如，Oracle 的限制约为 1000。

您可以创建子查询而不是 IN 子句吗？ 这会缩短sql。 也许这对 IN 子句中如此大量的值有帮助。 像这样的事情：

  DELETE FROM OUR_TABLE WHERE ID IN 
        (SELECT ID FROM somewhere WHERE some_condition)

如果数据库模型允许，可以通过对数据库进行一些干预来加快删除大量记录的速度。 以下是一些策略：

1. 您可以通过删除索引、删除记录并再次重新创建索引来加快速度。 这将消除在删除记录时重新平衡索引树的情况。

   • 删除表上的所有索引
   • 删除记录
   • 重新创建索引
   • 如果您与此表有很多关系，并且您绝对确定删除命令不会破坏任何完整性约束，请尝试禁用约束。 删除会更快，因为数据库不会检查完整性。 删除后启用约束。
   • 禁用完整性约束、禁用检查约束
   • 删除记录
   • 启用约束

   • 禁用表上的触发器（如果有的话）并且您的业务规则允许这样做。 删除记录，然后启用触发器。

   • 最后，按照其他建议进行操作 - 制作包含不被删除的行的表的副本，然后删除原始副本，重命名副本并重新创建完整性约束（如果有）。

我会尝试组合 1、2 和 3。如果这不起作用，则使用 4。如果一切都很慢，我会寻找更大的盒子 - 更多内存、更快的磁盘。

Answer 5

找出是什么消耗了性能！

在许多情况下，您可能会使用提供的解决方案之一。 但可能还有其他的（基于Oracle知识，所以在其他数据库上情况会有所不同。编辑：刚刚看到你提到了sybase）：

• 你在那个表上有外键吗？ 确保引用 ID 已建立索引
• 您在该表上有索引吗？ 删除之前删除并删除之后重新创建可能会更快。
• 检查执行计划。 是否使用全表扫描可能更快的索引？ 或者反过来呢？ 提示可能会有所帮助，
• 而不是像上面建议的那样选择 new_table，因为选择可能会更快。

但请记住：首先找出是什么消耗了性能。

当您使用 DDL 语句时，请确保您理解并接受它可能对事务和备份产生的后果。

Answer 6

尝试按照与表或索引存储的顺序相同的顺序对传递到“in”的 ID 进行排序。然后，您可能会在磁盘缓存上获得更多命中。

将要删除的 ID 放入临时表中，该临时表的 ID 排序顺序与主表相同，可以让数据库对主表进行简单扫描。

您可以尝试使用多个连接并通过连接分配工作，以便使用数据库服务器上的所有 CPU，但首先要考虑将删除哪些锁等。

Answer 7

我还认为临时表可能是最好的解决方案。

如果您要执行“delete from .. where ID in (select id from ...)”，那么对于大型查询来说，它仍然会很慢。 因此，我建议您使用联接进行删除 - 许多人不知道该功能。

因此，给定这个示例表：

    -- set up tables for this example
    if exists (select id from sysobjects where name = 'OurTable' and type = 'U')
        drop table OurTable
    go

    create table OurTable (ID integer primary key not null)
    go
    insert into OurTable (ID) values (1)
    insert into OurTable (ID) values (2)
    insert into OurTable (ID) values (3)
    insert into OurTable (ID) values (4)
    go

然后我们可以编写删除代码，如下所示：

    create table #IDsToDelete (ID integer not null)
    go
    insert into #IDsToDelete (ID) values (2)
    insert into #IDsToDelete (ID) values (3)
    go
    -- ... etc ...
    -- Now do the delete - notice that we aren't using 'from'
    -- in the usual place for this delete
    delete OurTable from #IDsToDelete
       where OurTable.ID = #IDsToDelete.ID
    go
    drop table #IDsToDelete
    go
    -- This returns only items 1 and 4
    select * from OurTable order by ID
    go

Answer 8

our_table 有关于删除级联的参考吗？