Comment fonctionnent les index en base de données

Les fonctionnalités d'indexation en base de données sont très importantes, et vous allez voir que si l'implémentation est souvent complexe, le concept, lui, est très simple et facilement compréhensible !

À noter que ce concept est valable sur des bases SQL et NoSQL, mais nous prendront des exemples SQL pour l'illustration de l'article !

Prenons une requête très simple, disons tous les utilisateurs ayant pour métier le développement web, ce qui donnerait ceci :

SELECT firstname, lastname, email
FROM User
WHERE job = 'web_developer';

Lorsque la base de données va rechercher le résultat de cette requête, le système devra parcourir TOUTES les lignes de la table en question pour trouver toutes les occurrences.

Si notre logiciel est un site de covoiturage par exemple, alors rechercher les utilisateurs par leur métier n'est pas vraiment très courant, cela signifie qu'avec beaucoup d'utilisateurs, la requête pourra être longue à s'exécuter, mais ne tournera qu'une fois de temps en temps.

Maintenant, si notre logiciel est un réseau social professionnel, alors cette requête sera exécutée des milliers de fois par jours, et plus le nombre d'utilisateurs est grand, plus elle pourra potentiellement être bloquante pour notre système...

En résumé : Exécutée 10 000 fois par jour, sur 100 000 utilisateurs, ce sont 1 milliard de lignes parcourues,  juste pour cette requête...

Autant dire qu'en termes de ressource, la consommation de cette simple requête n'est pas à négliger... Mais heureusement, les index sont là pour venir à notre rescousse !

Qu'est-ce qu'un index ?

Ce n'est pas pour rien que le mot "index" est utilisé dans la littérature, car c'est un concept inventé il y a des centaines d'années pour faciliter la recherche d'informations dans les encyclopédies.

Dans un livre, un index permet de retrouver toutes les pages liées à un mot important spécifique.

Dans l'exemple ci-dessous, on peut voir que les pages 21 à 24 parlent du sujet "animated cartoons".

Il est simple de comprendre qu'ici, l'index nous permet de ne pas avoir à parcourir toutes les pages du livre (et tous les mots), à la recherche d'un sujet en particulier.

C'est la même chose dans une base de données !

Chaque index ajouté à notre base de données est en réalité une table supplémentaire très simple contenant chaque valeur à indexer, et un pointeur vers les lignes contenant la valeur en question.

Voici un exemple d'un index pour les professions des utilisateurs :

En reprenant notre calcul précédent, et en admettant que 10% (10 000) des utilisateurs soient développeurs web, et qu'il y ait 50 professions différentes, alors le résultat est le suivant :

Exécutée 10 000 fois par jour, sur 10 000 utilisateurs indexés, ce sont 100 000 000 lignes parcourues,  soit 10 fois plus performant...

Mais alors, les index sont-ils une solution miracle qui peut résoudre tous nos problèmes ? Non, car comme avec toutes choses, il y a des avantages et des inconvénients !

Avantages et inconvénients

Comme dit précédemment, un index est une table supplémentaire qui va contenir une ligne pour chaque donnée indexée (par exemple pour chaque métier différent).

Donc plus le nombre de valeur unique est grand, plus l'index pèsera lourd et sera potentiellement plus lent...

En plus, ces index doivent être mis à jour à chaque fois qu'une donnée liée à l'index est ajoutée, supprimée ou modifiée.

Alors est-ce vraiment un gain de performance ?

La réponse simple est : Un bon index rendra votre base beaucoup plus performante, un mauvais index la rendra plus lente !

La réponse plus détaillée est : Créer un index, c'est faire un compromis ( ou "trade-off"). On échange un peu de performance lors de la mise à jour des données, contre un gros gain lors de la lecture de ces mêmes données.

Si les données sont mises à jour plus souvent qu'elles ne sont lues, alors l'index est mal choisi... Il est d'usage de concevoir de nouveaux index au fur et à mesure de la vie de la base et de l'analyse de la performance des requêtes, pour ne pas se tromper et faire de la mauvaise optimisation prématurée !