Indexare SQLExplained

31 of 31 rows

Concept↕	Explicație↕	Sfat↕
Tipuri de indecși
⤢B-tree	Arbore echilibrat pentru egalități, intervale și scanări sortate. Implicit în majoritatea bazelor de date.	Folosește pentru =, <, >, BETWEEN, ORDER BY. Nu pentru căutare full-text.
⤢Clustered	Index al cărui nivel de frunze este chiar tabelul. Rândurile sunt stocate în ordinea cheii. Unul singur pe tabel.	InnoDB grupează după cheia primară. Tabelele SQL Server fără una sunt heap-uri.
⤢Nonclustered	Structură separată care reține chei plus un pointer înapoi spre tabelul de bază. Mai multe pe tabel.	Pe un heap stochează un ID de rând. Pe un tabel clustered stochează cheia de clustering.
⤢Hash	Tabel de dispersie doar pentru căutări de egalitate. Potrivire exactă în timp constant.	Folosește doar pentru operații =; intervalele și ordonarea au nevoie de B-tree. Hash-ul din SQL Server pentru tabele optimizate pentru memorie are nevoie de un bucket count apropiat de numărul de rânduri.
⤢GIN	Generalized Inverted Index. Mapează valori la rânduri. Construit pentru vectori și full-text.	Folosește pentru jsonb, array-uri, tsvector și căutare trigram. Mai lent de construit decât B-tree.
⤢GiST	Generalized Search Tree. Extensibil pentru tipuri non-scalare și interogări de suprapunere.	Folosește pentru intervale geometrice, kNN și constrângeri de excludere. pg_trgm accelerează LIKE.
⤢SP-GiST	GiST cu partiționare în spațiu. Împarte datele în regiuni care nu se suprapun, prin arbori radix sau quad.	Folosește pentru puncte, prefixe IP și potrivire de prefixe. Nu pentru interogări de suprapunere; folosește GiST.
⤢BRIN	Block Range Index. Stochează min/max per interval de blocuri. Amprentă minusculă.	Folosește pe tabele mari, sortate fizic. Ieftin de construit, selectivitate slabă.
⤢Columnstore	Index stocat ca segmente pe coloane în loc de rânduri. Compresie mare, execuție în loturi.	Folosește pentru scanări analitice pe multe rânduri și puține coloane. Căutările unui singur rând sunt lente.
⤢Full-text	Index inversat dedicat pe cuvinte în MySQL și SQL Server. Construit per coloană de text.	Folosește MATCH AGAINST sau CONTAINS. Full-text-ul din Postgres folosește în schimb GIN pe tsvector.
⤢Bitmap	Index Oracle care stochează câte un bitmap per valoare distinctă. Fiecare bit marchează un rând.	Folosește pe coloane cu cardinalitate scăzută în depozite dominate de citiri. Scrierile concurente se serializează per bitmap.
⤢Descending	Coloană de index declarată DESC. Cheile pentru acea coloană sunt stocate în ordine inversă.	Necesar pentru sortări mixte precum (a ASC, b DESC). Sortările DESC simple pot citi și un index normal invers.
⤢Invisible	Index din MySQL 8 ignorat de optimizator. Totuși întreținut la fiecare scriere.	Ascunde un index înainte de a-l șterge, pentru a testa impactul. Fă-l din nou vizibil fără o reconstrucție.
Proiectare
⤢Composite	Index pe mai multe coloane. Coloanele principale trebuie să respecte ordinea filtrelor din interogare.	Pune întâi egalitățile, apoi intervalele. Doi indecși simpli nu pot înlocui unul compus.
⤢Partial	Index pe un predicat WHERE. SQL Server îl numește index filtrat. Acoperă doar rândurile care se potrivesc.	Indexează doar rândurile cu active='t'. Jumătate din dimensiune și din costul de scriere.
⤢Covering (INCLUDE)	B-tree cu coloane suplimentare din afara cheii atașate. Scanările doar din index evită căutările în heap.	Adaugă coloanele des extrase prin INCLUDE. Indexul rămâne sortabil.
⤢Unique	Index care interzice valorile duplicate. Respinge inserările care se ciocnesc.	Folosește pentru chei naturale și constrângeri unu-la-unu. UNIQUE cu NULL permite mai multe NULL.
⤢Expression	Index construit pe o funcție de coloane, nu pe coloanele înseși.	Indexează lower(email) sau date_trunc('day', created_at). Interogarea trebuie să repete exact aceeași expresie.
⤢Opclass	Clasă de operatori. Definește cum compară și cum stochează un index tipul unei coloane.	Setează varchar_pattern_ops pentru LIKE 'abc%' fără plierea majusculelor. Valorile implicite se potrivesc egalităților și intervalelor.
⤢Index-only scan	Plan care răspunde la o interogare doar din paginile de index. Heap-ul nu este niciodată citit.	Are nevoie de fiecare coloană extrasă în index sau în lista INCLUDE. Vacuum menține visibility map la zi.
⤢Foreign keys	Coloanelor de cheie străină nu li se creează automat un index în tabelul care referențiază.	Indexează fiecare coloană FK prin care faci join sau ștergeri. Ștergerile în cascadă scanează tabelul copil fără un index.
Întreținere
⤢EXPLAIN	Inspector de planuri. Arată scanări secvențiale, alegerile de indecși și estimări de cost.	Folosește EXPLAIN (ANALYZE, BUFFERS). Un Seq Scan pe un tabel de 10 milioane de rânduri înseamnă un index lipsă.
⤢ANALYZE	Eșantionează coloanele tabelului pentru a construi statisticile planificatorului. Histogramele determină alegerea indecșilor.	Rulează după încărcări masive. Statisticile învechite cauzează seq scan pe coloane indexate.
⤢Over-indexing	Fiecare index adaugă cost la scriere. Insert-urile, update-urile și delete-urile plătesc pentru fiecare index.	Șterge indecșii nefolosiți. Urmărește utilizarea în pg_stat_user_indexes.
⤢pg_stat_user_indexes	Vizualizare Postgres a utilizării per index. Numără scanările și tuplurile citite de la ultima resetare a statisticilor.	Filtrează după idx_scan = 0 pentru a găsi indecși nefolosiți. Resetează contoarele cu pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Construiește un index fără a bloca citirile sau scrierile. Are nevoie de două scanări ale tabelului.	Nu poate rula într-o tranzacție. O construcție eșuată lasă un index INVALID de șters.
⤢DROP INDEX	Șterge un index și îi eliberează stocarea. Ia implicit o blocare exclusivă.	Folosește DROP INDEX CONCURRENTLY pe tabele aglomerate. Verifică statisticile de utilizare înainte de eliminare.
⤢REINDEX	Reconstruiește un index umflat. Recâștigă spațiu de la tuplurile moarte.	Folosește REINDEX CONCURRENTLY în producție. Blocările opresc scrierile fără el.
⤢Bloat	Tupluri moarte și goluri de pagini care se acumulează în tabelele MVCC și în indecșii lor.	Măsoară cu pgstattuple. Vacuum des; REINDEX când spațiul mort domină.
⤢Fillfactor	Procent dintr-o pagină împachetat la scriere. Spațiul liber absoarbe actualizările viitoare.	Coboară-l la 70-90 pe tabele cu multe update-uri. Paginile pline forțează divizări de pagini.
⤢CLUSTER	Rescrie tabelul ordonat fizic după un index. Operație unică.	Se combină cu BRIN pentru blocuri sortate. Ordinea nu se menține la scrierile următoare.