索引不能使执行速度更快，并且在某些情况下会降低查询速度。为什么会这样呢？

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

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, 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 = 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 楼

尽管SQL Server建议使用索引，但为什么索引速度却会显着降低呢？


查询优化器会提供索引建议。如果遇到从现有索引不能很好地服务的表中进行逻辑选择，则可能在其输出中添加“缺少索引”建议。这些建议是机会主义的。它们不是基于对查询的全面分析，也没有考虑更广泛的考虑。充其量它们表明可能有更有用的索引编制，熟练的DBA应该看一下。

关于缺少索引建议的另一件事是，它们基于优化程序的成本计算模型，然后优化器估算建议索引的数量可以减少查询的估算成本。这里的关键词是“模型”和“估计”。查询优化器对您的硬件配置或其他系统配置选项一无所知-它的模型主要基于固定数字，该数字在大多数情况下会为大多数系统上的大多数人提供合理的计划结果。除了使用的确切成本数字存在问题外，结果始终是估算值-估算值可能是错误的。


什么是嵌套循环联接？缩短执行时间？


几乎没有什么可以改善交叉联接操作本身的性能。嵌套循环是交叉联接的唯一可能的物理实现。联接内侧的表假脱机是一种优化，可避免为每个外部行重新扫描内侧。这是否是有用的性能优化取决于多种因素，但是在我的测试中，如果没有它，查询会更好。同样，这是使用成本模型的结果-我的CPU和内存系统可能具有与您不同的性能特征。没有避免表假脱机的特定查询提示，但是有未记录的跟踪标志（8690），可用于测试有无假脱机的执行性能。如果这是实际的生产系统问题，则可以基于启用了TF 8690的计划，使用计划指南强制执行不带线轴的计划。不建议在生产中使用未记录的跟踪标记，因为该安装在技术上不受支持，并且跟踪标记可能会产生不良的副作用。遗忘的主要内容是，尽管根据优化程序的模型，使用非聚集索引的计划的估计成本较低，但它存在很大的执行时间问题。如果使用“聚集索引”查看计划中线程之间的行分布，则可能会看到合理的分布情况：


Index Seek，工作最终完全由一个线程执行：



没有查询提示会影响在线程之间进行行分布时，重要的是要意识到这种可能性，并能够读取执行计划中的足够详细信息，以确定何时导致问题。


默认索引（仅在主键上）为什么要花更少的时间，并且在存在非聚集索引的情况下，对于联接表中的每一行，应该更快地找到联接表行，因为联接位于索引的“名称”列已经被创造了。这反映在查询执行计划中，并且当IndexA处于活动状态时，“索引查找”成本会降低，但是为什么还要慢一些？同样，嵌套循环左外部联接中导致减速的原因是什么？只是性能在执行时跨线程分配不佳而导致性能问题。

为了完成示例并举例说明我提到的一些事情，一种获得更好效果的方法工作分配是使用一个临时表来驱动并行执行：并跨线程很好地分配工作：



在我的系统上，此计划的执行速度比集群索引扫描版本快得多。录音。

#2 楼

这实际上不是索引的问题，它是一个写得不好的查询。
您只有100个唯一的名称值，这样每个名称的唯一计数为5000。因此，对于表1中的每一行，您将从表2中加入5000。您能说25020004行吗？

    DECLARE @Distincts INT
    SET @Distincts = (SELECT  TOP 1 COUNT(*) FROM IndexTestTable I1 WHERE I1.Name = 'Name1' GROUP BY I1.Name)
    SELECT I1.Name
    , @Distincts
    , SUM(I1.val1) * @Distincts
    , SUM(I1.val2) * @Distincts
    , MIN(I2.Name)
    , SUM(I2.val1)
    , SUM(I2.val2)
    FROM   IndexTestTable I1
    LEFT OUTER JOIN

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

和时间：

    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 8 ms.
    Table 'IndexTestTable'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 1 ms.

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

     SQL Server Execution Times:
       CPU time = 16 ms,  elapsed time = 10 ms.

不能怪SQL格式错误的查询的索引

评论

---

感谢您的回答，是的，查询可以改进，但是我的问题的逻辑是对于默认索引（仅在主键上），为什么要花更少的时间，并且对于存在的非聚集索引，每一行在联接表中，应更快地找到联接表行，这反映在查询执行计划中，并且当IndexA处于活动状态时，“索引查找”成本较低，但是为什么还要慢一些？嵌套循环左外部联接中导致减速的原因是什么？我已编辑问题以添加此评论，以使问题更清楚。

– SpeedBirdNine
13-10-30在15:39

---