SQL - plusieurs-à-plusieurs de la clé primaire de la table

Question

Cette question se pose après la lecture d'un commentaire à cette question:

http://stackoverflow.com/questions/2190089/database-design/2190101

Lorsque vous créez un plusieurs-à-plusieurs table, faut-il créer une clé primaire composite sur les deux colonnes de clé étrangère, ou de créer un auto-incrément de substitution "ID" de la clé primaire, et vient de mettre indices sur vos deux FK colonnes (et peut-être une contrainte unique)? Quelles sont les implications sur les performances de l'insertion de nouveaux enregistrements/ré-indexation dans chaque cas?

En gros, ceci:

PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)

vs ce:

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)

Le commentateur dit:

ce qui rend les deux Id, le PK signifie l' le tableau est physiquement triés sur le disque dans cet ordre. Donc, si nous insérer (Part1/Device1), (Part1/Usb2), (Part2/Device3), puis (Partie 1/Device3) la base de données devra rompre l' table de part et d'insérer le dernier entre les entrées 2 et 3. Pour de nombreux enregistrements, cela devient très problématique car elle implique un brassage des centaines, des milliers, voire des millions d'enregistrements chaque fois que l'on est ajoutée. En revanche, un autoincrementing PK permet à la nouvelle des records pour être ajouté à la fin.

La raison pour laquelle je suis demander c'est parce que j'ai toujours été enclin à faire la clé primaire composite avec aucune substitution auto-incrémentée, mais je ne suis pas sûr si la clé de substitution est effectivement plus performant.

Answer 1

Avec un simple de deux colonnes à plusieurs pour la cartographie, je ne vois pas de réel avantage à avoir une clé de substitution. Avoir une clé primaire sur (col1,col2) est garanti unique (en supposant que votre col1 et col2 des valeurs dans les tables de référence sont uniques) et un index séparé sur (col2,col1) va attraper les cas où l'ordre inverse serait une exécution plus rapide. La mère porteuse est un gaspillage de l'espace.

Vous n'aurez pas besoin index sur les colonnes individuelles depuis la table ne devrait jamais être utilisée pour joindre les deux tables référencées ensemble.

Ce commentaire n'est pas la peine de les électrons qu'elle utilise, à mon avis. On dirait que l'auteur pense que le tableau est stocké dans un tableau plutôt qu'une extrêmement haute performance équilibrée multi-way structure de l'arbre. Pour commencer, il n'est jamais nécessaire de stocker ou de les obtenir à la table triée, juste l'index. Et l'index ne sera pas stocké de manière séquentielle, ça va être stockées de manière efficace pour être en mesure de récupérer de manière séquentielle.

En outre, la grande majorité des tables de base de données sont en lecture beaucoup plus souvent qu'à l'écrit. Qui fait tout ce que vous faites sur l'sélectionnez le côté beaucoup plus important que n'importe quoi sur l'insert côté.

Answer 2

Pas de clé de substitution est nécessaire pour lier les tables.

Un PK (col1, col2) et d'un index unique sur (col2, col1) est tout ce que vous devez

À moins d'utiliser un ORM qui ne peuvent pas faire face et dicte votre DB design pour vous...

Edit: j'ai répondu la même chose ici: SQL: avez-vous besoin d'un auto incrément de clé primaire pour les Nombreuses tables?

Answer 3

Un différentiel de clé primaire peut être nécessaire si la table est référencée. Il y a peut être plus de détails dans le plusieurs-à-plusieurs tableau, qui doit être tiré vers le haut à partir d'une autre table à l'aide supplémentaire de la clé primaire.

par exemple

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)
Other Details

Il est facile de tirer les "Autres Détails", à l'aide de PartDevice.ID de la FK. Ainsi, l'utilisation de l'accroissement de la clé primaire est nécessaire.

Answer 4

Le plus court et le plus direct, je peux répondre à votre question, c'est-à-dire qu'il y aura un impact sur les performances si les deux tables de liaison n'ont pas séquentielle des clés primaires. Comme vous l'avez dit/cité, l'indice de la table de lien sera fragmenté, ou le SGBD va travailler plus fort pour insérer des enregistrements si la table de lien n'a pas sa propre séquentielle de la clé primaire. C'est la raison pour la plupart des gens mettent un séquentiellement l'incrémentation de la clé primaire sur la table des liens.