Dela via

Profilfrågeprestanda i Azure Database for MySQL – flexibel server med hjälp av EXPLAIN

  • Artikel

GÄLLER FÖR: Azure Database for MySQL – Azure Database for MySQL – enskild server – flexibel server

Viktigt!

Azure Database for MySQL – enskild server är på väg att dras tillbaka. Vi rekommenderar starkt att du uppgraderar till en flexibel Azure Database for MySQL-server. Mer information om hur du migrerar till en flexibel Azure Database for MySQL-server finns i Vad händer med Azure Database for MySQL – enskild server?

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.

Nästa steg

  • Om du vill hitta peer-svar på dina viktigaste frågor eller för att publicera eller besvara en fråga går du till Stack Overflow.