Come identificare le query SQL lente nei negozi PrestaShop con un elevato traffico (tabelle Carrello e Ordini)

Pagine prodotto lente, checkout incerto, timeout dell'elenco ordini nel back office. Nove volte su dieci la causa è il database, non PHP. Ciò che rende prevedibili i negozi online più frequentati è dove si concentra il danno: le query SQL lente in PrestaShop si concentrano attorno a quattro tabelle che aggiungono righe ogni giorno e non ne eliminano mai, ovvero ps_cart, ps_cart_product, ps_orders e ps_order_detail. Quattro semplici passaggi permettono di individuarle. Attiva il log delle query lente, ordina i dati registrati in base al tempo totale impiegato, esegui EXPLAIN sulle voci più problematiche e infine modifica la query o l'indice. Niente tentativi a vuoto. Ogni sezione seguente illustra un passaggio, insieme ai modelli che penalizzano maggiormente i dati del carrello e degli ordini.

1.0 Perché i carrelli e le tabelle degli ordini diventano il collo di bottiglia più grande

Il tuo catalogo si muove a malapena. Lo indicizzi una volta e le sue prestazioni rimangono pressoché invariate, che tu venda 1,000 SKU o 50,000. I dati del carrello e degli ordini, invece, si comportano in modo completamente diverso, poiché sono in continua crescita.

Con l'aumento del traffico, si accumulano quattro fattori:

1. File che non se ne vanno mai. Quasi ogni visitatore che tocca il carrello guadagna una riga in ps_cart, bot inclusi. Nulla fa scadere queste righe, quindi ps_cart e ps_cart_product continuano a crescere anche dopo la chiusura delle sessioni.
2. Indici che puntano nella direzione sbagliata. Le chiavi principali privilegiano le ricerche tramite id_cart o id_order, mentre i report, i moduli e i filtri amministrativi tendono a cercare per prodotto, intervallo di date o stato.
3. Cicli al posto degli insiemi. Le regole del carrello, le promozioni e gli hook dei moduli gestiti in modo approssimativo trattano un solo articolo alla volta, trasformando la visualizzazione di una singola pagina in decine di passaggi di andata e ritorno.
4. Anche il Back Office si unisce a questa estensione. Le schermate degli ordini collegano clienti, stati, corrieri, paesi e negozi, quindi ordinano e impaginano i risultati.

Le schermate di checkout e di amministrazione si aggiornano prima del catalogo. Usate questo come indicatore. Le pagine prodotto rapide, accanto a un elenco ordini in continuo aggiornamento, puntano direttamente al carrello e alle tabelle degli ordini.

2.0 Inizia con il log delle query lente di PrestaShop.

Inizia da qui. Il registro riporta ciò che il tuo server ha effettivamente eseguito sotto traffico reale, non ciò che presumi, e nessun altro strumento ti offre questa informazione gratuitamente.

3.0 Scegli una soglia adatta al negozio

Di default, `long_query_time` è impostato a 10 secondi. Per il settore retail, questo valore è irrilevante. Una query del carrello da 900 ms rimane completamente invisibile, mentre silenziosamente incide negativamente sulle conversioni. Riducete questo valore e, già che ci siete, intercettate anche le query senza indice.

# Esempio di impostazioni my.cnf: perfezionale prima di applicarle in ambienti di produzione.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/shop-slow-queries.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000
log_slow_admin_statements = 1

Inizia con 0.5 secondi. Abbassa a 0.1 dopo aver eliminato i casi più evidenti. Fai attenzione a un effetto collaterale: log_queries_not_using_indexes intercetta anche le letture banali delle tabelle di configurazione, e min_examined_row_limit è ciò che mantiene il file leggibile una volta che ciò accade.

Anche le modifiche in fase di esecuzione funzionano, con una precisazione. `long_query_time` si applica alle nuove connessioni, quindi i worker di PHP-FPM che utilizzano connessioni persistenti mantengono la vecchia soglia fino al loro riciclo. La documentazione del log delle query lente di MySQL elenca tutte le variabili correlate.

-- Abilitazione in fase di esecuzione. Influisce solo sulle nuove connessioni.
IMPOSTA GLOBALE slow_query_log = 'ON';
IMPOSTA GLOBALE long_query_time = 0.5;

