sql-server – 为什么我的EXISTS查询执行索引扫描而不是索引查找？

我正在努力优化一些查询.

对于下面的查询,

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,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);

我创建了以下FILTERED索引：

-- 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);

此索引特别不用于此查询,还有其他查询使用此相同的索引,因此包含INCLUDED列.

特别是对于这个查询,我只想检查(EXISTS)订单是否有任何项目,其中decCatItemPrice> 0.

SQL Server正在进行索引扫描,如下图所示.

>统计数据刚刚更新.
>项表在测试中有41,208行.

请注意,我没有从items表中选择任何列.

这个项目表有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,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,[decOverrideDiscount]         DECIMAL(18,[decFinalPrice]               DECIMAL(18,[decTaxAmount]                DECIMAL(18,[strBCCurrencyCode]           VARCHAR(3)                       NOT NULL,[decBCCostPrice]              DECIMAL(18,[decBCCatItemPrice]           DECIMAL(18,[decBCOfferDiscount]          DECIMAL(18,[decBCOverrideDiscount]       DECIMAL(18,[decBCFinalPrice]             DECIMAL(18,[decBCTaxAmount]              DECIMAL(18,[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,[decShipFullPrice]            DECIMAL(18,[decShipOfferDisc]            DECIMAL(18,[decShipOverride]             DECIMAL(18,[decShipFinal]                DECIMAL(18,[decShipTax]                  DECIMAL(18,[decBCShipFullPrice]          DECIMAL(18,[decBCShipOfferDisc]          DECIMAL(18,[decBCShipOverride]           DECIMAL(18,[decBCShipFinal]              DECIMAL(18,[decBCShipTax]                DECIMAL(18,[decTotalAmount]              DECIMAL(18,[decBCTotalAmount]            DECIMAL(18,[decWrittenTotalAmount]       DECIMAL(18,[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,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],[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],[strOrderKey])
   WITH FILLFACTOR = 100

CREATE NONCLUSTERED INDEX [IX_tblBOrder_strBxOrderNo_sdtmOrdCreated_incl] 
   ON [dbo].[tblBorder] ([strBxOrderNo] asc,[strOrderKey],[decBCShipFinal],[decBCShipTax])

结论

我在LIVE系统上应用了我的索引,并更新了我的存储过程以使用SMALLDATETIME,以便匹配数据库中涉及的列的数据类型.

之后,在查看查询计划时,我会看到下面的图片：

这正是我想要它的方式.

我认为在这种情况下查询优化器做了很好的工作,以获得两个环境上最好的查询计划,我很高兴我没有添加任何查询提示.

我通过发布的3个答案了解到了.
感谢Max Vernon,Paul White和Daniel Hutmacher的回答.

解决方法

您的变量输入为datetime2：

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

但是这些被比较的列是typeld smalldatetime(正如sdtm前缀所示！)：

[sdtmOrdCreated] SMALLDATETIME NOT NULL

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!