主页 / dba / 问题 / 189607
Accepted
cilerler
Asked: 2017-10-29 12:46:38 +0800 CST

从 SQL 表中删除数百万行

  • 772

我必须从 221+ 百万行表中删除 16+ 百万条记录，而且速度非常慢。

如果您分享使以下代码更快的建议，我将不胜感激：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500);
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @BATCHSIZE > 0
        BEGIN
            DELETE TOP (@BATCHSIZE) FROM MySourceTable
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE NOT EXISTS (
                                 SELECT NULL AS Empty
                                 FROM   dbo.vendor AS v
                                 WHERE  VendorId = v.Id
                             );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);             
            PRINT @MSG;
            COMMIT TRANSACTION;
            CHECKPOINT;
        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO

执行计划（仅限 2 次迭代）

在此处输入图像描述

VendorId是PK和non-clustered，这里脚本不使用聚集索引。还有 5 个其他非唯一、非聚集索引。

任务是“删除另一个表中不存在的供应商”并将它们备份到另一个表中。我有 3 张桌子，vendors, SpecialVendors, SpecialVendorBackups. 尝试删除表SpecialVendors中不存在的Vendors记录，并备份已删除的记录，以防我做错了，我必须在一两周内将它们放回去。

sql-server performance

3 个回答

  1. Best Answer

    执行计划显示它正在以某种顺序从非聚集索引中读取行，然后对读取的每个外部行执行查找以评估NOT EXISTS

    在此处输入图像描述

    您正在删除表格的 7.2%。16,000,000 行 3,556 批 4,500

    假设符合条件的行最终分布在整个索引中，那么这意味着它将每 13.8 行删除大约 1 行。

    所以迭代 1 将读取 62,156 行并在找到要删除的 4,500 行之前执行许多索引查找。

    迭代 2 将读取 57,656 (62,156 - 4,500) 行，这些行肯定不符合条件，忽略任何并发更新（因为它们已经被处理），然后再读取 62,156 行以删除 4,500 行。

    迭代 3 将读取 (2 * 57,656) + 62,156 行，依此类推，直到最终迭代 3,556 将读取 (3,555 * 57,656) + 62,156 行并执行那么多查找。

    所以在所有批次中执行的索引搜索次数是SUM(1, 2, ..., 3554, 3555) * 57,656 + (3556 * 62156)

    哪个是((3555 * 3556 / 2) * 57656) + (3556 * 62156)- 或364,652,494,976

    我建议您先将要删除的行具体化到临时表中

    INSERT INTO #MyTempTable
SELECT MySourceTable.PK,
       1 + ( ROW_NUMBER() OVER (ORDER BY MySourceTable.PK) / 4500 ) AS BatchNumber
FROM   MySourceTable
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.vendor AS v
                   WHERE  VendorId = v.Id)

    并将 更改DELETE为删除WHERE PK IN (SELECT PK FROM #MyTempTable WHERE BatchNumber = @BatchNumber)您可能仍需要NOT EXISTS在DELETE查询本身中包含 a 以适应更新，因为临时表已填充，但这应该更有效，因为它只需要执行每批 4,500 次查找。

    • 8

  2. 执行计划表明每个连续的循环将比前一个循环做更多的工作。假设要删除的行均匀分布在整个表中，第一个循环将需要扫描大约 4500*221000000/16000000 = 62156 行以找到要删除的 4500 行。它还将对vendor表执行相同数量的聚集索引搜索。但是，第二个循环需要读取您第一次没有删除的相同 62156 - 4500 = 57656 行。我们可能期望第二个循环从表中扫描 120000 行MySourceTable并对vendor表执行 120000 次搜索。每个循环所需的工作量以线性速率增加。作为一个近似值，我们可以说平均循环需要从 from 读取 102516868 行，MySourceTable并针对vendor桌子。要删除批量大小为 4500 的 1600 万行，您的代码需要执行 16000000/4500 = 3556 次循环，因此您的代码要完成的工作总量约为 3645 亿行读取MySourceTable和 3645 亿次索引搜索。

    一个较小的问题是您@BATCHSIZE在 TOP 表达式中使用局部变量而没有任何RECOMPILE其他提示。查询优化器在创建计划时不会知道该局部变量的值。它将假定它等于 100。实际上，您删除的是 4500 行而不是 100 行，并且由于这种差异，您最终可能会得到一个效率较低的计划。插入表时的低基数估计也会导致性能下降。如果 SQL Server 认为需要插入 100 行而不是 4500 行，它可能会选择不同的内部 API 来执行插入。

    一种替代方法是简单地将要删除的行的主键/聚集键插入到临时表中。根据您的关键列的大小，这可以很容易地适应 tempdb。在这种情况下，您可以获得最少的日志记录，这意味着事务日志不会爆炸。您还可以使用SIMPLE. 有关要求的更多信息，请参阅链接。

    如果这不是一个选项，那么您应该更改您的代码，以便您可以利用MySourceTable. 关键是编写代码，以便每个循环执行大致相同数量的工作。您可以通过利用索引来做到这一点，而不是每次都从头开始扫描表。我写了一篇博客文章，介绍了一些不同的循环方法。该帖子中的示例确实插入到表中而不是删除，但您应该能够调整代码。

    在下面的示例代码中，我假设您的MySourceTable. 我很快就编写了这段代码，但无法对其进行测试：

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500)
        @STARTID BIGINT,
        @NEXTID BIGINT;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

SELECT @STARTID = ID
FROM MySourceTable
ORDER BY ID
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY;

SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @STARTID IS NOT NULL
        BEGIN
            WITH MySourceTable_DELCTE AS (
                SELECT TOP (60000) *
                FROM MySourceTable
                WHERE ID >= @STARTID
                ORDER BY ID
            )           
            DELETE FROM MySourceTable_DELCTE
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE NOT EXISTS (
                                 SELECT NULL AS Empty
                                 FROM   dbo.vendor AS v
                                 WHERE  VendorId = v.Id
                             );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);             
            PRINT @MSG;
            COMMIT TRANSACTION;

            CHECKPOINT;

            SET @STARTID = @NEXTID;
            SET @NEXTID = NULL;

            SELECT @NEXTID = ID
            FROM MySourceTable
            WHERE ID >= @STARTID
            ORDER BY ID
            OFFSET (60000) ROWS
            FETCH FIRST 1 ROW ONLY;

        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO

    关键部分在这里：

    WITH MySourceTable_DELCTE AS (
    SELECT TOP (60000) *
    FROM MySourceTable
    WHERE ID >= @STARTID
    ORDER BY ID
)

    每个循环只会读取 60000 行MySourceTable。这应该导致每个事务的平均删除大小为 4500 行，每个事务的最大删除大小为 60000 行。如果您想更保守地使用较小的批量大小，那也可以。该@STARTID变量在每个循环之后前进，因此您可以避免从源表中多次读取同一行。

    • 4

  3. 脑海中浮现出两个想法：

    延迟可能是由于使用该数据量进行索引。尝试删除索引、删除并重新构建索引。

    或者..

    将要保留的行复制到临时表中，删除包含 1600 万行的表，然后重命名临时表（或复制到源表的新实例）可能会更快。

    • 2

相关问题