Les clés étrangères améliorent-elles les performances des requêtes ?

Question

Supposons que j'ai deux tables, Products et ProductCategories. Les deux tables ont une relation sur CategoryId. Et voici la requête.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

Lorsque je crée le plan d'exécution, la table ProductCategories effectue une recherche d'index en grappe, ce qui est conforme aux attentes. Mais pour la table Products, elle effectue un scan de l'index en cluster, ce qui me fait douter. Pourquoi FK n'aide-t-il pas à améliorer les performances des requêtes ?

Je dois donc créer un index sur Products.CategoryId. Lorsque je crée à nouveau le plan d'exécution, les deux tables effectuent une recherche d'index. Et le coût estimé des sous-arbres est considérablement réduit.

Mes questions sont les suivantes :

1. Outre le fait que FK aide à résoudre les contraintes relationnelles, a-t-il une autre utilité ? Améliore-t-elle les performances des requêtes ?

2. Dois-je créer un index sur toutes les colonnes FK (comme Products.CategoryId) dans toutes les tables ?

Answer 1

Les clés étrangères sont un outil d'intégrité référentielle, et non un outil de performance. Au moins dans SQL Server, la création d'un FK ne crée pas d'index associé, et vous devriez créer des index sur tous les champs FK pour améliorer les temps de recherche.

Answer 2

Les clés étrangères peuvent améliorer (et nuire) aux performances

1. Comme indiqué ici : Les clés étrangères améliorent les performances

2. Vous devez toujours créer des index sur les colonnes FK pour réduire les recherches. Le serveur SQL ne le fait pas automatiquement.

Modifier

Le lien semble maintenant être mort (bravo à Chris pour l'avoir remarqué) Le tableau suivant montre l'essentiel des raisons pour lesquelles les clés étrangères peuvent améliorer (ou nuire) aux performances.

Les clés étrangères peuvent-elles améliorer les performances ?

La contrainte de clé étrangère améliore les performances au moment de la lecture des données mais, en même temps, elle ralentit les performances au moment de l'insertion / la modification / la suppression de données.

En cas de lecture de la requête, l'optimiseur peut utiliser les contraintes de clés étrangères pour créer des plans de requête plus efficaces, car les contraintes sont des règles pré-déclarées. Cela implique généralement de sauter partie du plan de requête car, par exemple, l'optimiseur peut constater que qu'en raison d'une contrainte de clé étrangère, il n'est pas nécessaire d'exécuter cette partie particulière du plan.

Answer 3

Une clé étrangère est un concept du SGBD permettant de garantir l'intégrité de la base de données.

Toute implication/amélioration des performances sera spécifique à la technologie de la base de données utilisée et est secondaire par rapport à l'objectif d'une clé étrangère.

Une bonne pratique dans SQL Server est de s'assurer que toutes les clés étrangères ont au moins un index non clusterisé sur elles.

J'espère que cela vous éclaircira les choses, mais n'hésitez pas à demander plus de détails.

Answer 4

Vous pouvez l'utiliser pour rendre une requête plus efficace. Il vous permet de restructurer les requêtes dans SQL Server afin d'utiliser une jointure externe au lieu d'une jointure interne, ce qui supprime la nécessité pour les serveurs SQL de vérifier si la colonne est nulle. Vous n'avez pas besoin de mettre ce qualificatif, car la relation de clé étrangère le fait déjà pour vous.

Alors ça :

select p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
from Products p
inner join ProductCategories c on
p.CategoryId = c.CategoryId
where c.CategoryId = 1;

Devient ceci :

 SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
 FROM ProductCategories c 
 LEFT OUTER JOIN Products P ON
 c.CategoryId = p.CategoryId 
 WHERE c.CategoryId = 1;

Cela ne se traduira pas nécessairement par des performances énormes dans les petites requêtes, mais lorsque les tables deviennent grandes, cela peut être plus efficace.

Answer 5

Votre meilleur pari en matière de performance est d'utiliser des index sur les champs que vous utilisez fréquemment. Si vous utilisez SQL Server, vous pouvez utiliser le profileur pour profiler une base de données spécifique et prendre le fichier qui sort et utiliser l'assistant de réglage pour recevoir des recommandations sur l'endroit où placer vos index. J'aime aussi utiliser le profileur pour éliminer les procédures stockées qui s'exécutent depuis longtemps. J'ai une liste des dix pires contrevenants que je publie chaque semaine, pour garder les gens honnêtes :D.