Comprendre l'Impact des Index en SQL

Les requêtes SQL peuvent être inefficaces sur des tables volumineuses.

Cela entraîne des temps de réponse lents et une charge accrue sur les systèmes.

L’utilisation judicieuse des index en SQL permet d’améliorer considérablement la performance des requêtes.

Table de matière

Fonctionnement des Index SQL Améliorer la Performance des Requêtes Créer un Index avec SQL Server Suppression d'un Index SQL FAQ Conclusion

Formation Le langage SQL : Le Guide Complet

Appréhendez les fondamentaux du langage SQL pour savoir assimiler ses fonctions standards.

Les indexes sont des structures de données qui permettent d’accélérer les recherches et les tris dans une base de données. Ils fonctionnent comme des repères, permettant à la base de données de localiser rapidement les enregistrements sans devoir parcourir l’intégralité de la table. Lorsqu’ils sont bien utilisés, les index améliorent significativement les performances des requêtes, notamment sur des tables volumineuses. Cependant, trop d’index peuvent ralentir les opérations de mise à jour et d’insertion. Il est donc essentiel de les utiliser judicieusement.

Fonctionnement des Index SQL

Table Clients :

La table contient des colonnes telles que CODE_CLIENT, NOM, et PRENOM.
Dans cet exemple, l’index est appliqué sur la colonne NOM.

Index sur le champ NOM :

Un index est une structure de données qui trie les valeurs d’une colonne (ici, NOM) de manière ordonnée. Cela permet de retrouver rapidement les enregistrements dans une table.
Dans le schéma, les valeurs Drissi, Harabazan, Veloso, et Zetor sont triées dans l’index.
Chaque valeur de l’index est associée à un ROWID (identifiant de ligne), qui pointe directement vers l’emplacement de l’enregistrement dans le bloc de données.

Requête SQL :

La requête SELECT nom, prenom FROM clients WHERE nom=’Harabazan’; utilise l’index pour trouver rapidement la valeur Harabazan.
L’index permet de ne pas parcourir toute la table mais d’accéder directement à la ligne correspondante via le ROWID .

Améliorer la Performance des Requêtes

Un index améliore les performances des requêtes, en particulier celles avec des clauses WHERE ou des jointures.
Il évite un scan complet de table (Full Table Scan), ce qui est essentiel pour des tables volumineuses.

Question : Quand Utiliser un Index ?

Sur des tables avec beaucoup de données, où les recherches sur une ou plusieurs colonnes sont fréquentes.
Sur les colonnes souvent utilisées dans les clauses WHERE pour filtrer les résultats.

Syntaxe pour Créer un Index :

Voici la syntaxe que tu as fournie pour créer un index :

				
					
 CREATE [unique] INDEX nom_index
ON nom_table(nom_champ [ASC/DESC], ...);

Unique :Cet attribut rend l’index unique, c’est-à-dire qu’il n’acceptera pas de valeurs dupliquées dans la colonne indexée.
ASC/DESC :Permet de spécifier l’ordre de tri, soit ascendant (ASC) soit descendant (DESC).

Créer un Index avec SQL Server

Pour suivre ces étapes et observer l’intérêt de l’utilisation des index dans SQL Server Management Studio (SSMS), voici le processus à suivre. L’objectif est de comparer les performances de la requête avant et après la création de l’index.

Étape 1 : Création de la table TEST_PERF

				
					
 CREATE TABLE TEST_PERF
(
num numeric(10),
nom varchar(50)
);

Étape 2 : Insertion de données en masse

Ensuite, nous allons insérer 100 000 enregistrements dans la table TEST_PERF. Ce script boucle 100 000 fois et insère une valeur dans la table :

				
					
 DECLARE @cpt INT;
SET @cpt = 1;
WHILE(@cpt < 100001)
BEGIN
INSERT INTO TEST_PERF VALUES (@cpt, 'Test index LANGAGE SQL');
SET @cpt = @cpt + 1;
END;

Étape 3 : Vérification du nombre total d’enregistrements

Pour vérifier que les 100 000 enregistrements ont bien été insérés, exécute la requête suivante :

				
					
 SELECT COUNT(*) FROM TEST_PERF;

Cela te montrera qu’il y a bien 100 000 enregistrements dans la table.

Étape 4 : Requête de recherche sans index

Avant de créer l’index, tu peux exécuter la requête suivante pour chercher un numéro spécifique (ici 100000) et observer le coût de l’opération (dans le plan d’exécution estimé de SSMS) :

				
					
 SELECT num FROM TEST_PERF WHERE num = 100000;

