Indexarea bazelor de date In această lucrare sunt trataţi indecsii şi rolul acestora în optimizarea timpului de răspuns la interogarea bazelor de date.În prima parte sunt tratate aspectele lehgate de tipurile d eindecsi, modul lor de stocare si categoriile existente, iar în a doua parte sunt explicate comenyile T-SQL referitor la indecsi CREATE, ALTER si DROP INDEX.In final este abordata problema fragmentarii indecsilor si impactul aspura performanţei sistemului , fiind deasemenea prezentate o serie de recomandări legate d emodul de creare a indecşilor.

Sistemele de baze de date utilizează indecşii pentru a oferi acces rapid la date relaţionale. Un index este o structură fizică distinctă ce permite ca interogările să acceseze mult mai rapid tuplele bazei de date.Alegerea potrivită a indecşilor se constituie astfel ca un element important de asigurare a performanţei sistemului cu baze de date. Dacă baza de date nu posedă un index SGBD va utiliza o aşa numită scanare - table scan, pentru a accesa regăsi tuple.Scanarea presupune că regăsirea tuplelor este realizată în secvenţă, astfel tuplele sunt extrase corespunzător locaţiei lor fizice din memorie, metodă mult ineficientă faţă de utilizarea indecşilor. Un index furnizeaza un acces direct şi rapid la înregistrările unei baze de date. Scopul utilizarii indecsilor este reducerea numarului de citiri/scrieri pe disc, prin folosirea unei cai indexate pentru a localiza mai rapid datele

Indecşii sunt stocaţi în structuri adiţionale de date numite pagini index, similar structurilor de stocare a datelor, astfel pentru fiecare tuplă indexată există o intrare index stocată la nivelul paginii index.Fiecare intrare index conţine o cheie index şi un pointer fiind din acest motiv de dimensiune mult mai mică decât tupla corespunzătoare.

Indecşii utilizaţi de sistemul MS SQL server sunt construiţi bazat pe structuri de date de tipul B’ Tree, ce posedă o structură arborescentă, cu frunzele aflate toate la acelaşi nivel, proprietate menţinută chiar dacă sunt adăugate sau şterse date în baza de date.Bazat pe acces indexat sunt necesare mult mai puţine operaţii de I-O pentru regăsirea unei tuple în timp mult mai scurt decât în varianta secvenţială.

Tipuri de indecşi şi proprietăţi Indexul clusterat determină ordinea fizică a datelor în tabelă. SGBD-ul permite crearea unui singur

index clusterat per tabelă deoarece tuplele tabelei pot fi ordonate fizic o singură dată.La utilizarea unui astfel de index, sistemul navighează în arborele Btree de la rădăcină spre frunze, acestea fiind legate într- o listă dublu înănţuită numită page chain.

Un index clusterat este creat în mod implicit pentru fiecare tabelă pentru care este definită o constrângere de tip cheie primară, astfel fiecare index clusterat este unic în mod implicit şi fiecare valoare de date apare o singură dată în coloana pentru care este definit un index clusterat.Dacă un astfel de index este creat pe o coloană nonunică, SGBD-ul va forţa unicitatea prin adăugarea unui identificator acelor tuple ce posedă valori duplicat.

Indexul nonclusterat. Un astfel de index are aceeaşi structură ca şi cel clusterat , având şi două

importante diferenţe şi anume: • Indexul nu modifică ordinea fizică a înregistrărilor în tabelă • Paginile frunză ale indexului conţin o cheie index şi un bookmark. Acesta identifică unde se găseşte

tupla corespunzătoare cheii index Ordinea fizică a tuplelor din tabelă nu se va modifica dacă vor fi definiţi pentru acea tabelă indecşi nonclusteraţi şi deasemenea numărul de operaţii I-O este mare. Un index este un obiect schema care îmbunatateste regasirea înregistrarilor . Indecsii pot fi creaţi explicit sau automat. Indexul este automat folosit şi întretinut , astfel odată creat indexul nu este necesară nici o intervenţie din partea utilizatorului.Indecsii sunt logic şi fizic independenti de bazele de date pe care le indexează. Acest lucru înseamnă că indecsii pot fi creaşi sau ştersi oricând fără nici un efect asupra bazelor de date sau a altor indecşi. Nota: Când este şters un tabel, indecşii corespunzători sunt de asemenea şterşi. Sintaxa T-SQL pentru indecşi Pot fi create doua tipuri de indecşi. Un prim tip îl reprezintă indexul unic, astfel serverul MS-SQL Server creează automat acest index când este definită o cheie primară (PRIMARY KEY) sau una unică (UNIQUE KEY), iar numele indexului este definit corelat cu numele dat acestui câmp.

Un al doilea tip de index este indexul mecanic. De exemplu, utilizatorii pot crea astfel de indecşi , în mod săecific diverselor interogări ale bazei de date cu scopul de a accelera accesul la înregistrarile bazei de date. Crearea unui index. Crearea unui index se face cu instructiunea CREATE INDEX a cărei sintaxă este următoarea:

