MySQL : Top 20 des meilleures pratiques !

A l’heure actuelle, les opérations sur les bases de données ont tendance à être le principal problème des applications web. Il n’est pas seulement du ressort des DBA (DataBase Administrator) que d’assurer le bon fonctionnement des bases de données; Les programmeurs ont aussi leur part de responsabilité ! Structurer correctement les tables, écrire des requêtes optimisées, écrire du code propre, font parti du travail des programmeurs ! Voici quelques techniques d’optimisations MySQL pour les programmeurs !

1. Optimisez vos requêtes pour le cache

Il faut savoir que sur énormément de serveur MySQL la mise en cache de requête est activé ! Très bien gérer par le moteur de base de données, c’est l’une des méthodes les plus efficaces pour améliorer les performances. Lorsqu’une même requête est exécutée plusieurs fois, le résultat est récupéré dans le cache, ce qui est bien plus rapide !

// La mise en cache ne fonctionne pas !
$req = mysql_query("SELECT username FROM user WHERE insc_date >= CURDATE()");
 
// Le cache fonctionne !
$today = date("Y-m-d");
$req = mysql_query("SELECT username FROM user WHERE insc_date >= '$today'");

A la première ligne, c’est l’utilisation de la fonction CURDATE() qui empêche la mise en cache de cette requête. Ceci s’applique à toutes les fonctions non-déterministes comme NOW() ou RAND(). En effet le résultat de ces fonctions pouvant changer, MySQL décide donc de ne pas mettre la requête en cache. Dans ce cas, il aurait suffit d’ajouter une simple ligne de code en PHP pour éviter que cela ce passe !

2. Utilisez EXPLAIN sur vos SELECT

L’utilisation du mot clé EXPLAIN peut vous renseigner sur ce que fait MySQL pour exécuter votre requête. Cela peut vous aider à localiser et à identifier des problèmes concernant vos requêtes ou vos tables.

Prenez une requête complexe (de préférence avec une jointure), et ajouter devant le mot clé « EXPLAIN ». Vous pouvez utiliser phpMyAdmin pour cela. Il vous affichera les résultats dans un tableau détaillé, vous indiquant la clé utilisée, le nombre de lignes traitées, et d’autres données qui vous aideront à optimiser vos requêtes.

3. LIMIT 1 pour obtenir un résultat à ligne unique

Parfois, lorsque vous interrogez votre base de données, vous savez déjà que le résultat de votre requête ne comportera qu’une seule ligne. Vous cherchez peut être un enregistrement unique, ou alors vous vérifiez l’existence d’une valeur qui satisfera votre clause WHERE.

Dans de tels cas, ajouter LIMIT 1 à votre requête peut réellement augmenter les performances. De cette façon le moteur de base de données peut arrêter de scanner les enregistrements dès l’instant où il en aura trouver un qui satisfera votre requête au lieu de traverser toute la table !

4. Indexez les champs recherchés

Les indexes ne sont pas réservées aux clés primaires et clés uniques. Si dans votre table il y a des colonnes qui vous servent dans vos recherches (ex : moteur de recherche, filtre, tri..), vous devriez quasiment tout le temps les indexer.

// Executé en 0.25 sec
$req = mysql_query("SELECT count(*) FROM users WHERE last_name LIKE 'a%'");
 
// On indexe la colonne last_name
$req = mysql_query("ALTER TABLE users ADD INDEX ('last_name')");
 
// Executé en 0.06 sec
$req = mysql_query("SELECT count(*) FROM users WHERE last_name LIKE 'a%'");

Comme vous pouvez le voir cette règle s’applique aussi sur une recherche de chaîne partielle. Lors d’une recherche  depuis le début d’une chaîne, MySQL est capable d’utiliser l’index sur cette colonne.

Néanmoins, cette règle n’est pas valable pour tous les types de recherche. Par exemple si vous cherchez un mot entier dans une chaîne, vous ne trouverez pas d’avantage particulier à utiliser une indexation dite « normal ». Utilisez plutôt la recherche en texte intégral, ou construisez votre propre solution d’indexation.

5. Indexez et utilisez les mêmes types pour vos jointures

