删除重复数据的最快技术

Question

在搜索 stackoverflow.com 后，我发现了几个询问如何删除重复项的问题，但没有一个解决速度问题。

就我而言，我有一个包含 10 列的表，其中包含 500 万个精确的行重复项。此外，我还有至少一百万行在 10 列中的 9 列中存在重复项。我当前的技术（到目前为止）需要 3 小时来删除这 500 万行。这是我的过程：

-- Step 1:  **This step took 13 minutes.** Insert only one of the n duplicate rows into a temp table
select
    MAX(prikey) as MaxPriKey, -- identity(1, 1)
    a,
    b,
    c,
    d,
    e,
    f,
    g,
    h,
    i
into #dupTemp
FROM sourceTable
group by
    a,
    b,
    c,
    d,
    e,
    f,
    g,
    h,
    i
having COUNT(*) > 1

接下来，

-- Step 2: **This step is taking the 3+ hours**
-- delete the row when all the non-unique columns are the same (duplicates) and
-- have a smaller prikey not equal to the max prikey
delete 
from sourceTable
from sourceTable
inner join #dupTemp on  
    sourceTable.a = #dupTemp.a and
    sourceTable.b = #dupTemp.b and
    sourceTable.c = #dupTemp.c and
    sourceTable.d = #dupTemp.d and
    sourceTable.e   = #dupTemp.e and
    sourceTable.f = #dupTemp.f and
    sourceTable.g = #dupTemp.g and
    sourceTable.h = #dupTemp.h and
    sourceTable.i   = #dupTemp.i and
    sourceTable.PriKey != #dupTemp.MaxPriKey  

关于如何加快速度或更快的方法有什么建议吗？请记住，对于不完全重复的行，我必须再次运行此操作。

非常感谢。

更新：
我不得不在 9 小时处停止运行第 2 步。 我尝试了OMG Ponies的方法，只用了40分钟就完成了。 我用 Andomar 的批量删除尝试了步骤 2，它运行了 9 个小时才停止。 更新： 使用 OMG Ponies 的方法运行了一个类似的查询，但少了一个字段以消除一组不同的重复项，并且该查询仅运行了 4 分钟（8000 行）。

下次有机会我会尝试 cte 技术，但是，我怀疑 OMG Ponies 的方法将很难被击败。

Answer 1

存在怎么样：

DELETE FROM sourceTable
 WHERE EXISTS(SELECT NULL
                FROM #dupTemp dt
               WHERE sourceTable.a = dt.a 
                 AND sourceTable.b = dt.b 
                 AND sourceTable.c = dt.c 
                 AND sourceTable.d = dt.d 
                 AND sourceTable.e = dt.e 
                 AND sourceTable.f = dt.f 
                 AND sourceTable.g = dt.g 
                 AND sourceTable.h = dt.h 
                 AND sourceTable.i = dt.i 
                 AND sourceTable.PriKey < dt.MaxPriKey)

Answer 2

您能承受原表在短时间内不可用的情况吗？

我认为最快的解决方案是创建一个没有重复项的新表。基本上是与临时表一起使用的方法，但创建了一个“常规”表。

然后删除原始表并将中间表重命名为与旧表同名。

Answer 3

批量行删除的瓶颈通常是 SQL Server 必须建立的事务。通过将删除操作分成较小的事务，您也许可以大大加快速度。例如，一次删除 100 行：

while 1=1
    begin

    delete top 100
    from sourceTable 
    ...

    if @@rowcount = 0
        break
    end

Answer 4

...基于上面 OMG Ponies 的评论，这是一种更紧凑的 CTE 方法。此方法在您（无论出于何种原因）没有主键的表上产生奇迹 - 您可以在所有列上拥有相同的行。

;WITH cte AS (
 SELECT ROW_NUMBER() OVER 
          (PARTITION BY a,b,c,d,e,f,g,h,i ORDER BY prikey DESC) AS sequence
    FROM sourceTable
)
DELETE
FROM cte
WHERE sequence > 1

Answer 5

嗯，有很多不同的东西。首先会做这样的工作（做一个选择或确保，甚至可能放入它自己的临时表中，#recordsToDelete）：

delete  
from sourceTable 
left join #dupTemp on   
       sourceTable.PriKey = #dupTemp.MaxPriKey   
where #dupTemp.MaxPriKey  is null

接下来你可以索引临时表，在prikey上放置索引

如果你的临时表中有记录对于要删除的表，您可以批量删除，这通常比通过删除锁定整个表更快。

Answer 6

在这个版本中，您可以将这两个步骤合并为一个步骤。

WITH cte AS
    ( SELECT prikey, ROW_NUMBER() OVER (PARTITION BY a,b,c,d,e,f,g,h,i ORDER BY
        prikey DESC) AS sequence
    FROM sourceTable
    )

DELETE
FROM sourceTable
WHERE prikey IN
    ( SELECT prikey
    FROM cte
    WHERE sequence > 1
    ) ;

顺便问一下，有没有可以暂时删除的索引？

Answer 7

如果您使用的是 Oracle 数据库，我最近发现从总持续时间以及 CPU 消耗的角度来看，以下语句执行效果最佳。
我已经使用不同的数据大小（从数十行到数千行）执行了多次测试，并且总是在循环中。我使用TKProf工具来分析结果。

与上面的 ROW_NUMBER() 解决方案相比，这种方法花费了原始时间的 2/3，并且消耗了大约 50% 的 CPU 时间。它似乎表现出线性，即对于任何输入数据大小都应该给出相似的结果。

请随时向我提供您的反馈。我想知道是否有更好的方法。

DELETE FROM sourceTable
WHERE
    ROWID IN(
        -- delete all
        SELECT ROWID
        FROM sourceTable t
        MINUS
        -- but keep every unique row
        SELECT
            rid
        FROM
            (
            SELECT a,b,c,d,e,f,g,h,i, MAX(ROWID) KEEP (DENSE_RANK FIRST ORDER BY ROWID) AS RID
            FROM sourceTable t
            GROUP BY a,b,c,d,e,f,g,h,i
            )
    )
;