Dietro le quinte delle prestazioni: dove e come i database memorizzano i loro indici?
Se un database è una biblioteca, allora i dati sono i libri sugli scaffali, e gli indici sono il catalogo intelligente che ti permette di trovare il libro giusto in pochi secondi senza scansionare ogni scaffale. Tutti sanno che gli indici accelerano le ricerche. Ma ti sei mai chiesto dove vivono fisicamente questi indici e come sono strutturati sotto il cofano? Comprendere questo è la chiave per una vera ottimizzazione.
Lasciamo cadere le astrazioni e guardiamo dentro.
Il principio fondamentale: un indice è una struttura dati separata
È fondamentale capire fin dall'inizio: un indice non è memorizzato "insieme" ai dati delle righe della tabella. È una struttura indipendente, ma strettamente collegata. Quando crei un indice, il DBMS crea e mantiene un oggetto speciale, molto spesso basato su alberi bilanciati (B-tree, B+tree).
Quindi dove si trova sul disco? Ci sono diverse opzioni:
- Nello stesso tablespace della tabella stessa. Questo è l'approccio predefinito più comune (ad esempio, in PostgreSQL, MySQL InnoDB). I dati di una tabella e i suoi indici sono diversi insiemi di pagine (blocchi) all'interno degli stessi file. Comodo per la gestione, ma può portare a contesa I/O tra le operazioni di lettura/scrittura dei dati e degli indici.
- In un tablespace separato o su un disco separato. Questa è una tecnica di ottimizzazione avanzata. Ad esempio, puoi posizionare gli indici su un SSD veloce e i dati a cui si accede meno frequentemente su un HDD più lento. Oppure distribuire il carico I/O. Questo è possibile in Oracle, SQL Server, PostgreSQL.
Il cuore dell'indice: la struttura B+Tree in dettaglio
Quasi tutti i DB relazionali usano un B+tree. Visualizziamo la sua anatomia:
- Nodi radice e non foglia: Contengono chiavi di navigazione (ad esempio, i valori della colonna indicizzata) e puntatori ai nodi figli. Questo è l'"indice" del catalogo.
- Nodi foglia: Questa è la parte più importante. Memorizzano i valori delle chiavi indicizzate e i puntatori (o i dati effettivi delle righe stesse, nel caso di un indice clustered).
Sul disco fisico, tutti questi nodi dell'albero sono memorizzati in pagine o blocchi — blocchi di memoria di dimensioni fisse (tipicamente 4-16 KB). Le pagine sono collegate tra loro per formare la struttura ad albero.
Casi speciali e altre strutture
- Indici Hash: Usati per ricerche esatte (=). Sono tabelle hash in cui la chiave è il valore hash della colonna e il valore è un puntatore alla riga. Memorizzati nel proprio segmento di memoria/disco. Molto veloci ma inutili per query di intervallo.
- Indici Bitmap: Ideali per colonne a bassa cardinalità (genere, stato). Sono array di bitmap. Memorizzati in formato compresso, risparmiando spazio. Ogni bitmap corrisponde a un valore univoco.
- Indici invertiti: Il fondamento della ricerca full-text (FTS). Memorizzano parole (token) e un elenco di documenti/righe in cui appaiono. Queste sono strutture specializzate, spesso gestite da motori separati (come Lucene in Elasticsearch).
Perché questa conoscenza pratica è importante?
- Prestazioni delle query: Un indice progettato male (su una colonna a bassa cardinalità) occuperà spazio su disco senza fornire benefici.
- Pianificazione del posizionamento dello storage: Nei sistemi ad alto carico, puoi posizionare indici e dati su dischi fisici diversi per ridurre la contesa I/O.
- Manutenzione: Gli indici si frammentano con aggiornamenti frequenti. Operazioni come REINDEX o REORGANIZE li ricostruiscono, riordinando fisicamente le pagine per una lettura sequenziale più veloce.
- Monitoraggio: Puoi interrogare le viste di sistema (come pg_stat_all_indexes in PG o sys.indexes in SQL Server) per vedere la dimensione dell'indice su disco e le sue statistiche di utilizzo.
Conclusione: Gli indici non sono magia; sono strutture dati concrete, fisicamente esistenti. La loro efficienza dipende non solo dalla logica (quali campi indicizzare) ma anche dal posizionamento fisico e dalla meccanica interna. Una profonda comprensione di questi meccanismi separa un buon sviluppatore da un vero ingegnere di database.