sql-server - 为什么查询不使用我的索引？

Question

CREATE TABLE [LB].[Orders]
(
    [OrderID] [bigint] IDENTITY(1,1) NOT NULL,
    [OrderDate] [date] NOT NULL,
    [Status] [nvarchar](max) NULL,

    CONSTRAINT [PK_MasterOrderID] 
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
) 

CREATE NONCLUSTERED INDEX [PK_Index]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate]); 

CREATE UNIQUE NONCLUSTERED INDEX [IX_OrderID_OrderDate]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate],[OrderID]); 

我正在尝试加快此查询的速度:

SELECT * FROM [BTP_NYA].[XX].[Orders] WHERE [OrderDate] = '20170921' AND [OrderID] = 62192

为什么此查询不使用我的 IX_OrderID_OrderDate 索引？我还能怎样做得更快？

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4232.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0032831" StatementText="SELECT * FROM [BTP_NYA].[LB].[Orders] WHERE [OrderDate]=@1 AND [OrderID]=@2" StatementType="SELECT" QueryHash="0x547011333B745205" QueryPlanHash="0xD16E72141B86DC03" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="144">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="836385" EstimatedPagesCached="209096" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="12369" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="59123">
              <OutputList>
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Index="[PK_MasterOrderID]" IndexKind="Clustered" Storage="RowStore" />
                <SeekPredicates>
                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@2],0)">
                            <Convert DataType="bigint" Style="0" Implicit="true">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="@2" />
                                </Identifier>
                              </ScalarOperator>
                            </Convert>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
                <Predicate>
                  <ScalarOperator ScalarString="[BTP_NYA].[LB].[Orders].[OrderDate]=CONVERT_IMPLICIT(date,[@1],0)">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Convert DataType="date" Style="0" Implicit="true">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="@1" />
                            </Identifier>
                          </ScalarOperator>
                        </Convert>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@2" ParameterCompiledValue="(62192)" ParameterRuntimeValue="(62192)" />
              <ColumnReference Column="@1" ParameterCompiledValue="'20170921'" ParameterRuntimeValue="'20170921'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answer 1

不使用非聚集索引的原因是使用唯一主键聚集索引选择单行效率更高。您无法比这更快地选择单行的所有列(除非内存表上的哈希索引)。