Index SQL Server : Améliorer les performances des bases de données

Lorsque j'ai commencé à travailler avec le serveur SQL, j'ai réalisé que toutes les requêtes lentes n'étaient pas créées de la même manière. Certaines requêtes étaient lentes en raison d'une mauvaise conception, tandis que d'autres étaient simplement inefficaces. Mais une découverte a changé à jamais ma façon d'aborder les performances des bases de données : les index. Imaginez que vous essayiez de trouver un chapitre spécifique dans un énorme manuel sans index ; vous auriez à feuilleter des centaines de pages, ce qui vous ferait perdre un temps précieux. Ce scénario reflète ce qui se passe dans les bases de données sans indexation appropriée.

Dans cet article, j'expliquerai pourquoi les index de SQL Server sont importants, comment ils fonctionnent et comment vous pouvez les exploiter pour maximiser les performances de vos bases de données. Si vous souhaitez approfondir vos compétences SQL dans SQL Server, je vous recommande de suivre nos cours Introduction à SQL Server et Intermédiaire SQL Server pour apprendre à utiliser T-SQL pour la manipulation et l'analyse des données.

Comprendre les index du serveur SQL

Les index de SQL Server vous permettent d'interroger efficacement les tableaux dans les bases de données sans avoir à analyser des ensembles de données entiers. Voici la définition des index du serveur SQL et leur importance dans la gestion des bases de données.

Qu'est-ce qu'un index SQL Server ?

Les index sont des structures de données spécialisées qui permettent à SQL Server de localiser et d'extraire des lignes plus efficacement, ce qui permet de gagner du temps et d'économiser des ressources informatiques. Les index permettent d'organiser les données de manière à accélérer les recherches. Au lieu de parcourir l'ensemble du tableau ligne par ligne (processus connu sous le nom de balayage du tableau), SQL Server peut utiliser l'index pour trouver les tableaux nécessaires beaucoup plus rapidement que s'il devait parcourir chaque entrée du tableau.

Il existe deux types fondamentaux d'index dans le serveur SQL :

• Index en grappe : Un index en grappe détermine l'ordre physique des données dans un tableau. Lorsque vous créez un index en grappe sur un tableau, les lignes sont stockées sur le disque dans l'ordre de la colonne indexée. Ceci est particulièrement utile pour les requêtes sur les intervalles, car cela permet un accès séquentiel efficace aux lignes.

• Index non groupés : Les index non groupés n'affectent pas l'ordre physique des données. Au lieu de cela, ils créent une structure distincte qui contient des pointeurs vers les lignes de données réelles. Les index non groupés sont utiles pour améliorer les performances des requêtes qui filtrent ou trient les colonnes non incluses dans l'index groupé.

Consultez notre tutoriel sur le serveur SQL pour comprendre l'utilisation de base à avancée du serveur SQL dans la gestion des données.

Pourquoi les index sont-ils importants ?

Les index sont importants pour améliorer les performances des requêtes et l'efficacité globale de la base de données. Voici quelques avantages clés de l'utilisation des index :

• Exécution plus rapide des requêtes : Les index réduisent le temps nécessaire à la recherche et à l'extraction des données. Par exemple, si vous recherchez toutes les commandes d'un client spécifique dans un tableau contenant des millions de lignes, un index sur la colonne client permet à SQL Server de localiser ces lignes presque instantanément.

• Utilisation efficace des ressources : Grâce aux index, le serveur SQL peut effectuer des tâches en utilisant moins de ressources de mémoire et d'unité centrale. Cette efficacité se traduit par une amélioration des performances globales du système, en particulier en cas de charge de travail élevée.

• Amélioration du tri et du regroupement : Les index peuvent accélérer les opérations de tri (ORDER BY) et de regroupement (GROUP BY). En conservant les données triées, les index permettent au serveur SQL d'extraire rapidement les résultats sans surcharge de tri supplémentaire.

Considérez ce cas d'utilisation pour les applications d'indexation. Imaginez une grande plateforme de commerce électronique où les utilisateurs recherchent fréquemment des produits par leur nom. En créant un index non groupé sur la colonne du nom du produit, vous pouvez rendre ces recherches presque instantanées, ce qui améliore l'expérience de l'utilisateur et réduit la charge du serveur.

Je vous recommande de consulter le parcours de compétences Fondamentaux du serveur SQL de DatCamp pour apprendre à trier, grouper et joindre des tableaux pour l'analyse de données.

Types d'index SQL Server

Il existe différents types d'index SQL Server, chacun ayant des caractéristiques et des cas d'utilisation uniques. Examinons les types d'index les plus courants.

Index groupés et index non groupés

Les deux principaux types d'index dans SQL Server sont les index en grappe et les index non en grappe.

Index en grappe

Un index en grappe détermine l'ordre physique des tableaux dans un tableau, en triant effectivement le tableau par la ou les colonnes de l'index. Les données ne pouvant être physiquement ordonnées que d'une seule manière, il ne peut y avoir qu'un seul index en grappe par tableau. Un index en grappe est idéal pour les requêtes qui renvoient une plage de valeurs ou qui doivent trier les données en fonction de la colonne indexée. 

Par exemple, un tableau de ventes trié par OrderDate peut bénéficier d'un index en grappe sur la colonne OrderDate. Les requêtes qui récupèrent tous les ordres dans une fourchette de dates s'exécutent beaucoup plus rapidement.

Index non groupé

Un index non groupé crée une structure distincte qui pointe vers les tableaux réels. Un tableau peut avoir plusieurs index non groupés, chacun étant utilisé dans un modèle de requête différent. Les index non groupés contiennent une référence (localisateur de ligne) aux données réelles, qui peut être un identifiant de ligne ou une clé d'index groupé. Ce type d'index est appliqué aux colonnes fréquemment utilisées dans les conditions de recherche ou les jointures (par exemple, EmailAddress, ProductName).

Par exemple, un index non groupé sur la colonne ProductName d'un tableau d'inventaire permet de rechercher plus rapidement des produits spécifiques sans modifier la structure du tableau.

Indices uniques et composites

SQL Server prend également en charge les index uniques et composites, chacun servant à des fins spécifiques.

Index uniques

Un index unique garantit que toutes les valeurs de la (des) colonne(s) indexée(s) sont distinctes. Elle renforce l'intégrité des données en empêchant les valeurs en double et est souvent appliquée aux colonnes naturellement uniques, telles que les adresses électroniques ou les noms d'utilisateur. Par exemple, un index unique sur la colonne Email d'un tableau d'utilisateurs garantit que deux utilisateurs ne peuvent pas avoir la même adresse électronique.

Indices composites

Un index composite couvre plusieurs colonnes, optimisant ainsi les requêtes qui filtrent ou trient sur la base d'une combinaison de ces colonnes. L'ordre des colonnes dans l'index composite est important, car SQL Server traite d'abord la colonne la plus à gauche. Les index composites prennent en charge les requêtes complexes impliquant des conditions multiples. Par exemple, un index composite sur LastName et FirstName dans un tableau de clients accélère les recherches de clients par leur nom complet.

Création et gestion des index du serveur SQL

Nous utilisons le SQL Server Management Studio (SSMS) pour créer un index dans SQL Server. Avant d'aller plus loin, consultez notre blog pour comprendre les différences entre les dialectes SQL Server, PostgreSQL et MySQL. Je vais vous présenter les étapes de la création et de la gestion des index de SQL Server.

Comment créer un index SQL Server

Vous trouverez ci-dessous un guide étape par étape pour la création d'index à l'aide de SQL Server Management Studio (SSMS) et du code SQL.

Création d'un index à l'aide de SSMS

1. Ouvrez l'Explorateur d'objets : Lancez SSMS et connectez-vous à votre instance de serveur SQL. Développez la base de données contenant le tableau pour lequel vous souhaitez créer un index.

Ouvrez l'Explorateur d'objets et naviguez dans le tableau pour créer l'index. Image par l'auteur.

2. Localisez le tableau : Accédez au dossier Tableaux, recherchez votre tableau et développez-le.

Localisez le tableau pour créer l'index et développez-le. Image par l'auteur.

3. Ouvrez le dossier Indexes : Cliquez avec le bouton droit de la souris sur le dossier Indexes et sélectionnez New Index.

Ouvrez le dossier Index et faites un clic droit pour créer un nouvel index. Image par l'auteur.

