Services webmasters
Partenaires
Jeux concours gratuits
 
Optimisation de MySQL
<<<
Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT ) Mesurer les performances d'une requête
>>>

5.2 Optimisation des SELECT s et autres requêtes
5 Optimisation de MySQL
 Manuel de Référence MySQL 4.1 : Version Française

->Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT )
Mesurer les performances d'une requête
Vitesse des requêtes SELECT
Comment MySQL optimise les clauses WHERE
Comment MySQL optimise la clause DISTINCT
Comment MySQL optimise LEFT JOIN et RIGHT JOIN
Comment MySQL optimise les clauses ORDER BY
Comment MySQL optimise la clause LIMIT
Vitesse des requêtes INSERT
Vitesses des commandes UPDATE
Rapidité des requêtes DELETE
Autres conseils d'optimisation

5.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT )


EXPLAIN nom_de_table
ou  EXPLAIN SELECT select_options
EXPLAIN nom_de_table est un synonyme de DESCRIBE nom_de_table ou SHOW COLUMNS FROM nom_de_table .Lorsque vous ajoutez au début d'une commande SELECT le mot clé EXPLAIN , MySQL va expliquer le processus qu'il va suivre pour exécuter la commande SELECT , et donner des détails sur la façon avec laquelle il va joindre les tables, et dans quel ordre.

Avec l'aide de EXPLAIN , vous pouvez identifier les index à ajouter pour accélérer les commandes SELECT .

Vous devriez souvent utiliser la commande ANALYZE TABLE pour mettre à jour les statistiques de cardinalité de vos tables, qui affectent les choix de l'optimisateur. Syntaxe de ANALYZE TABLE .

Vous pouvez aussi voir si l'optimisateur fait les jointures dans un ordre vraiment optimal. Pour forcer l'optimisateur à utiliser un ordre spécifique de jointure dans une commande SELECT , ajoutez l'attribut STRAIGHT_JOIN à la clause.

Pour les jointures complexes, EXPLAIN retourne une ligne d'information pour chaque table utilisée dans la commande SELECT . Les tables sont listées dans l'ordre dans lequel elles seront lues. MySQL résout toutes les jointures avec une seule passe multi-jointure. Cela signifie que MySQL lit une ligne dans la première table, puis recherche les lignes qui correspondent dans la seconde, puis dans la troisième, etc. Lorsque toutes les tables ont été traitées, MySQL affiche les colonnes demandées, et il remonte dans les tables jusqu'à la dernière qui avait encore des lignes à traiter. La prochaine ligne est alors traitée de la même façon.

Avec MySQL version 4.1 l'affichage de EXPLAIN a été modifié pour mieux fonctionner avec les structures comme UNION , sous-requêtes, et tables dérivées. La plus importante évolution est l'addition de deux nouvelles colonnes : id et select_type .

