Forum Clubic

[tutorial] [mySQL] Du Profiling de requêtes dans MySQL

Du Profiling de requêtes dans MySQL

Quand on construit une application avec un SGBD, on essaie d’optimiser ses requêtes afin de ne pas surcharger la base de données. Et comme chaque SGBD a ses spécificités, chacun prévoit des outils propres pour aider le développeur à optimiser ses requêtes et ses tables.

Je me baserai pour l’exemple sur la base de données World, téléchargeable sur le site officiel.

1) Comparaison de temps d’exécution avec Last_query_cost
Prenons deux requêtes renvoyant les mêmes résultats. Je choisis d’étudier le cas dont j’ai parlé dans mon topic de pagination.

A. La requête classique :

SQL
[color=blue;font-weight:bold]SELECT[/color] ID, Name

FROM City LIMIT 4000, 10

B. Celle qui est bidouillée :

SQL
SET @nb =0;

[color=blue;font-weight:bold]SELECT[/color] ID, Name, @nb := @nb +1
FROM City WHERE ID > 4000 AND @nb < 10;

La requête suivante, très simple, permet d’estimer le coût de la dernière requête exécutée pour MySQL. L’unité n’est pas temporelle, c’est une sorte d’indice permettant de comparer plusieurs requêtes.

SQL
SHOW status LIKE 'last_query_cost';

Je précise qu’il vaut mieux passer directement par l’invite de commandes mysql parce que PHPMyAdmin et même MySQL Query Browser ont tendance à déconner avec ce genre de résultats [pour y accéder mysql -uvotre_utilisateur (sans espace)].

Les résultats justement :
A. 884.520924
B. 113.009000

Sympa pour voir la différence non ? On voit que la deuxième requête sur un parcours d’environ 4000 enregistrements est à peu près 8 fois plus rapide…

2) Analyse du parcours des tables avec EXPLAIN

La syntaxe de la commande EXPLAIN est très simple, il suffit de rajouter le mot clé au début d’une requête de sélection pour que MySQL renvoie un résultat “bizarre”.
Là encore, nous allons comparer deux requêtes :

A. Une sous-requête

SQL
EXPLAIN [color=blue;font-weight:bold]SELECT[/color] Name

FROM City WHERE CountryCode = (
[color=blue;font-weight:bold]SELECT[/color] Code
FROM Country WHERE Name = ‘FRANCE’ ) ;

MySQL nous renvoie :

id  select_type  table    type  possible_keys  key   key_len  ref   rows  Extra
1   PRIMARY      City     ALL   NULL           NULL  NULL     NULL  4079  Using where
2   SUBQUERY     Country  ALL   NULL           NULL  NULL     NULL  239   Using where

Traduction : “On me demande de parcourir la table City en utilisant une clause WHERE, j’estime que je devrai lire 4079 enregistrements (ndlt : toute la table :D), je ne peux pas utiliser de clé (possible_key est à NULL). Je dois aussi exécuter une sous-requête sur la table Country en parcourant tous les enregistrements avec une clause WHERE là aussi, sans pouvoir utiliser d’index.”

B. La même requête avec une jointure

SQL
EXPLAIN [color=blue;font-weight:bold]SELECT[/color] c.Name

FROM City c, Country co
WHERE CountryCode = Code AND co.Name = ‘FRANCE’

Le résultat :

id  select_type  table  type     possible_keys  key      key_len  ref                  rows  Extra
1   SIMPLE       c      ALL      NULL           NULL     NULL     NULL                 4079   
1   SIMPLE       co     eq_ref   PRIMARY        PRIMARY  3        world.c.CountryCode  1     Using where

Traduction : “Je parcours les tables appelées c et co (alias) en simultané. Pour la première, je parcours encore tous les enregistrements sans pouvoir utiliser de clé ; pour la seconde par contre, je n’ai qu’une égalité simple à traiter sur la clé primaire, que j’utilise et qui mesure trois caractères. Je n’estime donc n’avoir qu’un seul enregistrement à lire.”

Déjà, on voit que sur une petite requête, le refactoring est efficace.
Essayons de rajouter une clé sur la table City afin de voir le résultat de la requête A :

C. La sous-requête après l’ajout d’un index sur le champ CountryCode de la table City :

id  select_type  table    type  possible_keys    key              key_len  ref    rows  Extra
1   PRIMARY      City     ref   cle_CountryCode  cle_CountryCode  3        const  39    Using where
2   SUBQUERY     Country  ALL   NULL             NULL             NULL     NULL   239   Using where

Traduction : “Je parcours d’abord la table City. Oh je peux utiliser la cle_CountryCode, je l’utilise ! L’efficacité de la recherche est maximale (type ref), je n’estime devoir parcourir que 39 lignes de résultats. La deuxième requête reste inchangée.”

Au total : 39 + 239 = 278 lignes parcourues face à 4080 pour la requête B.
Avec une clé bien placée, la requête la plus rapide n’est donc pas celle qu’on pense. :wink:

Je n’ai fait que survoler les possibilités et la syntaxe d’EXPLAIN, pour plus d’informations, veuillez regarder la doc.

PS : un last_query_cost sur les deux requêtes (avec la clé) montre que la sous-requête est 100 fois plus efficace (46.799 contre 4963.521).

Edit : voilà, après l’aide que vous m’avez apportée sur mon topic de révisions, j’ai décidé de faire quelque chose d’un peu plus long. :slight_smile:

