SQL IN会影响性能吗？

Question

SQL IN会影响性能吗？

sqlsql-server-2000

93

我有一个执行类似以下操作的查询：

SELECT FieldX, FieldY FROM A
WHERE FieldW IN (108, 109, 113, 138, 146, 160,
307, 314, 370, 371, 441, 454 ,457, 458, 479, 480,
485, 488, 490, 492, 519, 523, 525, 534, 539, 543,
546, 547, 550, 564, 573, 629, 642, 643, 649, 650,
651, 694, 698, 699, 761, 762, 768, 772, 773, 774,
775, 778, 784, 843, 844, 848, 851, 852, 853, 854,
855, 856, 857, 858, 859, 860, 861, 862, 863, 864,
865, 868, 869, 871, 872, 873, 891)

有这么多选项的IN子句是否会影响查询性能？我的应用程序经常出现超时问题，我认为这可能是问题的来源。在不删除数字的情况下，我能否通过使用任何好的SQL提示来优化查询？

编辑：@KM 这些是不同表格中的键。这是一个论坛应用程序，简要解释一下：C#从数据库中获取所有论坛并将其存储在应用程序高速缓存中。在C#调用获取这些论坛和此用户的线程的过程之前，C#会进行一些逻辑过滤“所有论坛”集合，考虑权限和一些业务逻辑。超时发生在数据库上，而不是应用程序本身。在查询中执行所有这些逻辑将需要大量的内部连接，我不能百分之百确定我能否在过程内执行所有这些逻辑。

我正在使用SQL Server 2000。

- Victor Rodrigues

4

这些数字是随机的吗？还是来自其他地方？也许你可以创建一个单独的表格，将这些数字放进去并建立索引，然后在选择语句中使用连接操作。 - Stan R.

不，它们不是随机数。在返回这些 ID 之前执行了一些查询（然后缓存）。由于在执行（缓存的）查询之后今天在 C# 中完成了一些应用程序逻辑，因此进行连接相当困难。 - Victor Rodrigues

从 SQL Server 的角度来看，它们是随机的，并且必须逐个查询。如果没有模式，我不知道你能否编写一个好的提示。您可以尝试使用多线程-将数字分成较小的块并发送更多具有较小列表的查询。 - quillbreaker

@OP，请告诉我们该字段是否有索引，如果有，我必须假设超时的原因必须在其他地方搜索... - tekBlues

@Victor，你说这是一个论坛应用程序。有没有网址可以让我注册并登录，讨论详情？你的设计描述在我的脑海中引发了一堆问题。 - dkretz

显示剩余8条评论

14个回答

7

你可以尝试创建一个临时表，将你的值插入到其中，并在IN谓词中使用该表代替。

据我所知，SQL Server 2000无法构建一个常量集的哈希表，这使得优化器无法使用HASH SEMI JOIN。

只有在FieldW上没有索引（但你应该有）时才会有帮助。

你也可以尝试将你的FieldX和FieldY列包含到索引中：

CREATE INDEX ix_a_wxy ON a (FieldW, FieldX, FieldY)

以便只使用索引来服务查询。

SQL Server 2000缺乏CREATE INDEX的INCLUDE选项，这可能会稍微降低DML性能，但提高查询性能。

更新：

从您的执行计划中，我看到您需要在(SettingsID，SectionID)上创建一个组合索引

SQL Server 2000确实可以将常量列表构建为哈希表（并且确实这样做），但Hash Semi Join很可能比查询查询的Nested Loop效率更低。

另外一件事：如果您需要知道满足WHERE条件的行数，请不要使用COUNT(column)，请改用COUNT(*)。

COUNT(column)不会计算column值为NULL的行数。

这意味着，首先，您可能会得到意想不到的结果，其次，如果您的列没有被服务于WHERE条件的索引覆盖，优化器将需要进行额外的Key Lookup / Bookmark Lookup。

由于ThreadId似乎是一个CLUSTERED PRIMARY KEY，对于这个查询来说没问题，但是一般情况下应该避免使用它。

- Quassnoi

1

我很想看到有人测试这个断言，即测试IN的性能与创建临时表和连接的性能相比如何。 - tekBlues

@tekBlues：抱歉手头没有2000版本。2005版本可以使用常量扫描方法在IN子句值上构建哈希表。请问您能否构建查询的执行计划并在此处发布？ - Quassnoi

1

@tekBlues - 我们使用的方法是为此目的创建一个临时表。为了提高性能，您必须将行插入到临时表中，然后更新临时表上的统计信息 - 否则，SQL执行计划优化器会认为临时表为空并执行全扫描。这种方法确实比使用IN或OR子句的查询更快，并且因为它可以避免解析查询和构建执行计划的步骤。 - LBushkin

创建这个临时表，向其中插入N次数据，更新其统计信息，并在将临时表与我的查询连接后，所有这些操作是否比IN子句更快？ - Victor Rodrigues

@Victor：你能否发布一下你查询的当前执行计划？ - Quassnoi

显示剩余6条评论

5

如果你在FieldW上有一个良好的索引，使用IN是完全正确的。

我刚刚测试了一下，当使用IN时，SQL 2000会进行聚集索引扫描。

- tekBlues

2

那样并不一定是好事。它应该进行查找而不是扫描，这表明使用IN并不是“完全正确”的。但表的大小、基数和其他因素也很重要。 - dkretz

1

@tekBlues：您能否看一下它是否在常量扫描上进行哈希匹配？只需在查询末尾添加一个OPTION（HASH JOIN）并查看计划即可。 - Quassnoi

@Quassnoi，我添加了HASH JOIN，但执行计划没有改变。 - tekBlues

1

