TOP 1比不加TOP慢的疑惑

问题描述：

有一个查询如下，去掉TOP 1的时候，很快就出来结果了，但加上TOP 1的时候，一般要2~3秒才出数据，何解？

SELECT TOP 1

    A.INVNO

FROM A, B

WHERE A.Item = B.ItemNumber

    AND B.OwnerCompanyCode IS NOT NULL

 

问题原因分析：

在使用TOP 1的时候，SQL Server会尽力先找出这条TOP 1的记录，这就导致它采用了与不加TOP时不一致的扫描算法，SQL Server查询优化器始终认为，应该可以比较快的找到匹配的第1条记录，所以一般是使用嵌套循环的联接，则不加TOP 1时，SQL Server会根据结构和数据的统计信息决策出联接策略。嵌套循环一般适用于联系的两个表，一个表的数据较大，而另一个表的数据较小的情况，如果查询匹配的值出现在扫描的前端，则在取TOP 1的情况下，是符合嵌套循环联系的使用条件的，但当匹配的数据出现在扫描的后端，或者是基本上没有匹配的数据时，则嵌套循环要扫描完成两个大表，这显然是不适宜的，也正是因为这种情况，导致了TOP 1比不加TOP 1的效率慢很多

 

关于此问题的模拟环境：

USE tempdb

GO

 

SET NOCOUNT ON

--======================================

--创建测试环境

--======================================

RAISERROR('创建测试环境', 10, 1) WITH NOWAIT

-- Table A

CREATE TABLE [dbo].A(

    [TranNumber] [int] IDENTITY(1, 1) NOT NULL,

    [INVNO] [char](8) NOT NULL,

    [ITEM] [char](15) NULL DEFAULT (''),

    PRIMARY KEY([TranNumber])

)

 

CREATE INDEX [indexONinvno] ON [dbo].A([INVNO])

CREATE INDEX [indexOnitem] ON [dbo].A ([ITEM])

CREATE INDEX [indexONiteminnvo] ON [dbo].A([INVNO], [ITEM])

GO

 

-- Table B

CREATE TABLE [dbo].B(

    [ItemNumber] [char](15) NOT NULL DEFAULT (''),

    [CompanyCode] [char] (4) NOT NULL,

    [OwnerCompanyCode] [char](4) NULL,

    PRIMARY KEY([ItemNumber], [CompanyCode])

)

 

CREATE INDEX [ItemNumber] ON [dbo].B([ItemNumber])

CREATE INDEX [CompanyCode] ON [dbo].B([CompanyCode])

CREATE INDEX [OwnerCompanyCode] ON [dbo].B([OwnerCompanyCode])

GO

 

--======================================

--生成测试数据

--======================================

RAISERROR('生成测试数据', 10, 1) WITH NOWAIT

INSERT [dbo].A([INVNO], [ITEM])

SELECT LEFT(NEWID(), 8), RIGHT(NEWID(), 15)

FROM syscolumns A, syscolumns B

 

INSERT [dbo].B([ItemNumber], [CompanyCode], [OwnerCompanyCode])

SELECT RIGHT(NEWID(), 15), LEFT(NEWID(), 4), LEFT(NEWID(), 4)

FROM syscolumns A, syscolumns B

GO

 

速度测试脚本：

--======================================

--进行查询测试

--======================================

RAISERROR('进行查询测试', 10, 1) WITH NOWAIT

DECLARE @dt DATETIME, @id int, @loop int

DECLARE @ TABLE(

    id int IDENTITY,

    [TOP 1] int,

    [WITHOUT TOP] int)

 

SET @loop = 0

WHILE @loop < 10

BEGIN

    SET @loop = @loop + 1

    RAISERROR('test %d', 10, 1, @loop) WITH NOWAIT

    SET @dt = GETDATE()

        SELECT TOP 1

            A.INVNO

        FROM A, B

        WHERE A.Item = B.ItemNumber

            AND B.OwnerCompanyCode IS NOT NULL

    INSERT @([TOP 1]) VALUES(DATEDIFF(ms, @dt, GETDATE()))

    SELECT @id = SCOPE_IDENTITY(), @dt = GETDATE()

        SELECT --TOP 1

            A.INVNO

        FROM A, B

        WHERE A.Item = B.ItemNumber

            AND B.OwnerCompanyCode IS NOT NULL

    UPDATE @ SET [WITHOUT TOP] = DATEDIFF(ms, @dt, GETDATE())

    WHERE id = @id

END

SELECT * FROM @

UNION ALL

SELECT NULL, SUM([TOP 1]), SUM([WITHOUT TOP]) FROM @

GO

 

测试数据的变更脚本：

DECLARE @value char(15), @value1 char(15)

SELECT

    @value = LEFT(NEWID(), 15),

    @value1 = LEFT(NEWID(), 15)

 

UPDATE A

SET Item = @value

FROM A

    INNER JOIN(

        SELECT TOP 1

            [TranNumber]

        FROM(

            SELECT TOP 20 PERCENT

                [TranNumber]

            FROM A

            ORDER BY [TranNumber]

        )AA

        ORDER BY [TranNumber] DESC

    )B

        ON A.[TranNumber] = B.[TranNumber]

 

UPDATE B

SET ItemNumber = @value

