--Un index este o structură pe disc care este asociată cu un tabel sau vizualizare și accelerează preluarea rândurilor din tabel sau vizualizare. Un index conține chei construite dintr-una sau mai multe coloane dintr-un tabel sau vizualizare. Aceste chei sunt stocate într-o structură arborescentă echilibrată care acceptă căutarea rapidă a rândurilor după valorile lor cheie în SQL Server.

--Indecurile grupate sortează și stochează rânduri de date în tabele sau vizualizări pe baza valorilor cheie ale acestora. Aceste valori sunt coloanele incluse în definiția indexului. Există un singur index grupat per tabel, deoarece rândurile de date pot fi sortate doar într-o singură ordine.
--Rândurile de date dintr-un tabel sunt stocate în ordine de sortare numai dacă tabelul conține un index grupat. Dacă un tabel are un index grupat, atunci tabelul se numește clustered. Dacă un tabel nu are un index grupat, rândurile de date sunt stocate într-o structură neordonată numită heap.

--Un index negrupat are exact aceeași structură ca un index grupat, dar cu două diferențe importante:
--un index nonclustered nu modifică ordinea fizică a rândurilor din tabel, iar paginile frunze dintr-un index nonclustered constau din chei de index și marcaje.

--Indecșii în cluster oferă o recuperare mai rapidă a datelor decât indecșii neclustrat. De obicei, se dovedesc a fi mai rapide și atunci când se actualizează, dar nu și atunci când au loc multe actualizări în același loc în mijlocul relației.

--Din anumite motive, un index cluster tinde să ruleze mai repede decât un index nonclustered. Când sistemul scanează un index grupat, nu este nevoie să părăsiți structura B-tree pentru a scana pagini de date, deoarece astfel de pagini sunt deja prezente la nivelul frunzei arborelui.

--Un index nonclustered necesită, de asemenea, mai multe operațiuni I/O decât indexul clustered corespunzător.

--Indexul nonclustered trebuie să citească paginile de date după scanarea arborelui B sau, dacă există un index grupat pe o altă coloană(e) a tabelului, indexul nonclustrat trebuie să citească structura arborelui B a indexului clusterizat .

--Deci, un index grupat va fi semnificativ mai rapid decât o scanare de tabel, chiar dacă selectivitatea sa este destul de slabă (interogarea returnează o mulțime de rânduri)

CREATE TABLE tsql.dbo.NI
ID int NU NULL,
T char(8) NULL
);

CREATE TABLE tsql.dbo.NCI
ID int NU NULL,
T char(8) NULL
);

--Creați un index grupat

CREAȚI INDEXUL CLUSTERED IX_1
ON tsql.dbo.NCI(ID);

--Creați un index nonclustered pe un tabel

CREAȚI INDEXUL NONCLUSTERED IX_2
ON tsql.dbo.NCI(T);

--Adăugați date de testare
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

ÎN CAZUL @i > 0
ÎNCEPE
inserați în valorile tsql.dbo.NI(@i, @t + CAST(@i AS char(6)));
inserați în valorile tsql.dbo.NCI(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
Sfârşit

--Interogări pe un tabel cu indecși
SELECT ID, T DIN tsql.dbo.NCI
COMANDA PE ID, T

SELECT ID, COUNT(*) CA C DIN tsql.dbo.NCI
GRUPĂ DUPĂ ID, T

SELECT ID, T DIN tsql.dbo.NCI
UNDE ID > 4000 ȘI ID< 55000 AND T LIKE "T%"

--Interogare folosind ambii indecși
UTILIZAȚI tsql;
SELECTARE CAST(dbo.NCI.ID AS VARCHAR)
DE LA dbo.NCI
GRUP DE DBO.NCI.ID
UNIREA TOȚI
SELECT dbo.NCI.T
DE LA dbo.NCI
GRUP DE DBO.NCI.T

--Informații despre indici
SELECT index_type_desc, index_depth, index_level,
page_count, record_count
DE LA sys.dm_db_index_physical_stats
(DB_ID(N"tsql")), OBJECT_ID(N"dbo.NCI"), NULL, NULL , "DETALIAT");

--Ștergerea indecșilor
DACĂ EXISTĂ (SELECTAȚI numele DIN sys.indexes
WHERE nume = N"IX_1")
DROP INDEX IX_1 PE tsql.dbo.NCI;

DACĂ EXISTĂ (SELECTAȚI numele DIN sys.indexes
WHERE nume = N"IX_2")
DROP INDEX IX_2 PE tsql.dbo.NCI;

În articolul anterior, am introdus modalități de optimizare a bazelor de date relaționale și am discutat cum funcționează indecșii grupați și nonclustered în contextul optimizării timpului de execuție a interogărilor bazei de date. Acum este timpul să punem aceste cunoștințe în practică, învățând cum să creați indici de optimizare pentru o bază de date MS SQL.

Permiteți-mi să vă reamintesc de definiția schemei de tabel Staffs cu care vom lucra:

Masa personalului

Să presupunem că trebuie să creăm un index non-cluster pentru tabelul Staffs, care va optimiza următoarea interogare:

SELECTAȚI Id, Numele, Postul DIN Lucruri UNDE SALARIUL > 1000 ȘI Fotografia NU ESTE NULĂ

Cheia de index va fi coloanele SALARIU și Fotografie, deoarece selecția este filtrată de aceste câmpuri. Iar coloanele Id, Nume și Job vor fi coloanele incluse în index.

Sintaxa generală a comenzii este următoarea:

UTILIZARE MERGE

CREAȚI INDEXUL NONCLUSTER PE (ASC -- coloane cheie index)

INCLUDE ( -- coloane incluse) GO

În cazul nostru, cererea va arăta astfel:

(Salariu, Foto) INCLUDE (Id, Nume, Job) GO

Am creat un index non-cluster. Sau, mai degrabă, un indice de acoperire negrupat. Aceasta înseamnă că indexul conține toate câmpurile necesare pentru a executa interogarea și SQL Server nu va accesa tabelul de bază la executarea interogării.

Dacă codul nostru ar fi așa:

CREAȚI INDEX NONCLUSTERED IDX_StaffsSearch ON Stuffs

(Salariu, Foto) INCLUDE (Id) GO

În acest caz, indexul încetează să mai fie un index de acoperire, deoarece nu include toate coloanele utilizate în interogare. Optimizatorul va folosi în continuare acest index la executarea interogării, dar eficiența acestuia va fi redusă cu un ordin de mărime, deoarece va necesita acces la tabelul de bază.

Indexul grupat este creat folosind următoarea comandă:

CREATE CLUSTERED INDEX IDX_Stsffsid ON Stuffs (Id)

Aici a fost creat un index unic grupat pe baza cheii primare a tabelului (coloana Id).

Exemplu real

Să dezvoltăm acum un scenariu în care putem evalua realist gradul de câștig de performanță în cazul utilizării indicilor.

Să creăm o nouă bază de date:

CREAȚI BAZĂ DE DATE TestDB;

Și un singur tabel Clienți, care va fi format din patru coloane:

CREATE TABLE .(

NOT NULL, NULL, NULL, NULL) GO

Acum să umplem tabelul nostru cu date aleatorii. Coloana Id va fi mărită într-o buclă, iar celelalte trei coloane ale tabelului vor fi umplute cu numere aleatorii folosind o versiune particulară a funcției aleatoare:

DECLARE @i int = 0;

In timp ce eu< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid())) SET @i = @i + 1; Sfârşit

Acest script adaugă o jumătate de milion de înregistrări la tabel, așa că aveți răbdare, scriptul va rula cel puțin 3 minute.

Totul este gata pentru test. Vom evalua caracteristicile de performanță ale interogării. Deoarece timpul de execuție a interogării poate depinde de mașina specifică, vom analiza un indicator mai independent - numărul de citiri logice.

