Français
Menu

Qu'est-ce que Index (index de base de données) ?

Structure de données d'une base de données qui permet de trouver rapidement, à partir d'une valeur d'une colonne donnée d'une table, l'emplacement des lignes qui contiennent cette valeur. Les arbres B+ sont couramment utilisés. Un index accélère les recherches, mais il doit être tenu à jour à chaque insertion, modification ou suppression de ligne, et il occupe de l'espace de stockage.

Nom japonais: インデックス

« Index (index de base de données) » apparaît dans 4 questions d'entraînement réparties sur 1 thèmes, dont Bases des bases de données.

Domaine: Bases des bases de données

Fonctionnement d'un index en arbre B+

Les index des bases de données relationnelles utilisent couramment des arbres B+. Un arbre B+ est une structure arborescente dont chaque nœud a de nombreuses branches et où toutes les feuilles sont au même nombre de niveaux de la racine ; les feuilles contiennent les valeurs de la colonne et l'emplacement des lignes dans l'ordre des valeurs, et sont chaînées les unes aux autres dans cet ordre. Si chaque nœud se ramifie en 100, 3 niveaux permettent de distinguer 1 million d'entrées ; même dans une table de 1 million de lignes, on atteint donc la ligne voulue en quelques lectures. Comme les feuilles sont chaînées dans l'ordre des valeurs, l'index sert non seulement aux recherches avec =, mais aussi aux recherches par plage comme BETWEEN et au tri avec ORDER BY.

Quand un index aide, et quand il n'aide pas

Un index est efficace sur une colonne très sélective, où une condition réduit les lignes à une petite fraction de l'ensemble. Une colonne dont toutes les valeurs sont différentes, comme un numéro de membre, est idéale ; mais sur une colonne à 2 valeurs seulement, comme le sexe, environ la moitié des lignes correspondent, et un parcours complet qui lit la table dans l'ordre est souvent plus rapide que l'extraction des lignes une à une via l'index. Un index ordinaire peut aussi être inutilisable pour des conditions qui appliquent une fonction à la colonne ou pour des recherches par suffixe comme LIKE '%eau'.

Le coût pour les écritures

Un index est une structure de données tenue à jour séparément de la table. À chaque insertion, modification ou suppression de ligne, tous les index concernés doivent aussi être mis à jour ; plus il y a d'index, plus les écritures ralentissent et plus l'espace de stockage utilisé augmente. L'approche de base consiste donc à créer des index sur les colonnes souvent utilisées comme conditions de recherche ou de jointure, et à supprimer les index inutilisés. De nombreux SGBD créent automatiquement un index sur les colonnes de clé primaire et UNIQUE.

S'entraîner : questions sur Index (index de base de données)

On a créé un index en arbre B+ sur la colonne NumMembre d'une table Membre de 1 million de lignes. Quelle combinaison associe correctement l'effet que l'on peut généralement attendre et son coût ?

  1. ALes recherches comme WHERE NumMembre = 12345 deviennent plus rapides, mais les écritures ralentissent parce que l'index doit aussi être mis à jour à chaque ajout ou modification de ligne
  2. BLes recherches sur toutes les colonnes deviennent plus rapides, sans aucun coût
  3. CLes lignes de la table sont triées par NumMembre, si bien que toutes les recherches deviennent plus rapides, et l'espace de stockage nécessaire est divisé par deux
Afficher la réponse et l'explication

Réponse : A. Les recherches comme WHERE NumMembre = 12345 deviennent plus rapides, mais les écritures ralentissent parce que l'index doit aussi être mis à jour à chaque ajout ou modification de ligne

