MySQL SQL NOT IN [id 列表]（性能）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11163145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL NOT IN [list of ids] (performance)
提问by Pol
I'm just wondering if the amount of id's in a list will influence query performance.
我只是想知道列表中 id 的数量是否会影响查询性能。
query example:
查询示例：
SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)
Where (2, 4, 5, 6, 7)is an indefinitely long list.
哪里(2, 4, 5, 6, 7)是无限长的名单。
And how many is too many (in context of order)?
多少是太多（在顺序的情况下）？
UPDATE:The reason why i'm asking it because i have two db. On of it (read-only) is the source of items and another one contain items that is processed by operator. Every time when operator asking for new item from read-only db I want to exclude item that is already processed.
更新：我问它的原因是因为我有两个数据库。其中（只读）是项目的来源，另一个包含由操作员处理的项目。每次当操作员从只读数据库中请求新项目时，我都想排除已经处理的项目。
回答by Aaron Bertrand
Yes, the amount of IDs in the list will impact performance. A network packet is only so big, for example, and the database has to parse all that noise and turn it into a series of:
是的，列表中的 ID 数量会影响性能。例如，一个网络数据包只有这么大，数据库必须解析所有这些噪音并将其转换为一系列：
WHERE foo.ID <> 2
AND foo.ID <> 4
AND foo.ID <> 5
AND ...
You should consider other ways to let your query know about this set.
您应该考虑通过其他方式让您的查询了解此集合。
回答by RolandoMySQLDBA
Here is wacky rewrite of that query that might perform a little better
这是对该查询的古怪重写，可能会表现得更好一点
SELECT * FROM foos
LEFT JOIN
(
SELECT 2 id UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7
) NOT_IDS
USING (id) WHERE NOT_IDS.id IS NULL;
The NOT_IDS subquery does work as shown by the following:
NOT_IDS 子查询的工作方式如下所示：
mysql> SELECT * FROM
-> (
-> SELECT 2 id UNION
-> SELECT 4 UNION
-> SELECT 5 UNION
-> SELECT 6 UNION
-> SELECT 7
-> ) NOT_IDS;
+----+
| id |
+----+
| 2 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
5 rows in set (0.00 sec)
mysql>
回答by swasheck
Just for fun, and given your update, I'm going to suggest a different strategy:
只是为了好玩，根据你的更新，我将建议一个不同的策略：
You could join across tables like so ...
你可以像这样跨表加入......
insert into db1.foos (cols)
select cols
from db2.foos src
left join db1.foos dst
on src.pk = dst.pk
where dst.othercolumn is null
I'm not sure how the optimizer will handle this or if it's going to be faster (depends on your indexing strategy, I guess) than what you're doing.
我不确定优化器将如何处理这个问题，或者它是否会比你正在做的更快（我猜取决于你的索引策略）。
回答by jurhas
The db's are in the same server? If yes you can make a multi-db query with a left join and take the null ones. (here an example: Querying multiple databases at once) . Otherwise you can make a stored procedure, pass the id's with a string, and split them inside with a regular expression. I have a similar problem, but within an in-memory db and a postgres db. Luckly my situation is (In...)
数据库在同一台服务器上？如果是，您可以使用左连接进行多数据库查询并采用空查询。（这里是一个例子：一次查询多个数据库）。否则，您可以创建一个存储过程，使用字符串传递 id，然后使用正则表达式将它们拆分。我有一个类似的问题，但在内存数据库和 postgres 数据库中。幸运的是我的情况是（在......）
