Sql-Server

為什麼我的 EXISTS 查詢執行索引掃描而不是索引查找？

  • May 12, 2020

我正在優化一些查詢。

對於下面的查詢，

SET STATISTICS IO ON;
DECLARE @OrderStartDate DATETIME2 = '27 feb 2016';
DECLARE @OrderEndDate  DATETIME2 = '28 feb 2016';

SELECT  o.strBxOrderNo
       , o.sintOrderStatusID
       , o.sintOrderChannelID
       , o.sintOrderTypeID
       , o.sdtmOrdCreated
       , o.sintMarketID
       , o.strOrderKey
       , o.strOfferCode
       , o.strCurrencyCode
       , o.decBCShipFullPrice
       , o.decBCShipFinal
       , o.decBCShipTax
       , o.decBCTotalAmount
       , o.decWrittenTotalAmount
       , o.decBCWrittenTotalAmount
       , o.decBCShipOfferDisc
       , o.decBCShipOverride
       , o.decTotalAmount
       , o.decShipTax
       , o.decShipFinal
       , o.decShipOverride
       , o.decShipOfferDisc
       , o.decShipFullPrice
       , o.lngAccountParticipantID
       , CONVERT(DATE, o.sdtmOrdCreated, 120) as OrderCreatedDateConverted
FROM    tablebackups.dbo.tblBOrder o
WHERE   o.sdtmOrdCreated >= @OrderStartDate
       AND o.sdtmOrdCreated < @OrderEndDate
       AND EXISTS  (
           SELECT  *
           FROM    tablebackups.dbo.tblBOrderItem oi 
           WHERE   oi.strBxOrderNo = o.strBxOrderNo
           AND     oi.decCatItemPrice > 0
       )
OPTION (RECOMPILE);

我創建了以下過濾索引：

-- table dbo.tblBorderItem
CREATE NONCLUSTERED INDEX IX_tblBOrderItem_decCatItemPrice_INCL 
ON dbo.tblBorderItem 
( 
    strBxOrderNo ASC
   , sintOrderSeqNo ASC
   , decCatItemPrice   
)   
INCLUDE 
(
   blnChargeShipping
   , decBCCatItemPrice
   , decBCCostPrice
   , decBCFinalPrice
   , decBCOfferDiscount
   , decBCOverrideDiscount
   , decBCTaxAmount
   , decCostPrice
   , decFinalPrice
   , decOfferDiscount
   , decOverrideDiscount
   , decTaxAmount
   , decWasPrice
   , dtmOrdItemCreated
   , sintOrderItemStatusId
   , sintOrderItemType
   , sintQuantity
   , strItemNo
)  
WHERE decCatItemPrice > 0 
WITH (DROP_EXISTING = ON, FILLFACTOR = 95);

此索引不僅特別用於此查詢，還有其他查詢使用相同的索引，因此包含列。

特別是對於這個查詢，我只想檢查 (EXISTS) 訂單是否有任何商品 where decCatItemPrice > 0.

SQL Server 正在執行索引掃描，如下圖所示。

  • 統計數據剛剛更新。
  • 項目表在測試中有 41,208 行。

請注意，我沒有從項目表中選擇任何列。

該項目表有 164,309,397 條正在使用中。我想避免在那裡進行掃描。

問題：

為什麼 SQL Server 不進行索引查找？

為了改進此查詢，我還應該考慮其他因素/事情嗎？

(4537 row(s) affected) Table 'tblBorder'. Scan count 1, logical reads
116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0. Table 'tblBorderItem'. Scan
count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

在此處輸入圖像描述 在此處輸入圖像描述

