Profilfrågeprestanda i Azure Database for MySQL – flexibel server med hjälp av EXPLAIN
EXPLAIN är ett praktiskt verktyg som kan hjälpa dig att optimera frågor. Du kan använda en EXPLAIN-instruktion för att få information om hur SQL-instruktioner körs. Följande visar exempelutdata från körning av en EXPLAIN-instruktion.
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
I det här exemplet är värdet för nyckeln NULL, vilket innebär att Azure Database for MySQL – flexibel server inte kan hitta några index som är optimerade för frågan. Därför utför den en fullständig tabellgenomsökning. Nu ska vi optimera den här frågan genom att lägga till ett index i ID-kolumnen och sedan köra EXPLAIN-instruktionen igen.
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
Nu visar utdata att Azure Database for MySQL– flexibel server använder ett index för att begränsa antalet rader till 1, vilket avsevärt förkortar söktiden.
Täckande index
Ett täckande index innehåller alla kolumner i en fråga, vilket minskar värdehämtningen från datatabeller. Följande GROUP BY-instruktion och relaterade utdata illustrerar detta.
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
Utdata visar att Azure Database for MySQL – flexibel server inte använder några index eftersom rätt index inte är tillgängliga. Utdata visar även Använda temporär; Med filesort, vilket anger att Azure Database for MySQL– flexibel server skapar en tillfällig tabell för att uppfylla GROUP BY-satsen .
Att bara skapa ett index på kolumn c2 gör ingen skillnad, och Azure Database for MySQL – flexibel server behöver fortfarande skapa en tillfällig tabell:
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
I det här fallet kan du skapa ett täckt index på både c1 och c2 genom att lägga till värdet c2 direkt i indexet, vilket eliminerar ytterligare datasökning.
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
Som utdata från EXPLAIN ovan visar använder Azure Database for MySQL flexibel server nu det täckta indexet och undviker att behöva skapa en tillfällig tabell.
Kombinerat index
Ett kombinerat index består av värden från flera kolumner och kan betraktas som en matris med rader som sorteras genom sammanlänkning av värdena för de indexerade kolumnerna. Den här metoden kan vara användbar i en GROUP BY-instruktion .
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
Azure Database for MySQL – flexibel server utför en filsorteringsåtgärd som är ganska långsam, särskilt när den måste sortera många rader. Om du vill optimera den här frågan skapar du ett kombinerat index för båda kolumnerna som sorteras.
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
Utdata från EXPLAIN-instruktionen visar nu att Azure Database for MySQL – flexibel server använder ett kombinerat index för att undvika ytterligare sortering eftersom indexet redan är sorterat.
Slutsats
Du kan öka prestanda avsevärt genom att använda EXPLAIN tillsammans med olika typer av index. Att ha ett index i en tabell betyder inte nödvändigtvis att Azure Database for MySQL – flexibel server kan använda det för dina frågor. Verifiera alltid dina antaganden med hjälp av EXPLAIN och optimera dina frågor med hjälp av index.