De la concurrence des requêtes SQL

Sans cité Montaigne, voici un cas académique dans lequel la concurrence des requêtes SQL est souvent oubliée.

Nous avons une table hits qui contient 4 champs : une date (nommé date), une clé étrangère pointant vers un objet du système (nommée object_id) et un compteur implémenté sous ca forme la plus simple : un entier (nommé counter). Puisqu'on travail avec une framework qui implémente de l'active record on ajoute un champ id qui fera office de clé primaire.
A chaque utilisation de l'objet référencé par la clé étrangère on souhaite incrémenter le compteur et recommencer à 0 tous les minuits afin d'avoir un historique journalier de l'utilisation de l'objet. Alors comme on a quand même un peu réfléchi à la performance du système (un billet sur le sujet est en préparation), on définit un index composé des champs date et object_id. Puis on définit la fonction suivante, donnée ci-dessous en pseudo-code, qui incrémente notre compteur et insère un nouveau tuple si aucun n'existe pour le jour courant :

void function hit ( int $object_id ) {
  SELECT FROM hits WHERE object_id = $object_id AND date = NOW()
  if (row_exists)
      UPDATE hits SET counter = counter + 1 WHERE  object_id = $object_id AND date = NOW()
  else
      INSERT INTO hits (object_id, date, counter) VALUES ($object_id, NOW(), 1)
}

Tout se passe bien jusqu'au jour où on se rend compte qu'il y a plusieurs lignes par objet et par jour dans la base de données.

Ce problème vient du fait qu'il peut se passer un temps indéterminé entre la requête SELECT et l'INSERT. Si à 00h01 2 requêtes sont faites en même temps sur le même objet, il est fort probable que 2 requêtes SELECT soient faites avant un INSERT, et donc les 2 tests if (row_exists) vont retourner faux et 2 INSERT seront fait.

Les solutions pour résoudre ce problème sont multiples, elles passent de la redéfinition de la clé primaire en date, object_id plutôt que notre champ id, ce qui aurait comme comportement de faire échouer le 2ème INSERT, erreur qui pourrait être capturée et traitée spécifiquement. Une autre solution serait de mettre un verrou sur la fonction, chose très aisée en Java avec le mot-clé synchronized. La fonction deviendrait void synchronized function hit(int object_id), évitant ainsi ce type de problème de concurrence.

Dans tous les cas les applications web sont aussi (voir même plus) soumises aux problèmes de concurrence, et l'expérience nous montre que la technique de l'autruche de même que les phrases du style "Il ne PEUT PAS y avoir 2 requêtes en même temps" sont à bannir absolument.

Types ENUM avec MySQL

Toujours en train de travailler avec MySQL, et encore pour un bon moment, voici une particularité du type enum :

Soit un champ field2 :

`field2` enum('0','1') NOT NULL default '0'

Soit une requête :

SELECT * FROM ... WHERE ... AND field2 = 1

Le résultat de cette requête est intéressant dans le sens où MySQL va sélectionner tous les tuples dont la valeur du champ field2 vaut '0'. Pourquoi cela ? Parce qu'en passant le 1 sous forme d'entier, MySQL va l'associer à la première valeur de l'énumération, '0' donc. Dans le même ordre d'idée WHERE ... AND field2 = 2 sélectionnera tous les tuples dont field2 vaut '1'.

La requête

SELECT * FROM ... WHERE ... AND field2 = '1'

fournira quant à elle la juste réponse. Attention donc avec les types des valeurs, surtout en PHP...

Mysql NT vs Unix : quand les systèmes de fichier font des différences

La question posée ici est la suivante : le nom des tables dans une base de données MySQL est-il sensible à la casse ou pas ?

La réponse dépend du système sur lequel le serveur tourne... Et cette différence vient même d'une manière plus intrinsèque du système de fichier sur lequel repose la base de données.

Explication :

Pour MySQL, une base de données est un répertoire du même nom dans lequel se trouvent des fichiers représentant les tables. Le nom des fichiers est naturellement identique aux tables, à l'extension prêt.

Pour savoir sur une base existe, MySQL va chercher si le répertoire correspondant existe. Du même principe pour accéder à un table il va ouvrir le fichier lié.

Sur un système de fichier Unix, ext3 ou reiserfs par exemple, un fichier base1/table1.MYI n'est pas le même que base1/tAbLe1.MYI, donc une requête "SELECT * FROM table1" ou "SELECT * FROM tAbLe1" ne produira pas le même résultat. Sur un système de fichier NTFS par contre, les chemins ci-dessus pointeront vers le même fichier, et donc les 2 requêtes précédents fonctionneront.

En conclusion, le nom des bases et tables MySQL sont sensibles à la casse sur un système Unix, et non sensible à la casse sur un système Windows.

