为什么 SQL Server ce 中的 Select * FROM Table where ID NOT IN (list of int ids) 查询很慢？

Question

这个问题在 sql server ce 中很常见
我在所有字段上都有索引。
也是相同的查询，但使用 ID IN（int ids 列表）非常快。
我尝试将查询更改为 OUTER Join 但这只会让情况变得更糟。 那么关于为什么会发生这种情况以及如何解决这个问题有什么提示吗？

Answer 1

ammoQ 是对的，索引对您的查询没有多大帮助。根据 ID 列中值的分布，您可以通过指定选择而不是不选择哪些 ID 来优化查询。如果你最终请求超过~25%的表索引无论如何都不会被使用，因为对于非聚集索引（这是SQL CE在内存可用的情况下支持的唯一索引类型），扫描表会更便宜。否则（如果查询实际上是选择性的），您可以使用要选择的 ID 范围重写查询（如果 SQL CE 支持“union all”，则“union all”可能比“or”更好地组合范围，不确定）

Answer 2

这不是SQL Server CE 中的问题，而是整个数据库的问题。

OPERATION IN 是可控制的，而 NOT IN 是不可控制的。

这是什么意思？

Search ARGument Able，这意味着 DBMS 引擎可以利用索引，对于 Non Search ARGument Ablee 则不能使用索引。

解决方案可能是使用过滤器语句来删除这些 ID

更多信息请参阅 Peter Gulutzan 的《SQL 性能调优》。

Answer 3

那是因为索引对于这种查询并没有真正的帮助，所以数据库必须进行全表扫描。如果查询（由于某种原因）比简单的“SELECT * FROM TABLE”慢，请改为执行此操作并过滤程序中不需要的 ID。

编辑：根据您的评论，我认识到您使用子查询而不是列表。因此，有三种可能的方法可以做到这一点（希望其中一种更快）：

原始声明：

select * from mytable where id not in (select id from othertable);

替代方案 1：

select * from mytable where not exists 
   (select 1 from othertable where mytable.id=othertable.id);

替代方案 2：

select * from mytable
minus
select mytable.* from mytable in join othertable on mytable.id=othertable.id;

替代方案 3：（丑陋且难以理解，但如果其他一切都失败了......）

select * from mytable
  left outer join othertable on (mytable.id=othertable.id)
  where othertable.id is null;