Comment réécrire une requête SQL ? Partie 2

21 décembre 2009 par stephane

Dans le précédent post, nous avons optimisé une requête en abandonnant un des principes du SQL (dire au SGBD ce qu’on souhaite faire, mais pas comment le faire). Ici nous allons voir un exemple où le fait de penser en SQL va nous permettre de rendre performante une requête difficile à améliorer.

Nous repartons de la table du post précédent, remplie avec 500 000 enregistrements :
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;

Nous voulons, à partir de cette table de produits, trouver pour chaque catégorie le produit qui s’est le plus vendu.

Première idée : raisonner en terme de boucle, c’est-à-dire demander au SGBD de retrouver pour chaque catégorie le produit qui s’est le plus vendu. Cela donne en SQL la requête suivante :

mysql> SELECT sql_no_cache pdt.*
FROM product pdt
WHERE sold =
(SELECT MAX(sold) FROM product
WHERE category_id = pdt.category_id);

On note la sous-requête corrélée, qui traduit en SQL notre idée de boucle à travers l’ensemble des catégories.

Temps d’exécution : très long… en effet j’ai arrêté l’exécution de la requête au bout de 20 mn, et toujours pas de résultat en vue à ce moment-là ! Un bon index est sans doute nécessaire…

Examinons le résultat de la commande EXPLAIN :

mysql> EXPLAIN SELECT...\G
***************** 1. row *****************
id: 1
select_type: PRIMARY
table: pdt
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 500000
Extra: Using where
***************** 2. row *****************
id: 2
select_type: DEPENDENT SUBQUERY
table: product
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 500000
Extra: Using where
2 rows in set (0,00 sec)

Effectivement, ce n’est pas fameux : pour chaque ligne de la table product, MySQL va exécuter la sous-requête, qui elle-même fait un scan complet de la table product… On est dans une situation bien pire qu’un CROSS JOIN, ce qui explique la lenteur constatée.

Un index composite sur (category_id, sold) va bien nous permettre d’améliorer la sous-requête…

mysql> ALTER TABLE product add index idx_category_sold (category_id,sold);
mysql> EXPLAIN SELECT ...\G
***************** 1. row *****************
id: 1
select_type: PRIMARY
table: pdt
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 500000
Extra: Using where
***************** 2. row *****************
id: 2
select_type: DEPENDENT SUBQUERY
table: product
type: ref
possible_keys: idx_category_sold
key: idx_category_sold
key_len: 4
ref: test.pdt.category_id
rows: 500
Extra: Using index
2 rows in set (0,00 sec)

… mais il n’existe pas de moyen d’améliorer la requête principale.

Le temps d’exécution après ajout de l’index est maintenant de 2mn15, ce qui est encore loin d’être satisfaisant.

La limitation de notre requête, comme nous l’a montré la commande EXPLAIN, c’est que pour chacune des 500 000 lignes de la table, MySQL va devoir exécuter la sous-requête. Cela signifie que même en optimisant au mieux la sous-requête, celle-ci sera toujours exécutée 500 000 fois, ce qui est forcément couteux. Ajouter un index ne fait que limiter les dégâts, mais ne suffit pas pour obtenir des performances correctes.

La vraie solution à notre problème va consister à changer de point de vue sur la demande initiale formulée en langage courant, afin de pouvoir écrire la requête d’une toute autre manière, qui, espérons-le, sera plus efficace.

Nous allons donc raisonner de façon ensembliste. Avec notre table, il nous est possible de constituer deux ensembles : l’ensemble E1 des informations sur les produits (facile : SELECT * FROM product) et l’ensemble E2 des produits qui se sont le mieux vendus (facile aussi : SELECT category_id, MAX(sold) FROM product GROUP BY category_id). Si nous sommes capables de faire l’intersection entre E1 et E2, nous aurons résolu notre problème.

Or faire l’intersection de deux ensembles se traduit en SQL par une jointure entre deux tables. La solution est donc toute tracée :

mysql> SELECT sql_no_cache pdt.* FROM (
SELECT category_id, MAX(sold) as maxi
FROM product
GROUP BY category_id
) AS maxi_list
INNER JOIN product pdt
ON pdt.category_id = maxi_list.category_id
AND pdt.sold = maxi_list.maxi;

EXPLAIN nous montre comment est exécutée cette nouvelle requête :

mysql> EXPLAIN SELECT ... \G
***************** 1. row *****************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
***************** 2. row *****************
id: 1
select_type: PRIMARY
table: pdt
type: ref
possible_keys: idx_category_sold
key: idx_category_sold
key_len: 8
ref: maxi_list.category_id,maxi_list.maxi
rows: 1
Extra:
***************** 3. row *****************
id: 2
select_type: DERIVED
table: product
type: range
possible_keys: NULL
key: idx_category_sold
key_len: 4
ref: NULL
rows: 1001
Extra: Using index for group-by
3 rows in set (0,05 sec)

MySQL exécute d’abord la sous-requête puis place le résultat dans une table temporaire, qui est jointe à la table product. A noter que la jointure se fait avec deux conditions, qui sont nécessaires toutes les deux.

Quel est le temps d’exécution de cette requete ? 0.06s… Pas besoin de commentaire, le gain est vertigineux !

Ces deux articles nous ont donc permis de voir que la manière dont une requête est formulée peut avoir des conséquences très importantes sur les temps d’exécution. Il n’existe pas de règle pour savoir si une requête est bien écrite ou pas, mais quand vous rencontrez une requête utilisant de bons index mais qui est lente, il peut être très intéressant de réfléchir à son sens pour trouver une réécriture qui sera performante.

Mots-clefs : ,

7 commentaires sur “Comment réécrire une requête SQL ? Partie 2”

  1. InAme dit :

    Enorme!
    Les gains de performances sont vraiment impressionnantes! On voit ces exemples sur des MIN ou des MAX, est il possible d’arriver à de tels résultats avec des COUNT?

  2. stephane dit :

    Malheureusement, pour les COUNT, ça ne fonctionne pas car pour il faut nécessairement parcourir toutes les lignes qui doivent être comptées.
    Une bonne technique pour optimiser les COUNT est d’utiliser un covering index, j’essaierai de faire prochainement un post sur le sujet.

  3. Arnaud dit :

    Hello,

    juste en passant : ca n’est pas aussi fiable qu’un count(*) mais quand je veux récupérer rapidement le nb (approximatif) de ligne d’une table, je fais un SHOW TABLE STATUS LIKE ‘ma_table’\G c’est immédiat et idéal pour récupérer un ordre de grandeur uniquement.

  4. stephane dit :

    Le show table status, tu le fais sur des tables MyISAM ou InnoDB ?
    Parce qu’il me semble que pour InnoDB, la commande verrouille le buffer pool, ce qui veut dire que la commande risque d’être longue.

  5. InAme dit :

    Oui c’est bien ce que je pensais… c’est normal.

    Mais dans mon cas « SHOW TABLE STATUS LIKE » ne me sera pas de grande utilité car je cherche à compter précisément un nombre de champs d’une table qui remplissent certaines conditions.
    Donc si j’ai 100.000 résultats à compter, mysql est obligé des lire toutes ces lignes.

  6. Arnaud dit :

    @steph : je l’utilise sur du InnoDB. Le verrouillage est tres court et pour moi inutile de s’en soucier sur l’execution d’une seule commande… Ca peut prendre qq secondes mais je n’ai jamais eu de blocage pénalisant.

  7. [...] représente une opération ensembliste, repensons notre requête en termes ensemblistes (voir cet autre article sur le [...]

Laisser une réponse