Optimiser ses requêtes SQL : bonnes pratiques pour les développeurs web

Les ORM

Avant toute chose, une précision sur les ORM (Object-Relational Mappers), devenus un standard incontournable pour de nombreuses applications. Ces outils abstraient la couche SQLLangage permettant de communiquer avec une base de données., vous permettant d'effectuer toutes les opérations nécessaires sur votre base de données sans écrire une seule ligne de SQL. On pourrait alors se demander : "Si je n'écris pas moi-même du SQL, est-il vraiment utile de savoir l'optimiser ?"

La réponse est oui. Il est essentiel de comprendre et de maîtriser SQL pour pouvoir utiliser au mieux votre ORM et ainsi optimiser les requêtes générées par celui-ci. Vous allez voir, dans la suite de cette article, que si l'on configure mal notre ORM, il peut rapidement faire n'importe quoi !

Maintenant que le contexte est posé, nous allons entrer dans le vif du sujet et détailler les principes fondamentaux pour maintenir une couche SQL optimisée.

Les Index

Les index sont des structures de données qui améliorent la rapidité des opérations de lecture en permettant un accès rapide aux données. Ils sont particulièrement utiles pour les colonnes fréquemment utilisées dans les clauses WHERE, les jointures, et les ordres (ORDER BY). Il existe différents types d'index, mais dans la majorité des cas, le type d'index par défaut proposé par votre SGBD sera suffisant.

Il est important de noter que, bien que les index accélèrent les lectures, ils peuvent ralentir les écritures. Il est donc essentiel de trouver un équilibre entre les deux.

Limiter les Résultats

Cette optimisation, bien que liée au SQL, relève aussi de la conception globale de l'application. L'objectif est de ne pas renvoyer trop d'informations en une seule fois. Lorsque vous devez afficher une liste d'éléments à vos utilisateurs, il est recommandé d'utiliser la pagination. Cela permet de ne pas surcharger les différentes couches de votre application : votre base de données retourne moins de résultats, votre application stocke moins d'objets en mémoire, et votre requête HTTP renvoie un contenu plus léger. Tout le monde y gagne ! Il est donc crucial de toujours limiter le nombre de résultats retournés par vos requêtes pour implémenter une pagination efficace.

Limiter les Requêtes SQL

Le constat est simple : moins il y a de requêtes SQL, plus votre application sera rapide. Souvent, les bases de données se trouvent sur un serveur distant, ce qui fait que le nombre de requêtes SQL a un impact significatif, car chaque requête engendre un temps de latence réseau.

Partant de ce constat, voici deux règles essentielles :

1. Ne jamais faire de requêtes dans les boucles.

2. Récupérer le plus d'informations possible en une seule fois.

Ne jamais faire de Requêtes SQL dans les Boucles

Pour minimiser les allers-retours avec la base de données et optimiser ses requêtes SQL, il faut éviter d'effectuer des requêtes à l'intérieur des boucles. Souvent, il est préférable de réaliser une seule requête pour récupérer toutes les données nécessaires aux différentes itérations de la boucle. Il est important de stocker le résultat dans une Map pour accéder rapidement aux données souhaitées lors de chaque itération. En optimisant les requêtes SQL, autant optimiser également son code ! Voici un exemple de la manière de procéder en Java avec le framework Spring Boot :

// Récupération de tous les articles à mettre à jour
List<Article> articles = getArticlesToUpdate();

// Récupération des nouveaux IDs des ArticleType nécessaires, sans doublons
Set<Long> articleTypeIds = articles.stream()
                                   .map(Article::getNewArticleTypeId)
                                   .collect(Collectors.toSet());

// Récupération de la liste des ArticleType par ID, stockée dans une Map
Map<Long, ArticleType> articleTypes = this.articleTypeRepository
        .findAllByIds(articleTypeIds)
        .stream()
        .collect(Collectors.toMap(ArticleType::getId, Function.identity()));

// Attribution du nouvel ArticleType à chaque Article
for (Article article : articles) {
    // Si l'ArticleType existe, on l'ajoute à l'Article, sinon on lève une exception
    if (articleTypes.containsKey(article.getNewArticleTypeId())) {
        article.setArticleType(articleTypes.get(article.getNewArticleTypeId()));
    } else {
        throw new Exception(String.format("Article Type with ID %s doesn't exist", article.getNewArticleTypeId()));
    }
}

Récupérer le Plus d'Informations en Une Fois

L'ORM vous permet de mapper vos tables de base de données en objets. Les choses se compliquent souvent lorsqu'il y a des jointures entre différentes tables, créant ainsi des dépendances entre les objets. Pour reprendre l'exemple précédent, supposons que vous ayez une table article contenant une clé étrangère vers une table article_type.

Si votre ORM est mal configuré, lorsque vous souhaitez récupérer une liste d'articles, il peut effectuer une première requête pour récupérer les articles, puis, pour chaque article, exécuter une nouvelle requête pour récupérer son ArticleType. Résultat : pour récupérer 10 articles, on se retrouve avec 11 requêtes SQL (1 + 10). Ce problème est bien connu sous le nom de "problème du N+1".

Pour éviter cette problématique, il est important de bien configurer les requêtes SQL de votre ORM afin qu'il récupère d'emblée toutes les dépendances nécessaires. La plupart des ORM proposent un système de "lazy loading" pour charger dynamiquement les dépendances des objets. En revanche, il est également possible d'utiliser le "eager loading", qui charge immédiatement les dépendances des objets au moment où ceux-ci sont récupérés.

En général, il est conseillé de configurer toutes vos dépendances en lazy loading, puis de préciser les dépendances nécessaires lors des requêtes à la base de données. Cette configuration permet de minimiser le nombre de requêtes tout en récupérant uniquement les données nécessaires.

Précautions avec les Clauses LIKE

L'utilisation de l'opérateur LIKE avec un % au début de la chaîne de caractères est inefficace, car elle nécessite une comparaison complète avec chaque ligne de la table. Pour améliorer les performances, il est préférable d'éviter cette pratique ou de l'utiliser avec précaution. De manière générale, pour effectuer des recherches dans les colonnes de vos tables, il peut être plus performant et pertinent d'utiliser un index de type Full Text Search, spécialement conçu pour les recherches basées sur des chaînes de caractères.

Optimisation des requêtes SQL, c'est essentiel !

L'optimisation SQL est une compétence essentielle pour tout développeur cherchant à maintenir des applications performantes et évolutives. En comprenant les mécanismes sous-jacents et en appliquant les meilleures pratiques décrites ci-dessus, vous pourrez améliorer considérablement la vitesse et l'efficacité de vos applications. Qu'il s'agisse de la gestion des index, de la limitation des résultats, de la réduction du nombre de requêtes, ou encore de l'utilisation judicieuse des ORM et des clauses LIKE, chaque aspect de l'optimisation SQL joue un rôle crucial dans la performance globale de vos systèmes. En suivant ces meilleures pratiques, vous pouvez significativement améliorer les performances de votre application et offrir une meilleure expérience utilisateur. Si vous avez des questions ou besoin de plus d'informations, n'hésitez pas à nous contacter !

Comme pour toutes les surcouches ou abstractions en informatique, il est crucial de maîtriser la couche sous-jacente afin de tirer pleinement parti des outils à votre disposition. Aujourd'hui, l'abstraction du SQL se fait via l'ORM, mais dans de nombreux autres concepts, vous réaliserez qu'il est tout aussi important de bien comprendre les différentes couches.