Observation avant indexation :

Sans index, la base de données effectue un « Full Table Scan » pour trouver l’enregistrement, ce qui peut être coûteux en termes de performance si la table est volumineuse. Cela signifie que l’intégralité de la table est parcourue pour trouver la donnée recherchée. Et lorsque tu consultes le plan d’exécution , tu constateras que le coût de l’opérateur estimé est égal à 0.536245 (100%) . Ce coût montre la lourdeur de l’opération sans indexation, surtout lorsque le volume de données augmente.

Étape 5 : Création de l’index

Maintenant, nous allons créer un index sur la colonne num de la table TEST_PERF :

				
					
 CREATE INDEX idx_num ON TEST_PERF(num);

Étape 6 : Requête de recherche avec index

Après avoir créé l’index, exécute à nouveau la même requête pour observer l’amélioration des performances :

				
					
 SELECT num FROM TEST_PERF WHERE num = 100000;

Observation après indexation :

Cette fois-ci, l’index permettra d’accéder directement à l’enregistrement correspondant à num = 100000 sans effectuer un scan complet de la table. Cela signifie que le Full Table Scan est évité, et à la place, la base de données utilise un Index Seek pour localiser la donnée.

Dans le plan d’exécution estimé , tu devrais voir que l’opérateur utilisé est un Index Seek au lieu d’un Table Scan , ce qui réduit considérablement le coût d’exécution. Lorsque tu vérifieras ce plan d’exécution, tu constateras que le coût d’opérateur estimé est égal à 0.0032831 (100%) , ce qui démontre une nette amélioration en termes de performance par rapport à l’absence d’index.

Étape 7 : Comparaison des performances

Vérifie le plan d’exécution (Estimated Execution Plan) pour chaque requête avant et après la création de l’index. Tu constateras que l’utilisation d’un index réduit significativement le coût d’opérateur estimé, particulièrement pour des tables volumineuses.

Suppression d'un Index SQL

Si tu souhaites revenir à l’état initial sans index, tu peux supprimer l’index avec la commande suivante :

				
					
 DROP INDEX idx_num ON TEST_PERF;

Formez-vous gratuitement avec Alphorm !

Maîtrisez les compétences clés en IT grâce à nos formations gratuites et accélérez votre carrière dès aujourd'hui.

FAQ

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

Un index en SQL est une structure de données qui améliore la vitesse des opérations de récupération des données dans une base de données. En agissant comme un guide, il permet d’accéder rapidement aux données requises sans parcourir l’ensemble de la table. L’index trie les valeurs d’une colonne de manière ordonnée, facilitant ainsi l’exécution des requêtes, notamment sur des tables volumineuses.

Comment fonctionne un index sur une base de données ?

Un index fonctionne en triant les valeurs d’une colonne spécifique et en associant chaque valeur à un identifiant de ligne (ROWID). Cela permet à la base de données de localiser rapidement l’enregistrement correspondant à une requête sans effectuer un scan complet de la table. Par exemple, un index sur la colonne NOM d’une table clients permettra de retrouver rapidement un enregistrement spécifique grâce à cet identifiant.

Quand est-il avantageux d'utiliser un index en SQL ?

Il est avantageux d’utiliser un index en SQL lorsque vous avez des tables volumineuses et que vous effectuez fréquemment des recherches sur une ou plusieurs colonnes. Les index sont particulièrement utiles pour les requêtes avec des clauses WHERE ou des jointures, car ils évitent les scans complets de table, améliorant ainsi considérablement les performances des requêtes.

Comment créer un index en SQL ?

Pour créer un index en SQL, vous utilisez la commande CREATE INDEX suivie du nom de l’index, du nom de la table et de la ou des colonnes à indexer. Par exemple, CREATE INDEX idx_nom ON clients(nom). Vous pouvez également spécifier un ordre de tri avec ASC ou DESC selon vos besoins. Un index unique peut être créé pour empêcher les doublons dans une colonne.

Quels sont les effets de la suppression d'un index ?

La suppression d’un index en SQL peut conduire à une dégradation des performances des requêtes qui dépendaient de cet index pour un accès rapide aux données. Sans index, la base de données doit effectuer un scan complet de la table pour exécuter certaines requêtes, ce qui peut être coûteux en termes de temps et de ressources, surtout pour des tables volumineuses.

Conclusion

Les index jouent un rôle crucial dans l’optimisation des performances des bases de données SQL. Comment envisagez-vous d’implémenter les index pour maximiser l’efficacité de vos requêtes?

Comprendre l’Impact des Index en SQL