Spiegazione interna dell’ottimizzatore di query di MariaDB: nozioni di base, piani e applicazione pratica

Spiego il Ottimizzatore MariaDB Esempi pratici: come crea i piani, stima i costi e perché a volte sbaglia. Ecco come leggere il piano di esecuzione SQL in modo mirato, utilizzare gli indici in modo sensato e guidare l'ottimizzatore con i fatti anziché con l'istinto.

Punti centrali

Per cominciare, riassumo brevemente gli elementi fondamentali, in modo che tu possa contestualizzare in modo mirato le sezioni seguenti e il Panoramica mantieni.

• Fasi: L'analisi, la preparazione, l'ottimizzazione e l'esecuzione costituiscono il ciclo di vita di ogni query.
• Modello di costo: I valori in microsecondi basati sul tempo determinano la selezione degli indici, le scansioni e l'ordine dei join.
• Statistiche: La cardinalità e gli istogrammi determinano la stima della selettività.
• Trasparenza: EXPLAIN, EXPLAIN ANALYZE e Optimizer Trace aprono la “scatola nera”.
• Sintonizzazione: Gli indici, la riscrittura delle query, il comando ANALYZE TABLE e i parametri di costo aumentano la velocità.

Ciclo di vita di una query in MariaDB

Prima che venga generato un piano, una query passa attraverso quattro fasi che verifico in modo mirato nella pratica quotidiana per Cause da individuare come causa di lentezza. Durante l’analisi sintattica, MariaDB converte l’SQL in una struttura interna; è qui che emergono gli errori di sintassi. Nella fase di preparazione, il motore verifica tabelle, colonne e potenziali indici ed esegue semplici trasformazioni. Segue quindi la fase di ottimizzazione, in cui vengono calcolati i piani candidati e valutati con un modello di costo. Nella fase di esecuzione, il server attua il piano selezionato passo dopo passo: lettura, join, filtraggio, restituzione.

Distinguo chiaramente gli errori di analisi in base alla fase, perché in questo modo le diagnosi hanno effetto più rapidamente e Misure agire in modo mirato. Nella maggior parte dei casi, i problemi di prestazioni hanno origine nell’ottimizzazione: stime errate, indici mancanti o sequenze di join sfavorevoli. Gli errori di parsing sono banali, ma la fase di preparazione può già comportare alcune sottigliezze, come la risoluzione delle view o la riorganizzazione delle sottoquery. Nella fase di esecuzione, le inefficienze saltano all’occhio senza pietà se in precedenza è stata scelta una scansione completa. Per questo motivo, inizio ogni analisi con una panoramica strutturata di tutte e quattro le fasi.

Come funziona il processo decisionale interno dell’Optimizer

MariaDB opera in base ai costi e valuta le esecuzioni alternative tramite un Funzione di costo. Per ogni variante, il server stima il numero di righe lette, la selettività delle condizioni WHERE/ON, i tipi di accesso quali Table Scan, Index Scan e Range Scan, nonché il tempo richiesto per le singole operazioni. Internamente, il server distingue tra join_preparation e join_optimization. In join_preparation vengono eseguite la riscrittura delle query, la semplificazione delle condizioni, la trasformazione delle sottoquery e la risoluzione delle viste. La fase `join_optimization` calcola gli ordini di join, verifica i candidati all’indicizzazione tramite `ref_optimizer_key_uses`, stima il numero di righe tramite `Range Scan` e assegna le condizioni a tabelle specifiche il prima possibile.

Questo meccanismo spiega perché un piccolo filtro posizionato nel posto sbagliato può causare costosi Conseguenze ha. Se l’operazione `attaching_conditions_to_tables` avviene in una fase tardiva, il piano trascina un numero inutilmente elevato di righe attraverso i join. Se le statistiche sono obsolete, `rows_estimation` e `Selectivity` risultano errati; l’ottimizzatore ricorre quindi a percorsi di accesso apparentemente vantaggiosi, ma in realtà lenti. È proprio su questi aspetti che intervengo: statistiche migliori, predicati più chiari, indici compositi ordinati in modo accurato. In seguito, la scelta del piano spesso cambia in modo tangibile.

