GUID comme clé primaire ?

Par Arian Papillon le jeudi 13 décembre 2018, 17:43 - Lien permanent

Amusant que la question soit encore régulièrement posée : est-ce une bonne idée d'utiliser un GUID (global unique identifier) comme clé primaire pour nos tables ?

Encore récemment, j'ai trouvé des défenseurs du GUID : à leurs dires, utiliser un GUID était mieux qu'une colonne IDENTITY, la raison en serait que l'index sur un IDENTITY ne serait pas balancé car "on ajoute toujours à la doite"...

La question est intéressante, mais le fait que les données soit ajoutées à droite ne rend pas pour autant l'index "unbalanced".

Les index SQL Server ont une structure BTREE+ (self-balancing tree) : arbres équilibrés chaînés, dont la distance entre la racine et les feuilles est identique pour toutes les branches. Le fait d'ajouter "à droite" ne modifie pas sa structure...

Faisons le test par l'exemple : si nous prenons une table avec > 10 millions de lignes, avec clé primaire sur INT IDENTITY

Remplissons la table ligne à ligne, avec son index actif, voici le résultat :

Après reconstruction (alter index rebuild), on ne gagne pas vraiment grand chose :

Après rebuild, les pages de niveau intermédiaire sont mieux remplies (on gagne un peu plus d’une centaine de pages), mais l’efficacité de l’index et le parcours (3 pages) sont les mêmes pour localiser une clé. A l’examen détaillé des pages d’index, on ne voit vraiment pas ce qui n’est pas balancé…
L’intérêt de la réorganisation pourrait éventuellement consister à obtenir des extensions allouées de manière contigüe, ce qui pourrait être un éventuel gain pour les performances de lecture en cas de scan, mais qui n’apporte en tout cas rien à la recherche de clé.

Si on regarde avantages et inconvénients :

  • Le principal inconvénient d’un index cluster sur un identity est surtout la contention (latch) lors d’insertions multiples et massives, les insertions se situant sur la même page, effectivement à droite…
  • Les inconvénients d’un index sur un GUID, c’est que c’est extrêmement volumineux et aussi qu’il n’y a pas d’ordre dans la séquence ce qui va nécessairement provoquer de la fragmentation. Ce sont bien là des problèmes souvent plus graves…
Pour exemple, la même table avec un GUID comme clé cluster, remplie ligne à ligne : la comparaison parle d'elle même, l'index est moins efficace : la taille a tout de même 15000 pages d'écart et nous constatons que l'index possède un niveau de plus...! N'oublions pas non plus que le GUID, plus volumineux (16 octets quand un INT en fait 4) va servir de pointeur pour tous les autres index nonclustered de la table, les rendant tous plus volumineux et moins efficaces...
Et ici, le rebuild va devenir réellement nécessaire, on arrive à passer ainsi à 3 niveaux (mais toujours un index trop volumineux)...
Dans les années 2000 (cela ne nous rajeunit pas), certains consultants de Microsoft (je ne citerai pas les noms :-) recommandaient d'utiliser systématiquement des clés GUID. Ils sont clairement revenus sur leur opinion.
  • Le GUID est volumineux : 16 octets, et ses valeurs aléatoires favorisent une forte fragmentation des index.
  • L'entier est petit (4 octets), c'est généralement suffisant ( de -2 147 483 648 à 2 147 483 647), sinon il y a le BIGINT sur 8 octets. Utilisé avec IDENTITY, les valeurs sont monotones et croissantes. Il est plus rapide à calculer et SQL Server sera plus efficace pour faire des jointures entre INT. Entre autres avantages, il est plus facile à lire et débugger, les fonctions permettant de connaitre la dernière valeur sont présentes (SCOPE_IDENTITY()), on peut utiliser des fonctions MIN ou MAX, etc...
Bonne indexation...!