"where id in(ids)"的性能? [英] performance of "where id in(ids)"?

问题描述

可以使用以下查询吗?表现如何?

is it okay to use the following query? how is the performance?

select * from table where id not in ( 2000 or much more ids here)

我的初始测试很快完成，但是我想这是因为我是目前唯一使用服务器的人.

my initial test comes up very fast, but I guess it is because I am the only who is using the server right now.

推荐答案

如果有索引，它可能会很快.

If you have an index it can be very fast.

但是在MySQL中有一个 bug (可能在MySQL 5.5中已修复)，如果没有索引，它不仅会变慢，还会令人难以置信变慢.这是因为即使没有，子查询也可以被检测为DEPENDENT SUBQUERY(相关子查询).您可以通过运行EXPLAIN SELECT ...并检查子查询的key不是NULL来查看MySQL是否使用了正确的查询计划.我还发布了有关此错误的另一篇文章，其中包含更多详细信息:

However there is a bug in MySQL (possibly fixed in MySQL 5.5) where if there is no index, it won't just be slow, it will be incredibly slow. This because the subquery can be detected as a DEPENDENT SUBQUERY (correlated subquery) even when it is not. You can see whether MySQL is using the correct query plan by running EXPLAIN SELECT ... and checking that key is not NULL for your subquery. I have made another post about this bug with some more details:

• 为什么要使用IN条件比SQL中的"="慢?

您也可以考虑重写查询以使用JOIN而不是IN来避免此错误.

You can also consider rewriting your query to use JOIN instead of IN to avoid this bug.

这篇关于"where id in(ids)"的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！