主页 / dba / 问题 / 183006
Accepted
Serdia
Asked: 2017-08-09 15:03:47 +0800 CST

消除会降低性能的 Key Lookup (Clustered) 运算符

  • 772

如何在执行计划中消除 Key Lookup (Clustered) 运算符？

表tblQuotes已经有一个聚集索引（on QuoteID）和 27 个非聚集索引，所以我试图不再创建。

我把聚集索引列QuoteID放在我的查询中，希望它会有所帮助——但不幸的是还是一样。

执行计划在这里。

或查看：

在此处输入图像描述

这就是 Key Lookup 运算符所说的：

在此处输入图像描述

询问：

declare
        @EffDateFrom datetime ='2017-02-01',
        @EffDateTo   datetime ='2017-08-28'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data 
CREATE TABLE #Data
(
    QuoteID int NOT NULL,   --clustered index

    [EffectiveDate] [datetime] NULL, --not indexed
    [Submitted] [int] NULL,
    [Quoted] [int] NULL,
    [Bound] [int] NULL,
    [Exonerated] [int] NULL,
    [ProducerLocationId] [int] NULL,
    [ProducerName] [varchar](300) NULL,
    [BusinessType] [varchar](50) NULL,
    [DisplayStatus] [varchar](50) NULL,
    [Agent] [varchar] (50) NULL,
    [ProducerContactGuid] uniqueidentifier NULL
)
INSERT INTO #Data
    SELECT 
        tblQuotes.QuoteID,

          tblQuotes.EffectiveDate,
          CASE WHEN lstQuoteStatus.QuoteStatusID >= 1   THEN 1 ELSE 0 END AS Submitted,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted,
          CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted,
          tblQuotes.ProducerLocationID,
          P.Name + ' / '+ P.City as [ProducerName], 
        CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business' 
             WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite'
             END AS BusinessType,
        tblQuotes.DisplayStatus,
        tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent,
        tblProducerContacts.ProducerContactGUID
FROM    tblQuotes 
            INNER JOIN lstQuoteStatus 
                on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID
            INNER JOIN tblProducerLocations P 
                On P.ProducerLocationID=tblQuotes.ProducerLocationID
            INNER JOIN tblProducerContacts 
                ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID

WHERE   DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0
        AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety
        AND tblQuotes.OriginalQuoteGUID is null

select * from #Data

执行计划：

在此处输入图像描述

sql-server sql-server-2012

3 个回答

  1. Best Answer

    当查询处理器需要从未存储在用于定位查询返回结果所需的行的索引中的列中获取值时，会发生各种类型的键查找。

    以下面的代码为例，我们正在创建一个带有单个索引的表：

    USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

    我们将在表中插入 1,000,000 行，以便我们可以处理一些数据：

    INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

    现在，我们将使用选项查询数据以显示“实际”执行计划：

    SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

    查询计划显示：

    在此处输入图像描述

    查询查看IX_Table1索引以查找行，Table1ID = 5000000因为查看该索引比扫描整个表以查找该值要快得多。但是，为了满足查询结果，查询处理器还必须找到表中其他列的值；Table1ID这就是“RID 查找”的用武之地。它在表中查找与包含500000 值的行关联的行 ID（RID 查找中的 RID），并从Table1Data列中获取值。如果将鼠标悬停在计划中的“RID Lookup”节点上，您会看到：

    在此处输入图像描述

    “输出列表”包含 RID 查找返回的列。

    具有聚集索引和非聚集索引的表是一个有趣的例子。下表共有三列；ID 是聚集键，Dat由非聚集索引IX_Table和第三列索引Oth。

    USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    ID int NOT NULL IDENTITY(1,1) 
        PRIMARY KEY CLUSTERED
    , Dat nvarchar(30) NOT NULL
    , Oth nvarchar(3) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat);
GO

