Profilare le prestazioni delle query nel server flessibile del Database di Azure per MySQL usando EXPLAIN
SI APPLICA A:
Database di Azure per MySQL - Server singolo Database di Azure per MySQL - Server flessibile
Importante
Il server singolo del Database di Azure per MySQL è in fase di ritiro. È consigliabile eseguire l'aggiornamento al server flessibile del Database di Azure per MySQL. Per altre informazioni sulla migrazione a Database di Azure per MySQL - Server flessibile, vedere Cosa succede a Database di Azure per MySQL - Server singolo?
EXPLAIN è uno strumento comodo per ottimizzare le query. È possibile usare l'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 il server flessibile del Database di Azure per MySQL non è in grado di individuare indici ottimizzati per la query. Di conseguenza, esegue la scansione completa della tabella. È possibile ottimizzare questa query aggiungendo un indice nella colonna ID e poi 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 il server flessibile del Database di Azure per MySQL 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 seguente GROUP BY 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 il server flessibile del Database di Azure per MySQL non usa indici perché gli indici appropriati non sono disponibili. L'output mostra anche Usare temporary; Usare filesort, che indica che il server flessibile del Database di Azure per MySQL crea una tabella temporanea per soddisfare la clausola GROUP BY.
La creazione di un indice solo nella colonna c2 non fa differenza e il server flessibile del Database di Azure per MySQL 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 di copertura sia su c1 che su c2, aggiungendo il valore di c2 direttamente nell'indice per evitare 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, il server flessibile del Database di Azure per MySQL usa ora l'indice di copertura 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
Il server flessibile del Database di Azure per MySQL 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 da ordinare.
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 il server flessibile del Database di Azure per MySQL usa un indice combinato per evitare un ulteriore ordinamento 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 il server flessibile del Database di Azure per MySQL possa usarlo per le query. Verificare sempre i presupposti usando EXPLAIN e ottimizzare le query con gli indici.
Passaggi successivi
- Per trovare risposte di peer alle domande più importanti o per pubblicare o rispondere a una domanda, visitare Stack Overflow.