J'ai une table avec dix foreign keys. Combien de coups vais-je prendre en insertion si j'ai des index sur les dix touches?

Voici une list décente d'exemples de quand et quel type d'index utiliser. Je ne pense pas que vous devriez accepter la "loi" et tout indexer. Vous devez définir ce qui sera utilisé dans les jointures de requête et indexer en conséquence

Il y a une pénalité de performance significative sur les insertions car tous les index doivent être mis à jour. En gros, vous devrez écrire un disque pour l'insertion sur la grande table et un peu plus d'un (en moyenne) pour chaque index de la table. Chaque nœud de feuille d'index subira une écriture, et certaines écritures supplémentaires se produiront de time à autre lorsque la feuille et (less souvent) les nœuds parents se splitont.

Chaque écriture de table et d'index entraînera également du trafic de journaux. La pénalité particulièrement désagréable est sur datatables insérées en bloc, car les index actifs sur les tables où vous insérez des données chargées en masse seront mis à jour pour chaque ligne – et ces mises à jour ne sont pas journalisées de manière minimale. Cela va massivement souffler votre E / S (qui sera tous un access random plutôt que de belles écritures en bloc séquentielles) et générera également de grandes quantités de trafic de journaux.

Il n'est pas nécessaire de mettre un index sur les foreign keys qui pointent dans des tables de search avec un petit nombre d'éléments.

La seule façon de répondre à votre question est de tester. Par exemple, si l'une des keys a une cardinalité de 10, elles ne seront probablement pas très utiles. Donc vous avez du travail pour faire des tests. Mais cela a beaucoup à voir avec la taille de votre table, la taille des touches, le niveau d'activité absolu et le mélange d'éléments CRUD. Méfiez-vous de toutes les réponses simples.

MODIFIER:

Si vous n'avez pas de données actuellement parce qu'il s'agit de la design initiale, commencez avec les index évidents et ajoutez-en d'autres selon vos besoins en fonction des tests. Cela n'a aucun sens de les append tous, sauf s'il s'agit d'une database à faible changement. Mais si c'est en lecture seule, il n'y a pas beaucoup de pénalité. (Une autre information que vous n'avez pas fournie.)

Le consensus semble être que toutes les foreign keys doivent avoir des index. Combien de frais généraux vais-je subir sur les encarts si je suis la lettre de la loi?

Il y a deux frais généraux: sur DML sur la table de reference, et DML sur la table référencée.

Une table référencée doit avoir un index, sinon vous ne serez pas en mesure de créer une FOREIGN KEY .

Une table de referencement ne peut pas avoir d'index. Cela rendra les INSERT dans la table de referencement un peu plus lent, et n'affectera pas les INSERT dans une table référencée.

Chaque fois que vous insérez une ligne dans une table de reference, les events suivants se produisent:

1. La ligne est vérifiée par rapport à la FOREIGN KEY comme dans cette requête:

    SELECT TOP 1 NULL FROM referenced ed WHERE ed.pk = @new_fk_value 

   • La ligne est insérée
   • L'index sur la ligne (le cas échéant) est mis à jour.

Les deux premières étapes sont toujours effectuées, et l'étape 1 utilise généralement un index sur la table référencée (encore une fois, vous ne pouvez pas créer une relation FOREIGN KEY sans avoir cet index).

L'étape 1 est la seule surcharge spécifique à une FOREIGN KEY .

La surcharge de l'étape 3 n'est impliquée que par le fait que l'index existe. Ce serait exactement la même chose FOREIGN KEY il n'y avait pas de FOREIGN KEY .

Mais UPDATE et DELETE de la table référencée peuvent être beaucoup plus lents si vous ne définissez pas un index sur la table de reference, surtout si cette dernière est grande.

Chaque fois que vous DELETE de la table référencée, les events suivants se produisent:

1. Les lignes sont vérifiées par rapport à la FOREIGN KEY comme dans cette requête:

    SELECT TOP 1 NULL FROM referencing ing WHERE ing.fk = @old_pk_value 

   • La ligne est supprimée
   • L'index sur la ligne est mis à jour.

Il est facile de voir que cette requête bénéficiera probablement d'un index sur referencing.fk .

Dans le cas contraire, l'optimiseur devra build une HASH TABLE sur toute la table même si vous supprimez un seul logging pour vérifier la contrainte.

La seule façon de connaître l'impact est de tester. La réponse peut varier considérablement selon que votre système a tendance à insert de grandes quantités de données dans une insertion en bloc ou un logging à la fois à partir de l'interface user. Cela dépend aussi beaucoup de la taille des tables et du nombre total d'index. Le test est le seul moyen de savoir avec certitude quels index utiliser. Une règle générale consiste à commencer par indexer les champs keys étrangers et les champs que vous utiliserez dans les clauses where. Mais c'est juste là où commencer à regarder votre système, pas la réponse «soyez tout – fin tout».

Je dirai que j'ai observé que les users ont tendance à être plus tolérants d'un peu plus de time passé sur l'insertion que de passer plus de time à interroger le système. Cela est d'autant plus vrai que les frameworks supérieurs ont tendance à faire plus d'interrogations que d'inputs de données et qu'ils peuvent devenir grincheux et avoir le pouvoir de faire quelque chose à ce sujet s'ils estiment que leur time est gaspillé.

Dans un nouveau système, vous devez générer des loggings de test au volume attendu que le système aura mis en œuvre. Si vous ne le faites pas, vous constaterez que les requêtes (et le design) qui ont fonctionné correctement dans un même banc d'essai peuvent être horribles avec de vrais users faisant plusieurs choses simultanément contre de grandes tables. Ce n'est pas amusant du tout de refactoriser une database où la performance n'a pas été considérée et testée dans la design. Il n'est pas amusant de retirer les changements de production parce que la requête prend plus de time que le paramètre de timeout car le développeur n'a pas testé la vraie volumn (ou dans le cas du nouveau projet, le volumn attendu).

SQL Server dispose d'outils pour vous aider à déterminer les meilleurs index. Utilisez l'assistant d'indexing et les plans d'exécution pour voir où vous avez besoin d'index. Mettez des index sur les champs et des insertions de test pour voir s'il y a un impact négatif. Il n'y a pas une bonne réponse. Il ne restra probablement pas la même réponse pour toute la durée de vie de votre database.

Insérer / mettre à jour / supprimer frappe toujours l'index et y écrit. Select sélectionne parfois l'index à lire, en fonction de l'parsing de l'optimiseur de requête ou de la meilleure estimation. Si vous n'avez pas besoin d'index pour accélérer les lectures (par exemple, si la colonne n'a qu'un faible nombre de valeurs potentielles), éliminez-la.

Si vous avez un milliard de lignes dans une table enfant et que vous souhaitez en supprimer 100 millions parce que vous supprimez une ligne de la table parente où cette ligne est le parent de toutes les 100 millions de lignes enfant, l'index ne ralentissez que l'opération entière, car le système doit également supprimer de l'index, mais n'accélère pas l'opération car le système n'utilisera pas l'index pour accélérer le choix des lignes à supprimer.

Je sais que la performance est un problème critique.

OMI, vous devriez considérer les ramifications de ne pas avoir un index (et donc pas de FK) sur datatables OLTP. Vous pouvez subir des problèmes d'intégrité des données sur un tel système.

Merci à tous pour votre participation.

Sur la base de vos commentaires, je pense que je vais append des index à toutes les foreign keys sauf celles qui pointent vers des tables de search (contenant un petit nombre d'loggings qui ne sont pas susceptibles de changer). Cela réduira de moitié le nombre d'index de key étrangère requirejs (de dix à cinq).

Si quelqu'un a d'autres idées, n'hésitez pas à postr de nouvelles réponses. Il me rest encore quelques votes. 🙂

Les champs vont-ils être utilisés dans la search et le sorting? Si oui, un indice pourrait être une bonne idée. La seule façon de savoir est de tester la mesure et de tester à nouveau

Edit : la table look sera probablement caching mais cela n'aidera pas une requête de search par rapport à la table de reference. Votre tableau de données qui est.