Rendere efficace e veloce il dialogo tra MySQL e PHP: guida completa alle performance

Sintomi tipici di un dialogo PHP-MySQL lento

Prima di mettere mano al codice, conviene riconoscere i segnali più comuni:

• TTFB alto (il server impiega tanto a “rispondere” prima ancora di inviare HTML).
• Pagine admin che “si piantano” quando carichi liste, ordini o report.
• CPU alta sul database durante picchi o anche a traffico medio.
• Molti “micro-lag” dovuti a tantissime query piccole.
• Query lente sporadiche ma devastanti (es. una ricerca senza indici).
• Tempo di risposta che peggiora linearmente con la quantità di dati (scalabilità assente).

Nella pratica: se la tua applicazione fa 200 query per una pagina, anche se ognuna impiega 5 ms,
sei già a 1 secondo solo di database (senza contare rete, PHP, rendering).
L’obiettivo realistico è ridurre il numero di query e rendere le query principali “index-friendly”.

Prima misura, poi ottimizza: strumenti indispensabili

L’ottimizzazione senza misurazione è tempo sprecato. Ti servono strumenti che dicano:
quali query, quanto impiegano, quante righe leggono e perché.

1) Slow Query Log

Attiva il slow query log su MySQL: ti mostra le query sopra una certa soglia (es. 0.2s o 0.5s).
È il modo più rapido per scovare i colpevoli reali.

2) EXPLAIN / EXPLAIN ANALYZE

Ogni query “importante” va letta con EXPLAIN per capire se usa indici, quante righe stima, se fa scansioni complete, ecc.

3) Profilazione lato PHP

Usa un profiler o almeno logging:
salva tempo di inizio e fine di ogni query, conta quante query fai per richiesta, e annota i punti caldi (hotspot).
Spesso scopri che la lentezza è in un loop che richiama MySQL 200 volte.

4) Metriche di sistema

Se puoi, monitora:
CPU/IO del DB, connessioni, buffer pool, cache hit ratio, lock.
Ti aiuta a capire se il problema è query, I/O su disco o concorrenza.

Connessione: PDO, mysqli, charset, errori e modalità

La connessione è il primo collo di bottiglia “sottovalutato”.
Se apri e chiudi connessioni di continuo o gestisci male charset e modalità, perdi tempo e rischi bug.

PDO o mysqli?

Entrambi vanno bene, ma in progetti moderni si usa spesso PDO per coerenza e flessibilità.
In ogni caso, il punto non è “quale libreria”, ma come la usi:
prepared statements, error mode, fetch mode, e soprattutto: una connessione ben gestita per richiesta.

Imposta charset e collazione corretti

Se lavori in italiano e vuoi compatibilità completa, usa utf8mb4.
Una configurazione coerente evita conversioni interne e problemi in join/ordinamenti.

Evita connessioni ripetute

In molte codebase legacy, ogni funzione apre la sua connessione: è un disastro.
La regola pratica: una connessione per richiesta (o un pool/persistente gestito bene), condivisa dove serve.

Gestione errori: non nascondere, normalizzare

Se MySQL fallisce e tu “ignori”, finisci con retry, query duplicate, fallback lenti o pagine bianche.
Gestisci gli errori in modo consistente: log dettagliato + messaggio utente pulito.

Query più veloci: regole d’oro che fanno la differenza

1) Seleziona solo ciò che serve

SELECT * è comodo, ma spesso è spreco:
più dati trasferiti, più memoria in PHP, più lavoro su MySQL.
Se ti servono 5 colonne, seleziona 5 colonne.

2) Filtra prima, ordina dopo (e sempre con indici)

Una query lenta tipica:
filtri generici, ordine su campo non indicizzato, limit alto.
Il pattern vincente: WHERE su colonne indicizzate e ORDER BY su indici coerenti.

3) LIMIT intelligente

Se mostri liste, limita righe e campi.
Se devi calcolare un conteggio totale, fallo separatamente e (se possibile) cached.

4) Evita funzioni su colonne in WHERE

Esempio: WHERE LOWER(email) = '...' spesso disabilita l’uso dell’indice.
Meglio normalizzare i dati (es. salvare email lowercase) o usare collazioni case-insensitive.

5) LIKE e ricerche: attenzione alle wildcard iniziali

LIKE '%test%' di solito non usa indice. Se puoi,
preferisci LIKE 'test%' oppure un indice FULLTEXT (quando appropriato).

6) Join: poche, mirate, con chiavi indicizzate

Le join sono potentissime, ma costose se fatte male.
Assicurati che le colonne di join siano indicizzate (FK, ID, campi usati per relazione).
Evita join su campi “testuali” lunghi o non normalizzati.

7) Query duplicate: il killer silenzioso

