您可以使用LEFT JOIN来链接数据表，以便在WHERE子句中识别数据表banks和employee中没有数据列的town_id：

WITH list AS
( SELECT t.town_id
    FROM towns AS t
    LEFT JOIN tbl.banks AS b ON b.town_id = t.town_id
    LEFT JOIN tbl.employees AS e ON e.town_id = t.town_id
   WHERE t.vendor_id IS NULL
     AND b.town_id IS NULL
     AND e.town_id IS NULL
   LIMIT 1000
)
DELETE FROM tbl.towns AS t
  USING list AS l
 WHERE t.town_id = l.town_id ;

在启动DELETE之前，可以检查表的索引。按如下方式添加索引可能很有用：

CREATE INDEX town_id_nulls ON towns (town_id NULLS FIRST) ;

最后但并非最不重要的是，您可以在cte中添加LIMIT子句，以便限制执行DELETE时删除的行数，并避免意外终止。因此，您必须多次重新启动DELETE，直到没有更多行可删除为止。

您可以在大表上尝试JOIN，它会比两个IN更快
您也可以尝试使用UNION ALL并使用副本，因为它比UNION更快
最后，您可以对id和vendor_id使用组合索引，以加快查询速度
第一个问题
| 计数器|
| - -|
| 第0页|

SELECT 1

fiddle

诀窍是首先创建一个列表，列出所有你想保留的town_id，然后开始删除那些不存在的。在两个表中查找会让服务器的工作更加困难，所以让我们先创建一个列表。

-- build empty temp-table
CREATE TEMPORARY TABLE TEMP_must_keep 
AS
SELECT town_id       
  FROM tbl.towns
 WHERE 1 = 2;
 
-- get id's from first table
INSERT TEMP_must_keep (town_id)
SELECT DISTINCT town_id 
  FROM tbl.banks;
  
-- add index to speed up the EXCEPT below
CREATE UNIQUE INDEX idx_uq_must_keep_town_id ON TEMP_must_keep (town_id);

-- add new ones from second table
INSERT TEMP_must_keep (town_id)
SELECT town_id 
  FROM tbl.employees
 
EXCEPT -- auto-distincts

SELECT town_id 
  FROM TEMP_must_keep;
  
-- rebuild index simply to ensure little fragmentation
REINDEX TABLE TEMP_must_keep;

-- optional, but might help: create a temporary index on the towns table to speed up the delete
CREATE INDEX idx_towns_town_id_where_vendor_null ON tbl.towns (town_id) WHERE vendor IS NULL;

-- Now do actual delete
-- You can do a `SELECT COUNT(*)` rather than a `DELETE` first if you feel like it, both will probably take some time depending on your hardware.
DELETE 
  FROM tbl.towns as del      
 WHERE vendor_id is null 
   AND NOT EXISTS ( SELECT * 
                      FROM TEMP_must_keep mk
                     WHERE mk.town_id = del.town_id);
                     
                     
-- cleanup
DROP INDEX tbl.idx_towns_town_id_where_vendor_null;
DROP TABLE TEMP_must_keep;

idx_towns_town_id_where_vendor_null是可选的，我不确定它是否会真正降低总时间，但IMHO它将有助于DELETE操作，如果只是因为索引应该给予查询优化器一个更好的视图，什么卷预期。