Le résultat de la commande EXPLAIN est constitué des colonnes suivantes :
    id
    identifiant de SELECT , le numéro séquentiel de cette commande SELECT dans la requête.
    select_type
    Type de clause SELECT , qui peut être :
      SIMPLE
      Simple SELECT (sans UNION s ou sous-requêtes).
      PRIMARY
      SELECT extérieur.
      UNION
      Second et autres UNION SELECT s.
      DEPENDENT UNION
      Second et autres UNION SELECTS s, dépend de la commande extérieure.
      SUBSELECT
      Premier SELECT de la sous-requête.
      DEPENDENT SUBSELECT
      Premier SELECT , dépendant de la requête extérieure.
      DERIVED
      Table dérivée SELECT .
    table
    La table à laquelle la ligne fait référence.
    type
    Le type de jointure. Les différents types de jointures sont les suivants, dans l'ordre du plus efficace au plus lent :
      system
      La table a une seule ligne (c'est une table système). C'est un cas spécial du type de jointure const .
      const
      La table a au plus une ligne correspondante, qui sera lue dès le début de la requête. Comme il n'y a qu'une seule ligne, les valeurs des colonnes de cette ligne peuvent être considérées comme des constantes pour le reste de l'optimisateur. Les tables const sont très rapides, car elles ne sont lues qu'une fois.
      eq_ref
      Une ligne de cette table sera lue pour chaque combinaison de ligne des tables précédentes. C'est le meilleur type de jointure possible, à l'exception des précédents. Il est utilisé lorsque toutes les parties d'un index sont utilisées par la jointure, et que l'index est UNIQUE ou PRIMARY KEY .
      ref
      Toutes les lignes avec des valeurs d'index correspondantes seront lues dans cette table, pour chaque combinaison des lignes précédentes. ref est utilisé si la jointure n'utilise que le préfixe de gauche de la clé, ou si la clé n'est pas UNIQUE ou PRIMARY KEY (en d'autres termes, si la jointure ne peut pas sélectionner qu'une seule ligne en fonction de la clé). Si la clé qui est utilisée n'identifie que quelques lignes à chaque fois, la jointure est bonne.
      range
      Seule les lignes qui sont dans un intervalle donné seront lues, en utilisant l'index pour sélectionner les lignes. La colonne key indique quel est l'index utilisé. key_len contient la taille de la partie de la clé qui est utilisée. La colonne ref contiendra la valeur NULL pour ce type.
      index
      C'est la même chose que ALL , sauf que seul l'arbre d'index sera lu et scanné. C'est généralement plus rapide que ALL , car le fichier d'index est généralement plus petit que le fichier de données.
      ALL
      Une analyse complète de la table sera faîte pour chaque combinaison de lignes issue des premières tables. Ce n'est pas bon si la première table n'est pas une jointure de type const et c'est très mauvais dans les autres cas. Normalement vous pouvez éviter ces situations de ALL en ajoutant des index basée sur des parties de colonnes.
    possible_keys
    La colonne possible_keys indique quels index MySQL va pouvoir utiliser pour trouver les lignes dans cette table. Notez que cette colonne est totalement dépendante de l'ordre des tables. Cela signifie que certaines clés de la colonne possible_keys pourraient ne pas être utilisées dans d'autres cas d'ordre de tables.

    Si cette colonne est vide, il n'y a pas d'index pertinent. Dans ce cas, vous pourrez améliorer les performances en examinant votre clause WHERE pour voir si des colonnes sont susceptibles d'être indexée. Si c'est le cas, créez un index ad hoc, et examinez le résultat avec la commande EXPLAIN . Syntaxe de ALTER TABLE .

    Pour connaître tous les index d'une table, utilisez le code SHOW INDEX FROM nom_de_table .
    key
    La colonne key indique l'index que MySQL va décider d'utiliser. Si la clé vaut NULL , aucun index n'a été choisi. Pour forcer MySQL à utiliser un index listé dans la colonne possible_keys , utilisez USE KEY/IGNORE KEY dans votre requête. Syntaxe des SELECT .De plus, exécuter myisamchk --analyze ( Syntaxe d'invocation de myisamchk ) ou ANALYZE TABLE ( Syntaxe de ANALYZE TABLE ) sur la table va aider l'optimiseur à choisir les index.
    key_len
    La colonne key_len indique la taille de la clé que MySQL a décidé d'utiliser. La taille est NULL si la colonne key vaut NULL . Notez que cela vous indique combien de partie d'une clé multiple MySQL va réellement utiliser.
    ref
    La colonne ref indique quelle colonne ou quelles constantes sont utilisées avec la clé key , pour sélectionner les lignes de la table.
    rows
    La colonne rows indique le nombre de ligne que MySQL estime devoir examiner pour exécuter la requête.
    Extra
    Cette colonne contient des informations additionnelle sur comment MySQL va résoudre la requête. Voici une explication des différentes chaînes que vous pourriez trouver dans cette colonne :
      Distinct
      MySQL ne va pas continuer à chercher d'autres lignes que la ligne courante, après en avoir trouvé une.
      Not exists
      MySQL a été capable d'appliquer une optimisation de type LEFT JOIN sur la requête, et ne va pas examiner d'autres lignes de cette table pour la combinaison de lignes précédentes, une fois qu'il a trouvé une ligne qui satisfait le critère de LEFT JOIN .

      Voici un exemple de cela :

      
      SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
      
      Supposons que t2.id est défini comme NOT NULL . Dans ce cas, MySQL va scanner t1 et rechercher des lignes dans t2 via t1.id . Si MySQL trouve une ligne dans t2 , il sait que t2.id ne peut pas être NULL , et il ne va pas scanner le reste des lignes de t2 qui ont le même id . En d'autres termes, pour chaque ligne de t1 , MySQL n'a besoin que de faire une recherche dans t2 , indépendamment du nombre de lignes qui sont trouvées dans t2 .
      range checked for each record (index map: #)
      MySQL n'a pas trouvé d'index satisfaisant à utiliser. Il va, à la place, pour chaque combinaison de lignes des tables précédentes, faire une vérification de quel index utiliser (si il en existe), et utiliser cet index pour continuer la recherche. Ce n'est pas très rapide, mais c'est plus rapide que de faire une recherche sans aucun index.
      Using filesort
      MySQL va avoir besoin d'un autre passage pour lire les lignes dans l'ordre. Le tri est fait en passant en revue toutes les lignes, suivant le type de jointure est stocker la clé de tri et le pointeur de la ligne pour chaque ligne qui satisfont la clause WHERE . Alors, les clés sont triées. Finalement, les lignes sont triées dans l'ordre.
      Using index
      Les informations de la colonne sont lues de la table, en utilisant uniquement les informations contenues dans l'index, sans avoir à faire d'autres lectures. Cela peut arriver lorsque toutes les colonnes utilisées dans une table font partie de l'index.
      Using temporary
      Pour résoudre la requête, MySQL va avoir besoin de créer une table temporaire pour contenir le résultat. C'est typiquement ce qui arrive si vous utilisez une clause ORDER BY sur une colonne différente de celles qui font partie de GROUP BY .
      Using where
      Une clause WHERE sera utilisée pour restreindre les lignes qui seront trouvées dans la table suivante, ou envoyée au client. Si vous n'avez pas cette information, et que la table est de type ALL ou index , vous avez un problème dans votre requête (si vous ne vous attendiez pas à tester toutes les lignes de la table).
    Si vous voulez rendre vos requêtes aussi rapide que possible, vous devriez examiner les lignes qui utilisent Using filesort et Using temporary .
Vous pouvez obtenir une bonne indication de la qualité de votre jointure en multipliant toutes les valeurs de la colonne rows dans la table de la commande EXPLAIN . Cela est une estimation du nombre de lignes que MySQL va examiner pour exécuter cette requête. C'est aussi ce nombre qui sera utilisé pour interrompre votre requête, grâce à la variable max_join_size . Choix des paramètres du serveur .L'exemple ci-dessous illustre comme une requête JOIN peut être optimisée avec les résultats de la commande EXPLAIN .

Supposons que vous avez la requête SELECT suivante, et que vous l'examinez avec EXPLAIN :


EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;
Pour cette exemple, nous supposons que :
  • Les colonnes utilisées sont déclarées comme ceci :
    Table Colonne Type de colonne
    tt ActualPC CHAR(10)
    tt AssignedPC CHAR(10)
    tt ClientID CHAR(10)
    et EMPLOYID CHAR(15)
    do CUSTNMBR CHAR(15)
  • Les tables ont les index suivants :
    Table Index
    tt ActualPC
    tt AssignedPC
    tt ClientID
    et EMPLOYID (primary key)
    do CUSTNMBR (primary key)
  • Les valeurs de tt.ActualPC ne sont pas réparties également.
Initialement, avant toute optimisation, la commande EXPLAIN produit les informations suivantes :

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)
Comme le type type vaut ALL pour chaque table, le résultat indique que MySQL fait un scan complet de toutes les tables. Cela va prendre un très long temps de calcul, car le nombre de lignes à examiner de cette façon est le produit du nombre de lignes de toutes les tables : dans notre cas, cela vaut 74 * 2135 * 74 * 3872 = 45,268,558,720 lignes. Si les tables étaient plus grandes, cela serait encore pire.