Un index est une structure de données qui permet de trouver rapidement, à partir d'une valeur d'une colonne donnée, l'emplacement des lignes qui contiennent cette valeur ; les bases de données relationnelles utilisent couramment des arbres B+. Sans index, trouver la ligne WHERE NumMembre = 12345 exige d'examiner dans l'ordre 1 million de lignes (parcours complet de la table, ou full table scan), alors qu'avec un arbre B+ on trouve la ligne en suivant seulement quelques niveaux de la racine à une feuille. Si chaque nœud se ramifie en 100, par exemple, 100 × 100 × 100 = 1 million ; environ 3 niveaux suffisent donc. Comme les feuilles d'un arbre B+ sont chaînées dans l'ordre des valeurs, l'index peut aussi servir pour des recherches par plage comme BETWEEN et pour le tri avec ORDER BY. En revanche, l'index doit être mis à jour à chaque insertion, modification ou suppression de ligne, si bien que les écritures ralentissent, et il faut de l'espace de stockage pour l'index. Les recherches sur des colonnes sans index ne deviennent pas plus rapides.

Pour une table Membre de 1 million de lignes, on envisage de créer un index en arbre B+ sur chacune des colonnes NumMembre (toutes différentes), Prefecture (47 valeurs) et Sexe (2 valeurs, réparties à peu près à moitié). Lors d'une recherche qui précise dans la clause WHERE la valeur d'une seule colonne, sur quelle colonne un index sera-t-il probablement le moins efficace ?

  1. APrefecture
  2. BSexe
  3. CNumMembre
Afficher la réponse et l'explication

Réponse : B. Sexe

Un index est efficace lorsque les lignes qui satisfont la condition se réduisent à une petite fraction de l'ensemble. Chaque NumMembre est différent ; une seule ligne correspond donc, et l'index y mène directement. Prefecture a 47 valeurs ; si les membres sont répartis uniformément, une valeur réduit la recherche à environ 1 million ÷ 47 ≈ 21 000 lignes (environ 2 % du total). Sexe a 2 valeurs réparties à peu près à moitié ; l'une ou l'autre valeur correspond donc à environ 500 000 lignes (environ 50 %). Extraire une par une autant de lignes en passant par un index est souvent plus lent qu'un parcours complet qui lit la table du début à la fin ; l'optimiseur du SGBD peut donc choisir un plan d'exécution qui n'utilise pas l'index. Ainsi, plus une colonne a de valeurs distinctes et plus une condition sur elle réduit fortement le nombre de lignes (plus elle est sélective), plus un index sur cette colonne est efficace.

Questions fréquentes

Faut-il créer un index sur toutes les colonnes ?

Non. Plus il y a d'index, plus il faut de travail de maintenance à chaque insertion ou modification de ligne, et plus l'espace de stockage utilisé augmente. Créez des index sur les colonnes souvent utilisées comme conditions de recherche ou de jointure et qui réduisent suffisamment le nombre de lignes.

Pourquoi un index n'est-il pas utilisé alors qu'il a été créé ?

Parmi les raisons possibles : l'optimiseur juge que tant de lignes correspondent qu'un parcours complet de la table est plus rapide, ou bien la condition ne peut pas exploiter l'ordre de l'index, par exemple parce qu'elle applique une fonction à la colonne ou qu'il s'agit d'une recherche LIKE par suffixe.

« Index (index de base de données) » dans les explications

Questions mentionnant « Index (index de base de données) » (4)

  1. On a créé un index en arbre B+ sur la colonne NumMembre d'une table Membre de 1 million de lignes. Quelle combinaison associe correctement l'effet que l'on peut généralement attendre et son coût ?
  2. Pour une table Membre de 1 million de lignes, on envisage de créer un index en arbre B+ sur chacune des colonnes NumMembre (toutes différentes), Prefecture (47 valeurs) et Sexe (2 valeurs, réparties à peu près à moitié). Lors d'une recherche qui précise dans la clause WHERE la valeur d'une seule colonne, sur quelle colonne un index sera-t-il probablement le moins efficace ?
  3. Les instructions SQL se répartissent en langage de définition de données (DDL), langage de manipulation de données (DML) et langage de contrôle de données (DCL). Laquelle relève du DDL ?
  4. Quelle proposition décrit correctement une vue (view) dans une base de données relationnelle ?

Termes associés

Base de données relationnelle Vue (view) SQL Transaction Clé étrangère (foreign key) Clé primaire Diagramme E-R (entité-association) Niveau d'isolation Normalisation NoSQL

Retour au glossaire