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 :
FROM City LIMIT 4000, 10
B. Celle qui est bidouillée :
[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.
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
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
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. 
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. 