![[BANNER]](/templates/iceage/images/banniere.jpg)
![[NEWS]](templates/iceage/images/icon-news.png){kind=icon}
Envoyé par unreal
On dit souvent que MySQL est une base limitée fonctionnellement. Même si cette affirmation est juste, ce n'est pas une raison pour ne pas optimiser ses requêtes car MySQL reste une base très adaptée à l'usage pour lequel il a été conçu : stocker les données de sites Web dynamiques !
Alors, nous allons voir dans ce petit dossier comment optimiser ses requêtes en vue de faire des sites rapides qui exploitent au mieux MySQL.
1. Les préliminaires
Nous supposons que vous utilisez MySQL 4.0 au minimum. Certaines features ne sont pas disponibles dans les version précédentes.
Ensuite, soient t1 et t2 deux tables MySQL :
Règle d'or : bien réflechir à l'organisation de la base avant de commencer à développer. Ce n'est pas utile d'employer des varchar (200) si des (10) suffisent ! Evitez de fragmenter l'information en utilisant trop de tables, de dupliquer les mêmes informations dans plusieurs tables.
2. SELECT * : à éviter !
On le voit tout le temps, mais quand on n'a pas besoin de tous les champs, "select *" est à proscrire car il utilise du temps CPU et de la mémoire en plus pour rien.
L'exemple suivant ne renvoie que les champs t1_c1 et t1_c2 :
3. Utilisation de LIMIT
Quand la requête va renvoyer plusieurs résultats il est important de limiter le nombre d'éléments avec LIMIT. Supposons que t1 contienne 100 000 lignes et qu'on fasse :
Alors, MySQL va retourner 100 000 lignes que php (en supposant que le code soit en php) va essayer d'enregistrer dans un array ! Il y'a de fortes chances que cela prenne très longtemps ou que php arrête l'exécution si la mémoire nécessaire dépasse la valeur fixée par l'administrateur. Alors voilà comment faire :
Cette requête n'enverra que 10 lignes, quelque soit le nombre total d'éléments dans la table.
4. Utilisation d'ORDER BY
La syntaxe habituelle est la suivante :
Le problème est qu'il effectue le classement avant de limiter le nombre de résultats avec LIMIT. Du coup, selon l'ordre ASC ou DESC, la base ne renvoie pas les mêmes résultats (en fait, elle va renvoyer 10 résultats en partant du haut ou du bas de la table selon l'ordre choisi). Evidemment, quand on souhaite afficher les mêmes résultats, avec seulement l'ordre qui change, il faut chercher ailleurs.
Plusieurs solutions existent (selon la version de MySQL) :
4.1. Solution avec MySQL 4.0 (ne fonctionne pas avec MySQL 4.1)
Les connaisseurs reconnaîtront une 1/2 d'UNION qui n'est pas officiellement supportée mais qui marche parfaitement avec MySQL 4.0. L'avantage de cette méthode est que la requête reste très rapide.
4.2. Solution avec MySQL 4.1 (ne fonctionne pas avec MySQL 4.0)
C'est la méthode des subqueries qui n'est présente qu'à partir de MySQL 4.1 :
Avant d'utiliser les subqueries je vous conseille de vous assurer que vous n'aurez pas à garantir la compatibilité descendante.
4.3. Solution avec les UNION (fonctionne avec MySQL 4.0 et 4.1)
Inconvénient : l'UNION complète est un lente.
5. Les jointures
Introduisons maintenant la table t2. La table t2 contient des références sur la table t1 ; on souhaite réaliser une requête sur t2 pour récupérer la référence qui permettra de retrouver l'information souhaitée sur t1.
Il existe bien sûr la bonne et la mauvaise façon de procéder.
5.1. La boucle while : à éviter !!!
C'est probablement ce qu'on peut faire de pire en SQL.
Ensuite, pour chaque valeur de t2_refid (jusqu'à 100 valeurs dans cet exemple), on réalise une nouvelle requête :
On réalise donc jusqu'à 101 (1 + 100) requêtes, ce qui est carrément catastrophique en termes de performances.
De façon générale, il ne faut pas placer des requêtes à l'intérieur de boucles.
5.2. La méthode LEFT JOIN
On obtient exactement le même résultat que l'exemple précédent... avec une seule requête. Gain de performance considérable.
5.3. La méthode INNER JOIN
Cette méthode donne sensiblement le même résultat que la méthode précédente. Il semblerait que ce soit plus rapide sur des grosses tables, mais avec ma base MySQL, les temps de réponse sont identiques.
6. Les fonctions MySQL
Voici quelques exemples d'utilisation des fonctions MySQL.
6.1. Count
6.2. Rand
7. Conclusion
Avec un peu de chance, ce petit guide vous aura servi pour optimiser vos requêtes SQL.
N'oubliez pas de consulter la documentation officielle pour la syntaxe exacte des requêtes et pour lire les commentaires.
Have fun.
Alors, nous allons voir dans ce petit dossier comment optimiser ses requêtes en vue de faire des sites rapides qui exploitent au mieux MySQL.
1. Les préliminaires
Nous supposons que vous utilisez MySQL 4.0 au minimum. Certaines features ne sont pas disponibles dans les version précédentes.
Ensuite, soient t1 et t2 deux tables MySQL :
t1
----------
t1_id int(10)
t1_c1 varchar(20)
t1_c2 varchar(20)
t2
----------
t2_id int(10)
t2_refid int(10)
t2_c1 tinyint(1)
----------
t1_id int(10)
t1_c1 varchar(20)
t1_c2 varchar(20)
t2
----------
t2_id int(10)
t2_refid int(10)
t2_c1 tinyint(1)
Règle d'or : bien réflechir à l'organisation de la base avant de commencer à développer. Ce n'est pas utile d'employer des varchar (200) si des (10) suffisent ! Evitez de fragmenter l'information en utilisant trop de tables, de dupliquer les mêmes informations dans plusieurs tables.
2. SELECT * : à éviter !
On le voit tout le temps, mais quand on n'a pas besoin de tous les champs, "select *" est à proscrire car il utilise du temps CPU et de la mémoire en plus pour rien.
L'exemple suivant ne renvoie que les champs t1_c1 et t1_c2 :
SELECT t1_c1, t1_c2 FROM t1 WHERE t1_id = 20
3. Utilisation de LIMIT
Quand la requête va renvoyer plusieurs résultats il est important de limiter le nombre d'éléments avec LIMIT. Supposons que t1 contienne 100 000 lignes et qu'on fasse :
SELECT t1_c1, t1_c2 FROM t1
Alors, MySQL va retourner 100 000 lignes que php (en supposant que le code soit en php) va essayer d'enregistrer dans un array ! Il y'a de fortes chances que cela prenne très longtemps ou que php arrête l'exécution si la mémoire nécessaire dépasse la valeur fixée par l'administrateur. Alors voilà comment faire :
SELECT t1_c1, t1_c2 FROM t1 LIMIT 0, 10
Cette requête n'enverra que 10 lignes, quelque soit le nombre total d'éléments dans la table.
4. Utilisation d'ORDER BY
La syntaxe habituelle est la suivante :
SELECT t1_c1, t1_c2 FROM t1 ORDER BY t1_id ASC LIMIT 0, 10
Le problème est qu'il effectue le classement avant de limiter le nombre de résultats avec LIMIT. Du coup, selon l'ordre ASC ou DESC, la base ne renvoie pas les mêmes résultats (en fait, elle va renvoyer 10 résultats en partant du haut ou du bas de la table selon l'ordre choisi). Evidemment, quand on souhaite afficher les mêmes résultats, avec seulement l'ordre qui change, il faut chercher ailleurs.
Plusieurs solutions existent (selon la version de MySQL) :
4.1. Solution avec MySQL 4.0 (ne fonctionne pas avec MySQL 4.1)
(SELECT t1_c1, t1_c2 FROM t1 LIMIT 0, 10) ORDER BY t1_id ASC
Les connaisseurs reconnaîtront une 1/2 d'UNION qui n'est pas officiellement supportée mais qui marche parfaitement avec MySQL 4.0. L'avantage de cette méthode est que la requête reste très rapide.
4.2. Solution avec MySQL 4.1 (ne fonctionne pas avec MySQL 4.0)
C'est la méthode des subqueries qui n'est présente qu'à partir de MySQL 4.1 :
SELECT * FROM (SELECT t1_c1, t1_c2 FROM t1 LIMIT 0, 10) AS tbl ORDER BY t1_id ASC
Avant d'utiliser les subqueries je vous conseille de vous assurer que vous n'aurez pas à garantir la compatibilité descendante.
4.3. Solution avec les UNION (fonctionne avec MySQL 4.0 et 4.1)
(SELECT t1_c1, t1_c2 FROM t1 LIMIT 0, 10) UNION (SELECT t1_c1, t1_c2 FROM t1 LIMIT 0, 10) ORDER BY t1_id ASC
Inconvénient : l'UNION complète est un lente.
5. Les jointures
Introduisons maintenant la table t2. La table t2 contient des références sur la table t1 ; on souhaite réaliser une requête sur t2 pour récupérer la référence qui permettra de retrouver l'information souhaitée sur t1.
Il existe bien sûr la bonne et la mauvaise façon de procéder.
5.1. La boucle while : à éviter !!!
C'est probablement ce qu'on peut faire de pire en SQL.
SELECT t2_refid FROM t2 WHERE t2_id = 5 LIMIT 100
Ensuite, pour chaque valeur de t2_refid (jusqu'à 100 valeurs dans cet exemple), on réalise une nouvelle requête :
SELECT t1_c1, t1_c2 FROM t1 WHERE t1_id = <valeur t2_refid>
On réalise donc jusqu'à 101 (1 + 100) requêtes, ce qui est carrément catastrophique en termes de performances.
De façon générale, il ne faut pas placer des requêtes à l'intérieur de boucles.
5.2. La méthode LEFT JOIN
SELECT t1_c1, t1_c2 FROM t1 LEFT JOIN t2 ON t1_id = t2_refid WHERE t2_id = 5 LIMIT 100
On obtient exactement le même résultat que l'exemple précédent... avec une seule requête. Gain de performance considérable.
5.3. La méthode INNER JOIN
Cette méthode donne sensiblement le même résultat que la méthode précédente. Il semblerait que ce soit plus rapide sur des grosses tables, mais avec ma base MySQL, les temps de réponse sont identiques.
SELECT t1_c1, t1_c2 FROM t1, t2 WHERE t1_id = t2_refid AND t2_id = 5 LIMIT 100
6. Les fonctions MySQL
Voici quelques exemples d'utilisation des fonctions MySQL.
6.1. Count
SELECT count(t1_id) as total FROM t1
6.2. Rand
SELECT t1_c1, t1_c2 FROM t1 ORDER BY rand() LIMIT 10
7. Conclusion
Avec un peu de chance, ce petit guide vous aura servi pour optimiser vos requêtes SQL.
N'oubliez pas de consulter la documentation officielle pour la syntaxe exacte des requêtes et pour lire les commentaires.
Have fun.
Posté le 01/06/05 à 04:56