L’ottimizzatore a Ipergrafo di MySQL ora disponibile in HeatWave MySQL

Lo scorso anno, con MySQL 8.0.31, abbiamo annunciato come HeatWave utilizzi il nuovo ottimizzatore a ipergrafo di MySQL per ottenere piani di query migliori. Da allora abbiamo continuato a lavorare sull’ottimizzatore e, a partire da MySQL 9.0, è stato abilitato come funzione opzionale in HeatWave MySQL. Questa nuova funzionalità è disponibile solo in HeatWave MySQL e porterà vantaggi ai carichi di lavoro OLTP. Non è disponibile nei cloud di altri fornitori. Continua a leggere per una rapida introduzione su come provarlo e cosa significherà per le tue query.

Come abilitare l’ottimizzatore a Ipergrafo

Poiché un nuovo ottimizzatore è un passo importante, lo stiamo introducendo come funzionalità opzionale, il che significa che devi abilitarlo esplicitamente. L’ottimizzatore a ipergrafo si abilita impostando il parametro optimizer_switch su hypergraph_optimizer:

SET SESSION optimizer_switch='hypergraph_optimizer=on';

Ecco fatto! La tua prossima query utilizzerà l’ottimizzatore a ipergrafo. Ovviamente, dato che l’ottimizzatore decide solo come eseguire la query e non modifica la semantica della query stessa, i risultati rimarranno identici a quelli ottenuti con il vecchio ottimizzatore, quindi inizialmente non noterai grandi differenze. Tuttavia, ci sono alcune piccole variazioni visibili. La più evidente è nell’output di EXPLAIN. MySQL attualmente supporta tre formati per l’output di EXPLAIN: TRADITIONAL, JSON e TREE. Con l’ottimizzatore a ipergrafo, il formato TRADITIONAL, predefinito per molti anni, non è più un’opzione. Il predefinito per l’ottimizzatore a ipergrafo è il formato TREE.

Il formato EXPLAIN FORMAT=JSON è stato reso configurabile in MySQL 8.3. Impostando la variabile di sistema explain_json_format_version su 2, otterrai il nuovo formato JSON che è fondamentalmente una versione leggibile dalla macchina del formato TREE. Infatti, l’output TREE è implementato nel server come una proiezione più leggibile per l’umano dell’output JSON. Con l’ottimizzatore a ipergrafo, non puoi più usare la versione 1 del formato JSON. Le due opzioni disponibili sono il formato TREE e la versione 2 del formato JSON.

Il motivo è tecnico: il vecchio ottimizzatore e l’ottimizzatore a ipergrafo usano formati interni diversi per rappresentare il piano di query, e i formati TRADITIONAL e JSON versione 1 sono rappresentazioni di strutture dati che non esistono con l’ottimizzatore a ipergrafo. Da tempo, utilizzando il vecchio ottimizzatore, MySQL ha convertito silenziosamente il vecchio formato del piano in quello nuovo, utilizzato nativamente dall’ottimizzatore a ipergrafo, motivo per cui i formati TREE e JSON versione 2 sono disponibili anche per il vecchio ottimizzatore.

Cosa significa l’ottimizzatore a ipergrafo per me?

Il nuovo output di EXPLAIN è sicuramente qualcosa a cui ci dovremo abituare, ma questo riguarda solo la visualizzazione. La differenza principale sta nel modo in cui l’ottimizzatore a ipergrafo sceglie di pianificare le query.

Lo scopo dell’ottimizzatore è selezionare il miglior piano di query possibile per le tue query, e generalmente l’ottimizzatore a ipergrafo fa un lavoro migliore rispetto al vecchio ottimizzatore. Ad esempio, se hai avuto difficoltà a far scegliere all’ottimizzatore tra l’uso di un indice per leggere i dati in ordine o effettuare un’operazione di ordinamento esplicita, l’ottimizzatore a ipergrafo dovrebbe risolvere questo problema. La necessità di impostare prefer_ordering_index potrebbe diventare obsoleta.

Anche gli ordini di join dovrebbero migliorare, se già non sono ottimali. L’ottimizzatore a ipergrafo può anche generare piani di join complessi, il che può essere utile soprattutto per query analitiche più complesse. Inoltre, è migliore nel fare la scelta corretta tra hash join e nested loop join, accelerando molte query.

In generale, l’ottimizzatore a ipergrafo è più efficace nel selezionare il piano migliore in base ai calcoli di costo. Il vecchio ottimizzatore si basava molto più sulle euristiche, che possono funzionare bene per alcune query, ma per altre no. L’ottimizzatore a ipergrafo riesce a prendere decisioni basate sui costi che risultano in piani di query migliori.

Ciò significa che possiamo fare a meno di suggerimenti e fare affidamento sull’ottimizzatore? Non proprio. Le formule di costo e le statistiche sono più importanti con l’ottimizzatore a ipergrafo, e noterai che generare statistiche migliori (ad esempio, creando più istogrammi) aiuterà l’ottimizzatore a fare scelte migliori. Tuttavia, anche le formule di costo e le statistiche sono semplificazioni, e, teoricamente, sarà sempre possibile creare un set di dati e query che producano un piano subottimale.

Anche se cerchiamo di far funzionare bene il modello di costo per le query reali, ci possono essere alcuni casi in cui i suggerimenti sono ancora necessari per ottenere un buon piano. Per questo motivo, abbiamo cercato di mantenere il comportamento dei suggerimenti dell’ottimizzatore esistente, per quanto possibile. I suggerimenti precedenti dovrebbero continuare a forzare le scelte di piano nello stesso modo. Tuttavia, il nostro consiglio è di provare prima senza suggerimenti e poi utilizzarli solo quando necessario.

Stiamo continuando a migliorare il modello di costo, quindi se una query non funziona bene oggi, potrebbe migliorare domani. Senza suggerimenti.

L’ottimizzatore a ipergrafo di MySQL è disponibile in HeatWave MySQL su OCI, AWS e Azure. Non lo troverai nei cloud di altri fornitori. Prova l’ottimizzatore a ipergrafo di MySQL oggi stesso!

[source https://blogs.oracle.com/mysql/post/the-mysql-hypergraph-optimizer-now-available-in-heatwave-mysql]