Sql-Server-2012

強制索引假離線

  • August 31, 2020

我知道出於性能原因應該避免它，但我試圖展示它作為展示出現的情況，以確保它不會出現。

然而，我最終得到一個缺少索引的警告，但優化器選擇不創建臨時索引。

我正在使用的查詢是

SELECT 
   z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
   EXISTS 
   (
       SELECT y.a 
       FROM dbo.t4 AS y
       WHERE y.a = z.a
   )
OPTION (MAXDOP 1);

表模式是：

CREATE TABLE dbo.t4
(
   a   integer NULL,
   b   varchar(1000) NULL,
   p   varchar(100) NULL
);

CREATE TABLE dbo.t5
(
   a   integer NULL,
   b   varchar(1000) NULL
);

CREATE UNIQUE CLUSTERED INDEX c1 
ON dbo.t5 (a);

兩個表都有 10,000 行，您可以使用以下方法進行模擬：

UPDATE STATISTICS dbo.t4 
WITH 
   ROWCOUNT = 10000, 
   PAGECOUNT = 1000;

UPDATE STATISTICS dbo.t5 
WITH 
   ROWCOUNT = 10000,
   PAGECOUNT = 1000;

查詢計劃為：

預設計劃

<?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="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <BatchSequence>
   <Batch>
     <Statements>
       <StmtSimple StatementCompId="1" StatementEstRows="5532.16" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.407384" StatementText="select a from t5  z WITH(INDEX(0))  where exists (select a from t4 where a=z.a )" StatementType="SELECT" QueryHash="0x1B882FCEA34AEAF4" QueryPlanHash="0x1B276DC04B718F7C" 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="1" MemoryGrant="2912" CachedPlanSize="32" CompileTime="10" CompileCPU="10" CompileMemory="296">
           <MissingIndexes>
             <MissingIndexGroup Impact="82.4536">
               <MissingIndex Database="[planoper]" Schema="[dbo]" Table="[t4]">
                 <ColumnGroup Usage="EQUALITY">
                   <Column Name="[a]" ColumnId="1" />
                 </ColumnGroup>
               </MissingIndex>
             </MissingIndexGroup>
           </MissingIndexes>
           <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="2912" RequiredMemory="1024" DesiredMemory="2912" RequestedMemory="2912" GrantWaitTime="0" GrantedMemory="2912" MaxUsedMemory="896" />
           <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104846" EstimatedPagesCached="11834" EstimatedAvailableDegreeOfParallelism="2" />
           <RelOp AvgRowSize="11" EstimateCPU="0.228447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5532.16" LogicalOp="Left Semi Join" NodeId="0" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.407384">
             <OutputList>
               <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
             </OutputList>
             <MemoryFractions Input="1" Output="1" />
             <RunTimeInformation>
               <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
             </RunTimeInformation>
             <Hash>
               <DefinedValues />
               <HashKeysBuild>
                 <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
               </HashKeysBuild>
               <HashKeysProbe>
                 <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
               </HashKeysProbe>
               <ProbeResidual>
                 <ScalarOperator ScalarString="[planoper].[dbo].[t4].[a]=[planoper].[dbo].[t5].[a] as [z].[a]">
                   <Compare CompareOp="EQ">
                     <ScalarOperator>
                       <Identifier>
                         <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                       </Identifier>
                     </ScalarOperator>
                     <ScalarOperator>
                       <Identifier>
                         <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                       </Identifier>
                     </ScalarOperator>
                   </Compare>
                 </ScalarOperator>
               </ProbeResidual>
               <RelOp AvgRowSize="11" EstimateCPU="0.0110785" EstimateIO="0.0565368" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0676153" TableCardinality="10000">
                 <OutputList>
                   <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                 </OutputList>
                 <RunTimeInformation>
                   <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                 </RunTimeInformation>
                 <IndexScan Ordered="false" ForcedIndex="true" ForceScan="false" NoExpandHint="false">
                   <DefinedValues>
                     <DefinedValue>
                       <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                     </DefinedValue>
                   </DefinedValues>
                   <Object Database="[planoper]" Schema="[dbo]" Table="[t5]" Index="[c1]" Alias="[z]" IndexKind="Clustered" />
                 </IndexScan>
               </RelOp>
               <RelOp AvgRowSize="11" EstimateCPU="0.011157" EstimateIO="0.100162" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.111319" TableCardinality="10000">
                 <OutputList>
                   <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                 </OutputList>
                 <RunTimeInformation>
                   <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                 </RunTimeInformation>
                 <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                   <DefinedValues>
                     <DefinedValue>
                       <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                     </DefinedValue>
                   </DefinedValues>
                   <Object Database="[planoper]" Schema="[dbo]" Table="[t4]" IndexKind="Heap" />
                 </TableScan>
               </RelOp>
             </Hash>
           </RelOp>
         </QueryPlan>
       </StmtSimple>
     </Statements>
   </Batch>
 </BatchSequence>
</ShowPlanXML>

它甚至告訴我創建這個索引：

USE [planoper];
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[t4] ([a]);

讓索引假離線自然出現的一種方法是使用稍微不同的語法來表達需求：

SELECT DISTINCT 
   z.a
FROM dbo.t5 AS z
JOIN dbo.t4 AS y ON
   y.a >= z.a AND y.a <= z.a
OPTION (LOOP JOIN, MAXDOP 1, FORCE ORDER);

這會產生一個執行計劃，例如：

加入不平等計劃

將等式重寫為一對等價的不等式鼓勵使用索引假離線，儘管假離線謂詞並不完全是您所追求的，但語義最終是相同的。

另一種方法是濫用TOP：

SELECT 
   z.a
FROM dbo.t5 AS z
WHERE 
   EXISTS 
   (
       SELECT TOP ((SELECT 100)) PERCENT y.a 
       FROM dbo.t4 AS y
       WHERE y.a = z.a
   );

百分百計劃

使用原始語法引入所需的索引假離線並不容易；但是，這並不是說不可能。由於您只需要它來進行展示，並且不會在生產系統附近的任何地方使用它，因此我將向您展示另一種方式：

SELECT 
   z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
   EXISTS 
   (
       SELECT y.a 
       FROM dbo.t4 AS y
       WHERE y.a = z.a
   )
OPTION (MAXDOP 1, LOOP JOIN, QUERYTRACEON 9114);

執行計劃是：

跟踪標誌計劃

索引假離線謂詞是所需的：

Seek Keys[1]: Prefix: [dbo].[t4].a = [dbo].[t5].[a] as [z].[a]

您將無法在USE PLAN提示中使用此計劃，因為優化器通常不會考慮它。

進一步閱讀：

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

相關問答

Sql-Server

同一條 SQL 語句的執行計劃不同

  • January 30, 2015
檢視問答
Sql-Server-2012

執行計劃與儲存過程不匹配

  • June 3, 2013
檢視問答
Sql-Server

INSERT INTO.. 使用預設關鍵字選擇

  • July 22, 2022
檢視問答
Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

更新語句非常慢，即使有索引也需要 11 分鐘才能更新 20 或 0 行

  • February 21, 2022
檢視問答
Sql-Server

使用儲存在另一個表中的單詞進行全文搜尋

  • January 4, 2022
檢視問答