Modello dei costi a partire da MariaDB 11.0

Le versioni attuali non valutano più il lavoro in modo approssimativo in base ai pesi, ma con microsecondi per operazioni di archiviazione specifiche. Parametri quali optimizer_disk_read_cost, optimizer_disk_read_ratio e optimizer_where_cost avvicinano il modello ai tempi di esecuzione reali. In questo modo, l’ottimizzatore confronta la scansione dell’intervallo dell’indice con la scansione completa sulla base di ipotesi temporali reali. LAST_QUERY_COST mostra i costi totali stimati e spesso presenta una correlazione con la realtà nettamente migliore rispetto al passato. Per i sistemi con grandi volumi di dati, questa maggiore precisione offre vantaggi immediati.

Calibro il modello con attenzione quando le caratteristiche hardware contraddicono le ipotesi predefinite e quindi la Scelta del piano distorcere. Gli SSD NVMe, le memorie distribuite o le cache specializzate possono modificare in modo significativo il rapporto disco e i tempi di lettura. Piccole modifiche ai valori di `optimizer_costs` fanno sì che MariaDB privilegi percorsi più efficienti. Documento ogni modifica e poi controllo EXPLAIN ANALYZE per misurarne l'effetto. Senza misurazioni, l'ottimizzazione rimane un terno al lotto.

Selettività, statistiche e istogrammi

Le stime accurate partono da dati precisi cardinalità e di una selettività affidabile. MariaDB tiene traccia delle statistiche relative ai diversi valori per ogni colonna e, facoltativamente, può utilizzare istogrammi per le distribuzioni. Proprio i dati non uniformi – hotspot, distribuzioni di Zipf, modelli stagionali – traggono vantaggio dagli istogrammi. Dopo modifiche significative ai dati, eseguo ANALYZE TABLE affinché l’ottimizzazione possa nuovamente basarsi su dati effettivi. Chi se ne dimentica rischia scansioni complete che sono oggettivamente errate.

Programmo ANALYZE come processo periodico, in base a Cambiamenti nel volume dei dati e sulle tabelle critiche. In caso di distribuzioni delle colonne fortemente distorte, gli istogrammi aiutano a valutare in modo realistico la selettività dei valori singolari. Ciò riduce gli errori di valutazione nelle scansioni di intervallo e nelle strategie di unione. In combinazione con indici compositi adeguati, la precisione dei risultati migliora notevolmente. Risultato: tempi di esecuzione più brevi e meno operazioni di I/O.

Lettura di EXPLAIN e dei piani di esecuzione

Per rendere visibili le decisioni, utilizzo EXPLAIN, EXPLAIN EXTENDED e FORMATO=JSON. Le colonne classiche offrono una rapida panoramica: id, select_type, table, type, possible_keys, key, key_len, ref, rows ed eventualmente filtered. Un valore type=ALL indica una scansione completa, che raramente è auspicabile. FORMAT=JSON mostra in dettaglio come sono state spostate le condizioni e quali percorsi sono stati valutati dall’ottimizzatore. Nel contesto dell’hosting, consiglio la guida su Piani di esecuzione nell'hosting, per collegare le informazioni relative ai piani agli effetti sulle infrastrutture.

Per una rapida interpretazione mi è d’aiuto una piccola tabella che riassume brevemente i valori tipici e quindi Interpretazioni errate impedito.

