Indici database ecommerce: EXPLAIN, query plan e manutenzione guidata dal carico

Partire dalla query lenta reale, non dall’elenco delle colonne

Un indice utile nasce da un percorso ecommerce osservato: ricerca catalogo, disponibilità per magazzino, carrello, ordine o riconciliazione. Crearlo perché una colonna compare spesso nei filtri confonde frequenza e selettività. Prima raccogli la query parametrizzata, la durata, le righe restituite, la frequenza, il contesto transazionale e la versione applicativa. Conserva anche distribuzione dei parametri e picchi, perché lo stesso SQL può comportarsi diversamente per un ordine puntuale e per un intervallo molto ampio.

Congelare forma e contesto della query

Normalizza i valori sensibili nei campioni, ma non perdere tipi, predicati, ordinamento, join e limite. Verifica se l’applicazione invia la forma attesa o genera varianti che impediscono il riuso. Registra concorrenza, cache calda o fredda e volume delle tabelle. La domanda iniziale non è “quale indice manca?”, bensì “quale accesso costa, con quali parametri e quale impatto sul percorso cliente?”

Leggere EXPLAIN come un’ipotesi dell’ottimizzatore

EXPLAIN mostra il piano scelto: ordine delle operazioni, metodi di accesso, join, ordinamenti e stime. Non basta cercare la parola che indica una scansione completa. Su una tabella piccola o quando la query restituisce gran parte delle righe, leggere in sequenza può essere una scelta sensata. Cerca invece i nodi che amplificano il lavoro: molte righe attraversate per produrne poche, ordinamenti evitabili, join alimentati da cardinalità sbagliate o filtri applicati troppo tardi.

PostgreSQL espone costi e stime per nodo; MySQL documenta EXPLAIN per mostrare come esegue le istruzioni. Le rappresentazioni e i nomi non sono intercambiabili. Definisci una scheda di lettura per il motore e la versione effettivi, evitando confronti diretti tra valori di costo provenienti da sistemi diversi.

Separare stime e valori effettivi

Il divario tra righe stimate e reali è spesso più informativo del tempo assoluto. Può indicare statistiche non aggiornate, dati fortemente sbilanciati, correlazione tra colonne o parametri non rappresentativi. Prima di aggiungere un indice, aggiorna le statistiche secondo le procedure del motore e ripeti il piano con lo stesso campione. Se la stima resta errata, documenta la distribuzione e valuta statistiche più adatte o una riscrittura prudente, senza trasformare ogni anomalia in un indice nuovo.

Usare EXPLAIN ANALYZE in modo controllato

Un piano con misure reali consente di confrontare righe, cicli e tempo dei nodi, ma l’istruzione viene eseguita. Non provarla alla cieca su un’istruzione di modifica dei dati o su una query molto onerosa in produzione. Riproduci dati e parametri in un ambiente rappresentativo, applica limiti di tempo, osserva carico e blocchi e concorda una finestra per i casi indispensabili. Per istruzioni che modificano dati, una transazione annullata può aiutare in alcuni scenari, ma non neutralizza necessariamente ogni effetto esterno o funzione con effetti collaterali.

Confronta almeno un piano di riferimento e uno candidato. Mantieni invariati parametri, volume e condizioni di cache quanto possibile. Un singolo tempo più basso non dimostra un miglioramento stabile: il risultato deve reggere alle forme principali del carico senza spostare il costo su scritture, replica o manutenzione.

Progettare l’ordine di un indice composto dal piano

In un indice B-tree multicolonna l’ordine delle chiavi influenza la parte di struttura che il motore può restringere. PostgreSQL descrive l’efficacia delle uguaglianze sulle colonne iniziali e della prima disuguaglianza successiva, con ottimizzazioni che possono variare. Parti quindi dai predicati reali: uguaglianze stabili, intervalli, join e ordinamento. Non applicare ciecamente la regola “colonna più selettiva prima”; verifica il piano delle combinazioni importanti e il riuso possibile da altre query.

Evita duplicati quasi identici. Un indice composto può coprire alcuni accessi che usano il suo prefisso, ma non tutti i motori sfruttano allo stesso modo colonne saltate, direzione dell’ordinamento o predicati. Elenca le query che dovrebbero beneficiarne e quelle escluse, poi controlla che il nuovo indice non replichi una struttura esistente con una sola colonna aggiunta.

