Index sur clés étrangères

Bonjour,
je suis une formation (purement pour loisir) et j'ai un petit projet pour la terminer. je voudrais votre éclaircissement sur l'utilisation et la création des index.
Lors d'un TD que nous avions fait en formation j'ai vu que dans la base de donnée les index était créé sur toutes les clés étrangères de la base. J'ai un peu compris l'utilité des index mais j'ai des doutes si il faut les créer pour une table entière ou bien sur les clés étrangères etc...
Pouvez-vous me donner vos conseils ?
merci
Bonne journée

Bonjour,

Oui il faut indexer les clés étrangères, car lors de la suppression de lignes parentes d'une table, les tables ayant des clés étrangères sur cette table doivent être parcourues pour vérifier la présence de lignes "enfant".

Il y a plusieurs cas de figure

• la clef étrangère référence une table ne possédant que quelques valeurs distinctes (table des codes sexe ou des codes civilité par exemple)
  ==> en ce cas, indexer la colonne FK n'a aucun intérêt, l'index n'est pas discriminant, il ne sera pas utilisé pour les recherches et ralentira les mises à jour
• la clef étrangère référence une table possédant un grand nombre de valeurs distinctes et la colonne fait l'objet de recherches dans la table où elle est clef étrangère
  ==> indexer la clef étrangère permet d'accélérer les recherches
• la clef étrangère participe à la clef primaire. C'est systématiquement le cas quand on utilise l'identification relative (ex : ligne de facture identifiée relativement à la facture).
  C'est également le cas pour les tables associatives (tables issues d'une association du MCD).
  ==> en ce cas, il ne faut pas créer d'index supplémentaire, puisque l'index de la clef primaire inclut déjà la colonne clef étrangère

@Vanagreg : la gestion des clefs étrangères n'impose pas systématiquement la présence d'index du coté de la table enfant, c'est du coté de la table parent que la colonne référencée doit être indexée (et faire l'objet d'une contrainte PK ou UNIQUE). L'exigence d'index sur la colonne clef étrangère est une particularité de certains SGBD, pas une règle.

Bonjour,
Merci beaucoup pour vos explications détaillées et surtout merci de me consacrer votre temps.
Bonne journée

L'indexation systématique des clefs étrangères n'a aucun intérêt....

J'ai écrit il y a quelques temps un article dans Server Fault à ce sujet :
https://softwareengineering.stackexc.../436832#436832

En voici une traduction...

Premièrement, il n'y a pas d'indexation automatique de FOREIGN KEY dans RDBMS sauf MySQL, ce qui est un comportement stupide.

Deuxièmement, dans certains cas, l'indexation d'un FK crée un index redondant inclus, en particulier lorsque :

1. la table est une table associative
2. la table provient d'un design hérité

Troisièmement, indexer systématiquement FK n'est pas une bonne pratique, sauf si toutes vos requêtes ne sont qu'une jointure sans qu'aucune autre colonne ne soit utilisée dans une autre partie de la requête pour la table des enfants.

Quelques explications...

POINT 2

Premier exemple, table associative :

Code:

---

1 2 3

CREATE TABLE Commandes (ord_id int PRIMARY KEY, ...) CREATE TABLE Produit (prd_id int PRIMARY KEY, ...) CREATE TABLE order_details (ord_id int NOT NULL REFERENCES Orders (ord_id), prd_id int NOT NULL REFERENCES Product (prd_id), PRIMARY KEY (ord_id, prd_id), ...)

---

Ajouter un index sur ord_id pour la table order_details est stupide, car la PRIMARY KEY a déjà un index (ord_id, prd_id) qui peut être utilisé à la place du simple FK (ord_id). indice.

Deuxième exemple, table héritée :

Code:

---

1 2	CREATE TABLE véhicules (vhc_id int PRIMARY KEY, ...) CREATE TABLE vehicle_cars (vhc_id int PRIMARY KEY REFERENCES vehicles (vhc_id , ...)

---

Ajouter un index sur vhc_id est encore plus stupide, car la PRIMARY KEY a déjà un index (vh_idc) qui est strictement le même.

POINT 3

Code:

---

1 2	CREATE TABLE clients (ctm_id int PRIMARY KEY, ...) CREATE TABLE Commandes (ord_id int PRIMARY KEY, ctm_id int NOT NULL REFERENCES clients (ctm_id), ...)

---

L'ajout d'un index sur la colonne ctm_id dans la table des commandes ne sera utilisé que dans deux cas :

l'index lui-même est suffisant pour récupérer toutes les données de la jointure
il y a peu de lignes renvoyées de la table des commandes

Exemple de sous-point 2.1 :

Code:

---

1 2 3 4 5

SELECT ctm_name, COUNT(*) FROM clients AS c JOIN Orders AS o ON c.ctm_id = o.ctm_id GROUP BY ctm_name ;

---

Exemple de sous-point 2.2 :

Code:

---

1 2 3 4 5

SELECT * FROM clients AS c JOIN Orders AS o ON c.ctm_id = o.ctm_id WHERE ctm_id = 123 ;

---

Dans toute autre requête, il y a une grande "chance" que l'index FK ne soit pas utilisé car le coût d'utilisation de l'index en mode "recherche", puis le jointure de l'index à la table pour récupérer toutes les autres colonnes qui ne sont pas dans l'index, sera bien plus qu'un simple balayage de la table.

DONC, INDEXER SYTÉMATIQUEMENT LA CLÉ ÉTRANGERE EST SURTOUT STUPIDE !

Rappelons que les index sont très coûteux en termes de transactions lorsque les données sont modifiées (INSERTs, UPDATEs, DELETEs, MERGEs, TRUNCATEs...). Alors, ne créez pas d'index inutiles !

Pour être clair, le meilleur choix pour chaque index est d'avoir un index COUVRANT, ce qui signifie que le seul et unique index lui-même est suffisant pour toute la requête, y compris WHERE, ON (à partir des JOIN), HAVING, GROUP BY, ORDER BY et SELECT. Ceci étant grandement facilité par l'introduction de la clause INCLUDE pour les index dans certains SGBDR (SQL Server depuis la version 2008 et PostGreSQL depuis la version 11 en 2018)

Bien entendu, le choix de l'indexation doit résulter de l'exploitation de la base de données et non d'un dogme !

Certains SGBDR (Microsoft SQL Server depuis la version 2008) proposent systématiquement un diagnostic complet des index à créer, et il est très rare que des index concernant uniquement la colonne de clé étrangère soient signalés par le système de diagnostic... A titre d'exemple, cette requête pour Microsoft SQL Server :

Code:

---

SELECT * FROM sys.dm_db_missing_index_details ;

---

Donnez la liste de tous les index nécessaires pour améliorer les performances des requêtes réellement exécutées depuis le démarrage de l'instance SQL Server et quelques détails supplémentaires, comme le gain potentiel, si l'index est créé...

A +

Bonsoir,
je vous remercie pour votre réponse qui est un vrais cours très claire pour nous débutants.
Bonne soirée et merci