Indici SQL Server - cheia pentru performanța optimă a bazei de date

Majoritatea aplicațiilor serioase de afaceri folosesc în continuare sisteme de baze de date relaționale, cum ar fi SQL Server, pentru stocarea datelor. Pentru ca datele stocate acolo să poată fi accesate eficient, indexurile sunt un factor foarte decisiv - de la proiectarea bazei de date până la interogare. Cu toate acestea, MS SQL Server oferă acum o gamă întreagă de forme de index diferite: Pe lângă indexurile clasice grupate și ne-grupate, există indexuri pentru domenii speciale de aplicație, cum ar fi indexuri full-text, indexuri XML și indexuri spațiale. În plus, au fost adăugați mai târziu indicii magazinului de coloane proiectat inițial pentru depozite de date, care cel târziu în a treia generație pot fi utilizați eficient și pentru alte domenii.

General cum funcționează indicii

Pentru a înțelege mai bine cum funcționează indexurile, este util să înțelegeți cum SQL Server stochează datele pe disc. Datele sunt stocate în fișiere cu extensia .mdf sau .ndf. Acestea sunt împărțite în pagini de memorie cu o dimensiune de 8 KB fiecare, care este, de asemenea, cea mai mică unitate în care SQL Server citește sau scrie date. Opt pagini de memorie succesive formează fiecare așa-numita extensie. În timp ce o pagină de memorie conține doar date dintr-un tabel, extensiile sunt atât uniforme, cât și mixte, care conțin pagini de memorie din tabele diferite. De exemplu, dacă o interogare citește toate persoanele cu un anumit nume de familie dintr-un tabel care nu are un index, trebuie să citească toate paginile de memorie care conțin date pe acel tabel.

Pentru a putea executa aceste interogări mai eficient, sunt utile structurile corespunzătoare, care asigură că paginile de memorie relevante pot fi găsite mai repede și, astfel, nu toate paginile de memorie pot fi citite. Exact asta fac indicii.

Indici grupați

În cel mai simplu caz, tabelul în sine este sortat în funcție de o coloană specifică (în cazul tabelului persoană, de exemplu, în funcție de numele de familie). Exact ceea ce face un index grupat. Cu toate acestea, acest lucru nu se face sub forma unei liste simple, ci sub forma unui arbore binar, în care fiecare nod al arborelui folosește propria sa pagină de memorie care conține o listă de nume sortate alfabetic, completată de o referință la pagina de memorie în care căutați acest nume (sau numele care îl urmează alfabetic). Acest lucru poate continua - în funcție de numărul de înregistrări de date din tabel - în mai multe etape până când în cele din urmă se face o referire la pagina de memorie în care sunt stocate datele personale complete. Aceasta înseamnă că trebuie citite doar câteva pagini de date înainte de găsirea rândului (tabelelor) corespunzător.

În timp ce un nume de familie poate apărea cu siguranță de mai multe ori într-un tabel de persoane, în practică se folosește de obicei o coloană unică pentru un index grupat, care deseori corespunde cheii principale a tabelului, adică cheia cu care toate rândurile unui tabel pot fi identificate clar. Prin urmare, atunci când creați o cheie primară, este generat implicit un index grupat, cu excepția cazului în care cuvântul cheie NONCLUSTERED este specificat în mod explicit (Listarea 1).

Cea mai comună variantă pentru un index grupat este o coloană Id de tip int (sau bigint pentru tabele mari) cu o specificație de identitate care asigură că coloanei i se atribuie automat numere unice. Ca alternativă, sunt adesea utilizate coloane de tip unic identificator, care sunt, de asemenea, furnizate cu valori unice - dar nu continue - prin funcția NewId () ca o constrângere implicită. Acest lucru este deosebit de util în mediile distribuite, unde se creează rânduri noi în diferite copii ale bazei de date care sunt apoi îmbinate ulterior fără a suprapune cheile principale.
Un dezavantaj major al indexurilor grupate este că acestea pot fi sortate doar într-o singură ordine. O interogare care caută pe altă coloană (cum ar fi prenumele) nu va beneficia de un index pe coloana Nume. Pentru a rezolva acest lucru, se utilizează n indexuri grupate.

Afișarea planurilor de execuție

