Il primo passaggio consiste nell'assegnare una priorità alle query da ottimizzare:

1. Identifica le domande del database che richiedono il tempo di risposta migliore 
2. Identifica le domande del database che vengono eseguite di frequente

Queste due categorie devono essere ottimizzate durante la progettazione dello schema del database.

Una transazione è un'unità logica in cui tutte le istruzioni contenute devono eseguire il commit completo o il rollback. La transazione è la funzionalità che fornisce atomicità, coerenza, isolamento e durabilità (ACID) per MySQL. 

In InnoDB, il motore di archiviazione per MySQL, tutte le attività degli utenti avvengono all'interno di una transazione. Per impostazione predefinita, è abilitata la modalità di commit automatico, il che significa che ogni istruzione SQL forma una singola transazione a sé stante. Per eseguire una transazione con più istruzioni quando è abilitato il parametro autocommit, avvia la transazione in modo esplicito con START_TRANSACTION o START_TRANSACTION e terminala con START_TRANSACTION o START_TRANSACTION. Se la modalità autocommit è disabilitata, la sessione ha sempre una transazione aperta fino a quando non viene completato un COMMIT o un COMMIT e ne inizia uno nuovo. 

La best practice relativa alle transazioni consiste nel mantenerle il più brevi possibile. Ciò è dovuto al fatto che le transazioni lunghe presentano diversi svantaggi, come descritto più avanti in questo articolo.

1. Contese di blocco prolungate, che causano query più lente e potenziali errori delle query

• Il blocco a livello di riga InnoDB viene mantenuto per tutta la durata della transazione
• Aumenta potenzialmente le attese del blocco, il timeout del blocco e i deadlock, che rendono la query più lenta o con esito negativo

2. Prestazioni ridotte del server a causa di un numero elevato di log di annullamento 

• A causa del controllo della contemporaneità multi-versione (MVCC) di InnoDB, le versioni precedenti delle righe modificate vengono archiviate nei log di annullamento per operazioni di lettura e rollback coerenti. Con il livello di isolamento in lettura continua predefinito, i log di annullamento non vengono eliminati definitivamente fino al completamento delle transazioni già avviate. Pertanto, una transazione a lunga esecuzione accumula log di annullamento. Questa operazione può essere osservata e monitorata tramite l'elenco della cronologia nella funzione della riga di comando SHOW Engine INNODB STATUS.
• Quando l'elenco della cronologia supera i milioni, le prestazioni del server ne risentiranno a causa della contesa mutex sui segmenti di rollback, dell'aumento del volume di lettura dei log di annullamento e del tempo maggiore per trasferire l'elenco collegato dei log di annullamento. Inoltre, aumenta il carico di lavoro per l'eliminazione definitiva dei thread.

3. Maggiore utilizzo del disco

• Aumento dei log di annullamento archiviati su disco, nello spazio delle tabelle di sistema o di annullamento

4. Tempo di arresto prolungato

• Durante il normale arresto, verrà eseguito il rollback delle transazioni in corso. Il tempo di rollback è spesso più lungo del tempo impiegato per arrivare al punto. Pertanto, l'arresto del server potrebbe richiedere molto tempo per il rollback.

5. Tempo di ripristino prolungato in caso di arresto anomalo

• Durante il ripristino in seguito a un arresto anomalo, InnoDB ripete le transazioni dall'ultimo checkpoint e sblocca le transazioni non impegnate. In caso di una transazione lunga, il passaggio corrispondente richiederà più tempo.

• Query SELECT
• I blocchi di riga non vengono mantenuti
• Potrebbero causare l'accumulo dei log annullati
• Consulta la seguente sezione sull'ottimizzazione delle query
• Query UPDATE/INSERT/DELETE
• La query in batch funziona meglio di molte modifiche apportate a una singola riga
• Suddividi e limita il tempo di esecuzione in batch a un paio di secondi

• Valuta la possibilità di separare le query SELECT
• Se è presente una logica dell'applicazione tra le query del database, valuta la possibilità di suddividere la transazione
• Stima il numero di blocchi di righe previsto per ogni istruzione
• Valuta l'ordine di esecuzione per ridurre al minimo il blocco di righe
• Cerca opportunità per ridurre le dimensioni delle transazioni

Le query possono essere acquisite sul lato applicazione o sul lato database.

È buona prassi di sviluppo registrare le query di database e il tempo di esecuzione delle query. Il logging laterale delle applicazioni consente di valutare facilmente l'efficacia e l'efficienza delle query nel contesto aziendale. Ad esempio, gli utenti possono registrare il tempo di risposta di ogni query o il tempo di risposta dei log per determinate funzionalità. Questo è anche un modo semplice per ottenere un tempo di esecuzione totale per le transazioni con più istruzioni. 

Inoltre, il tempo di risposta alle query misurato dal logging lato applicazione è una misurazione end-to-end, che include il tempo di rete. Integra il tempo di esecuzione delle query registrato dal database e semplifica l'identificazione della presenza di problemi relativi alla rete o al database.

