dbnewz

Que faire quand une requête est lente ? Tout le monde vous dira qu’il faut commencer par regarder ce que dit EXPLAIN, puis ajouter ou modifier des index et s’il n’y a aucune amélioration, alors il faut essayer de réécrire la requête.

Déterminer quels sont les index à poser pour optimiser la requête est une tâche relativement simple avec un peu d’habitude, mais que peut bien signifier la notion de réécriture d’une requête ?

Bien souvent, cette notion se limite à triturer les conditions de la clause WHERE de manière à isoler une colonne pour qu’un index soit utilisé. Et pourtant, il y a bien d’autres choses à imaginer, ce que nous allons aborder dans cet article.

Pour commencer, revenons très rapidement sur l’isolation des colonnes. Pour rappel, MySQL ne peut pas se servir d’un index sur une colonne si cette colonne fait partie d’une fonction.

Un petit exemple ?

mysql> EXPLAIN SELECT * FROM t WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date_gen) > 180\G
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
           ...

Ici, à cause de la fonction UNIX_TIMESTAMP(), MySQL refuse même d’étudier la possibilité d’utiliser l’index sur la colonne date_gen (ce qu’on voit dans la colonne possible_keys). Si par contre on réécrit un peu plus proprement notre condition et qu’on regarde de nouveau le plan d’exécution :

mysql> EXPLAIN SELECT * FROM t WHERE date_gen < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 180 SECOND)\G
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: idx_date
          key: idx_date
          ...

L’index sur la date est maintenant un candidat potentiel (toujours possible_keys) et il est utilisé (colonne key).

Ce genre de manipulations est en général simple à réaliser, venons-en donc à des techniques un peu moins connues.

Pour la suite de cet article, nous allons prendre des exemples avec une table product ayant les colonnes suivantes :

CREATE TABLE `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL DEFAULT '0',
`reference` varchar(20) NOT NULL DEFAULT '',
`name` varchar(25) NOT NULL DEFAULT '',
`sold` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM;


Cette table a été peuplée avec 500 000 enregistrements grâce à supersmack.

Imaginons que nous voulons retrouver le premier produit qui a été inséré dans cette table dans la categorie n° 100. Facile :

mysql> SELECT MIN(product_id) FROM product WHERE category_id = 100;


Cette requête prend environ 0.9s à s’exécuter. Comme nous pensons que c’est un peu lent, nous allons essayer de l’améliorer, mais avec une contrainte forte : nous ne voulons pas modifier la structure de la table, et donc en particulier nous ne souhaitons pas ajouter un éventuel index.

La commande EXPLAIN nous apprend que le plan d’exécution choisi est un scan complet de la table, ce qui n’est pas une surprise puisque le seul index disponible (la clé primaire) ne risque pas de nous aider. Que faire alors ? Réécrire la requête bien sûr…

En réfléchissant bien, on peut se dire que si on scannait la clé primaire, on pourrait s’arrêter à la première valeur trouvée pour laquelle le champ category_id vaut 100. En effet, la clé primaire étant triée par ordre strictement croissant, la première valeur trouvée sera nécessairement la plus petite. D’où un premier essai :

mysql> SELECT MIN(product_id) FROM product USE INDEX(PRIMARY) WHERE category_id = 100;

Malheureusement, un appel à EXPLAIN montre que MySQL ne veut toujours pas utiliser la clé primaire, idem avec FORCE INDEX. Il va donc falloir être un peu plus persuasif…

Nous allons changer la requête en remplaçant le MIN() par un tri avec une clause LIMIT :

mysql> SELECT product_id FROM product WHERE category_id = 100 ORDER BY product_id ASC LIMIT 1;


Cette fois-ci, EXPLAIN nous indique bien un scan de la clé primaire. Extrait du résultat de la commande :

mysql> EXPLAIN SELECT product_id FROM product  WHERE category_id = 100 ORDER BY product_id ASC LIMIT 1\G
           id: 1
  select_type: SIMPLE
        table: product
         type: index
possible_keys: NULL
          key: PRIMARY

Et maintenant, le temps d’exécution passe à 0.01s, soit un gain notable.

Voilà donc un cas intéressant, où en comprenant bien comment sont stockées les données et les index, on peut réussir à réécrire une requête pour optimiser son exécution. Néanmoins, certains d’entre vous ont peut-être bondi en voyant cette réécriture.

En effet, nous avons tous appris que l’essence du SQL, c’est d’indiquer au SGBD ce qu’on souhaite trouver en laissant toute latitude au SGBD pour déterminer la manière la plus efficace d’effectuer la requête. Ici, non seulement, on indique comment faire la requête mais en plus, on perd le sens de la requête, l’appel à la fonction MIN() étant beaucoup plus explicite sur notre intention que la clause ORDER BY avec un LIMIT. Parfois, il faut pourtant juste être pragmatique et laisser tomber les grands principes au profit de la performance.

A noter avant de clore cette 1ère partie que la même requête initiale avec un MAX() n’aurait pas pu être réécrite d’une manière similaire. Notre technique de réécriture repose en effet sur le tri croissant de la clé primaire et il aurait fallu un tri décroissant sur la clé primaire pour que la technique fonctionne avec le MAX().

La seconde partie de cette article sera consacrée à des exemples de réécriture où le fait de penser en SQL sera bénéfique. Ouf, nous pourrons nous réconcilier avec les puristes du SQL.

Cet article a été publié le Jeudi 26 novembre 2009 à 11:04 et est classé dans MySQL. Vous pouvez en suivre les commentaires par le biais du flux RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre propre site.