sql-server – 索引不会使执行更快,并且在某些情况下会降低查询

创建测试表并用数据填充的查询是：

CREATE TABLE [dbo].[IndexTestTable](
    [id] [int] IDENTITY(1,1) PRIMARY KEY,[Name] [nvarchar](20) NULL,[val1] [bigint] NULL,[val2] [bigint] NULL)

DECLARE @counter INT;
SET @counter = 1;

WHILE @counter < 500000
BEGIN
    INSERT INTO IndexTestTable
      (
        -- id -- this column value is auto-generated
        NAME,val1,val2
      )
    VALUES
      (
        'Name' + CAST((@counter % 100) AS NVARCHAR),RAND() * 10000,RAND() * 20000
      );

    SET @counter = @counter + 1;
END

-- Index in question
CREATE NONCLUSTERED INDEX [IndexA] ON [dbo].[IndexTestTable]
(
    [Name] ASC
)
INCLUDE (   [id],[val1],[val2])

现在查询1,这是改进的(只是略有但改进是一致的)是：

SELECT *
FROM   IndexTestTable I1
       JOIN IndexTestTable I2
            ON  I1.ID = I2.ID
WHERE  I1.Name = 'Name1'

没有索引的统计信息和执行计划(在这种情况下,表使用默认的聚簇索引)：

(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2,logical reads 5580,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 109 ms,elapsed time = 294 ms.

现在启用了索引：

(5000 row(s) affected)
Table 'IndexTestTable'. Scan count 2,logical reads 2819,lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 94 ms,elapsed time = 231 ms.

现在查询因索引而变慢(查询没有意义,因为它只是为了测试而创建)：

SELECT I1.Name,SUM(I1.val1),SUM(I1.val2),MIN(I2.Name),SUM(I2.val1),SUM(I2.val2)
FROM   IndexTestTable I1
       JOIN IndexTestTable I2
            ON  I1.Name = I2.Name
WHERE   
       I2.Name = 'Name1'
GROUP BY
       I1.Name

启用聚簇索引：

(1 row(s) affected)
Table 'IndexTestTable'. Scan count 4,logical reads 60,lob read-ahead reads 0.
Table 'Worktable'. Scan count 0,logical reads 0,lob read-ahead reads 0.
Table 'Worktable'. Scan count 1,logical reads 155106,lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 17207 ms,elapsed time = 17337 ms.

现在禁用索引：

(1 row(s) affected)
Table 'IndexTestTable'. Scan count 5,logical reads 8642,lob read-ahead reads 0.
Table 'Worktable'. Scan count 2,logical reads 165212,lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 17691 ms,elapsed time = 9073 ms.

问题是：

>即使SQL Server建议索引,为什么呢
缓慢的事情显着差异？
>什么是嵌套循环连接,它占用了大部分时间
如何改善执行时间？
>有什么我做错了或错过了吗？
>使用默认索引(仅在主键上)为什么需要更少
时间,并且存在非聚集索引,对于每一行
连接表,应该更快地找到连接的表行,因为
join位于已创建索引的Name列上.这是
反映在查询执行计划和Index Seek成本较低
当IndexA处于活动状态时,为什么还要慢？还有什么
嵌套循环左外连接导致减速？

使用SQL Server 2012

解决方法

Even though the index is suggested by the SQL Server,why does it slow things down by a significant difference?

索引建议由查询优化器完成.如果遇到来自现有索引不能很好地服务的表的逻辑选择,它可能会向其输出添加“缺失索引”建议.这些建议是机会主义的;它们不是基于对查询的完整分析,也没有考虑更广泛的考虑因素.充其量,它们表明可能有更多有用的索引,熟练的DBA应该看一看.

关于缺少索引建议的另一个问题是它们基于优化器的成本计算模型,优化器估计建议的索引可能会减少查询的估计成本.这里的关键词是“模型”和“估计”.查询优化器对您的硬件配置或其他系统配置选项知之甚少 – 其模型主要基于固定数字,这些固定数字恰好可以在大多数系统上为大多数人生成合理的计划结果.除了使用的确切成本数字的问题,结果总是估计 – 并且估计可能是错误的.

What is the Nested Loop join which is taking most of the time and how to improve its execution time?

要提高交叉连接操作本身的性能,还有很多工作要做;嵌套循环是交叉连接可能的唯一物理实现.连接内侧的工作台线轴是一种优化,以避免重新扫描每个外部行的内侧.这是否是一个有用的性能优化取决于各种因素,但在我的测试中,如果没有它,查询会更好.同样,这是使用成本模型的结果 – 我的CPU和内存系统可能具有与您不同的性能特征.没有特定的查询提示来避免表假脱机,但是有一个未记录的跟踪标志(8690),您可以使用它来测试使用和不使用假脱机的执行性能.如果这是一个真正的生产系统问题,可以使用基于启用TF 8690生成的计划的计划指南强制执行没有阀芯的计划.建议不要在生产中使用未记录的跟踪标志,因为安装在技术上不受支持,并且跟踪标志可能会产生不良副作用.

Is there something that I am doing wrong or have missed?

您遗漏的主要问题是,尽管使用非聚集索引的计划根据优化程序的模型具有较低的估计成本,但它具有显着的执行时间问题.如果使用聚簇索引查看计划中线程的行分布,您可能会看到相当好的分布：

在使用Nonclustered Index Seek的计划中,工作最终完全由一个线程执行：

这是通过并行扫描/查找操作在线程之间分配工作的方式的结果.并行扫描并不总是能够比索引搜索更好地分配工作 – 但在这种情况下确实如此.更复杂的计划可能包括重新划分交换以跨线程重新分配工作.此计划没有此类交换,因此一旦将行分配给线程,所有相关工作都在同一个线程上执行.如果查看执行计划中其他运算符的工作分布,您将看到所有工作都由索引搜索所示的相同线程执行.

没有任何查询提示可以影响线程之间的行分配,重要的是要注意这种可能性并能够在执行计划中读取足够的细节以确定它何时导致问题.

With the default index (on primary key only) why does it take less time,and with the non clustered index present,for each row in the joining table,the joined table row should be found quicker,because join is on Name column on which the index has been created. This is reflected in the query execution plan and Index Seek cost is less when IndexA is active,but why still slower? Also what is in the Nested Loop left outer join that is causing the slowdown?

现在应该清楚的是,非聚集索引计划可能更有效,正如您所期望的那样;在执行时跨线程的工作分配很差,这会导致性能问题.

为了完成示例并说明我提到的一些事情,获得??更好的工作分配的一种方法是使用临时表来驱动并行执行：

SELECT
    val1,val2
INTO #Temp
FROM dbo.IndexTestTable AS ITT
WHERE Name = N'Name1';

SELECT 
    N'Name1',SUM(T.val1),SUM(T.val2),SUM(I2.val2)
FROM   #Temp AS T
CROSS JOIN IndexTestTable I2
WHERE
    I2.Name = 'Name1'
OPTION (FORCE ORDER,QUERYTRACEON 8690);

DROP TABLE #Temp;

这导致一个计划使用更有效的索引查找,没有表假脱机,并且很好地跨线程分配工作：

在我的系统上,此计划的执行速度明显快于聚集索引扫描版本.

如果您有兴趣了解有关并行查询执行内部的更多信息,您可能会喜欢watch my PASS Summit 2013 session recording.

（编辑：李大同）

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