如何优化此查询以删除其他表中没有相应Id的记录

Question

我有一个"主"；由50万条记录组成的表，其中包含一个主键Id(MovieId(，该主键Id被其他几个表用作外键(多对多表(。

这些多对多表中的一些表有数百万条记录(高达2000万条(。

我想从主表中不存在外键的多对多表中删除所有记录。这将极大地减少它们的大小(减少到每个"只有"一百万或两百万条记录(。

但SQL实现这一点似乎非常耗时——基本上要循环2000多万条记录，每次都要查看主表中多达50万条记录，看看2000万条多对多表记录中的外键是否作为主键存在于50万条主表记录中。

我可以想象这需要很长时间。有(相对(快速的方法吗？

我实现这一目标的第一个想法是：

DELETE FROM ACTORS_MOVIES_M2M
WHERE MovieiD NOT EXISTS (SELECT MovieiD FROM MOVIES_MAIN)

再说一遍，我觉得这需要。。。一段时间。

Answer 1

您想要编写的查询：

delete m2m 
from actors_movies_m2m m2m
where not exists (select 1 from movies_main m where m.movieid = m2m.movieid)

movies_main(movieid)上的索引将有助于子查询快速执行(前提是movieid是movies_main的主键，它已经存在(。

虽然这在技术上是正确的，但这可能不是最有效的方法。如果你要删除表格的重要部分，那么清空并重新填充它可能会更有效

create table tmp_actors_movies_m2m as
select * 
from actors_movies_m2m m2m
where exists (select 1 from movies_main m where m.movieid = m2m.movieid)
truncate table actors_movies_m2m;  -- back it up first!
insert into actors_movies_m2m 
select * 
from tmp_actors_movies_m2m;
drop table actors_movies_m2m;

请注意，您的问题本身表明存在潜在的设计问题。通过使用on delete cascade选项设置正确的外键，您可以从一开始就避免孤立记录：

create table actors_movies_m2m  (
... -- columns here
movieid int references movies_main (movieid) on delete cascade
);