php - 获取要删除的 Mysql 重复行的 ID

Question

我正在尝试编写一个 Laravel Eloquent 声明来执行以下操作。

查询一个表并获取所有重复行的所有 ID(或者理想情况下是除重复的第一个实例的 ID 之外的所有 ID)。

现在我有以下 mysql 语句:

select `codes`, count(`codes`) as `occurrences`, `customer_id` from `pizzas` 
group by `codes`, `customer_id` 
having `occurrences` > 1;

重复项是共享 codes 和 customer_id 组合的任何行，示例:

codes,customer_id
183665A4,3
183665A4,3
183665A4,3
183665A4,3
183665A4,3

我正在尝试删除其中 1 个以外的所有内容。

这将返回一组代码，包括它们的出现和它们的 customer_id，因为我只想要同时具有这两者的行。

目前我想通过这个循环，并保存第一个实例的 ID，然后再次调用它并删除任何没有该 ID 的实例。这似乎不是很快，因为大约有 5000 万行，所以每个查询都需要很长时间，而且我们有多个查询要删除每个重复项。

// get every order that shares the same code and customer ID
$orders = Order::select('id', 'codes', DB::raw('count(`codes`) as `occurrences`'), 'customer_id')
            ->groupBy('codes')
            ->groupBy('customer_id')
            ->having('occurrences', '>', 1)
            ->limit(100)
            ->get();

        // loop through those orders
        foreach ($orders as $order)
        {
            // find the first order that matches this duplicate set
            $first_order = Order::where('codes', $order->codes)
                                ->where('customer_id', $order->customer_id)
                                ->first();
            
            // delete all but the first        
            Order::where('codes', $order->codes)
                ->where('customer_id', $order->customer_id)
                ->where('id', '!=', $first_order->id)
                ->delete();
        }

必须有一种更有效的方法来追踪共享相同代码和 customer_id 的所有行，并删除所有重复项但保留第一个实例，对吗？哈哈

我在想，如果我可以在结果中添加一个伪造的列，它是每个 ID 的数组，那么我至少可以删除第一个 ID 并删除其他 ID。

Answer 1

不涉及PHP

This seems not very fast

问题中的逻辑本质上很慢，因为它有很多查询，并且每个查询都有:

• DB<->PHP 网络往返
• PHP ORM 逻辑/开销

鉴于问题中的数字，整个代码需要调用多达 10k 次(如果这 200 万条重复记录中的每条记录恰好出现 2 次)，为了论证起见，假设总共有 1k 组重复项那是:

• 1,000 个查询发现重复项
• 100,000 个查询找到第一条记录
• 100,000 个删除查询

201,000 个查询很多并且 php 开销使其速度慢了一个数量级(根据经验猜测)。

直接在数据库上做

只需消除 php/orm/network(即使它们在同一台机器上)时间就会使过程显着加快，这将涉及编写一个过程来模拟问题中的 php 逻辑。

不过还有更简单的方法，具体看情况。在评论中你说:

• 该表的大小为 140GB
• 它包含 5000 万行
• 大约 200 万条是重复记录
• 没有足够的可用空间来制作表格的副本

从表面上看这些评论，我建议的过程是:

确保你有一个功能性的数据库备份

在做任何事情之前，请确保您有一个功能性的数据库备份。如果你设法犯了一个错误，例如删除表 - 确保您可以在不丢失数据的情况下恢复。

无论如何，您将首先在数据库副本上测试此过程，对吧:)？

创建一个“ids to keep”表并填充它

这是 removing duplicate with a unique index 的排列:

CREATE TABLE ids_to_keep (
    id INT PRIMARY KEY,
    codes VARCHAR(50) NOT NULL, # use same schema as source table
    customer_id INT NOT NULL, # use same schema as source table
    UNIQUE KEY derp (codes,customer_id)
);

INSERT IGNORE INTO ids_to_keep 
    SELECT id, codes, customer_id from pizzas;

Mysql 将静静地删除与唯一索引冲突的行，从而生成一个表，每个代码一个 id + customer_id 元组。

如果您没有空间放这张 table - 腾出空间 :)。它不应该太大； 140GB 和 50M 行意味着每行大约 3kb - 这个临时表可能需要原始大小的个位数百分比。

删除重复记录

在执行任何预期会变慢的查询之前，使用 EXPLAIN 检查查询是否会在合理的时间内完成。

作为单个查询运行:

DELETE FROM 
  pizzas 
WHERE 
  id NOT IN (SELECT id from ids_to_keep);

如果你想分块做事:

DELETE FROM 
  pizzas 
WHERE 
  id BETWEEN (0,10000) AND
  id NOT IN (SELECT id from ids_to_keep);

清理

一旦不再需要该表，就将其删除:

DROP TABLE ids_to_keep;

确保不再发生这种情况

为了防止这种情况再次发生，给表添加一个唯一索引:

CREATE UNIQUE INDEX ON pizzas(codes, customer_id);