理想情况下，您希望查询针对每个值专门搜索索引，而不是从头到尾阅读整个索引（“扫描”）。如果只有一个键或键是有序集，则更有可能这样做。但是，如果键不在表中（有序或易于排序），则查询优化器可能会执行一些次优操作。此外，很明显您已经将这些值放在了（唯一可能的）聚集索引中，这对于 OP 可能是真实的，也可能不是重要的。 - dkretz

2

实际问题是应用程序超时，可能是由于此查询，也可能是由于缓慢，或者由于锁定。这与构建哈希表的能力相去甚远。您是否至少想先查看查询计划呢？只有在确定存在问题后，优化查询才有意义。 - dkretz

显示剩余8条评论

5

根据您的数据分布情况，在WHERE子句中添加其他谓词可能会提高性能。例如，如果ID集相对于表中的总数较小，并且您知道这些ID相对靠近（也许它们通常是最近添加的，因此聚集在范围的高端），您可以尝试包含谓词"AND FieldW BETWEEN 109 AND 891"（在C#代码中确定集合中的最小值和最大值后）。如果有索引，对这些列进行区间扫描可能比当前使用的方法更快。

- Steve Broberg

3

有更好的编程方式，但我怀疑这不是超时的原因，特别是如果只是 SELECT 操作。你可以通过查看查询跟踪记录来确定原因。不过重新编写代码只是在猜测优化，而且这种猜测是不太可能的。

让我们从实际超时的查询计划开始。你确定是哪个查询导致的超时吗？

- dkretz

2

IN与编写OR列表的效果完全相同。而OR通常会导致查询无法使用索引，因此可能会忽略您的索引并进行全表扫描。

- Remus Rusanu

1

我一般会使用用户定义的表类型来处理类似这样的查询。

CREATE TYPE [dbo].[udt_int] AS TABLE (
    [id] [int] NOT NULL
)

使用表变量，并为每个数字填充行，您可以执行以下操作：

SELECT 
    FieldX, 
    FieldY
FROM A
INNER JOIN @myIds B ON
    A.FieldW = B.id

- Donald.Record

1

这会导致性能更差。在一个 @myIds 表中有 100 个身份标识和一个需要 5 秒才能执行的繁重查询，使用 IN (select id from @myIds) 降级到 12 秒，而在 SqlServer 13 中使用 INNER JOIN。 - Patrik Melander

1

基本上，where子句所做的是“FieldW = 108 OR FieldW = 109 OR FieldW = 113...”。有时候，通过执行多个select语句并将它们与union组合起来，可以获得更好的性能。例如：

SELECT FieldX, FieldY FROM A WHERE FieldW = 108
UNION ALL
SELECT FieldX, FieldY FROM A WHERE FieldW = 109

但是当你要比较如此多的值时，这显然是不切实际的。

另一个选择可能是将这些值插入到临时表中，然后将A表与该临时表连接。

- Tommi

1

在这种情况下，我会谨慎使用UNION语句。实际上，UNION语句对最终结果集执行了SELECT DISTINCT的等效操作。换句话说，UNION将两个类似的记录集合并起来，然后执行SELECT DISTINCT以消除任何重复行。换句话说，在底层你将运行指数级别的SELECT语句。 - Adam McKee

1

通常情况下，IN子句对性能有害，但是“不好”的具体表现取决于应用程序、数据、数据库大小等因素。您需要测试自己的应用程序以确定最佳方案。

- Bryan Migliorisi

嗨，Bryan，你所说的“有害于性能”是什么意思？场景是我想为一个字段过滤某些值。最好的方法是什么？在我看来，使用IN子句是最好的选择。 - tekBlues

1

性能只能在您尝试做什么的情况下进行评估。在这种情况下，您正在请求检索大约70行（假设它们是唯一值），因此您可以期望类似于检索单个值的持续时间的70倍。当然，由于缓存，可能会少一些。

但是，查询优化器可能需要或选择执行完整表扫描以检索值，在这种情况下，性能与通过相同访问计划检索单个值几乎没有区别。

- David Aldridge

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- LBushkin · Accepted Answer

在使用IN运算符编写查询时，有几个要考虑到的因素会对性能产生影响。

首先，大多数数据库通常会在内部重写IN子句以使用OR逻辑连接词。 因此col IN ('a','b','c')将被重写为：(COL = 'a') OR (COL = 'b') or (COL = 'c')。假设你在col上拥有索引，这两个查询的执行计划会很可能是等效的。

其次，在使用具有可变参数数量的IN或OR时，每次参数更改时，你都会导致数据库重新解析查询并重建执行计划。 构建查询的执行计划可以是一个昂贵的步骤。大多数数据库使用完全相同的查询文本作为键来缓存它们运行的查询的执行计划。如果你执行类似的查询但使用谓词中不同的参数值 - 你很可能会导致数据库花费大量时间解析和构建执行计划。这就是为什么强烈建议使用绑定变量来确保最佳的查询性能。

第三，许多数据库都有一个可以执行的查询复杂度限制 - 其中之一是谓词中可以包含的逻辑连接数目。 在你的情况下，几十个值不太可能达到数据库内置限制，但如果你预计将数百或数千个值传递给IN子句 - 这种情况肯定会发生。在那种情况下，数据库将简单地取消查询请求。

第四点，在查询谓词中包含IN和OR的情况下，并不总是能够在并行环境中进行最优重写。在各种情况下，都有可能无法应用并行服务器优化。有关为并行处理优化查询的介绍，MSDN提供了一个相当不错的引入。通常情况下，使用UNION ALL运算符的查询在大多数数据库中都可以很容易地并行化 - 并且在可能的情况下，比逻辑连接符（如OR和IN）更可取。