Se la stessa query gira 20 volte per la stessa pagina (stessi parametri), stai buttando via tempo.
Soluzione: caching a livello applicativo e miglioramento della logica.

Indici MySQL: cosa sono e come usarli bene

Gli indici sono spesso la differenza tra una query da 2 secondi e una da 20 millisecondi.
Ma vanno progettati: un indice “a caso” può non servire o addirittura rallentare scritture.

Indici singoli vs composti

Se filtri spesso per status e ordini per created_at, un indice composto
(status, created_at) può aiutare molto.
La regola pratica: costruisci indici in base a WHERE + ORDER BY delle query reali.

Non indicizzare tutto

Ogni indice costa: spazio, RAM e tempo di scrittura (INSERT/UPDATE/DELETE).
Indica solo ciò che serve davvero nelle query più frequenti e più lente.

Indice su colonna a bassa cardinalità?

Campi come status con pochi valori (0/1) a volte non aiutano da soli.
Spesso funzionano meglio in indice composto con un campo più selettivo (data, id, categoria).

EXPLAIN e piani di esecuzione: leggere la mente di MySQL

EXPLAIN ti dice come MySQL eseguirà la query: quali tabelle, che tipo di accesso, quali indici, quante righe stima.
Imparare a leggere EXPLAIN è uno dei moltiplicatori di performance più importanti.

• type: se vedi ALL, spesso è scansione completa (da evitare su tabelle grandi).
• key: l’indice usato. Se è NULL, non sta usando indici.
• rows: righe stimate. Se sono enormi, stai leggendo troppo.
• Extra: occhio a Using filesort e Using temporary (spesso sintomi di ORDER BY non indicizzato o GROUP BY pesante).

Strategia rapida: prendi le 10 query più lente dal slow log, fai EXPLAIN, poi aggiusta indici o riscrivi query.

N+1 query e loop: come eliminare la lentezza “invisibile”

Il pattern più comune in PHP:
prendi una lista di elementi (N) e dentro il loop fai una query per ogni elemento.
Risultato: 1 query per la lista + N query per dettagli = N+1.
Con N=100 hai 101 query… per una sola pagina.

Soluzioni pratiche

• Join: recupera già i dati collegati in un’unica query (se ha senso).
• IN(): recupera i dettagli in blocco: WHERE id IN (...).
• Precaricamento: carica mappa id => dati e poi usa in memoria in PHP.
• Caching: se alcuni dettagli cambiano raramente, cache per chiave.

Eliminare N+1 spesso dà un boost enorme: meno roundtrip, meno parsing, meno lock, meno overhead PHP.

Paginazione efficiente: evitare OFFSET pesanti

La paginazione classica:
ORDER BY created_at DESC LIMIT 20 OFFSET 20000
diventa lenta quando OFFSET cresce: MySQL deve “saltare” molte righe.

Keyset pagination (la soluzione scalabile)

Invece di OFFSET, usa l’ultimo valore visto:
WHERE created_at < :last_created_at ORDER BY created_at DESC LIMIT 20.
È molto più veloce su tabelle grandi, soprattutto con indice su created_at (o composto).

Conteggi: attenzione ai COUNT(*)

Fare COUNT(*) su tabelle grandi a ogni pagina è spesso un errore.
Se puoi: cache del conteggio, conteggio approssimato, o “mostra più risultati” senza totale preciso.

Caching: da “utile” a “obbligatorio”

Se generi sempre gli stessi risultati (categorie, filtri, pagine con traffico alto),
il caching è ciò che evita di martellare MySQL.

Livelli di caching

• Query cache applicativa: cache in PHP (file, Redis, Memcached) per il risultato di query “ripetitive”.
• Object cache: cache di entità (es. prodotto, utente, categoria) per chiave.
• Page cache: se alcune pagine sono pubbliche e poco dinamiche, una cache full-page è potentissima.
• HTTP cache: ETag, Cache-Control e reverse proxy (dove applicabile).

Cache invalida bene o diventa un problema

Il caching funziona solo se sai quando invalidare:
quando un prodotto cambia prezzo, devi invalidare la cache di quel prodotto e delle liste collegate (se necessario).
Il trucco è non essere “perfetto” ovunque, ma ottimizzare le aree ad alto traffico.

Transazioni e scritture: velocità + coerenza

In scrittura, l’errore tipico è fare molte query separate senza transazione:
se qualcosa fallisce a metà, hai dati incoerenti e magari retry che peggiorano le performance.

Quando usare una transazione

• Ordini e pagamenti
• Movimenti di magazzino
• Operazioni “multi-tabella” che devono essere atomiche

Una transazione ben gestita riduce anche overhead e lock “casuali”, perché raggruppa le operazioni in un perimetro chiaro.

