IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Index sur clés étrangères


Sujet :

Langage SQL

  1. #1
    Membre du Club
    Homme Profil pro
    Autre
    Inscrit en
    mars 2021
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Mayenne (Pays de la Loire)

    Informations professionnelles :
    Activité : Autre

    Informations forums :
    Inscription : mars 2021
    Messages : 95
    Points : 63
    Points
    63
    Par défaut 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

  2. #2
    Membre expérimenté
    Homme Profil pro
    Développeur Oracle
    Inscrit en
    décembre 2019
    Messages
    1 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : décembre 2019
    Messages : 1 013
    Points : 1 690
    Points
    1 690
    Par défaut
    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".

  3. #3
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    8 976
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 8 976
    Points : 33 563
    Points
    33 563
    Billets dans le blog
    3
    Par défaut
    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.

  4. #4
    Membre du Club
    Homme Profil pro
    Autre
    Inscrit en
    mars 2021
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Mayenne (Pays de la Loire)

    Informations professionnelles :
    Activité : Autre

    Informations forums :
    Inscription : mars 2021
    Messages : 95
    Points : 63
    Points
    63
    Par défaut
    Bonjour,
    Merci beaucoup pour vos explications détaillées et surtout merci de me consacrer votre temps.
    Bonne journée

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    21 292
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 21 292
    Points : 50 996
    Points
    50 996
    Billets dans le blog
    1
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Membre du Club
    Homme Profil pro
    Autre
    Inscrit en
    mars 2021
    Messages
    95
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Mayenne (Pays de la Loire)

    Informations professionnelles :
    Activité : Autre

    Informations forums :
    Inscription : mars 2021
    Messages : 95
    Points : 63
    Points
    63
    Par défaut
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Souci sur clés étrangères
    Par Invité dans le forum Langage SQL
    Réponses: 8
    Dernier message: 19/03/2012, 10h02
  2. index sur clé étrangère
    Par alassanediakite dans le forum Développement
    Réponses: 5
    Dernier message: 27/02/2012, 10h07
  3. [phpMyAdmin] Lien sur clés étrangères
    Par gescolino dans le forum EDI, CMS, Outils, Scripts et API
    Réponses: 1
    Dernier message: 09/03/2011, 12h25
  4. Index et clés étrangères
    Par ouadie99 dans le forum Administration
    Réponses: 3
    Dernier message: 01/12/2008, 19h49
  5. Réponses: 2
    Dernier message: 28/09/2007, 18h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo