Delen via

UITLEG gebruiken om queryprestaties te profilen in Azure Database for MariaDB

  • Artikel

Belangrijk

Azure Database for MariaDB bevindt zich op het buitengebruikstellingspad. We raden u ten zeerste aan om te migreren naar Azure Database for MySQL. Zie Wat gebeurt er met Azure Database for MariaDB voor meer informatie over migreren naar Azure Database for MySQL.

EXPLAIN is een handig hulpmiddel om query's te optimaliseren. EXPLAIN-instructie kan worden gebruikt om informatie op te halen over hoe SQL-instructies worden uitgevoerd. In de volgende uitvoer ziet u een voorbeeld van de uitvoering van een EXPLAIN-instructie.

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

Zoals u in dit voorbeeld kunt zien, is de waarde van de sleutel NULL. Deze uitvoer betekent dat MariaDB geen indexen kan vinden die zijn geoptimaliseerd voor de query en dat er een volledige tabelscan wordt uitgevoerd. We gaan deze query optimaliseren door een index toe te voegen aan de id-kolom .

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

De nieuwe UITLEG laat zien dat MariaDB nu een index gebruikt om het aantal rijen te beperken tot 1, wat op zijn beurt de zoektijd aanzienlijk verkort.

Index dekken

Een dekkingsindex bestaat uit alle kolommen van een query in de index om het ophalen van waarde uit gegevenstabellen te verminderen. Hier volgt een afbeelding in de volgende GROUP BY-instructie .

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

Zoals te zien is in de uitvoer, gebruikt MariaDB geen indexen omdat er geen juiste indexen beschikbaar zijn. Het toont ook Het gebruik van tijdelijk; Met behulp van het sorteren van bestanden, wat betekent dat MariaDB een tijdelijke tabel maakt om te voldoen aan de GROUP BY-component .

Het maken van een index op kolom c2 maakt alleen geen verschil en MariaDB moet nog steeds een tijdelijke tabel maken:

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 dit geval kan een gedekte index op zowel c1 als c2 worden gemaakt, waarbij de waarde van c2 rechtstreeks in de index wordt toegevoegd om verdere gegevenszoekacties te elimineren.

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

Zoals hierboven uitgelegd, gebruikt MariaDB nu de gedekte index en vermijdt u het maken van een tijdelijke tabel.

Gecombineerde index

Een gecombineerde index bestaat uit waarden uit meerdere kolommen en kan worden beschouwd als een matrix van rijen die worden gesorteerd op het samenvoegen van waarden van de geïndexeerde kolommen. Deze methode kan handig zijn in een GROUP BY-instructie .

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

MariaDB voert een bestandssorteerdbewerking uit die vrij traag is, met name wanneer het veel rijen moet sorteren. Als u deze query wilt optimaliseren, kunt u een gecombineerde index maken voor beide kolommen die worden gesorteerd.

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

De UITLEG laat nu zien dat MariaDB gecombineerde index kan gebruiken om extra sortering te voorkomen, omdat de index al is gesorteerd.

Conclusie

Het gebruik van EXPLAIN en een ander type indexen kan de prestaties aanzienlijk verhogen. Als u een index in de tabel hebt, betekent dit niet noodzakelijkerwijs dat MariaDB deze kan gebruiken voor uw query's. Valideer altijd uw veronderstellingen met BEHULP van EXPLAIN en optimaliseer uw query's met behulp van indexen.

Volgende stappen