Pentru a activa modul de colectare a statisticilor, trebuie să rulați următoarea comandă:

Acum, după executarea fiecărei solicitări, în fila Mesaje vom avea acces la statistici privind executarea acestei solicitări, după cum se arată mai jos:

Ne interesează doar valoarea parametrului de citire logică.

Deci, nu există încă indici în tabelul nostru. Să rulăm următoarele trei interogări și să înregistrăm numărul de citiri logice pentru fiecare interogare în tabelul cu rezultate de mai jos:

1) SELECT ID, Num1, Num2 FROM Customers WHERE Id = 2000

2) SELECTează Id, Num1, Num2 FROM Clienți WHERE Id >= 0 AND Id< 1000

3) SELECTează Id, Num1, Num2 FROM Clienți WHERE Id >= 0 AND Id< 5000

Aceste interogări vor returna 1 rând, 1000 de rânduri și, respectiv, 5000 de rânduri. Fără indici, indicatorul de performanță (numărul de citiri logice) pentru toate interogările este același și egal cu 1621. Să introducem datele în tabelul cu rezultate:

Vedem că pentru a doua și a treia interogare, atunci când se returnează un număr destul de mare de rânduri, indexul creat nu a îmbunătățit performanța. Cu toate acestea, pentru o interogare care returnează un singur rând, accelerarea a fost uriașă. Astfel, putem concluziona că are sens să creăm indecși care nu acoperă atunci când optimizăm interogări care returnează un singur rezultat.

Acum să creăm un indice de acoperire, obținând astfel performanță maximă.

Mai întâi, să ștergem indexul anterior:

UTILIZAȚI TestDB GO DROP INDEX Clienți.TestIndex1

Și să creăm un nou index:

CREATE NONCLUSTERED INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

Acum să rulăm interogările noastre a treia oară și să scriem rezultatele într-un tabel:

Fără indici
Indice de neacoperire
Index de acoperire

Este ușor de observat că creșterea performanței a fost enormă. Astfel, am mărit viteza de execuție a interogărilor de zeci de ori. Când rulați o bază de date care stochează milioane de rânduri, acest câștig de performanță va fi destul de vizibil.

În acest articol, am analizat un exemplu de optimizare a unei baze de date prin crearea de indici. Este de remarcat faptul că crearea de indici este un proces pur individual pentru fiecare cerere. Pentru a construi un index care va optimiza cu adevărat performanța interogării, trebuie să analizați cu atenție interogarea în sine și planul său de execuție.

Construirea eficientă a indexului este una dintre cele mai bune modalități de a îmbunătăți performanța unei aplicații de bază de date. Fără utilizarea indecșilor, SQL Server este ca un cititor care încearcă să găsească un cuvânt într-o carte privind fiecare pagină. Dacă cartea are un index de subiecte (index), cititorul poate căuta mult mai rapid informațiile necesare.

În absența unui index, serverul SQL, atunci când preia date dintr-un tabel, va scana întregul tabel și va verifica fiecare rând pentru a vedea dacă criteriile de interogare sunt îndeplinite. O astfel de scanare completă poate fi dezastruoasă pentru performanța întregului sistem, mai ales dacă există o mulțime de date în tabele.

Una dintre cele mai importante sarcini atunci când lucrați cu o bază de date este construirea unui index optim pentru a îmbunătăți performanța sistemului. Majoritatea bazelor de date majore oferă instrumente pentru a vizualiza planul de execuție a interogărilor și vă ajută să reglați și să optimizați indecșii. Acest articol evidențiază câteva reguli generale bune care se aplică atunci când se creează sau se modifică indecși într-o bază de date. Mai întâi, să ne uităm la situațiile în care indexarea îmbunătățește performanța și în care indexarea poate dăuna.

Indici utili

Deci, indexarea tabelelor va fi utilă atunci când căutați o anumită înregistrare într-un tabel folosind instrucțiunea Where. Astfel de interogări includ, de exemplu, interogări care caută un interval de valori, interogări care potrivesc o valoare exactă cu o anumită valoare și interogări care îmbină două tabele.

De exemplu, următoarele interogări împotriva bazei de date Northwind vor rula mai eficient atunci când construiți un index pe coloana UnitPrice.

Ștergeți din produse unde UnitPrice=1
Selectați * din produsele unde preț unitar între 14 și 16

Deoarece articolele de index sunt stocate sortate, indexarea este, de asemenea, utilă atunci când construiți o interogare folosind clauza Order by. Fără un index, înregistrările sunt încărcate și sortate în timp ce interogarea rulează. Un index bazat pe UnitPrice vă va permite să scanați pur și simplu indexul și să preluați rândurile prin referință atunci când procesați următoarea cerere. Dacă doriți să sortați rândurile în ordine descrescătoare, puteți pur și simplu să scanați indexul în ordine inversă.

Selectați * Din comanda produselor după UnitPrice ASC

Gruparea unei înregistrări folosind declarația Grupare după necesită adesea sortare, astfel încât construirea unui index pe coloana UnitPrice va fi utilă și pentru următoarea interogare care numără numărul de unități ale unui produs la fiecare preț specific.

Selectați count(*), UnitPrice Din Grupul de produse după UnitPrice

Indicii sunt folositori pentru menținerea unei valori unice pentru o coloană, deoarece SGBD-ul poate privi cu ușurință indexul pentru a vedea dacă valoarea există deja. Din acest motiv, cheile primare sunt întotdeauna indexate.

Dezavantajele indexării

Indicii degradează performanța sistemului în timpul modificărilor înregistrărilor. De fiecare dată când se execută o interogare pentru a modifica datele dintr-un tabel, indexul trebuie să se schimbe. Pentru a selecta numărul optim de indici, trebuie să testați baza de date și să monitorizați performanța acesteia. Sistemele statice, în care bazele de date sunt utilizate în principal pentru regăsirea datelor, cum ar fi raportarea, pot conține mai mulți indici pentru a accepta interogări doar în citire. Bazele de date cu un număr mare de tranzacții pentru a modifica datele vor avea nevoie de un număr mic de indici pentru a oferi un randament mai mare.

Indecșii ocupă spațiu suplimentar pe disc și în RAM. Mărimea exactă va depinde de numărul de înregistrări din tabel, precum și de numărul și dimensiunea coloanelor din index. În majoritatea cazurilor, aceasta nu este o problemă majoră, deoarece spațiul pe disc este acum ușor de sacrificat pentru o performanță mai bună.

Construirea unui index optim

Index simplu

Un index simplu este un index care folosește valorile unui singur câmp dintr-un tabel. Utilizarea unui index simplu este benefică din două motive. În primul rând, rularea unei baze de date pune mult stres pe hard disk. Cheile de index mari vor forța baza de date să efectueze mai multe operațiuni I/O, ceea ce limitează performanța.

În al doilea rând, deoarece elementele indexului sunt adesea implicate în comparații, indici mai mici sunt mai ușor de comparat. Din aceste două motive, o singură coloană cu numere întregi este un index mai bun, deoarece este mic și ușor de comparat. Șirurile de caractere, pe de altă parte, necesită comparații caracter cu caracter și atenție la manipularea parametrilor.

Index selectiv

Cei mai eficienți indici sunt cei cu un procent mic de valori duplicate. De exemplu, o agendă telefonică pentru un oraș în care aproape toată lumea are numele de familie Smith nu va fi la fel de utilă dacă intrările din acesta sunt sortate după nume de familie.

Un index cu un procent mare de valori unice se mai numește și indice selectiv. Evident, un indice unic are cea mai mare selectivitate, deoarece nu conține valori duplicate. Multe SGBD-uri pot urmări statistici despre fiecare index și pot recunoaște câte valori neduplicate conține fiecare index. Această statistică este utilizată la generarea unui plan de execuție a interogării.

Indici de acoperire

Indecii constau dintr-o coloană de date pe care este construit indexul însuși și un pointer către rândul corespunzător. Este ca indexul unei cărți: conține doar cuvintele cheie și un link către o pagină la care poți accesa pentru mai multe informații. În mod obișnuit, DBMS va urma indicatorii către un rând din index pentru a colecta toate informațiile necesare pentru interogare. Cu toate acestea, dacă indexul conține toate coloanele necesare în interogare, informațiile pot fi preluate fără a accesa tabelul în sine.

Să luăm în considerare un index pe coloana UnitPrice, care a fost deja menționat mai sus. SGBD-ul poate folosi numai elementele de index pentru a executa următoarea interogare.

Selectați Count(*), UnitPrice Din Grupul de produse după UnitPrice

Acest tip de interogare se numește interogare de acoperire deoarece toate coloanele interogate pot fi preluate dintr-un singur index. Pentru cele mai importante interogări, poate doriți să luați în considerare crearea unui index de acoperire pentru cea mai bună performanță posibilă. Este posibil ca astfel de indici să fie compuși (folosind mai mult de o coloană), ceea ce este opusul primului principiu: creați indici simpli. Evident, alegerea numărului optim de coloane dintr-un index poate fi evaluată doar prin testarea și monitorizarea performanței bazei de date în diverse situații.

Indexul clusterului

Multe baze de date au un index special pe un tabel, unde toate datele dintr-un rând sunt conținute în index. În SQL Server, un astfel de index este numit index cluster. Un index grupat poate fi comparat cu un director telefonic deoarece fiecare element de index conține toate informațiile de care aveți nevoie și nu conține link-uri pentru a obține date suplimentare.

Există o regulă generală - fiecare tabel non-trivial trebuie să aibă un index grupat. Dacă este posibil să creați un singur index pe un tabel, faceți-l grupat. În SQL Server, atunci când este creată o cheie primară, va fi creat automat un index grupat (dacă nu conține deja unul), folosind coloana cheii primare ca cheie de indexare. Un index cluster este cel mai eficient index (dacă este utilizat, acoperă întreaga interogare) și în multe SGB-uri un astfel de index ajută la gestionarea eficientă a spațiului solicitat pentru stocarea tabelelor, deoarece altfel (fără a construi un index cluster) rândurile de tabel sunt stocate în o structură neordonată, numită grămada.

Aveți grijă când selectați coloane pentru un index grupat. Dacă modificați o înregistrare și modificați valoarea unei coloane într-un index grupat, baza de date va fi forțată să reconstruiască elementele indexului (pentru a le păstra în ordine sortată). Amintiți-vă, elementele de index pentru un index grupat conțin toate valorile coloanei, astfel încât modificarea valorii unei coloane este comparabilă cu executarea unei instrucțiuni Delete urmată de o instrucțiune Insert, care va cauza, evident, probleme de performanță dacă se face frecvent. Din acest motiv, indecșii grupați constau adesea dintr-o cheie primară și o coloană cheie externă. Dacă valorile cheie se schimbă, acestea se schimbă foarte rar.

Concluzie

Determinarea indicilor corecti de utilizat într-o bază de date necesită o analiză și testare atentă a sistemului. Practicile prezentate în acest articol sunt reguli bune pentru construirea de indici. După aplicarea acestor metode, va trebui să retestați aplicația dumneavoastră specifică în condițiile specifice de hardware, memorie și operațiuni.

Una dintre cele mai importante moduri de a obține o productivitate ridicată SQL Server este utilizarea indicilor. Un index accelerează procesul de interogare, oferind acces rapid la rândurile de date dintr-un tabel, la fel ca un index dintr-o carte vă ajută să găsiți rapid informațiile de care aveți nevoie. În acest articol voi oferi o scurtă prezentare generală a indicilor în SQL Serverși explicați cum sunt organizate în baza de date și cum ajută la accelerarea interogărilor bazei de date.

Indecii sunt creați pe coloanele de tabel și vizualizare. Indecșii oferă o modalitate de a căuta rapid date pe baza valorilor din acele coloane. De exemplu, dacă creați un index pe o cheie primară și apoi căutați un rând de date folosind valorile cheii primare, atunci SQL Server va găsi mai întâi valoarea indexului și apoi va folosi indexul pentru a găsi rapid întregul rând de date. Fără un index, va fi efectuată o scanare completă a tuturor rândurilor din tabel, ceea ce poate avea un impact semnificativ asupra performanței.
Puteți crea un index pe majoritatea coloanelor dintr-un tabel sau vizualizare. Excepția sunt în principal coloanele cu tipuri de date pentru stocarea obiectelor mari ( LOB), ca imagine, text sau varchar(max). De asemenea, puteți crea indecși pe coloanele concepute pentru a stoca date în format XML, dar acești indici sunt structurați ușor diferit față de cei standard și luarea în considerare a acestora depășește domeniul de aplicare al acestui articol. De asemenea, articolul nu discută columnstore indici. În schimb, mă concentrez pe acei indecși care sunt cel mai frecvent utilizați în bazele de date SQL Server.
Un index constă dintr-un set de pagini, noduri de index, care sunt organizate într-o structură arborescentă - arbore echilibrat. Această structură este de natură ierarhică și începe cu un nod rădăcină în partea de sus a ierarhiei și noduri frunză, frunzele, în partea de jos, așa cum se arată în figură:


Când interogați o coloană indexată, motorul de interogare pornește în partea de sus a nodului rădăcină și își parcurge drumul în jos prin nodurile intermediare, fiecare strat intermediar conținând informații mai detaliate despre date. Motorul de interogare continuă să se deplaseze prin nodurile de index până când ajunge la nivelul de jos cu frunzele de index. De exemplu, dacă căutați valoarea 123 într-o coloană indexată, motorul de interogare va determina mai întâi pagina la primul nivel intermediar la nivelul rădăcină. În acest caz, prima pagină indică o valoare de la 1 la 100, iar a doua de la 101 la 200, astfel încât motorul de interogare va accesa a doua pagină a acestui nivel intermediar. În continuare, veți vedea că ar trebui să treceți la a treia pagină a următorului nivel intermediar. De aici, subsistemul de interogări va citi valoarea indexului însuși la un nivel inferior. Frunzele de index pot conține fie datele tabelului în sine, fie pur și simplu un pointer către rânduri cu date în tabel, în funcție de tipul de index: index cluster sau index nonclustered.

Index grupat

Un index grupat stochează rândurile reale de date în frunzele indexului. Revenind la exemplul anterior, aceasta înseamnă că rândul de date asociat cu valoarea cheie de 123 va fi stocat în indexul însuși. O caracteristică importantă a unui index grupat este că toate valorile sunt sortate într-o anumită ordine, fie crescătoare, fie descrescătoare. Prin urmare, un tabel sau o vizualizare poate avea un singur index grupat. În plus, trebuie remarcat faptul că datele dintr-un tabel sunt stocate în formă sortată numai dacă pe acest tabel a fost creat un index grupat.
Un tabel care nu are un index grupat se numește heap.

Index negrupat

Spre deosebire de un index grupat, frunzele unui index nonclustered conțin doar acele coloane ( cheie) prin care se determină acest index și, de asemenea, conține un pointer către rânduri cu date reale din tabel. Aceasta înseamnă că sistemul de subinterogare necesită o operațiune suplimentară pentru a localiza și a prelua datele necesare. Conținutul indicatorului de date depinde de modul în care sunt stocate datele: tabel în cluster sau heap. Dacă un indicator indică un tabel grupat, acesta indică un index grupat care poate fi utilizat pentru a găsi datele reale. Dacă un pointer se referă la un heap, atunci indică un anumit identificator de rând de date. Indecșii nonclustered nu pot fi sortați ca indici clustered, dar puteți crea mai mult de un index nonclustered pe un tabel sau vizualizare, până la 999. Acest lucru nu înseamnă că ar trebui să creați cât mai mulți indecși posibil. Indicii pot fie îmbunătăți, fie pot degrada performanța sistemului. Pe lângă faptul că puteți crea mai mulți indecși non-cluster, puteți include și coloane suplimentare ( coloana inclusă) în indexul său: frunzele indexului vor stoca nu numai valoarea coloanelor indexate în sine, ci și valorile acestor coloane suplimentare neindexate. Această abordare vă va permite să ocoliți unele dintre restricțiile impuse indexului. De exemplu, puteți include o coloană neindexabilă sau puteți ocoli limita de lungime a indexului (900 de octeți în majoritatea cazurilor).

