sql - शीर्ष क्यों चुनें...अनुक्रमित स्तंभ द्वारा क्रम अभी भी क्रमबद्ध है?




sql-server tsql indexing sql-server-2014 (2)

मैंने चयनित शीर्ष कथन को कवर करने के लिए निम्न सूचकांक बनाया है।

-- Column A, B have type of int
create unique index ix_ on T (A, B) with (data_compression = page) 
-- tried to create non-unique index too and the execution plan is the same

select top 20 A, B from T order by A, B -- 19 seconds
select top 20 A, B from T -- return result instantly

हालांकि, यह अभी भी कुछ समय लेता है (मेरे टेबल पर 1 9 सेकंड जो 50 मिलियन पंक्तियाँ हैं) और निष्पादन योजना से पता चलता है कि अभी भी एक "सॉर्ट" है?

निष्पादन योजना से पता चलता है

← मूल्य (मूल्य: 0%) ← शीर्ष (लागत: 0%) ← समानता (इकट्ठा होने वाली धारा) (लागत: 0%) ← सॉर्ट करें (टॉप एन सॉर्ट) लागत: 93% ← इंडेक्स स्कैन (नॉनक्लास्टर्ड) [टी.िक्स_] लागत : 7%

निष्पादन योजना

<?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.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="552.009" StatementText="select  top 20 A A, B B --, checksum(*) cs&#xA;from T with (index(ix_))&#xA;order by A, B" StatementType="SELECT" QueryHash="0x1531573504856080" QueryPlanHash="0x5D4FED760C34AF43" 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="8" MemoryGrant="1024" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="256">
            <ThreadStat Branches="1" UsedThreads="8">
              <ThreadReservation NodeId="0" ReservedThreads="8" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="896" DesiredMemory="960" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="896" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="768000" EstimatedPagesCached="768000" EstimatedAvailableDegreeOfParallelism="8" />
            <RelOp AvgRowSize="15" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="552.009">
              <OutputList>
                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="(20)">
                    <Const ConstValue="(20)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="15" EstimateCPU="0.0286101" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="552.009">
                  <OutputList>
                    <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                    <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                      </OrderByColumn>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="15" EstimateCPU="212.739" EstimateIO="303.269" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="551.98">
                      <OutputList>
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                        <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="20" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <TopSort Distinct="false" Rows="20">
                        <OrderBy>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                          </OrderByColumn>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                          </OrderByColumn>
                        </OrderBy>
                        <RelOp AvgRowSize="15" EstimateCPU="5.81245" EstimateIO="30.16" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="42226500" LogicalOp="Index Scan" NodeId="3" Parallel="true" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="35.9724" TableCardinality="42226500">
                          <OutputList>
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                            <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="8" ActualRows="3993270" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="7" ActualRows="2713924" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="6" ActualRows="8866373" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="5" ActualRows="10625143" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="4" ActualRows="4254726" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="3" ActualRows="3195887" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="3626671" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="4950538" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <RunTimePartitionSummary>
                            <PartitionsAccessed PartitionCount="41">
                              <PartitionRange Start="1" End="41" />
                            </PartitionsAccessed>
                          </RunTimePartitionSummary>
                          <IndexScan Ordered="false" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[DB]" Schema="[dbo]" Table="[T]" Index="[ix_]" Alias="[T]" IndexKind="NonClustered" Storage="RowStore" />
                          </IndexScan>
                        </RelOp>
                      </TopSort>
                    </RelOp>
                  </Parallelism>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answers

आपकी तालिका बी द्वारा विभाजित है

सूचकांक इस विभाजन योजना को तब तक विरासत में लेता है जब तक आप अन्यथा निर्दिष्ट नहीं करते। साथ उदाहरण के लिए

create unique index ix_ on T (A, B)  with (data_compression = page) on [primary] 

(जो मामले में यह गैर-गठबंधन हो जाता है और मेटाडेटा जैसे कुछ कार्रवाइयां रोकती हैं)

सबसे कम "ए" मान किसी भी विभाजन में हो सकता है।

यह बहुत अच्छी तरह से अनुकूलित नहीं है आप संरेखित इंडेक्स को रख सकते हैं और कोड के आधार पर इस रीराइट का उपयोग कर सकते हैं

SELECT TOP 20 A, B 
FROM sys.partitions AS P
  CROSS APPLY ( SELECT TOP 20 A, B 
                FROM dbo.T
                WHERE $PARTITION.YourPartitionFunction(T.B) = P.partition_number 
                ORDER BY A,B
                ) AS A
WHERE P.object_id = OBJECT_ID('dbo.T')
AND P.index_id = INDEXPROPERTY( OBJECT_ID('dbo.T'), 'ix_', 'IndexID' )
ORDER BY  A,B