Si votre application utilise de nombreuses requêtes avec jointures , vous devez vous assurer que les colonnes que vous tentez de joindre sont indexées dans les tables. Cela va influer sur la façon dont MySQL  optimisera l’opération de jointure interne.

Bien entendu les colonnes jointes doivent être du même type. Si vous tentez de joindre une colonne de type DECIMAL avec une colonne de type INT, MySQL ne sera même pas en mesure d’utiliser au moins un des deux indexes. Pour les colonnes de type chaînes, même les codages des caractères doivent être identiques.

6. N’utilisez pas ORDER BY RAND();

C’est l’une de ces astuces qui aux premiers abords paraissent plutôt cool, du coup les programmeurs tombent assez facilement dans ce piège. Vous ne réalisez pas quel d’atrocité vous mettez en place lorsque vous commencez à utiliser ce genre d’astuces dans vos requêtes !

Si vous avez réellement besoin d’ordonner vos résultats aléatoirement, il existe des moyens beaucoup plus efficaces pour le faire. Certes ces moyens prennent quelques lignes de code supplémentaires, mais elles vous permettent d’éviter d’utiliser ce genre d’astuces horribles qui fonctionnent de moins en moins bien à mesure que votre volume de donnée augmente.

Le problème avec cette astuce est que MySQL devra exécuter la fonction RAND() (qui utilise de la puissance de calcul) pour chaque ligne de votre table, avant de les trier, et de pouvoir vous retourner votre résultat.

// Ce qu'il ne faut pas faire :
$req = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");  
 
// Ce qu'il faut faire :
$req = mysql_query("SELECT count(*) FROM user");
$res = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);  
 
$req = mysql_query("SELECT username FROM user LIMIT $rand, 1");

Le but de cette manipulation est de choisir un nombre aléatoirement entre 0 et le nombre de résultat de votre requête, et d’utiliser ensuite ce nombre comme offset dans votre clause LIMIT.

7. Evitez SELECT *

Plus les données seront lues à partir des tables, plus les requêtes seront longues. Cela augmente le temps nécessaire aux opérations sur les disques. De plus lorsque le serveur web est distinct du serveur de base de données, vous enverrez plus de données que nécessaire sur le réseau, vous augmenterez donc les délais.

C’est une bonne habitude à prendre que de spécifier les colonnes dont vous avez besoin lorsque vous écrivez votre SELECT.

8. Utilisez toujours une colonne id

Dans toutes les tables il devra y avoir une colonne id, qui sera la clé primaire, en auto_increment, le plus souvent un INT, et de préférence UNSIGNED puisqu’un id ne peut être négatif.

Même si vous avez une table utilisateur qui a un champ « nom » unique, n’en faite pas votre clé primaire ! Les champs VARCHAR comme clé primaire sont beaucoup plus lents. De plus, vous aurez une meilleure structure dans votre code en faisant référence à un id plutôt qu’a un nom..

Il y a aussi les opérations faites par MySQL en interne, qui utilisent la clé primaire de vos tables.

Bien évidemment il y a des exceptions à cette règle. Par exemple les tables d’associations ne nécessitent pas un id, puisque normalement elle servent à relier deux tables en utilisant les ids de ces tables.

9. Utilisez ENUM au lieu de simples VARCHAR

Les colonnes de type ENUM sont très rapides, et très compactes puisqu’en interne ils sont stockés en tant que TINYINT.

Si vous avez un champ qui ne contient que quelques valeurs différentes, utilisez ENUM au lieu de VARCHAR. Par exemple si vous avez une colonne « etat » qui contient des valeurs comme : « actif », « inactif », « en cours », etc.., il sera plus efficace d’utiliser un ENUM.

10. Obtenez des suggestions avec PROCEDURE ANALYSE();

PROCEDURE ANALYSE() permet à MySQL d’analyser la structures de vos tables, le type des colonnes, ainsi que les données présentes à ce moment là. Suite à cette analyse MySQL vous retournera quelques suggestions afin d’améliorer votre base de données. Cela n’est utile que si vous avez des données réelles dans votre table.

Par exemple, si vous avez créé un champ de type INT pour votre clé primaire, et que votre table ne contient que très peu de ligne, MySQL vous conseillera de passer ce INT en MEDIUM INT. Ou si vous utilisez un champ VARCHAR pour une colonne qui a toujours les mêmes résultats (ex : un etat), MySQL vous conseillera l’utilisation d’une ENUM.

