Performances des requêtes de profil dans Azure Database pour MySQL - Serveur flexible à l’aide d’EXPLAIN

  • Article

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

Important

Azure Database pour MySQL serveur unique se trouve sur le chemin de mise hors service. Nous vous recommandons vivement de procéder à la mise à niveau vers Azure Database pour MySQL serveur flexible. Pour plus d’informations sur la migration vers Azure Database pour MySQL serveur flexible, consultez Ce qui se passe pour Azure Database pour MySQL serveur unique ?

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 la clé est NULL, ce qui signifie que Azure Database pour MySQL serveur flexible 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, la sortie indique que Azure Database pour MySQL serveur flexible utilise un index pour limiter le nombre de lignes à 1, ce qui raccourcit 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

La sortie indique que Azure Database pour MySQL serveur flexible n’utilise aucun index, car les index appropriés ne sont pas disponibles. La sortie affiche également l’utilisation temporaire ; À l’aide de filesort, ce qui indique que Azure Database pour MySQL serveur flexible 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 Azure Database pour MySQL serveur flexible 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 la sortie de l’explication ci-dessus, Azure Database pour MySQL serveur flexible utilise désormais l’index couvert et évite d’avoir à 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

Azure Database pour MySQL serveur flexible effectue une opération de tri de fichier assez lente, en particulier lorsqu’il doit trier de nombreuses 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

La sortie de l’instruction EXPLAIN montre maintenant que Azure Database pour MySQL serveur flexible 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. Avoir un index sur une table ne signifie pas nécessairement que Azure Database pour MySQL serveur flexible peut 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.