删除给定列表中除行号之外的所有其他行

Question

所以基本上这就是我想要做的：我有一个帐户表，我有一个 acct_id 列表：(3, 24, 515, 6326, 17)，假设表中有大约 100,000 个帐户，最有效的方法是什么删除给定列表中除了包含 account_id 的行之外的所有其他行吗？

我想出了这样的想法：

delete from account where acct_id is not in (3, 24, 515, 6326, 17);

我听说这个查询很慢，因为它是递归的或者其他什么。考虑一下我拥有的行数，这会非常慢。有什么更好的方法来做到这一点？

Answer 1

delete from table
 where not acct_id in (3, 24, 515, 6326, etc.);

根据数据库风格、索引、分布式与否等，这可能需要大量工作。即使在完整日志数据库中也能有效工作的替代方案是：

create table2 temp as /* create new table from the rows to keep */
   select *
   from table
   where acct_id in (3, 24, 515, 6326, etc.);
drop table;           /* discard table */
create table as       /* copy new table to rename */
  select * from table2;
drop table2;          /* get rid of temporary table */

Answer 2

您的查询对我来说似乎很好，但请看一下 解释如果您正在尝试优化查询。

Answer 3

如果您在 acct_id 上有索引，我看不出您的查询应该很慢的任何原因。
据我所知，

in (3, 24, 515, 6326, 17)

这只是语法糖，

acct_id != 3 AND acct_id != 24 ...

应该足够快。

Answer 4

并非特定于 MySQL，但删除通常相对昂贵，因为它们需要引擎执行大量选择以确保它删除正确的记录以及实际的删除。您最终还会在事务日志中添加大量事务（当然取决于引擎和设置）。

如果您只有要保留的一小部分记录和要丢弃的一大组记录，那么您可以通过作弊获得更快的性能...

您复制要保留的记录，然后删除或截断表，然后将“守护者”添加回来。

Answer 5

我的解决方案是避免 DELETE 并使用 TRUNCATE 表，因为删除数据库时会执行两个操作。删除记录并将其写入回滚段。

当然，这意味着截断时不会回滚。

-- copy the few records into a temp table
select into temp 
 select * from account
 where acct_id in (3, 24, 515, 6326, 17);

-- truncate is super fast
truncate table account;

-- put back the few records
insert into account select * from temp;

drop table temp;