Tipuri de indici

Pe lângă faptul că este fie un index grupat, fie non-cluster, acesta poate fi configurat în continuare ca index compus, index unic sau index de acoperire.

Indice compozit

Un astfel de index poate conține mai mult de o coloană. Puteți include până la 16 coloane într-un index, dar lungimea lor totală este limitată la 900 de octeți. Atât indecșii grupați, cât și cei care nu sunt grupați pot fi compuși.

Index unic

Acest index asigură că fiecare valoare din coloana indexată este unică. Dacă indexul este compus, atunci unicitatea se aplică tuturor coloanelor din index, dar nu fiecărei coloane individuale. De exemplu, dacă creați un index unic pe coloane NUMEȘi NUME DE FAMILIE, atunci numele complet trebuie să fie unic, dar sunt posibile duplicate în nume sau prenume.
Un index unic este creat automat atunci când definiți o constrângere de coloană: cheie primară sau constrângere de valoare unică:

• Cheia principala
  Atunci când definiți o constrângere a cheii primare pe una sau mai multe coloane, atunci SQL Server creează automat un index cluster unic dacă nu a fost creat anterior un index cluster (în acest caz, un index unic non-cluster este creat pe cheia primară)
• Unicitatea valorilor
  Atunci când definiți o constrângere asupra unicității valorilor, atunci SQL Server creează automat un index unic non-cluster. Puteți specifica crearea unui index cluster unic dacă nu a fost încă creat niciun index cluster pe tabel

Index de acoperire

Un astfel de index permite unei interogări specifice să obțină imediat toate datele necesare din frunzele indexului fără acces suplimentar la înregistrările tabelului în sine.

Proiectarea indicilor

Oricât de utili pot fi indicii, ei trebuie proiectați cu atenție. Deoarece indexurile pot ocupa un spațiu semnificativ pe disc, nu doriți să creați mai mulți indecși decât este necesar. În plus, indecșii sunt actualizați automat atunci când rândul de date în sine este actualizat, ceea ce poate duce la supraîncărcare suplimentară a resurselor și la degradarea performanței. La proiectarea indexurilor trebuie luate în considerare mai multe considerații referitoare la baza de date și interogările împotriva acesteia.

Bază de date

După cum sa menționat mai devreme, indexurile pot îmbunătăți performanța sistemului deoarece acestea oferă motorului de interogări o modalitate rapidă de a găsi date. Cu toate acestea, ar trebui să țineți cont și de cât de des intenționați să introduceți, să actualizați sau să ștergeți datele. Când modificați datele, indecșii trebuie, de asemenea, modificați pentru a reflecta acțiunile corespunzătoare asupra datelor, ceea ce poate reduce semnificativ performanța sistemului. Luați în considerare următoarele linii directoare atunci când vă planificați strategia de indexare:

• Pentru tabelele care sunt actualizate frecvent, utilizați cât mai puțini indecși.
• Dacă tabelul conține o cantitate mare de date, dar modificările sunt minore, atunci utilizați cât mai mulți indecși este necesar pentru a îmbunătăți performanța interogărilor dvs. Cu toate acestea, gândiți-vă bine înainte de a utiliza indici pe tabele mici, deoarece... Este posibil ca utilizarea unei căutări index să dureze mai mult decât simpla scanare a tuturor rândurilor.
• Pentru indecșii grupați, încercați să păstrați câmpurile cât mai scurte posibil. Cea mai bună abordare este să folosiți un index grupat pe coloanele care au valori unice și nu permit NULL. Acesta este motivul pentru care o cheie primară este adesea folosită ca index grupat.
• Unicitatea valorilor dintr-o coloană afectează performanța indexului. În general, cu cât aveți mai multe duplicate într-o coloană, cu atât indexul are performanțe mai slabe. Pe de altă parte, cu cât sunt mai multe valori unice, cu atât performanța indicelui este mai bună. Folosiți un index unic ori de câte ori este posibil.
• Pentru un index compus, luați în considerare ordinea coloanelor din index. Coloane care sunt folosite în expresii UNDE(De exemplu, WHERE Prenume = „Charlie”) trebuie să fie primul în index. Coloanele ulterioare ar trebui listate pe baza unicității valorilor lor (coloanele cu cel mai mare număr de valori unice vin pe primul loc).
• De asemenea, puteți specifica un index pe coloanele calculate dacă acestea îndeplinesc anumite cerințe. De exemplu, expresiile folosite pentru a obține valoarea unei coloane trebuie să fie deterministe (se returnează întotdeauna același rezultat pentru un set dat de parametri de intrare).

Interogări baze de date

O altă considerație la proiectarea indicilor este ce interogări sunt executate în baza de date. După cum am menționat mai devreme, trebuie să luați în considerare cât de des se schimbă datele. În plus, trebuie utilizate următoarele principii:

• Încercați să inserați sau să modificați cât mai multe rânduri posibil într-o singură interogare, mai degrabă decât să faceți acest lucru în mai multe interogări individuale.
• Creați un index negrupat pe coloanele care sunt utilizate frecvent ca termeni de căutare în interogările dvs. UNDEși conexiuni în A TE ALATURA.
• Luați în considerare indexarea coloanelor utilizate în interogările de căutare a rândurilor pentru potriviri exacte ale valorii.

Și acum, de fapt:

14 întrebări despre indecși în SQL Server pe care ați fost jenat să le puneți

De ce nu poate un tabel să aibă doi indici grupați?

Vrei un răspuns scurt? Un index grupat este un tabel. Când creați un index grupat pe un tabel, motorul de stocare sortează toate rândurile din tabel în ordine crescătoare sau descrescătoare, conform definiției indexului. Un index grupat nu este o entitate separată ca alți indecși, ci un mecanism pentru sortarea datelor într-un tabel și facilitarea accesului rapid la rândurile de date.
Să ne imaginăm că aveți un tabel care conține istoricul tranzacțiilor de vânzare. Tabelul de vânzări include informații precum ID-ul comenzii, poziția produsului în comandă, numărul produsului, cantitatea de produs, numărul și data comenzii etc. Creați un index grupat pe coloane Comanda IDȘi LineID, sortate în ordine crescătoare, după cum se arată în continuare T-SQL cod:
CREAȚI INDEXUL UNIC CLUSTERED ix_oriderid_lineid PE dbo.Sales(OrderID, LineID);
Când rulați acest script, toate rândurile din tabel vor fi sortate fizic mai întâi după coloana OrderID și apoi după LineID, dar datele în sine vor rămâne într-un singur bloc logic, tabelul. Din acest motiv, nu puteți crea doi indecși grupați. Poate exista un singur tabel cu o singură dată și acel tabel poate fi sortat o singură dată într-o anumită ordine.

Dacă un tabel grupat oferă multe beneficii, atunci de ce să folosiți un heap?