Pentru a verifica utilizarea indexurilor în interogări, SQL Server Management Studio oferă posibilitatea de a afișa atât planurile de execuție așteptate, cât și cele reale ale interogărilor. Ambele opțiuni pot fi activate cel mai ușor prin opțiunile corespunzătoare din meniul de interogare sau alternativ prin bara de instrumente. Trebuie remarcat faptul că afișarea planului de execuție anticipat nu execută interogarea în sine, în timp ce planul de execuție propriu-zis poate fi afișat numai după ce interogarea a fost executată. În planurile de execuție afișate grafic, care pot fi citite din dreapta sus, puteți vedea rapid dacă (și cum) se accesează un index sau tabelul în sine. Cu „cum”, trebuie menționate cuvintele cheie „scanare” și „căutare”. O căutare a indexului înseamnă că rândurile relevante sunt găsite printr-o căutare binară în arborele indexului, în timp ce o scanare indică faptul că tabelul sau indexul au fost citite complet, ceea ce, în majoritatea cazurilor, consumă mult mai mult timp. Informații mai detaliate despre citirea planurilor de implementare pot fi găsite în referințele acestui articol [1], [2], [3].

Indici non-grupați

Indicii ne-grupați reprezintă o structură de date suplimentară care este gestionată și într-o structură de copac, dar ale cărei frunze nu conțin datele de linie reale, ci o referință la adresa datelor. Pur și simplu, un indice ne-grupat se comportă ca un index la sfârșitul unei cărți: este sortat în sine, astfel încât să puteți căuta rapid un anumit termen. Odată ce termenul a fost găsit, acesta este urmat doar de numărul paginii unde poate fi găsit termenul. O altă modificare a paginii relevante a cărții este necesară pentru a găsi informațiile dorite. Cu indexul bazei de date, referința la datele reale este, desigur, puțin mai complexă decât un simplu număr de pagină. Acest proces, cunoscut sub numele de căutare rând, folosește un identificator de rând (RID pe scurt), care constă din trei părți:

Numărul fișierului bazei de date (deoarece un tabel poate fi împărțit în mai multe fișiere)
Numărul paginii de memorie
Numărul înregistrării de date de pe această pagină de memorie

Cu toate acestea, acest lucru se aplică numai dacă tabelul nu are un index grupat și, prin urmare, este stocat ca un „heap” neordonat. Dacă, pe de altă parte, există un index grupat, valoarea cheie a indexului grupat este specificată ca o referință la nivelul frunzei a indexului non-grupat. Trebuie parcurs un alt arbore de index înainte ca datele reale să poată fi găsite. (Cu toate acestea, efortul suplimentar pentru această așa-numită căutare cheie este de obicei destul de redus.)

Fig. 1: Arborele indexului pentru un tabel fără un index grupat

Un mare avantaj al indexurilor ne-grupate este că pot exista mai multe dintre ele pentru un tabel, care sunt sortate în funcție de coloane diferite. Aceasta înseamnă că puteți căuta apoi diverse criterii cu performanțe ridicate (Fig. 1).

Deoarece indicii care nu sunt grupați sunt structuri de date suplimentare, trebuie să fim întotdeauna conștienți de faptul că, pe de o parte, necesită memorie suplimentară și, pe de altă parte, de fiecare dată când datele în sine sunt modificate, toți indicii în care sunt conținute coloanele modificate trebuie actualizate. Dacă este posibil, nu ar trebui să creați un index separat pentru fiecare coloană a unui tabel, ci să cântăriți întotdeauna pentru ce coloane are sens un index și unde poate fi renunțat. Ca regulă de bază, vă puteți aminti că indicii suplimentari pot accelera accesele de citire, dar pot încetini accesele la scriere. Decizia pentru care coloane are sens un index poate fi luată pe baza următoarelor criterii:

Dacă coloana este căutată sau filtrată des, aceasta vorbește pentru un index.
Același lucru se aplică coloanelor care sunt folosite ca coloane cu cheie străină, deoarece acestea sunt filtrate în timpul unui JOIN.
Dacă coloana conține doar câteva valori diferite (se vorbește și despre selectivitate scăzută), aceasta se referă mai degrabă la un index pe ea. Acest lucru este valabil mai ales pentru coloanele care utilizează tipul de date pe biți.
Dacă tabelul este citit în cea mai mare parte și se modifică foarte rar, sunt acceptabili mai mulți indici.
Dacă tabelul este scris în primul rând (de exemplu, un tabel jurnal) și este citit foarte rar, numărul de indici ar trebui menținut cât mai mic posibil.

