為什麼我沒有索引搜尋?
使用 SQL Server 2014 並嘗試優化查詢。其中一部分是進行表掃描。我把這個問題提煉成最簡單的形式。
表(第 3 方產品的數據庫)中有一個沒有索引和 PK 的欄位。我們經常在該欄位上搜尋 (
Form_FKey
)SELECT load_ID FROM [dbo].[tblLoads] WHERE [Form_FKey] = '87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'
執行計劃建議這樣做:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tblLoads] ([Form_FKey])
我創建了索引並且警告消失了,但我仍在進行表掃描。表中有 95K 條記錄,此查詢找到 860 行。
謂詞說:
[Scale_WH].[dbo].[tblLoads].[Form_FKey]={guid'87F13E42-B11D-413B-AEBC- A58E4CDB9D3E'}
我什至嘗試將字元串轉換為唯一標識符:
SELECT load_ID FROM [dbo].[tblLoads] WHERE [Form_FKey] = CAST('87F13E42-B11D-413B-AEBC-A58E4CDB9D3E' AS UNIQUEIDENTIFIER)
誰能解釋為什麼我仍然要進行表掃描?
loadid
是獨特的。實際上是表上的主鍵(但沒有定義主鍵。它是第 3 方供應商的數據庫,因此我們必須非常小心地修改架構)。我正在嘗試了解有關調整的更多資訊,這是一個範例。在這種情況下,假設它只是一個正常列。我的理解是它使用
WHERE
子句中的索引來辨識行,然後它會直接進入該行以獲取不在索引中的其他列,因此 select 中的列不會影響它是否進行搜尋。我可能是錯的。查詢計劃 XML
<?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.4449.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="2" StatementEstRows="860.314" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="1.70055" StatementText="SELECT [load_ID] FROM [dbo].[tblLoads] WHERE [Form_FKey]=@1" StatementType="SELECT" QueryHash="0x8334E910E51EB276" QueryPlanHash="0xA3BB83D7483E1FB" RetrievedFromCache="false"> <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="1" CachedPlanSize="16" CompileTime="3" CompileCPU="3" CompileMemory="248"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1153433" EstimatedPagesCached="720896" EstimatedAvailableDegreeOfParallelism="5" /> <RelOp AvgRowSize="39" EstimateCPU="0.105572" EstimateIO="1.59498" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="860.314" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="1.70055" TableCardinality="95832"> <OutputList> <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Load_ID" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="860" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Load_ID" /> </DefinedValue> </DefinedValues> <Object Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" IndexKind="Heap" Storage="RowStore" /> <Predicate> <ScalarOperator ScalarString="[Scale_WH].[dbo].[tblLoads].[Form_FKey]={guid'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'}"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Form_FKey" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="{guid'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'}" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </TableScan> </RelOp> <ParameterList> <ColumnReference Column="@1" ParameterCompiledValue="'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'" ParameterRuntimeValue="'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'" /> </ParameterList> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>
您的新索引沒有覆蓋查詢,因為它缺少語句中的
load_ID
列INCLUDE
(順便說一下,索引定義中也缺少該列)。重新創建包含附加列的索引,看看您是否獲得了預期的行為。我的理解是它使用
WHERE
子句中的索引來辨識行,然後它會直接進入該行以獲取不在索引中的其他列,因此 select 中的列不會影響它是否進行搜尋。我可能是錯的。它可以這樣做並在執行 Seek 操作後執行行查找,但這不能保證。Aaron 提到了臨界點,正如Kimberly Tripp在該
Why is the tipping point interesting?
部分中所解釋的那樣,“窄(非覆蓋)非聚集索引的使用比通常預期的要少(僅僅因為查詢在WHERE
子句中有列並不意味著 SQL Server 的將使用該索引)”
我添加
load_id
為一個包含的列,我得到了一個搜尋。