SQL WHERE ID IN (id1, id2，…idn)

我需要编写一个查询来检索一个大的id列表。

我们确实支持许多后端(MySQL, Firebird, SQLServer, Oracle, PostgreSQL…)，所以我需要写一个标准的SQL。

id集的大小可能很大，查询将以编程方式生成。那么，最好的方法是什么呢?

1)使用IN编写查询

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

我的问题是。如果n很大会怎样?还有，性能如何?

2)使用OR编写查询

SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

我认为这种方法没有n的限制，但是如果n很大，性能会怎样呢?

3)编写程序解决方案:

  foreach (var id in myIdList)
{
var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
myObjectList.Add(item);
}

当通过网络查询数据库服务器时，我们使用这种方法遇到了一些问题。通常情况下，做一个查询来检索所有结果比做很多小查询更好。也许我错了。

这个问题的正确解决方法是什么?

517928 次浏览
小开

在大多数数据库系统中，IN (val1, val2, …)和一系列OR被优化为相同的计划。

第三种方法是将值列表导入到一个临时表中并连接它，如果有很多值，这种方法在大多数系统中更有效。

你可能会想读这篇文章:

小开
最佳答案

选项1是唯一好的解决方案。

为什么?

  • 选项2做同样的事情，但你重复了很多次列名;此外，SQL引擎不会立即知道您要检查的值是否是固定列表中的值之一。然而，一个好的SQL引擎可以优化它，使其具有与IN相同的性能。不过，可读性仍然是一个问题……

  • 第三种选择在性能方面非常糟糕。它在每个循环中发送一个查询，并使用小型查询敲打数据库。它还阻止它使用任何优化“值是给定列表中的一个”

小开

另一种方法可能是使用另一个表来包含id值。然后，这个表可以内部连接到table上，以约束返回的行。这样做的主要优点是不需要动态SQL(即使在最好的情况下也很有问题)，而且不需要无限长的IN子句。

您可以截断另一个表，插入大量的行，然后创建一个索引来提高连接性能。它还可以让您将这些行的积累与数据检索分离，从而为您提供更多调优性能的选项。

更新:虽然你可以使用一个临时表，但我并不是说你必须或应该这样做。用于临时数据的永久表是一种常见的解决方案，其优点超出了本文所述。

小开

我想你说的是SqlServer，但在Oracle上你有一个硬限制，你可以指定多少个IN元素:1000。

小开

示例3将是所有示例中表现最差的，因为您在没有明显原因的情况下无数次地访问数据库。

将数据加载到临时表中，然后进行连接，这将是目前为止最快的方法。之后，IN的工作速度应该比or组略快。

小开

Ed guinness的建议确实是一个性能助推器，我有一个这样的问题

select * from table where id in (id1,id2.........long list)

我做了什么:

DECLARE @temp table(
ID  int
)
insert into @temp
select * from dbo.fnSplitter('#idlist#')

然后inner将temp与主表连接起来:

select * from table inner join temp on temp.id = table.id

性能得到了极大的提高。

小开

第一个选择绝对是最好的选择。

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

然而考虑到id列表非常庞大，比如说百万，你应该像下面这样考虑块大小:

  • 将id列表划分为固定数量的块，比如100
  • 块大小应该根据服务器的内存大小来决定
  • 假设你有10000个id，你将有10000/100 = 100个块
  • 每次处理一个数据块，导致100次数据库select调用

为什么要分成几部分?

你永远不会得到内存溢出异常，这在你的场景中很常见。 您将优化数据库调用的数量，从而获得更好的性能

这对我来说一直很有魅力。希望它也能适用于我的其他开发人员:)

小开

在一个有5亿条记录的Azure SQL表上执行SELECT * FROM MyTable where id in()命令导致等待时间为> 7min!

这样做会立即返回结果:

select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id

使用连接。

小开

试试这个

SELECT Position_ID , Position_Name
FROM
position
WHERE Position_ID IN (6 ,7 ,8)
ORDER BY Position_Name
小开
  1. 对于第一个选项
    在临时表中添加id，并添加与主表的内部连接 
CREATE TABLE #temp (column int)
INSERT INTO #temp (column)
SELECT t.column1 FROM (VALUES (1),(2),(3),...(10000)) AS t(column1)

提交答案