這是表tblBorderItem的定義和索引

   IF OBJECT_ID('[dbo].[tblBorderItem]') IS NOT NULL 
   DROP TABLE [dbo].[tblBorderItem] 
   GO
   CREATE TABLE [dbo].[tblBorderItem] ( 
   [strBxOrderNo]                VARCHAR(20)                      NOT NULL,
   [sintOrderSeqNo]              SMALLINT                         NOT NULL,
   [sintOrderItemStatusId]       SMALLINT                         NOT NULL,
   [sintNameStructureID]         SMALLINT                         NOT NULL,
   [strItemNo]                   VARCHAR(20)                      NOT NULL,
   [sintQuantity]                SMALLINT                         NOT NULL,
   [strCurrencyCode]             VARCHAR(3)                       NOT NULL,
   [decCostPrice]                DECIMAL(18,4)                    NOT NULL,
   [decCatItemPrice]             DECIMAL(18,2)                    NOT NULL,
   [decOfferDiscount]            DECIMAL(18,2)                    NOT NULL,
   [decOverrideDiscount]         DECIMAL(18,2)                    NOT NULL,
   [decFinalPrice]               DECIMAL(18,2)                    NOT NULL,
   [decTaxAmount]                DECIMAL(18,2)                    NOT NULL,
   [strBCCurrencyCode]           VARCHAR(3)                       NOT NULL,
   [decBCCostPrice]              DECIMAL(18,4)                    NOT NULL,
   [decBCCatItemPrice]           DECIMAL(18,4)                    NOT NULL,
   [decBCOfferDiscount]          DECIMAL(18,4)                    NOT NULL,
   [decBCOverrideDiscount]       DECIMAL(18,4)                    NOT NULL,
   [decBCFinalPrice]             DECIMAL(18,4)                    NOT NULL,
   [decBCTaxAmount]              DECIMAL(18,4)                    NOT NULL,
   [dtmOrdItemCreated]           DATETIME                         NOT NULL,
   [blnChargeShipping]           BIT                              NOT NULL,
   [lngTimeOfOrderQtyOnHand]     INT                                  NULL,
   [sdtmTimeOfOrderDueDate]      SMALLDATETIME                        NULL,
   [lngProdSetSeqNo]             INT                                  NULL,
   [lngProdRelationId]           INT                                  NULL,
   [lngProdRelationMemberId]     INT                                  NULL,
   [decWasPrice]                 DECIMAL(18,2)                        NULL,
   [sintOrderItemType]           SMALLINT                             NULL,
   [tsRowVersion]                TIMESTAMP                            NULL,
   [sdtmOrderItemStatusUpdated]  SMALLDATETIME                        NULL,
   CONSTRAINT   [PK_tblBOrderItem]  
PRIMARY KEY CLUSTERED    
([strBxOrderNo] asc, [sintOrderSeqNo] asc) 
WITH FILLFACTOR = 100)

   GO

   CREATE NONCLUSTERED INDEX 
   [IX_tblBOrderItem__dtmOrdItemCreated] 
      ON [dbo].[tblBorderItem] ([dtmOrdItemCreated] asc)
      WITH FILLFACTOR = 100


   CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__sintOrderItemStatusId] 
      ON [dbo].[tblBorderItem] ([sintOrderItemStatusId] asc)
      INCLUDE ([sdtmOrderItemStatusUpdated], 
   [sintOrderSeqNo], [strBxOrderNo], [strItemNo])
      WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__
sintOrderItemStatusId_decFinalPrice_
sdtmOrderItemStatusUpdated_
include_strBxOrderNo] 
  ON [dbo].[tblBorderItem] 
([sintOrderItemStatusId] asc, 
[decFinalPrice] asc, 
[sdtmOrderItemStatusUpdated] asc)
  INCLUDE ([strBxOrderNo])
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strBxOrderNo] 
  ON [dbo].[tblBorderItem] 
([strBxOrderNo] asc)
  WITH FILLFACTOR = 100


CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strItemNo] 
  ON [dbo].[tblBorderItem] ([strItemNo] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrderItem_decCatItemPrice_INCL] 
  ON [dbo].[tblBorderItem] 
([strBxOrderNo] asc, [sintOrderSeqNo] asc, [decCatItemPrice] asc)
  INCLUDE ([blnChargeShipping], 
[decBCCatItemPrice], [decBCCostPrice], [decBCFinalPrice], 
[decBCOfferDiscount], [decBCOverrideDiscount], 
[decBCTaxAmount], [decCostPrice], [decFinalPrice], 
[decOfferDiscount], [decOverrideDiscount], 
[decTaxAmount], [decWasPrice], [dtmOrdItemCreated], 
[sintOrderItemStatusId], [sintOrderItemType], 
[sintQuantity], [strItemNo])
  WHERE ([decCatItemPrice]>(0))
  WITH FILLFACTOR = 95

這是表tblBorder上的定義和索引