CREATE [UNIQUE] [CLUSTERED |NONCLUSTERED] INDEX index_name ON table_name (column1 [ASC | DESC] ,...) [ INCLUDE ( column_name [ ,... ] ) ] [WITH [FILLFACTOR=n] [[, ] PAD_INDEX = {ON | OFF}] [[, ] DROP_EXISTING = {ON | OFF}] [[, ] SORT_IN_TEMPDB = {ON | OFF}] [[, ] IGNORE_DUP_KEY = {ON | OFF}] [[, ] ALLOW_ROW_LOCKS = {ON | OFF}] [[, ]ALLOW_PAGE_LOCKS = {ON | OFF}] [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}] [[, ]ONLINE = {ON | OFF}]] [ON file_group | "default"]

unde:

Index_ name – este numele indexului. Table_ name – numele bazei de date. Column1 – numele campului în baza de date ce urmeaza a fi indexat.

Observaţii :

• Orice coloană din baza de date ,excepţie cele ce conţin imagini , pot fi indexate. • Fiecare index compozit posedă anumite restricţii legat de lungimea şi numărul de coloane, astfel

lungimea maximă a unui index este 900 bytes, şi poate conţine max 16 coloane. • Opţiunea UNIQUE - specifică faptul că fiecare valoare de date poate apărea o singură dată în

coloana indexată. • Opţiunea CLUSTERED specifică un index clusterat, iar NONCLUSTERED specifică faptul că

indexul nu modifică ordinea fizică a tuplelor indexate . • Este posibilă specificare modului de realizare a indexării per coloană şi anume ascendent-

descendent (ASC-DESC). • Opţiunea INCLUDE permite specificarea unei coloane noncheie adăugată paginilor frunză ale unui

index nonclusterat.

Informaţii legate de fragmentarea unui index. Pe durata existenţei sale un index poate deveni fragmentat, adică stocarea datelor în paginile sale este ineficientă. Există două forme de fragmentare : internă şi externă. Fragmentarea internă specifică volumul de date stocat la nivelul fiecărei pagini, iar fragmentarea externă apare dacă ordinea logică a paginilor este eronată.

Preluarea informaţiei legate de nivelul de fragmentare intern poate fi realizată prin utiliyarea unei vederi specifice DMV ( Dynamic management view) sys.dm_db_index_physical_stats astfel :

DECLARE @db_id INT; DECLARE @tab_id INT; DECLARE @ind_id INT; SET @db_id = DB_ID('sample'); SET @tab_id = OBJECT_ID('employee'); SELECT avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (@db_id, @tab_id, NULL, NULL, NULL) După vizualizarea informaţiei legat de nivelul de fragmentare, este posibilă editarea acesteia prin funcţii

specifice astfel: sys.indexes catalog view

sys.index_columns catalog view sp_helpindex system procedure OBJECTPROPERTY property function SQL Server Management Studio sys.dm_db_index_usage_stats DMV sys.dm_db_missing_index_details DMV

Reconstruirea unui index. SGBD MS SQL Server permite modificarea indecşilor utilizând

instrucţiunea ALTER INDEX, instrucţiune ce poate fi utilizată pentru activităţi de mentenanţă a acestora.Sintaxa acestei instrucţiuni este similară celei de creare a indexului, permiţând desemenea o serie de alte opraţii cum ar fi :

• Reconstruirea indexului folosind opţiunea REBUILD • Reorganizarea paginilor frunză ale indexului fololsind opţiunea REORGANIZE • Dezactivarea unui index folosind opţiunea DISABLE

Reconstruirea indexului. Există trei modalităţi prin care un index poate fi reconstruit şi anume

• Utilizarea opţiunii REBUILD in sintaxa ALTER INDEX ( posibil cu specificarea ALL) • Utilizarea opţiunii DROP_EXISTING din sintaxa CREATE INDEX • Utilizarea comenzii DBCC DBREINDEX

Cea de a doua modalitate este mult mai puternică decât prima deoarece oferă mai mulţi parametri, ce pot fi

utilizaţi mult mai flexibil, între care schimbarea coloanelor parte a indexului, schimbarea unui index clusterat în nonclusterat.

Reorganizarea paginilor index. Această opţiune specifică faptul că paginile frunză ale indexului vor fi

reorganizate astfel încât ordinea fizică a paginilor se mapează la ordinea logigă stânga- dreapta a nodurilor frunză, eliminând astfel parte din fragmentare şi asigurând creşterea performanţei accesului

Dezactivarea indexului asigură eliminarea indexului din lista celor utilizabili până la proxima sa reactivare.Un index deyactivat nu va fi menţinut dacă survin modificări ale datelor, pentru aceasta este necesară completa sa reconstruire dacă se doreşte utilizarea sa. Pentru reactivarea indexului se va utiliza opţiunea REBUILD din sintaxa ALTER TABLE.

Stergerea şi redenumirea unui index. Stergerea unui index din dicţionarul de date se realizează prin

instrucţiunea DROP INDEX. Această operaţie o poate efectua doar cel care l-a creat sau are privilegiul DROP ANY INDEX.