4. Choisissez le type d'index : Sélectionnez le type d'index que vous souhaitez créer (par exemple, Clustered Columnstore Index ou Non-Clustered Index).
5. Définir les propriétés de l'index : Spécifiez le nom de l'index, puis ajoutez des colonnes à l'index en cliquant sur Ajouter et en sélectionnant les colonnes souhaitées. Configurez des options supplémentaires, telles que l'unicité et l'ordre de tri.

Choisissez le type d'index et définissez les propriétés. Image par l'auteur.

6. Créez l'index : Cliquez sur OK pour créer l'index.

Créez l'index en cliquant sur ok. Image par l'auteur.

Création d'un index à l'aide du code SQL

Vous pouvez également créer des index directement à l'aide de commandes T-SQL. Vous trouverez ci-dessous des exemples de création de différents types d'index à l'aide de requêtes :

-- Create a non-clustered index on the Product column
CREATE NONCLUSTERED INDEX IX_Sales_Product
ON Sales (Product);

-- Create a unique index on the combination of CustomerName and OrderDate
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_CustomerName_OrderDate
ON Sales (CustomerName, OrderDate);

-- Create a clustered index on the OrderDate column
CREATE CLUSTERED INDEX IX_Sales_OrderDate
ON Sales (OrderDate);

Comment supprimer un index SQL Server

Vous pouvez supprimer un index en utilisant la méthode simple. Soyez prudent, car la suppression d'un index peut avoir un impact sur les performances des requêtes.

Suppression d'un index SQL Server à l'aide de SSMS

1. Dans l'Explorateur d' objets, accédez au dossier Indexes situé sous le tableau.
2. Cliquez avec le bouton droit de la souris sur l'index que vous souhaitez supprimer et sélectionnez Supprimer.

Naviguez jusqu'à l'index que vous souhaitez supprimer. Image par l'auteur.

3. Confirmez la suppression dans la boîte de dialogue.

Confirmez la suppression dans la boîte de dialogue et cliquez sur ok. Image par l'auteur.

Suppression d'un index en T-SQL

Vous pouvez utiliser les commandes suivantes pour supprimer les index existants d'un tableau dans SQL Server.

-- Drop a non-clustered index
DROP INDEX IX_Sales_Product ON Sales;

-- Drop a clustered index
DROP INDEX IX_Sales_OrderDate ON Sales;

Comment réorganiser un index SQL Server

La réorganisation d'un index défragmente les pages de niveau feuille de l'index, améliorant ainsi les performances sans le reconstruire entièrement.

Réorganiser un index SQL Server à l'aide de SSMS

1. Cliquez avec le bouton droit de la souris sur l'index, puis cliquez sur Réorganiser tout.

Cliquez avec le bouton droit de la souris sur les index, puis sur Réorganiser tout. Image par l'auteur.

2. Sélectionnez le(s) index à réorganiser et confirmez.

Sélectionnez les index à réorganiser, puis confirmez. Image par l'auteur.

Réorganisation d'un index en T-SQL

Vous pouvez également utiliser les requêtes suivantes pour réorganiser les index dans SQL Server.

-- Reorganize a single index
ALTER INDEX IX_Sales_Product
ON Sales
REORGANIZE;

-- Reorganize all indexes on the table
ALTER INDEX ALL
ON Sales
REORGANIZE;

Comment reconstruire un index SQL Server

La reconstruction d'un index le recrée à partir de zéro, ce qui permet d'éliminer la fragmentation et d'optimiser le stockage.

Reconstruction d'un index à l'aide de SSMS

1. Cliquez avec le bouton droit de la souris sur l'index, puis cliquez sur Reconstruire tout.

Cliquez avec le bouton droit de la souris sur l'index, puis cliquez sur Reconstruire tout. Image par l'auteur

2. Sélectionnez le(s) index à reconstruire et confirmez.

Sélectionnez les index à reconstruire et confirmez. Image par l'auteur

Reconstruction d'un index à l'aide de T-SQL

La requête suivante reconstruit les index du tableau.

-- Rebuild a single index
ALTER INDEX IX_Sales_Product
ON Sales
REBUILD;

-- Rebuild all indexes on the table
ALTER INDEX ALL
ON Sales
REBUILD;

Meilleures pratiques pour la gestion des indices

La maintenance et l'optimisation des index du serveur SQL sont importantes pour garantir des performances optimales de la base de données. Voici quelques conseils pour une gestion efficace de l'index.