Optimisation du nombre de requêtes SQL dans les collections d'objets et relation n-m

L'orientation objet de PHP n'est plus contestable, mais les problèmes d'optimisation persistent.

Par exemple le malheureusement célèbre n+1 pattern, qui fait que pour afficher une liste de n objets, le + 1 étant la requête qui sélectionne tous les ids des objets à instancier, n + 1 requêtes SQL seront effectuées.

De même dans des relations n-m, la majorité des implémentations sélectionnent les n objets (en n + 1 requêtes donc), puis pour chacun on sélectionne les m objets de la relation. On obtient donc n * m + 1 requêtes.

Le but de cet article est de présenter deux techniques qui, combinées, réduisent les n * m + 1 requêtes en n + m + 1.

Les deux techniques que je vais illustrer ici se nomment object caching et grouped fetching. Elles se combinent très bien, ce qui permet d'optimiser drastiquement les performances d'un script PHP, du points de vue I/O (moins de requêtes), vitesse d'exécution et même mémoire utilisée (les objets ne sont pas dupliqués).

Object caching :

Le principe de l'object caching est de rendre le constructeur de l'objet privé (au pire protégé) et de l'instancier au moyen d'une factory. Puis on ajoute à la classe un tableau statique dans lequel les références des objets instanciés seront placés. La factory va donc regarder dans le tableau de références si l'objet existe, et si ça n'est pas le cas elle va le créer, l'ajouter au tableau et le retourner.

class A {
 protected static $objects_cache = array();

 public static function factory($id, $class = __CLASS__)
 {
   if (array_key_exists($id, $class::$objects_cache)) {
     return $class::$objects_cache[$id];
   } else {
     $o = new $class($id);
     $class::$objects_cache[$id] = $o;
     return $o;
   }
 }
}

Cette solution pourrait encore être améliorée si les objets pouvaient être partagés entre toutes les instances de PHP. Ce n'est pas le cas à cause de l'architecture share nothing de PHP, et c'est un des points forts des serveurs d'applications.
Un autre désavantage de ce concept, si on a un script qui tourne suffisamment longtemps pour que le grabage collector se lance, est que tous les objets créés sont toujours référencés, même ceux qui pourraient être des candidats potentiels à la finalisation. Ce problème est résolu dans d'autres langages, en Java par exemple grâce aux références faibles (WeakReference).

Grouped fetching

Le principe du grouped fetching, dérivé d'une solution proposée par notre ami Colder, est de charger les données des objets de manière asynchrone et en bloque. Quand un objet est instancié, il est marqué comme non chargé, et sa référence est placée dans un tableau global de la classe. Puis lors d'un accès à un champ non chargé, la classe va sélectionner dans la base de données tous les objets instanciés mais pas encore chargés. Plus on retarde les accès aux attributs d'un objet, plus on va paralléliser les requêtes SQL.

class A {
 private static $_to_load = array();
 private $_is_loaded = false;
 public function __construct($id)
 {
   $this->id = $id;
   self::$_to_load[$id] = $this;
 }

 public function __get($attribut)
 {
   if (!$this->_is_loaded) {
     self::_groupedLoad();
   }
   return $this[$attribut];
 }

 private function _setLoaded()
 {
   $this->is_loaded = true;
 }

 private static function _groupedLoad()
 {
   $ids = implode(', ', self::$_to_load);
   $query = 'SELECT * FROM ' . self::$_table . ' WHERE id IN ( ' . $ids . ' ) ';
   $res = db_query($query);
   while ($row = $res->getNext()) {
     self::_to_load[$row['id']]->_initByArray($row);
     self::_to_load[$row['id']]->_setLoaded;
   }
 }
}

L'overhead de ce concept est très faible (un tableau de références supplémentaire), et le nombre d'accès à la base de données sont grandement réduit. Mais le problème de la finalisation des objets se repose aussi.

De l'art d'éviter les requêtes SQL inutiles

Dans le domaine de l'optimisation, une chose simple à faire est d'essayer de réduire au strict minimum le nombre de requêtes à faire à la base de données.

Je souhaite simplement rendre attentif au problème posé par les procédures embarquées et autres tirggers :

Dans le cas d'une mise-à-jour d'un objet mappé sur une table d'une base de données, les champs de l'objet sont remplacés (après nettoyage...) par ceux du formulaire. On pourrait donc penser qu'il est inutile de faire un SELECT juste après un UPDATE, car les données mises-à-jour sont celles fournies.

Cela est vrai sans compter sur les triggers déclenchés en cas de mises-à-jour : sans un SELECT après l'UPDATE, certains champs peuvent contenir des données fausses car non traitées par le trigger. Les champs auto-timestamp de Mysql sont un exemple tout simple de trigger à ne pas oublier...