juin
2013
Un article de Walik
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 certains nombre d’aspects.
Le modèle de donnée
D’abord il faut que le modèle de votre base de données soit basé sur des méthodologies qui ont fait leur preuves comme Merise.
L’application des principes d’une telle méthodologie apportera un modèle saint 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 d’argent.
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é augmentent la vitesse de recherche. Ainsi nous voyons ici trois aspects importants traités: La sécurité, le volume et la vitesse.
La sécurité
En terme de sécurité, il faut protéger votre application contre les injections sql.
Vous devez ensuite indiquer précisément les types 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 ».
Tous laxisme à ce niveau entraîne des failles difficile à 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’a l’exécution.
Un de mes clients dont le site internet proposait un système de crédits à ces utilisateurs a pâtis 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 a 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 temporaire il convient d’automatiser leur suppression.
Utilisez également du round-robin sur des types de données qui s’y prête comme les données de monitoring.
La vitesse
Nous allons traiter ici de ce sujet probablement le plus important et auquel on pense immédiatement.
Tous d’abord avoir un modèle de donnée cohérent accéléra vos opération 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ête:
N’utilisez pas « SELECT * » mais nommé spécifiquement les champs que vous recherchez
Effectuer autant que possible des recherche 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 requetes avec des outils comme « show profile » ou « show status »
Le vérrouillage
Lorsque on accède a une ressouce mysql , en lecture ou en écriture, celle ci est vérrouillé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 vérrouillage plusieurs solution peuvent être envisagées comme:
- Changer de moteur: Le moteur InnoDb effectue sont vérrouillage à un niveau lignes sur une table tandis que Myisam vérouille 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é 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 soit pas sollicité sur les données qui ne change que ponctuellement évitera a ces ressources d’être inaccessible lors des requêtes.
L’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: celà consiste à éclater une table une table en plusieurs table à la manière des table assemblées merge mysql.
- Mettre des indexes là ou c’est nécessaire comme indiqué plus haut
Conclusion
Voilà un assez bref petit tours d’horizon bien que non exhaustif qui j’espere apportera a certains une vision synthétique, une sensibilisation aux optimisation Mysql ou tout simplement des débuts de pistes à explorer
M.C