主页 / dba / 问题 / 173939
Accepted
Joe Obbish
Asked: 2017-05-19 05:47:56 +0800 CST

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

  • 772

我问这个问题是为了更好地了解优化器的行为并了解索引假脱机的限制。假设我将 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);

解决方法 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);

解决方法 2

为什么原始查询没有索引假脱机？是否有任何一组记录或未记录的提示或跟踪标志会给它一个索引假脱机？我确实找到了这个相关的问题，但它并没有完全回答我的问题，而且我无法让神秘的跟踪标志适用于这个查询。

sql-server optimization

1 个回答

  1. Best Answer

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

    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);

    阀芯计划

    索引线轴更适合应用，因为外部参考意味着选择应用于连接的内侧。你会经常看到这个，SelToIndexOnTheFly但存在其他路径。请参阅我的文章The Eager Index Spool 和 The Optimizer。

    • 21

相关问题