Utilizzate MariaDB, come molti provider PrestaShop? Aggiungete `log_slow_verbosity = query_plan,explain`. In questo modo, ogni voce verrà visualizzata con il relativo piano di query allegato, risparmiandovi un'analisi successiva.

4.0 Trasforma il log grezzo in un elenco classificato

I log non elaborati sono inutilizzabili in grandi quantità, quindi è necessario aggregarli tramite l'impronta digitale della query. mysqldumpslow è incluso in MySQL e non richiede installazione:

mysqldumpslow -st -t 20 /var/log/mysql/shop-slow-queries.log # ordinato per tempo totale
mysqldumpslow -sc -t 20 /var/log/mysql/shop-slow-queries.log # ordinato per conteggio chiamate

La funzione pt-query-digest di Percona Toolkit svolge correttamente il suo compito. I valori letterali vengono compressi in impronte digitali, le voci vengono classificate in base al tempo totale impiegato e ognuna di esse riporta il numero di righe esaminate rispetto a quelle restituite. Prestate attenzione a quest'ultima coppia. Nessun altro strumento indica un indice mancante in modo così evidente.

Ecco dove i team sbagliano. Leggete il report in base al tempo totale impiegato, non al caso peggiore. Un report di tre secondi che viene eseguito due volte al giorno costa molto meno di una query del carrello di 90 ms che si attiva quaranta volte per ogni transazione. Eppure la query problematica viene corretta, mentre quella dispendiosa in termini di tempo rimane.

Il log presenta un punto cieco. Una raffica di query singole e veloci non supera mai il valore di long_query_time, quindi non viene scritto nulla. È possibile intercettarle utilizzando la profilazione di debug di PrestaShop su una copia di staging: _PS_DEBUG_PROFILING_ visualizza un pannello con il conteggio delle query per ogni pagina. Centinaia di query su una pagina del carrello indicano che qualcosa è in un ciclo. Non significa che una singola istruzione sia lenta.

5.0 Leggi il piano con MySQL EXPLAIN

Una volta stilata la lista ristretta, chiedete all'ottimizzatore cosa intende fare. ESPRIMETE le stime e nient'altro. Non esegue mai l'istruzione, motivo per cui l'esecuzione sull'ambiente di produzione rimane sicura.

SPIEGARE
SELECT o.id_order, o.reference, o.total_paid, c.lastname
DA ps_orders o
JOIN ps_customer c ON c.id_customer = o.id_customer
DOVE o.date_add >= '2026-01-01 00:00:00'
  E o.current_state = 3
ORDINA PER o.date_add DESC
LIMITE 50;

La maggior parte del significato è racchiusa in quattro colonne:

1. Digitare – il metodo di accesso. const, eq_ref e ref sono corretti. range e index sono accettabili. ALL significa una scansione completa della tabella e, su un ps_orders di grandi dimensioni, quel singolo valore è solitamente il risultato.
2. chiave – l'indice effettivamente scelto. Quando possible_keys elenca i candidati ma la chiave mostra NULL, l'ottimizzatore li ha rifiutati, spesso perché una funzione incapsula la colonna filtrata.
3. righe e filtrate – il volume stimato di dati che MySQL deve elaborare. Un valore elevato di righe combinato con una bassa percentuale di filtraggio significa che la query legge molti più dati di quanti ne restituisca.
4. Extra – gli avvisi. L'utilizzo di filesort e di temporary significa che nessun indice soddisfa l'ordinamento. L'utilizzo di index è il caso ideale: un indice di copertura risponde alla query senza modificare la tabella.

La documentazione di riferimento di MySQL per EXPLAIN descrive ogni valore, e la guida di PlanetScale alla lettura dell'output di EXPLAIN offre un utile modello mentale: considera i tipi di accesso come un semaforo e lavora prima su quelli rossi.

6.0 Dove SPIEGARE ANALIZZARE si guadagna il suo posto

Dalla versione 8.0.18 in poi, è disponibile anche EXPLAIN ANALYZE. Questa variante esegue la query e stampa i tempi misurati e il numero di iterazioni del ciclo accanto alle stime, il che è di grande aiuto quando un piano di esecuzione è leggibile ma la query è ancora lenta. Tieni presente che viene eseguita. Limitala alle query SELECT in sola lettura durante le ore di minore attività.

