Comment créer, maintenir, reconstruire ou supprimer des index pour optimiser les requêtes.
Un index, c’est quoi ?
Un index est créé sur un champ d’une table afin d’améliorer les requêtes utilisant ce champ dans des prédicats.
Un index est une structure de données Il permet, à SQL Server (et aux systèmes de gestion de bases de données, en général) de retrouver rapidement des enregistrements spécifiques, à l’instar de la table des matières d’un livre permettant, au lecteur, de retrouver facilement la page ou le mot-clé recherché.
Les indexes sont créés sur des colonnes de tables ou de vues.
Dans la vie de tous les jours, on utilise des indexes sans le savoir, un code postal, un type de magasin (les boulangeries de mon village – vous avez mis un « index » sur les boulangeries dans votre tête (adresse, repère dans la ville…) – vous y allez directement sans faire toutes les boutiques une à une …
Pour illustrer l’utilité des index avec la métaphore du livre, il est plus rapide d’aller directement à la page recherchée que de les parcourir toutes :
Comment identifier le besoin de créer un index?
Lors de l’exécution d’une requête ou en utilisant le PROFILER ou la visualisation du plan d’exécution, un besoin d’index peut être identifié sur une requête longue.
Un index est une structure séparée dans la base de données, construite en utilisant l’instruction CREATE INDEX
Il nécessite son propre espace sur le disque et détient une copie des données de la table.
Cela signifie qu’un index est une redondance. Créer un index ne modifie pas les données de la table.
Cela crée une nouvelle structure de données faisant référence à la table.
Il occupe de la place, il est redondant et il fait référence aux informations réelles stockées ailleurs.
Quels sont les types d’index ?
CLUSTER
1 SEUL index cluster – création automatique lors d’une création de clé primaire
N’occupe pas de place supplémentaire
Création possible (contraintes : pas de varchar, première création)
La création d’une contrainte PRIMARY KEY entraîne la création automatique d’un index cluster unique sur la ou les colonnes pour autant qu’il n’existe pas encore d’index cluster dans la table ou qu’un index non-cluster unique n’ait pas été défini explicitement. La colonne clé primaire ne peut pas contenir de valeurs NULL.
NON CLUSTER
Plusieurs index non cluster peuvent être créés (999 par table)– création à la demande
Occupe de la place supplémentaire
Lorsque vous créez une contrainte UNIQUE, un index non-cluster unique est créé pour appliquer par défaut une contrainte UNIQUE.
ATTENTION : La création d’index est toujours un COMPROMIS (vitesse gagnée::place occupée, vitesse perdue) et impact sur d’autres requêtes.
Comment créer un index
Créer un index non cluster sur une table ou une vue :
CREATE INDEX i1 ON t1 (col1);
Créer un index cluster sur une table et utiliser 3 champs de la table
CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);
Créer un index non cluster avec une contrainte unique sur 3 colonnes et spécifier l’ordre de chaque colonne
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);
Utilisation des index
Dans les clauses WHERE ORDER BY GROUP BY
ATTENTION : l’encapsulation d’un index (fonction ou Isnull) annule son usage
La fragmentation des index
Avec les suppressions, les créations et les modifications des éléments de la table, il y a des « trous » dans la table des index, les index se fragmentent. Dans notre image de la table des matières d’un livre, c’est un peu comme si les pages se détachaient et étaient éparpillées dans la pièce. Il devient alors difficile de les consulter et on perd le bénéfice d’une telle table.
Pour palier à cela, il faut donc « Défragmenter » les index (reconstituer en quelque sorte notre table des matières).
Comment défragmenter les index
Plusieurs solutions sous SSMS
- Passer un script de défragmentation globale
- Planifier une tache de maintenance avec l’AGENT.
- Lancer une défragmentation de l’index au niveau de la table