Quanto è importante l’ordine delle colonne negli indici?

Ho sentito che dovresti mettere le colonne che saranno le più selettive all’inizio della dichiarazione dell’indice.

Per esempio:

CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) 

Prima di tutto, questa voce è corretta?

Se è così, probabilmente vedrò grandi differenze nelle prestazioni riorganizzando l’ordine delle colonne nel mio indice, o è più una pratica “carina da fare”?

La ragione per cui le sto chiedendo è che dopo aver inserito una query attraverso il DTA, ho raccomandato di creare un indice che contenesse quasi tutte le stesse colonne di un indice esistente, solo in un ordine diverso.

Stavo considerando di aggiungere le colonne mancanti all’indice esistente e chiamarlo buono. Pensieri?

Guarda un indice come questo:

 Cols 1 2 3 ------------- | | 1 | | | A |---| | | | 2 | | |---|---| | | | | | | | 1 | 9 | | B | | | | |---| | | | 2 | | | |---| | | | 3 | | |---|---| | 

Guarda come limitare per primo A, poiché la prima colonna elimina più risultati rispetto alla restrizione sulla prima colonna prima? È più facile se si immagina come deve essere attraversato l’indice, colonna 1, quindi colonna 2, ecc … si vede che la cancellazione della maggior parte dei risultati nel passaggio pugno rende il secondo passo molto più veloce.

Un altro caso, se hai interrogato sulla colonna 3, l’ottimizzatore non userebbe nemmeno l’indice, perché non è affatto utile nel restringere i set di risultati. Ogni volta che sei in una query, restringendo il numero di risultati da affrontare prima del passaggio successivo significa prestazioni migliori.

Poiché anche l’indice viene archiviato in questo modo, non c’è alcun backtracking nell’indice per trovare la prima colonna quando si esegue una query su di esso.

In breve: No, non è per lo spettacolo, ci sono dei veri benefici in termini di prestazioni.

L’ordine delle colonne è fondamentale. Ora quale ordine è corretto dipende da come lo si sta interrogando. Un indice può essere utilizzato per eseguire una ricerca esatta o una scansione di intervallo. Una ricerca esatta è quando vengono specificati i valori per tutte le colonne nell’indice e la query finisce esattamente sulla riga interessata. Per cercare l’ordine delle colonne è irrilevante. Una scansione di intervallo è quando vengono specificate solo alcune colonne e in questo caso quando l’ordine diventa importante. SQL Server può utilizzare un indice per un intervallo di scansione solo se viene specificata la colonna più a sinistra e solo se viene specificata la colonna più a sinistra successiva e così via. Se hai un indice su (A, B, C) può essere usato per scansionare per [email protected] , per [email protected] AND [email protected] ma non per [email protected] , per [email protected][email protected] AND [email protected] . Il caso [email protected] AND [email protected] è misto, come nella [email protected] porzione utilizzerà l’indice, ma [email protected] not (la query eseguirà la scansione di tutti i valori B per [email protected] , sarà non ‘salta’ in [email protected] ). Altri sistemi di database hanno il cosiddetto operatore “skip scan” che può avvantaggiarsi delle colonne interne in un indice quando le colonne esterne non sono specificate.

Con questa conoscenza in mano puoi rivedere le definizioni dell’indice. Un indice su (MostSelective, SecondMost, Least) sarà efficace solo quando è specificata la colonna MostSelective . Ma essendo il più selettivo, la rilevanza delle colonne interne si degraderà rapidamente. Molto spesso scoprirete che un indice migliore è su (MostSelective) include (SecondMost, Least) o on (MostSelective, SecondMost) include (Least) . Poiché le colonne interne sono meno rilevanti, posizionando colonne con bassa selettività in tali posizioni corrette nell’indice non si ottiene altro che rumore per una ricerca, quindi ha senso spostarle fuori dalle pagine intermedie e tenerle solo sulle pagine foglia, per scopi di copertura di query. In altre parole, spostali in INCLUDE. Ciò diventa più importante all’aumentare della dimensione della colonna Least . L’idea è che questo indice può solo avvantaggiare query che specificano MostSelective come valore esatto o intervallo, e quella colonna essendo la più selettiva limita già in larga misura le righe candidate.

D’altra parte un indice su (Least, SecondMost, MostSelective) può sembrare un errore, ma in realtà è un indice piuttosto potente. Poiché ha la colonna Least come query più esterna, può essere utilizzata per le query che devono aggregare i risultati su colonne a bassa selettività. Tali query sono prevalenti nei data warehouse di analisi e OLAP, e questo è esattamente il caso in cui tali indici hanno un ottimo caso per loro. Tali indici rendono in realtà eccellenti indici cluster , proprio perché organizzano il layout fisico su grossi blocchi di righe correlate (stesso valore di Least , che di solito indica una sorta di categoria o tipo) e facilitano le query di analisi.

