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.
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.
- 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 ?
Comment fonctionne un index sur une base de données ?
Quand est-il avantageux d'utiliser un index en SQL ?
Comment créer un index en SQL ?
Quels sont les effets de la suppression d'un index ?
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?