MySQL

MySQL

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 ?!