Lezioni del modulo (1/4)
Indici e B-Trees
Come fa un database a trovare un singolo record su milioni di righe in frazioni di secondo?
Di default, quando chiediamo SELECT * FROM customers WHERE city = 'Milano', Postgres deve eseguire una Sequential Scan (scansione sequenziale): legge fisicamente l'intero contenuto della tabella dal disco riga per riga, confrontando ogni città con "Milano". Quando una tabella possiede milioni di record, questo processo paralizza le performance.
La soluzione strutturale principale per questi colli di bottiglia sono gli Indici.
Gli Indici (B-Trees)
Gli indici funzionano esattamente come l'indice analitico in fondo a un grosso libro. Invece di leggere tutto il libro per trovare dove si parla di "Milano", consultiamo le pagine in fondo dove la parola "Milano" ci reindirizzerà a [pag. 45, 114, 230].
I database, dietro le quinte, creano questa separata struttura dati molto compatta (spesso un "B-Tree") ordinata per la colonna o le colonne in questione, che contiene puntatori diretti allo storage fisico delle righe corrispondenti.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Questa singola operazione avvia il motore di Postgres che andrà ad estrarre in background e indicizzare tutti i valori di city. Al completamento, ogni successiva SELECT ... WHERE city = '...' volerà!
Chiavi primarie ed unicità
Sapevi che la regola della chiave primaria (PRIMARY KEY) o delle chiavi UNIQUE usano segretamente gli Indici in background? Essendo costretto il DB ad assicurare che una mail o un ID non sia mai duplicato in fase di insert o alterazione, PostgreSQL vi crea automaticamente sopra un Indice B-Tree implicito: in questo modo i controlli di unicità impiegano microsecondi.
E' per questo motivo che le Join effettuate sul campo ID sono incredibilmente performanti.
Cancellare un indice
Se ci si rende conto che le queries pesanti sono cambiate, ed un indice non viene mai interpellato, è saggio distruggerlo recuperando spazio vitale:
DROP INDEX idx_customers_city;Prova tu
Nel nostro e-commerce notiamo tramite delle dashboard che effettuiamo spessissimo ricerche dei clienti filtrati in base alla loro nazionalità (colonna country in customers). \nCrea un nuovo indice base sulla colonna country denominandolo 'idx_customers_country'.
Mostra suggerimento
Usa CREATE INDEX nome_indice ON nome_tabella(nome_colonna);
Soluzione disponibile dopo 3 tentativi
Esplorare indici unici secondari
Un indice B-Tree può anche essere sfruttato per simulare una constraint UNIQUE durante la sua creazione e velocizzare le ricerche, contemporaneamente rendendo esplicito e controllato l'intento logico.\n\nCrea un nuovo indice in cui definisci la parola UNIQUE. Definiscilo in modo che blocchi mai iscrizioni non volute da altri user, forzando la colonna 'email' di 'customers' e chiamalo 'idx_unique_email_customer'.
Mostra suggerimento
Scrivi CREATE UNIQUE INDEX nome_indice ON tabella(colonna);
Soluzione disponibile dopo 3 tentativi