Query Insights di MySQL di Cloud SQL

Lo strumento Query Insights di Cloud SQL consente l'acquisizione, il monitoraggio e la diagnosi delle query.

Query Insights consente di trovare facilmente le query principali in base al tempo e alla frequenza di esecuzione.

Lo strumento offre opzioni di filtro come intervallo di tempo, database, account utente e indirizzo client. Include grafici per mostrare l'utilizzo della CPU e la suddivisione su attese IO e di blocco. La tabella "Query e tag principali" elenca le query principali per tempo di esecuzione con le query che vengono normalizzate. Oltre al tempo di esecuzione, include statistiche su "righe medie scansionate" e "righe medie restituite" che forniscono insight sull'efficienza delle query.

Consulta la documentazione per vedere tutto ciò che offre lo strumento e come attivarlo.

Utilizzare performance_schema

Su Cloud SQL per MySQL, la funzionalità performance_schema è abilitata per impostazione predefinita per MySQL 8.0.26 e versioni successive con oltre 15 GB di memoria. L'abilitazione o la disabilitazione della funzionalità richiede il riavvio dell'istanza. 

Quando performance_schema=ON, gli strumenti di istruzione query sono abilitati per impostazione predefinita. La tabella sys.statement_analysis fornisce statistiche aggregate per le query normalizzate. Risponde a domande come: 

• Per quali query viene eseguita la scansione completa della tabella?
• full_scan/exec_count: per scoprire se le query eseguono di frequente la scansione completa della tabella, che è spesso una pratica inefficiente 
• Quali query vengono eseguite lentamente?
• avg_latency: tempo medio di esecuzione della query
• Quali query sono inefficienti? 
• rows_examined_avg/rows_examined_avg: per le query di lettura. Il rapporto ideale è 1. Maggiore è il rapporto, più inefficiente è la query. 
• rows_examined_avg/rows_examined_avg: applicabile alle query di scrittura. Il rapporto ideale è 1. Maggiore è il rapporto, più inefficiente è la query. 
• Quali query utilizzano le tabelle temporanee, che devono essere convertite in tabelle temporanee su disco? 
• tmp_disk_tables/tmp_disk_tables: per verificare se il valore tmp_disk_tables/tmp_disk_tables è sufficiente
• Quali query utilizzano Filesort? 
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: per identificare le query con grandi quantità di ordinamento. Potrebbero utilizzare un campo rows_sorted più grande

Se utilizzi MySQL Workbench, è dotato di report sugli schemi delle prestazioni basati sulla visualizzazione del sistema. Il report include una sezione relativa alle istruzioni SQL ad alto costo, che fornisce insight sulle prestazioni delle query.

Utilizza lo slow log + strumenti

Lo slow log acquisisce tutte le query in esecuzione più a lungo di long_query_time. Registra inoltre i tempi di esecuzione delle query, il tempo di blocco, le righe di dati esaminate e le righe di dati inviate. Le statistiche aggiuntive sull'esecuzione lo rendono un candidato preferito per l'analisi delle query del database rispetto all'utilizzo del log generale.

È consigliabile attivare gli slow log. Generalmente, il valore long_query_time deve essere mantenuto a una soglia ragionevole per acquisire le query che intendi esaminare e ottimizzare. 

log_output=FILE

slow_query_log=ON

long_query_time=2

Di tanto in tanto, è opportuno impostare long_query_time=0 in modo da acquisire tutte le query per un breve periodo e ottenere una panoramica del volume e delle prestazioni delle query. 

Esistono strumenti, come mysqldumpslow e pt-query-digest, che estraggono le firme delle query e generano un report per mostrare le statistiche sulle query.   

Esistono altri strumenti di monitoraggio di terze parti che generano report sulle statistiche delle query, ad esempio Monitoraggio e gestione Percona, Monitoraggio delle prestazioni del database di SolarWinds (in precedenza VividCortex) e altri ancora.

Dopo aver acquisito le query nelle transazioni, il passaggio successivo è ottimizzarle.

Il comando EXPLAIN fornisce il piano di esecuzione della query e, a partire dalla versione 8.0.18, il comando EXPLAIN ANALYZE esegue un'istruzione e produce un output EXPLAIN insieme ai tempi dell'esecuzione. 

Query Insight di MySQL fornisce un accesso utile al piano EXPLAIN.

Che cosa cerchiamo nell'output? 

