Quelles sont les meilleures pratiques pour utiliser un GUID en tant que clé primaire, notamment en ce qui concerne les performances?

Question

J'ai une application qui utilise des GUID comme clé primaire dans presque toutes les tables et j'ai lu qu'il y a des problèmes de performance lors de l'utilisation de GUID comme clé primaire. Honnêtement, je n'ai pas vu de problème, mais je vais commencer 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 champ).

J'utilise un GUID car ils sont pratiques et faciles à gérer lorsque vous avez différents environnements tels que des bases de données "production", "test" et "dev", et aussi pour la migration de données entre les 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. (c'est-à-dire je ne veux pas laisser SQL générer le GUID).

Quelle est la meilleure pratique pour la création de clés primaires basées sur les GUID, afin d'éviter les prétendus problèmes de performances associés à cette approche?

Answer 1

Les GUID peuvent sembler être un choix naturel pour votre clé primaire - et si vraiment nécessaire, vous pourriez probablement argumenter pour l'utiliser comme CLÉ PRIMAIRE de la table. Ce que je recommande fortement de ne pas faire est d'utiliser la colonne GUID comme clé de regroupement, ce que SQL Server fait par défaut, à moins que vous ne lui disiez spécifiquement de ne pas le faire.

Vous devez vraiment séparer deux problèmes:

1. la clé primaire est un concept logique - l'une des clés candidates qui identifient de manière unique et fiable chaque ligne de votre table. Cela peut être n'importe quoi, vraiment - un INT, un GUID, une chaîne de caractères - choisissez ce qui a le plus de sens pour votre scénario.

2. la clé de regroupement (la colonne ou les colonnes qui définissent l'«index regroupé» sur la table) - c'est une chose liée au stockage physique, et ici, un type de données petit, stable et toujours croissant est le 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 regroupement - mais cela n'a pas besoin d'être ainsi! J'ai personnellement constaté des gains de performance massifs en séparant la clé primaire/principale basée sur GUID en deux clés distinctes - la clé primaire (logique) sur le GUID, et la clé de regroupement (d'ordonnancement) sur une colonne INT IDENTITY(1,1) distincte.

Comme Kimberly Tripp - la Reine de l'Indexation - et d'autres l'ont dit de nombreuses fois - un GUID en tant que clé de regroupement n'est pas optimal, car en raison de sa randomité, cela entraînera une fragmentation massive des pages et des index et une performance généralement médiocre.

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

Il y a ensuite un autre point à considérer: la clé de regroupement sur une table sera ajoutée à chaque entrée de chaque index non regroupé sur votre table également - donc vous voulez vraiment vous assurer qu'elle est aussi petite que possible. En général, un INT avec 2+ milliards de lignes devrait suffire pour la grande majorité des tables - et par rapport à un GUID en tant que clé de regroupement, vous pouvez vous économiser des centaines de mégaoctets de stockage sur disque et en mémoire serveur.

Calcul rapide - en utilisant INT vs. GUID en tant que Clé Primaire et de Regroupement:

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

TOTAL: 25 Mo vs. 106 Mo - et c'est juste pour une seule table!

Un peu plus à réfléchir - excellentes informations de la part de Kimberly Tripp - lisez-le, relisez-le, assimilez-le! C'est un véritable évangile de l'indexation SQL Server.

• GUIDs en tant que CLÉ PRIMAIRE et/ou clé de regroupement
• Le débat sur l'index regroupé continue
• Clé de regroupement de plus en plus croissante - Le débat sur l'index regroupé..........encore une fois!
• L'espace disque est bon marché - ce n'est pas le point!

PS: bien sûr, si vous traitez seulement quelques centaines ou quelques milliers de lignes - la plupart de ces arguments n'auront pas vraiment beaucoup d'impact sur vous. Cependant: si vous atteignez les dizaines ou les centaines de milliers de lignes, ou que vous commencez à compter en millions - alors ces points deviennent très cruciaux et très importants à comprendre.

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

CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
 MyINT INT IDENTITY(1,1) NOT NULL,
 .... ajoutez plus de colonnes au besoin ...... )

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

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

Fondamentalement: vous devez simplement dire explicitement à la contrainte de PRIMARY KEY qu'elle est NONCLUSTERED (sinon elle est créée comme votre index regroupé, par défaut) - puis vous créez un deuxième index défini comme CLUSTERED

Cela fonctionnera - et c'est une option valide si vous avez un système existant qui doit être "reconçu" pour la performance. Pour un nouveau système, si vous partez 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 ma clé primaire de regroupement - beaucoup plus efficace que tout autre chose!

Answer 2

Je utilise des GUIDs comme clés primaires depuis 2005. Dans ce monde de base de données distribuées, c'est absolument le meilleur moyen de fusionner des données distribuées. Vous pouvez fusionner des tables sans vous soucier que les entiers correspondent à travers les tables jointes. Les jointures GUID peuvent être copiées sans aucun souci.

Voici ma configuration pour utiliser les GUIDs :