FROM B

    INNER JOIN(

        SELECT TOP 1

            [ItemNumber], [CompanyCode]

        FROM(

            SELECT TOP 20 PERCENT

                [ItemNumber], [CompanyCode]

            FROM B

            ORDER BY [ItemNumber], [CompanyCode]

        )BB

        ORDER BY [ItemNumber] DESC, [CompanyCode] DESC

    )B1

        ON B.[ItemNumber] = B1.[ItemNumber]

            AND B.[CompanyCode] = B1.[CompanyCode]

GO

 

测试说明：

1．   在刚建立好测试环境的时候，是没有任何匹配项的，这时候，TOP 1会扫描两个表的所有数据，运行“速度测试脚本”可以看到此时有无TOP 1的效率差异：TOP 1明显比不加TOP 慢

2．   修改“测试数据的变更脚本”中，红色的20，让匹配的数据出现在扫描的顶端、中间和尾端，分别使用“速度测试脚本”测试，可以看到，匹配的值靠近扫描的前端的时候，TOP 1比不加TOP 快，随着匹配数据很后端的推移，这种效率差异会越来越小，到后面就变成TOP 1比不加TOP 1慢。

注意：每次变更数据，并且完成“速度测试脚本”测试后，需要修改“测试数据的变更脚本”中，红色的@value为@value1，让刚才设置匹配的数据再变回为不匹配

 

附：联接的几种方式

1．     嵌套循环联接

嵌套循环联接也称为“嵌套迭代”，它将一个联接输入用作外部输入表（显示为图形执行计划中的顶端输入），将另一个联接输入用作内部（底端）输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行，在内部输入表中搜索匹配行。

最简单的情况是，搜索时扫描整个表或索引；这称为“单纯嵌套循环联接”。如果搜索时使用索引，则称为“索引嵌套循环联接”。如果将索引生成为查询计划的一部分（并在查询完成后立即将索引破坏），则称为“临时索引嵌套循环联接”。查询优化器考虑了所有这些不同情况。

如果外部输入较小而内部输入较大且预先创建了索引，则嵌套循环联接尤其有效。在许多小事务中（如那些只影响较小的一组行的事务），索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中，嵌套循环联接通常不是最佳选择。

 

2．     合并联接

合并联接要求两个输入都在合并列上排序，而合并列由联接谓词的等效 (ON) 子句定义。通常，查询优化器扫描索引（如果在适当的一组列上存在索引），或在合并联接的下面放一个排序运算符。在极少数情况下，虽然可能有多个等效子句，但只用其中一些可用的等效子句获得合并列。

由于每个输入都已排序，因此 Merge Join 运算符将从每个输入获取一行并将其进行比较。例如，对于内联接操作，如果行相等则返回。如果行不相等，则废弃值较小的行并从该输入获得另一行。这一过程将重复进行，直到处理完所有的行为止。

合并联接操作可以是常规操作，也可以是多对多操作。多对多合并联接使用临时表存储行。如果每个输入中有重复值，则在处理其中一个输入中的每个重复项时，另一个输入必须重绕到重复项的开始位置。

如果存在驻留谓词，则所有满足合并谓词的行都将对该驻留谓词取值，而只返回那些满足该驻留谓词的行。

合并联接本身的速度很快，但如果需要排序操作，选择合并联接就会非常费时。然而，如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算法。

 

3．     哈希联接

哈希联接有两种输入：生成输入和探测输入。查询优化器指派这些角色，使两个输入中较小的那个作为生成输入。

哈希联接用于多种设置匹配操作：内部联接；左外部联接、右外部联接和完全外部联接；左半联接和右半联接；交集；联合和差异。此外，哈希联接的某种变形可以进行重复删除和分组，例如 SUM(salary) GROUP BY department。这些修改对生成和探测角色只使用一个输入。

以下几节介绍了不同类型的哈希联接：内存中的哈希联接、Grace 哈希联接和递归哈希联接。

内存中的哈希联接

哈希联接先扫描或计算整个生成输入，然后在内存中生成哈希表。根据计算得出的哈希键的哈希值，将每行插入哈希存储桶。如果整个生成输入小于可用内存，则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算，并为每个探测行计算哈希键的值，扫描相应的哈希存储桶并生成匹配项。

Grace 哈希联接

如果生成输入大于内存，哈希联接将分为几步进行。这称为“Grace 哈希联接”。每一步都分为生成阶段和探测阶段。首先，消耗整个生成和探测输入并将其分区（使用哈希键上的哈希函数）为多个文件。对哈希键使用哈希函数可以保证任意两个联接记录一定位于相同的文件对中。因此，联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希联接应用于每对分区文件。

递归哈希联接

如果生成输入非常大，以至于标准外部合并的输入需要多个合并级别，则需要多个分区步骤和多个分区级别。如果只有某些分区较大，则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快，将使用大的异步 I/O 操作以便单个线程就能使多个磁盘驱动器繁忙工作。

注意：

如果生成输入仅稍大于可用内存，则内存中的哈希联接和 Grace 哈希联接的元素将结合在一个步骤中，生成混合哈希联接。

在优化过程中不能始终确定使用哪种哈希联接。因此，SQL Server 开始时使用内存中的哈希联接，然后根据生成输入的大小逐渐转换到 Grace 哈希联接和递归哈希联接。

如果优化器错误地预计两个输入中哪个较小并由此确定哪个作为生成输入，生成角色和探测角色将动态反转。哈希联接确保使用较小的溢出文件作为生成输入。这一技术称为“角色反转”。至少一个文件溢出到磁盘后，哈希联接中才会发生角色反转。

注意：

角色反转的发生独立于任何查询提示或结构。角色反转不会显示在查询计划中；角色反转对于用户是透明的。