sql server - utilisation - Quelles sont les meilleures pratiques pour utiliser un GUID comme clé primaire, en particulier en ce qui concerne les performances?




utilisation index sql server (4)

J'ai une application qui utilise GUID comme clé primaire dans presque toutes les tables et j'ai lu qu'il y a des problèmes au sujet des performances en utilisant GUID comme clé primaire. Honnêtement, je n'ai vu aucun problème, mais je suis sur le point de démarrer une nouvelle application et je veux toujours utiliser les GUID comme clés primaires, mais je pensais utiliser une clé primaire composite (le GUID et peut-être un autre domaine .)

J'utilise un GUID parce qu'il est agréable et facile à gérer quand vous avez différents environnements tels que les bases de données "production", "test" et "dev", et aussi pour les données de migration entre bases de données.

Je vais utiliser Entity Framework 4.3 et je veux assigner le Guid dans le code de l'application, avant de l'insérer dans la base de données. (ie je ne veux pas laisser SQL générer le Guid).

Quelle est la meilleure pratique pour créer des clés primaires basées sur GUID, afin d'éviter les hits de performance supposés associés à cette approche?


Ce lien le dit mieux que je ne pouvais et m'a aidé dans ma prise de décision. J'opte généralement pour un int comme une clé primaire, sauf si j'ai un besoin spécifique de ne pas et je laisse également le serveur SQL auto-générer / maintenir ce champ sauf si j'ai une raison particulière de ne pas le faire. En réalité, les problèmes de performance doivent être déterminés en fonction de votre application spécifique. De nombreux facteurs entrent en jeu, notamment la taille de la base de données attendue, l'indexation correcte, l'interrogation efficace et bien plus encore. Bien que les gens puissent être en désaccord, je pense que dans plusieurs cas, vous ne remarquerez aucune différence avec l'une ou l'autre option et vous devriez choisir ce qui est le plus approprié pour votre application et ce qui vous permet de développer plus facilement, plus rapidement et plus efficacement. quelle différence fait le reste :).

https://web.archive.org/web/20120812080710/http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

PS Je ne sais pas pourquoi vous utiliseriez un PK composite ou quel avantage vous en retireriez.


J'utilise des GUID comme PK depuis 2005. Dans ce monde de bases de données distribuées, c'est le meilleur moyen de fusionner des données distribuées. Vous pouvez déclencher et oublier les tables de fusion sans avoir à craindre que les ints ne correspondent aux tables jointes. Les jointures de GUID peuvent être copiées sans aucun souci.

