Voilà un élément qui peut prendre beaucoup temps si on y prend pas garde : les requêtes SQL.
En effet, il n’est pas rare de voir une page bien optimisée s’afficher en plus d’une seconde et de ne rien pouvoir faire de plus côté serveur ou client (regroupement et minification JS / CSS, sprites, CDN, …). Mais voilà, lorsque la page prend déjà plus d’une seconde pour être calculée à cause d’une requête SQL trop lourde, il faut résoudre ça au plus vite !
Je ne vais pas m’attarder sur les clés ou sur les requêtes LIKE contenant des wildcards (caractères de remplacement). Je ne tiendrais pas compte, non plus, de la sécurité. Les morceaux de codes de cet article sont juste des astuces à mettre en place. À vous de les adapter à vos besoins.
Trouvez les requêtes lentes
Si vous avez un serveur dédié, vous pouvez commencer par mettre en log les requêtes lentes à l’aide de la directive « log_slow_queries » :
log_slow_queries = /dossier-log/mysql-slow-query.log long_query_time = 2 log-queries-not-using-indexes
Vous aurez un fichier avec les requêtes prenant plus de 2 secondes et celles qui n’utilisent pas d’index.
Si vous n’avez pas accès à cette possibilité, il va falloir regarder vos requêtes une par une… Bon, vous pouvez quand même faire un premier tri entre les requêtes compliquées et les simples. Quoique… Comme vous allez le voir, les requêtes les plus simples sont parfois celles qui prennent le plus de temps.
Une fois que vous avez les requêtes lentes, utilisez EXPLAIN pour connaître les clés utilisées pour le résultat de la requête. Par exemple :
EXPLAIN SELECT * FROM v2_villes WHERE Ville LIKE 'a%'
Vous indiquez cela dans votre PhpMyAdmin préféré et vous obtenez un tableau. Si cette requête n’utilise pas de clé, il faut y remédier (par exemple).
Nombre de lignes dans une table
On a souvent besoin de cette requête : pour afficher une liste page par page ou pour afficher des fiches aléatoires parmi la totalité de la base.
Mais cette requête peut s’avérer chronophage si on y prend pas garde :
SELECT * FROM ma_table
Avec cette requête, vous allez obtenir le nombre de lignes dans la table ‘ma_table’. Mais cette requête prend beaucoup de temps si vous avez un nombre important de ligne. À titre d’exemple, pour une base de 35000 fiches (des codes postaux), cette requête prend environ 1 seconde.
2 solutions à ce problème :
- Mettez en cache le résultat. Il ne change que très rarement. Vous pouvez éventuellement mettre à jour le cache lorsqu’une fiche est ajoutée ou supprimée.
- Utilisez la fonction COUNT
SELECT COUNT(*) AS NbFiches FROM ma_table
Cette fonction ne va pas lire le fichier de données, mais le fichier descripteur et sera nettement plus rapide : 0.007 sec avec le même environnement que la précédente requête.
Attention toutefois, cela est valable pour une table MyISAM, mais pas InnoDB.
Et rien ne vous interdit de mettre ces 2 solutions en action.
Limitez vos requêtes
La plupart du temps, vous n’aurez pas besoin de récupérer l’ensemble des informations d’une requête. En effet, si vous faites une TOP 10, vous n’aurez jamais besoin de la 11ème fiche. Du coup, il est bon de limiter la requête :
SELECT * FROM ma_table ORDER BY Score DESC LIMIT 0, 10
LIMIT permet d’indiquer l’offset (le premier enregistrement) et le nombre d’enregistrements que vous souhaitez. Vous gagnerez beaucoup en temps d’exécution surtout si votre table dépasse plusieurs milliers d’enregistrements.
Une fiche aléatoire
Pour rechercher une fiche aléatoire, vous pouvez utiliser une fonction bien pratique : ORDER BY RAND()
Malheureusement, si votre table contient beaucoup de lignes (genre plus de 2000), cette fonction fera souffrir votre serveur.
Une solution consiste à utiliser des limites aléatoires :
$n = mt_rand(0, nbLignes-100) SELECT * FROM ma_table ORDER BY RAND() LIMIT $n,100
On demande donc une recherche parmi une centaine de fiches et non plus la totalité. Le résultat reste aléatoire puisque la recherche démarre à partir d’une fiche aléatoire. Par contre, la vitesse d’exécution de cette requête est nettement plus rapide que sans la limite.
Dans cet exemple, vous aurez 100 fiches aléatoires. Si vous n’avez besoin que d’une fiche, remplacez 100 par 1.
SELECT * vs SELECT col1, col2
Vous vous êtes très certainement posé cette question et par défaut, vous avez pris un SELECT * parce que ça va plus vite à écrire. Ou alors vous avez pris le temps de sélectionner les colonnes que vous vouliez parce que vous pensez que la requête est plus rapide ?
Finalement, qu’en est-il réellement ?
Envoyer un SELECT * n’est pas plus lent que de sélectionner des colonnes. Par contre, le volume de données qui sera renvoyé ne sera pas le même. Du coup, si votre serveur MySQL se trouve sur un autre serveur que votre site, vous allez prendre un peu plus de bande passante et donc du temps.
De la même façon, même si MySQL se trouve sur votre serveur, en fonction de la quantité de champs ramenés par la requête, il est fort possible que la mémoire de votre serveur soit très sollicitée.
Vous voilà prévenu, à vous de voir en fonction de votre projet.
Conclusion
Il existe bon nombre d’astuces pour faire en sorte d’accélérer vos requêtes SQL. Il est impossible de les répertorier toutes ici. Chaque cas est différent et je n’ai indiqué ici que des solutions à des problèmes que l’on rencontre régulièrement.
Outre les solutions purement liées à l’écriture des requêtes, il y a les optimisations liées à l’OS (partitions, Windows / Linux, …), liées au réseau (distance, multi_query, procédures stockées, …) et, évidement, à l’optimisation même du serveur MySQL en le paramétrant correctement en fonction de vos besoins et non pas de besoins génériques.
Et vous, vous avez des astuces ?!
L’importance du moteur (MyIsam ou InnoDB) est énorme sur un site à fort trafic.
InnoDB :
+ : Possibilité d’utiliser les transactions (mais risque de Deadlock), accès concurrents sur une même table possible (par exemple, si un de vos UPDATE on INSERT dure 1 seconde, InnoDB permet de quand même faire un SELECT durant cette seconde. Pas MyIsam), clef étrangères pour update et delete en cascade). Plus rapide que MyIsam pour UPDATE, INSERT et DELETE
– : Demande plus d’espace de stockage, plus lent pour les SELECT que MyIsam (grâce à un une gestion des index très performante)
Si vous avez la chance d’avoir un site, un forum ou un blog très fréquenté mais que vous souffrez de performances médiocres, une solution très simple à mettre en place et qui peut faire des miracles : étudiez l’utilité de vos tables, passez en InnoDB vos tables très fortement UPDATé et INSERTé et en MyIsam vos tables qui n’ont presque que des SELECTs
Exemple pour un forum =>
– Table des posts en InnoDB
– Table des des groupes, des utilisateurs et des correspondances groupes/utilisateurs en MyIsam
Tout à fait ! 🙂
Juste pour nuancer, le LIMIT a ses… limites 😛 Si MySql n’a pas d’autres moyens que de parcourir tout le jeu de résultat initial voir la table ça ne changera rien.
Je précise que LIMIT ne soit pas pris comme un messie, ça dépend du cas et ça prive surtout pas d’avoir une utilisation correcte des index 😉
Encore une fois, je suis entièrement d’accord. Il est évident qu’il faut adapter à la situation les morceaux de codes et les solutions que je donne ici.
C’est un métier que de développer des applications. C’est en faisant pleins de tests que l’on se forge ses connaissances. Alors au boulot ! 🙂
Je reste étonné par le order by, et par le select * équivalent à select col,col
@Hum : « Je reste étonné par le select * équivalent à select col,col »
En première approximation, l’exécution de la requête sera au pire la même entre les deux.
Comme il le dit dans l’article, l’impact sera lié à la quantité de données retournée (volume transféré, désérialisation des données côté client (pour nourrir l’orm…), …).
Maintenant, il y a des cas où select col1,col2 sera plus rapide : par exemple si col1,col2 sont dans l’index utilisé pour la requête. Le moteur n’a qu’à lire l’index (déjà en mémoire si bonne conf du serveur) pour traiter la requête ET retourner les résultats (sans aller lire les données dans la table).
Typiquement : SELECT col1,col2 FROM table WHERE col1 = « truc » ORDER BY col1,col2 (avec un index col1,col2)
> Mettez en cache le résultat
Pourrais-tu donner un peu de détail sur cette méthode ?
J’ai l’habitude de stocker certaines valeurs d’agréments dans les variables de sessions … mais je pense qu’il s’agit d’autre chose.
kéké, rarement présent sur le net … qui attend son petit gars dans 10 jours !
Je parlais d’un système de cache tout ce qu’il y a de plus banal.
Par exemple, au lieu de refaire la requête systématiquement, si les données n’ont pas besoin d’un résultat en temps réel, il est préférable d’enregistrer la page dans un fichier et de vérifier que ce fichier n’est pas trop ancien. Si il date de moins d’une heure (par exemple), alors on affiche le contenu du fichier. Sinon, on refait la requête et on reconstruit ce fichier.
C’est utile par exemple pour afficher des classements qui ne changent pas à chaque rechargement de page.
Ha … zut, je pensais à un truc vachement transcendant … Une copine qui fait de l’asp.net à fond, utilise des méthodes de caching de la mort qui tue … et ça ne se base pas (je crois) sur du fichier plat. N’ayant jamais trop eu le temps de discuter avec elle … je ne sais toujours pas trop de quoi il s’agit.
Ca va toi sinon ?
Ca fait depuis mars que je n’ai pas pris/eu le temps de m’intéresser au web … Préférant parfois jouer sur le net pour me détendre plutôt que m’attaquer à quelques choses sans trop de visibilité…
kéké
@Maxime :
Effectivement, si on part de l’idée que les col ne sont pas dans les index, et qu’on travaille sur des tables avec peu de col et peu de donnée, on peut considérer que * sera aussi rapide que Col, Col.
L’autre problème cependant d’utiliser un * est justement le fait de renvoyer des champs potentiellement inutile dans le sens où une base de donnée peut évoluer et si un jour des champs sont rajoutés à cette table, ils seront renvoyés dans ces requêtes pour rien. Cela nuit à l’évolutivité de l’application au final.