Quindi, sfortunatamente, non esiste un ordine ‘corretto’. Non dovresti seguire alcuna ricetta di cookie cutter, ma invece analizzare il modello di query che stai per utilizzare contro quelle tabelle e decidere quale ordine di colonne indice è giusto.

dovresti mettere le colonne che saranno le più selettive all’inizio della dichiarazione dell’indice.

Corretta. Gli indici possono essere compositi – composti da più colonne – e l’ordine è importante a causa del principio più a sinistra. Il motivo è che il database controlla l’elenco da sinistra a destra e deve trovare un riferimento di colonna corrispondente che corrisponda all’ordine definito. Ad esempio, con un indice su una tabella di indirizzi con colonne:

  • Indirizzo
  • Città
  • Stato

Qualsiasi query che utilizza la colonna address può utilizzare l’indice, ma se la query presenta solo riferimenti di city e / o di state , l’indice non può essere utilizzato. Questo perché la colonna più a sinistra non è referenziata. Le prestazioni delle query dovrebbero indicare quale è ottimale: singoli indici o più compositi con ordini diversi. Buona lettura: The Tipping Point , di Kimberley Tripp

Come dice Remus, dipende dal tuo carico di lavoro.

Voglio affrontare però un aspetto fuorviante della risposta accettata.

Per le query che eseguono una ricerca di uguaglianza su tutte le colonne dell’indice non vi è alcuna differenza significativa.

Il sotto crea due tabelle e le popola con dati identici. L’unica differenza è che uno ha le chiavi ordinate dal più al meno selettivo e l’altro al contrario.

 CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null); CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least CHAR(1), Filler CHAR(4000) null); CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least); CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective); INSERT INTO Table1 (MostSelective, SecondMost, Least) output inserted.* into Table2 SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~' FROM master..spt_values WHERE type = 'P' AND number >= 0 ORDER BY number; 

Ora facendo una query su entrambe le tabelle …

 SELECT * FROM Table1 WHERE MostSelective = REPLICATE('P', 800) AND SecondMost = 3 AND Least = '~'; SELECT * FROM Table2 WHERE MostSelective = REPLICATE('P', 800) AND SecondMost = 3 AND Least = '~'; 

… Entrambi usano una multa dell’indice ed entrambi hanno lo stesso identico costo.

inserisci la descrizione dell'immagine qui

L’arte ASCII nella risposta accettata non è in realtà come sono strutturati gli indici. Le pagine indice per Table1 sono rappresentate di seguito (fare clic sull’immagine per aprirla a schermo intero).

inserisci la descrizione dell'immagine qui

Le pagine indice contengono righe che contengono l’intera chiave (in questo caso c’è in realtà una colonna chiave aggiuntiva aggiunta per l’identificatore di riga in quanto l’indice non è stato dichiarato come unico ma che può essere ignorato ulteriori informazioni su questo possono essere trovate qui ).

Per la query sopra SQL Server non interessa la selettività delle colonne. Fa una ricerca binaria della pagina radice e scopre che la chiave (PPP...,3,~ ) è >=(JJJ...,1,~ ) e < (SSS...,3,~ ) quindi dovrebbe leggere la pagina 1:118 . Quindi esegue una ricerca binaria delle voci chiave su quella pagina e individua la pagina della foglia da cui scendere.

La modifica dell'indice in ordine di selettività non influisce sul numero previsto di confronti chiave dalla ricerca binaria o sul numero di pagine che devono essere esplorate per eseguire una ricerca indice. Al massimo potrebbe velocizzare leggermente il confronto delle chiavi stesso.

Talvolta, ordinare prima l'indice più selettivo avrà comunque senso per altre query nel carico di lavoro.

Ad esempio se il carico di lavoro contiene query di entrambi i moduli seguenti.

 SELECT * ... WHERE MostSelective = 'P' SELECT * ...WHERE Least = '~' 

Gli indici sopra non coprono nessuno dei due. MostSelective è abbastanza selettivo da rendere utile un piano con ricerche e ricerche, ma la query contro Least non lo è.

Tuttavia questo scenario (indice non coprente cerca sul sottogruppo di colonne principali di un indice composito) è solo una ansible class di query che può essere aiutata da un indice. Se in realtà MostSelective non cerca mai da solo o una combinazione di MostSelective, SecondMost e cerca sempre da una combinazione di tutte e tre le colonne, questo vantaggio teorico è inutile per te.

Richieste al contrario come

 SELECT MostSelective, SecondMost, Least FROM Table2 WHERE Least = '~' ORDER BY SecondMost, MostSelective 

Sarebbe utile avere l'ordine inverso di quello comunemente prescritto - poiché copre la query, può supportare una ricerca e restituisce le righe nell'ordine desiderato per l'avvio.

Quindi questo è un consiglio spesso ripetuto, ma al massimo è un'euristica circa il potenziale beneficio per altre domande - e non è un sostituto per guardare realmente al carico di lavoro.