Profilatura delle prestazioni delle query in Database di Azure per MySQL - Server flessibile tramite EXPLAIN
SI APPLICA A:
Database di Azure per MySQL - Server singolo Database di Azure per MySQL - Server flessibile
Importante
Database di Azure per MySQL server singolo si trova nel percorso di ritiro. È consigliabile eseguire l'aggiornamento a Database di Azure per MySQL server flessibile. Per altre informazioni sulla migrazione a Database di Azure per MySQL server flessibile, vedere Che cosa accade a Database di Azure per MySQL server singolo?
EXPLAIN è uno strumento utile che consente di ottimizzare le query. È possibile usare un'istruzione EXPLAIN per ottenere informazioni sulla modalità di esecuzione delle istruzioni SQL. Di seguito viene illustrato l'output di esempio dell'esecuzione di un'istruzione EXPLAIN.
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 10.00
Extra: Using where
In questo esempio il valore della chiave è NULL, il che significa che Database di Azure per MySQL server flessibile non è in grado di individuare indici ottimizzati per la query. Di conseguenza, esegue un'analisi completa della tabella. È possibile ottimizzare questa query aggiungendo un indice nella colonna ID e quindi eseguire di nuovo l'istruzione EXPLAIN.
mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
L'output mostra ora che Database di Azure per MySQL server flessibile usa un indice per limitare il numero di righe a 1, riducendo notevolmente il tempo di ricerca.
Indice di copertura
Un indice di copertura include tutte le colonne di una query, che riduce il recupero di valori dalle tabelle dati. L'istruzione GROUP BY seguente e l'output correlato illustrano questa operazione.
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
L'output mostra che Database di Azure per MySQL server flessibile non usa indici, perché gli indici appropriati non sono disponibili. L'output mostra anche Using temporary; Utilizzando filesort, che indica che Database di Azure per MySQL server flessibile crea una tabella temporanea per soddisfare la clausola GROUP BY.
La creazione di un indice solo nella colonna c2 non fa differenza e Database di Azure per MySQL server flessibile deve comunque creare una tabella temporanea:
mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
In questo caso, è possibile creare un indice coperto sia in c1 che in c2 aggiungendo il valore di c2" direttamente nell'indice, eliminando così ulteriori ricerche di dati.
mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: covered
key: covered
key_len: 108
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using index
Come illustrato nell'output di EXPLAIN precedente, Database di Azure per MySQL server flessibile usa ora l'indice coperto ed evita di dover creare una tabella temporanea.
Indice combinato
Un indice combinato è costituito da valori da più colonne e può essere considerato una matrice di righe ordinate concatenando i valori delle colonne indicizzate. Questo metodo può essere utile in un'istruzione GROUP BY.
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using filesort
Database di Azure per MySQL server flessibile esegue un'operazione di ordinamento dei file piuttosto lenta, soprattutto quando è necessario ordinare molte righe. Per ottimizzare questa query, creare un indice combinato in entrambe le colonne ordinate.
mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: NULL
key: my_sort2
key_len: 108
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where; Using index
L'output dell'istruzione EXPLAIN mostra ora che Database di Azure per MySQL server flessibile usa un indice combinato per evitare l'ordinamento aggiuntivo quando l'indice è già ordinato.
Conclusione
È possibile aumentare significativamente le prestazioni usando EXPLAIN insieme a diversi tipi di indici. La presenza di un indice in una tabella non significa necessariamente che Database di Azure per MySQL server flessibile possa usarlo per le query. Convalidare sempre i presupposti usando EXPLAIN e ottimizzare le query usando gli indici.
Passaggi successivi
- Per trovare risposte peer alle domande più importanti o per pubblicare o rispondere a una domanda, visitare Stack Overflow.