Le premier problème que vous avons ici, est que MySQL ne peut pas (encore) utiliser d'index sur les colonnes, si elles sont déclarées différemment. Dans ce contexte, les colonnes VARCHAR et CHAR sont les mêmes, mais elles ont été déclarée avec des tailles différentes. Comme tt.ActualPC est déclarée comme CHAR(10) et que et.EMPLOYID est déclaré comme CHAR(15) , il y a un problème de taille.

Pour corriger cette disparité, utilisez la commande ALTER TABLE pour agrandir la colonne ActualPC de 10 caractères à 15 :

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Maintenant, tt.ActualPC et et.EMPLOYID sont tous les deux des colonnes de type VARCHAR(15) . Exécuter la commande EXPLAIN produit maintenant le résultat suivant :

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    Using where
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1
Ce n'est pas parfait, mais c'est bien mieux. Le produit de toutes les lignes a été divisé par 74). Cette version s'exécute en quelques secondes.

Une autre modification peut être faîte pour éliminer les problèmes de taille de colonne pour tt.AssignedPC = et_1.EMPLOYID et tt.ClientID = do.CUSTNMBR :


mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);
Maintenant, EXPLAIN produit le résultat suivant :

table type   possible_keys   key      key_len ref           rows Extra
et    ALL    PRIMARY         NULL     NULL    NULL          74
tt    ref    AssignedPC,     ActualPC 15      et.EMPLOYID   52   where used
             ClientID,
             ActualPC
et_1  eq_ref PRIMARY         PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY         PRIMARY  15      tt.ClientID   1
C'est presque aussi bon que cela pourrait l'être.Le problème final est que, par défaut, MySQL support que les valeurs de la colonne tt.ActualPC sont uniformément répartie, et que ce n'est pas le cas pour la table tt . Mais il est facile de le dire à MySQL :

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh
Maintenant, la jointure est parfaite, et la commande EXPLAIN produit ce résultat :

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 where used
             ClientID,
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1
Notez que la colonne rows dans le résultat de EXPLAIN est une prédiction éclairée de l'optimisateur de jointure MySQL. Pour optimiser une requête, vous devriez vérifier si ces nombres sont proches de la réalité. Si ce n'est pas le cas, vous pourriez obtenir de meilleures performances avec l'attribut STRAIGHT_JOIN dans votre commande SELECT , et en choisissant vous même l'ordre de jointure des tables dans la clause FROM .

<< Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT ) >>
Optimisation de MySQL Optimisation des SELECT s et autres requêtes Mesurer les performances d'une requête
Services webmasters
Les manuels
 
CoursPHP.com - Reproduction interdite -