• 20 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 03/09/2019

Peaufinez votre schéma avec les index

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Comment pensez-vous que se passe une recherche dans une table ? Eh bien, par défaut, le SGBD parcourt chaque ligne de la table à la recherche des lignes correspondant à vos critères.

Oula, ça doit être long si ma table contient des centaines de milliers de lignes !?

C'est pas faux ! Mais il y a un moyen d'arranger les choses en mettant en place des index.

Mais les index ont aussi d'autres utilités, comme ordonner les lignes d'une table ou imposer une contrainte d'unicité. C'est ce que je vais vous détailler maintenant.

Qu'est-ce qu'un index ?

Associé à une table, un index est une structure gérée automatiquement par le SGBD qui permet de pointer plus « directement » les lignes de la table correspondant à un critère.

Par « plus directement », il faut comprendre que la recherche ne se fait plus en parcourant toutes les lignes de la table, mais en parcourant l'index qui contient les liens vers les lignes correspondant au critère.

Ce fonctionnement est similaire à ce que vous faites quand vous recherchez une information dans un livre technique par exemple. Vous ne feuilletez pas tout le livre, mais vous allez plutôt consulter l'index, vous y recherchez le mot clé qui correspond à ce que vous cherchez et vous obtenez ainsi la liste des pages contenant ce mot.

Un même index peut porter sur une ou plusieurs colonnes à la fois. Il est également possible de créer plusieurs index sur une même table.

En plus de servir de référencement, un index peut avoir des caractéristiques supplémentaires :

  • unique : l'index ajoute une contrainte supplémentaire empêchant de créer plusieurs lignes ayant la même combinaison de valeurs dans les colonnes faisant partie de cet index.

  • clustered : les lignes de la table sont triées dans l'ordre imposé par cet index. Agissant au niveau du stockage des lignes de la table, vous ne pouvez donc créer qu'un seul index clustered par table.

Les index créés par défaut

Sans que vous le sachiez, vous avez déjà utilisé les index, par exemple en créant les clés primaires.

En général, les SGBD créent automatiquement des index pour les colonnes de clés primaire et certains le font pour les colonnes clés étrangères aussi.

Pour la clé primaire, l'index impose une unicité de valeur : il n'est pas possible de créer 2 lignes qui auront exactement les mêmes valeurs dans les colonnes constituant la clé primaire.

Pour la clé étrangère, même sans index, la contrainte suivante est imposée : les valeurs renseignées dans les colonnes de clés étrangères doivent exister dans la clé primaire de la table référencée (c'est-à-dire la table située de l'autre côté de la relation).

Ajoutez des index

Pour ajouter un index sur une table dans SQL Power Architect, il faut procéder ainsi :

  1. Sélectionnez la table concernée.

  2. Cliquez sur l'icône de création d'un nouvel index .

  3. Une fenêtre s'ouvre, vous permettant de définir les propriétés de l'index (nom, caractéristiques) et de sélectionner les colonnes sur lesquelles il porte. S'il s'agit d'un index clustered, vous pouvez définir le sens de tri de chaque colonne (croissant/décroissant) ainsi que l'ordre dans lequel les colonnes interviennent dans le tri (grâce aux boutons ).

  4. Validez votre configuration en cliquant sur le bouton OK.

Dans l'exemple ci-dessus, un index unique et clustered, nommé commune_idx  a été ajouté à la table commune :

  • Les lignes de la table seront ordonnées (index clustered) par :

    1. le code_postal, par ordre croissant (ASCENDING)

    2. le nom, par ordre croissant (ASCENDING)

  • Les couples de valeurs (code_postal;nom) doivent être uniques (index unique) : il ne sera pas possible d'avoir deux communes avec le même nom et le même code postal.

Les colonnes concernées par un index unique sont signalées par la notation [ AK ].

La table « commune » contient un index unique
La table « commune » contient un index unique

Les index c'est bien, mais...

Si les index permettent d'accélérer les recherches, ils ont un impact néfaste sur les opérations d'écriture (ajout, modification et suppression de lignes).

En effet, lorsque vous apportez un changement dans une colonne indexée, le SGBD doit alors modifier le ou les index associés à cette colonne. Et cela prend du temps (enfin tout est relatif, cela dépend de la taille des index).

Si j'avais un conseil à vous donner, ce serait de ne poser des index que pour ajouter des contraintes (contrainte d'unicité par exemple) et d'ajouter des index supplémentaires, si besoin, au cas par cas, pendant le développement.

Il vous sera toujours possible de créer des index après coup, même sur une base de données en production pour adresser des problèmes particuliers.

Exemple de certificat de réussite
Exemple de certificat de réussite