Vous pouvez utiliser cette procédure via phpMyAdmin en cliquant sur le lien « Propose table structure ».

Gardez bien à l’esprit que ce ne sont que des suggestions, et que si par exemple votre table est amené à prendre du volume, toutes les suggestions ne sont peut être pas bonnes à prendre.

11. Utilisez NOT NULL si possible

Sauf si vous avez une raison très particulière d’utiliser une valeur NULL, vous devriez toujours positionner vos colonnes comme NOT NULL.

Tout d’abord, demandez-vous s’il ya une différence entre avoir une valeur de chaîne vide par rapport à une valeur NULL (pour les champs INT : 0 vs NULL). S’il n’y a aucune raison d’avoir les deux, vous n’avez pas besoin d’un champ de valeur NULL. Saviez-vous que Oracle considère NULL et chaîne vide de la même manière ?

La documentation MySQL dit à ce sujet :

« NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. »

12. Utilisez les commandes préparées

Il y a de multiples avantages à utiliser les commandes préparées, mais les deux principales restent : la performance, et la sécurité !

Par défaut, les commandes préparées vont filtrer les variables qui leur sont envoyées, ce qui est parfait pour protéger votre application des attaques par injections SQL. Vous pouvez bien sûr filtrer vos variables manuellement, mais ces méthodes sont sujets à l’erreur humaine, et l’oubli du programmeur !

Du coté des performances, les avantages sont nombreux. Notons tout de même que ces avantages sont d’autant plus visible lorsqu’il s’agit d’une requête qui est utilisé plusieurs fois dans votre application.

Les dernières version de MySQL transmettent les requêtes préparées sous une forme binaire native, qui sont bien plus efficaces et peuvent également contribuer à réduire les délais réseau.

Il fut un temps où les programmeurs éviter les requêtes préparées exprès, car elles empêchaient la mise en cache. Ce temps est passé ! Depuis MySQL 5.1 la mise en cache des requêtes préparées est supportée.

Pour utiliser les commandes préparées en PHP il vous faudra l’extension mysqli ou alors utiliser une couche d’abstraction de base de données comme PDO.

13. Utilisez les requêtes non-bufferisées

Normalement lorsque vous effectuez une requête à partir d’un script PHP, il va attendre que l’exécution de cette requête soit terminée pour pouvoir continuer. Vous pouvez changer cela en utilisant les « Unbuffered queries ».

La fonction correspondante est la suivante : mysql_unbufferd_query();

Cependant ce type de requête à quand même des limites. Après avoir utilisé cette fonction, vous devez lire toutes les lignes ou alors utiliser la fonction mysql_free_result(); avant de pouvoir exécuter une nouvelle requête. Par ailleurs, si vous utiliser une requête non bufferisée, vous ne pouvez utiliser les fonctions mysql_num_rows(); et mysql_data_seek();

14. Stockez les IPs en UNSIGNED INT

De nombreux programmeurs stockent des IPs en base en utilisant des VARCHAR(15) sans savoir qu’ils pourraient le faire avec un INT. Avec un INT vous descendez à seulement 4 bytes d’espace et bénéficiez d’une taille fixe.

Dans votre requête vous pouvez utiliser la fonction INET_ATON() pour convertir une IP en INT, et la fonction INET_NTOA() pour faire l’opération inverse.
Il existe aussi des fonctions similaires en PHP, tels que : ip2long() et long2ip().

15. Les tables de tailles fixes sont plus rapides

Lorsque toutes les colonnes d’une tables sont de tailles fixes, c’est la table elle même qui est considéré comme statique.
Parmi les types de taille non fixe vous trouverez : VARCHAR, BLOB, TEXT. Si vous incluez ne serait-ce qu’un seul de ces types, votre table ne sera plus considérée comme statique, et sera donc traitée différemment par MySQL.

Les tables de tailles fixes sont également plus faciles à mettre en cache, et plus faciles à récupérer en cas de crash. La contrepartie est qu’elles peuvent parfois prendre plus de place que nécessaire. Il faut donc parfois savoir s’arrêter et ne pas « trop » vouloir optimiser au risque de voir se produire l’effet inverse.

