La première étape dans une optimisation logicielle est certainement l’optimisation de votre base de données SQL.
Que vous vouliez optimiser votre application pour résister à la charge ou aux failles de sécurité, que ce soit un site internet ou une application de bureau, le principe reste le même.
Dans ce domaine il faut s’assurer de la qualité d’un certain nombre d’aspects.
Modèle de données
D’abord il faut que le modèle de votre base de données soit basé sur des méthodologies qui ont fait leurs preuves comme Merise.
L’application des principes d’une telle méthodologie apportera un modèle sain et rapide, elle évitera des effets de bords imprévisibles qui ne manqueront pas de planter votre application et/ou de corrompre vos données à moyen-long terme engendrant des coûts en maintenance, en termes d’effort, et donc de budget.
Par exemple l’ajout de contraintes empêche la corruption des données. Des relations 1.1, 1.n ou n.n pertinentes diminuent le volume de données, des indexes bien placés augmentent la vitesse de recherche. Ainsi sont traités ici trois concepts important s: La sécurité, le volume et la vitesse que nous traiteront plus en détail dans ce qui suit.
Sécurité
En terme de sécurité, il faut protéger votre application contre les injections sql.
Vous devez ensuite indiquer précisément le typage des champs de votre table, un champ qui ne doit jamais être NULL doit être flagué « NOT NULL », un champ qui n’est jamais sensé être négatif devrait être « unsigned ».
Tout laxisme à ce niveau entraîne des failles difficiles à détecter, on peut y avoir une analogie avec le typage dans les langages de programmation, il vaut mieux qu’un programme plante à la compilation qu’à l’exécution.
Un de mes clients dont le site internet proposait un système de crédits à ces utilisateurs a pâti d’un manquement à ce niveau. Un utilisateur malin arrivait à passer des valeurs négatives lors des opérations de dé-crédit et par conséquent son solde grossissait perpétuellement au lieu de diminuer, il réussi ainsi à avoir des crédits illimités.
Volume de données
Pensez à automatiser l’archivage des données qui sont importantes mais inutiles au fonctionnement de votre système, par exemple les transactions ou les facturations qui ont plus de 6 mois.
Quant aux données temporaires il convient d’automatiser leur suppression.
Utilisez également du round-robin sur des types de données qui s’y prêtent comme les données de monitoring.
Vitesse
J’ai volontairement abordé ce sujet en dernier, car bien qu’important, c’est celui auquel on pense immédiatement.
Tous d’abord avoir un modèle de données cohérent accéléra vos opérations d’archivage réduisant plus vite votre volume de données.
Ensuite il y a quelque règles de base à respecter si vous voulez accélérer vos requêtes :
- N’utilisez pas « SELECT * » mais nommez spécifiquement les champs que vous recherchez
- Effectuer autant que possible des recherches sur des champs de même type, text-text, int-int etc…
- Mettez des indexes sur les champs les plus sollicités en recherche
Ensuite il y a deux deux raisons qui font que vos requêtes sont lentes : Le verrouillage et l’exécution
Pour le déterminer vous pouvez analyser vos requêtes avec des commandes comme « show profile » ou « show status »
Verrouillage
Lorsque on accède à une ressource mysql, en lecture ou en écriture, celle ci est verrouillée le temps de l’opération pour garantir l’intégrité des informations retournées.
Si votre table sql est lente à cause des mécanismes de verrouillage plusieurs solutions peuvent être envisagées comme :
Changer de moteur : Le moteur InnoDb effectue sont verrouillage à un niveau ligne sur une table tandis que Myisam verrouille toute la table.
Sharding vertical : Plus la table est grosse plus il est difficile d’y accéder, splitter vos données verticalement en externalisant vos champs les plus sollicités dans de multiples tables externes constitue une solution de choix dans ce cas.
Cache : Utiliser une solution de cache afin que les bases de données ne soient pas sollicités sur les données qui ne changent que ponctuellement évitera à ces ressources d’être inaccessibles lors des requêtes.
Exécution
Si vos requête sont lentes à l’exécution vous pouvez :
- Supprimer les jointures et les remplacer par des sous requêtes
- Restructurer les champs de vos tables pour économiser l’espace, la ou un tiny int est suffisant inutile de mettre un int
- Utiliser du Sharding horizontale : cela consiste à éclater une en plusieurs table à la manière des table assemblées merge mysql.
- Mettre des indexes là où c’est nécessaire comme indiqué plus haut
Conclusion
Voilà un assez bref petit tour d’horizon bien que non exhaustif qui j’espère apportera à certains une vision synthétique, une sensibilisation aux optimisation Mysql ou tout simplement des débuts de pistes à explorer.
M.C