Ai dreptate. Tabelele grupate sunt excelente și majoritatea interogărilor dvs. vor avea performanțe mai bune pe tabelele care au un index grupat. Dar, în unele cazuri, poate doriți să lăsați mesele în starea lor naturală, curată, de exemplu. sub forma unui heap și creați numai indecși non-cluster pentru a vă menține interogările în funcțiune.
Heap-ul, după cum vă amintiți, stochează datele în ordine aleatorie. De obicei, subsistemul de stocare adaugă date la un tabel în ordinea în care sunt introduse, dar subsistemul de stocare îi place, de asemenea, să mute rândurile pentru o stocare mai eficientă. Ca urmare, nu aveți nicio șansă să preziceți în ce ordine vor fi stocate datele.
Dacă motorul de interogări trebuie să găsească date fără a beneficia de un index neclustrat, va efectua o scanare completă a tabelului pentru a găsi rândurile de care are nevoie. Pe mesele foarte mici, aceasta nu este de obicei o problemă, dar pe măsură ce grămada crește în dimensiune, performanța scade rapid. Desigur, un index non-cluster poate ajuta folosind un indicator către fișierul, pagina și rândul în care sunt stocate datele necesare - aceasta este de obicei o alternativă mult mai bună la o scanare a tabelului. Chiar și așa, este dificil să compari beneficiile unui index grupat atunci când luăm în considerare performanța interogărilor.
Cu toate acestea, grămada poate ajuta la îmbunătățirea performanței în anumite situații. Luați în considerare un tabel cu multe inserări, dar puține actualizări sau ștergeri. De exemplu, un tabel care stochează un jurnal este folosit în primul rând pentru a insera valori până când este arhivat. Pe heap, nu veți vedea paginarea și fragmentarea datelor așa cum ați face cu un index grupat, deoarece rândurile sunt pur și simplu adăugate la sfârșitul heap-ului. Împărțirea prea mult a paginilor poate avea un impact semnificativ asupra performanței și nu într-un mod bun. În general, heap-ul vă permite să inserați date relativ fără durere și nu va trebui să vă ocupați de costurile de stocare și întreținere pe care le-ați face cu un index cluster.
Dar lipsa actualizării și ștergerii datelor nu ar trebui considerată singurul motiv. Modul în care datele sunt eșantionate este, de asemenea, un factor important. De exemplu, nu ar trebui să utilizați o grămadă dacă interogați frecvent intervale de date sau datele pe care le interogați adesea trebuie să fie sortate sau grupate.
Toate acestea înseamnă că ar trebui să luați în considerare utilizarea heap-ului numai atunci când lucrați cu tabele foarte mici sau toată interacțiunea cu tabelul este limitată la inserarea de date și interogările sunt extrem de simple (și utilizați indecși non-cluster). oricum). În caz contrar, rămâneți cu un index grupat bine conceput, cum ar fi unul definit pe un câmp cheie ascendent simplu, cum ar fi o coloană utilizată pe scară largă cu IDENTITATE.

Cum modific factorul de umplere implicit al indexului?

Modificarea factorului de umplere implicit al indexului este un lucru. Înțelegerea modului în care funcționează raportul implicit este o altă chestiune. Dar mai întâi, fă câțiva pași înapoi. Factorul de umplere a indexului determină cantitatea de spațiu pe pagină pentru a stoca indexul la nivelul de jos (nivelul frunzei) înainte de a începe să umpleți o pagină nouă. De exemplu, dacă coeficientul este setat la 90, atunci când indicele crește, acesta va ocupa 90% din pagină și apoi va trece la pagina următoare.
În mod implicit, valoarea factorului de umplere a indexului este în SQL Server este 0, care este la fel cu 100. Ca rezultat, toți indecșii noi moștenesc automat această setare, cu excepția cazului în care specificați în mod specific o valoare în codul dvs. care este diferită de valoarea standard a sistemului sau modificați comportamentul implicit. Poți să folosești SQL Server Management Studio pentru a ajusta valoarea implicită sau pentru a rula o procedură stocată în sistem sp_configure. De exemplu, următorul set T-SQL comanda setează valoarea coeficientului la 90 (mai întâi trebuie să comutați la modul setări avansate):
EXEC sp_configure „show advanced options”, 1; GO RECONFIGURĂ; GO EXEC sp_configure „factor de umplere”, 90; GO RECONFIGURĂ; MERGE
După modificarea valorii factorului de umplere a indexului, trebuie să reporniți serviciul SQL Server. Acum puteți verifica valoarea setată rulând sp_configure fără al doilea argument specificat:
EXEC sp_configure „factor de umplere” GO
Această comandă ar trebui să returneze o valoare de 90. Ca rezultat, toți indecșii nou creați vor folosi această valoare. Puteți testa acest lucru creând un index și interogând valoarea factorului de umplere:
UTILIZAȚI AdventureWorks2012; -- baza de date GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
În acest exemplu, am creat un index non-cluster pe un tabel Persoanăîn baza de date AdventureWorks2012. După crearea indexului, putem obține valoarea factorului de umplere din tabelele de sistem sys.indexes. Interogarea ar trebui să returneze 90.
Cu toate acestea, să ne imaginăm că am șters indexul și l-am creat din nou, dar acum am specificat o anumită valoare a factorului de umplere:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
De data aceasta am adăugat instrucțiuni CUși opțiunea factor de umplere pentru operațiunea noastră de creare a indexului CREAȚI INDEXși a specificat valoarea 80. Operator SELECTAȚI acum returnează valoarea corespunzătoare.
Până acum totul a fost destul de simplu. Unde vă puteți arde cu adevărat în întregul proces este atunci când creați un index care utilizează o valoare implicită a coeficientului, presupunând că cunoașteți acea valoare. De exemplu, cineva modifică setările serverului și este atât de încăpățânat încât a stabilit factorul de umplere a indexului la 20. Între timp, continuați să creați indici, presupunând că valoarea implicită este 0. Din păcate, nu aveți cum să aflați umplerea factor până când nu creați un index și apoi verificați valoarea așa cum am făcut în exemplele noastre. În caz contrar, va trebui să așteptați momentul în care performanța interogărilor scade atât de mult încât începeți să bănuiți ceva.
O altă problemă de care ar trebui să fii conștient este reconstruirea indicilor. Ca și în cazul creării unui index, puteți specifica valoarea factorului de umplere a indexului atunci când îl reconstruiți. Cu toate acestea, spre deosebire de comanda create index, reconstrucția nu folosește setările implicite ale serverului, în ciuda a ceea ce poate părea. Chiar mai mult, dacă nu specificați în mod specific valoarea factorului de umplere a indicelui, atunci SQL Server va folosi valoarea coeficientului cu care exista acest indice înainte de restructurarea lui. De exemplu, următoarea operație ALTER INDEX reconstruiește indexul pe care tocmai l-am creat:
ALTER INDEX ix_people_lastname ON Person.Person RECONSTRUIRE; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Când verificăm valoarea factorului de umplere, vom obține o valoare de 80, deoarece asta am specificat când am creat ultima dată indexul. Valoarea implicită este ignorată.
După cum puteți vedea, modificarea valorii factorului de umplere a indicelui nu este atât de dificilă. Este mult mai dificil să cunoști valoarea actuală și să înțelegi când este aplicată. Dacă specificați întotdeauna în mod specific coeficientul atunci când creați și reconstruiți indici, atunci știți întotdeauna rezultatul specific. Cu excepția cazului în care trebuie să vă faceți griji pentru a vă asigura că altcineva nu strica setările serverului din nou, determinând ca toți indexurile să fie reconstruite cu un factor de umplere ridicol de scăzut.

Este posibil să creați un index grupat pe o coloană care conține duplicate?

Da și nu. Da, puteți crea un index grupat pe o coloană cheie care conține valori duplicate. Nu, valoarea unei coloane cheie nu poate rămâne într-o stare non-unica. Lasă-mă să explic. Dacă creați un index grupat neunic pe o coloană, motorul de stocare adaugă un unificator la valoarea duplicată pentru a asigura unicitatea și, prin urmare, pentru a putea identifica fiecare rând din tabelul grupat.
De exemplu, puteți decide să creați un index grupat pe o coloană care conține date despre clienți Nume păstrând numele de familie. Coloana conține valorile Franklin, Hancock, Washington și Smith. Apoi inserați din nou valorile Adams, Hancock, Smith și Smith. Dar valoarea coloanei cheie trebuie să fie unică, astfel încât motorul de stocare va schimba valoarea duplicatelor astfel încât acestea să arate cam așa: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 și Smith5678.
La prima vedere, această abordare pare în regulă, dar o valoare întreagă mărește dimensiunea cheii, ceea ce poate deveni o problemă dacă există un număr mare de duplicate, iar aceste valori vor deveni baza unui index neclustrat sau a unui index străin. referință cheie. Din aceste motive, ar trebui să încercați întotdeauna să creați indecși grupați unici ori de câte ori este posibil. Dacă acest lucru nu este posibil, atunci cel puțin încercați să utilizați coloane cu un conținut de valoare unică foarte mare.

Cum este stocat tabelul dacă nu a fost creat un index cluster?

SQL Server acceptă două tipuri de tabele: tabele grupate care au un index grupat și tabele heap sau doar heaps. Spre deosebire de tabelele grupate, datele din heap nu sunt sortate în niciun fel. În esență, acesta este o grămadă de date. Dacă adăugați un rând la un astfel de tabel, motorul de stocare îl va adăuga pur și simplu la sfârșitul paginii. Când pagina este plină cu date, aceasta va fi adăugată la o pagină nouă. În cele mai multe cazuri, veți dori să creați un index grupat pe un tabel pentru a profita de sortarea și viteza de interogare (încercați să vă imaginați să căutați un număr de telefon într-o agendă de adrese nesortată). Cu toate acestea, dacă alegeți să nu creați un index cluster, puteți crea în continuare un index nonclustered pe heap. În acest caz, fiecare rând index va avea un pointer către un rând heap. Indexul include ID-ul fișierului, numărul paginii și numărul liniei de date.

Care este relația dintre constrângerile unicității valorii și o cheie primară cu indici de tabel?

O cheie primară și o constrângere unică asigură că valorile dintr-o coloană sunt unice. Puteți crea doar o cheie primară pentru un tabel și nu poate conține valori NUL. Puteți crea mai multe restricții cu privire la unicitatea unei valori pentru un tabel și fiecare dintre ele poate avea o singură înregistrare cu NUL.
Când creați o cheie primară, motorul de stocare creează, de asemenea, un index cluster unic dacă nu a fost deja creat un index cluster. Cu toate acestea, puteți suprascrie comportamentul implicit și va fi creat un index non-cluster. Dacă există un index grupat atunci când creați cheia primară, va fi creat un index unic nonclustrat.
Când creați o constrângere unică, motorul de stocare creează un index unic, necluster. Cu toate acestea, puteți specifica crearea unui index cluster unic dacă unul nu a fost creat anterior.
În general, o constrângere de valoare unică și un indice unic sunt același lucru.

De ce indexurile grupate și non-cluster sunt numiți B-tree în SQL Server?

Indecșii de bază din SQL Server, în cluster sau necluster, sunt distribuiți în seturi de pagini numite noduri de index. Aceste pagini sunt organizate într-o ierarhie specifică cu o structură arborescentă numită arbore echilibrat. La nivelul superior se află nodul rădăcină, în partea de jos sunt nodurile frunzelor, cu noduri intermediare între nivelurile de sus și de jos, așa cum se arată în figură:


Nodul rădăcină oferă punctul de intrare principal pentru interogările care încearcă să recupereze date prin index. Pornind de la acest nod, motorul de interogare inițiază o navigare în jos în structura ierarhică până la nodul frunză corespunzător care conține datele.
De exemplu, imaginați-vă că a fost primită o solicitare de selectare a rândurilor care conțin o valoare cheie de 82. Subsistemul de interogare începe să funcționeze de la nodul rădăcină, care se referă la un nod intermediar potrivit, în cazul nostru 1-100. De la nodul intermediar 1-100 există o tranziție la nodul 51-100, iar de acolo la nodul final 76-100. Dacă acesta este un index grupat, atunci frunza nodului conține datele rândului asociat cheii egale cu 82. Dacă acesta este un index negrupat, atunci frunza index conține un pointer către tabelul grupat sau un anumit rând din grămada.

Cum poate un index chiar să îmbunătățească performanța interogării dacă trebuie să traversați toate aceste noduri de index?

În primul rând, indicii nu îmbunătățesc întotdeauna performanța. Prea mulți indecși creați incorect transformă sistemul într-un mocir și degradează performanța interogărilor. Este mai corect să spunem că, dacă indicii sunt aplicați cu atenție, aceștia pot oferi câștiguri semnificative de performanță.
Gândiți-vă la o carte uriașă dedicată reglajului performanței SQL Server(versiune pe hârtie, nu versiune electronică). Imaginați-vă că doriți să găsiți informații despre configurarea Resource Governor. Puteți trage cu degetul pagină cu pagină prin întreaga carte, sau deschideți cuprinsul și aflați numărul exact al paginii cu informațiile pe care le căutați (cu condiția ca cartea să fie indexată corect și conținutul să aibă indecșii corecti). Acest lucru cu siguranță vă va economisi timp semnificativ, chiar dacă mai întâi trebuie să accesați o structură complet diferită (indexul) pentru a obține informațiile de care aveți nevoie din structura primară (carte).
Ca un index de carte, un index în SQL Server vă permite să executați interogări precise asupra datelor de care aveți nevoie în loc să scanați complet toate datele conținute într-un tabel. Pentru tabelele mici, o scanare completă nu este de obicei o problemă, dar tabelele mari ocupă multe pagini de date, ceea ce poate duce la un timp semnificativ de execuție a interogării, cu excepția cazului în care există un index pentru a permite motorului de interogare să obțină imediat locația corectă a datelor. Imaginați-vă că vă pierdeți la o intersecție rutieră cu mai multe niveluri în fața unei metropole importante fără o hartă și veți înțelege ideea.

Dacă indexurile sunt atât de grozave, de ce să nu creați unul pe fiecare coloană?

Nicio faptă bună nu trebuie să rămână nepedepsită. Cel puțin așa este în cazul indicilor. Desigur, indecșii funcționează excelent atâta timp cât rulați interogări de preluare a operatorului SELECTAȚI, dar de îndată ce încep apelurile frecvente către operatori INTRODUCE, ACTUALIZAȚIȘi ȘTERGE, așa că peisajul se schimbă foarte repede.
Când inițiați o solicitare de date de către operator SELECTAȚI, motorul de interogare găsește indexul, se deplasează prin structura sa arborescentă și descoperă datele pe care le caută. Ce poate fi mai simplu? Dar lucrurile se schimbă dacă inițiezi o declarație de modificare cum ar fi ACTUALIZAȚI. Da, pentru prima parte a declarației, motorul de interogare poate folosi din nou indexul pentru a localiza rândul care este modificat - aceasta este o veste bună. Și dacă există o simplă modificare a datelor într-un rând care nu afectează modificările în coloanele cheie, atunci procesul de modificare va fi complet nedureros. Dar dacă modificarea face ca paginile care conțin datele să fie împărțite sau valoarea unei coloane cheie este schimbată, ceea ce face ca aceasta să fie mutată într-un alt nod de index - acest lucru va avea ca rezultat ca indexul să aibă nevoie de o reorganizare care afectează toți indecșii și operațiunile asociate. , ducând la o scădere pe scară largă a productivității.
Procese similare apar la apelarea unui operator ȘTERGE. Un index poate ajuta la localizarea datelor care sunt șterse, dar ștergerea datelor în sine poate duce la remanierea paginii. Referitor la operator INTRODUCE, principalul inamic al tuturor indicilor: începi să adaugi o cantitate mare de date, ceea ce duce la modificări ale indicilor și reorganizarea acestora și toată lumea are de suferit.
Deci, luați în considerare tipurile de interogări către baza de date atunci când vă gândiți ce tip de indici și câți să creați. Mai mult nu înseamnă mai bine. Înainte de a adăuga un nou index la un tabel, luați în considerare costul nu numai al interogărilor subiacente, ci și al cantității de spațiu pe disc consumat, costul menținerii funcționalității și indicilor, care poate duce la un efect de domino asupra altor operațiuni. Strategia dumneavoastră de proiectare a indexului este unul dintre cele mai importante aspecte ale implementării dumneavoastră și ar trebui să includă multe considerații, de la dimensiunea indexului, numărul de valori unice până la tipul de interogări pe care indexul le va accepta.

Este necesar să se creeze un index grupat pe o coloană cu o cheie primară?

Puteți crea un index grupat pe orice coloană care îndeplinește condițiile necesare. Este adevărat că un index grupat și o constrângere a cheii primare sunt făcute unul pentru celălalt și sunt o potrivire făcută în rai, așa că înțelegeți faptul că atunci când creați o cheie primară, atunci un index grupat va fi creat automat dacă nu a fost creat unul. creat înainte. Cu toate acestea, puteți decide că un index grupat ar avea rezultate mai bune în altă parte și adesea decizia dvs. va fi justificată.
Scopul principal al unui index grupat este de a sorta toate rândurile din tabel pe baza coloanei cheie specificate la definirea indexului. Acest lucru oferă căutare rapidă și acces ușor la datele din tabel.
Cheia primară a unui tabel poate fi o alegere bună, deoarece identifică în mod unic fiecare rând din tabele fără a fi nevoie să adăugați date suplimentare. În unele cazuri, cea mai bună alegere va fi o cheie primară surogat, care nu este doar unică, ci și de dimensiuni reduse și ale cărei valori cresc secvențial, făcând indici nonclustered bazați pe această valoare mai eficienți. Optimizatorul de interogări îi place, de asemenea, această combinație de index grupat și cheie primară, deoarece unirea tabelelor este mai rapidă decât alăturarea într-un alt mod care nu utilizează o cheie primară și indexul grupat asociat. După cum am spus, este un meci făcut în rai.
În cele din urmă, totuși, este de remarcat faptul că atunci când se creează un index grupat, există mai multe aspecte de luat în considerare: câți indici non-cluster se vor baza pe acesta, cât de des se va schimba valoarea coloanei indexului cheie și cât de mare. Când valorile din coloanele unui index grupat se modifică sau indexul nu funcționează conform așteptărilor, atunci toți ceilalți indici de pe tabel pot fi afectați. Un index grupat ar trebui să se bazeze pe cea mai persistentă coloană ale cărei valori cresc într-o anumită ordine, dar nu se modifică în mod aleatoriu. Indexul trebuie să accepte interogări față de datele cel mai frecvent accesate ale tabelului, astfel încât interogările profită din plin de faptul că datele sunt sortate și accesibile la nodurile rădăcină, frunzele indexului. Dacă cheia primară se potrivește acestui scenariu, atunci utilizați-o. Dacă nu, atunci alegeți un alt set de coloane.

Ce se întâmplă dacă indexați o vizualizare, este încă o vizualizare?

O vizualizare este un tabel virtual care generează date din unul sau mai multe tabele. În esență, este o interogare numită care preia date din tabelele subiacente atunci când interogați acea vizualizare. Puteți îmbunătăți performanța interogărilor prin crearea unui index grupat și a indecșilor nonclustered în această vizualizare, similar modului în care creați indecși într-un tabel, dar principalul avertisment este că mai întâi creați un index grupat și apoi puteți crea unul nonclustered.
Când este creată o vedere indexată (vizualizare materializată), atunci definiția vederii în sine rămâne o entitate separată. Acesta este, până la urmă, doar un operator codificat SELECTAȚI, stocat în baza de date. Dar indexul este o cu totul altă poveste. Când creați un index cluster sau nonclustered pe un furnizor, datele sunt salvate fizic pe disc, la fel ca un index obișnuit. În plus, atunci când datele se modifică în tabelele de bază, indexul vizualizării se schimbă automat (aceasta înseamnă că este posibil să doriți să evitați indexarea vizualizărilor pe tabelele care se modifică frecvent). În orice caz, vizualizarea rămâne o vedere - o vedere a tabelelor, dar una executată în momentul de față, cu indecși corespunzători acesteia.
Înainte de a putea crea un index pe o vizualizare, acesta trebuie să îndeplinească mai multe constrângeri. De exemplu, o vizualizare poate face referire numai la tabele de bază, dar nu și la alte vederi, iar acele tabele trebuie să fie în aceeași bază de date. Există de fapt multe alte restricții, așa că asigurați-vă că verificați documentația SQL Server pentru toate detaliile murdare.

De ce să folosiți un indice de acoperire în loc de un index compus?

În primul rând, să ne asigurăm că înțelegem diferența dintre cele două. Un index compus este pur și simplu un index obișnuit care conține mai mult de o coloană. Pot fi folosite mai multe coloane de cheie pentru a vă asigura că fiecare rând dintr-un tabel este unic sau este posibil să aveți mai multe coloane pentru a vă asigura că cheia primară este unică sau este posibil să încercați să optimizați execuția interogărilor invocate frecvent pe mai multe coloane. În general, totuși, cu cât un index conține mai multe coloane cheie, cu atât indexul va fi mai puțin eficient, ceea ce înseamnă că indecșii compoziți trebuie utilizați în mod judicios.
După cum sa menționat, o interogare poate beneficia foarte mult dacă toate datele necesare sunt imediat localizate pe frunzele indexului, la fel ca indexul însuși. Aceasta nu este o problemă pentru un index cluster deoarece toate datele sunt deja acolo (de aceea este atât de important să vă gândiți cu atenție când creați un index grupat). Dar un index negrupat pe frunze conține doar coloane cheie. Pentru a accesa toate celelalte date, optimizatorul de interogări necesită pași suplimentari, care pot adăuga o suprasarcină semnificativă la executarea interogărilor.
Aici indicele de acoperire vine în ajutor. Când definiți un index neclustrat, puteți specifica coloane suplimentare pentru coloanele cheie. De exemplu, să presupunem că aplicația dvs. interogează frecvent datele coloanei Comanda IDȘi Data comandă in masa Vânzări:
SELECT ID comandă, Data comandă FROM Vânzări WHERE ID comandă = 12345;
Puteți crea un index compus non-cluster pe ambele coloane, dar coloana OrderDate va adăuga doar costul general de întreținere a indexului, fără a servi ca o coloană cheie deosebit de utilă. Cea mai bună soluție ar fi crearea unui index de acoperire pe coloana cheie Comanda IDși coloană inclusă în plus Data comandă:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
Acest lucru evită dezavantajele indexării coloanelor redundante, menținând în același timp beneficiile stocării datelor în frunze atunci când rulează interogări. Coloana inclusă nu face parte din cheie, dar datele sunt stocate pe nodul frunză, frunza index. Acest lucru poate îmbunătăți performanța interogărilor fără nicio suprasolicitare suplimentară. În plus, coloanele incluse în indexul de acoperire sunt supuse la mai puține restricții decât coloanele cheie ale indexului.

Numărul de duplicate dintr-o coloană cheie contează?

