为什么这个查询不使用索引假脱机？

一尘不染

为什么这个查询不使用索引假脱机？

sql-server

我问这个问题是为了更好地了解优化器的行为并了解索引假脱机的限制。假设我将 1 到 10000 之间的整数放入一个堆中：

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

并强制嵌套循环加入MAXDOP 1：

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

这是对 SQL Server 采取的相当不友好的操作。当两个表都没有任何相关索引时，嵌套循环连接通常不是一个好的选择。这是计划：

该查询在我的机器上花费了 13 秒，从表假脱机中提取了 100000000 行。但是，我不明白为什么查询必须很慢。查询优化器能够通过索引假脱机即时创建索引。这个查询似乎是索引假脱机的完美候选者。

以下查询返回与第一个相同的结果，有一个索引假脱机，并在不到一秒内完成：

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

此查询也有一个索引假脱机并在不到一秒内完成：

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

阅读 187

2022-11-16

共1个答案

一尘不染

如您所知，优化器的搜索并不详尽。它尝试在上下文中有意义的事情，并且经常在实际查询中带来好处。强制在两个单列未索引堆表之间进行循环连接不是这种情况。也就是说，这里有一些细节：

SQL Server 喜欢尽早将转换应用于连接，因为它知道更多连接技巧。稍后，它可能会探索将连接转换回应用。两者之间的差异是相关参数（外部参考）。当内侧有合适的索引时，应用才有意义。您的示例没有索引，因此不会说服优化器探索应用程序的翻译。

简单（非应用）连接在连接运算符上有连接谓词，而不是外部引用。非应用的假脱机优化通常是惰性表假脱机，因为在内部没有谓词，只有在连接处。

优化器不会考虑动态构建索引来启用应用程序；相反，事件的顺序通常是相反的：转换为应用，因为存在一个好的索引。

APPLY有时您可以通过在查询中使用语法来鼓励应用而不是连接。未记录的跟踪标志 9114 可以通过阻止优化器预先将逻辑应用转换为连接来帮助实现这一点。例如：

SELECT * 
FROM dbo.X_1000 AS a
CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b
OPTION (QUERYTRACEON 9114);

索引假脱机适用于应用，因为外部引用意味着选择应用于连接的内侧。您经常会看到这个 viaSelToIndexOnTheFly但存在其他路径。

2022-11-16