Optimiser Tables et Requêtes

Nous allons voir comment et à quoi servent ANALYZE TABLE et OPTIMIZE TABLE, et dans un second temps, détailler le résultat que nous donne un EXPLAIN effectué sur une requête.

Optimiser ses tables

Deux commandes sont accessibles :

ANALYZE TABLE nomTable

Cette procédure demande au moteur de mettre à jour les calculs statistiques concernant les index, pour que l’optimiseur de requêtes soit au point dans le choix de ses méthodes ; choix que l’ont peut voir dans un EXPLAIN expliqué au chapitre suivant.

OPTIMIZE TABLE nomTable

Optimiser une table revient à la défragmenter, ce qui est très utile pour les tables de type DYNAMIC. Cela permet également de restructurer la table et retrier ses index (ce qui peut prendre un temps certain). On appellera cette procédure de façon périodique pour une table subissant de nombreuses mises à jour, de type DELETEINSERTUPDATE.

Optimiser des requêtes

Depuis la version 5.0.1, on peut utiliser conjointement la variable last_query_cost pour comparer le coût de la dernière requête effectuée.

EXPLAIN s’utilise très simplement, il suffit de le précéder au SELECT. La résultat nous donne plusieurs informations, dont quelques unes essentielles, et notamment le type de SELECT, qui nous donne l’information de la complexité du traitement. Voici différents types pouvant être renvoyés, classés du moins performant au plus rapide :

  • ALL
  • La table entière est parcourue ligne par ligne. Très mauvais, il manque sûrement un index, ou la recherche se fait sur un sous ensemble du champ ne commençant pas par son début, par exemple un ... LIKE '%abc....

  • INDEX
  • Tout comme ALL, mais on ne parcourt que l’index, pas la table.

  • RANGE
  • La lecture se fait sur un sous-ensemble de la totalité des lignes de la table. Ceci n’est efficace que si l’unicité de l’index est élevé dans la table (la cardinalité de l’index rejoint le nombre de lignes de la table).

  • INDEX_SUBQUERY
  • Remplace la sous requête par les lignes correspondantes à l’index pas forcément unique.

  • UNIQUE_SUBQUERY
  • Tout comme INDEX_SUBQUERY, mais plus rapide car l’index est unique.

  • INDEX_MERGE
  • Avant la version 5.0 de MySQL, un seul index ne pouvait être utilisé par requête. Maintenant, il est possible d’en utiliser plusieurs dans certains cas : quand le nombre d’enregistrement n’est pas trop élevé et si la requête s’y prête.

  • REF, REF_OR_NULL
  • Recherche des valeurs indexées (nulles ou pas) pas forcément uniques dans la table.

  • EQ_REF
  • Utilise le résultat d’une partie de la requête, qui sert de base pour compléter la suite de la requête (pour satisfaire un AND par ex).

  • CONST
  • On recherche dans une colonne indexée et unique une constante.

Cette liste n’est pas exhaustive, je vous invite à consulter la doc MySQL pour tous les détails.

La partie rows renvoyée par EXPLAIN nous donne une approximation du nombre de lignes qui devront être lues pour satisfaire la requête. Il est nécessaire, pour que le calcul soit crédible, qu’on ait effectué un ANALYZE TABLE ou un OPTIMIZE TABLE sur les tables concernées par l’opération.

Une autre donnée importante nous est renvoyée par EXPLAIN, la colonne extra, dont voici quelques résultats intéressants :

  • using_filesort
  • Un tri doit être effectué par MySQL dans un deuxième temps, et l’index correspondant au tri n’existe pas.

  • using_index
  • La requête n’a pas besoin de lire dans la table, elle ne lit que les informations de l’index (ex : SELECT id FROM table;).

  • using temporary
  • Souvent couplée avec using_filesort, MySQL crée ici une table temporaire en mémoire pour pouvoir effectuer ses calculs.

  • using intersect
  • Lorsqu’on utilise plusieurs index (index_merge) dont la PRIMARY KEY, et que la table ne contient pas trop de lignes.

  • Influencer l’optimiseur :
  • Bien souvent, l’optimiseur ne débrouille très bien tout seul, mais il peut arriver, pour des requêtes complexes, ou pour une raison x ou y, qu’on ait besoin d’orienter les choix faits par l’optimiseur MySQL pour ses méthodes de sélections.

  • USE INDEX
  • Indique à MySQL de préférer tel index plutôt qu’un autre, quand le choix est possible (... FROM table USE INDEX (nomIndex) ...).

  • FORCE INDEX
  • Comme le USE INDEX, mais indique en plus à MySQL de ne pas choisir l’option de parcourir séquentiellement la table quand l’index demandé est accessible (il existe).

Enfin, pour certaines requêtes, plus complexes à optimiser, MySQL demande plus de calculs pour choisir la meilleure option. Mais dans ce cas, les performances peuvent s’en retrouver amoindries. Deux stratégies sont configurables :

  • optimize_prune_level
  • 1 : évite d’étudier certaines stratégies qui provoquent la lecture d’un trop grand nombre d’enregistrements.
    0 : Demande à MySQL d’examiner plus de possibilités

  • optimizer_search_depth
  • Un grand nombre permet de passer plus de temps à chercher la meilleure solution (car analyse de plus de tables).
    Un petit nombre diminue les chances de trouver la meilleure solution, mais fournit rapidement une stratégie.
    Enfin, défini à 0, c’est l’optimiseur qui définit la marche à suivre.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *