Optimisation des requêtes MySQL en PHP

accueil > Cours > MYSQL > Optimisation des requêtes MySQL en PHP

Cours / tutoriel écrit le 17-06-2010 par sky

Introduction

Vous avez des problèmes de lenteur de chargement de vos pages ? Si vous utilisez MySQL dans vos script, il y a de grande chance que vous puissiez résoudre une bonne partie de ces lenteur en optimisation vos requêtes MySQL.

Les scripts PHP sont très rapide à traiter et ne ralentissent pas significativement vos pages ... du moins, là aussi il y a des exeptions, mais en général ce sont les rquêtes MySQL qui demande le plus de temps pour être traité. Il est donc VITAL pour votre site et le serveur de bien penser la structure de vos tables MySQL et de bien optimiser vos requête MySQL.

Le fais de créer une fonction qui gère vos requête MySQL améliore significativement le temps de traitement et d'exécution.

Bien choisir vos type de champs

On en parlera d'une façon plus appronfondi dans un autre cours dans la section Cours MySQL, mais voici un rapide aperçu.
Quand on commence la programmation en PHP, on ne sais pas du tout à quoi sert le type des champs MySQL. Quand on ouvre phpMyAdmin, on aurai tendance à mettre un peut n'importe quoi sans vraiment savoir ce qu'on fais. Ne pas faire attention à ces détails suffit pour alonger le temps de réponse de vos scripts et alourdir votre base de donnée. (en poid)

Une page reference pour les types de champs MySQL est sur le site de mysql.com bien sur : http://dev.mysql.com/doc/mysql/en/column-types.html
Je n'ai pas encore trouver l'équivalence en français, mais cela ne saurai tarder. Etudier cette page est très important.
On va juste jeter un coup d'oeil aux champs numériques qui sont le moins compris.

Type	Bytes	Valeur Minimum	Valeur Maximum
		(Signed/Unsigned)	(Signed/Unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

Quand vous avez un champ que vous savez être numérique, vous avez peut être tendance à utiliser INT comme type. Comme vous pouvez le voir, INT peut stocker des chiffres aussi bas que -2,147,483,648 jusqu'a 2,147,483,647 et chaque "entré" utilise 4 bytes. Bon maintenant, combien de fois avez vous besoin d'entré un chiffre plus grand que 2 millions ?

Disons que vous avez une table Utilisateurs et que pour le champ ID vous devez choisir qu'elle type utiliser. Demandez vous combien d'utilisateur vous pensez avoir ... 99 ? 999 ? 9999 ? plus ? En général, un petit site communautaire n'a guère plus de 999 membres. Donc, on pourrais utiliser le type SMALLINT pour le champ ID. Vuq ue vous n'aller pas avoir plus de 999 membres, vous spécifier 3 étant la longueur, ce qui verut dire que vous pourrez stocker des chiffres allant de 1 à 999.

Consulter ce lien pour les autres types de champs : http://dev.mysql.com/doc/mysql/en/column-types.html

Sélectionner des données inutiles

Est ce que sélectioner un seul champ et sur une ligne (une ligne de donnée, genre id/pseudo/email/signature/texte) nescessite moins de temps que sélectionner tous les champs de toutes les lignes : OUI, bien sur ! Ce qui veut dire que la requête suivante :

SELECT col1, col2, col3 FROM table1
sera plus long à exécuter que :
SELECT col1 FROM table1

Beaucoup de requête comme le 1er exemple peut ralentir le temps de chargement de la page.
Voici un autre exemple que l'on voi très souvent :

<?php
$utilisateurs = array();
$exe = mysql_query('SELECT id, prenom, email FROM utilisateurs');

while ($ligne = mysql_fetch_array($exe))
{
    $utilisateurs[$ligne['id']] = $ligne['prenom'];
}
?>

Dite moi exactement à quoi ça sert de sélectionner l'email de l'utilisateur ?
Bref, c'est pour vous dire qu'il faut uniquement sélectionner les champs dont on a vraiment besoin.

Disons que la requête la plus abominable que l'on peut trouvé est celle-ci :

<?php
$utilisateurs = array();
$exe = mysql_query('SELECT * FROM utilisateurs');

while ($ligne = mysql_fetch_array($exe))
{
    $utilisateurs[$ligne['id']] = $ligne['prenom'];
}
?>

Cette requête est normalement utilisé par les programmeurs qui sont flemmard. Dans ce cas, non seulement on augmente le temps de génération de la page, mais en plus on vient de commettre un déli grave en spécifiant le joker (*) pour les champs. Quand on utilise un joker (*) on fais en faite DEUX requêtes MySQL :

• 1) Chercher tous les champs qui correspondrer au joker.
• 2) Sélectionner les bonne colonnes selon les bonne lignes.

Ceci veut dire que l'on peut diminuer de 50% le temps de vos requête rien qu'en enlevant ces fameux oker (*) et en spécifiant chaque les champs que 'lon va utiliser.

Trop de requête est aussi source de ralentissement des requêtes.
En voici un bon exemple :

<?php
$_CONFIG = array();
$exe = mysql_query('SELECT * FROM config');

while ($ligne = mysql_fetch_assoc($exe))
{
    $_CONFIG[$ligne['config_name']] = $ligne['config_value'];
}

$exe = mysql_query('SELECT COUNT(*) as total FROM posts');
$ligne = mysql_fetch_assoc($exe);

echo "Il y a un total de {$ligne['total']} posts dans ce forum.";
?>

On sais déjà que l'on devai pas utiliser le joker (*) et plus plutôt utiliser SELECT config_name, config_value, et dans la seconde requête on ne devrai pas utilser le COUNT(*) mais COUNT(id).

On pourrais juste faire ces changements et être content d'avoir supprimer 2 requête mysql sans aucun effort, mais on peut encore faire beaucoup mieu.
Pour qu'elle raison devrait on chaque fois compter le nombre de réponse dans le forum ? Si à chaque fois que l'on poste une question/réponse sur lez forum on incrémenter de 1 un champ qui nous indiquerai le nombre de post total sur le forum ?
En sachant ceci, le script ressemblerai maintenant à ceci :

<?php
$_CONFIG = array();
$exe = mysql_query('SELECT config_name, config_value FROM config');

while ($ligne = mysql_fetch_assoc($exe))
{
    $_CONFIG[$ligne['config_name']] = $ligne['config_value'];
}

echo "Il y a un total de { $_CONFIG['total_forum_posts']} posts dans ce forum.";
?>

Et voilà ! A chaque chargement de la page nous avons éliminer une requête. Bien sur à chaque envoi d'un message sur le forum nous devons bein faire attention de bien incrémenter de 1 le nombre total de messages.

Il ne faut jamais oublier que le but du jeux est de faire le moins requête et de sélectionner chque fois le moins de donner que possible.

 
Cette article est traduite de cette page en anglais : http://pixelfull.com/tutorials.php_tutorial=view&id=38
N'hésité pas à en venir en parler sur le forum sur ce topic : optimisation des requête mysql.
  
  

---

Commentaires