Operaţia de eliminare a unui index clusterat este o operaţie costisitoare în resurse sistem , deoarece toţi indecşii nonclusteraţi trebuie reconstruiţi deoarece aceştia utilizează cheia index a indexului clusterat ca pointer în paginile frunză.Există şi opţiunea MOVE TO pentru operaţia de ştergere prin care se specifică locaţia la care să se mute tuplele de date aflate în paginile frunză ale indexului clusterat.

Notă : instrucţiunea DROP INDEX nu poate fi utilizată pentru a a elimina indecşi creaţi în mod implicit de către sistem pentru constângerile de integritate cum ar fi PRIMARY KEY şi UNIQUE.Pentru a elimina astfel de indecşi trebuie anterior eliminată constrângerea.

Aspecte privind crearea şi utilizarea indecşilor Deşi SGBD nu posedă nici un fel de limitări practice în ceea ce priveşte numărul de indecşi suportaţi,

este de dorit limitarea acestora din următoarele motive : de ex. aceştia utilizează spaţiu disc fiind astfel posibilă creşterea dimensiunii indecşilor peste dimensiunea bazei de date. Deasemenea prin contrast cu beneficiile utilizării indecşilor la regăsirea datelor, pentru operaţii de inserare şi actualizare aceştia au un impact negativ,deoarece creşterea numărului de indecşi ai unei tabele determină creşterea numărului de operaţii de reorganizare. Este recomandat în general ,crearea unui index să fie realizată pentru următoarele situaţii :

• un câmp ce este utilizat des in clauzele Where sau in conditii join • câmpuri cu mare varietate de valori • câmpuri cu numar mare de valori NULL • două sau mai multe câmpuri ce sunt folosite împreună frecvent într-o clauza WHERE sau într-o condiţie

join • baza de date este mare, şi majoritatea interogărilor nu vizează mai mult de 2-4% din înregistrări

Mai mulţi indecşi într-o bază de date nu înseamnă în mod automat o optimizare a interogărilor. Fiecare

operaţie de manipulare a datelor ce se realizează într-o bază de date ce conţine indecşi , implică o actualizare a indecşilor. Cu cât sunt mai multi indecşi asociaţi tabelului, cu atât va dura mai mult până când serverul va actualiza indecşii . Nu este necesara crearea unui index atunci când:

• baza de date este mică • campurile nu sunt des folosite intr-o conditie • majoritatea interogarilor vizeaza mai mult de 2-4% din inregistrari

Indecşi şi condiţii în clauza WHERE. Dacă clauza WHERE conţine o condiţie cu o singură coloană ,

aceasta se va constitui ca şi coloană de indexare. Dacă în clauză apar mai mai mulţi operatori AND , este necesară crearea unui index compozit ce include toate coloanele specificate Indecşi şi operatorul JOIN. În situaşia unui operator JOIN, este recomandată indexarea fiecărei coloane JOIN, acste de regulă reprezintă cheia primară a unei tabele , respectiv cheia străină a celeilalte, creând un index nonclusterat pentru coloana cheii străine , deoarece sistemul va crea implicit un index clusterat pentru coloana cheie primară.O creştere semnificativă de performanţă este realizată dacă toate coloanele din interogare sunt cuprinse în index.

Începând cu versiunea SQL Server 2005 este posibil ca o coloana calculată să fie marcată în mod explicit ca persistentă opţiunea PERSISTED , astfel ea va exista fizic alături de celelalte,opţiune necesară pentru coloane calculate generate pe tipuri de date aproximative ( FLOAT, REAL), deoarece un index poate fi creat pentru o coloană calculată doar dacă aceasta posedă un tip de date precis.Un index pe o coloanî calculată poate fi creat doar dacă următorii parametrii sunt setaţi ON:

QUOTED_IDENTIFIER CONCAT_NULL_YIELDS_NULL ANSI_NULLS ANSI_PADDING ANSI_WARNINGS

Concluzii Indecşii sunt utilizaţi pentru a realiza un acces la date mai eficient. Aceştia pot afecta atît operaţiile de

tip SELECT cât şi cele de manipulare a datelor cum ar fi INSERT, UPDATE sau DELETE.Un index poate fi clusterat sau nonclusterat, unic sau nonunic simplu sau compus.Indexul clusterat sorteayă fizic tuplele tabelei în ordinea specificată de coloana de indexare.Un index unic specifică faptul că fiecare valoare apare o singură dată pentru coloana acelei tabele. O facilitate deosebit de utilă legat de crearea şi manipularea indecşilor , o constituie DTA - Database Engine Tuning Advisor parte a componentei SQL Server numită SQL Server Profiler, ce realizează analiza încărcării în utilizare a bazei de date şi recomandă indecşi bazat pe acea analiză Exerciţii 1. Analizaţi beneficiile şi dezavantajele creării unui index 2. Creaţi pentru baza de date Scoala, un index compozit. Analizaţi pentru ce tipuri de interogări acest index va imbunătăţi performanţa.Identificaţi alternative. 3. Cum poate fi şters un index creat în mod implicit pentru cheia primară a unei tabele din baza de date Scoala