16. Le partitionnement vertical

Le partitionnement vertical c’est l’acte de diviser la structure de votre table de manière vertical, toujours pour des raisons d’optimisation.

Exemple : Vous avez une table d’utilisateur qui contient des adresses de domicile qui ne sont pas lues souvent. Vous pouvez donc choisir de scinder votre table et de stocker les informations liées à l’adresse de manière séparée. De cette manière votre principale « table utilisateur » diminuera en taille, ce qui augmentera les performances.

Vous devez par contre faire attention à ce que vous n’ayez pas constamment besoin de faire une jointure entre vos tables scindés, ce qui serait une fois de plus de la contre optimisation.

17. Scindez les grosses requêtes DELETE ou INSERT

Si vous avez besoin d’effectuer une grosse requête DELETE ou INSERT sur un site web en production, vous devez faire attention à ne pas perturber votre trafic web. Lorsque une grosse requête comme celle-ci est exécuté elle peut parfois mener à un LOCK de vos tables, ce qui pour un site en production n’est pas possible.

Si vous avez une sorte de script d’entretien qui a besoin de supprimer un grand nombre de lignes, il suffit d’utiliser la clause LIMIT pour supprimer par lot plus petit et éviter une congestion.

Exemple :

while (1) {
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
    if (mysql_affected_rows() == 0) {
        // Tout a été effacé
        break;
    }
    // Il est possible de faire une petite pause
    usleep(50000);
}

18. Les plus petites colonnes sont les plus rapides

Stocker les choses de la façon la plus compacte possible est généralement utile en termes de performances. Cela permet en effet de réduire le volume de transfert sur le disque.

Exemple : Si une table n’a que très peu de ligne, il n’y a aucune raison d’utiliser un INT en clé primaire. Il serait mieux d’utiliser un MEDIUMINT, un SMALLINT, ou encore un TINYINT dans certains cas.
De la même manière si vous n’avez pas besoin de stocker l’heure, alors il n’y a aucun raison d’utiliser un DATETIME. Il vaudrait mieux privilégier l’utilisation d’un simple DATE.

Cependant, faites toujours attention à garder une certaine marge de sécurité.

19. Utilisez le bon moteur de stockage

Les deux principaux moteurs de stockage de MySQL sont MyISAM et InnoDB. Chacun ont leurs avantages et leurs inconvénients.

MyISAM est bon pour la lecture des données, mais il ne se place pas très bien quand il y a beaucoup d’écritures à effectuer. De plus lorsque vous mettez à jour un champ d’une ligne existante, toute la table est verrouillé, et aucun autre processus ne peut lire la table jusqu’à ce que cette requête ne soit terminée. Néanmoins MyISAM est très rapide à calculer les requêtes de type : SELECT COUNT (*).

InnoDB a tendance à être un moteur de stockage plus complexe et peut être plus lent que MyISAM pour la plupart des petites applications. Mais il supporte le verrouillage « row-based », c’est à dire le verrouillage de la ligne concerné et non de toute la table comme MyISAM. Il supporte également quelques fonctionnalités avancées telles que les transactions.

20. Soyez prudents avec les connexions persistantes

Les connexions persistantes ont pour but de réduire la surcharge liée à la création des connexions au serveur MySQL. Lorsqu’une connexion persistante est créé, elle restera ouverte même après la fin de l’exécution du script.

La fonction PHP qui permet d’utiliser les connexions persistantes est : mysql_pconnect();

Tout dépend des cas mais la plus part du temps l’utilisation des connexions persistantes n’est pas utile. Lorsque vous utilisez les connexions persistantes Apache démarre énormément de processus parallèles, et crée de nombreux processus enfant. C’est la principale raison pour laquelle les connexions persistantes ne fonctionnent pas très bien dans cette environnement. Avant d’utiliser les connexions persistantes il est donc bon de consulter son Administrateur Système.

Et bien voila, normalement maintenant vous êtes prêts à développer des applications web sans tomber dans les pièges d’une base mal structurée ou des requêtes non-optimisées !

Bon développement !

Source : NetTuts+

Tags: developpement, Le web
Posté dans Autres, Le web