当前位置:Gxlcms > mysql > TOP1比不加TOP慢的疑惑

TOP1比不加TOP慢的疑惑

时间:2021-07-01 10:21:17 帮助过:46人阅读

问题描述: 有一个查询如下,去掉 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 的时候,很快就出来结果了,但加上 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 操作以便单个线程就能使多个磁盘驱动器繁忙工作。

border: 1pt solid #dedfef; padding: 0cm

人气教程排行