IF OBJECT_ID('[dbo].[tblBorder]') IS NOT NULL 
DROP TABLE [dbo].[tblBorder] 
GO
CREATE TABLE [dbo].[tblBorder] ( 
[strBxOrderNo]                VARCHAR(20)                      NOT NULL,
[uidOrderUniqueID]            UNIQUEIDENTIFIER                 NOT NULL,
[sintOrderStatusID]           SMALLINT                         NOT NULL,
[sintOrderChannelID]          SMALLINT                         NOT NULL,
[sintOrderTypeID]             SMALLINT                         NOT NULL,
[blnIsBasket]                 BIT                              NOT NULL,
[sdtmOrdCreated]              SMALLDATETIME                    NOT NULL,
[sintMarketID]                SMALLINT                         NOT NULL,
[strOrderKey]                 VARCHAR(20)                      NOT NULL,
[strOfferCode]                VARCHAR(20)                      NOT NULL,
[lngShippedToParticipantID]   INT                              NOT NULL,
[lngOrderedByParticipantID]   INT                              NOT NULL,
[lngShipToAddressID]          INT                              NOT NULL,
[lngAccountAddressID]         INT                              NOT NULL,
[lngAccountParticipantID]     INT                              NOT NULL,
[lngOrderedByAddressID]       INT                              NOT NULL,
[lngOrderTakenBy]             INT                              NOT NULL,
[strCurrencyCode]             VARCHAR(3)                       NOT NULL,
[decShipFullPrice]            DECIMAL(18,2)                    NOT NULL,
[decShipOfferDisc]            DECIMAL(18,2)                    NOT NULL,
[decShipOverride]             DECIMAL(18,2)                    NOT NULL,
[decShipFinal]                DECIMAL(18,2)                    NOT NULL,
[decShipTax]                  DECIMAL(18,2)                    NOT NULL,
[strBCCurrencyCode]           VARCHAR(3)                       NOT NULL,
[decBCShipFullPrice]          DECIMAL(18,4)                    NOT NULL,
[decBCShipOfferDisc]          DECIMAL(18,4)                    NOT NULL,
[decBCShipOverride]           DECIMAL(18,4)                    NOT NULL,
[decBCShipFinal]              DECIMAL(18,4)                    NOT NULL,
[decBCShipTax]                DECIMAL(18,4)                    NOT NULL,
[decTotalAmount]              DECIMAL(18,2)                    NOT NULL,
[decBCTotalAmount]            DECIMAL(18,4)                    NOT NULL,
[decWrittenTotalAmount]       DECIMAL(18,2)                        NULL,
[decBCWrittenTotalAmount]     DECIMAL(18,4)                        NULL,
[blnProRataShipping]          BIT                              NOT NULL,
[blnChargeWithFirstShipment]  BIT                              NOT NULL,
[sintShippingServiceLevelID]  SMALLINT                         NOT NULL,
[sintShippingMethodID]        SMALLINT                         NOT NULL,
[sdtmDoNotShipUntil]          SMALLDATETIME                        NULL,
[blnHoldUntilComplete]        BIT                              NOT NULL,
[tsRowVersion]                TIMESTAMP                            NULL,
CONSTRAINT   [PK_tblBOrder]  
PRIMARY KEY CLUSTERED    
([strBxOrderNo] asc) WITH FILLFACTOR = 100)