MariaDB offre una funzionalità simile con ANALYZE SELECT, che mostra il numero effettivo di righe accanto a quello previsto. Un'ampia differenza tra i due valori di solito indica statistiche obsolete. Eseguite ANALYZE TABLE, ricontrollate i risultati e decidete in seguito in merito all'indice.

7.0 Schemi comuni di query lente nei dati del carrello e degli ordini

Quattro modelli coprono quasi tutto ciò che si presenta qui. Ognuno richiede il proprio rimedio, quindi individua quale modello ti riguarda prima di toccare lo schema.

7.1 Ricerca lato prodotto scansiona ps_cart_product

Prima di dare per scontato qualsiasi cosa, verifica lo schema del tuo sistema, poiché le installazioni aggiornate spesso differiscono da quelle pulite:

SHOW CREATE TABLE ps_cart_product;
MOSTRA L'INDICE DA ps_cart_product;

Nello schema attuale, la chiave primaria di ps_cart_product è in testa con id_cart. Le letture lato carrello vengono quindi già eseguite come ricerche nell'indice e l'aggiunta di un altro indice (id_cart, id_product) non fa altro che rallentare le scritture.

I problemi iniziano nella direzione opposta. MySQL legge un indice dalla colonna più a sinistra, quindi una chiave che inizia con id_cart non può servire una query che filtra solo in base a id_product. Gli strumenti di recupero dei carrelli abbandonati, i widget di monitoraggio della pressione delle scorte e le eliminazioni di massa dei prodotti filtrano tutti esattamente in questo modo e finiscono tutti per scansionare la tabella.

-- Esegue la scansione di ps_cart_product quando nessun indice inizia con la colonna del prodotto.
SELEZIONA COUNT(DISTINCT cp.id_cart)
FROM ps_cart_product cp
JOIN ps_cart c ON c.id_cart = cp.id_cart
DOVE cp.id_product = 4821
  E c.date_upd > DATE_SUB(NOW(), INTERVAL 7 DAY);

L'ottimizzazione della tabella del carrello di PrestaShop si riduce a questa distinzione. Indicizza ciò che i tuoi moduli interrogano effettivamente e lascia inalterato ciò che il core gestisce già.

7.2 Le regole del carrello attivano cicli di query N+1

La logica delle promozioni gestisce le regole una alla volta. PrestaShop raccoglie le regole del carrello applicabili, calcola ciascun valore a turno e ogni valutazione verifica le proprie restrizioni rispetto alle tabelle relative a prodotto, categoria, corriere, gruppo e paese. Dieci regole attive comportano quindi dieci valutazioni, oltre alle relative ricerche annidate, ad ogni rendering del carrello e ad ogni aggiornamento AJAX.

Ogni query rimane veloce, quindi il log delle query lente non le segnala mai. Il profiler, invece, rivela lo schema: il numero di query aumenta di pari passo con il numero di regole attive.

Risolvete il problema alla fonte anziché tramite gli indici. Disattivate le regole scadute invece di lasciarle attive con una data di scadenza passata, unite le promozioni sovrapposte e preferite regole specifiche a quelle valide per l'intero catalogo e con lunghe liste di restrizioni.

7.3 I filtri data nei report ordinati eliminano gli indici

Inserire una colonna in una funzione rende il suo indice inutilizzabile. Questo accade di continuo nelle dashboard personalizzate e nelle esportazioni CSV.

-- Lento: la funzione blocca l'accesso all'indice.
SELECT COUNT(*) FROM ps_orders WHERE DATE(date_add) = '2026-03-14';

-- Veloce: un range semiaperto mantiene l'indice in gioco.
SELECT COUNT(*) FROM ps_orders
DOVE date_add >= '2026-03-14 00:00:00'
  E date_add < '2026-03-15 00:00:00';

I filtri YEAR(date_add) e il raggruppamento DATE_FORMAT() condividono lo stesso difetto. Riscriveteli come intervalli ovunque abbiate il controllo del codice. Quando ciò non è possibile, ad esempio all'interno di un modulo closed-source, MySQL 8.0.13 e versioni successive supportano parti di chiave funzionale, consentendo di indicizzare l'espressione stessa. MariaDB non ha un equivalente, sebbene una colonna generata indicizzata ottenga lo stesso risultato.

