Optimiser les requêtes SQL : Pourquoi et comment ?

Le temps de chargement des pages web est devenu un facteur important pour le référencement d’un site. Je vois beaucoup de sites qui font confiance à leurs noms de domaines car ils ont su prendre le bon vent lors de la démocratisation du web, ou encore du fait qu’il s’agisse de grande marque. Mais pour de nombreux petits sites, la majeure partie de l’optimisation porte uniquement sur le CSS, l’HTML et le JavaScript. Il s’agit d’à peu près 50% de l’optimisation possible, mais il faut encore bosser derrière, car si on compare un site à une voiture et que Google est un circuit automobile, une voiture finie à 50% ne roule pas ou a très peu de chance de rouler. Un site c’est pareil. Les 50% autres se situent dans le backend, c’est-à-dire le système interne au site (il n’existe malheureusement pas de traduction littéral).

Dans cet article ci, je vais vous parler de l’optimisation SQL. Pourquoi le SQL ? Car généralement, les bases de données MySQL se situent en dehors du serveur apache où se situe votre fichier, de ce fait, aller rechercher les informations stockées dans la base de données sont plus loin et donc mettent plus de temps à arriver (ça se joue à quelques centièmes de secondes, mais c’est toujours ça d’économiser), de plus, chaque connexion entrante dans le serveur MySQL crée un processus en plus et s’il y a trop de connexion simultanée (heure de pointe entre autre), le serveur peut ralentir (même si sur des grosses infrastructures, tel que OVH, ils sont équipés pour cela peut vous être dans votre boulot ou même en cas de panne d’un serveur MySQL).

1er m moyen d’optimiser les requêtes SQL : ne prendre que ce que l’on veut.

On nous apprend au début (via l’ex site du zéro) qu’on peut sélectionner toute la table, ou toute une ligne, puis ensuite que l’on sélectionne ce que l’on souhaite exactement. Mais nous, en tant que jeune, on se simplifie la tâche et on sélectionne tout. Sauf que là, apparait un gros point noir, sur une table membre par exemple, avec un nom d’utilisateur de 50 caractères, un id de 11 caractères, un mot de passe de 255 caractères (cryptages), un niveau d’accréditation de 11 caractères, un email de 255, un hash (sécurité) de 255, une date de connexion de 25 (il s’agit ici du maximum bien sûr), on atteint les 862 caractères, sont les jointures entre table, sois prêt de 1Ko de donnée envoyé, reçu et traité pour la connexion d’un membre (avouez, vous avez tous fait un SELECT(‘*’) pour connecter un membre :p ) donc imaginez si votre site fonctionne et que n’avez rien optimisé, 100 membres se connecte, cela fait 100Ko, (j’arrondis un peu), si en plus il affiche tous les derniers articles de 5000 caractères, et que vous mettez à jour leurs profils sur la dernière connexion : 100 x 5000 + 100 x 25 (mise à jour du profil) + 100 x 862 = 588 700 Ko, soit 0.6Mo, c’est immense alors qu’avec l’optimisation que vous pouvez faire, vous pouvez utiliser que : 100 x 25 + 100 x (50+11) + 1 x 5000 = 13,6 Ko (sois 0,013Mo). Votre temps de chargement s’en fait ressentir et Google vous référencera mieux, n’excluons pas non plus les utilisateurs qui ont un faible débit, comme les « mobinautes ».

Pour optimiser une connexion, par exemple, faites juste ‘SELECT login, group’ et si vous voulez jouer la carte de la sécurité, faites un ‘SELECT id, hash’ même taille, mais plus de sécurité et de flexibilité.

2ème moyen d’optimiser les requêtes SQL: ne pas tronquer le texte

Je vois ça surtout dans les blogs ou dans des résumés, on veut prendre le début d’un article, comme les 100 premiers caractères. C’est bien, mais vous chargez 5Ko pour en récupérer que 0.1, surtout qu’un blog n’a pas qu’un article, donc on va dire 10 articles de 5000 caractères, ça fait 50Ko au lieu de 1Ko.

Pour optimiser les blogs, au lieu de récupérer le texte entier puis le tronquer, lors de l’enregistrement tronquer le et mettez le bout de texte dans une colonne spéciale.

3ème moyen d’optimiser les requêtes SQL : regrouper le plus possible tout ce dont vous avez besoin

Au lieu de faire 10 appels pour récupérer 10 données, regrouper tout ce que vous pouvez dans une seul requête, cela va limiter le nombre de connexion entre vous et le serveur MySQL et donc le nombre de processus, vous contribuerez alors au partage des ressources, et puis cela va aussi améliorer le temps de réponse, car moins il y a d’appel, moins il y a de temps d’envoi, temps de réception, traitement des serveurs distants, c’est tout bénef’ pour vous.

Pour ça, je vous invite à utiliser les jointures entre table.

4ème moyen d’optimiser les requêtes SQL : le cache

Je ne vais pas parler ici comment mettre un site web en cache, mais plutôt donner un indice, une piste à approfondir (un tuto créer un système de cache sera pour plus tard). Ne mettez jamais une page complète en cache, car en cas de modification du CSS, le code peut créer des problèmes d’affichage. De plus, si vous faites un système de cache pour une page complète, autant faire un site uniquement en HTML, sans PHP. Ici je parle de mettre en cache les données envoyées par votre serveur SQL. Pour un blog, cela ne sert à rien qu’à chaque article vu par divers internautes, on appelle la base de données, cela fait des appels pour rien, surtout si vous écrivez peu d’article. 100 personnes visitant le même article n’ont pas besoin d’appeler 100 fois la base de données, pareil pour un listing dans une catégorie. L’autre avantage de la mise en cache est : lorsque le serveur MySQL distant est en panne, vos articles peuvent toujours s’afficher.

Mettez en cache, fichier ou memcache, vos données renvoyées par le serveur MySQL. Cependant, faites attention, cette technique est surtout à utiliser pour des blogs, des appels à d’autres serveurs (tweets), livre d’or, affichage de profil… Les forums, les messages privées, les connexions n’ont pas besoin de cache, car ils s’actualisent souvent, l’expérience utilisateur s’en ferai ressentir.

Une fois que tout ceci est en place, vous devriez voir une augmentation assez nette. Pour info, la page d’accueil de ce blog charge en 2,5 secondes (maximum) sans cache (communication via tweeter, la base de donnée et le Framework), avec le cache je passe à 0,2 secondes (il m’arrive d’atteindre le 0,5 malheureusement).