question bete :
comment on recupere la valeur de @nb construit a partir de la requete :
SET @nb =0;
SELECT ID, Name, @nb := @nb +1
FROM City WHERE ID > 4000 AND @nb < 10; ?

Comment ça ?
Dans un autre langage de prog’ ?
Soit en passant par le numéro de colonne, soit en renommant la colonne avec un “AS” je pense.

me suis mal exprimé
Si j’ai bien compris cette variable (@nb) permet de connaitre le nombre de ligne retourné par la requete.
Dans un prog java, est ce possible de récuperer sa valeur?
(resultset.getString("@nb))??

Heu, tu m’excuseras, mais tu oublie de préciser un truc relativement important :

SQL
SET @nb =0;

[color=blue;font-weight:bold]SELECT[/color] ID, Name, @nb := @nb +1
FROM City WHERE ID > 4000 AND @nb < 10;

Rien ne dit qu’il y aura des identifiants triés dans le bon ordre. Si j’insère 4001, 5000, 4002, … tu as toutes les chances d’avoir quelques choses dans l’ordre de base = à priori insertion.

Donc déjà, ça foire un peu sur ce plan là. La bonne requête est :

SQL
[color=blue;font-weight:bold]SELECT[/color] ID, Name

FROM City WHERE ID > 4000
ORDER BY ID ASC LIMIT 10

(y a pas ta solution des variables, puisque le WHERE porte AVANT le tri, donc ça ne serait pas consistant).

Pire que ça : si tu reprend le contexte :

SQL
[color=blue;font-weight:bold]SELECT[/color] ID, Name

FROM City LIMIT 4000, 10

= prendre 10 éléments dans n’importe quel ordre, à partir du 4000, soit : prendre un curseur, l’incrémenter (ou le faire aller) de 4000 vers l’avant, et renvoyer les 10 premiers résultats.

SQL
SET @nb =0;

[color=blue;font-weight:bold]SELECT[/color] ID, Name, @nb := @nb +1
FROM City WHERE ID > 4000 AND @nb < 10;

= prendre l’index ID, rechercher dans la structure B+Tree (ou B-, ou R Tree, ou Arbre blanc rouge, ou que sais je) tout ce qui est > 4000 dans un ordre potentiellement ascendant. Pour chaque tuple, incrémenter une variable locale de 1, et s’arrêter quand elle vaut 10.

C’est subtil, mais ça n’est pas du tout du tout la même chose. Dans un cas tu suppose que l’ordre lors de l’insertion est respecté, ce qui n’est pas forcément le cas : si tu as effacé un tuple, par exemple le tout premier, le SGBD - s’il est pas trop con - devrait tenter de réutiliser l’espace.

Résultat : les deux requêtes ne font pas du tout les mêmes choses, dont l’optimisation oui, mais pas à outrance… maintenant, honnêtement je n’ai pas testé, et je suis pas pour la bidouille (car bon, autant prendre un vrai SGBD (pgSQL, Oracle, MsSQL et autant se payer un cluster, et autres…), ça peut marcher, mais j’ai des doutes quand à l’égalité des requêtes par rapport à ce que tu veux. Pour moi, le tri est un prerequis qui n’est pas acquis…

jmgchouchou> comme ça

SQL
[color=blue;font-weight:bold]SELECT[/color] ID, Name, (@nb := @nb +1) AS nb

FROM City WHERE ID > 4000 AND @nb < 10; ?

(sinon avec un autre champ @nb AS … ou par l’index, et dans ce cas vaut mieux le mettre au tout début).

-> rs.getInteger("nb") ou rs.getInteger(2) // ou 3.

Excusez-moi, j’étais au tél.

En fait, je ne dis pas que les deux requêtes sont équivalentes, je les ai simplement prises en exemple parce que dans la table City il n’y a pas de trou. :neutre:
Je l’ai bien précisé au début justement parce qu’en fait dans mes explications, ce ne sont pas vraiment les requêtes qui m’intéressaient mais la manière de les profiler.

Et dans un cas où je sais qu’il n’y a pas de trou, que les résultats renvoyés sont les mêmes, alors mes deux requêtes seront équivalentes.

PS : c’est pas un tuto, c’est une astuce du jour. :o

Sans-Nom -> merci :jap:

mandarounet -> maintenant c’est un tuto, et ça permet aux débutant de se former.

qu’est ce que te renvoit mysql sur un :

SQL
EXPLAIN [color=blue;font-weight:bold]SELECT[/color] c.Name

FROM City c
INNER JOIN Country co
ON co.CountryCode = c.Code
WHERE co.Name = ‘FRANCE’

?

Il arrive à l’optimiser :slight_smile:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  co  ALL  PRIMARY  NULL  NULL  NULL  239  Using where
1  SIMPLE  c  eq_ref  PRIMARY  PRIMARY  4  world.co.Code  1  Using where

(par contre j’ai la flemme de mettre en forme, c’était le plus long dans mon post)

240 lignes parcourues, c’est clairement mieux !
Ça te dérange si je le rajoute dans le premier post ? C’est intéressant pour voir comment l’écriture d’une requête permet de faciliter la vie à un optimiseur.

Edit : j’ai corrigé ta requête.

oui bien sur que tu peux la mettre dans le premier post; et puis pour la syntaxe, j’avais fait ça à la volée :whistle: