Une clé étrangère crée-t-elle automatiquement un index ?

Question

On m'a dit que si j'utilise une clé étrangère pour deux tables, le serveur SQL créera quelque chose de semblable à un index dans la table enfant. J'ai du mal à croire que cela soit vrai, mais je ne trouve pas grand-chose de spécifique à ce sujet.

Si je vous pose cette question, c'est parce que le temps de réponse d'une instruction de suppression d'une table qui comporte probablement 15 tables connexes est très lent. J'ai demandé à notre responsable de la base de données et il m'a répondu que s'il existe une clé étrangère sur les champs, elle agit comme un index. Quelle est votre expérience à ce sujet ? Devrais-je ajouter des index sur tous les champs de clé étrangère ou bien s'agit-il simplement d'une surcharge inutile ?

Answer 1

Une clé étrangère est une contrainte, une relation entre deux tables, qui n'a rien à voir avec un index en soi.

Mais il est bien connu qu'il est très utile d'indexer toutes les colonnes qui font partie d'une relation de clé étrangère, car par le biais d'une relation de clé étrangère, vous aurez souvent besoin de consulter une table de référence et d'extraire certaines lignes sur la base d'une seule valeur ou d'une plage de valeurs.

Il est donc logique d'indexer toutes les colonnes impliquées dans un FK, mais un FK en soi n'est pas un index.

Consultez l'excellent article de Kimberly Tripp. "Quand SQL Server a-t-il cessé de mettre des index sur les colonnes de clés étrangères ?" .

Answer 2

Wow, les réponses sont toutes sur la carte. Donc le Documentation dit :

Une contrainte FOREIGN KEY est un candidat pour un index car :

• Les modifications apportées aux contraintes PRIMARY KEY sont vérifiées avec les contraintes FOREIGN KEY dans les tables liées.

• Les colonnes de clé étrangère sont souvent utilisées dans les critères de jointure lorsque les données de tables liées sont combinées dans des requêtes en faisant correspondre la ou les colonnes de la contrainte FOREIGN KEY d'une table avec la ou les colonnes de clé primaire ou unique de l'autre table. Un index permet à Microsoft® SQL Server™ 2000 de trouver rapidement les données connexes dans la table à clé étrangère. Cependant, la création de cet index n'est pas une obligation. Les données de deux tables liées peuvent être combinées même si aucune contrainte de clé primaire ou de clé étrangère n'est définie entre les tables, mais une relation de clé étrangère entre deux tables indique que les deux tables ont été optimisées pour être combinées dans une requête qui utilise les clés comme critères.

Il semble donc assez clair (bien que la documentation soit un peu confuse) qu'elle ne crée pas d'index en fait.

Answer 3

Non, il n'y a pas d'index implicite sur les champs de clé étrangère, sinon pourquoi Microsoft dirait-il "Créer un index sur une clé étrangère est souvent utile" . Votre collègue confond peut-être le champ de la clé étrangère dans la table de référence avec la clé primaire dans la table de référence - clés primaires faire créer un index implicite.

Answer 4

Disons que vous avez une grande table appelée commandes, et une petite table appelée clients. Il existe une clé étrangère entre une commande et un client. Maintenant, si vous supprimez un client, Sql Server doit vérifier qu'il n'y a pas de commandes orphelines ; si c'est le cas, il émet une erreur.

Pour vérifier s'il y a des commandes, Sql Server doit chercher dans la grande table des commandes. Maintenant, s'il y a un index, la recherche sera rapide ; s'il n'y en a pas, la recherche sera lente.

Ainsi, dans ce cas, la lenteur de la suppression pourrait s'expliquer par l'absence d'un index. Surtout si Sql Server devait rechercher 15 grandes tables sans index.

P.S. Si la clé étrangère a ON DELETE CASCADE, Sql Server doit toujours chercher dans la table des commandes, mais ensuite supprimer toutes les commandes qui font référence au client supprimé.

Answer 5

Pas à ma connaissance. Une clé étrangère ajoute seulement une contrainte selon laquelle la valeur de la clé enfant doit également être représentée quelque part dans la colonne parent. Elle ne dit pas à la base de données que la clé enfant doit aussi être indexée, mais seulement contrainte.