Dans les coulisses de la performance : où et comment les bases de données stockent-elles leurs index ?

Si une base de données est une bibliothèque, alors les données sont les livres sur les étagères, et les index sont le catalogue intelligent qui vous permet de trouver le bon livre en quelques secondes sans parcourir chaque étagère. Tout le monde sait que les index accélèrent les recherches. Mais vous êtes-vous déjà demandé où ces index vivent physiquement et comment ils sont structurés sous le capot ? Comprendre cela est la clé d'une véritable optimisation.

Laissons tomber les abstractions et regardons à l'intérieur.

Le principe fondamental : un index est une structure de données séparée

Il est crucial de comprendre dès le départ : un index n'est pas stocké « avec » les données des lignes de la table. C'est une structure indépendante, mais étroitement liée. Lorsque vous créez un index, le SGBD crée et maintient un objet spécial, le plus souvent basé sur des arbres équilibrés (B-tree, B+tree).

Alors, où est-il sur le disque ? Il existe plusieurs options :

1. Dans le même tablespace que la table elle-même. C'est l'approche par défaut la plus courante (par exemple, dans PostgreSQL, MySQL InnoDB). Les données d'une table et ses index sont différents ensembles de pages (blocs) dans les mêmes fichiers. Pratique pour la gestion, mais peut entraîner une contention d'E/S entre les opérations de lecture/écriture des données et des index.
2. Dans un tablespace séparé ou sur un disque séparé. C'est une technique d'optimisation avancée. Par exemple, vous pouvez placer les index sur un SSD rapide et les données moins fréquemment consultées sur un HDD plus lent. Ou répartir la charge d'E/S. C'est possible dans Oracle, SQL Server, PostgreSQL.

Le cœur de l'index : la structure B+Tree en détail

Presque tous les SGBD relationnels utilisent un B+tree. Visualisons son anatomie :

• Nœuds racine et non-feuilles : Contiennent des clés de navigation (par exemple, les valeurs de la colonne indexée) et des pointeurs vers les nœuds enfants. C'est la « table des matières » du catalogue.
• Nœuds feuilles : C'est la partie la plus importante. Ils stockent les valeurs de clé indexées et des pointeurs (ou les données de ligne elles-mêmes, dans le cas d'un index clusterisé).

Sur le disque physique, tous ces nœuds de l'arbre sont stockés dans des pages ou blocs — des fragments de mémoire de taille fixe (généralement 4 à 16 Ko). Les pages sont liées entre elles pour former la structure de l'arbre.

Cas particuliers et autres structures

1. Index Hash : Utilisés pour les recherches exactes (=). Ce sont des tables de hachage où la clé est la valeur de hachage de la colonne et la valeur est un pointeur vers la ligne. Stockés dans leur propre segment mémoire/disque. Très rapides mais inutiles pour les requêtes de plage.
2. Index Bitmap : Idéaux pour les colonnes à faible cardinalité (genre, statut). Ce sont des tableaux de bitmaps. Stockés dans un format compressé, économisant de l'espace. Chaque bitmap correspond à une valeur unique.
3. Index inversés : Le fondement de la recherche plein texte (FTS). Ils stockent des mots (tokens) et une liste de documents/lignes où ils apparaissent. Ce sont des structures spécialisées, souvent gérées par des moteurs séparés (comme Lucene dans Elasticsearch).

Pourquoi ces connaissances pratiques sont-elles importantes ?

1. Performance des requêtes : Un index mal conçu (sur une colonne à faible cardinalité) occupera de l'espace disque sans apporter de bénéfices.
2. Planification du placement du stockage : Dans les systèmes à forte charge, vous pouvez placer les index et les données sur différents disques physiques pour réduire la contention d'E/S.
3. Maintenance : Les index se fragmentent avec des mises à jour fréquentes. Des opérations comme REINDEX ou REORGANIZE les reconstruisent, réordonnant physiquement les pages pour une lecture séquentielle plus rapide.
4. Surveillance : Vous pouvez interroger les vues système (comme pg_stat_all_indexes dans PG ou sys.indexes dans SQL Server) pour voir la taille de l'index sur le disque et ses statistiques d'utilisation.

Conclusion : Les index ne sont pas de la magie ; ce sont des structures de données concrètes, physiquement existantes. Leur efficacité dépend non seulement de la logique (quels champs indexer) mais aussi du placement physique et de la mécanique interne. Une compréhension approfondie de ces mécanismes sépare un bon développeur d'un véritable ingénieur de base de données.

Avez-vous déjà été confronté à des tâches impliquant le placement physique d'index ou la lutte contre la fragmentation ? Partagez votre expérience dans les commentaires !

#database #optimization #performance #indexes #sql #postgresql #mysql #oracle #administration #backend