• Effectuez une maintenance régulière de l'index : Réorganisez ou reconstruisez régulièrement votre index pour réduire la fragmentation, qui peut dégrader les performances au fil du temps. Utilisez des scripts de maintenance ou des outils tels que les plans de maintenance du serveur SQL pour automatiser ces tâches.

• Analyser l'utilisation de l'index : Utilisez les vues de gestion dynamique (DMV), telles que sys.dm_db_index_usage_stats, pour identifier les index inutilisés ou sous-utilisés. Supprimez les index qui n'offrent pas d'avantages significatifs en termes de performances afin d'éviter toute surcharge inutile.

• Évitez la surindexation : Un trop grand nombre d'index peut ralentir les opérations d'écriture (INSERT, UPDATE, DELETE) car tous les index pertinents doivent être mis à jour. Concentrez-vous sur l'indexation des colonnes fréquemment utilisées dans les requêtes SELECT, les jointures ou les conditions de filtrage.

• Surveillez les niveaux de fragmentation : Utilisez sys.dm_db_index_physical_stats pour identifier les index fortement fragmentés. Réorganisez les index pour une fragmentation inférieure à 30 % et reconstruisez-les pour une fragmentation supérieure à 30 %.

• Tirez parti des indices filtrés : Utilisez des index filtrés pour optimiser les requêtes portant sur un sous-ensemble de données, comme les utilisateurs actifs ou des plages de dates spécifiques.

Vous devez également prêter attention aux pratiques suivantes afin d'éviter les pièges les plus courants lors de la gestion de vos index :

• Surindexation : Évitez de créer des index pour chaque requête ; cela a un impact sur les opérations d'écriture et le stockage.

• Négliger l'entretien : L'absence de reconstruction ou de réorganisation des index entraîne une augmentation de la fragmentation.

• Ignorer l'indexation pour les écritures : Si les index accélèrent les lectures, ils peuvent aussi ralentir considérablement les écritures.

• Ne pas tester les indices composites : Un mauvais ordre des colonnes dans les index composites peut les rendre inefficaces.

Consultez le cursus de compétences SQL Server for Database Administrators de DataCamp pour en savoir plus sur les transactions et la gestion des erreurs dans SQL Server afin d'améliorer les performances des requêtes.

Considérations sur les performances

Les index sont utiles pour optimiser les performances de la base de données, mais ils peuvent s'accompagner de certains compromis. Il est essentiel de comprendre leur impact et de les gérer efficacement pour maintenir un système de base de données performant.

Impact des index sur les performances des requêtes

Les index influencent considérablement les plans d'exécution des requêtes et les performances en réduisant le temps nécessaire à l'extraction des données. Ils permettent à SQL Server de localiser rapidement les tableaux, réduisant ainsi la nécessité de procéder à des analyses complètes et coûteuses du tableau. Les tableaux d'exécution affichent souvent des opérations telles que la recherche d'index au lieu du balayage de tableau en présence d'index efficaces.

Les outils de mesure de la performance de l'indice sont les suivants :

• sys.dm_db_index_usage_stats cursus de l'utilisation des index par les requêtes.

• sys.dm_db_index_operational_stats fournit des mesures opérationnelles telles que les lectures et écritures de pages.

• Le magasin de requêtes analyse les performances des requêtes et identifie les inefficacités causées par des index manquants ou inutilisés.

Par exemple, la requête suivante permet d'obtenir des statistiques sur l'utilisation des index dans les tableaux définis par l'utilisateur, y compris des détails sur les recherches, les balayages, les consultations et les mises à jour, afin d'évaluer leur efficacité et leurs performances.

-- Retrieve index usage statistics for user tables
SELECT 
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName, 
    I.name AS IndexName,
    IXS.user_seeks AS Seeks,
    IXS.user_scans AS Scans,
    IXS.user_lookups AS Lookups,
    IXS.user_updates AS Updates
-- DMV providing index usage stats
FROM 
    sys.dm_db_index_usage_stats IXS         
JOIN 
    sys.indexes I ON I.OBJECT_ID = IXS.OBJECT_ID 
                 AND I.index_id = IXS.index_id
WHERE 
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;

Équilibrer l'utilisation et le stockage des index

