Indexation SQL : comment les index accélèrent la recherche
Lorsqu’une application cherche une commande dans une table volumineuse, le moteur peut devoir examiner de nombreuses lignes. Un index adapté lui fournit un chemin d’accès plus direct et peut réduire le travail nécessaire pour répondre aux requêtes.
Dans SQL Server, Azure SQL Database, Azure SQL Managed Instance et la base de données SQL dans Microsoft Fabric, l’optimisation repose sur un équilibre. L’indexation améliore certaines recherches, mais chaque index ajoute du stockage et du travail lors des modifications de données.
Une structure ordonnée plutôt qu’un balayage complet
Pour comprendre le principe, imaginez l’index à la fin d’un livre : il associe un terme à une page, au lieu d’obliger à lire chaque page. Un index rowstore organise de façon comparable les valeurs d’une ou plusieurs colonnes et les localisateurs des lignes correspondantes.
Selon la documentation Microsoft sur l’architecture des index, les index rowstore s’appuient généralement sur une arborescence B+. Le moteur peut alors parcourir cette structure pour trouver des valeurs, mais il choisit un balayage de table si celui-ci convient mieux à la requête.
Le choix du moteur dépend de la requête
L’existence d’un index ne garantit donc pas son utilisation. Pour une petite table ou un filtre qui retourne une grande part des lignes, lire directement les données peut coûter moins cher que parcourir l’index puis récupérer chaque ligne.
Une équipe qui observe une recherche lente gagne à examiner le plan d’exécution réel dans SQL Server Management Studio. Il montre les opérations retenues par l’optimiseur et aide à vérifier si l’index répond au besoin, plutôt que d’en créer un au hasard.
Choisir le type d’index selon les colonnes et la charge
Une fois le parcours des données compris, le choix du stockage devient déterminant. Les colonnes sollicitées, la fréquence des écritures et le type d’analyse orientent la conception de l’index.
Rowstore, columnstore et mémoire optimisée
Selon Microsoft, les index rowstore conviennent aux recherches ciblées et aux traitements transactionnels courants. Les index columnstore organisent les données par colonnes et sont conçus pour analyser efficacement de grands volumes, notamment dans des usages analytiques.
Les tables optimisées en mémoire proposent, elles, des index de hachage et des index non clusterisés adaptés à leur mode de stockage. Le hachage favorise les recherches d’égalité sur l’ensemble des colonnes de clé ; un index non clusterisé convient mieux aux recherches par plage.
Le tableau distingue les grandes familles à envisager, sans remplacer l’analyse des requêtes réelles. L’optimiseur reste libre de sélectionner un autre plan s’il estime qu’il sera plus rapide.
| Type d’index | Organisation | Usage courant |
|---|---|---|
| Rowstore clusterisé | Lignes ordonnées selon la clé | Accès aux lignes et parcours ordonnés |
| Rowstore non clusterisé | Clés et localisateurs de lignes | Recherche ciblée sur des colonnes |
| Columnstore | Données regroupées par colonne | Analyses de grands ensembles |
| Hachage en mémoire | Compartiments et chaînes d’entrées | Recherche d’égalité sur la clé complète |
Des clés courtes et ordonnées selon les filtres
Pour un index composite, l’ordre des colonnes compte. Si les requêtes filtrent d’abord sur la date, puis sur le statut, placer ces colonnes dans l’ordre correspondant peut rendre l’index plus pertinent que l’ordre inverse.
Selon la documentation Microsoft sur les index, une clé étroite limite le stockage et les coûts de maintenance. Une colonne utilisée uniquement pour afficher un résultat peut souvent être ajoutée comme colonne incluse, plutôt que placée dans la clé.
Une vérification utile consiste à rapprocher les colonnes de la clé des prédicats de recherche et de jointure réellement employés. Cette lecture prépare aussi le choix entre un index complet et un index filtré.
Concevoir des index utiles sans ralentir les écritures
Le choix d’un index prend tout son sens quand il répond à une requête fréquente. À l’inverse, des index redondants peuvent ralentir les insertions, mises à jour et suppressions, car le moteur doit aussi les maintenir.
Repérer les colonnes à indexer
Selon Microsoft, les colonnes employées dans les prédicats de recherche et les jointures constituent de bons candidats, sous réserve de leur distribution. Un index sur une colonne contenant très peu de valeurs distinctes peut apporter peu de sélectivité et ne pas accélérer la recherche.
Pour cadrer la conception, une équipe peut examiner les éléments suivants avant de créer un nouvel index :
Critères de sélection des colonnes :
- Fréquence des requêtes utilisant la colonne dans un filtre
- Rôle de la colonne dans les jointures et les tris
- Nombre de valeurs distinctes et répartition des données
- Effet attendu sur les écritures et l’espace de stockage
Si une requête ne concerne qu’un sous-ensemble stable, un index filtré peut limiter les lignes stockées. Par exemple, indexer seulement les enregistrements actifs peut convenir lorsque les recherches portent régulièrement sur cette catégorie, sans indexer toute la table.
Éviter la sur-indexation et mesurer les effets
Un index couvrant peut contenir les colonnes nécessaires à une requête et éviter un retour vers la table. Mais multiplier les colonnes incluses augmente son volume ; le bénéfice en lecture doit donc être comparé au coût de stockage et de maintenance.
Le Magasin des requêtes et les plans d’exécution aident à repérer les requêtes coûteuses et les index effectivement utilisés. Avant d’ajouter une structure, il est également utile de vérifier si un index existant peut être ajusté pour couvrir le besoin.
Le tableau ci-dessous relie des symptômes fréquents à des pistes de vérification. Il s’agit de points de diagnostic, et non d’une promesse de gain automatique.
| Observation | Piste d’analyse | Risque à surveiller |
|---|---|---|
| Recherche lente sur un filtre récurrent | Examiner la clé et le plan d’exécution | Colonne peu sélective |
| Lecture de nombreuses lignes inutiles | Évaluer un index filtré | Filtre trop large ou peu réutilisé |
| Nombreux retours vers la table | Étudier des colonnes incluses | Index trop volumineux |
| Écritures ralenties | Mesurer l’usage des index existants | Structures redondantes |
Adapter l’indexation aux analyses et aux données ciblées
Après les requêtes transactionnelles, les besoins changent lorsque la base sert surtout à analyser de grands jeux de données. Les index columnstore et les index filtrés répondent à des situations différentes, qu’il faut distinguer.
Columnstore pour les analyses volumineuses
Un index columnstore stocke principalement les données par colonnes, ce qui permet de traiter seulement les colonnes utiles à certaines analyses. Les insertions peuvent d’abord passer par un stockage intermédiaire avant leur compression dans des groupes de lignes.
Selon Microsoft, cette organisation favorise les analyses de grands volumes, mais elle n’est pas automatiquement le meilleur choix pour des recherches transactionnelles ponctuelles. Une table peut combiner certains types d’index afin de servir des usages distincts, au prix d’un stockage et d’une maintenance supplémentaires.
Index filtrés pour un sous-ensemble bien défini
Un index filtré ne conserve qu’une partie des lignes répondant à une condition précise. Il peut être adapté aux colonnes comportant beaucoup de valeurs nulles ou aux catégories régulièrement recherchées, à condition que le filtre de la requête corresponde à celui de l’index.
Pour comparer les options, une équipe peut suivre ces étapes de manière contrôlée :
Étapes de validation des index :
- Identifier une requête fréquente et son plan initial
- Vérifier la distribution des valeurs concernées
- Créer ou modifier un seul index pertinent
- Comparer lectures, durée et coût des écritures
- Conserver l’index seulement si les mesures confirment son intérêt
Cette démarche évite de confondre la présence d’un index avec une optimisation effective. Elle permet aussi de réviser les choix lorsque l’application, les requêtes ou la distribution des données évoluent.
Les outils de visualisation peuvent aider à expliquer les plans à l’équipe, mais les décisions doivent rester fondées sur les mesures de la base. Une démonstration vidéo complète cette exploration des stratégies d’indexation.
Source : Microsoft, « Guide de l’architecture et de la conception d’index », documentation SQL Server ; Microsoft, « Index columnstore : vue d’ensemble », documentation SQL Server ; Microsoft, « CREATE INDEX (Transact-SQL) », documentation SQL Server.