Campo EXPLAIN	Valore tipico	Significato nella pratica
tipo	ALL, range, ref, eq_ref, const	Più ci si sposta verso destra, più la selezione è rigorosa; ALL indica una scansione completa.
possible_keys	Elenco degli indici	Indici che, in teoria, sono adeguati; se qui mancano dei candidati, manca la struttura.
chiave	Nome dell'indice	Indice effettivamente utilizzato; se vuoto, significa che non viene utilizzato alcun indice.
righe	Numero	Stima delle righe lette; forte discrepanza rispetto alla realtà = statistica inaffidabile.
filtrato	Per cento	Quanto viene trasmesso dopo il filtro; spesso è meglio che sia poco.

Perché a volte l’Optimizer sbaglia

Non esiste un modello di costi che si adatti a ogni situazione, quindi lo correggo Errori in modo mirato. Le statistiche obsolete portano a stime errate delle righe e a sequenze di join sfavorevoli. Indici compositi strutturati in modo errato impediscono l’utilizzo degli indici nei filtri a più colonne. Sottoquery molto annidate rendono difficili le riscritture efficaci e bloccano la materializzazione. Filtri mancanti o fuorvianti costringono il motore a spostare molte righe prima che i predicati utili abbiano effetto.

Per prima cosa verifico se la formulazione della query è conforme al Indice Ciò che funziona davvero: regola del prefisso a sinistra, ordine di ordinamento adeguato, evitare funzioni sulle colonne nella clausola WHERE. Dopodiché controllo in EXPLAIN ANALYZE se la realtà conferma la stima. In caso contrario, eseguo ANALYZE TABLE e, se necessario, una riscrittura. Solo come ultima risorsa ricorro a FORCE INDEX o all’hinting, poiché ciò può limitare le ottimizzazioni future.

Utilizzo mirato della traccia dell'ottimizzatore

Se EXPLAIN non è sufficiente, attivo l'Optimizer Trace e monitoro Decisioni nel log JSON. Qui posso vedere quali piani sono stati presi in considerazione, scartati o accettati. Capisco perché una condizione si applica in ritardo o perché un indice non è stato preso in considerazione. Il log mostra anche come sono state riorganizzate le condizioni. Questa visione approfondisce la comprensione e fornisce leve concrete per la prossima fase di ottimizzazione.

Salvo le sezioni rilevanti della traccia insieme all'hash della query e Parametrivalutare. In questo modo, in seguito potrò confrontare quale modifica abbia prodotto quale effetto. La documentazione del server MariaDB e diverse presentazioni nell’ecosistema descrivono i campi in modo dettagliato (fonte: documentazione del server MariaDB su Query Optimizer e Optimizer Trace). Con questo strumento riesco a individuare le ipotesi errate più rapidamente rispetto al metodo per tentativi ed errori. Risparmio tempo soprattutto nei join complessi.

Esercitazione pratica: ottimizzazione del database passo dopo passo

Inizio ogni ottimizzazione con una chiara Misurazione. Individuo i problemi tramite il monitoraggio e il Registro delle query lente. Successivamente confronto EXPLAIN con EXPLAIN ANALYZE per mettere a confronto il piano e i risultati effettivi. Adatto la strategia di indicizzazione a WHERE, JOIN e ORDER BY; allineo gli indici compositi ai punti di accesso più frequenti. Utilizzo FORCE INDEX solo se l’ottimizzatore, nonostante statistiche corrette, seleziona il candidato sbagliato.

Ogni fase prevede la cura della Statistiche: ANALYZE TABLE su tabelle molto trafficate, istogrammi per distribuzioni asimmetriche. Semplifico le sottoquery superflue, materializzo i risultati intermedi se necessario ed elimino le vecchie soluzioni provvisorie. In presenza di hardware speciale, verifico gli optimizer_costs affinché il modello a microsecondi sia corretto. Documento ogni modifica con valori «prima» e «dopo», in modo che l’effetto rimanga tracciabile nel tempo.

Problemi tipici degli ottimizzatori e relative soluzioni