Si les index améliorent les performances de la base de données, ils s'accompagnent également de coûts de stockage puisqu'ils consomment de l'espace disque, en particulier pour les grands ensembles de données. Il est important de comprendre comment équilibrer ces facteurs lors de la mise en œuvre des indices. Un plus grand nombre d'index peut ralentir les opérations d'écriture, en particulier dans les environnements où les transactions sont nombreuses. Envisagez les stratégies suivantes pour équilibrer l'utilisation de l'index.

• Ne répertoriez que ce qui est important : Concentrez-vous sur les colonnes d'indexation fréquemment utilisées dans les clauses WHERE, JOIN et ORDER BY. Évitez d'indexer les colonnes rarement interrogées.

• Utilisez des index filtrés : Créez des index pour des sous-ensembles de données afin d'économiser de l'espace et d'optimiser des requêtes spécifiques.

• La compression de l'effet de levier : Activez la compression des données pour réduire l'empreinte de stockage des index.

• Archivez les anciennes données : Déplacez les données rarement consultées vers un tableau d'archivage et réduisez l'indexation du tableau principal.

Les stratégies ci-dessus permettront d'optimiser les performances des requêtes tout en maîtrisant les coûts de stockage et de maintenance.

Résolution des problèmes liés à l'index

S'ils ne sont pas correctement entretenus, les index peuvent se dégrader au fil du temps, ce qui réduit l'efficacité de la base de données. Voyons comment identifier et résoudre ces problèmes.

• Fragmentation : Au fil du temps, les index deviennent fragmentés, ce qui rend la recherche de données inefficace. Les signes de fragmentation sont l'allongement des temps d'exécution des requêtes et l'augmentation des entrées/sorties sur le disque. Pour résoudre ce problème, utilisez sys.dm_db_index_physical_stats pour identifier la fragmentation. Ensuite, réorganisez les index pour les niveaux de fragmentation inférieurs à 30 % et reconstruisez les index pour les niveaux de fragmentation supérieurs à 30 %.

• Statistiques dépassées : Les statistiques périmées conduisent à des plans d'exécution des requêtes sous-optimaux. Vous risquez donc d'avoir des requêtes lentes malgré l'existence d'index appropriés. Pour résoudre ce problème, mettez à jour manuellement les statistiques des index critiques ou activez AUTO_UPDATE_STATISTICS.

• Index inutilisés ou redondants : Certains index restent inutilisés, consomment de l'espace de stockage et ralentissent les opérations d'écriture. Les symptômes de ce problème sont des coûts élevés de maintenance de l'index et une faible utilité des requêtes. Pour résoudre ce problème, identifiez les index inutilisés à l'aide de sys.dm_db_index_usage_stats et supprimez-les si nécessaire.

• Index manquants : L'absence d'index appropriés peut entraîner des balayages du tableau, ce qui ralentit les requêtes. Dans de tels tableaux, les plans d'exécution des requêtes indiquent un balayage de table au lieu d'une recherche d'index. Pour résoudre ce problème, utilisez la DMV d'index manquants sys.dm_db_missing_index_details pour identifier les opportunités et créer les index recommandés en fonction des modèles de requête.

• Chevauchement d'index : Des index multiples avec des structures de colonnes similaires gaspillent des ressources, ce qui entraîne une utilisation accrue du stockage et des opérations de maintenance redondantes. Consolidez toujours les index qui se chevauchent en un seul index bien conçu.

Essayez notre cursus de développeur SQL Server pour en savoir plus sur le dépannage des problèmes d'index dans SQL Server afin d'améliorer l'efficacité de la base de données.

Conclusion

Il est important de maîtriser les index de SQL Server si vous souhaitez améliorer les performances de la base de données en écrivant des requêtes efficaces. Lorsque vous travaillez avec des index, vous devez également comprendre les différents types d'index SQL Server afin de gérer vos index pour une performance optimale de la base de données.

Je vous recommande notre cursus Associate Data Analyst in SQL pour devenir un analyste de données compétent. Notre cours Reporting in SQL vous aidera également à maîtriser la création de rapports et de tableaux de bord complexes pour une présentation efficace des données. Si vous vous préparez à un entretien au cours duquel vous devrez présenter vos compétences en matière de serveur SQL, je vous recommande de consulter notre blog, Top 30 SQL Server Interview Questions, pour vous aider à vous entraîner et à réussir l'entretien.