Utilizarea indexurilor și efectuarea scanărilor tabelului sunt două abordări fundamentale pentru accesarea datelor dintr-o bază de date, fiecare cu compromisuri distincte în funcție de situație.
Un indice într -o bază de date este o structură de date care permite o căutare mai rapidă, permițând sistemului să localizeze rapid rândurile fără a scana fiecare rând din tabel. Majoritatea bazelor de date relaționale folosesc structuri de arbori B+ pentru indexuri, care organizează chei și indicatoare într -o formă de copac. Acest lucru permite căutări, inserții și ștergeri în complexitatea timpului logaritmic $$ o (\ log n) $$, care este de obicei mult mai rapid decât scanarea întregului tabel cu o complexitate de $$ o (n) $$. Indici pot fi grupați sau necredincioși, cu indexuri grupate care stochează date în ordine sortată fizic, îmbunătățind performanța de scanare a gamei la costul cheltuielilor generale suplimentare la modificările datelor. Indici pot fi, de asemenea, compoziți, parțiali, filtrați sau bazați pe hash, reglați pentru modele de interogare specifice.
În schimb, o scanare a tabelului (sau scanare completă a tabelului) citește în mod secvențial fiecare rând din tabel, indiferent de selectivitatea interogării. Aceasta implică scanarea tuturor blocurilor de date ale tabelului și este adesea considerată cea mai scumpă metodă de acces, deoarece procesează mai multe date decât este necesar. Cu toate acestea, scanările de tabel pot funcționa bine în anumite cazuri. De exemplu, atunci când întrebările recuperează un procent mare de rânduri, cheltuielile generale ale utilizării unui indice (care necesită adesea căutări suplimentare pentru rândurile reale) poate depăși costul de scanare a întregului tabel o dată. Scanările de tabel pot folosi citiri cu mai multe blocaje, care permit citirea unor bucăți mari de date cu mai puține operații de I/O, reducând astfel latența în comparație cu citirea multor blocuri individuale impuse la întâmplare de scanările indexului.
Un compromis major implică selectivitatea și dimensiunea setului de date returnat de interogare. Dacă interogarea se filtrează până la un număr mic de rânduri (selectivitate ridicată), indexurile depășesc, în general, scanările tabelului, deoarece trebuie doar să acceseze datele relevante. Dar, pe măsură ce procentul de rânduri returnate crește, costul scanării indicelui crește, deoarece pot fi necesare mai multe căutări cheie, iar motorul bazei de date trebuie să efectueze operațiuni suplimentare de I/O aleatoare. La un anumit prag, adesea în jur de 10-20% din rândurile tabelului, dar dependente de lățimea datelor și hardware, o scanare completă a tabelului devine mai eficientă. Acest lucru se datorează faptului că costurile de scanare rămân constante indiferent de selectivitate, pur și simplu citind tabelul secvențial o dată.
Scanările de index citesc de obicei mai puține pagini decât o scanare a tabelului atunci când coloanele acoperite sunt mai puține sau mai compacte decât rândurile complete de tabel. De exemplu, un index ar putea include doar coloanele indexate fără datele complete ale tabelului, ceea ce îl face mai subțire și permițând mai multe rânduri să se potrivească pe fiecare pagină a bazei de date. Acest lucru reduce aerul I/O atunci când scanează indicele în comparație cu scanarea datelor întregului tabel. În plus, unii indici pot fi filtrați (indexuri parțiale) pentru a exclude rândurile irelevante, reducând în continuare amprenta de scanare.
Pe de altă parte, scanările de masă completă scriu mai puțină povară pe partea de întreținere a bazei de date. Indicele introduc cheltuielile generale în timpul operațiunilor de modificare a datelor, cum ar fi inserarea, actualizarea și ștergerea. Fiecare modificare a tabelului necesită actualizarea indexurilor, ducând uneori la o latență de scriere sporită și la stocarea cheltuielilor generale, în special dacă există mulți indici pe tabel. Acest cheltuieli generale poate afecta concurența și poate duce la conținut în medii de scriere grea. Astfel, scanările de tabel, care citesc pur și simplu datele în ordinea sa naturală, fără întreținere suplimentară a structurii, evită acest cost.
O altă considerație importantă este efectul în cache și caracteristicile hardware. Scanările de tabel beneficiază de I/O secvențiale și de preîncărcare, permițând sistemului să citească mai multe blocuri contigue eficient, adesea din memorie, în cache. În schimb, scanările indexului suportă I/O aleatoriu pentru a obține blocuri de date disparate, mai ales dacă scanarea indexului trebuie să caute indicatoare de rând în stocarea mormanelor. Acest lucru poate face scanările indexului mai lent pe sisteme cu un disc mai lent de performanță I/O aleatoare, deși SSD -urile și grupurile mari de memorie restrâng acest decalaj. Situația poate depinde, de asemenea, de specificații precum paralelismul și capacitățile cu mai multe fileturi ale motorului bazei de date, unde scanările tabelului paralel pot spori semnificativ randamentul.
În plus, fragmentarea internă și aspectul de stocare fizică influențează compromisurile de performanță. Scanările de masă pe tabelele organizate de grămadă ar putea suferi de înregistrări transmise, unde rândurile s-au mutat în diferite pagini din cauza actualizărilor, agravând eficiența scanării. Indexuri grupate, care stochează datele sortate după cheie, pot evita această problemă și uneori fac o „scanare de tabel” echivalentă cu o scanare a indexului grupat. Cu toate acestea, beneficiile vin cu costul reordonilor scumpe de rânduri în timpul creșterii datelor grele.
Din perspectiva Optimizerului de interogare, decizia dintre o scanare a indexului și o scanare a tabelului este de obicei luată de modelele de estimare bazate pe costuri, ținând cont de statistici privind distribuția datelor, numărul de rânduri și costurile hardware. Optimizatorul echilibrează costurile CPU, I/O și memorie pentru a alege cea mai eficientă cale de acces. Aceste decizii pot fi influențate de factori precum memoria disponibilă, starea de cache și modelele de interogare. Nu există un prag fix între când să folosești unul sau altul; Punctul de crossover variază în funcție de sistem și volum de muncă.
În rezumat, compromisurile dintre utilizarea indexurilor și scanările tabelului includ:
- Performanță vs. Volumul datelor: indexuri depășesc atunci când filtrați la câteva rânduri; Scanările de tabel pot fi mai bune pentru regăsirea de date mari.
- Modele de I/O: scanările indexului provoacă citiri aleatorii I/O; Scanările de tabel beneficiază de I/O secvențiale și citiri multi-bloc.
- Întreținere generală: indexurile cresc costurile de operare de scriere din cauza actualizărilor din structurile de index; Scanările de masă nu suportă acest lucru.
- Eficiență de stocare: indexurile pot fi compacte prin acoperirea mai puține coloane; Scanările tabelului procesează rândurile complete și potențial mai multe date.
- Efecte în cache: Scanările de tabel pot utiliza eficient cache -ul datelor, în special cu citiri secvențiale mari; Scanările de index ar putea să nu beneficieze la fel de mult din cauza accesului aleatoriu.
- Decizia optimizatoare: Optimizatorii de interogare bazate pe costuri aleg dinamic între aceste opțiuni pe baza statisticilor de interogare și a specificului volumului de muncă.
- Impactul aspectului datelor: Tabelele Heap pot suporta penalități precum înregistrările transmise în timpul scanărilor; Indicele grupate organizează date fizic, dar cresc costurile de actualizare.