INSERT INTO dbo.Table1 (Dat, Oth)
SELECT TOP(1000000) CRYPT_GEN_RANDOM(30), CRYPT_GEN_RANDOM(3)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

    以这个示例查询为例：

    SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

    我们要求 SQL Server 从表中返回Dat包含单词的每一列Test。我们在这里有几个选择；我们可以查看表（即聚集索引）——但这需要扫描整个事物，因为表是按ID列排序的，这不会告诉我们列中包含哪些Test行Dat。另一个选项（也是 SQL Server 选择的选项）包括在IX_Table1非聚集索引中查找行 where Dat = 'Test'，但是由于我们也需要该Oth列，SQL Server 必须使用“键”对聚集索引执行查找查找”操作。这是为此的计划：

    在此处输入图像描述

    如果我们修改非聚集索引使其包含该Oth列：

    DROP INDEX IX_Table1
ON dbo.Table1;
GO

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat)
INCLUDE (Oth);        <---- This is the only change
GO

    然后重新运行查询：

    SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

    我们现在看到一个非聚集索引查找，因为 SQL Server 只需要定位索引Dat = 'Test'中的行IX_Table1，其中包括 的值Oth和ID列的值（主键），它自动出现在每个非聚集索引中。聚集索引。计划：

    在此处输入图像描述

    • 41

  2. 导致键查找是因为引擎选择使用不包含您尝试获取的所有列的索引。所以索引没有覆盖 select 和 where 语句中的列。

    要消除键查找，您需要包含缺少的列（键查找的输出列表中的列）= ProducerContactGuid、QuoteStatusID、PolicyTypeID 和 ProducerLocationID，或者另一种方法是强制查询使用聚集索引。

    请注意，表上的 27 个非聚集索引可能对性能不利。运行更新、插入或删除时，SQL Server 必须更新所有索引。这种额外的工作可能会对性能产生负面影响。

    • 7

  3. 您忘记提及此查询中涉及的数据量。另外，您为什么要插入临时表？如果只需要显示，则不要运行插入语句。

    出于此查询的目的，tblQuotes不需要 27 个非聚集索引。它需要 1 个聚集索引和 5 个非聚集索引，或者可能需要 6 个非聚集索引。

    此查询需要这些列上的索引：

    QuoteStatusID
ProducerLocationID
ProducerContactGuid
EffectiveDate
LineGUID
OriginalQuoteGUID

    我还注意到以下代码：

    DATEDIFF(D, @EffDateFrom, tblQuotes.EffectiveDate) >= 0 AND 
DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <= 0

    即NON Sargable它不能利用索引。

    要使该代码SARgable更改为：

    tblQuotes.EffectiveDate >= @EffDateFrom 
AND  tblQuotes.EffectiveDate <= @EffDateFrom

    要回答您的主要问题，“为什么要查找密钥”：

    您得到KEY Look up的原因是查询中提到的某些列不存在于覆盖索引中。

    您可以 google 并研究Covering Index或Include index。

    在我的示例中，假设 tblQuotes.QuoteStatusID 是非聚集索引，那么我也可以涵盖 DisplayStatus。因为你想要结果集中的 DisplayStatus。任何不存在于索引中但存在于结果集中的列都可以被覆盖以避免KEY Look Up or Bookmark lookup。这是一个覆盖索引的示例：

    create nonclustered index tblQuotes_QuoteStatusID 
on tblQuotes(QuoteStatusID)
include(DisplayStatus);

    **免责声明：**请记住，上面只是我的示例 DisplayStatus 可能在分析后被其他非 CI 覆盖。

    同样，您必须在查询中涉及的其他表上创建索引和覆盖索引。

    你Index SCAN也在你的计划中。

    这可能是因为表上没有索引，或者当有大量数据时，优化器可能决定扫描而不是执行索引查找。

    这也可能由于High cardinality. 由于连接错误，获得的行数超过了要求。这也可以纠正。

    • 4

相关问题