Una schermata lenta non dimostra che il problema sia il database, né che un indice sia la soluzione. La stessa percezione può avere origine nel codice PHP, nella saturazione delle connessioni, in una chiamata HTTP esterna, nella serializzazione delle risposte, nei blocchi o in una query che restituisce troppi dati. Sapere come analizzare query lente in PHP consiste nel raccogliere evidenze prima di modificare lo schema o aggiungere ottimizzazioni che potrebbero aumentare il costo delle scritture.
Separare la latenza dell'applicazione dalla latenza dei dati
Iniziate scomponendo il tempo totale di una richiesta. Registrate una route o un comando identificabile, l'ora di inizio e fine, le query eseguite, le relative durate e le dipendenze esterne. Non basta misurare il tempo medio: un'API può sembrare in salute e, tuttavia, non funzionare per determinati filtri, clienti o pagine profonde.
Per ogni incidente, è opportuno distinguere:
- Tempo in PHP: trasformazione di collezioni, cicli, serializzazione JSON, generazione di documenti o uso eccessivo di memoria.
- Tempo del database: durata di ogni query, attesa per blocchi, apertura della connessione e numero di righe trasferite.
- Tempo di rete e delle dipendenze: cache remote, API di terze parti, archiviazione di file, code o servizi di identità.
- Tempo in coda: richieste in attesa di worker PHP, connessioni disponibili o risorse del database.
Usate trace o log strutturati con un identificatore di richiesta. Una query isolata di 20 ms può trasformarsi in un problema di secondi se viene eseguita centinaia di volte nella stessa risposta. Al contrario, una query di 500 ms può non essere la causa principale se il processo resta in attesa di un servizio esterno per vari secondi.
Raccogliere evidenze prima di modificare il codice
Acquisite la query parametrizzata e, separatamente, parametri rappresentativi. Evitate di registrare segreti, dati personali completi o valori non necessari per riprodurre il caso. Una ricerca per uno stato frequente non si comporta come una ricerca per un identificatore univoco; valutare soltanto il caso più comodo porta a decisioni errate.
Le evidenze minime devono includere:
- Route, job asincrono o comando interessato, oltre alla relativa frequenza.
- Durata osservata, percentili elevati e momento di comparsa.
- SQL, parametri tipizzati e numero di esecuzioni per richiesta.
- Righe restituite e, quando possibile, righe lette o esaminate.
- Dimensione approssimativa delle tabelle e distribuzione dei valori filtrati.
- Concorrenza, operazioni di scrittura simultanee e blocchi rilevanti.
I log delle query lente del motore aiutano a individuare candidati, ma non sostituiscono un trace dell'applicazione: normalmente non indicano quale endpoint abbia costruito una query né quante volte sia stata ripetuta. In MySQL e PostgreSQL, combinate queste informazioni con metriche di connessione, CPU, I/O e tempi di attesa per non confondere un piano inefficiente con un'infrastruttura temporaneamente satura.
Individuare pattern che moltiplicano il costo
Prima di analizzare un'istruzione complessa, cercate pattern ricorrenti. L'N+1 compare quando un elenco ottiene le righe principali e poi esegue una query aggiuntiva per ogni relazione. Anche se ogni singola query è rapida, il volume di round trip al database, il lavoro di pianificazione e la contesa crescono con la dimensione della pagina.
Sono sospetti anche i filtri a bassa selettività, come stati molto comuni; funzioni applicate su colonne filtrate; conversioni implicite di tipo; ricerche con wildcard iniziale; ordinamenti di set grandi e paginazione mediante OFFSET elevato. Richiedere SELECT * può aumentare trasferimento, memoria e lavoro di lettura, anche quando il piano utilizza già un indice.
La correzione non è sempre una query unica. Caricare relazioni in modo controllato può risolvere un N+1, ma un eager loading senza limiti può creare una query o una risposta enorme. Definite quali relazioni servono davvero alla route, limitatene le colonne e misurate l'effetto con la dimensione di pagina prevista.
Interpretare il piano di esecuzione con dati reali
Eseguite EXPLAIN per conoscere il piano proposto e usate la variante che incorpora l'esecuzione reale quando è sicura e adatta all'ambiente. In PostgreSQL, EXPLAIN ANALYZE esegue la query; un'istruzione di modifica non deve essere analizzata in questo modo senza comprenderne l'effetto. In MySQL, le modalità disponibili dipendono dalla versione e dalla configurazione, ma l'obiettivo è lo stesso: confrontare le stime con il lavoro reale.
Una scansione sequenziale non è automaticamente negativa. Se la query richiede una parte consistente di una tabella piccola o poco selettiva, percorrerla può costare meno che alternare accessi tra indice e tabella. Indagate invece quando il piano mostra molte più righe reali di quelle stimate, ordinamenti costosi, letture temporanee, join su set ampi o cicli interni eseguiti molte volte.
Domande utili durante la revisione di un piano
- Quante righe si aspettava l'ottimizzatore e quante ne ha effettivamente elaborate?
- Quale nodo concentra più tempo, letture o iterazioni?
- Il filtro viene applicato presto o dopo aver combinato set di grandi dimensioni?
- L'ordinamento avviene su più righe di quante ne richieda la risposta?
- Le statistiche riflettono l'attuale distribuzione dei dati?
Le discrepanze tra stima e realtà possono richiedere l'aggiornamento delle statistiche o la revisione di tipi e condizioni, non la creazione immediata di un indice. Il piano è una spiegazione di un'esecuzione con determinati parametri e carico; non un ordine automatico di modifica.
Scegliere tra riscrittura, paginazione, accesso ai dati e indice
Riducete prima il lavoro inevitabile. Selezionate solo le colonne necessarie, applicate limiti ragionevoli, eliminate relazioni non utilizzate ed evitate di trasferire cronologie complete a PHP per filtrarle in seguito. Se il caso d'uso richiede di esplorare una cronologia in crescita, sostituite la paginazione profonda con la paginazione tramite cursore o chiave: per esempio, continuando da una combinazione stabile di data e identificatore anziché scartare migliaia di righe con OFFSET.
Rivedete join e filtri affinché confrontino colonne compatibili ed esprimano chiaramente la condizione. A volte è opportuno interrogare una relazione in blocchi; in altri casi, è preferibile una singola query ben delimitata. La decisione dipende da cardinalità, volume della risposta e frequenza, non da una regola universale.
Un indice composto aiuta quando corrisponde al pattern di accesso. Il suo ordine è importante: normalmente le colonne di uguaglianza e selettive devono facilitare il filtraggio prima di quelle usate per intervallo o ordinamento, ma la query concreta e il motore determinano il risultato. Un indice può inoltre aiutare a evitare un ordinamento se copre un ordinamento compatibile, anche se non ogni combinazione di WHERE e ORDER BY lo consente.
Evitate di indicizzare colonne solo perché compaiono in una condizione. Gli indici occupano spazio, consumano memoria e aggiungono lavoro a INSERT, UPDATE e DELETE. Indici ridondanti o di scarsa utilità possono peggiorare un sistema con molte scritture. Verificate anche se l'indice consente di ottenere le colonne richieste senza letture aggiuntive, ma non aggiungete colonne di copertura senza misurarne il costo.
Esempio ipotetico: una cronologia delle operazioni
Supponete un elenco che mostra operazioni per conto, stato e data. Con la crescita della cronologia, la pagina 200 peggiora. La prima ipotesi potrebbe essere creare un indice sulla data. Tuttavia, il trace rivela una query principale seguita da una query per ogni operazione per ottenere l'utente responsabile: c'è un N+1. Il piano della query principale inoltre legge molte righe per scartare quelle precedenti mediante OFFSET.
La sequenza ragionevole sarebbe caricare i responsabili in modo aggregato o tramite un join limitato alle colonne necessarie, sostituire la paginazione profonda con un cursore basato su created_at e id, quindi misurare nuovamente. Solo allora si valuta un indice allineato al filtro del conto, all'ordinamento stabile e al cursore. La modifica dell'accesso può ridurre più lavoro di un indice isolato, e l'indice finale deve essere verificato anche rispetto alla creazione di nuove operazioni.
Convalidare sotto carico e monitorare regressioni nelle scritture
Confrontate prima e dopo con parametri, distribuzione dei dati e concorrenza rappresentativi. Misurate durata, righe elaborate, letture, uso della CPU, memoria in PHP, dimensione della risposta e numero di query per richiesta. Per un nuovo indice, misurate anche latenza e capacità delle operazioni di scrittura interessate.
Definite limiti di accettazione espliciti: per esempio, una riduzione verificabile del percentile elevato della route senza aumentare in modo inaccettabile il tempo di creazione o aggiornamento. Testate casi vuoti, valori molto frequenti, filtri rari, prime e ultime pagine e autorizzazioni che modificano l'ambito dei dati.
Distribuire le modifiche in modo osservabile e reversibile
Separate, quando possibile, il deployment del codice dalla creazione dell'indice. La creazione di indici può competere per le risorse o acquisire blocchi a seconda del motore, dell'operazione e dell'ambiente. Pianificate il momento, verificate il metodo supportato dal vostro database e monitorate durata, errori e pressione sull'I/O.
Introducete gradualmente la nuova query se l'architettura lo consente e mantenete una procedura di rollback chiara: ripristinare la query precedente, disattivare un percorso di accesso alternativo o rimuovere un indice che si dimostri dannoso. Un rollback non sostituisce i backup né la revisione delle migrazioni, ma riduce il tempo di esposizione in caso di comportamento inatteso.
Checklist per un miglioramento ripetibile
- Identificate la route, il sintomo e i parametri che lo riproducono.
- Separate il tempo di PHP, database, rete e dipendenze esterne.
- Quantificate ripetizioni, righe restituite e righe elaborate.
- Cercate N+1, paginazione profonda, filtri poco selettivi e ordinamenti.
- Rivedete il piano e confrontatene le stime con l'esecuzione reale.
- Provate riduzione dei dati, riscrittura e modifica della paginazione prima di indicizzare.
- Progettate l'indice in base al pattern completo di filtro, ordinamento e scrittura.
- Convalidate letture e scritture con carico rappresentativo, osservabilità e rollback.