C'est ma configuration pour utiliser les GUID:

  1. PK = GUID. Les GUID sont indexés de la même façon que les chaînes, de sorte que des tables de lignes élevées (plus de 50 millions d'enregistrements) peuvent nécessiter un partitionnement de table ou d'autres techniques de performance. SQL Server devient extrêmement efficace, donc les problèmes de performance sont de moins en moins applicables.

  2. PK Guid est un index non clusterisé. Ne jamais indexer un GUID à moins qu'il ne s'agisse d'un NewSequentialID. Mais même dans ce cas, un redémarrage du serveur entraînera des interruptions majeures dans la commande.

  3. Ajoutez ClusterID Int à chaque table. Ceci est votre Index CLUSTERED ... qui commande votre table.

  4. L'adhésion à ClusterIDs (int) est plus efficace, mais je travaille avec 20 à 30 millions de tables d'enregistrements, de sorte que joindre des GUID n'affecte pas visiblement les performances. Si vous voulez des performances maximales, utilisez le concept ClusterID comme clé primaire et joignez-vous à ClusterID.

Voici ma table Email ...

CREATE TABLE [Core].[Email] (

[EmailID]      UNIQUEIDENTIFIER CONSTRAINT [DF_Email_EmailID] DEFAULT (newsequentialid()) NOT NULL,

[EmailAddress] NVARCHAR (50)    CONSTRAINT [DF_Email_EmailAddress] DEFAULT ('') NOT NULL,

[CreatedDate]  DATETIME         CONSTRAINT [DF_Email_CreatedDate] DEFAULT (getutcdate()) NOT NULL,

[ClusterID] INT NOT NULL IDENTITY,
    CONSTRAINT [PK_Email] PRIMARY KEY NonCLUSTERED ([EmailID] ASC)
);
GO

CREATE UNIQUE CLUSTERED INDEX [IX_Email_ClusterID] ON [Core].[Email] ([ClusterID])
GO

CREATE UNIQUE NonCLUSTERED INDEX [IX_Email_EmailAddress] ON [Core].[Email] ([EmailAddress] Asc)

Si vous utilisez GUID comme clé primaire et créez un index cluster, alors je suggère d'utiliser la valeur par défaut de NEWSEQUENTIALID ()


GUID peut sembler être un choix naturel pour votre clé primaire - et si vous devez vraiment, vous pourriez probablement argumenter pour l'utiliser pour la clé primaire de la table. Ce que je vous recommande vivement de ne pas faire est d'utiliser la colonne GUID comme clé de clustering , ce que SQL Server fait par défaut, à moins que vous ne le disiez spécifiquement.

Vous devez vraiment séparer deux problèmes:

  1. la clé primaire est une construction logique - l'une des clés candidates qui identifie de manière unique et fiable chaque ligne de votre tableau. Cela peut être quelque chose, vraiment - un INT , un GUID , une chaîne - choisissez ce qui est le plus logique pour votre scénario.

  2. la clé de clustering (la ou les colonnes qui définissent l'index clusterisé sur la table) - c'est une chose liée au stockage physique , et ici, un petit type de données stable et croissant est votre meilleur choix - INT ou BIGINT comme option par défaut

Par défaut, la clé primaire sur une table SQL Server est également utilisée comme clé de clustering - mais cela n'a pas besoin d'être ainsi! J'ai personnellement constaté des gains de performances massifs lors de la décomposition de la clé primaire / cluster précédente basée sur GUID en deux clés distinctes: la clé principale (logique) du GUID et la clé de clustering (ID) sur une INT IDENTITY(1,1) colonne.

Comme Kimberly Tripp - la reine de l'indexation - et d'autres l'ont dit à maintes reprises - un GUID car la clé de clustering n'est pas optimale car, en raison de son caractère aléatoire, elle entraînera une fragmentation massive des index et des pages.

Oui, je sais - il y a newsequentialid() dans SQL Server 2005 et plus - mais même cela n'est pas vraiment et complètement séquentiel et souffre donc aussi des mêmes problèmes que le GUID - juste un peu moins en évidence.

Il y a ensuite un autre problème à prendre en compte: la clé de clustering sur une table sera ajoutée à chaque entrée de chaque index non clusterisé de votre table. Vous voulez donc vous assurer qu'elle est la plus petite possible. Généralement, un INT avec 2+ milliards de lignes devrait être suffisant pour la grande majorité des tables - et comparé à un GUID comme clé de clustering, vous pouvez économiser des centaines de mégaoctets de stockage sur le disque et dans la mémoire du serveur.

Calcul rapide - en utilisant INT et GUID comme Clé primaire et Clustering:

  • Table de base avec 1'000'000 lignes (3,8 Mo contre 15,26 Mo)
  • 6 index non clusterisés (22,89 Mo contre 91,55 Mo)

TOTAL: 25 Mo contre 106 Mo - et c'est juste sur une seule table!

Un peu plus de matière à réflexion - d'excellentes choses de Kimberly Tripp - lisez-le, relisez-le, digérez-le! C'est l'évangile d'indexation de SQL Server, vraiment.

PS: bien sûr, si vous avez affaire à quelques centaines ou quelques milliers de lignes - la plupart de ces arguments n'auront pas vraiment d'impact sur vous. Cependant: si vous entrez dans les dizaines ou les centaines de milliers de rangées, ou si vous commencez à compter en millions, alors ces points deviennent très importants et très importants à comprendre.

Mise à jour: si vous voulez avoir votre colonne PKGUID comme clé primaire (mais pas votre clé de clustering), et une autre colonne MYINT ( INT IDENTITY ) comme clé de clustering - utilisez ceci:

CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
 MyINT INT IDENTITY(1,1) NOT NULL,
 .... add more columns as needed ...... )

ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)

CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT)

Fondamentalement: il suffit de dire explicitement à la contrainte PRIMARY KEY qu'elle est NONCLUSTERED (sinon elle est créée en tant qu'index en cluster, par défaut) - et ensuite vous créez un second index défini comme CLUSTERED

Cela fonctionnera - et c'est une option valable si vous avez un système existant qui doit être «repensé» pour la performance. Pour un nouveau système, si vous démarrez à partir de zéro, et que vous n'êtes pas dans un scénario de réplication, alors je choisirais toujours ID INT IDENTITY(1,1) comme clé primaire en cluster - beaucoup plus efficace que toute autre chose!





guid