Linee guida per l'ottimizzazione delle query

Dopo aver identificato le query problematiche nel carico di lavoro, ogni query deve essere ottimizzata. Usa le seguenti linee guida sull'ottimizzazione per far funzionare il carico di lavoro in modo più efficiente.

Riduci al minimo il numero di righe sottoposte a scansione

Per quanto possa sembrare semplice, si tratta di un ottimo consiglio da seguire quando si ottimizzano le query. Usa l'istruzione EXPLAIN e controlla la colonna rows per vedere quante righe vengono scansionate dall'ottimizzatore a ogni join. Prova a ridurre il numero di righe scansionate creando un indice ottimale, quindi spiega nuovamente la query per confermare il lavoro svolto. Per ulteriori informazioni, consulta la documentazione di MySQL.

Se usi tabelle partizionate, esegui sempre query con la clausola WHERE che abilita l'eliminazione delle partizioni, in modo che l'ottimizzatore non debba scansionare ogni partizione. Se la clausola WHERE contiene una costante per la colonna partizionata, l'ottimizzatore sa quale partizione cercare e questo rende la query più efficiente.

Un altro facet di questo consiglio è la progettazione del database. Minore è il numero di tabelle nella query, maggiore sarà la velocità della query. Se è possibile denormalizzare la struttura del database, è possibile fare in modo che l'ottimizzatore esegua la scansione di un numero di righe minore, con conseguente miglioramento delle prestazioni delle query.

È possibile ridurre al minimo l'utilizzo della tabella temporanea e le tabelle temporanee nel disco

L'ottimizzatore di Aurora compatibile con MySQL crea tabelle temporanee sia sulla RAM che sul disco se non riesce a ottenere i risultati della query desiderati direttamente dagli indici. Di conseguenza, gran parte dell'ottimizzazione consiste nell'avere gli indici adatti al carico di lavoro. Tuttavia, nel carico di lavoro potrebbero esserci delle query che non possono basarsi solo sugli indici: pertanto alcune operazioni potrebbero essere eseguite in un file temporaneo. Ciò non costituisce un problema purché si mantengano questi valori al minimo e si assicuri che sul disco vengano create pochissime tabelle. MySQL crea tabelle su disco quando la dimensione della tabella temporanea è troppo grande per essere ospitata in memoria. La logica utilizzata da MySQL per controllare la dimensione della tabella temporanea interna è il più piccolo dei due valori variabili tmp_table_size e. max-heap-table-size È possibile regolare queste variabili su un valore ottimale in base al carico di lavoro in modo che, nei casi in cui non sia possibile impedire le tabelle temporanee, le si trasferisca su disco solo in rare occasioni.

Evita l'ordinamento dei file

Se il carico di lavoro presenta molte query ORDER BY, il modo migliore per risolverle è utilizzare gli indici giusti sulle tabelle. Assicurati che gli indici a più colonne siano progettati correttamente per evitare l'ordinamento nei file. L'ordinamento non può avvenire su una colonna se le colonne precedenti non sono scansionate con costanti (in, >, <, != e BETWEENnon consentirà l'ordinamento nella colonna successiva a destra). Il modo ottimale per ordinare in MySQL è quello di inserire un indice a più colonne che posizioni le colonne che contengono valori costanti forniti nella query a sinistra della colonna di ordinamento in una struttura contigua. Se, in ultima istanza, la query non è in grado di restituire risultati senza un ordinamento dei file, sposta l'ordinamento nell'applicazione.

Evita di eseguire query di aggregazione con un'elevata simultaneità

Il tuo carico di lavoro potrebbe avere un numero limitato di query di aggregazione in grado di soddisfare alcune funzionalità dell'applicazione. Questo caso d'uso richiede molta cautela. Il motore InnoDB è predisposto per i carichi OLTP (Online Transaction Processing, elaborazione di transazioni online), ma anche poche query di raggruppamento a elevata simultaneità possono gravare molto sulla CPU e ridurre rapidamente le prestazioni del cluster. Per risolvere i casi d'uso in cui sono necessari set di risultati aggregati, preaggrega i dati in tabelle pronte per la lettura in modo da evitare del tutto il raggruppamento per query.

Verifica la simultaneità delle tue query

Quando ottimizzi le singole query, ricorda che queste query vengono eseguite contemporaneamente su diverse v compatibili con Aurora con MySQL. CPUs La tua query potrebbe essere eseguita in pochi millisecondi nell'ambiente di test su esecuzioni singole. Ma questo non è il quadro completo. Assicurati di testare la query con il livello di simultaneità previsto nel tuo cluster di produzione e di eseguire il benchmarking delle sue prestazioni. Invia la query in produzione solo quando soddisfa gli obiettivi di simultaneità prefissati. Assicurati di usare l'ottimizzatore hint sql_no_cache negli script di test in modo da evitare di recuperare i risultati dalla cache. Puoi usare strumenti come mysqlslap per eseguire il test simultaneamente ed eseguire il benchmarking dei risultati.