SQL Server - est un GUID basé sur la PK la meilleure pratique pour supporter le partitionnement horizontal basé sur les locataires.

Question

J'essaie de déterminer quelle est la meilleure approche pour concevoir un schéma de base de données multi-locataires qui devra être partitionné horizontalement à l'avenir.

Quelques chiffres approximatifs sur la base de données

Le nombre total de locataires sera d'environ 10 000. La quantité de données stockées par locataire varie entre 500MB -> 3GB. Le nombre de locataires sera faible au départ et augmentera jusqu'à 10 000 en quelques années. Nous pouvons donc commencer par une seule base de données multilocataires, mais à long terme, elle devra évoluer horizontalement pour des raisons de performance. pour des raisons de performance.

Mise à jour - un facteur de complication est qu'occasionnellement les locataires (sociétés) peuvent fusionner ensemble et je dois également prendre en charge cette situation...,

La multilocation sera mise en œuvre à l'aide d'une base de données partagée et d'une architecture à schéma partagé, comme décrit dans le présent document. http://msdn.microsoft.com/en-us/library/aa479086.aspx

Étant donné que nous serons confrontés au partitionnement horizontal à l'avenir et qu'il est probable que nous déplacions les clients d'une base de données à une autre plusieurs fois avant que les choses ne se stabilisent, je pense qu'il est préférable d'utiliser les GUID comme clés primaires sur chaque table ainsi qu'une colonne unique tenantID.

Je sais que l'utilisation de GUID comme clé primaire entraîne une surcharge de performance, mais est-ce un compromis que je dois accepter ? Existe-t-il un autre moyen de concevoir un partitionnement horizontal à l'avenir ?

Voici un exemple : disons que je veux fusionner des entreprises avec des locataires 100 et 200 dans le futur, si le PK est un entier, il peut y avoir une collision lorsque je copie les lignes de la base de données 2 vers la base de données 1, avec {guids} je suis sûr qu'il n'y aura pas de collision...

base de données 1 base de données 2 tenantid, id, description tenantid, id, description 100 ,1 , "foo" 200 ,1 , "xxx" 100 ,2 , "boo" 20 100 ,2 , 'boo' 200 ,2 , 'yyy

base de données 1 base de données 2 tenantid, id, description tenantid, id, description 100 ,{aaa} , 'foo' 200 ,{ccc} , 'xxx' 100 ,{bbb} , 'boo' 200 ,{ddd} , 'yyy

Answer 1

Les GUIDs peuvent sembler être un choix naturel pour votre clé primaire - et si vous devez vraiment le faire, vous pourriez probablement argumenter pour l'utiliser pour la CLÉ PRIMAIRE de la table. Ce que je recommande fortement à ne pas faire est d'utiliser la colonne GUID comme clé de répartition Le serveur SQL le fait par défaut, sauf si vous lui demandez de ne pas le faire.

Vous devez vraiment séparer les deux problèmes :

1) le clé primaire est une construction logique - l'une des clés candidates qui identifie 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 - choisissez ce qui a le plus de sens pour votre scénario.

