Optimisez vos requêtes SQL #1

Pour certains, l'optimisation des requêtes SQL n'est plus nécessaire. Les machines d'aujourd'hui ont des capacités de calcul tellement élevé que l'optimisation devient un sujet secondaire. Mais ne croyez pas cela ! Dans les débuts de l'informatique on comptait la mémoire en ko alors même si maintenant on est très loin de tout ça, n'oublions pas que plus c'est rapide mieux c'est. Et quand on sait que certaines pratiques peuvent multiplier vos temps de traitement par centaine...
Il existe des tonnes d'astuces pour améliorer la vitesse de vos requêtes. Dans cette série d'articles je vais vous en présenter quelques unes que vous pourrez facilement mettre en œuvre.

Tout au long de cette série nous allons prendre comme exemple la base de donnée suivante :

On a une table musique et une table genre. Une musique appartient à un et un seul genre. Un genre est composé de 0 à plusieurs musiques.
On a une donc une clé étrangère id_genre dans la table musique faisant référence à id de la table Genre.

Pour bien comprendre les exemples nous allons ajouter des données aux tables :

id
titre
date_sortie
id_genre
1
Born Again
2014
1
2
Scarface
2011
2
3
Animals2013
1
4
Sunset Lover
2017
1
5
Selfie
2015
2
6
4422
2017
2
id
libelle
1
ELECTRO
2
RAP

Pour tous les articles sur ce thème, nous prendrons en référence cette base de données.
On commence avec :

Les INNER JOIN

Pour les plus novices d'entre nous, un INNER JOIN permet de faire l'association entre nos tables dans une requête SQL. Voilà comment nous pouvons récupérer la liste de nos musiques du genre ELECTRO :

SELECT titre,id_genre 
FROM musique INNER JOIN genre ON musique.genre_id = genre.id
WHERE genre.libelle LIKE "ELECTRO";

On obtient :

titre
id_genre
Born Again
1
Animals
1
Sunset Lover
1

Jusqu'ici tout va bien, on a fait notre requête, on a la bon résultat. Mais en fait rien ne vas plus... La base des INNER JOIN c'est le produit cartésien. En mathématiques, le produit cartésien de deux ensembles X et Y, est l'ensemble de tous les couples dont la première composante appartient à X et la seconde à Y. Un produit cartésien c'est une multiplication.

Pour obtenir le résultat le SGBD commence par faire le produit cartésien de Musique et Genre. C'est cette partie de la requête :

FROM musique INNER JOIN genre ON musique.genre_id

Voilà ce que le SGBD obtient :

titre
id_genre
id (de la table Genre)
Born Again
11
Born Again
12
Scarface
21
Scarface
2
2
Animals
1
1
Animals
1
2
Sunset Lover
1
1
Sunset Lover
1
2
Selfie
2
1
Selfie
2
2
4422
2
1
4422
2
2

... ça fait beaucoup de lignes non ? Et ce n'est pas fini ! Le SGBD doit maintenant tester les lignes, c'est cette partie de la requête :

ON musique.genre_id = genre.id

Il vérifie si musique.genre_id est égal à genre.id et sélectionne les lignes en conséquence. Ce qui donne :

title
id_genre
id (de la table Genre)
Born Again
1
1
Born Again
1
2
Scarface
2
1
Scarface
2
2
Animals
1
1
Animals
1
2
Sunset Lover
1
1
Sunser Lover
1
2
Selfie
2
1
Selfie
2
2
4422
2
1
4422
2
2

Une fois les lignes rouges enlevés, on obtient le résultat du inner join, mais c'est pas fini ! Il reste la dernière partie de la ququête, le WHERE :

WHERE genre.libelle LIKE "ELECTRO"

Ici c'est plus simple, on supprime les lignes qui ont genre.id!=1. On obtient :

titre
id_genre
Born Again
1
Animals
1
Sunset Lover
1

Et voilà ! On en conclut que passer par un INNER JOIN induit beaucoup de traitements. Et on oublie pas le produit cartésien ! On a :

  • 6 lignes dans la table Musique
  • 2 lignes dans la table Genre

Ce qui fait déjà 6*2=12 lignes à traiter !

Si j'ai 2000 musiques et 8 genres : 2000*8 = 16000 lignes à traiter !!!

Ce n'est pas tout, imaginons que l'on ajoute une table artiste avec dedans 1000 artistes, on a tout simplement : 2000*8*1000 = 16 000 000 LIGNES A TRAITER !!!! pour un select qui va nous ressortir quoi ? maximum 2000 lignes !

Vous l'aurez compris c'est là le principal problème du INNER JOIN ! Plus on ajoute de donnée et de tables, plus le temps de traitement de la requête est long. Et il ne s'agit pas simplement su simple au double... ça montre très, très vite.

La solution : les sous-requêtes

Une solution simple à mettre en œuvre (mais qui a ses limitations, je sais) est l'utilisation des sous-requêtes. Et c'est là la puissance du SQL, mettre des requêtes dans des requêtes. Avec SQL il est possible d'imbriquer des requêtes un peu à la manière de poupées gigognes.

Reprenons notre exemple ci-dessus (tables Musique et Genre) et voyons ce qu'on peut faire avec.

En INNER JOIN on a :

SELECT titre,id_genre 
FROM musique INNER JOIN genre ON musique.genre_id = genre.id
WHERE genre.libelle LIKE "ELECTRO";

En sous-requête :

SELECT titre,id_genre
FROM musique
WHERE id_genre IN (
    SELECT id
    FROM genre
    WHERE libelle LIKE "ELECTRO"
);
La sous-requête se place dans le WHERE. Pour comprendre comment cela fonctionne, on commence par executer la sous-requête (dans notre tête) on a :

id
1

Pour illustrer, la requête devient :

SELECT titre,id_genre
FROM musique
WHERE id_genre IN (1);

Vous connaissez la suite, une requête select normale.

En conclusion, une sous-requête utilise beaucoup moins de ressources qu'un INNER JOIN. Mais si les INNER JOIN existent, ce n'est pas pour rien... Les sous-requêtes peuvent être utilisés dans certains cas mais il reste des exceptions.

Les limites

On se rend rapidement compte que l'utilisation des sous-requêtes apporte rapidement quelques limites.

Première limite : récupérer des données de l'autre table dans le SELECT. C'est tout simplement impossible.
Avec un INNER JOIN je peux faire un SELECT titre, id_genre, libelle avec un sous-requête ça n'est pas possible.

Ensuite, pour ceux qui utilisent régulièrement des framework, vous savez que les ORM utilisent des INNER JOIN pour faire les jointures entre les tables. Difficile donc de passer à côté.


En conclusion, je sais qu'avec les technologies actuelles tout ça paraît inutile. Gardez en tête que le temps est précieux, quand on sait combien de temps un utilisateur reste sur un site web en moyenne, ne perdez pas de temps avec le chargement...

Commentaires