Batch insert/update

Se devi inserire 1000 righe, evita 1000 INSERT singoli: preferisci batch (quando possibile) o almeno transazioni con commit unico.

Schema e tipi: scelte che impattano più del codice

Puoi ottimizzare PHP quanto vuoi, ma se lo schema è “sbagliato”, MySQL soffrirà sempre.
Alcuni punti chiave:

Tipi corretti

• Usa INT per ID e chiavi (meglio se UNSIGNED quando sensato).
• Evita VARCHAR enormi “per sicurezza”.
• Usa DATETIME/TIMESTAMP per date e ordini temporali.
• Normalizza dove serve, ma senza fanatismo: la normalizzazione eccessiva crea join inutili.

Indici coerenti con il modello

Se hai tabelle di relazione (molti-a-molti), gli indici sulle colonne di relazione sono essenziali.
Esempio: product_id e tag_id indicizzati in una tabella ponte.

Partizionamento e archiviazione

Se hai log o tabelle che crescono all’infinito (eventi, tracking, cronologie),
valuta archiviazione periodica o partizionamento (nei casi sensati).
Meno dati “caldi” = query più rapide.

Sicurezza e performance: prepared statements, injection e overhead

Sicurezza e performance vanno spesso insieme.
I prepared statements non servono solo a evitare SQL injection: spesso migliorano anche la stabilità e riducono errori.

Prepared statements sempre

Mai concatenare input direttamente in SQL.
Oltre al rischio enorme, finisci con query “sporche”, difficile caching e bug.

Sanitizzazione: fallo nel posto giusto

Non duplicare sanitizzazione ovunque. Normalizza in input (PHP) e poi usa binding parametri.
Evita mysql_real_escape_string (legacy) e sostituiscilo con binding.

Riduci l’esposizione del DB

Un DB sotto attacco (anche “soft”, come bot e scraping) è un DB lento.
Rate limit, caching, e query ottimizzate proteggono anche le performance.

Checklist finale e piano di lavoro

Se vuoi un percorso pratico (anche su progetti legacy), segui questa sequenza:

1. Attiva slow log e raccogli le query più lente e frequenti.
2. Conta quante query fai per pagina (e dove).
3. Elimina N+1 e query duplicate: è spesso il boost più grande.
4. EXPLAIN sulle query principali, aggiusta indici e riscrivi query.
5. Riduci payload: meno colonne, meno righe, paginazione corretta.
6. Cache su liste, lookup ripetuti, pagine pubbliche e risultati pesanti.
7. Rivedi schema: tipi, indici composti, tabelle ponte.
8. Ottimizza scritture: transazioni, batch, riduzione lock.
9. Monitora: CPU/IO/connessioni, e ripeti il ciclo.

Con questa checklist, anche un progetto “stanco” migliora sensibilmente senza dover riscrivere tutto.
La chiave è sempre la stessa: misura → interveni sui colli di bottiglia → misura di nuovo.

FAQ: domande frequenti

Quante query dovrebbe fare una pagina “veloce”?

Non esiste un numero magico, ma come riferimento:
una pagina ben ottimizzata spesso sta tra 10 e 50 query,
mentre pagine che superano 150-200 iniziano quasi sempre a soffrire.
Se hai caching e query rapide, puoi fare anche di più, ma l’obiettivo resta ridurre roundtrip inutili.

Meglio ottimizzare MySQL o PHP?

Quasi sempre inizi da query + indici (MySQL), perché un indice giusto può dare un boost enorme.
Subito dopo, lavora su riduzione query (N+1) e caching in PHP.
Ottimizzare “micro” PHP senza sistemare query lente è raramente efficace.

Il caching non rischia di mostrare dati vecchi?

Sì, se lo fai male. Ma puoi gestire bene con:
TTL breve sulle parti dinamiche, invalidazione mirata su update, e caching per segmenti (non tutto o niente).
In molti casi, avere dati aggiornati “entro 30-60 secondi” è accettabile e salva il DB.

OFFSET è sempre sbagliato?

No: su tabelle piccole va benissimo.
Diventa un problema quando hai dataset grandi e pagine profonde.
In quei casi, la keyset pagination è più scalabile.

Gli indici rallentano gli INSERT?

Sì, perché a ogni scrittura MySQL deve aggiornare gli indici.
Per questo non si indicizza tutto: si indicizzano le colonne usate davvero nelle query critiche.

Prepared statements sono davvero necessari anche se “mi fido” degli input?

Sì. Perché non è solo “fidarsi”: basta un bug, una route esposta, un endpoint AJAX,
un campo non validato, e hai un rischio enorme. Inoltre i prepared rendono il codice più pulito e coerente.