Se EXPLAIN type=ALL, nonostante il campo possible_keys sia pieno, controllo innanzitutto Selettività. Spesso l'ordine delle colonne nell'indice composito non è corretto oppure una funzione impedisce l'utilizzo dell'indice. In questi casi inverto l’ordine, rimuovo le funzioni che causano problemi o suddivido i predicati. Se l’ordine dei join è errato, verifico se sia possibile applicare un filtraggio iniziale, ad esempio anteponendo la tabella più selettiva. Laddove opportuno, converto le sottoquery in join o tabelle TEMPORARY.

Riconosco le decisioni errate anche da valori fortemente discostanti righe tra teoria e realtà. In questi casi è utile ricorrere al comando ANALYZE TABLE o a un istogramma sulla colonna in questione. Se anche statistiche corrette non portano al risultato desiderato, prendo in considerazione l’uso di hint espliciti. Prima di farlo, salvo i dati di controllo e i valori misurati, in modo che le versioni successive dell’ottimizzatore non vengano rallentate a causa di impostazioni memorizzate. In questo caso, la disciplina nella documentazione ripaga.

Contesto dell'hosting e aspetti operativi

La qualità delle query e l'infrastruttura devono essere in sintonia, altrimenti l'applicazione va sprecata Potenziale. SSD veloci, cache coerenti e una configurazione ottimale sono la base su cui l’Optimizer prende le decisioni giuste. Un traffico elevato non tollera scansioni complete; poche query errate rallentano interi sistemi. Per gli ambienti MySQL/MariaDB in produzione, forniscono consigli pratici come Ottimizzatore MySQL spunti di riflessione utili sulla combinazione tra piano e piattaforma. Chi tiene conto di questo aspetto previene i colli di bottiglia prima che si aggravino.

Associ sempre l'analisi del piano alle metriche relative a I/O, latenza e concorrenza. Se i valori non corrispondono al modello di costi ipotizzato, verifico i parametri. Successivamente, esamino le dimensioni dei buffer, i carichi di lavoro paralleli e la distribuzione degli hotset. In questo modo riesco a gestire in modo armonioso le query e le risorse e a mantenere sotto controllo i picchi di carico.

Percorsi di join e di accesso nella pratica

Chiaro molti malintesi spiegando che il Tipi di accesso valutino in modo mirato l'uno rispetto all'altro. Un intervallo- o rif.-L'accesso funziona quasi sempre TUTTO. In caso di collegamenti con condizioni di uguaglianza su chiavi univoche (eq_ref) i progetti sono particolarmente solidi. Verifico inoltre se un Indice di copertura che soddisfa completamente la query: se tutte le colonne necessarie sono presenti nell'indice, MariaDB evita costosi accessi alle tabelle. Condizione Index Pushdown (ICP) aiuta a verificare condizioni WHERE aggiuntive già nell'indice – ciò riduce il numero di righe restituite e le operazioni di I/O.

Circa Unione di indici MariaDB può combinare più indici (intersezione/unione). Ciò è utile in presenza di predicati OR o di più condizioni selettive, ma spesso risulta più lento rispetto a un indice composito ben scelto. Sto inoltre valutando MRR (Lettura multigamma) e BKA (Batched Key Access). MRR ordina le chiavi primarie da leggere per livellare gli I/O casuali; BKA raggruppa le ricerche nei join e offre vantaggi soprattutto nei join non coperti. In pratica, testo BKA/MRR tramite optimizer_switch e verifico con EXPLAIN ANALYZE se i modelli di I/O diminuiscono. Se invece MariaDB ricorre al Ciclo annidato a blocchi (BNL), di solito è più vantaggioso aumentare la dimensione del buffer di join (join_buffer_size) – oppure utilizzare un rewrite che consenta dei veri e propri join su indici.

-- Esempio: indice composito per join + filtro + ordinamento
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Accesso tipico
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Grazie all'indice sopra indicato, l'ottimizzatore può scegliere l'ordine più selettivo, valutare i filtri in anticipo e spesso eseguire l'ordinamento senza ricorrere a un filesort aggiuntivo.

ORDER BY, GROUP BY, Filesort e tabelle temporanee

