Indici SQLExplained

31 of 31 rows

Concetto↕	Spiegazione↕	Suggerimento↕
Tipi di indice
⤢B-tree	Albero bilanciato per scansioni di uguaglianza, intervalli e ordinate. Predefinito nella maggior parte dei database.	Usalo per =, <, >, BETWEEN, ORDER BY. Evitalo per la ricerca full-text.
⤢Clustered	Indice il cui livello foglia è la tabella stessa. Le righe sono memorizzate in ordine di chiave. Uno per tabella.	InnoDB fa clustering sulla chiave primaria. Le tabelle SQL Server senza chiave primaria sono heap.
⤢Nonclustered	Struttura separata che contiene chiavi più un puntatore alla tabella base. Molte per tabella.	Su una heap memorizza un row ID. Su una tabella clustered memorizza la chiave di clustering.
⤢Hash	Tabella hash solo per ricerche di uguaglianza. Corrispondenza esatta a tempo costante.	Usalo solo per operazioni =; intervalli e ordinamento richiedono B-tree. L'hash memory-optimized di SQL Server richiede un bucket count vicino al numero di righe.
⤢GIN	Generalized Inverted Index. Mappa valori su righe. Costruito per array e full-text.	Usalo per jsonb, array, tsvector, ricerca trigram. Più lento da costruire del B-tree.
⤢GiST	Generalized Search Tree. Estendibile per tipi non scalari e query di sovrapposizione.	Usalo per range geometrici, kNN, vincoli di esclusione. pg_trgm accelera LIKE.
⤢SP-GiST	GiST a partizione spaziale. Divide i dati in regioni non sovrapposte tramite alberi radix o quad.	Usalo per punti, prefissi IP e corrispondenza di prefissi. Non per query di sovrapposizione; usa GiST.
⤢BRIN	Block Range Index. Memorizza min/max per intervallo di blocchi. Ingombro minimo.	Usalo su tabelle grandi fisicamente ordinate. Economico da costruire, selettività debole.
⤢Columnstore	Indice memorizzato come segmenti di colonna invece che di righe. Alta compressione, esecuzione batch.	Usalo per scansioni analitiche su molte righe e poche colonne. I lookup a riga singola sono lenti.
⤢Full-text	Indice invertito dedicato alle parole in MySQL e SQL Server. Costruito per colonna di testo.	Usa MATCH AGAINST o CONTAINS. Il full-text di Postgres usa invece GIN su tsvector.
⤢Bitmap	Indice Oracle che memorizza una bitmap per valore distinto. Ogni bit marca una riga.	Usalo su colonne a bassa cardinalità in warehouse a lettura intensiva. Le scritture concorrenti si serializzano per bitmap.
⤢Descending	Colonna di indice dichiarata DESC. Le chiavi di quella colonna sono memorizzate in ordine inverso.	Necessario per ordinamenti misti come (a ASC, b DESC). Gli ordinamenti DESC semplici possono anche leggere un indice normale al contrario.
⤢Invisible	Indice di MySQL 8 che l'ottimizzatore ignora. Resta mantenuto a ogni scrittura.	Nascondi un indice prima di eliminarlo per testarne l'impatto. Rendilo di nuovo visibile senza ricostruzione.
Progettazione
⤢Composite	Indice a più colonne. Le colonne iniziali devono rispettare l'ordine dei filtri della query.	Ordina prima le uguaglianze, poi gli intervalli. Due indici singoli non sostituiscono un indice composito.
⤢Partial	Indice su un predicato WHERE. SQL Server lo chiama indice filtrato. Copre solo le righe corrispondenti.	Indicizza solo le righe con active='t'. Dimezza dimensioni e costo di scrittura.
⤢Covering (INCLUDE)	B-tree con colonne extra non chiave aggiunte. Le index-only scan evitano i lookup sulla heap.	Aggiungi le colonne recuperate di frequente via INCLUDE. Mantiene l'indice ordinabile.
⤢Unique	Indice che vieta valori duplicati. Rifiuta gli insert in collisione.	Usalo per chiavi naturali e vincoli uno-a-uno. UNIQUE NULL ammette molti NULL.
⤢Expression	Indice costruito su una funzione delle colonne anziché sulle colonne stesse.	Indicizza lower(email) o date_trunc('day', created_at). La query deve ripetere l'espressione esatta.
⤢Opclass	Classe di operatori. Definisce come un indice confronta e memorizza il tipo di una colonna.	Imposta varchar_pattern_ops per LIKE 'abc%' senza case folding. I default vanno bene per uguaglianza e intervalli.
⤢Index-only scan	Piano che risponde a una query usando solo le pagine dell'indice. La heap non viene mai letta.	Richiede ogni colonna recuperata nell'indice o nella INCLUDE list. Il Vacuum mantiene aggiornata la visibility map.
⤢Foreign keys	Le colonne di chiave esterna non ricevono un indice automatico nella tabella referenziante.	Indicizza ogni colonna FK su cui fai join o delete. Le delete a cascata scansionano la tabella figlia senza indice.
Manutenzione
⤢EXPLAIN	Ispettore di piani. Mostra scansioni sequenziali, scelte di indice, stime di costo.	Usa EXPLAIN (ANALYZE, BUFFERS). Un Seq Scan su una tabella da 10M di righe significa indice mancante.
⤢ANALYZE	Campiona le colonne della tabella per costruire le statistiche del planner. Gli istogrammi guidano la scelta degli indici.	Esegui dopo i caricamenti massivi. Statistiche stantie causano seq scan su colonne indicizzate.
⤢Over-indexing	Ogni indice aggiunge overhead in scrittura. Insert, update e delete pagano per indice.	Elimina gli indici inutilizzati. Traccia l'uso in pg_stat_user_indexes.
⤢pg_stat_user_indexes	Vista Postgres dell'uso per indice. Conta scansioni e tuple lette dall'ultimo reset delle statistiche.	Filtra per idx_scan = 0 per trovare gli indici inutilizzati. Azzera i contatori con pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Costruisce un indice senza bloccare letture o scritture. Richiede due scansioni della tabella.	Non può girare in una transazione. Una costruzione fallita lascia un indice INVALID da eliminare.
⤢DROP INDEX	Rimuove un indice e libera il suo spazio. Prende un lock esclusivo per impostazione predefinita.	Usa DROP INDEX CONCURRENTLY sulle tabelle trafficate. Controlla le statistiche d'uso prima di rimuovere.
⤢REINDEX	Ricostruisce un indice gonfio. Recupera spazio dalle tuple morte.	Usa REINDEX CONCURRENTLY in produzione. Senza, i lock bloccano le scritture.
⤢Bloat	Tuple morte e frammentazioni di pagina che si accumulano nelle tabelle MVCC e nei loro indici.	Misura con pgstattuple. Vacuum spesso; REINDEX quando lo spazio morto domina.
⤢Fillfactor	Percentuale di pagina riempita in scrittura. Lo spazio libero assorbe i futuri update.	Abbassalo a 70-90 sulle tabelle con hot-update. Le pagine piene forzano page split.
⤢CLUSTER	Riscrive la tabella fisicamente ordinata secondo un indice. Operazione una tantum.	Si abbina a BRIN per blocchi ordinati. L'ordine non è mantenuto nelle scritture successive.