Partager via

Performances des requêtes de profil dans le Serveur flexible Azure Database pour MySQL en utilisant l’instruction EXPLAIN

  • Article

S'APPLIQUE À : Azure Database pour MySQL - Serveur unique Azure Database pour MySQL - Serveur flexible

Important

Le serveur unique Azure Database pour MySQL est en voie de mise hors service. Nous vous conseillons vivement de procéder à une mise à niveau vers Azure Database pour MySQL – Serveur flexible. Pour obtenir plus d’informations sur la migration vers Azure Database pour MySQL – Serveur flexible, consultez Qu’en est-il du Serveur unique Azure Database pour MySQL ?

L’instruction EXPLAIN est un outil pratique qui peut vous aider à optimiser des requêtes. Vous pouvez utiliser une instruction EXPLAIN pour obtenir des informations sur la façon dont les instructions SQL sont exécutées. L’exemple suivant montre la sortie de l’exécution d’une instruction 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

Dans cet exemple, la valeur de key est NULL, ce qui signifie que le Serveur flexible Azure Database pour MySQL ne peut pas localiser d’index optimisés pour la requête. Par conséquent, il effectue une analyse de table complète. Nous allons optimiser cette requête en ajoutant un index sur la colonne ID, puis réexécuter l’instruction 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

À présent, le résultat indique que le Serveur flexible Azure Database pour MySQL utilise un index pour limiter le nombre de lignes à 1, ce qui réduit considérablement le temps de recherche.

Index de couverture

Un index de couverture inclut toutes les colonnes d’une requête, ce qui a pour effet de réduire l’extraction de valeurs à partir des tables de données. L’instruction GROUP BY suivante et la sortie associée illustrent cela.

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

Le résultat indique que le Serveur flexible Azure Database pour MySQL n’utilise aucun index, car les index appropriés ne sont pas disponibles. Il indique aussi Using temporary; Using filesort, ce qui signifie que le Serveur flexible Azure Database pour MySQL crée une table temporaire pour satisfaire la clause GROUP BY.

La création d’un index uniquement sur la colonne c2 ne fait aucune différence, et le Serveur flexible Azure Database pour MySQL doit toujours créer une table temporaire :

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

Dans ce cas, il est possible de créer un index couvert sur les colonnes c1 et c2, en ajoutant la valeur de c2 directement dans l’index afin d’éliminer toute recherche de données supplémentaire.

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

Comme le montre le résultat de l’instruction EXPLAIN ci-dessus, le Serveur flexible Azure Database pour MySQL utilise désormais l’index couvert, ce qui évite de devoir créer une table temporaire.

Index combiné

Un index combiné est constitué de plusieurs colonnes et peut être considéré comme un tableau de lignes triées en concaténant les valeurs des colonnes indexées. Cette méthode peut être utile dans une instruction 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

Le Serveur flexible Azure Database pour MySQL effectue une opération de tri de fichiers qui est relativement lente, en particulier quand le tri porte sur un grand nombre de lignes. Pour optimiser cette requête, créez un index combiné sur les deux colonnes qui font l’objet du tri.

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

Le résultat de l’instruction EXPLAIN montre désormais que le Serveur flexible Azure Database pour MySQL utilise un index combiné pour éviter un tri supplémentaire, car l’index est déjà trié.

Conclusion

Vous pouvez augmenter considérablement les performances en utilisant l’instruction EXPLAIN avec différents types d’index. L’existence d’un index sur une table ne signifie pas nécessairement que le Serveur flexible Azure Database pour MySQL pourra l’utiliser pour vos requêtes. Validez toujours vos hypothèses en utilisant la commande EXPLAIN, et optimisez-les à l’aide d’index.

Étapes suivantes

  • Pour trouver des réponses de pairs aux questions qui vous préoccupent le plus, ou pour poster une question ou répondre à une question, visitez le forum Stack Overflow.