Gestionarea si utilizarea indecsilor

Gestionarea si utilizarea indecsilor

Una dintre cele mai importante responsabilitati ale proiectantului unei baze de date este sa defineasca tabelele (relatiile) astfel incat performantele sa fie optime. Structura de baza a unei tabele nu specifica modul in care datele urmeaza sa fie accesate sau stocate fizic, ci doar constrangerile impuse de tipurile de date si eventualele constrangeri asupra coloanelor.

SQL Server ofera un mecanism de indexare a tabelelor care faciliteaza optimizarea raspunsului la interogari. In lipsa indecsilor, SQL Server ar trebui sa efectueze baleieri ale tabelei, adica sa citeasca fiecare linie a unei tabele pentru a raspunde la o interogare. In cazul tabelelor foarte mari, aceasta abordare este in mod evident una costisitoare pentru server. Indecsii reprezinta o modalitate prin care SQL Server organizeaza pointeri la datele necesare.

Un index dintr-o baza de date reprezinta o lista de valori semnificative impreuna cu referinte la intrarile din tabela bazei de date in care se afla informatiile corespunzatoare. Astfel, este posibila citirea unui numar in general mai mic de intrari de index, intrari care indica datele ce satisfac cererea efectuata.

Indecsii reprezinta metoda interna prin care SQL Server organizeaza datele din tabela, astfel incat acestea sa poata fi regasite in mod optim. Optim, in acest caz, se refera la un consum minim de timp.

Un index reprezinta o multime de valori unice dintr-o tabela, impreuna cu pointeri corespunzatori catre intrarile de date in cadrul carora respectivele valori sunt stocate fizic in tabela. Un index este un obiect, ca oricare altul in baza de date, necesitand spatiu de alocare. Avantajul unui index este ca reduce numarul de operatii de I/O necesare pentru accesarea unei informatii dintr-o tabela.

Cand se creeaza un index in SQL Server, acesta baleiaza tabela in cauza, aduna valorile din coloana (coloanele) de indexat si apoi genereaza o lista cu pagini de date si identificatori de linie, care pun in corespondenta intrarile dintr-un index, cu valorile indexate. Astfel, serverul poate sa se rezume la baleierea unei liste cu intrari de index, fara a mai baleia o intreaga tabela pentru a gasi datele cautate.

Indecsii creati vor fi legati in general de datele pe care aplicatiile le acceseaza in mod uzual. Aceste extrageri de date din tabele joaca un rol principal in alegerea coloanelor de indexat. Nu trebuie sa avem in vedere doar regasirea datelor, ci si operatiile de inserare si modificare a acestora. Efectul major apare la actualizarea indecsilor. Daca un sistem efectueaza multe inserari, este bine sa se restranga indecsii activi din tabelele bazei de date. Inserarile si modificarile de inregistrari implica actualizarea indecsilor. In general, numarul magic pentru astfel de sisteme este 5 indecsi. Cu cat se depaseste acest numar, cu atat se inregistreaza scaderi mai dramatice ale performantelor. Pe de alta parte, daca exista un sistem orientat spre interogari in care se efectueaza putine inserari si care se concentreaza mai ales asupra analizarii informatiilor existente, se pot implementa oricati indecsi, pentru a spori performantele interogarilor.

Atunci cand se creeaza un index trebuie sa stabilim ce anume urmarim cu indexul respectiv, adica sa intelegem ce tip de interogari vor fi efectuate asupra bazei de date. Trebuie sa anticipam ce coloane vor fi specificate in clauzele WHERE ale instructiunilor SELECT.

La crearea unui index trebuie specificate campurile pe care le va cuprinde. SQL Server ia in calcul utilizarea unui index numai daca in instructiunea SELECT se specifica prima coloana a indexului. In caz contrar, indexul este pur si simplu ignorat.

De exemplu, daca intr-o baza de date avem un index dupa codul postal si dupa judet, in aceasta ordine, la cautarea dupa judet a unei inregistrari in baza de date nu va fi utilizat indexul respectiv. SQL Server va cauta o alta cale de a extrage liniile care satisfac cererea adresata. In functie de mai multi factori, aceasta poate insemna o baleiere de tabela, adica inamicul performantelor. Pe de alta parte, o instructiune de interogare pe baza codului postal va beneficia de ajutorul indexului. Aceasta nu tine cont in nici un fel de includerea judetului in cadrul indexului.

SQL Server ia decizii privind indecsii de utilizat in executia unei interogari, pe baza unor date, care sintetizeaza distributia informatiilor din tabele. SQL Server foloseste aceste informatii de sinteza pentru a hotari daca metoda cea mai buna si rapida de executie a unei interogari este o baleiere de tabela, sau utilizarea unor indecsi. Atunci cand se creaza o tabela cu date, informatiile de sinteza sunt create automat. Daca pe de alta parte se creaza o tabela si se adauga datele ulterior, informatiile de sinteza nu vor fi actualizate. In acest caz, precum si de fiecare data cand adaugati, modificati sau stergeti 10 - 20% din informatiile unei tabele, este necesara actualizarea explicita a acestor informatii. Aceasta se face prin executia unei comenzi Update Statistics.

Aceasta comanda nu are niciodata efecte distructive si poate sa imbunatateasca substantial performantele.

Nu este posibila crearea unui index dupa o coloana de tip Bit, Text si Image.

Dimensiunea maxima pentru un index este de 900 octeti, astfel ca utilizarea coloanelor mari de tip char, varchar, nchar, nvarchar, binary sau varbinary nu este permisa.

Regulile generale, bune de urmat, in cautarea unor buni candidati pentru un index sunt:

cheile straine reprezinta candidati de baza pentru indecsi, ca si cheile primare;

in cazul interogarilor care intorc o multime rezultat mare, este bine sa indexati pe cat de mult posibil;

indexarea dupa coloanele specificate in clauzele Order By si Group By aduce avantaje mari.

SQL Server 7 a adus imbunatatiri considerabile in prelucrarea interogarilor si utilizarea indecsilor. Una dintre cele mai importante imbunatatiri este capacitatea de utilizare a mai multor indecsi pentru o tabela, in cadrul unei interogari. Impactul asupra performantelor la cautarea datelor este substantial, pentru ca baleierile sunt practic excluse.

Noul mod de operare complica sarcina proiectantului unei baze de date, in ceea ce priveste alegerea indecsilor de creat. Se pune problema daca este mai bine sa se creeze un numar mai mic de indecsi compusi, sau este de preferat un numar mai mare de indecsi dupa o singura coloana. In cazul acelor indecsi folositi pentru impunerea unicitatii, acolo unde sunt implicate mai multe linii este necesar un index compus.

SQL Server permite utilizarea mai multor indecsi pentru o tabela in cadrul unei interogari prin intermediul a doua tehnici:

q      intersectie de indecsi

q      reuniune de indecsi.

Fiecare linie dintr-o tabela SQL Server are asociat un identificator rowid. In cadrul unei interogari care foloseste un index, SQL Server colecteaza acesti identificatori din paginile de index. Odata ce a adunat toti identificatorii, datele sunt furnizate cu ajutorul acestor pointeri. Atunci cand se utilizeaza mai multi indecsi se foloseste aceeasi metoda, insa identificatorii sunt colectati acum din mai multi indecsi. Daca in interogare se foloseste operatorul AND se efectueaza o intersectie de indecsi. Multimea rezultat este generata pe baza identificatorilor care formeaza intersectia seturilor adunate de identificatori. Asta inseamna ca se folosesc doar identificatorii gasiti de fiecare dintre indecsi. Daca in interogare se foloseste operatorul OR, se efectueaza o reuniune de indecsi. Aceasta inseamna ca se aduna identificatorii din toti indecsii si se furnizeaza clientului datele din toate liniile corespunzatoare.

Crearea indecsilor in Transcat-SQL se face cu comanda CREATE INDEX care are sintaxa de mai jos:

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]

INDEX nume_index ON Tabela (coloana[, . n])

