Dois-je concevoir une table avec une clé primaire de type varchar ou int?

Lors du choix de la clé primaire en général, vous choisissez la clé cluster. Eux deux sont souvent confondus, mais vous devez comprendre la différence.

Clés primaires sont logiques affaires éléments. La clé primaire est utilisée par votre application pour identifier une entité, et la discussion sur les clés primaires est en grande partie que d'utiliser les clefs naturelles ou clé de substitution. Les liens aller dans beaucoup plus de détails, mais l'idée de base est que les touches sont dérivées d'une entité existante propriété comme ssn ou phone number, alors que les clés de substitution n'ont pas de sens en ce qui concerne l'entreprise de l'entité, comme id ou rowid et ils sont généralement de type IDENTITY ou une sorte d'uuid. Mon opinion personnelle est que les clés de substitution sont de qualité supérieure à des clés naturelles, et le choix doit être toujours les valeurs d'identité locale seulement les applications, les guid pour n'importe quelle sorte de données distribuées. Une clé primaire ne change pas pendant la durée de vie de l'entité.

Cluster clés sont la clé qui définit le stockage physique de lignes dans la table. La plupart du temps ils se chevauchent avec la clé primaire (la logique identifiant d'entité), mais qui n'est pas exécutée, ni nécessaire. Lorsque les deux sont différents, cela signifie qu'il ya un non-clustered index unique sur la table qui met en œuvre la clé primaire. Valeurs de clés cluster peut réellement changer au cours de la durée de vie de la ligne, soit dans la ligne d'en être physiquement déplacé dans le tableau vers un nouvel emplacement. Si vous devez séparer la clé primaire de la clé cluster (et parfois, vous n'), le choix d'une bonne clé cluster est beaucoup plus difficile que de choisir une clé primaire. Il ya deux principaux facteurs que le lecteur de votre clé cluster de conception:

1. Les plus répandus, schéma d'accès aux données.
2. La considérations de stockage.

Schéma D'Accès Aux Données. Par la présente je comprends la façon dont la table est interrogé et mis à jour. Rappelez-vous que cluster touches de déterminer l'ordre des lignes dans le tableau. Pour certains modèles d'accès, certaines dispositions font toute la différence dans le monde en ce qui concerne la vitesse des requêtes ou de mettre à jour concurency:

• rapport de données d'archive. Dans de nombreuses applications, les données du mois en cours est souvent accessible, tandis que celui qui, dans le passé, est rarement accessible. Dans de tels cas, la conception de la table utilise table de partitionnement par la date de la transaction, le plus souvent à l'aide d'un fenêtre coulissante algorithme. Le mois en cours de la partition est conservé dans le groupe de fichiers situé à chaud un disque rapide, l'archive de la vieille données sont transférées à des groupes de fichiers hébergés sur le moins cher mais plus lent de stockage. Évidemment, dans ce cas, la clé cluster (date) n'est pas la clé primaire (id de transaction). La séparation des deux est motivée par les exigences d'échelle, comme l'optimiseur de requête sera en mesure de détecter que les requêtes ne sont intéressés que dans la partition actuelle et de ne pas même regarder les historiques.

• File d'attente FIFO style de traitement. Dans ce cas, la table a deux points chauds: la queue où les insertions se produire (mise en file d'attente), et de la tête où supprime se produire (file d'attente). La clé cluster doit prendre cela en compte et organiser le tableau de séparer physiquement la queue et la tête à l'emplacement sur le disque, afin de permettre concurency entre la file et file d'attente, par exemple. en utilisant une mise en file d'attente de l'ordre de la clé. Dans pur les files d'attente de cette clé cluster est la seule clé, car il n'y a pas de clé primaire sur la table (il contient messages, pas entités). Mais la plupart du temps la file d'attente n'est pas pure, elle sert également de rangement pour les entités, et la ligne entre le file d'attente et la table est estompée. Dans ce cas, il existe également une clé primaire, ce qui ne peut pas être la clé cluster: les entités peuvent être re-file d'attente, et donc de changer la mise en file d'attente afin cluster valeur de la clé, mais ils ne peuvent pas modifier la valeur de la clé primaire. Incapacité à voir la séparation est la principale raison d'être de l'utilisateur table adossés à des files d'attente sont donc notoirement difficiles à obtenir à droite et criblé de blocages: parce que la mise en file d'attente et de résorption de se produire entrelacés trought la table, au lieu d'localisée au niveau de la queue et de la tête de la file d'attente.

• Corrélation de traitement. Lorsque l'application est bien conçu, il s'partition de traitement de corrélation des articles entre les threads. Par exemple, un processeur est conçu pour avoir 8 thread de travail (disons pour faire correspondre les 8 Processeurs sur le serveur) de sorte que les processeurs de la partition de données entre eux, par exemple. travailleur 1 reprend seulement les comptes nommés de A à E, travailleur 2 F à J, etc. Dans de tels cas, la table doit être fait en cluster par le nom du compte (ou par une clé composite qui a la position la plus à gauche de la première lettre du nom de compte), de sorte que les travailleurs localiser leurs requêtes et mises à jour dans la table. Ce tableau aurait 8 points chauds, autour de la zone à chaque travailleur se concentre pour le moment, mais la chose importante est qu'ils ne se chevauchent pas (pas de blocage). Ce type de conception est répandue sur OLTP à haut débit dessins et dans TPCC de référence des charges, où ce type de partitionnement reflète également dans l'emplacement mémoire de la pages chargées dans le pool de mémoire tampon NUMA (localité), mais je m'éloigne du sujet.

Considérations De Stockage. La clé cluster largeur a d'énormes repercursions dans le stockage de la table. Pour l'un des principaux occupe de l'espace dans chaque page de feuille de l'arbre-b, si une grande touche d'occuper plus d'espace. Deuxièmement, et souvent le plus important, c'est que la clé cluster est utilisé comme clé de recherche par tous les non-clustred clé, de sorte chaque non-cluster touche pour stocker la totalité de la largeur de la clé cluster pour chaque ligne. C'est ce qui fait de grandes cluster clés comme varchar(256) et les guid un mauvais choix pour les clés d'index cluster.

Aussi le choix de la tonalité a un impact sur l'index cluster fragmentation, parfois radicalement affecter les performances.

Ces deux forces peuvent être parfois antagonistes, le schéma d'accès aux données nécessitant une certaine grande clé cluster qui va provoquer des problèmes de stockage. Dans de tels cas, bien sûr, un équilibre est nécessaire, mais il n'y a pas de formule magique. - Vous mesurer et vous test pour obtenir le sweet spot.

Alors, que faisons-nous faire de tout cela? Toujours commencer avec le fait de considérer clé cluster, qui est aussi la clé primaire de la forme entity_id IDENTITY(1,1) NOT NULL. Séparer les deux et organiser le tableau en conséquence (par exemple. partition par jour) lorsque approprié.

Comme vous l'avez dit, la cohérence est la clé. Personnellement j'utilise les entiers non signés. Vous n'allez pas manquer d'entre eux, sauf si vous travaillez avec ridicule quantités de données, et vous pouvez toujours savoir toute la colonne de la clé doit être de ce type et vous n'aurez jamais à aller à la recherche de la bonne valeur pour chaque colonne.

Pour de meilleures performances, 99.999% du temps la clé primaire doit être d'un seul champ de type entier.

Sauf si vous avez besoin de la clé primaire doit être unique au sein de plusieurs tables dans une base de données ou sur plusieurs bases de données. Je suis en supposant que vous vous posez sur MS SQL-Server, parce que c'est comment votre question a été marqué. Dans ce cas, pensez à utiliser le champ GUID à la place. Bien mieux qu'un varchar, le champ GUID de la performance n'est pas aussi bon que d'un nombre entier.

Si Joe Celko était ici, il pourrait avoir des mots durs... 😉

Je tiens à souligner que les services de renseignements comme une règle dure et rapide ne sont pas toujours appropriés. Disons que vous avez un véhicule de table avec tous les types de voitures, camions, etc. Maintenant, disons que vous avez eu un VehicleType table. Si vous vouliez obtenir tous les camions que vous pourriez faire ceci (avec un INT identity):

SELECT V.Make, V.Model
FROM Vehicle as V
INNER JOIN VehicleType as VT
ON V.VehicleTypeID = VT.VehicleTypeID
WHERE VT.VehicleTypeName = 'Truck'

Maintenant, avec un Varchar PK sur VehicleType:

SELECT Make, Model
FROM Vehicle 
WHERE VehicleTypeName = 'Truck'

Le code est un peu plus propre et vous éviter une jointure. Peut-être la jointure n'est pas la fin du monde, mais si vous avez seulement un outil dans votre boîte à outils, il vous manque des opportunités pour des gains de performance et plus propres schémas.

Juste une pensée. 🙂

Avec PostgreSQL j'utilise généralement de la "Série" ou "BigSerial" 'type de données" pour la génération des clés primaires. Les valeurs sont auto incrémenté et je trouve toujours des nombres entiers pour être facile à travailler avec. Ils sont essentiellement équivalentes à un serveur MySQL d'un champ de type entier qui est mis à "auto_increment".

Nous devons garder à l'esprit que la clé primaire d'une table ne devrait pas avoir de "logique d'entreprise" et il devrait être seulement une identité de l'enregistrement il appartient. Suivant cette règle simple un int et surtout une identité de type int est une très bonne solution. En demandant varchar je suppose que tu veux dire en utilisant par exemple le "Nom Complet" comme une clé pour les "gens" de la table. Mais que faire si nous voulons changer le nom de "George quelque Chose de" de "de George A. quelque Chose" ? Et quelle taille a le champ ? Si nous changeons la taille, nous avons pour modifier la taille sur toutes les tables de trop. Donc, nous devrions éviter de logique sur les touches. Parfois, nous pouvons utiliser l'sociale ID (valeur entière) comme clé mais je éviter que de trop. Maintenant, si un projet a des perspectives à l'échelle, vous devriez envisager d'utiliser Guid trop (de type uniqueidentifier de type SQL).