SQL Server

Index dans SQL Server : une introduction pour débutants

Découvrez comment les index aident SQL Server à trouver des lignes, ce qui distingue un index cluster des autres et pourquoi tout index a un coût.

Par JaviPublié 12 min de lectureDébutant

Un index est une structure de base de données qui aide SQL Server à localiser des lignes à partir de colonnes choisies. Sans chemin d’accès adapté, le moteur peut devoir examiner beaucoup de lignes pour répondre à une requête. Un index peut réduire ce travail pour certaines requêtes, mais il utilise de l’espace et doit être entretenu lors des changements de données. C’est un outil utile, pas une correction automatique de toute requête lente.

Cette introduction utilise dbo.Customers et dbo.Orders de la base synthétique JaviBuildsLab. Elle présente le vocabulaire de base, montre comment créer et examiner un index simple et décrit les compromis à comprendre avant d’en ajouter un. Pour revoir les clés, consultez clés primaires et étrangères. Cette leçon évite volontairement les détails avancés comme la fragmentation, les statistiques internes, les recherches par clé, les DMV d’index manquants et l’analyse des plans.

Rôle d’un index

Imaginez un livre muni d’un index à la fin. Pour trouver un thème, vous repérez son entrée et allez aux pages indiquées au lieu de lire le livre entier. Un index de base de données évoque cette idée : il organise des valeurs clés et des informations qui aident SQL Server à retrouver les lignes correspondantes. L’analogie a ses limites. L’index d’un livre est fixe et consulté par une personne ; SQL Server choisit si un index convient à une requête et entretient les structures lorsque les lignes changent.

Par exemple, une requête qui recherche les commandes d’un client utilise CustomerId comme critère :

Requête SQL: Rôle d’un indexT-SQL
SELECT OrderId, CustomerId, OrderDate, Status, Amount
FROM dbo.Orders
WHERE CustomerId = 11
ORDER BY OrderDate DESC;
T-SQL4 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Un index dont la première clé est CustomerId peut aider SQL Server à trouver ces lignes sans examiner chaque commande. Son utilité dépend de la taille de la table, de la requête, de la distribution des valeurs et des autres chemins d’accès. Sur une toute petite table, parcourir les lignes peut être plus rapide que naviguer dans l’index. L’idée importante est qu’un index offre une autre façon de trouver les données ; il ne garantit pas une amélioration.

Index cluster et non cluster

Un index cluster organise les lignes de la table selon sa clé cluster. Une table ne peut en avoir qu’un, car ses lignes ne peuvent avoir qu’une seule organisation de ce type. La clé est souvent choisie en fonction des accès importants, mais aucune règle universelle ne dicte une colonne précise. « Cluster » ne signifie pas que les résultats des requêtes apparaîtront toujours dans cet ordre ; ajoutez ORDER BY lorsque l’ordre compte.

Un index non cluster est une structure séparée avec ses colonnes clés et une référence aux lignes de base. Une table peut avoir plusieurs index non cluster, chacun adapté à une manière utile de trouver les lignes. Par exemple, une recherche de client et une recherche par plage de dates peuvent avoir besoin de clés différentes. Chaque index supplémentaire demande aussi de l’espace et de l’entretien.

Certains index non cluster peuvent inclure des colonnes supplémentaires. Elles sont stockées dans les entrées de l’index mais ne font pas partie de l’ordre des clés. Elles peuvent fournir les données nécessaires à une requête, mais bien les choisir exige de comprendre la charge et les coûts de stockage. Considérez-les comme une option de conception ultérieure, pas comme une liste à remplir par défaut.

Créer un index de base

Dans la base de pratique, cet exemple crée un index non cluster sur dbo.Orders.CustomerId :

Requête SQL: Créer un index de baseT-SQL
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
T-SQL2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Le nom IX_Orders_CustomerId suit une convention qui indique la table et la clé. ON dbo.Orders désigne la table et (CustomerId) la clé de l’index. C’est un point de départ simple lié à la relation clients-commandes étudiée précédemment. Dans une vraie base, vérifiez d’abord qu’un index équivalent n’existe pas et discutez toute modification de schéma avec le responsable. Relancer CREATE INDEX avec un nom déjà présent provoque une erreur, sans remplacer l’objet silencieusement.

Ne créez pas d’index uniquement parce qu’une colonne apparaît dans une requête. Examinez les filtres et les jointures, le volume de données, la fréquence d’exécution et les index existants. La conception d’index concerne toute la charge de travail, pas une instruction isolée.

Examiner les index dans SSMS

Dans l’Explorateur d’objets, développez la base, Tables, puis dbo.Orders. Développez le dossier Indexes de la table pour voir ses index. Celui de la clé primaire peut apparaître avec les autres index non cluster. Faites un clic droit sur un index ou ouvrez les propriétés de la table pour examiner sa définition et ses colonnes clés. Les libellés exacts varient selon la version de SSMS, mais l’arborescence de l’objet donne accès aux index.