Indici di copertura e indici filtrati richiedono verifiche specifiche

Un indice di copertura contiene le chiavi di ricerca e dati aggiuntivi necessari a soddisfare una query con meno accessi alla struttura principale. I dettagli cambiano: in SQL Server le colonne incluse non sono colonne chiave e la guida raccomanda di evitare indici eccessivamente larghi; altri motori hanno sintassi e condizioni diverse. Un indice filtrato di SQL Server indicizza un sottoinsieme definito da un predicato ed è utile solo quando quel predicato rappresenta davvero il carico. Non tradurre automaticamente questa tecnica in una funzionalità omonima su PostgreSQL o MySQL: progetta e verifica la variante nativa del motore.

Mettere nel budget anche il costo delle scritture

Ogni indice aggiunge lavoro agli inserimenti e alle eliminazioni, oltre che agli aggiornamenti che modificano dati presenti nella struttura; per gli altri aggiornamenti il costo dipende dal motore. Su carrelli, inventario e ordini, una lettura più rapida può aumentare latenza di inserimento o aggiornamento, log, spazio, pressione sulla cache e lavoro di manutenzione. Prima del rilascio registra dimensione prevista, volume di scrittura sulle colonne coinvolte e impatto sul percorso critico. Un indice largo su una tabella soggetta a molti aggiornamenti richiede una giustificazione più forte di un indice compatto su dati quasi statici.

La creazione stessa è un’operazione da pianificare. Verifica opzioni online o concorrenti disponibili nella versione usata, durata stimata, spazio temporaneo, replica e possibilità di annullamento. Le parole “online” o “concurrent” non significano assenza totale di blocchi o consumo: leggi la documentazione del motore e della versione e prepara soglie di arresto.

Osservare indici e piani come un ciclo di vita

Collega ogni indice a proprietario, query, ipotesi, data di revisione e indicatori. Misura latenza per percentile, righe esaminate rispetto a quelle restituite, frequenza del piano, letture, scritture e crescita della struttura. Conserva impronte dei piani per individuare variazioni dopo crescita dei dati, aggiornamenti delle statistiche o cambi di versione. Un piano differente non è automaticamente una regressione; deve essere correlato a costo e risultato del carico reale.

Rimuovere un indice inutilizzato senza scorciatoie

Un contatore di utilizzo pari a zero non basta: può essere stato azzerato da un riavvio oppure ignorare query mensili, procedure di emergenza e vincoli. Osserva un periodo che includa i cicli commerciali rilevanti, verifica se l’indice sostiene unicità o relazioni, cerca riferimenti nei piani e consulta i proprietari. Dove possibile rendilo invisibile o disabilitalo in un ambiente controllato; altrimenti prepara una rimozione reversibile con definizione DDL archiviata e monitoraggio immediato.

Rilasciare una modifica alla volta con un rollback chiaro

Crea l’indice, raccogli statistiche e confronta i piani senza riscrivere contemporaneamente la query. Distribuisci per fasi, osservando sia il caso bersaglio sia le scritture e le query vicine. Definisci in anticipo condizioni di rollback: crescita della coda, latenza delle operazioni di scrittura, pressione sullo spazio di archiviazione o peggioramento di un piano frequente. Se il motore sceglie ancora un altro percorso, non forzare hint permanenti come prima risposta; ricontrolla stime, tipi, predicati e beneficio atteso.

Misurare il risultato e mantenere un portafoglio essenziale

Il successo non è “indice usato”, ma meno lavoro per una query importante entro un costo operativo accettabile. Confronta latenza, righe elaborate, consumo di CPU e operazioni di I/O, blocchi e velocità di scrittura su finestre equivalenti. Riesamina periodicamente sovrapposizioni, strutture cresciute e piani cambiati. Per trasformare analisi, rollout e manutenzione in una pratica verificabile, scopri i nostri servizi per sistemi digitali ed ecommerce. Un portafoglio piccolo, motivato e osservato è più affidabile di una collezione di indici creata dopo ogni picco.