[WITH

[Pad_Index]

[[, ] FILLFACTOR = factor_umplere]

[[, ] IGNORE_DUP_KEY]

[[, ] SORTED_DATA_REORG]

[[, ] ]

[[, ] DROP_EXISTING]

[[, ] STATISTICS_NORECOMPUTE]

[ON grup_fisiere]

Optiunile din comanda CREATE INDEX sunt explicate mai jos.

UNIQUE - daca un index este definit ca UNIQUE, SQL Server nu permite valori duplicat in index, deci poate sa impiedice un client sa insereze o inregistrare in cadrul tabelei de baza. Aceasta asigurare a integritatii unei tabele reprezinta cea mai uzuala folosire a indecsilor. Indecsii unici nu pot fi creati in tabelele care contin valori duplicat, in cadrul coloanelor indexate. Aceste valori trebuie mai intai eliminate.

Clustered - un index grupat (clustered) este un index special care determina SQL Server sa stocheze datele din tabela exact in ordinea din cadrul indexului. Folosirea unui index grupat pentru a realiza stocarea fizica a datelor dintr-o tabela, sub o forma particulara poate sa sporeasca foarte mult performantele la accesarea tabelei. Este bine sa se defineasca intotdeauna un index grupat pentru o tabela. Acesta va spori substantial performantele prin mentinerea unei baze de date intr-o stare ordonata. Mai mult, permite refolosirea spatiului eliberat prin stergerea liniilor. Pentru ca nu poate exista decat un singur index grupat intr-o tabela, trebuie sa fie alese cu grija coloana (coloanele) din compunerea acestui index, astfel incat sa se obtina un avantaj maxim. Coloanele in care cautam serii de valori constituie in general buni candidati pentru indecsii grupati.

Nonclustered - acesta este tipul implicit al unui index. In acest caz, SQL Server creeaza un index ale carui elemente reprezinta pointeri catre datele efective din tabela in cauza, a bazei de date. Pentru o tabela se pot crea pana la 249 de indecsi dispersati.

nume_index - numele indexului trebuie sa fie unic in baza de date.

tabela - tabela de indexat.

coloana - coloana indexata. In cazul in care se specifica mai multe coloane se creeaza un index compus, sau complex. Coloanele trebuie separate prin spatii. O cheie compusa poate cuprinde pana la 16 coloane, dar dimensiunea cumulata maxima a tipurilor acestor coloane nu trebuie sa depaseasca 900 octeti.

Pad_index - indica numarul de linii dintr-un nod intern al unui index. Nu pot fi niciodata mai putin de doua linii. Este utila daca se foloseste si optiunea FillFactor, deoarece foloseste acelasi procentaj ca si aceasta.

FillFactor = factor_umplere - se foloseste rar, pentru specificarea unui factor de umplere indica SQL Server modul de "impachetare" a datelor dintr-un index in pagini. Determina SQL Server sa rezerve spatiu in paginile unui index pentru linii noi, care se astepta sa contina valori cheie identice sau similare.

Ignore_dup_key - determina comportamentul SQL in ceea ce priveste posibilitatea de aparitie a unor valori duplicat intr-o tabela, pe care este definit un index unic. Implicit, SQL Server respinge intotdeauna valoarea duplicat si intoarce eroare. Optiunea de fata permite sa determine SQL Server sa continue operatia curenta fara eroare.

Sorted_Data - se foloseste pentru a accelera crearea indecsilor grupati. Prin specificarea acestei optiuni SQL Server este informat ca datele de indexat sunt deja ordonate fizic in ordinea data de index. Folosirea acestui cuvant cheie reduce foarte mult timpul si spatiul necesare pentru crearea unui index grupat, pentru ca SQL Server nu pierde timp cu ordonarea datelor.

Sorted_data_reorg - similara cu Sorted_Data prin aceea ca imbunatateste performantele generale ale SQL Server prin ordonarea fizica a datelor dintr-o tabela in concordanta cu ordinea din index. Determina SQL Server sa modifice ordinea datelor in conformitate cu ordinea din cadrul indexului. Se poate dovedi foarte util in cazul tabelelor indexate dispersat, atunci cand se doreste reducerea numarului de divizari de pagina datorate faptului ca datele nu se mai afla in pagini adiacente.

Ignore_dup_row - permite crearea unui index grupat unic. La specificarea sa in momentul crearii unui index in cadrul unei tabele care contine date duplicat, SQL Server:

creeaza indexul;

sterge valoarea duplicat;

intoarce eroare.

Daca se insereaza sau se modifica date in cadrul tabelei dupa crearea indexului, SQL Server:

accepta orice valori care nu sunt duplicat;

sterge valoarea duplicat;

intoarce eroare procesului apelant.

Allow_dup_row - aceasta optiune nu poate fi specificata pentru un index care are activata optiunea Ignore_dup_row. Optiunea priveste comportamentul in cazul inserarilor si modificarilor de inregistrari intr-o tabela care contine un index grupat unic. Daca aceasta optiune este prezenta crearea de inregistrari duplicat intr-un index grupat nu produce erori si nu afecteaza datele.

Drop_existing - aceasta optiune determina stergerea si regenerarea unui index grupat. Indecsii dispersati sunt regenerati ulterior. Astfel se previne o dubla actualizare a indecsilor dispersati.

Statistics_Norecompute - aceasta optiune determina ca statisticile de distributie sa nu mai fie actualizate automat. Pentru a reveni la actualizarea automata va trebui sa generati si sa actualizati statisticile.

ON grup_fisiere - specificarea unui grup de fisiere pentru plasarea unui index permite stocarea indexului intr-un alt grup de fisiere decat datele. Aceasta sporeste performantele unui index dispersat.

La executia unei interogari se poate impune folosirea unui index prin intermediul sectiunii optiuni_optimizare din sintaxa instructiunii SELECT.

Exemplu:

Select .

From nume_tabela (index = n)

Se determina folosirea indexului specificat prin numarul n. Daca n = 0, SQL Server va recurge la baleierea tabelei. Daca n = 1, se va utiliza indexul grupat daca exista. Celelalte valori n sunt date de numarul de indecsi din tabela.

Afisarea de informatii despre indecsi se poate face cu procedura stocata sp_helpindex si sp_statistics.

sp_helpindex nume_tabela - intoarce primii opt indecsi gasiti intr-o tabela.

sp_statistics [, 'posesor'][, 'califictiv'][, 'model_index']

[, 'is_unique'][, 'accuracy']

nume - numele tabelei despre indecsii careia doriti informatii.

posesor - posesorul tabelei.

calificativ - numele bazei de date in care se afla tabela.

model_index - indexul vizat.

is_unique - daca acest parametru este Y va intoarce doar indecsii unici

accuracy - nivel de acuratete de cardinalitate si de pagina pentru statistici.

Stergerea indecsilor se face cu comanda DROP INDEX:

DROP INDEX [posesor.] nume_tabela.nume_index

[, [posesor.] nume_tabela.nume_index . ]