Optimiser les requêtes SQL de CakePHP

Lorsque l’on crée une application dont les tables contiennent des dizaines ou des centaines de milliers d’enregistrements, il s’agit de bien contrôler les requêtes SQL qui vont être lancées afin d’éviter les “goulets d’étranglement” qui ne manqueront pas de ralentir l’application, voire de planter le serveur de base de données.

Au sommaire :

1. Définir les bons index ;
2. Spécifier les champs attendus ;
3. Régler le niveau de récursivité ;
4. Utiliser le Comportement Containable ;
5. Forcer les jointures avec les relations hasMany.

Nous allons voir quelques règles à respecter, quelques bonnes habitudes à adopter, mais aussi un moyen de tricher pour parvenir à nos fins. Cependant, gardons bien à l’esprit cette règle générale : utilisons au maximum les méthodes d’accès aux données fournies par Cake (Model::find, Model::save, etc.), en évitant autant que possible d’exécuter nos propres requêtes (Model::query) faites à la main. Quatre raisons à cela :

1. la compatibilité entre SGBD est conservée ;
2. la sécurité de la requête est assurée ;
3. le format de sortie habituel des résultats est conservé ;
4. les callbacks sont disponibles (beforeFind, afterFind, beforeValidate, beforeSave, afterSave, beforeDelete, afterDelete)

1. Définir les bons index

La première et la plus bénéfique optimisation possible consiste à définir des bons index sur une table. Disons pour simplifier qu’un index est une table des matières que le SGBD consulte pour trouver rapidement un ou plusieurs enregistrements. Le parcours des index étant infiniment plus rapide que le parcours des lignes de la table, il est crucial d’en mettre, et sur les bonnes colonnes. D’une manière générale, les colonnes à indexer sont les clés primaires (automatique), les clés étrangères et ensuite, si besoin, les colonnes sur lesquelles vont s’effectuer des recherches.

Nous laissons le lecteur se documenter sur ce sujet qui ne concerne pas CakePHP directement.

2. Spécifier les champs attendus

Un moyen simple de réduire le volume de données qui transite entre les scripts et la base est de définir les champs dont nous avons réellement besoin. Par exemple, pour afficher simplement une liste d’articles, nous ne nous servirons pas de tous les champs, surtout pas du corps de l’article, souvent assez volumineux :

$this->Article->find('all'); // Renvoie toutes les colonnes
$this->Article->find('all', array('fields' => array('id', 'titre'))); // Renvoie l'id et le titre

3. Régler le niveau de récursivité

Le niveau de récursivité d’un find indique à Cake jusqu’à quelle “distance” il doit rapatrier les données des modèles liés. Prenons l’exemple des Articles d’un blog : ils ont un Auteur et peuvent avoir plusieurs Tags. Suivant le niveau de récursivité, la méthode Article::find('all') renverra :

• recursive = -1 : les Articles uniquement
• recursive = 0 : les Articles et leur Auteur
• recursive = 1 : les Articles, leur Auteur et les éventuels Tags associés
• recursive = 2 : les Articles, leur Auteur et les Articles de celui-ci, les éventuels Tags associés et les Articles associés à chacun des Tags.

Attention donc à ne pas définir un niveau de récursivité trop élevé.

4. Utiliser le Comportement Containable

Cet indispensable Comportement permet de choisir les modèles qui seront effectivement rapatriés lors d’un Model::find. Reprenons l’exemple ci-dessus et imaginons que nous souhaitions afficher les Articles (titre et date de création uniquement) et leurs Tags (libellé uniquement) mais pas les Auteurs :

class Article extends AppModel {
	var $actsAs = array('Containable');
	var $belongsTo = array('Auteur');
	var $hasAndBelongsToMany = array('Tag');
}

class ArticlesController extends AppController {
	function index() {
		$data = $this->Article->find(
			'all',
			array(
				'fields' => array('Article.titre', 'Article.created'),
				'recursive' => 1,
				'contain' => array(
					'Tag' => array(
						'fields' => array('Tag.label')
					)
				)
			)
		);
		$this->set(compact('data'));
	}
}

Notons que le paramètre 'recursive' => 1 n’est pas ici indispensable, le Comportement Containable étant capable de déterminer lui-même le niveau de récursivité requis pour que les modèles associés demandés soient bien inclus.

5. Forcer les jointures avec les relations hasMany

Essayons maintenant de trouver la liste des Auteurs et leurs Articles :

class Auteur extends AppModel {
	var $actsAs = array('Containable');
	var $hasMany = array('Article');
}

class AuteursController extends AppController {
	function index() {
		$data = $this->Auteur->find(
			'all',
			array(
				'fields' => array('Auteur.nom', 'Auteur.prenom'),
				'contain' => array(
					'Article' => array(
						'fields' => array('Article.titre')
					)
				)
			)
		);
		$this->set(compact('data'));
	}
}

Si nous regardons le debug de l’exemple ci-dessus, nous voyons que CakePHP envoie deux requêtes : la première pour trouver le nom et le prénom des Auteurs (plus l’id des Auteurs, ajouté automatiquement par Containable), et la deuxième pour trouver les titres des Articles de chaque Auteur, avec l’instruction SQL :

Article.auteur_id IN(id des Auteurs)

Au delà de 1000 id d’Auteur, le serveur ne répond plus. Pour faire face à cela, nous allons tricher un peu et forcer Cake à n’effectuer qu’une seule requête avec un jointure LEFT JOIN entre les tables auteurs et articles, beaucoup plus rapide que les deux requêtes séparées.

Pour arriver à ce résultat, nous allons enlever l’association hasMany entre Auteur et Article, puis créer une association hasOne le temps du find :

class AuteursController extends AppController {
	function index() {
		$this->Auteur->unbindModel(array('hasMany' => array('Article')));
		$this->Auteur->bindModel(array('hasOne' => array('Article')));
 
		$data = $this->Auteur->find(
			'all',
			array(
				'fields' => array('Auteur.nom', 'Auteur.prenom'),
				'contain' => array(
					'Article' => array(
						'fields' => array('Article.titre')
					)
				)
			)
		);
		$this->set(compact('data'));
	}
}

Cette fois, nous voyons dans le debug que Cake n’a bien fait qu’une seule requête, et nous pouvons travailler avec plus d’un millier d’enregistrements.

Pierre-Emmanuel Fringant