• Il campo "righe" mostra la quantità di righe da leggere 
• L'IO è la parte che richiede più tempo. Se una query deve leggere una grande quantità di dati, è probabile che sia lenta. Per avere un'idea approssimativa, moltiplica le "righe" tra le tabelle unite. Nell'esempio precedente, l'operazione è 858 * 23523. Leggere 23.523 righe da t2 per ognuna delle 858 righe di t1 non sembra un approccio ottimale. Detto ciò, l'ottimizzazione prevede la riduzione della quantità di accesso ai dati da t2 per ogni iterazione.
• Il campo "tipo" descrive il tipo di join della tabella 
• Il tipo "index" corrisponde all'indicizzazione dell'indice. Se l'indice soddisfa tutti i dati richiesti dalla tabella, il campo Extra mostrerà "Utilizza indice". 
• Il tipo "intervallo" significa che non solo viene utilizzato un indice, ma viene anche fornita una condizione di intervallo per limitare la scansione dei dati. 
• Per le tabelle successive in ordine di unione, il tipo "eq_ref" indica che viene letta una riga da questa tabella per ogni combinazione di righe delle tabelle precedenti, che è la soluzione più efficiente. 
• Il tipo "ref" significa che la corrispondenza dell'indice è 1:m anziché 1:1. Da questa tabella verrà letta più di una riga per ogni combinazione di righe delle tabelle precedenti. 
• Il tipo da evitare è "ALL", che significa che viene eseguita una scansione completa della tabella per ogni combinazione di righe dalle tabelle precedenti. 
• Il campo "key" mostra l'indice effettivo in uso. 
• La selezione dell'indice da utilizzare si basa sulla cardinalità dell'indice che potrebbe non essere aggiornata. Pertanto, è importante verificare che venga utilizzato l'indice più selettivo.  
• Il campo "key_len" indica la lunghezza della chiave in byte. 
• Nel caso di un indice a più colonne, key_len suggerisce la parte dell'indice utilizzata. Ad esempio, se un indice ha (col1, col2, col3) e la condizione della query è "col1 = n e col2 come '%string%'", viene usato solo col1 per il filtro degli indici. Se la query potesse essere modificata in "col1 = n e col2 come 'stringa%", allora entrambe (col1, col2) verrebbero utilizzate per il filtro dell'indice. Questa piccola modifica potrebbe fare una notevole differenza nelle prestazioni delle query. 
• Il campo "Extra" contiene ulteriori informazioni sul piano di query 
• "Using temporary" indica la creazione di una tabella temporanea interna che potrebbe generare una tabella temporanea sul disco
• "Using filesort" significa che l'ordinamento non è riuscito a utilizzare alcun indice e richiede un buffer di ordinamento e potenzialmente file disco temporanei 
• "Using index" indica che tutti i dati richiesti da questa tabella sono contenuti nell'indice; non è necessario leggere le righe di dati

Le variabili di stato della sessione possono essere utilizzate per ottenere i dettagli sull'esecuzione della query. 

Innanzitutto, cancella le variabili di sessione, quindi esegui la query ed esamina i contatori. Ad esempio, lo stato Handler_* mostra il pattern di accesso ai dati e la quantità di righe. Created_* viene visualizzato se viene creata una tabella temporanea e/o una tabella temporanea sul disco. Sort_* mostra il numero di pass di unione per l'ordinamento e il numero di righe ordinate. Ulteriori variabili di sessione sono illustrate nella documentazione.

L'istruzione SHOW PROFILE fornisce il tempo di esecuzione della query in base alla fase di esecuzione, un'informazione che potrebbe essere utile.

Una volta compreso il piano di esecuzione della query, esistono diversi modi per influenzarlo e ottimizzarlo. 

• Aggiungere o aggiornare la definizione dell'indice 
• Per un filtro migliore, con meno accesso ai dati
• Per l'ordinamento, evita di ordinare i file
• Aggiornare le statistiche dell'indice se l'opzione non è attiva
• ANALYZE TABLE <tbl>;
• Quindi ricontrolla l'output del piano EXPLAIN
• Usare i suggerimenti per l'indice
• Per suggerire o forzare l'utilizzo di un determinato indice per i filtri, unisci o ordina per/raggruppa per 
• Utilizzare STRAIGHT_JOIN per definire l'ordine di join della tabella 
• Utilizzare i suggerimenti dello strumento per l'ottimizzazione

Per ottimizzare la configurazione del server per alcune query, consigliamo vivamente di utilizzare le variabili a livello di sessione anziché modificare il valore globale che interessa tutte le sessioni. 

I valori di sessione utilizzati di frequente sono:

In breve, per l'ottimizzazione delle query, abbiamo parlato di tre aspetti: 

• Prendere decisioni consapevoli quando si scrivono le query. Queste decisioni sono il fattore trainante per le prestazioni delle query, la velocità effettiva complessiva del server e le prestazioni del server. 
• Monitorare i dati dell'esecuzione della query sia sul lato applicazione sia sul lato database. Il logging lato applicazione è importante. Potrebbe essere configurato in base agli interessi aziendali e rispecchiare le operazioni aziendali. 
• Infine, esistono diversi strumenti che aiutano a comprendere il piano di esecuzione delle query, i costi associati ai diversi passaggi e i modi per ottimizzare le query.