代码之家 › 专栏 › 技术社区 › Dmitry B.

摆脱全索引扫描

query-optimization sql-server-2008 tsql sql-server

Dmitry B. · 技术社区 · 16 年前

由于p4filereleases中650万条记录的完全非聚集索引扫描以及哈希联接,以下查询的性能很差。我在寻找优化器选择扫描而不是搜索的可能原因。

SELECT p4f.FileReleaseID 
   FROM P4FileReleases p4f
   INNER JOIN AnalyzedFileView af 
      ON p4f.FileRelease = (af.path+'#'+cast(af.revision as varchar))  
   WHERE (af.tracked_change_id = 1)

据我所知,优化器没有理由选择p4filereleases扫描。WHERE子句将正确数据集的大小限制为大约1K条记录,优化器应该知道这一点(参见下面的柱状图)。

如果事实如此,如果我获取视图数据并将其放入一个堆表(与索引视图结构相同),那么查询将使用对较大表的索引查找和内部联接循环(而不是哈希联接)来执行(总成本从145降到1左右)。

有什么想法可以把优化器扔掉吗?

细节。 SQL Server 2008(版本10.0.2757.0)。

p4filereleases表 拥有650万条记录

CREATE TABLE [dbo].[P4FileReleases](
    [FileReleaseID] [int] IDENTITY(1,1) NOT NULL,
    [FileRelease] [varchar](254) NOT NULL,
    -- 5 more fields 
 CONSTRAINT [CIX_P4FileReleases_FileReleaseID_PK] PRIMARY KEY CLUSTERED 
(
    [FileReleaseID] ASC
),
CONSTRAINT [NCIX_P4FileReleases_FileRelease] UNIQUE NONCLUSTERED 
(
    [FileRelease] ASC
)

分析文件视图 是一个启用了统计信息并且是最新的索引视图。

它有四列:

   key int (int, PK) - clustered index
   tracked_change_id (int, FK) - non-unique, non-clustered index (covering 'path', 'revision')
   path (nvarchar(1024), null) 
   revision (smallint, null)

跟踪的_-change_-id柱状图:

1   0   1222    0   1
4   0   787     0   1
8   0   2754    0   1
12  0   254     0   1
13  0   34      0   1

查询计划

  |--Parallelism(Gather Streams)
       |--Hash Match(Inner Join, HASH:([Expr1011])=([Expr1010]), RESIDUAL:([Expr1010]=[Expr1011]))
            |--Bitmap(HASH:([Expr1011]), DEFINE:([Bitmap1015]))
            |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1011]))
            |         |--Compute Scalar(DEFINE:([Expr1011]=([qpsitools].[dbo].[analyzed_file_view].[path]+N'#')+CONVERT_IMPLICIT(nvarchar(30),CONVERT(varchar(30),[qpsitools].[dbo].[analyzed_file_view].[revision],0),0)))
            |              |--Index Seek(OBJECT:([qpsitools].[dbo].[analyzed_file_view].[tracked_change_id]), SEEK:([qpsitools].[dbo].[analyzed_file_view].[tracked_change_id]=(1)) ORDERED FORWARD)
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1010]), WHERE:(PROBE([Bitmap1015],[Expr1010])))
                 |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(nvarchar(254),[Blueprint].[dbo].[P4FileReleases].[FileRelease] as [p4f].[FileRelease],0)))
                      |--Index Scan(OBJECT:([Blueprint].[dbo].[P4FileReleases].[NCIX_P4FileReleases_FileRelease] AS [p4f]))

4 回复 | 直到 16 年前

Philip Kelley 16 年前

您正在将varchar列p4f.filerelease与nvarchar列(af.path)联接。由于数据类型不匹配,SQL必须将一个类型转换为另一个类型(当然,它不能从nvarchar转换为varchar)。在将af.path转换为nvarchar时,它失去了使用索引查找/筛选这些值的能力,因此需要扫描和转换所有可能的行。

最好的解决方案是将数据存储为匹配的数据类型(将p4f.filerelase列更改为nvarchar,或将af.path更改为varchar)。由于没有人能够修改现有的数据库结构,因此解决方法可能是在查询中显式地将af.path强制转换为varchar。测试一下看…当然,如果数据确实需要双字节格式,就不能这样做。

SQLMenace 16 年前

您的问题不是在何处,而是在联接中,您将得到一个隐式转换和对联接的扫描,在何处条件下您将得到一个查找

ON p4f.FileRelease = (af.path+'#'+cast(af.revision as varchar))

并行性也可能是一个问题,请尝试添加maxdop=1

你的统计数据是最新的吗?是否存在过多碎片?

ThatSteveGuy 16 年前

尝试将“af.tracked_change_id=1”移到join子句中。

INNER JOIN AnalyzedFileView af 
ON p4f.FileRelease = (af.path+'#'+cast(af.revision as varchar))
AND af.tracked_change_id = 1

内部连接后的应用位置

Dmitry B. 16 年前

菲利普·凯利发现了这个问题。p4filereleases中的varchar与analyzedfileview中的nvarchar数据类型不匹配。