7.4 Le schermate degli ordini del back office si uniscono troppo

L'elenco degli ordini dell'amministratore unisce ps_orders a ps_customer, alle tabelle dello stato e della traduzione degli ordini e alle tabelle del corriere, del paese e del negozio. Successivamente, concatena i nomi per la ricerca, applica un filtro LIKE, ordina e impagina.

Da ciò derivano due problemi. Il filtraggio o l'ordinamento su una colonna unita o tradotta, che si tratti di nome cliente, etichetta di stato o paese, non può utilizzare un indice su ps_orders, quindi MySQL assembla prima il set unito e poi lo ordina. Peggio ancora, il costo tiene traccia del numero totale di ordini anziché della dimensione della pagina, il che spiega perché la visualizzazione peggiora man mano che il negozio cresce.

Alcune misure di mitigazione non costano nulla: ridurre le dimensioni predefinite della pagina, mantenere il filtro di stato restrittivo e formare il personale a effettuare ricerche tramite riferimento d'ordine anziché sfogliare le pagine alla cieca.

7.4 Applica correzioni sicure: una strategia di indicizzazione che resiste agli aggiornamenti

Gli indici vengono per ultimi, mai per primi. Questa è la sequenza che regge:

1. Riscrivi tutto il codice SQL di cui sei proprietario. Rimuovi le funzioni dalle colonne filtrate ed elimina le join che nessuno legge.
2. Riduci il carico di lavoro. Elimina le regole per i carrelli inattivi, inasprisci i filtri amministrativi.
3. Aggiorna le statistiche utilizzando ANALYZE TABLE, quindi esegui EXPLAIN una seconda volta.
4. Aggiungi un singolo indice. Misuralo prima e dopo.

Ogni indice rallenta le scritture e consuma spazio nel buffer pool, e le tabelle del carrello assorbono un traffico di scrittura elevato. Quindi, aggiungeteli con cautela:

-- Esempi. Eseguire prima un test su una copia ripristinata della produzione e confermare con
-- MOSTRA INDICE che non esiste già un indice equivalente.
CREATE INDEX idx_cp_product_cart ON ps_cart_product (id_product, id_cart);
CREATE INDEX idx_cart_dateupd_shop ON ps_cart (date_upd, id_shop);

Alcune regole mantengono sicura l'indicizzazione della tabella degli ordini di PrestaShop durante gli aggiornamenti del core e dei moduli:

• Mai Modificare una chiave primaria o aggiungere un vincolo UNIQUE a una tabella principale. Il codice principale dipende dalla struttura della chiave esistente e un indice UNIQUE può impedire inserimenti legittimi durante il checkout.
• Mai Ho eliminato un indice principale perché una query di test lo ha ignorato. Quasi certamente un altro controller ne dipende.
• Aggiungi un prefisso distintivo ai nomi degli indici personalizzati, in modo che lo sviluppatore successivo possa distinguerli da quelli del core.
• Mantieni ogni indice personalizzato in una migrazione versionata con uno script di rollback, quindi riverifica dopo ogni aggiornamento con SHOW INDEX.
• Durante una finestra di manutenzione, è consigliabile creare indici sulle tabelle di grandi dimensioni oppure utilizzare uno strumento online per la modifica dello schema, poiché un'istruzione ALTER TABLE bloccante può interrompere le versioni in produzione.

8.0 Elimina e archivia i vecchi dati del carrello prima di aggiungere l'hardware.

Gli indici sono utili, ma ridurre il set di lavoro spesso è più efficace. I carrelli abbandonati rappresentano la maggior parte del peso morto, quindi inventariateli per primi:

-- Sola lettura. Quanto è superfluo il contenuto di ps_cart?
SELEZIONA COUNT(*) COME carrelli abbandonati
DA ps_cart c
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
DOVE o.id_order È NULL
  E c.date_upd < DATE_SUB(NOW(), INTERVAL 12 MONTH);

Alcune regole non sono negoziabili. Non eliminare mai un carrello a cui è associato un ordine, poiché le schermate degli ordini e la rigenerazione delle fatture continuano a leggere quei dati. La condizione LEFT JOIN … IS NULL menzionata sopra è ciò che ti protegge. Elimina le righe figlie prima delle righe principali, inclusa ps_cart_product, oltre a qualsiasi tabella modulo con chiave id_cart. Esegui prima un backup, fai una prova su una copia ripristinata, quindi esegui le eliminazioni in produzione in piccoli lotti anziché in un'unica lunga transazione durante l'orario di lavoro.