Le concepteur de table ou les propriétés de l’objet peuvent aussi aider à comprendre les contraintes et les index présents. Avant d’en ajouter un, comparez ses clés aux définitions existantes. Un nom proche ne garantit pas une définition équivalente ; vérifiez la table et les colonnes réelles. Notez l’index créé dans la base de pratique afin de le retrouver facilement pendant l’exercice.

Une clé primaire et un index sont liés, mais différents

Une clé primaire est une contrainte qui impose l’unicité et une identité non NULL pour les lignes. Un index est une structure qui aide à accéder aux lignes. SQL Server crée souvent un index pour mettre en œuvre une clé primaire, selon la définition de la clé et les index déjà présents. La contrainte exprime la règle ; l’index est une structure qui peut la prendre en charge et aider les requêtes.

Un index unique peut imposer l’unicité, mais ne déclare pas à lui seul que la colonne est la clé primaire de la table. De même, une clé étrangère exprime une relation, sans garantir automatiquement un index sur la colonne qui la référence. Consultez les clés primaires et étrangères pour les règles d’intégrité. Gardez ces notions distinctes lors des discussions de conception.

Chaque index a un coût

Les index utilisent de l’espace disque et de la mémoire lorsqu’ils sont sollicités. Ils ajoutent aussi du travail aux modifications. Lorsqu’une ligne est insérée, supprimée ou qu’une valeur indexée change, SQL Server doit parfois mettre à jour les structures concernées. Les écritures peuvent donc devenir plus coûteuses à mesure que les index s’accumulent. Un index peu utile en lecture impose malgré tout de l’espace et de l’entretien.

Trop d’index peuvent ralentir les changements et compliquer la maintenance du schéma. Une clé mal choisie ne sert pas forcément les requêtes importantes. Un index sélectif peut aider une recherche ciblée, alors qu’une colonne avec peu de valeurs fréquentes ne convient pas à toutes les requêtes. Ces choix dépendent de la charge ; aucune quantité universelle d’index ne convient à chaque table.

Les index ne sont pas magiques. Ils ne corrigent ni une jointure incorrecte, ni un filtre manquant, ni une demande de colonnes inutiles. Ils ne dispensent pas de tester avec des données représentatives. Commencez par une logique claire, puis étudiez l’optimisation si une charge réelle montre que la recherche des lignes pose problème.

Quand étudier l’ajout d’un index ?

Un index mérite d’être envisagé lorsqu’une requête importante filtre, joint ou trie régulièrement sur une colonne, et que le volume de lignes peut justifier ce chemin d’accès. Examinez d’abord les index présents. Demandez-vous quelle charge utilisera le nouvel index et à quelle fréquence les données changent. Testez la proposition dans un environnement sûr avec un volume représentatif ; vérifiez le gain pour la requête visée et l’absence de régression des écritures ou d’autres requêtes.

Dans cet exercice, l’index CustomerId illustre comment un index pourrait aider le filtre d’une requête clients-commandes. Il ne garantit pas que SQL Server le choisira dans tous les environnements. L’optimiseur évalue les options ; les décisions de production doivent s’appuyer sur des mesures de la charge. Le parcours d’optimisation SQL Server montre comment passer d’un symptôme à des preuves puis à une validation. Pour un exemple de production, consultez les effets des index sur les JOIN, UPDATE et DELETE.

Exercice et suite

Examinez dans SSMS les index de dbo.Orders. Identifiez la clé primaire et les index non cluster existants. Dans une copie jetable de JaviBuildsLab, exécutez une fois l’exemple CREATE NONCLUSTERED INDEX, actualisez le dossier Indexes et examinez la colonne clé. Comparez son rôle avec la requête filtrée sur CustomerId. Ne supposez ni que sa création accélère toutes les requêtes, ni qu’un index d’exercice doit être déployé automatiquement ailleurs.

  1. La requête cherche
  2. L’index trouve des clés
  3. Espace et écritures coûtent
Les index ont des coûts

Les clés protègent l’identité et les relations ; les index offrent des chemins d’accès avec des compromis. La leçon de synthèse relie ces notions aux activités de production : requêtes en cours, blocages, surveillance, Query Store et optimisation : Des bases de SQL à l’optimisation des performances de SQL Server. Vous pouvez revoir la feuille de route SQL Server Foundations.

Vérifiez ce que vous avez retenu

2 questions rapides

Question 1 sur 2Pourquoi une table peut-elle avoir un index ?
Question 2 sur 2Quel est un coût possible des index ?

Fondamentaux de SQL Server

Leçon 24 sur 25

Continuer vers la leçon suivante

Des bases de SQL à l’optimisation des performances de SQL Server

Continuer vers la leçon 25

Étiquettes

  • SQL Server
  • Fondamentaux SQL
  • SSMS
  • T-SQL