如何优化这个sql删除语句

Question

我有3个表，第一个表（table1）具有 id 列作为主键，第二个表（table2）具有列 table1_id ，该列作为 table1.id 的外键，第三个表（table3）具有，作为 table2 ，作为 table1.id 的外键引用的列 table1_id。

我必须从 table1 中删除 table1.id 不在 table2.table1_id 中且不在 table3.table1_id 中的所有行

现在我正在使用此查询：

DELETE FROM table1
WHERE  table1.id IN (SELECT table1.id
                     FROM   (table2
                             RIGHT OUTER JOIN table1
                               ON table2.table1_id = table1.id)
                            LEFT OUTER JOIN table3
                              ON table3.table1_id = table1.id
                     WHERE  table2.table1_id IS NULL
                            AND table3.table1_id IS NULL);  

但它非常慢，需要很多时间，有一些更好的方法到这个删除语句？

如果这可以帮助我可以假设表2比表3有更多的数据。

我使用的数据库是 Apache Derby。

感谢您的帮助。

Answer 1

假设您已经涵盖了明显的内容（为 table1.id、table2.table1_id 和 table3.table1_id 创建的索引），您不需要执行完全外连接只是为了测试某个键是否在另一个表中，您可以在您的情况下使用子查询和 exists() - 或 notexists() 。

由于您只是测试存在性，因此可以使用以下模式：

where not exists ( select top 1 1 from... where... )

Answer 2

DELETE     table1
FROM       table1
LEFT JOIN  table2 ON table1.id = table2.table1_id
LEFT JOIN  table3 ON table1.id = table3.table1_id
WHERE table2.table1_id IS NULL
  AND table3.table1_id IS NULL

Answer 3

您知道要删除多少行吗？我同意@Blindy，如果德比支持的话，在你的情况下不存在可能会更好（我不了解德比，所以我不能肯定地说）。但是，如果有大量记录被删除，您可能需要分批执行此操作。无论查询效率如何，删除 10,000,000 条记录都需要很长时间。在一次执行 1000 个的循环中删除它们通常对数据库来说更好，因为在整个过程完成时不会使用表锁并锁定用户。再说一遍，我不了解 Derby，所以我不知道 Derby 是否属实，但它肯定有助于在我熟悉的大多数数据库中进行大量删除。

Answer 4

DELETE from table1
WHERE 
   table1_id NOT IN (SELECT table1_id FROM table2)
   AND
   table1_id NOT IN (SELECT table1_id FROM table3)