Je suis récemment tombé sur cet article http://www.sitepoint.com/mysql-mistakes-php-developers listant quelques bonnes pratiques concernant MySQL. J'ai trouvé ce post intéressant et souhaite donc le partager avec vous (l'approche est un peu différente de l'article original).

Utiliser InnoDB plutôt que MyISAM

Par défaut, lorsque vous créez une base de données MySQL, celle-ci est au format MyISAM, c'est dommage ! En effet, MyISAM ne gère ni les clés étrangères, ni les transactions. MyISAM lock aussi complètement les tables en cas d'insertion ou de mis à jour, ce qui est "sécurisant" mais coûteux en terme de performance.

Préférez donc InnoDB qui présente les avantages suivants :

  • modèle ACID : atomicité, cohérence, isolation et durabilité (plus performant que le système de lock de MyISAM)
  • supporte les transactions
  • support les clé étrangères
  • permet les sauvegardes à chaud

C'est bien connu : pas d'avantages sans inconvénients ! Ainsi, InnoDB est plus complexe à administrer que MyISAM. Vous trouverez la procédure permettant d'activer le mode InnoDB par défaut et de migrer vos table de MySIAM vers InnoDB ici.

Utiliser l'extension PHP Mysqli

Depuis la version 4.1+ de mysql, il est préférable d'utiliser mysqli (MySQL Improved) pour communiquer avec la base de données. Les principaux avantages de cette extension sont les suivants :

  • interface (optionnelle) orientée objet
  • utilisation de requêtes préparées  : amélioration de la sécurité et des performances
  • support des transactions

Vous trouverez la documentation officielle à cette adresse : http://www.php.net/manual/fr/book.mysqli.php.

Sécuriser les inputs

Il est nécessaire de valider toutes les variables reçues côté PHP avant de les insérer dans une requête afin de limiter les risques d'injection SQL du type :

$username = $_POST["name"];
$password = $_POST["password"];
$sql = "SELECT userid FROM usertable WHERE username='$username' AND password='$password';";
// run query...

Imaginons que $_POST[« name »] ait pour valeur : "admin'; –" la requête envoyée au serveur MySql sera la suivante :

SELECT userid FROM usertable WHERE username='admin';

ET voilà, l'utilisateur sera connecté en admin dans le cas d'une requête utilisée pour une authentification. Il faut savoir que généralement, les frameworks et CMS gèrent automatiquement ce type d'injection en échappant certains caractères par exemple. Si vous devez faire les tests manuellement, voici 2 petites actuces :

  • appliquer la méthode mysql_real_escape_string() au chaines de caractères
  • vérifier le entiers / numériques grâce à la méthode intval()

Utiliser UTF-8

Pensez à utiliser le charset utf-8, notamment dans le cadre de projets multilingues.

CREATE DATABASE `mabase` CHARACTER SET utf8 COLLATE utf8_general_ci;

Bon côté PHP, vous allez me dire "oui mais moi avec ces histoires d'encodage je galère à chaque fois", patience, PHP 6 devrait régler ce problème.

Préférer un traitement SQL plutôt que PHP

En tant que développeur, vous préfèrez coder une moyenne en PHP plutôt que d'utiliser la petite fonction AVG de MySQL ? Erreur, il est plus performant de traiter ce cas côté MySQL. Songez donc à étudier la documentations MySQL pour vérifier s'il est possible d'effectuer les traitements dans la requête. De la même manière, évitez les requêtes dans une boucle PHP.

Optimiser les requêtes

99% des problèmes de performance sont causés par la base de données. La commande EXPLAIN de MySQL(tutoriel ici) permet de visualiser le plan d'exécution des requêtes, vous pourrez ainsi repérer les points bloquants. Des outils comme Query Profiler vous permettront également d'optimiser vos requêtes et votre schéma.

Utiliser le type de donnée adapté

Nous sommes souvent tenté de stocker des données sérialisées dans un champ de type texte. Ceci est très pratique mais peut s'avérer catastrophique en cas de forte volumétrie. Cette solution est donc à manipuler avec des pincettes, il est préférable d'utiliser au maximum les types de champs proposés par MySQL.

Eviter le "SELECT *"

Souvent, seuls certains champs sont nécessaires, il est donc inutile de tous les récupérer. Vous devez donc, spécifier les champs à récupérer un par un même si vous les voulez tous. En effet, si par la suite de nouveaux champs sont ajoutés à la table, le "*" les remontera alors qu'ils n'étaient pas nécessaires dans cette requête.

Manipuler les index raisonnablement

La règle de base pour positionner des index sur des tables est la suivante : toutes les colonnes aparaissant dans la clause WHERE de vos requêtes doivent avoir un index. On peut donc être tenté d'en positionner sur toutes les colonnes… surement pas ! En enffet, les index sont recalculés lors des opérations INSERT et UPDATE, ceci provoquerait des problèmes de performance.

Sauvegarder

Assurer vous de mettre en place des backups de la base. Vous pouvez également mettre en place un système de réplication master / slave.
Rappel : mysqldump permet d'effectuer des sauvegardes en ligne de commande.

Si vous avez des remarques ou des compléments à apporter, n'hésitez pas à commenter cet article.