Când creați un index, trebuie să încercați să reduceți numărul de duplicate din coloanele cheie. Sau mai precis: incearca sa pastrezi rata de repetare cat mai scazuta.
Dacă lucrați cu un index compus, atunci duplicarea se aplică tuturor coloanelor cheie în ansamblu. O singură coloană poate conține multe valori duplicate, dar ar trebui să existe o repetiție minimă între toate coloanele index. De exemplu, creați un index compus nonclustered pe coloane NumeȘi Nume, puteți avea multe valori John Doe și multe valori Doe, dar doriți să aveți cât mai puține valori John Doe posibil, sau de preferință doar o singură valoare John Doe.
Raportul de unicitate al valorilor unei coloane cheie se numește selectivitate index. Cu cât sunt mai multe valori unice, cu atât selectivitatea este mai mare: un indice unic are cea mai mare selectivitate posibilă. Motorului de interogări îi plac foarte mult coloanele cu valori de selectivitate ridicate, mai ales dacă acele coloane sunt incluse în clauzele WHERE ale interogărilor executate cel mai frecvent. Cu cât indexul este mai selectiv, cu atât mai rapid motorul de interogare poate reduce dimensiunea setului de date rezultat. Dezavantajul, desigur, este că coloanele cu relativ puține valori unice vor fi rareori candidați buni pentru indexare.

Este posibil să se creeze un index non-cluster doar pe un anumit subset al datelor unei coloane cheie?

În mod implicit, un index nonclustered conține câte un rând pentru fiecare rând din tabel. Desigur, puteți spune același lucru despre un index grupat, presupunând că un astfel de index este un tabel. Dar când vine vorba de un index non-clustered, relația unu-la-unu este un concept important deoarece, începând cu versiunea SQL Server 2008, aveți opțiunea de a crea un index filtrabil care limitează rândurile incluse în acesta. Un index filtrat poate îmbunătăți performanța interogării deoarece... are dimensiuni mai mici și conține statistici filtrate, mai precise decât toate cele tabelare - acest lucru duce la crearea unor planuri de execuție îmbunătățite. Un index filtrat necesită, de asemenea, mai puțin spațiu de stocare și costuri de întreținere mai mici. Indexul este actualizat numai atunci când datele care corespund filtrului se modifică.
În plus, un index filtrabil este ușor de creat. În operator CREAȚI INDEX trebuie doar să indicați în UNDE starea filtrului. De exemplu, puteți filtra toate rândurile care conțin NULL din index, așa cum se arată în cod:
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber NU ESTE NULL;
Putem, de fapt, să filtram orice date care nu sunt importante în interogările critice. Dar ai grijă, pentru că... SQL Server impune mai multe restricții asupra indecșilor filtrabili, cum ar fi incapacitatea de a crea un index filtrabil pe o vizualizare, deci citiți cu atenție documentația.
De asemenea, se poate obține rezultate similare prin crearea unei vizualizări indexate. Cu toate acestea, un index filtrat are mai multe avantaje, cum ar fi capacitatea de a reduce costurile de întreținere și de a îmbunătăți calitatea planurilor dumneavoastră de execuție. Indexurile filtrate pot fi reconstruite și online. Încercați acest lucru cu o vizualizare indexată.

Și din nou puțin de la traducător

Scopul apariției acestei traduceri pe paginile lui Habrahabr a fost de a vă spune sau de a vă aminti despre blogul SimpleTalk de la RedGate.
Publică multe postări distractive și interesante.
Nu sunt afiliat cu niciun produs al companiei RedGate, nici cu vânzarea lor.

După cum am promis, cărți pentru cei care vor să afle mai multe
Recomand trei cărți foarte bune de la mine (linkurile duc la aprinde versiuni în magazin Amazon):

În principiu, puteți deschide indecși simpli

pentru incepatori

index

Adaugă etichete

Noțiuni fundamentale pentru Microsoft SQL Server 2012 T-SQL (referință pentru dezvoltatori) Autorul Itzik Ben-Gan Data publicării: 15 iulie 2012 Autorul, un maestru al meșteșugului său, oferă cunoștințe de bază despre lucrul cu bazele de date. Dacă ai uitat totul sau nu ai știut niciodată, cu siguranță merită citit.

Indici ROWID sunt obiecte de bază de date care oferă o afișare a tuturor valorilor dintr-o coloană de tabel, precum și ROWID-urile tuturor rândurilor din tabel care conțin valorile coloanei.

RÂNDUL este o pseudo-coloană care este un identificator unic pentru un rând dintr-un tabel și descrie de fapt locația fizică exactă a acelui rând. Pe baza acestor informații Oracol poate găsi ulterior datele asociate cu rândul tabelului. De fiecare dată când un rând este mutat, exportat, importat sau orice altă operațiune care își schimbă locația, RÂNDUL linie deoarece ocupă o poziție fizică diferită. Pentru stocarea datelor RÂNDUL Sunt necesari 80 de biți (10 octeți). Identificatori RÂNDUL constau din patru componente: numărul obiectului (32 biți), numărul relativ al fișierului (10 biți), numărul blocului (22 biți) și numărul liniei (16 biți). Acești identificatori sunt afișați ca secvențe de 18 caractere indicând locația datelor în baza de date, fiecare caracter reprezentat în format de bază 64 constând din caracterele A-Z, a-z, 0-9, + și /. Primele șase caractere sunt numărul obiectului de date, următoarele trei sunt numărul relativ al fișierului, următoarele șase sunt numărul blocului și ultimele trei sunt numărul liniei.

Exemplu:

SELECT familia, RÂNDUL DIN student;

FAM RÂNDUL

——————————————

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

În baza de date Oracol indecșii sunt utilizați în diferite scopuri: pentru a asigura unicitatea valorilor în baza de date, pentru a îmbunătăți performanța căutării înregistrărilor într-un tabel etc. Performanța este îmbunătățită prin includerea unei referințe la coloana sau coloanele indexate în criteriile de căutare pentru datele din tabel. ÎN Oracol indecșii pot fi creați pe orice coloană de tabel, cu excepția coloanelor LONGE. Indicii fac diferența între aplicațiile insensibile la viteză și aplicațiile de înaltă performanță, în special atunci când lucrați cu mese mari. Cu toate acestea, înainte de a decide să creați un index, trebuie să cântăriți argumentele pro și contra în ceea ce privește performanța sistemului. Performanța nu se va îmbunătăți dacă pur și simplu introduceți un index și uitați de el.

Deși cea mai mare îmbunătățire a performanței vine din crearea unui index pe o coloană în care toate valorile sunt unice, puteți obține rezultate similare pentru coloanele care conțin valori duplicate sau NULL. Nu este necesar ca valorile coloanei să fie unice pentru a crea un index. Iată câteva recomandări pentru a vă ajuta să obțineți creșterea dorită a performanței atunci când utilizați un index standard și vom analiza, de asemenea, problemele legate de echilibrul dintre performanță și consumul de spațiu pe disc la crearea unui index.

Utilizarea indecșilor pentru a căuta informații în tabele poate oferi îmbunătățiri semnificative de performanță față de scanarea tabelelor ale căror coloane nu sunt indexate. Cu toate acestea, alegerea indicelui potrivit nu este deloc ușoară. Desigur, o coloană ale cărei valori sunt toate unice este de preferat pentru indexarea cu un index B-tree, dar o coloană care nu îndeplinește aceste cerințe este un bun candidat atâta timp cât aproximativ 10% din rândurile sale conțin valori identice si nu mai mult. Coloanele „Switch” sau „flag”, de exemplu cele care stochează informații despre sexul unei persoane, nu sunt potrivite pentru indici B-tree. Coloanele care sunt folosite pentru a stoca un număr mic de „valori de încredere”, precum și cele care stochează anumite valori, de asemenea, nu sunt potrivite, apoi semnele, de exemplu, „fiabilitate” sau „nefiabilitate”, „activitate” sau „inactivitate”, „da” sau „nu”, etc., etc. În cele din urmă, indicii cu taste inverse sunt folosit, de regulă, acolo unde este instalat și funcționează Oracol Parallel Server și trebuie să creșteți nivelul de paralelism în baza de date la maximum.