1. PK = GUID. Les GUIDs sont indexés de manière similaire aux chaînes, donc les tables à fort volume de lignes (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 préoccupations de performance sont de moins en moins applicables.

2. L'index PK Guid n'est pas regroupé. N'indexez jamais un GUID à moins qu'il ne s'agisse de NewSequentialID. Mais même dans ce cas, un redémarrage du serveur provoquera de sérieux problèmes d'ordonnancement.

3. Ajoutez un ClusterID Int à chaque table. C'est votre Index GROUPÉ... qui ordonne votre table.

4. Les jointures sur les ClusterIDs (int) sont plus efficaces, mais je travaille avec des tables de 20 à 30 millions d'enregistrements, donc les jointures sur des GUIDs n'affectent pas visiblement les performances. Si vous voulez une performance maximale, utilisez le concept de ClusterID comme clé primaire et joignez sur 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)

Answer 3

Je développe actuellement une application web avec EF Core et voici le schéma que j'utilise :

Toutes mes classes (tables) ont une clé primaire et étrangère de type int. Ensuite, j'ai une colonne supplémentaire de type Guid (générée par le constructeur C#) avec un index non clusterisé dessus.

Tous les joints de tables dans EF sont gérés à travers les clés de type int tandis que tous les accès depuis l'extérieur (contrôleurs) se font avec les Guid.

Cette solution permet de ne pas afficher les clés de type int dans les URLs tout en maintenant le modèle propre et rapide.

Answer 4

Ce lien exprime mieux que je ne pourrais le faire et m'a aidé dans ma prise de décision. J'opte généralement pour un int comme clé primaire, sauf si j'ai un besoin spécifique de ne pas le faire, et je laisse également SQL server générer/maintenir automatiquement ce champ sauf si j'ai une raison spécifique de ne pas le faire. En réalité, les préoccupations de performance doivent être déterminées en fonction de votre application spécifique. De nombreux facteurs entrent en jeu ici, notamment mais sans s'y limiter, la taille de la base de données attendue, l'indexation appropriée, les requêtes efficaces, et plus encore. Même si les avis peuvent diverger, je pense que dans de nombreux scénarios, vous ne remarquerez pas de 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 (Si vous ne terminez jamais l'application, quelle importance le reste a-t-il :).

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

P.S. Je ne suis pas sûr pourquoi vous utiliseriez une PK composite ou quel avantage vous pensez que cela vous apporterait.

Answer 5

Eh bien, si vos données ne dépassent jamais des millions de lignes, vous êtes tranquille. Si vous me demandez, je n'utilise jamais GUID comme colonne d'identité de base de données de quelque type que ce soit, y compris en tant que PK même si vous me forcez à concevoir avec un fusil sur la tête.

Utiliser GUID comme clé primaire est un arrêt définitif de mise à l'échelle, et critique. Je vous recommande de vérifier l'option d'identité de base de données et de séquence. La séquence est indépendante de la table et peut fournir une solution à vos besoins (MS SQL possède des séquences).

Si vos tables commencent à atteindre quelques dizaines de millions de lignes, par exemple 50 millions, vous ne pourrez pas lire/écrire des informations à des délais acceptables et même la maintenance standard des index de base de données deviendrait impossible.

Alors vous devez utiliser le partitionnement, et être scalable jusqu'à un demi-milliard voire 1 à 2 milliards de lignes. Ajouter le partitionnement en cours de route n'est pas la chose la plus facile, toutes les déclarations de lecture/écriture doivent inclure la colonne de partition (changement complet de l'application !).

Ces chiffres bien sûr (50 millions et 500 millions) sont pour une utilisation légère de la sélection. Si vous avez besoin de sélectionner des informations de manière complexe et/ou faites beaucoup d'insertions/mises à jour/suppressions, ceux-ci pourraient même être de 1 à 2 millions et 50 millions respectivement, pour un système très exigeant. Si vous ajoutez également des facteurs tels que le modèle de récupération complète, la haute disponibilité et l'absence de fenêtre de maintenance, communs aux systèmes modernes, les choses deviennent extrêmement moches.

Notez à ce stade que 2 milliards est la limite int qui semble mauvaise, mais int est 4 fois plus petit et est un type de données séquentiel, la petite taille et le type séquentiel sont le facteur n°1 de la scalabilité de la base de données. Et vous pouvez utiliser big int qui est juste deux fois plus petit mais toujours séquentiel, séquentiel est ce qui est vraiment mortellement important - encore plus important que la taille - quand il s'agit de plusieurs millions ou de quelques milliards de lignes.

Si GUID est également clusterisé, les choses sont bien pires. Insérer simplement une nouvelle ligne sera enregistré partout de manière aléatoire dans la position physique.

Même en étant juste une colonne, pas PK ou partie de PK, simplement l'indexer est un problème. Du point de vue de la fragmentation.

Avoir une colonne guid est parfaitement normal comme toute colonne varchar tant que vous ne l'utilisez pas comme partie de PK et en général comme colonne clé pour joindre des tables. Votre base de données doit avoir ses propres éléments de PK, filtrer et joindre les données en les utilisant - filtrer aussi par un GUID ensuite est parfaitement normal.