Indexuri combinate

O modalitate de a menține numărul de indici care urmează să fie gestionat scăzut, dar și de a face indexurile utilizabili cât mai eficient posibil, este de a utiliza indexuri combinate care sunt definite pe mai multe coloane. De exemplu, dacă există un index separat pentru coloanele prenume și prenume dintr-un tabel persoană, următoarea interogare cu greu va putea utiliza ambii indexuri:

SELECTA * DE LA Person.Person

UNDE prenume = „Ioan” ȘI prenume = „lemn”

În schimb, SQL Server va utiliza automat indexul de la care se așteaptă numărul mai mic de pagini care trebuie citite (indexul de pe coloana cu selectivitate mai mare). Cu toate acestea, deoarece pot exista mai multe intrări atât pentru prenume, cât și pentru nume, ar fi și mai eficient să existe un index care conține ambele coloane, adică un index combinat care poate fi creat după cum urmează:

CREAȚI INDICE NECLUSIONATE IX_Person_LastName_FirstName

ON Person.Person (Nume, Prenume)

În timp ce numele indexului (IX_Person_LastName_FirstName) este doar o convenție de denumire obișnuită, ordinea coloanelor din definiția indexului are o importanță decisivă. Punând numele de familie pe primul loc, indexul poate fi utilizat în mod eficient și atunci când o interogare caută doar numele de familie (deoarece acesta este criteriul principal de sortare pentru index). Dacă în schimb căutați doar un prenume, indexul poate fi, de asemenea, utilizat, dar nu prin accesul efectiv prin arborele indexului binar (Index Seek). În schimb, trebuie citit întregul index (scanarea indexului) pentru a găsi toate combinațiile posibile care conțin prenumele pe care îl căutați (ceea ce, în majoritatea cazurilor, este încă mai eficient decât citirea întregului tabel fără index).

Deoarece o căutare numai a prenumelui va avea loc cu siguranță mult mai puțin frecvent în practică decât o căutare a numelui de familie, se recomandă ordinea coloanelor selectate mai sus și are efectul secundar pe care îl puteți face fără indicii separați pentru coloanele prenume și prenume.

Acoperirea indexurilor și includ coloane

Când s-au explicat indexurile ne-grupate, s-a subliniat faptul că există referințe la înregistrările de date efective la nivelul frunzei arborelui index binar (fie sub forma cheii de index grupate, fie ca adresă cu mai multe părți, alcătuită din numărul fișierului, pagina de memorie și numărul liniei). În mod ideal, totuși, toate coloanele interogate sunt conținute în indexul în sine, deci nu este necesar să urmați această referință. Dacă următoarea interogare este executată cu un index combinat existent pe coloanele LastName și FirstName:

atunci indexul poate fi căutat folosind coloana LastName, dar conține și LastName interogat, astfel încât să poată fi omisă căutarea normal necesară pentru întreaga linie de date. Se vorbește apoi despre un indice de acoperire (în raport cu interogarea), deoarece acesta acoperă toate coloanele pentru interogare.

Pentru a utiliza avantajul unui index de acoperire cât mai des posibil fără a fi nevoie să reordonați indexul prea des, coloanele pot fi integrate în index așa-numitele include coloane. Aceasta înseamnă că valorile acestor coloane sunt conținute la nivelul frunzei arborelui index, dar nu sunt luate în considerare pentru sortarea indexului (prin urmare, este suficient dacă coloanele există doar la nivelul frunzei și nu pe nodurile de mai sus). Prin urmare, următorul index ar fi suficient și pentru interogarea afișată ultima dată:

Mai pot fi definite mai multe coloane de includere, a căror ordine este apoi irelevantă, deoarece nu sunt relevante pentru sortare. Dacă ar fi să includeți toate coloanele în index (fie pentru sortare, fie ca coloane de includere), ați fi simulat un index grupat, dar cu dezavantajul că spațiul pentru referința la linia de date în sine este apoi utilizat. ceea ce nu este necesar în această variantă, deoarece este complet conținut în index. Deci acest lucru nu este cu siguranță recomandat, mai ales că indicii pot fi utilizați mai eficient cu cât sunt mai mici.

Indexuri filtrate

De la SQL Server 2008, a existat o altă opțiune cu indexurile filtrate pentru a menține indexul cât mai compact posibil și astfel pentru a minimiza numărul de pagini de memorie care trebuie citite. Un index este completat de o clauză WHERE, astfel încât indexul trebuie creat numai pentru rândurile care îndeplinesc condiția specificată. În consecință, rezultă restricția că condițiile de filtrare pot fi utilizate numai în combinație cu indici ne-grupați.

Un caz de utilizare tipic pentru indexurile filtrate sunt tabelele cu coloane care nu sunt completate într-o mare parte a rândurilor:

UNDE MiddleName NU ESTE NUL

Vizualizări indexate

De la versiunea 2005, SQL Server a oferit și opțiunea de a crea indici pe baza vizualizărilor. Aceasta înseamnă că datele solicitate de vizualizare sunt salvate redundant ca o copie, dar sortate în conformitate cu criteriile indexului. Prin urmare, acest concept este adesea menționat ca o viziune materializată (acest termen este deosebit de comun în mediul Oracle). Principalul avantaj al unei astfel de vizualizări indexate este că toate datele care trebuie solicitate sunt deja legate și se află în ordinea corectă de sortare. Astfel, interogarea trebuie doar să acceseze vizualizarea indexată fără a fuziona mai întâi datele din mai multe tabele folosind condițiile JOIN. De asemenea, nu există căutări suplimentare de rânduri sau chei deoarece - cu condiția ca vizualizarea să fie creată corespunzător - toate coloanele care trebuie interogate sunt conținute în vizualizare și, prin urmare, și în indexul de pe aceasta.

Cu toate acestea, pentru a putea utiliza vizualizări indexate, trebuie respectate câteva restricții:

Vizualizarea în sine trebuie creată cu opțiunea WITH SCHEMABINDING, care împiedică modificarea structurii de date a coloanelor utilizate în vizualizare.
Indexul din vizualizare trebuie creat ca INDICE CLUSTER UNIC.
Indexul nu trebuie filtrat, dar acest lucru nu înseamnă nicio restricție reală, deoarece un criteriu de filtrare poate fi ușor de adaptat în vizualizarea utilizată de index.

Listarea 2 arată cum poate fi creată o vizualizare indexată în doi pași. După ce vizualizarea a fost creată cu adăugarea SCHEMABINDING, pentru această vizualizare este creat INDEXUL UNIC CLUSTERED. Dacă efectuați acum o interogare simplă în vizualizare, puteți determina din planul de execuție că numai indexul în sine a fost citit și nu tabelele din spatele acestuia:

Un efect secundar interesant al vizualizărilor indexate este că acestea pot fi utilizate chiar dacă tabelele din spatele acestuia nu sunt abordate în mod explicit, ci mai degrabă vizualizarea. Acest lucru poate fi verificat cu ușurință examinând planul de execuție al următoarei interogări:

La crearea planului de execuție, SQL Server recunoaște automat că există o vizualizare indexată adecvată care poate fi accesată mai rapid decât tabelele și indexurile utilizate.

Concluzie

În prima parte a acestei serii de articole, au fost create elementele de bază și a fost oferită o prezentare generală a variantelor de index „clasice” disponibile de mult timp. Următoarea tranșă din această serie se va ocupa de menținerea indexurilor și câteva opțiuni mai avansate, cum ar fi compresia indexului.

Linkuri și literatură

[1] Panther, Robert: „Optimizarea interogărilor SQL”, entwickler.press, iunie 2014, ISBN: 978-3868021233

[2] Panther, Robert: „SQL Server Performance Ratgeber”, entwickler.press, februarie 2010, ISBN: 978-3868020304

[3] Fritchey, Grant: „SQL Server Execution Plans”, Simple Talk Publishing, octombrie 2012, ISBN: 978-smartblock>

Dezvoltator Windows

Acest articol a fost publicat în Windows Developer. Windows Developer oferă informații complete, neutre pentru producător, despre noile tendințe și posibilități în dezvoltarea de software și sisteme pentru tehnologiile Microsoft.