Depuis la version 4 de MySQL, une gestion de cache des requêtes est disponible.
Le grand principe du cache MySQL est de stocker dans une zone mémoire réservée le résultat d'une requête de type SELECT. Ainsi lors du prochain appel de cette requête, le moteur SQL n'aura pas à exécuter le traitement. Il retrouvera le résultat directement en mémoire. La recherche d'un résultat de requête en mémoire prend en général moins d'une milliseconde.
Le moteur SQL détecte tout changement de contenu dans une table et supprime automatiquement du cache tous les résultats de requêtes sur une table qui a été modifiée par une instruction UPDATE ou INSERT.
Il n'y a donc aucun risque de retourner un résultat périmé. Le moteur gère tout seul le cache et la purge en libérant le développeur de ces tâches délicates.

Le principe du cache MySQL est donc très intéressant pour les requêtes qui sont appelées fréquemment et dont le résultat ne varie pas souvent. C'est le cas pour de nombreuses requêtes sur un site web : actus, menus, articles.
A l'opposé, une requête sur une table fréquemment mise à jour (session, log, compteurs) ne profitera pas du cache car le moteur devra gérer la création du cache et sa purge à chaque mise à jour de la table.

Paramétrer le cache
MySQL permet de paramétrer les options du cache.

QUERY_CACHE_LIMIT	1048576
QUERY_CACHE_SIZE	20971520
QUERY_CACHE_TYPE	DEMAND

Le paramètre QUERY_CACHE_LIMIT empêche l'enregistrement en cache des résultats occupant trop de place. Par défaut 1 Mo.

Le paramètre QUERY_CACHE_SIZE définit la taille de la zone mémoire allouée au cache des résultats de requêtes. 20 Mo dans ce cas.
Si le cache n'est pas suffisant pour stocker tous les résultats, le moteur supprime les requêtes les moins courantes pour libérer de l'espace mémoire.
La taille idéale du cache est impossible à prédire. Il faut l'affiner progressivement après quelques jours d'utilisation.

Le paramètre QUERY_CACHE_TYPE permet de choisir le mode de gestion du cache.
ON Toutes les requêtes SELECT sont mises en cache sauf celles qui ont le mot clé SQL_NO_CACHE
OFF Le cache est désactivé
DEMAND Seules les requêtes SELECT avec le mot clé SQL_CACHE sont mises en cache.

Pour modifier ces paramètres, éditez le fichier my.ini (window) ou my.cnf (linux) et relancez le serveur MySQL. Attention, l'arrêt du serveur MySQL entraîne la destruction du cache.

Le choix du type de cache est important. La facilité ferait choisir l'activation du cache, en laissant la gestion au moteur SQL. Je recommande plutôt le choix DEMAND qui implique une analyse des requêtes.
En choisissant de forcer la mise en cache pour les requêtes judicieusement sélectionnées, on aide grandement le moteur.
Pour placer une requête en cache, utilisez le mot clé SQL_CACHE :
SELECT SQL_CACHE * FROM matable WHERE cle="valeur"
Choisissez les requêtes sur des tables qui ne varient pas trop souvent.

Pensez à bien remplacer les * par le nom des colonnes vraiment utiles pour limiter l'espace mémoire nécessaire au stockage du résultat.

Attention, les mots clés SQL_CACHE et SQL_NO_CACHE ne sont pas reconnus par les versions inférieures de MySQL et génèrent des erreurs avec la version 3.

Surveiller le résultat
La commande SHOW STATUS affiche l'état du serveur MySQL.
Les paramètres qui nous intéresse pour la gestion du cache sont préfixés par Qcache.

Qcache queries in cache	4813	Nombre de requêtes enregistrées en cache
Qcache inserts	21306	Nombre de requêtes insérées dans le cache
Qcache hits	285823	Nombre de résultats de requêtes retrouvés dans le cache
Qcache lowmem prunes	0	Nombre de requêtes purgées du cache par manque de mémoire
Qcache not cached	347812	Nombre de requêtes non enregistrées en cache
Qcache free memory	14803992	Taille mémoire restante
Qcache free blocks	1285	Nombre de blocs mémoire libres
Qcache total blocks	10932	Nombre totale de bloc mémoire

Les paramètres à surveiller sont en particulier Qcache hits qui donne le nombre de fois que le cache est lu. L'idéal est d'avoir le maximum de lectures (Qcache hits) et le minimum d'écritures (Qcache inserts). Dans cet exemple, le rapport est de 13 lectures en cache pour une écriture. C'est plutôt bon comme ratio...

Vérifiez aussi la valeur de Qcache lowmem prunes. Si il est trop important, cela signifie que la taille du cache est trop faible.

Qcache not cached indique le nombre de requêtes qui n'ont pas été stockées en cache. Il faut dans la mesure du possible limiter ce nombre, tout en gardant un bon ratio Lectures / Ecritures.


Nous avons fini avec l'optimisation côté MySQL. Si vous avez d'autres idées, n'hésitez pas à m'en faire part pour que je complète cet article.


Voyons maintenant comment optimiser les traitements en PHP, toujours avec l'utilisation de cache