L'ordinamento e l'aggregazione richiedono tempo. Mi assicuro che ORDER BY e GRUPPO PER possono essere eseguiti nell'ordine dell'indice. Ciò funziona se il prefisso e la direzione corrispondono esattamente. Altrimenti viene applicata una Filesort con buffer di ordinamento (sort_buffer_size) ed eventualmente una tabella temporanea. Se l'insieme dei risultati contiene colonne TEXT/BLOB di grandi dimensioni, MariaDB risulta più veloce su disco Tabelle TEMP (Aria). Adotto misure preventive selezionando solo le colonne necessarie, caricando i campi di grandi dimensioni solo alla fine oppure utilizzando prefissi di lunghezza limitata.

Nelle aggregazioni, quando possibile, utilizzo, Scansione dell'indice non ottimizzata (ad es. GROUP BY sulla parte principale dell'indice) e seleziono indici compositi lungo il raggruppamento. Quando i risultati intermedi diventano voluminosi, una materializzazione con chiavi appropriate offre una scalabilità migliore rispetto a un unico mega-join. Misuro regolarmente le metriche degli handler e i contatori Created_tmp_* per individuare i punti critici relativi all'ordinamento e alle tabelle temporanee.

Sottoconsultazioni, semi-join e materializzazione

Molte sottoquery possono essere riformulate in modo efficiente in fase di preparazione. Le strutture IN/EXISTS possono essere utilizzate come Semi-join funzionano, con strategie come Materialization o LooseScan. Verifico se l’Optimizer è un derived_merge è stato possibile: se una tabella derivata (o un CTE WITH) viene inserita nel piano esterno, i suoi indici sono immediatamente disponibili. Se ciò non fosse possibile, la sottoquery finisce in una tabella temporanea – in tal caso, se fattibile, le assegno una chiave (ad esempio tramite SELECT DISTINCT/ORDER BY sulle colonne chiave), in modo che i join su di essa non finiscano nel nulla.