2) la clé de répartition (la ou les colonnes qui définissent l'"index en grappe" sur la table) - il s'agit d'une physique Dans ce cas, un type de données petit, stable et croissant est votre meilleur choix - INT ou BIGINT comme option par défaut.

Par défaut, la clé primaire d'une table SQL Server est également utilisée comme clé de clustering - mais il n'est pas nécessaire qu'il en soit ainsi ! J'ai personnellement constaté des gains de performance massifs en décomposant la clé primaire / clusterisée basée sur le GUID en deux clés distinctes - la clé primaire (logique) sur le GUID, et la clé clusterisée (de classement) sur une colonne séparée INT IDENTITY(1,1).

Comme Kimberly Tripp - la Reine de l'Indexation - et d'autres l'ont dit à de nombreuses reprises - un GUID comme clé de clustering n'est pas optimal, car en raison de son caractère aléatoire, il conduira à une fragmentation massive des pages et de l'index et à des performances généralement mauvaises.

Oui, je sais - il y a newsequentialid() dans SQL Server 2005 et plus - mais même cette méthode n'est pas vraiment et entièrement séquentielle et souffre donc des mêmes problèmes que le GUID - mais de façon un peu moins évidente.

Il y a ensuite un autre problème à prendre en compte : la clé de clustering d'une table sera ajoutée à chaque entrée de chaque index non clusterisé de votre table également - vous devez donc vous assurer qu'elle est aussi petite que possible. Typiquement, un INT de plus de 2 milliards de lignes devrait suffire pour la grande majorité des tables - et par rapport à 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 - utilisation d'INT ou de GUID comme clé primaire et clé de regroupement :

• Tableau de base avec 1'000'000 rangs (3.8 MB vs. 15.26 MB)
• 6 index non regroupés (22,89 Mo contre 91,55 Mo)

TOTAL : 25 Mo contre 106 Mo - et ce, sur une seule table !

D'autres éléments de réflexion - excellent travail de Kimberly Tripp - lisez-le, relisez-le, digérez-le ! C'est l'évangile de l'indexation du serveur SQL, vraiment.

• GUIDs en tant que PRIMARY KEY et/ou clé clusterisée
• Le débat sur les index en grappe se poursuit
• Clé du clustering toujours plus grande - le débat sur les index clusterisés..........again !
• L'espace disque est bon marché - c'est no le point !

Marc

Answer 2

Il y a deux choses à noter ici :

1. identifier l'ensemble des rangées d'un locataire parmi toutes les rangées
2. identifier une rangée au sein des rangées d'un locataire au sein de toutes les rangées

Le point 2 est la clé primaire.

L'utilisation d'un GUID pour identifier un locataire est utile car vous ne pouvez pas deviner l'ID d'un autre locataire (comme si vous utilisiez la colonne IDENTITY selon ce livre blanc). Mais un GUID pour la clé clusterisée est une mauvaise idée (comme dans la réponse de marc_s).

Cela conduit à une PK composite de GUID et d'une colonne IDENTITY, probablement

• l'IDENTITÉ première comme un index unique en cluster,
• le GUID est un FK d'une table de locataire, index non clusterisé
• et le PK sur les deux colonnes mais non groupé

Cela devrait être un compromis raisonnable pour couvrir la plupart des modèles de requête et des FK de cette table.

Bien sûr, cela dépend de la conception finale : J'ai supposé ici qu'il s'agit d'une sorte de table "fact" ou "parent de fact".

Answer 3

Avez-vous envisagé les fédérations SQL Azure à la place ? Un partitionnement horizontal évolutif qui inclut la prise en charge des opérations de rééquilibrage prêtes à l'emploi (c'est-à-dire le déplacement des locataires entre les partitions), et une solution de haute disponibilité fournie par le service. Il n'y a tout simplement rien de tel sur le produit SQL Server box. Voir Introduction à la fédération dans SQL Azure o Comment partager avec SQL Azure .

Quant à la question de faire cada entité PK soit un GUID, je ne vois vraiment pas l'intérêt. Faire en sorte que chaque table de données des locataires soit préfixée avec un ID de locataire oui, absolument. Faire en sorte que tous les index clusterisés des données des locataires aient le tenantId comme touche la plus à gauche : (tenantId, key, key, key)) : idem, indispensable. Faire en sorte que la clé primaire de l'entité soit (tenantId, entityId) : très probable. Mais faire le entityId un guide ? Je ne vois vraiment pas pourquoi. A moins que vous n'ayez des entités partagé entre les locataires, le tenantId agit comme un espace de nom dans lequel les entityId s'applique. Déplacer les données entre les shards est bien, même si cela mène à la duplication entityId valeurs puisque les entités PK sont scoped par le tenantId .