Vai al contenuto 
Grazie alla traccia dell'ottimizzatore in MariaDB riesco a capire, passo dopo passo, perché l'ottimizzatore sceglie un determinato piano e quali varianti scarta. Questa traccia JSON mi mostra Decisioni su costi, sequenze di join e filtri, in modo da poter personalizzare le query SQL in modo mirato.
Punti centrali
- Trasparenza: Una traccia basata su JSON illustra le riscritture, i costi e i piani scartati.
- Focus: join_preparation e join_optimization forniscono le informazioni più importanti.
- Sistema di controllo: Le variabili di sessione riducono il sovraccarico e l'utilizzo della memoria.
- Flusso di lavoro: EXPLAIN/ANALYZE per il piano, Trace per il „perché“.
- Vantaggi pratici: Personalizzare in modo mirato indici, statistiche e sequenze di join.
Che cos'è la traccia dell'ottimizzatore di MariaDB?
A partire dalla versione 10.4, MariaDB implementa un Ottimizzatore Trace, che documenta in formato JSON ogni fase di ottimizzazione significativa di un’istruzione SELECT, UPDATE o DELETE. In questo modo posso vedere come il motore espanda le query, normalizzi le condizioni e, infine, determini l’ordine dei join, compresi gli accessi agli indici. Questa visione va ben oltre EXPLAIN, che mostra principalmente il piano finale, e rivela le alternative scartate con le relative motivazioni. La traccia è presente in memoria per ogni connessione ed è disponibile tramite information_schema.OPTIMIZER_TRACE pronto. In questo modo ottengo una descrizione completa e leggibile da un computer delle strutture interne Passi, che hanno portato alla definizione di un piano esecutivo.
Attivare e leggere la traccia dell’ottimizzatore
Attivo questa funzione in modo mirato per ogni sessione, in modo da poter eseguire diagnosi senza un sovraccarico globale e avere il pieno controllo su Memoria ho. Di solito inserisco SET SESSION optimizer_trace = 'enabled=on'; e se necessario SET SESSION optimizer_trace_max_mem_size = 1048576; o superiore, se la traccia diventa troppo lunga. Dopodiché eseguo la query sospetta e leggo la traccia con SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Importante: la tabella memorizza solo l’ultima query della connessione attiva, e prendo in considerazione campi come MISSING_BYTES_BEYOND_MAX_MEM_SIZE oppure PRIVILEGI INSUFFICIENTI per indicazioni diagnostiche. Questo approccio mantiene snello l'ambiente di produzione e rende l'analisi accurata.
| Variabile/Campo | Scopo | Valore di esempio |
|---|---|---|
optimizer_trace | Attiva la traccia per sessione | 'enabled=on' |
optimizer_trace_max_mem_size | Memoria massima per traccia | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Istruzione SQL originale | SELECT ... |
OPTIMIZER_TRACE.TRACE | Documento JSON dell'ottimizzazione | Testo JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Byte troncati se la traccia è troppo lunga | 0 o numero |
PRIVILEGI INSUFFICIENTI | È sufficiente l'autorizzazione alla lettura? | 0 o 1 |
Struttura JSON: join_preparation e join_optimization
La struttura JSON è suddivisa nei seguenti blocchi join_preparation e ottimizzazione delle unioni, che esamino per prime perché sono le più importanti Note fornire. Nella sezione join_preparation riconosco la query estesa (expanded_query) e verifico se e in che modo il motore abbia modificato le condizioni o le proiezioni. Il secondo blocco ottimizzazione delle unioni registra le stime delle righe, i piani considerati, l’ordine di join scelto e l’aggiunta di parti WHERE selettive alle tabelle. Particolarmente utili sono i sottoalberi rows_estimation, piani_di_esecuzione_presunti e associare_condizioni_alle_tabelle, poiché rimandano direttamente alle ipotesi sui costi e alle posizioni dei filtri. In questo modo riesco a individuare rapidamente eventuali valutazioni errate o condizioni sfavorevoli Indici portare a piani non ottimali.
Ottimizzazione con EXPLAIN e ANALYZE
Per una valutazione completa, combino EXPLAIN, ANALYZE e il Traccia seguendo una procedura prestabilita. Per prima cosa utilizzo SPIEGARE oppure EXPLAIN FORMAT=JSON, per visualizzare il piano selezionato e i percorsi chiave. Dopodiché imposto SPIEGAZIONE ANALISI per ottenere dati reali sui tempi di esecuzione e valori di conteggio come i loop e le righe filtrate. Se rimangono dei dubbi, attivo l’Optimizer Trace e verifico quali varianti l’ottimizzatore ha valutato e scartato. Questo articolo su mi offre una breve introduzione all’interpretazione dei dati Comprendere EXPLAIN e ANALYZE, a cui ricorro come fonte integrativa quando necessario.
Comprendere le decisioni relative ai piani: costi, cardinalità, filtri
La logica decisionale si basa sulle cardinalità, sui modelli di costo e sul posizionamento di Filtro seguendo il piano. Nel trace vedo, per ogni sequenza di join considerata, quali insiemi di righe si aspetta il motore e come ne ricava il costo totale. Verifico se statistiche obsolete o correlazioni sfavorevoli portino a sottovalutare le scansioni di intervallo e a privilegiare le scansioni complete. Inoltre, verifico se il motore applica le condizioni WHERE alla tabella più selettiva con sufficiente anticipo, in modo da ridurre le costose fasi di join. In questo modo riesco a formulare conclusioni fondate sul motivo per cui è stato scelto un determinato piano e su come modificarlo con Indici, le riscritture o la gestione delle statistiche.
Esempio pratico: tracciamento di una semplice query di filtro
All'indirizzo SELECT * FROM t1 WHERE a < 10 verifico sotto join_preparation, se il motore abbia esteso la proiezione ed eventualmente consolidato le condizioni, il che mi ha dato una prima Indicatori fornisce. Dopodiché vedo nel blocco rows_estimation, quante righe il motore utilizza per il range scan su a rispetto alla scansione completa della tabella. Se si riscontrano valori non realistici, spesso lo interpreto come un segno di statistiche obsolete o di istogrammi mancanti. Nella sezione piani_di_esecuzione_presunti A quel punto capisco se l'accesso all'indice è stato effettivamente calcolato come più conveniente rispetto alla scansione completa. Infine, mostra associare_condizioni_alle_tabelle, se la condizione selettiva si applica a a è in anticipo rispetto al programma, il che riduce notevolmente la durata si abbassa.
Funzioni JSON: estrarre in modo mirato porzioni di dati
Poiché il trace è in formato JSON, filtro in modo mirato i sottoalberi con JSON_EXTRACT e realizzo piccole analisi per eventi ricorrenti Campione. Ad esempio, leggo solo l’elenco dei piani presi in considerazione per verificare se determinate sequenze di join falliscono sistematicamente. Allo stesso modo, estraggo i campi relativi ai costi dei candidati principali e li confronto con i dati di ANALYZE per individuare eventuali ipotesi errate. Tramite semplici viste o stored procedure automatizzo questi controlli per le mie sessioni di diagnosi. In questo modo mi costruisco un semplice Monitoraggio per le decisioni dell'ottimizzatore senza attivare il tracciamento permanente.
Casi d'uso tipici e vantaggi
Ricorro al trace quando EXPLAIN mostra una scansione completa inaspettata e voglio capire il motivo del rifiuto di un Indice voglio scoprire. Allo stesso modo, per molte tabelle, il trace mi fornisce la motivazione alla base dell'ordine di join scelto, indicandomi la strada verso piani alternativi. In caso di cambio di versione, salvo i trace prima e dopo l’aggiornamento per valutare le modifiche nel comportamento dell’ottimizzatore. Per le questioni strategiche relative al tuning, questa panoramica mi aiuta a meccanismi interni dell'ottimizzatore, che collego ai risultati di Trace. In questo modo decido in modo strutturato se apportare modifiche agli indici, alle statistiche o alla formulazione delle query vite di regolazione metto.
Migliori pratiche per la produzione
Attivo sistematicamente la traccia come Sessione-Chiudo e termino la diagnosi in modo corretto non appena ho raccolto dati sufficienti. Per le tracce di grandi dimensioni, aumento optimizer_trace_max_mem_size solo per un breve periodo, dopodiché reimpostato il valore su un livello basso. Prima di condividere i file JSON, nascondo le costanti sensibili, i testi dei commenti o gli indicatori aziendali. Utilizzo il trace in modo mirato come strumento di diagnostica, mentre per il monitoraggio continuo preferisco i log delle query lente, le viste sulle prestazioni o i profiler esterni. Questa disciplina mantiene i sistemi snelli e previene inutili Spese generali nel lavoro quotidiano.
Traccia dell'ottimizzatore nel mix di strumenti
Per un tuning olistico, riproduco la catena costituita dalla comprensione del progetto, dall’analisi delle cause e dalla misurazione del sistema e collego le Risultati. EXPLAIN mi mostra il piano, ANALYZE conferma i costi effettivi e la traccia fornisce le motivazioni alla base della decisione. Parallelmente, esamino i concetti relativi al piano di esecuzione delle query per individuare schemi nella scelta delle chiavi, nelle cardinalità e nelle strategie di join. Un’ottima integrazione a questa prospettiva è la panoramica sintetica su Piani di esecuzione delle query, a cui faccio riferimento per le questioni di architettura. Da ciò traggo conclusioni attendibili Priorità per il lavoro sull'indice, le riscritture e i parametri.
Approfondimento: analisi della gamma e scelta delle chiavi
Spesso nella traccia c'è un blocco analisi_intervallo per ogni tabella, in modo da poter individuare quali indici fossero idonei per gli accessi di tipo range, ref o eq-ref. L’ottimizzatore confronta alternative quali „range su idx_a“, „range su idx_b“ o „full scan“, assegna loro i costi e il numero di righe previsto e individua l’opzione vincente. Se noto che un indice sensato è stato scartato a causa dei costi elevati, esamino successivamente le selettività e le statistiche sottostanti. Se le ipotesi non sono corrette, un ANALIZZA TABELLA (eventualmente con statistiche persistenti) oppure la creazione di un Indice di copertura annullare la decisione.
È utile anche esaminare le suddivisioni degli indici composti: la traccia documenta se la condizione utilizza solo la prima colonna dell’indice o se sono applicabili predicati aggiuntivi e se entrano in gioco altre colonne chiave. Da ciò deduco se è opportuno riformulare i predicati (ad esempio evitando le funzioni) oppure estendere l’indice in modo da coprire i tipici filtri e ordinamenti.
I join in dettaglio: semi-join, BKA/MRR e buffer di join
Nelle query multi-tabella, le sezioni di traccia indicano se è stata presa in considerazione una strategia di semijoin e, in caso affermativo, quale (ad esempio FirstMatch, DuplicateWeedout, LooseScan o Materialization). Lì capisco perché una variante è stata scartata – ad esempio a causa di costi di materializzazione elevati o di una selettività troppo bassa. Inoltre Accesso a chiave batch (BKA) e Lettura multi-campo (MRR) compaiono nella traccia, se attivati. Queste tecniche raggruppano le ricerche delle chiavi e migliorano la località della cache. Se BKA/MRR non compaiono nella traccia, controllo optimizer_switch e parametri quali join_cache_level. Nei carichi di lavoro con numerose ricerche casuali di chiavi, è possibile accelerare sensibilmente la fase di join, come si può verificare con EXPLAIN ANALYZE.
È inoltre fondamentale considerare la dimensione e il tipo del buffer di join: la traccia mostra se sono state eseguite varianti di Nested Loop con o senza buffer e in quale punto vengono applicati i filtri. Valuto se l’aggiunta di indici sulle chiavi di join o una riscrittura volta a ridurre i risultati intermedi rappresenti una scelta più efficiente rispetto all’aumento delle dimensioni dei buffer.
Sottoquery, tabelle derivate e viste
All'indirizzo join_preparation A mio avviso, se le sottoquery nella forma EXISTS/IN in Semijoin sono stati trasformati (in_to_exists), se le tabelle derivate sono state unite (derived_merge) o siano stati concretizzati e se Condizione Pushdown fino alle tabelle derivate. Questi passaggi sono fondamentali perché l’assenza di un’operazione di merge può comportare una materializzazione costosa. Se nel trace noto ripetutamente decisioni di materializzazione con costi elevati, verifico se è possibile utilizzare un’operazione esplicita STRAIGHT_JOIN, un suggerimento o una riorganizzazione della query (ad es. Common Table Expressions con filtri mirati) induca il motore a adottare una strategia più efficiente. Nel caso delle viste, verifico se l’ottimizzatore risolva adeguatamente il contenuto della vista o se manchino indici aggiuntivi nella tabella sottostante.
Partizionamento e potatura
Nel caso delle tabelle partizionate, la traccia mostra quali partizioni sono state escluse in base alle chiavi di partizione e ai predicati (Potatura delle partizioni). Se il pruning previsto non avviene, ciò indica che è opportuno formulare i filtri in modo più precoce e in base alla chiave di partizione. Presto inoltre attenzione all’interazione tra partizionamento e indici: in assenza di indici locali o globali, il motore potrebbe controllare un numero eccessivo di righe nonostante il pruning, il che risulta evidente nella traccia a causa degli elevati costi di scansione.
Verificare in modo mirato gli hints, i parametri dell'indice e l'optimizer_switch
Utilizzo il trace per verificare l'effetto degli hint e degli switch dei parametri occupare. Se, ad esempio, inserisco. FORCE INDEX oppure un suggerimento dell'ottimizzatore, nel trace posso vedere se l'alternativa è stata effettivamente imposta e come è stata valutata. Tramite optimizer_switch posso attivare o disattivare temporaneamente le strategie (ad esempio per le decisioni relative a semijoin, index_merge o derived_merge). La traccia mi serve quindi come prova per verificare se il motore ha accettato le specifiche o se continuano a prevalere altre restrizioni (ad esempio le cardinalità). Facoltativamente, utilizzo flag di formattazione come one_line oppure end_markers all'indirizzo optimizer_trace-String, per adattare la leggibilità al mio strumento di analisi.
Update/DELETE e percorsi di scrittura
La traccia dell’ottimizzatore non si limita alle istruzioni SELECT. Anche nel caso delle istruzioni UPDATE e DELETE posso vedere come vengono scelte le vie di accesso e se i filtri vengono applicati con sufficiente anticipo per mantenere basso il numero di righe interessate. Verifico se un filtro WHERE non sia "sargable" o se la mancanza di un indice comporti una fase di scansione estesa prima che venga eseguita la modifica effettiva. Dalla traccia deduco se un indice compatto (ad es. solo le colonne necessarie) eviti accessi inutili avanti e indietro, riducendo così i blocchi e il volume del log.
Sicurezza, privilegi e prepared statement
Per poter leggere l'intero record, ho bisogno di privilegi sufficienti sull'oggetto; se questi mancano, il campo segnala PRIVILEGI INSUFFICIENTI Limitazioni. In scenari vicini alla produzione utilizzo quindi le stesse credenziali di accesso dell’applicazione o un account di diagnostica con autorizzazioni specifiche. Nel caso delle prepared statement, la traccia mostra in genere già la forma ottimizzata con i parametri vincolati, il che mi consente di valutare le selettività senza rivelare costanti sensibili. Se devo condividere le tracce, maschererò i valori dei parametri o li sostituirò con intervalli rappresentativi per rispettare i requisiti di protezione dei dati.
Automazione: acquisire, distinguere e documentare le tracce
Per garantire la riproducibilità delle analisi, salvo le tracce in modo casuale in una tabella di diagnostica e le corredo di metadati quali schema, versione, variabili di sessione e data e ora. In questo modo posso confrontare i dati prima e dopo le modifiche agli indici o gli aggiornamenti di versione diffen, quali decisioni sono state rinviate. È utile suddividere i blocchi piani_di_esecuzione_presunti e rows_estimation da archiviare separatamente, per poter confrontare rapidamente le variazioni dei costi. Alcune piccole query ausiliarie mi consentono di estrarre l'ordine di join selezionato e i costi calcolati – ad esempio con JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – e salvano il risultato insieme ai risultati di EXPLAIN e ANALYZE. In questo modo si ottiene una documentazione affidabile per ogni fase di ottimizzazione.
Limiti, peculiarità delle versioni e confronto con MySQL
Le strutture chiave del trace si basano su MySQL, ma i dettagli e i nomi dei campi possono variare leggermente a seconda della versione di MariaDB. Mi concentro quindi sulla semantici Sezioni (Rewrites, Rows-Estimation, piani considerati, Condition-Attachments), invece di lasciarmi confondere da differenze puramente estetiche. Importante: in MariaDB l’attenzione è focalizzata sull’ultima istruzione della connessione attiva. Chi analizza molte istruzioni consecutive, quindi, le legge immediatamente dopo l’esecuzione o in modo automatizzato tramite hook, in modo che nessuna traccia rilevante venga sovrascritta. Per file JSON molto grandi, tengo conto dello spazio di memoria necessario e comprendo MISSING_BYTES_BEYOND_MAX_MEM_SIZE come invito ad aumentare temporaneamente il limite e a ripetere l'analisi.
Estrazioni JSON concrete per l'uso quotidiano
Per concludere, ecco alcuni brevi estratti che utilizzo spesso nella pratica per andare subito al sodo:
- Ordine di join selezionato ed elenchi dei candidati: estraggo i prefissi del piano e la tabella ad essi associata per poter ricostruire la sequenza decisionale.
- Alternative di range e costi: estraggo l'elenco degli indici valutati per le tabelle più selettive, al fine di valutare con precisione le riscritture o i nuovi indici.
- Filtri applicati in fase iniziale: sto leggendo il
associare_condizioni_alle_tabelle-sezioni, per garantire che i predicati forti siano il più possibile vicini alla fonte dei dati.
Grazie alle poche visualizzazioni di queste estrazioni, dispongo di uno strumento di analisi essenziale per le decisioni dell'ottimizzatore, che attivo all'occorrenza durante le sessioni di diagnostica e poi disattivo nuovamente.
Frequenti inciampi e risoluzione di problemi
Se mancano gli istogrammi o le statistiche sono obsolete, le stime risultano errate e generano Piani con scansioni complete non necessarie. Se nel trace noto cardinalità molto divergenti, aggiorno le statistiche, imposto indici adeguati o riformulo i filtri in modo che possano essere memorizzati in un array. Riconosco i trace troppo succinti tramite MISSING_BYTES_BEYOND_MAX_MEM_SIZE e reagisco aumentando temporaneamente il limite. Se ANALYZE fornisce tempi di esecuzione migliori per un percorso alternativo, verifico nella traccia quale fattore di costo abbia favorito la variante scelta. In questo modo colmo passo dopo passo le lacune di conoscenza e raggiungo Chiarezza sulla logica decisionale.
Riassumendo brevemente
Il MariaDB Optimizer Trace mi spiega, in un documento JSON, come il motore riformuli le query, stimi il numero di righe, confronti i piani e infine generi una Sequenza seleziona. Lo attivo per ogni sessione, leggo la traccia, controllo join_preparation e ottimizzazione delle unioni e metto in relazione queste informazioni con EXPLAIN/ANALYZE. Dai motivi alla base degli indici rifiutati, dei filtri tardivi o delle stime errate deduco misure concrete: indici migliori, statistiche più aggiornate e formulazioni chiare delle query. Con le funzioni JSON estraggo frammenti, riconosco modelli e documento le decisioni in modo riproducibile. In questo modo riesco a gestire in modo affidabile anche carichi di lavoro SQL di grandi dimensioni. Prestazioni e rendi comprensibili le decisioni relative alla messa a punto.