Conservate gli ordini stessi. Le norme di conservazione delle fatture e dei documenti fiscali variano a seconda della giurisdizione, quindi è preferibile archiviarli piuttosto che eliminarli quando è necessario ridurre la quantità di documenti.

Le tabelle si riempiono automaticamente, il che significa che una singola operazione di pulizia ti garantisce al massimo mesi di tempo. Scrivi uno script per la pulizia, includilo nel controllo dell'ordine di cui sopra e affida il compito al tuo pianificatore. Digitare manualmente le eliminazioni di produzione alle 2 del mattino è il modo in cui si verificano gli errori. Concludi con ANALIZZA TABELLA in modo che l'ottimizzatore apprenda la nuova distribuzione delle righe.

9.0 Continuate a monitorare la situazione, altrimenti le prestazioni del database di PrestaShop potrebbero peggiorare.

Le regressioni arrivano silenziosamente. Un aggiornamento di un modulo include una query eseguita con poca attenzione, nessuno se ne accorge e le richieste di assistenza arrivano una settimana dopo. I controlli di routine sono sempre più efficaci della gestione degli incidenti.

• Programmare il riassunto. Esegui pt-query-digest settimanalmente sui log lenti ruotati e confronta le impronte digitali di settimana in settimana. Le nuove voci mettono in evidenza le regressioni in anticipo.
• Interroga le tabelle di digest. Lo schema Performance di MySQL aggrega ogni istruzione in modo continuo, e lo schema sys espone viste leggibili come statement_analysis e statements_with_full_table_scans.
• Attribuisci le query al codice. Uno strumento APM ti indica quale hook, controller o modulo ha emesso una query. Il log delle query lente identifica l'istruzione; APM identifica il codice da correggere.
• La scala si legge separatamente. Il sistema proprietario di PrestaShop documentazione di scalabilità Si consiglia una singola istanza di scrittura con repliche di lettura, poiché il front office genera molte più operazioni di lettura che di scrittura. Si suggerisce inoltre di analizzare le tabelle dopo importazioni di grandi dimensioni, in modo che l'ottimizzatore lavori sulla base delle statistiche correnti.
• Lascia spazio al buffer pool. Se il database possiede il proprio server, aumenta `innodb_buffer_pool_size` finché non raggiunge la dimensione massima del set di lavoro che ti puoi permettere. Se lo lasci troppo piccolo, un negozio PrestaShop con un traffico elevato raggiungerà il limite massimo in pochi giorni.

10.0 Conclusione

Individuare le query SQL lente in PrestaShop è un lavoro metodico, non un'indagine improvvisata. Registra i log con una soglia ragionevole, ordinali per tempo totale ed esegui EXPLAIN su ciò che si trova in cima alla lista. Quindi, analizza il piano di esecuzione senza edulcorare la situazione. Un tipo ALL, una chiave NULL o l'utilizzo di filesort in Extra indicano ciascuno una possibile soluzione, come una riscrittura o un indice.

Dopodiché, concentratevi sui dati stessi. Eliminate i carrelli abbandonati, archiviate i vecchi record, verificate i miglioramenti con query reali anziché con test sintetici e programmate i controlli in modo che le regressioni emergano prima che i clienti le trovino. I negozi che seguono questo ciclo mantengono la procedura di checkout reattiva anche con l'aumento del volume degli ordini e il back office rimane utilizzabile su larga scala.

Se dovete fare una sola cosa questa settimana, abilitate il registro delle query lente sul vostro negozio più trafficato e leggete le prime dieci voci. Quel singolo elenco di solito vi indica dove concentrare gli sforzi sul database per il mese successivo.

Noi di Knowband aiutiamo i proprietari di negozi PrestaShop a migliorare le prestazioni, la scalabilità e la stabilità attraverso plugin PrestaShop affidabili, lo sviluppo di funzionalità personalizzate per il sito web e lo sviluppo di app.

Hai bisogno di assistenza? Contattaci all'indirizzo [email protected] o visita l' Helpdesk di Knowband.