-- Esempio: EXISTS al posto di IN e tabella derivata compatibile con il merge
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Derivazione con chiavi univoche
WITH paid_orders AS (
  SELECT DISTINCT order_id
  FROM payments
  WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;

Con EXPLAIN FORMAT=JSON verifico se materializzato oppure sottoconsultazione dipendente è stato eletto e se vi siano condizioni (condizione pushdown) intervenire con sufficiente anticipo.

Partizionamento e potatura

Il partizionamento non sostituisce gli indici, ma può Volume di dati per accesso ridurlo drasticamente. L’Optimizer esegue una potatura corretta solo se il predicato soddisfa il Chiave di partizione sia unidimensionale e non venga reso irriconoscibile dalle funzioni. Per questo motivo evito espressioni come DATE(created_at) nella clausola WHERE sulle tabelle partizionate e utilizzo invece i limiti di intervallo. EXPLAIN mostra quali partizioni vengono lette; intervalli ampi indicano una scarsa ottimizzazione (pruning).

Un numero eccessivo di partizioni di piccole dimensioni aumenta il sovraccarico di pianificazione. Scelgo quindi una granularità adeguata (ad esempio mensile anziché giornaliera), mantengo aggiornate le statistiche per ciascuna partizione (ANALYZE PARTITION) e verifico se gli indici importanti sono presenti localmente nelle partizioni. Nei progetti di migrazione tengo conto dell’impatto sulla replica e sul backup: entrambi questi fattori influenzano il livello di aggressività con cui eseguo la partizionatura.

Sargability e modelli di riscrittura

La leva più semplice rimane Trasportabilità della bara – Condizioni che rendono utilizzabili gli indici. Evito di applicare funzioni alle colonne nel WHERE, riduco le costanti a livello di colonna e, se necessario, scompongo le condizioni OR in UNIONE TUTTI. Per le ricerche LIKE senza anchor iniziale ("%foo") un indice BTREE non serve a nulla; in questo caso sto valutando di utilizzare la ricerca full-text o un servizio di ricerca adeguato. Per i calcoli utilizzo colonne generate indicizzate, in modo che l'ottimizzatore possa riconoscere la logica presente nell'indice.

-- Anti-pattern: funzione su colonna
WHERE DATE(created_at) = '2026-08-01'
-- Meglio: intervallo sul valore grezzo
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-pattern: l'operatore OR impedisce l'uso dell'indice
WHERE status = 'open' OR customer_id = 42
-- Migliore: due ricerche con UNION ALL e un indice dedicato per ciascuna
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

Per quanto riguarda gli indici compositi, ritengo che il regola del prefisso a sinistra Rispetta rigorosamente questa regola: ordina le colonne in base alla selettività e all’ordinamento che sarà necessario in seguito. Se ho bisogno di un ORDER BY in ordine decrescente, ne tengo conto nella struttura dell’indice: in questo modo evito il filesort.

Interruttore dell'ottimizzatore e regolazione fine dei costi

Prima di eseguire le query, controllo optimizer_switch e buffer di memoria. Funzionalità come mrr, batched_key_access, index_merge, semijoin, derived_merge oppure condizione_pushdown_per_derivato possono essere personalizzati per ogni sessione. Attivo i candidati in modo mirato per una sessione di test, effettuo una misurazione con EXPLAIN ANALYZE e ripristino lo stato precedente se l’effetto non si verifica. Il percorso di join beneficia di una quantità sufficiente di join_buffer_size; grandi varietà di ordinamento_buffer_size. Allo stesso tempo, tengo sotto controllo i buffer in relazione alla concorrenza, in modo che il server non entri in modalità di swapping sotto carico parallelo.

A livello di costi, se necessario, adeguo gli elementi già menzionati costi_ottimizzatore in microsecondi. La mia linea guida: piccoli passi reversibili con punti di misurazione documentati. Utilizzo COSTO_ULTIMA_QUERY per verificare la plausibilità ed eseguire misurazioni ripetute con valori dei parametri realistici, poiché i piani possono dipendere fortemente da valori letterali concreti.

Stabilità del piano, regressioni e flusso di lavoro del team

Anche un buon piano può essere compromesso dall'aumento del volume dei dati o da un cambio di versione ribaltare. Per questo motivo raccolgo informazioni sul piano di esecuzione: hash delle query, JSON di EXPLAIN, estratti della traccia dell’ottimizzatore e tempi di esecuzione di EXPLAIN ANALYZE. Le modifiche agli indici e le riscritture vengono effettuate tramite pull request, corredate da prove “prima e dopo”. Negli ambienti CI/CD verifico automaticamente le query critiche rispetto a set di dati rappresentativi. In questo modo riesco a Piani di regressione di buon mattino.

Per i casi delicati, ritengo che Suggerimenti (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch per ogni query) sono disponibili come ultima opzione, ma utilizzale con parsimonia e fissando una data di scadenza. È meglio risolvere le cause alla radice: statistiche, indici, formulazione. Nei team, una guida sintetica sulla “sargability”, sulla progettazione degli indici e sulla disciplina di misurazione garantisce che le nuove funzionalità non introducano, inosservate, problemi di prestazioni.

Breve sintesi: dal progetto alla realizzazione

Chi può utilizzare il Piano Chi comprende, controlla le prestazioni. Le fasi di parsing, preparazione, ottimizzazione ed esecuzione spiegano dove si perde tempo. Il modello di costo basato sul tempo a partire dalla versione 11.0, le statistiche aggiornate e gli istogrammi rendono le stime affidabili. EXPLAIN, EXPLAIN ANALYZE e l’Optimizer Trace garantiscono trasparenza, che traduco in misure concrete. Con una strategia di indicizzazione ben strutturata, una progettazione chiara delle query e un’infrastruttura adeguata, le query MariaDB forniscono risposte costantemente rapide.