Indicizzazione dei database spiegata

Quasi ogni segnalazione di "database lento" che analizziamo si riduce a un indice mancante o usato male. L'indicizzazione è la competenza sulle performance con il più alto impatto che uno sviluppatore possa acquisire, eppure viene spesso trattata come una materia oscura. Non lo è. Una volta capito cosa sia realmente un indice e come leggere un piano di esecuzione, si può trasformare una query da dieci secondi a dieci millisecondi — con consapevolezza, non per fortuna.

Cos'è davvero un indice

Si pensi a un indice come all'indice analitico in fondo a un libro. Senza di esso, trovare ogni menzione di un argomento significa leggere ogni pagina: una sequential scan. Con l'indice, si salta direttamente alle pagine giuste. In un database, un indice è una struttura dati separata e ordinata che consente al motore di trovare le righe senza esaminare l'intera tabella.

La maggior parte degli indici sono B-tree, che mantengono i valori ordinati così da permettere al database di effettuare una ricerca binaria. Per questo un indice B-tree accelera uguaglianze (=), intervalli (<, >, BETWEEN), ordinamenti e prefissi, ma non serve a nulla per una LIKE '%foo' con wildcard iniziale.

Indicizzare ciò su cui si filtra, si fa join e si ordina

La regola pratica è questa: qualsiasi colonna che compare regolarmente in una clausola WHERE, in una condizione di JOIN o in un ORDER BY è candidata a un indice. Le chiavi esterne vale quasi sempre la pena di indicizzarle, dato che si effettueranno join su di esse in continuazione.

Per le query che filtrano su più colonne, un indice composito su più colonne insieme è più efficace di indici separati su singola colonna. Ma l'ordine delle colonne conta: un indice su (tenant_id, created_at) aiuta le query che filtrano per tenant_id da solo o per entrambi i campi, ma non quelle che filtrano per created_at da solo. Si metta prima la colonna più selettiva, quella su cui si filtra più spesso.

Imparare a leggere EXPLAIN

Non è mai necessario indovinare se un indice viene usato. In PostgreSQL, si esegua EXPLAIN ANALYZE sulla propria query e si legga il piano:

• Una Seq Scan su una tabella grande in una query critica è un campanello d'allarme: il database sta leggendo tutto.
• Index Scan o Index Only Scan significa che l'indice sta facendo il suo lavoro.
• Le stime sulle righe riportate rispetto alle righe effettive indicano se le statistiche del planner sono accurate.

Si faccia di EXPLAIN ANALYZE un riflesso automatico, prima e dopo aver aggiunto un indice. Trasforma il lavoro sulle performance da arte empirica a misura oggettiva.

Gli indici non sono gratuiti

È allettante indicizzare tutto, ma ogni indice ha un costo. Gli indici occupano spazio su disco e, cosa ancora più importante, ogni INSERT, UPDATE e DELETE deve aggiornare tutti gli indici coinvolti. Indicizzare in modo eccessivo una tabella con molte scritture rallenta esattamente le operazioni che più interessano.

Si indicizzi quindi con criterio:

1. Si aggiungano indici per supportare query lente reali e misurate, non ipotetiche.
2. Si eliminino periodicamente gli indici che nessuno usa (Postgres tiene traccia delle statistiche di utilizzo degli indici).
3. Si monitorino gli indici ridondanti, nei casi in cui un indice composito copre già quello su singola colonna.

Costruire l'abitudine

L'intera disciplina si racchiude in un ciclo: scrivere la query, eseguire EXPLAIN ANALYZE, aggiungere l'indice che il piano sta reclamando e misurare di nuovo. Farlo con costanza fa sì che le emergenze da "database lento" spariscano quasi del tutto dalla quotidianità. I team che faticano non mancano di qualche segreto: semplicemente non hanno mai guardato un piano di esecuzione.

Se la propria applicazione è in difficoltà e si sospetta il database, si contatti il nostro team.