GO

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngAccountAddressID] 
  ON [dbo].[tblBorder] 
  ([lngAccountAddressID] asc, [sintOrderStatusID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngAccountParticipantID] 
  ON [dbo].[tblBorder] 
  ([lngAccountParticipantID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngOrderedByAddressID] 
  ON [dbo].[tblBorder] 
  ([lngOrderedByAddressID] asc, [sintOrderStatusID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngOrderedByParticipantID] 
  ON [dbo].[tblBorder] ([lngOrderedByParticipantID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngShippedToParticipantID] 
  ON [dbo].[tblBorder] 
  ([lngShippedToParticipantID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngShipToAddressID] 
  ON [dbo].[tblBorder] 
  ([lngShipToAddressID] asc, [sintOrderStatusID] asc)
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__sdtmOrdCreated_sintMarketID__include_strBxOrderNo] 
  ON [dbo].[tblBorder] 
  ([sdtmOrdCreated] asc, [sintMarketID] asc)
  INCLUDE ([strBxOrderNo])
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX 
[IX_tblBOrder_sdtmOrdCreated_INCL] 
  ON [dbo].[tblBorder] 
  ([sdtmOrdCreated] asc)
  INCLUDE ([decBCShipFinal], [decBCShipFullPrice], 
           [decBCShipOfferDisc], [decBCShipOverride], 
           [decBCShipTax], [decBCTotalAmount], [decBCWrittenTotalAmount], 
           [decShipFinal], [decShipFullPrice], [decShipOfferDisc], 
           [decShipOverride], [decShipTax], [decTotalAmount], 
           [decWrittenTotalAmount], [lngAccountParticipantID], 
           [lngOrderedByParticipantID], [sintMarketID], 
           [sintOrderChannelID], [sintOrderStatusID], 
           [sintOrderTypeID], [strBxOrderNo], [strCurrencyCode], 
           [strOfferCode], [strOrderKey])
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED 
INDEX [IX_tblBOrder_sintMarketID_sdtmOrdCreated] 
  ON [dbo].[tblBorder] 
  ([sintMarketID] asc, [sdtmOrdCreated] asc)
  INCLUDE ([sintOrderChannelID], [strBxOrderNo])
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED 
INDEX [IX_tblBOrder__sintOrderChannelID_sdtmOrdCreated_INCL] 
  ON [dbo].[tblBorder] 
  ([sintOrderChannelID] asc, [sdtmOrdCreated] asc)
  INCLUDE ([decBCShipFinal], [decBCShipFullPrice], 
  [decBCShipTax], [decShipFinal], [decShipFullPrice], 
  [decShipTax], [lngAccountParticipantID], [sintMarketID], 
  [sintOrderTypeID], [strBxOrderNo], 
  [strCurrencyCode], [strOrderKey])
  WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX [IX_tblBOrder_strBxOrderNo_sdtmOrdCreated_incl] 
  ON [dbo].[tblBorder] ([strBxOrderNo] asc, 
  [sdtmOrdCreated] asc)
  INCLUDE ([sintOrderChannelID], [sintOrderTypeID], [sintMarketID], 
  [strOrderKey], [lngAccountParticipantID], [strCurrencyCode], 
  [decShipFullPrice], [decShipFinal], [decShipTax], 
  [decBCShipFullPrice], [decBCShipFinal], 
  [decBCShipTax])

結論

我在 LIVE 系統上應用了我的索引，並更新了我的儲存過程以使用 SMALLDATETIME，以便為所涉及的列匹配數據庫中的數據類型。

之後，在查看查詢計劃時，我看到了下圖：

在此處輸入圖像描述

在此處輸入圖像描述

這正是我想要的樣子。

我認為這種情況下的查詢優化器在兩種環境中都獲得了最佳查詢計劃方面做得很好，我很高興我沒有添加任何查詢提示。

我通過發布的 3 個答案了解到。感謝Max Vernon、Paul White和Daniel Hutmacher的回答。

如果您想從查詢優化器中獲得好的結果，那麼注意數據類型是值得的。

您的變數鍵入為datetime2：

DECLARE @OrderStartDate datetime2 = '27 feb 2016';
DECLARE @OrderEndDate  datetime2 = '28 feb 2016';

但是這些與之比較的列是鍵入smalldatetime（正如sdtm前綴所暗示的那樣！）：

[sdtmOrdCreated] SMALLDATETIME NOT NULL

類型不兼容使得優化器很難通過類型轉換來計算結果基數估計，如執行計劃 xml 所示：

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@OrderStartDate],NULL,(22))">
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[@OrderEndDate],(10))">

目前的估計可能準確也可能不准確（可能不准確）。修復類型不兼容可能會也可能不會完全解決您的計劃選擇問題，但這是我在深入研究問題之前要解決的第一件事（簡單！）：

DECLARE @OrderStartDate smalldatetime = CONVERT(smalldatetime, '20160227', 112);
DECLARE @OrderEndDate smalldatetime = CONVERT(smalldatetime, '20160228', 112);

在決定重寫查詢或使用提示之前，請始終檢查基數估計的準確性以及任何差異的原因。

有關動態搜尋的更多詳細資訊，請參閱我的 SQLblog.com 文章“動態搜尋和隱藏的隱式轉換”。

**更新：**修復數據類型為您提供了您想要的搜尋計劃。之前的類型轉換導致的基數估計錯誤給了你較慢的計劃。

引用自：https://dba.stackexchange.com/questions/132689

相關問答

Sql-Server

如何通過單個表上不同數據類型的多個欄位來提高查詢的執行時間？

  • September 28, 2018
檢視問答
Sql-Server

即使存在覆蓋索引，也會對分區表進行聚集索引掃描

  • August 14, 2018
檢視問答
Sql-Server

強制流不同

  • August 2, 2017
檢視問答
Sql-Server

SQL Server 查詢中的索引使用率問題

  • July 21, 2017
檢視問答
Sql-Server

壞索引的定義是什麼？我們如何決定？我們應該有什麼邏輯？

  • August 2, 2016
檢視問答
Sql-Server

哪些 SET 選項會影響計劃重用以及如何在 T-SQL 中獲取它們的值？

  • April 17, 2016
檢視問答