sql-server – 在不返回任何行的查询中包含ORDER BY会严重影响性

给定一个简单的三表连接,即使没有返回任何行,当包含ORDER BY时,查询性能也会发生巨大变化.实际问题场景需要30秒才能返回零行,但在不包括ORDER BY时是即时的.为什么？

SELECT * 
FROM tinytable t                          /* one narrow row */
JOIN smalltable s on t.id=s.tinyId        /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3                       /* doesn't match */
ORDER BY b.CreatedUtc          /* try with and without this ORDER BY */

我知道我可以在bigtable.smallGuidId上有一个索引,但是,我认为在这种情况下实际上会让它变得更糟.

这是创建/填充表以供测试的脚本.奇怪的是,小型表具有nvarchar(max)字段似乎很重要.我似乎很重要的是我用一个guid加入bigtable(我猜它想要使用哈希匹配).

CREATE TABLE tinytable
  (
     id        INT PRIMARY KEY IDENTITY(1,1),foreignId INT NOT NULL
  )

CREATE TABLE smalltable
  (
     id     INT PRIMARY KEY IDENTITY(1,GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),tinyId INT NOT NULL,Magic  NVARCHAR(max) NOT NULL DEFAULT ''
  )

CREATE TABLE bigtable
  (
     id          INT PRIMARY KEY IDENTITY(1,CreatedUtc  DATETIME NOT NULL DEFAULT GETUTCDATE(),smallGuidId UNIQUEIDENTIFIER NOT NULL
  )

INSERT tinytable
       (foreignId)
VALUES(7)

INSERT smalltable
       (tinyId)
VALUES(1)

-- make a million rows 
DECLARE @i INT;

SET @i=20;

INSERT bigtable
       (smallGuidId)
SELECT GuidId
FROM   smalltable;

WHILE @i > 0
  BEGIN
      INSERT bigtable
             (smallGuidId)
      SELECT smallGuidId
      FROM   bigtable;

      SET @i=@i - 1;
  END

我已经在SQL 2005,2008和2008R2上测试了相同的结果.

解决方法

DBCC SHOW_STATISTICS (tinytable,foreignId) WITH HISTOGRAM

SQL Server知道自捕获统计信息后事情可能已发生更改,因此在执行计划时可能会有一行值为3.此外,在计划编译和执行之间可能会经过任何时间(毕竟,计划被缓存以便重用).正如Martin所说,SQL Server包含的逻辑用于检测何时进行了足够的修改以证明为了最优原因而重新编译任何缓存的计划.

然而,这些最终都不重要.在一个边缘情况例外情况下,优化器永远不会将表操作产生的行数估计为零.如果它可以静态地确定输出必须始终为零行,则操作是冗余的并且将被完全删除.

优化器的模型改为估计最少一行.如果可能的估计值较低,采用这种启发式方法往往会产生更好的平均计划.从处理流中的那一点开始,在某个阶段产生零行估计的计划将是无用的,因为没有基础来进行基于成本的决策(零行是零行,无论如何).如果估计结果是错误的,那么在零行估计之上的计划形状几乎没有合理的机会.

第二个因素是另一个称为遏制假设的建模假设.这基本上表示如果查询将一系列值与另一个值范围连接起来,那是因为范围重叠.另一种说法就是说要指定连接,因为预计会返回行.如果没有这种推理,通常会低估成本,导致对广泛的常见查询的计划不佳.

从本质上讲,您所拥有的是一个不适合优化器模型的查询.我们无法通过多列或过滤索引来“改进”估算;这里没有办法估计低于1行.一个真实的数据库可能有外键来确保不会出现这种情况,但假设这里不适用,我们将使用提示来纠正模型外的情况.任何数量的不同提示方法都适用于此查询. OPTION(FORCE ORDER)恰好与编写的查询一起工作.

（编辑：上饶站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!