Dois-je créer des index sur des clés étrangères?

La contrainte de clé étrangère seule ne fournit pas l’index – il faut (et doit) créer.

La création d’une clé étrangère ne crée pas automatiquement un index sur A.B_ID. Il serait donc logique, du sharepoint vue des performances des requêtes, de créer un index distinct sur A.B_ID.

Si vous supprimez des lignes dans B, vous souhaitez certainement que A.B_ID soit indexé. Sinon, Oracle devra effectuer une parsing complète de la table sur A chaque fois que vous supprimez une ligne de B pour vous assurer qu’il n’ya pas d’enregistrements orphelins (selon la version d’Oracle, il peut également y avoir des implications de locking, mais celles-ci sont diminuées). dans les versions Oracle plus récentes).

Juste pour plus d’informations: Oracle ne crée pas automatiquement un index (comme pour les contraintes uniques) car (a) il n’est pas nécessaire d’appliquer la contrainte et (b) dans certains cas, vous n’en avez pas besoin.

La plupart du temps, vous souhaiterez toutefois créer un index (en fait, dans Oracle Apex, il existe un rapport sur les “clés étrangères non indexées”).

Chaque fois que l’application doit pouvoir supprimer une ligne de la table parent ou mettre à jour la valeur PK (ce qui est plus rare), le DML souffrira si aucun index n’existe, car il devra verrouiller la totalité de la table enfant.

Un cas où je choisis généralement de ne pas append d’index est l’endroit où le FK se trouve dans une table de “données statiques” qui définit le domaine d’une colonne (par exemple une table de codes d’état), où les mises à jour directement par l’application. Toutefois, si l’ajout d’un index sur la colonne apporte des avantages aux requêtes importantes de l’application, l’index sera toujours une bonne idée.

SQL Server n’a jamais mis automatiquement d’index sur des colonnes de clé étrangère – consultez l’ excellent article de Kim Tripp sur le contexte et l’histoire de ce mythe urbain.

C’est généralement une bonne idée d’indexer vos colonnes de clé étrangère, donc oui, je vous recommande de vous assurer que chaque colonne FK est sauvegardée par un index; pas nécessairement sur cette seule colonne – peut-être qu’il peut être judicieux de créer un index sur deux ou trois colonnes avec la colonne FK comme premier. Dépend de votre scénario et de vos données.

Pour des raisons de performances, un index doit être créé. Est utilisé dans les opérations de suppression sur la table primaire (pour vérifier que l’enregistrement que vous supprimez n’est pas utilisé) et dans les jointures qui impliquent généralement une clé étrangère. Seules quelques tables (je ne les crée pas dans les journaux) pourraient ne pas avoir besoin de l’index mais probablement, dans ce cas, vous n’avez probablement pas besoin de la contrainte de la clé étrangère.

MAIS

Certaines bases de données créent déjà automatiquement des index sur des clés étrangères. Jet Engine (fichiers Microsoft Access) Firebird MySQL

POUR SÛR

SQL Server Oracle

NE FAIT PAS

Comme pour tout ce qui concerne la performance, cela dépend de nombreux facteurs et il n’y a pas de solution miracle, par exemple dans un environnement très actif, le maintien d’un index peut être inacceptable.

La sélectivité est la plus évidente: si les valeurs de l’index sont fortement dupliquées, cela peut donner de meilleurs résultats en supprimant l’index (si possible) et en autorisant une parsing de la table.