优化数百万行的“不在（…）”查询

Question

注意：我无法访问与此问题相关的源代码/数据库。这两个表位于不同的服务器上。

我在一家第三方公司工作，该公司的系统与我们自己的系统集成。他们有一个运行类似这样的查询；

DELETE FROM table WHERE column NOT IN(1,2,3,4,5,.....3 000 000)

它在中引用了大约300万个值，而不是在中。

我试图指出，对于删除多行并保留查询中记录的所有行来说，这似乎是一种低效的方法。问题是，由于我自己没有访问源代码/数据库的权限，我不完全确定应该建议什么解决方案。

我知道这个查询的想法是保持目标服务器与源服务器同步。因此，如果在源服务器上删除了一行，则目标服务器将在运行此（和其他）查询时反映该更改。

由于知识有限，我可以向他们提出哪些可能的建议？

首先想到的是有一个标志栏，指示它是否被删除。当同步脚本运行时，它将首先在目标服务器上对标记为已删除的所有行执行更新（或插入新行），然后执行第二次查询以删除标记为已删除的所有行。

有没有更合乎逻辑的方法来做这样的事情，记住，功能上的全面检修是不可能的。由于许多原因，对当前流程只可能进行一些小的调整。




Answer 1

匿名用户

删除大量行将花费大量时间。这可能需要进行完整的表扫描。当它找到要删除的行时，它将强调撤消/重做日志。它将阻塞复制（如果使用这种方法）。等

您希望删除多少行？

最好将列表分成1000块。（无论是在（常量列表）中使用，还是在JOIN中使用，这都适用），但是，由于您不这样做，它会变得更粘。最好的方法可能是复制您想要的：

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE id IN (...);  -- without NOT
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

我将详细介绍大删除中的分块、分区和其他技术。

Answer 2

我知道这个查询的想法是保持目标服务器与源服务器同步。因此，如果在源服务器上删除了一行，则目标服务器将在运行此（和其他）查询时反映该更改。

我知道这是显而易见的，但为什么这两台服务器不使用复制保持同步呢？我猜这是因为除了这张表，他们没有相同的数据。

如果开箱即用复制不够灵活，可以使用更改数据捕获工具。

这个想法是，该工具监控MySQL二进制日志流中的变化，并对其做出反应。反应是用户定义的，它可以包括将相同的更改应用到另一个MySQL实例，这将使它们保持同步。

这里有一个博客展示了如何使用Maxwell，它是开源CDC工具之一，这是Zendesk发布的：https://www.percona.com/blog/2016/09/13/mysql-cdc-streaming-binary-logs-and-asynchronous-triggers/

这种方法有两个优点：

• 无需重新同步整个表。您只会在发生增量更改时应用它们。
• 无需每天安排重新同步或其他。由于增量更改可能很小，您几乎可以立即应用更改。

Answer 3

而不是

DELETE FROM your_table 
WHERE column NOT IN(1,2,3,4,5,.....3 000 000)

你可以做的

delete t1
from your_table t1
left join table_where_the_ids_come_from t2 on t1.column = t2.id
where t2.id is null