इसे 41 विभाजनों में से प्रत्येक के शीर्ष 20 पंक्तियां मिलेंगी (बिना किसी क्रम के), तो सिर्फ 820 पंक्तियों को ठीक करें, जो कि अंतिम शीर्ष 20 (पूरे 42 मिलियन के बजाय) पाने के लिए होता है।


यह चर्चा महत्वपूर्ण बिंदु पर अनुपलब्ध है: प्रश्न यह नहीं है कि "गैर-कुंजी-कॉलम" इंडेक्स -कॉलम या शामिल -कॉलम के रूप में शामिल करने के लिए बेहतर हैं।

सवाल यह है कि इंडेक्स में वास्तव में आवश्यक कॉलम को शामिल करने के लिए शामिल-तंत्र का उपयोग करना कितना महंगा है? (आमतौर पर जहां-खंडों का हिस्सा नहीं है, लेकिन अक्सर चयन में शामिल होता है)। तो आपकी दुविधा हमेशा होती है:

  1. आईडी 1, आईडी 2 ... आईडीएन अकेले या इंडेक्स पर इंडेक्स का प्रयोग करें
  2. आईडी 1, आईडी 2 पर इंडेक्स का उपयोग करें ... आईडीएन प्लस में col1, col2 ... colN शामिल हैं

कहां: id1, id2 ... idN कॉलम अक्सर कॉलम और col1, col2 में उपयोग किए जाते हैं ... coln कॉलम अक्सर चुने जाते हैं, लेकिन आमतौर पर प्रतिबंधों में उपयोग नहीं किया जाता है

(इंडेक्स-कुंजी के हिस्से के रूप में इन सभी कॉलम को शामिल करने का विकल्प हमेशा मूर्खतापूर्ण होता है (जब तक कि वे प्रतिबंधों में भी उपयोग नहीं किए जाते) - क्योंकि यह हमेशा बनाए रखना अधिक महंगा होगा क्योंकि इंडेक्स को अद्यतन और सॉर्ट किया जाना चाहिए जब भी "कुंजी" नहीं बदला है)।

तो विकल्प 1 या 2 का उपयोग करें?

उत्तर: यदि आपकी तालिका शायद ही कभी अपडेट की जाती है - अधिकतर इसमें से डाली / हटा दी जाती है - तो यह कुछ "हॉट कॉलम" को शामिल करने के लिए शामिल-तंत्र का उपयोग करने के लिए अपेक्षाकृत सस्ती है (जिसे अक्सर चयन में उपयोग किया जाता है - लेकिन अक्सर प्रतिबंधों पर उपयोग नहीं किया जाता है) आवेषण / हटाए जाने के लिए इंडेक्स को किसी भी तरह अद्यतन / सॉर्ट करने की आवश्यकता होती है और इस प्रकार थोड़ा अतिरिक्त ओवरहेड कुछ अतिरिक्त कॉलम को संग्रहीत करने के साथ जुड़ा हुआ है जबकि इंडेक्स को पहले से अपडेट किया जा रहा है। ओवरहेड अतिरिक्त मेमोरी और सीपीयू इंडेक्स पर अनावश्यक जानकारी स्टोर करने के लिए प्रयोग किया जाता है।

यदि कॉलम जिन्हें आप शामिल करते हैं, कॉलम को अक्सर अपडेट किया जाता है (इंडेक्स- कुंजी -कॉलम अपडेट किए बिना) - या - यदि उनमें से बहुत से हैं कि इंडेक्स आपकी तालिका की एक प्रति के करीब हो जाता है - विकल्प 1 का उपयोग करें मैं सुझाव दूंगा! इसके अलावा यदि कुछ शामिल-कॉलम जोड़ना कोई प्रदर्शन-अंतर नहीं करता है - तो आप उन्हें जोड़ने का विचार छोड़ना चाहेंगे :) सत्यापित करें कि वे उपयोगी हैं!

कुंजी (आईडी 1, आईडी 2 ... आईडीएन) में समान मूल्यों की पंक्तियों की औसत संख्या कुछ महत्व भी हो सकती है।

ध्यान दें कि यदि एक कॉलम - जिसे इंडेक्स के शामिल- कॉलम के रूप में जोड़ा जाता है - प्रतिबंध में उपयोग किया जाता है: जब तक इंडेक्स का उपयोग किया जा सकता है ( इंडेक्स- कुंजी- कॉलम के खिलाफ प्रतिबंध के आधार पर) - तब SQL सर्वर मेल खाता है तालिका के चारों ओर महंगी तरीके से जाने के बजाय सूचकांक (पत्ती-नोड-मूल्य) के खिलाफ कॉलम-प्रतिबंध।





sql sql-server tsql indexing sql-server-2014