Cette page traite des schémas et présente les tables entrelacées, ce qui peut améliorer les performances des requêtes lors de l'interrogation de tables dans une relation parent-enfant.
Les bases de données Spanner contiennent une ou plusieurs tables. Les tableaux sont structurés comme lignes et colonnes. Une ou plusieurs colonnes sont définies comme clé primaire de la table, qui identifie chaque ligne de manière unique. Les clés primaires sont toujours indexées pour une recherche de ligne rapide, et vous pouvez définir des index secondaires sur une ou plusieurs colonnes. Si vous souhaitez mettre à jour ou supprimer des lignes existantes dans une table, celle-ci doit disposer d'une clé primaire. Une table sans colonne de clé primaire ne peut avoir qu'une seule ligne. Seules les bases de données utilisant le dialecte GoogleSQL peuvent comporter des tables sans clé primaire.
Dans Spanner, les données sont fortement typées. Vous devez définir un schéma pour chaque base de données, qui doit spécifier le type de données de chaque colonne de chaque table. Les types de données incluent des types scalaires et complexes, décrits dans les sections Types de données dans GoogleSQL et Types de données PostgreSQL.
Relations de table parents-enfants
Il existe deux façons de définir des relations parent-enfant dans Spanner : l'entrelacement de tables et les clés étrangères.
L'entrelacement de tables de Spanner est un bon choix pour de nombreuses relations parent-enfant. Avec l'entrelacement, Spanner colocalise physiquement les lignes enfants avec les lignes parentes dans l'espace de stockage. La colocation peut améliorer considérablement les performances. Par exemple, si vous disposez d'une table Customers et d'une table Invoices, et que votre application récupère fréquemment toutes les factures d'un client, vous pouvez définir Invoices comme table enfant entrelacée de Customers. En procédant ainsi, vous déclarez une relation de localité des données entre deux tables indépendantes. Vous indiquez à Spanner de stocker une ou plusieurs lignes de l'élément Invoices avec une ligne Customers.
Pour associer une table enfant à une table parente, utilisez le LDD qui déclare la table enfant comme étant entrelacée dans la table parente et incluez la clé primaire de la table parente en tant que première partie de la clé primaire composite de la table enfant. Pour en savoir plus sur l'entrelacement, consultez la section Créer des tables entrelacées plus loin dans cette rubrique.
Les clés étrangères constituent une solution parent-enfant plus générale et répondent à des cas d'utilisation supplémentaires. Elles ne sont pas limitées aux colonnes de clé primaire, et les tables peuvent posséder plusieurs relations de clés étrangères en faisant office de tables parentes dans certaines relations et de tables enfants dans d'autres. Toutefois, une relation de clé étrangère n'implique pas la colocation des tables dans la couche de stockage.
Google vous recommande de représenter les relations parents-enfants soit comme des tables entrelacées, soit comme des clés étrangères, mais pas les deux. Pour en savoir plus sur les clés étrangères et leur comparaison avec les tables entrelacées, consultez la page Présentation des clés étrangères.
Choisir une clé primaire
Souvent, votre application possède déjà un champ qui peut parfaitement remplir la fonction de clé primaire. Par exemple, pour une table Customers, un élément CustomerId fourni par l'application peut être utilisé en plus de la clé primaire. Dans d'autres cas, vous devrez peut-être générer une clé primaire lors de l'insertion de la ligne. Il s'agit généralement d'un nombre entier unique sans aucune importance commerciale (clé primaire de substitution).
Dans tous les cas, lors du choix de votre clé primaire, veillez à ne pas créer de hotspots. Par exemple, si vous insérez des données dont la clé est un nombre entier augmentant de manière monotone, les insertions se feront toujours à la fin de l'espace clé. Ce n'est pas souhaitable, car Spanner divise les données entre les serveurs par plages de clés, ce qui signifie que vos insertions seront dirigées vers un seul serveur, créant ainsi un hotspot. Il existe des techniques pouvant répartir la charge sur plusieurs serveurs et éviter la création de hotspots. Les voici :
- Hacher la clé et la stocker dans une colonne. Utilisez la colonne de hachage (ou la colonne de hachage et les colonnes de clé unique ensemble) comme clé primaire.
- Échanger l'ordre des colonnes dans la clé primaire.
- Utiliser un identifiant unique universel (UUID). La version 4 d'UUID est recommandée, car elle utilise des valeurs aléatoires dans les bits de poids fort. N'utilisez pas d'algorithme UUID (tel que la version 1 d'UUID), car il stocke l'horodatage dans les bits de poids fort.
- Utiliser des valeurs séquentielles de bits inversés.
Ajouter des index secondaires basés sur des clés primaires
Dans certains cas, l'utilisation de votre base de données peut bénéficier de l'ajout d'index secondaires basés sur des clés primaires. Cela est particulièrement vrai si vous exécutez fréquemment des requêtes nécessitant des analyses inversées de la clé primaire d'une table.
Clés primaires dans les tables entrelacées
Pour l'entrelacement, chaque table doit avoir une clé primaire. Si vous déclarez qu'une table est l'enfant entrelacé d'une autre table, la table doit comporter une clé primaire composite qui inclut tous les composants de la clé primaire du parent, dans le même ordre et, généralement, une ou plusieurs colonnes enfants supplémentaires.
Spanner stocke les lignes dans l'ordre de tri des valeurs de clé primaire, les lignes enfants étant insérées entre les lignes parentes. Vous trouverez un exemple de lignes entrelacées dans la section Créer des tables entrelacées plus loin dans cette rubrique.
En résumé, Spanner peut colocaliser physiquement des lignes de tables associées. Les exemples de schéma montrent à quoi ressemble cette mise en page physique.
Divisions de base de données
Vous pouvez définir des hiérarchies de relations parent-enfant entrelacées jusqu'à sept couches, ce qui signifie que vous pouvez colocaliser les lignes de sept tables indépendantes. Si la taille des données de vos tables est faible, un seul serveur Spanner pourra probablement gérer votre base de données. Mais que se passe-t-il lorsque la taille des tables associées augmente et commence à atteindre les limites de ressources d'un serveur individuel ? Spanner est une base de données distribuée. Cela signifie qu'à mesure que votre base de données s'agrandit, Spanner divise vos données en fragments appelés "divisions". Les divisions individuelles peuvent être déplacées indépendamment les unes des autres et être affectées à différents serveurs pouvant se trouver dans des emplacements physiques différents. Une division contient une plage de lignes contiguës. Les clés de début et de fin de cette plage sont appelées "limites de division". Spanner ajoute et supprime automatiquement les limites de division en fonction de la taille et de la charge, ce qui modifie le nombre de divisions dans la base de données.
Répartition basée sur la charge
Pour illustrer la manière dont Spanner effectue une répartition basée sur la charge pour limiter les hotspots de lecture, supposons que votre base de données contient une table avec 10 lignes lues plus fréquemment que toutes les autres lignes de la table. Spanner peut ajouter des limites de division entre chacune de ces 10 lignes afin qu'elles soient chacune gérées par un serveur différent, au lieu d'autoriser toutes les lectures de ces lignes à consommer les ressources d'un seul serveur.
En règle générale, si vous suivez les bonnes pratiques de conception de schéma, Spanner peut limiter les hotspots de sorte que le débit en lecture s'améliore toutes les quelques minutes jusqu'à ce que les ressources de votre instance soient saturées ou que vous ne puissiez ajouter aucune nouvelle limite de division (car votre division ne couvre qu'une seule ligne sans enfants entrelacés).
Exemples de schéma
Les exemples de schéma ci-dessous montrent comment créer des tables parentes et enfants avec et sans entrelacement, et illustrent les mises en page physiques correspondantes des données.
Créer une table parente
Supposons que vous créiez une application musicale et que vous ayez besoin d'une table simple (ci-dessous) qui stocke des lignes de données concernant des chanteurs.
Notez que la table contient une colonne de clé primaire, SingerId, qui apparaît à gauche de la ligne en gras, et que les tables sont organisées par lignes et par colonnes.
Vous pouvez définir la table avec un schéma Spanner comme celui-ci:
GoogleSQL
CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY (SingerId);
PostgreSQL
CREATE TABLE singers ( singer_id BIGINT PRIMARY KEY, first_name VARCHAR(1024), last_name VARCHAR(1024), singer_info BYTEA );
Veuillez noter ce qui suit à propos de l'exemple de schéma :
Singersest une table située à la racine de la hiérarchie de la base de données (car elle n'est pas définie comme un enfant entrelacé d'une autre table).- Pour les bases de données utilisant le dialecte GoogleSQL, les colonnes de clé primaire sont généralement annotées avec
NOT NULL(bien que vous puissiez omettre cette annotation si vous souhaitez autoriser les valeursNULLdans les colonnes de clé). Pour en savoir plus, consultez la section Colonnes clés. - Les colonnes qui ne sont pas incluses dans la clé primaire sont appelées colonnes "non clés" et peuvent comporter l'annotation facultative
NOT NULL. - Les colonnes qui utilisent le type
STRINGouBYTESdans GoogleSQL doivent être définies avec une longueur qui représente le nombre maximal de caractères Unicode pouvant être stockés dans le champ. La spécification de la longueur est facultative pour les types PostgreSQLvarcharetcharacter varying. Pour en savoir plus, consultez les pages Types de données scalaires pour les bases de données utilisant le dialecte GoogleSQL et types de données PostgreSQL pour les bases de données basées sur le dialecte PostgreSQL.
À quoi ressemble la disposition physique des lignes dans la table Singers ? Le schéma suivant montre les lignes de la table Singers stockées par clé primaire ("Singers(1)", puis "Singers(2)", etc., où le nombre entre parenthèses correspond à la valeur de la clé primaire.
Le schéma précédent illustre un exemple de limite de division entre les lignes associées à Singers(3) et Singers(4). Les données des divisions obtenues sont attribuées à différents serveurs. À mesure que cette table s'agrandit, il est possible que des lignes de données Singers soient stockées à différents emplacements.
Créer des tables parents et enfants
Supposons que vous souhaitiez maintenant ajouter des données de base sur les albums de chaque chanteur à l'application musicale.
Notez que la clé primaire de la table Albums est composée de deux colonnes : SingerId et AlbumId, pour associer chaque album à son chanteur. L'exemple de schéma suivant définit les tables Albums et Singers à la racine de la hiérarchie de la base de données, ce qui en fait des tables sœurs.
-- Schema hierarchy: -- + Singers (sibling table of Albums) -- + Albums (sibling table of Singers)
GoogleSQL
CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY (SingerId); CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId);
PostgreSQL
CREATE TABLE singers ( singer_id BIGINT PRIMARY KEY, first_name VARCHAR(1024), last_name VARCHAR(1024), singer_info BYTEA ); CREATE TABLE albums ( singer_id BIGINT, album_id BIGINT, album_title VARCHAR, PRIMARY KEY (singer_id, album_id) );
La disposition physique des lignes de Singers et Albums ressemble au diagramme suivant, où les lignes de la table Albums sont stockées par une clé primaire contiguë, puis les lignes de Singers stockées par une clé primaire contiguë:
Remarque importante concernant le schéma : Spanner n'assume aucune relation de localité de données entre les tables Singers et Albums, car il s'agit de tables de niveau supérieur. À mesure que la base de données se développe, Spanner peut ajouter des limites de division entre toutes les lignes. Ainsi, les lignes de la table Albums peuvent se retrouver dans une division différente de celles de la table Singers, et les deux divisions peuvent exister indépendamment l'une de l'autre.
En fonction des besoins de votre application, il peut s'avérer judicieux de permettre aux données de la table Albums d'être placées dans des divisions différentes de celles où se trouvent les données de la table Singers. Toutefois, cela peut entraîner une perte de performances en raison de la nécessité de coordonner les lectures et les mises à jour sur des ressources distinctes. Si votre application doit fréquemment récupérer des informations sur tous les albums d'un chanteur particulier, vous devez créer Albums en tant que table enfant entrelacée de Singers, ce qui colocalise les lignes des deux tables en fonction de la dimension de clé primaire. L'exemple suivant explique cela plus en détail.
Créer des tables entrelacées
Une table entrelacée est une table que vous déclarez comme enfant entrelacée d'une autre table, car vous souhaitez que les lignes de la table enfant soient stockées physiquement avec la ligne parente associée. Comme indiqué précédemment, la clé primaire de la table parente doit être la première partie de la clé primaire composite de la table enfant.
Lors de la conception de votre application musicale, supposons que vous vous rendiez compte qu'elle doit accéder fréquemment aux lignes de la table Albums lorsqu'elle accède à une ligne Singers. Par exemple, lorsque vous accédez à la ligne Singers(1), vous devez également accéder aux lignes Albums(1, 1) et Albums(1, 2). Dans ce cas, Singers et Albums doivent avoir une forte relation de localité des données.
Vous pouvez déclarer cette relation de localité des données en créant Albums en tant que table enfant entrelacée de Singers.
-- Schema hierarchy: -- + Singers -- + Albums (interleaved table, child table of Singers)
La ligne en gras du schéma suivant montre comment créer Albums en tant que table entrelacée de Singers.
GoogleSQL
CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY (SingerId); CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
PostgreSQL
CREATE TABLE singers ( singer_id BIGINT PRIMARY KEY, first_name VARCHAR(1024), last_name VARCHAR(1024), singer_info BYTEA ); CREATE TABLE albums ( singer_id BIGINT, album_id BIGINT, album_title VARCHAR, PRIMARY KEY (singer_id, album_id) ) INTERLEAVE IN PARENT singers ON DELETE CASCADE;
Remarques sur ce schéma :
SingerId, qui est la première partie de la clé primaire de la table enfantAlbums, est également la clé primaire de sa table parenteSingers.- L'annotation
ON DELETE CASCADEsignifie que lorsqu'une ligne de la table parente est supprimée, ses lignes enfants le sont également automatiquement. Si une table enfant ne comporte pas cette annotation ou si l'annotation afficheON DELETE NO ACTION, vous devez supprimer les lignes enfants avant de pouvoir supprimer la ligne parente. - Les lignes entrelacées sont d'abord triées par ligne de la table parente, puis par lignes contiguës de la table enfant qui partagent la clé primaire du parent. Par exemple, "Singers(1)", "Albums(1, 1)", "Albums(1, 2)", et ainsi de suite.
- La relation de localité des données de chaque chanteur et les données de son album sont conservées en cas de division de cette base de données, à condition que la taille d'une ligne
Singerset de toutes ses lignesAlbumsreste inférieure à la limite de taille de fractionnement et qu'il n'y ait aucun hotspot dans ces lignesAlbums. - La ligne parent doit exister pour que vous puissiez insérer des lignes enfants. La ligne parent peut soit déjà exister dans la base de données, soit être ajoutée avant l'insertion des lignes enfants dans la même transaction.
Créer une hiérarchie de tables entrelacées
La relation parent-enfant entre les tables Singers et Albums peut être étendue à davantage de tables descendantes. Par exemple, vous pouvez créer une table entrelacée appelée Songs en tant qu'enfant de la table Albums pour stocker la liste des pistes de chaque album.
Songs doit avoir une clé primaire qui inclut toutes les clés primaires des tables supérieures dans la hiérarchie, c'est-à-dire SingerId et AlbumId.
-- Schema hierarchy: -- + Singers -- + Albums (interleaved table, child table of Singers) -- + Songs (interleaved table, child table of Albums)
GoogleSQL
CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), ) PRIMARY KEY (SingerId); CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE; CREATE TABLE Songs ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, TrackId INT64 NOT NULL, SongName STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId, TrackId), INTERLEAVE IN PARENT Albums ON DELETE CASCADE;
PostgreSQL
CREATE TABLE singers ( singer_id BIGINT PRIMARY KEY, first_name VARCHAR(1024), last_name VARCHAR(1024), singer_info BYTEA ); CREATE TABLE albums ( singer_id BIGINT, album_id BIGINT, album_title VARCHAR, PRIMARY KEY (singer_id, album_id) ) INTERLEAVE IN PARENT singers ON DELETE CASCADE; CREATE TABLE songs ( singer_id BIGINT, album_id BIGINT, track_id BIGINT, song_name VARCHAR, PRIMARY KEY (singer_id, album_id, track_id) ) INTERLEAVE IN PARENT albums ON DELETE CASCADE;
Le schéma suivant représente une vue physique des lignes entrelacées.
Dans cet exemple, à mesure que le nombre de chanteurs augmente, Spanner ajoute des limites de division entre les chanteurs afin de préserver la localité des données entre un chanteur et les données de son album et de son titre. Toutefois, si la taille d'une ligne de chanteur et de ses lignes enfants dépasse la limite de taille de fractionnement, ou si un hotspot est détecté dans les lignes enfants, Spanner tente d'ajouter des limites de division pour isoler cette ligne ainsi que toutes les lignes enfants en dessous.
En résumé, une table parent, et toutes ses tables enfants et descendantes, forment une hiérarchie de tables dans le schéma. Bien que chaque table de la hiérarchie soit logiquement indépendante, le fait de les entrelacer physiquement de cette manière peut améliorer les performances. En effet, les tables sont préjointes, ce qui vous permet d'accéder ensemble aux lignes associées tout en minimisant les accès au stockage.
Jointures avec des tables entrelacées
Si possible, joignez des données dans des tables entrelacées par clé primaire. Étant donné que chaque ligne entrelacée est généralement stockée physiquement dans la même division que sa ligne parente, Spanner peut effectuer des jointures par clé primaire localement, ce qui réduit l'accès au stockage et le trafic réseau. Dans l'exemple suivant, Singers et Albums sont joints à la clé primaire SingerId.
GoogleSQL
SELECT s.FirstName, a.AlbumTitle FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
PostgreSQL
SELECT s.first_name, a.album_title FROM singers AS s JOIN albums AS a ON s.singer_id = a.singer_id;
Colonnes de clé
Cette section contient des remarques sur les colonnes clés.
Modifier les clés de la table
Les clés d'une table ne peuvent pas changer ; vous ne pouvez ni ajouter, ni supprimer une colonne de clé dans une table existante.
Stocker des valeurs NULL dans une clé primaire
En GoogleSQL, si vous souhaitez stocker des valeurs NULL dans une colonne de clé primaire, omettez la clause NOT NULL pour cette colonne dans le schéma. (Les bases de données basées sur le dialecte PostgreSQL ne prennent pas en charge les valeurs NULL dans une colonne de clé primaire.)
Voici un exemple d'omission de la clause NOT NULL dans la colonne de clé primaire SingerId. Notez que comme SingerId est la clé primaire, une seule ligne peut stocker NULL dans cette colonne.
CREATE TABLE Singers ( SingerId INT64, FirstName STRING(1024), LastName STRING(1024), ) PRIMARY KEY (SingerId);
La propriété pouvant accepter la valeur Null de la colonne de clé primaire doit correspondre aux déclarations des tables parents et enfants. Dans cet exemple, NOT NULL n'est pas autorisé pour la colonne Albums.SingerId, car Singers.SingerId l'omet.
CREATE TABLE Singers ( SingerId INT64, FirstName STRING(1024), LastName STRING(1024), ) PRIMARY KEY (SingerId); CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
Types non autorisés
Les colonnes suivantes ne peuvent pas être de type ARRAY:
- Les colonnes de clé d'une table
- Les colonnes de clé d'un index
Concevoir pour l'architecture mutualisée
Vous pouvez implémenter l'architecture mutualisée si vous stockez des données appartenant à différents clients. Par exemple, un service de musique peut vouloir stocker le contenu de chaque maison de disques séparément.
Architecture mutualisée classique
La méthode classique de conception d'une architecture mutualisée consiste à créer une base de données distincte pour chaque client. Dans cet exemple, chaque base de données possède sa propre table Singers :
| SingerId | FirstName | LastName |
|---|---|---|
| 1 | Marc | Richards |
| 2 | Catalina | Smith |
| SingerId | FirstName | LastName |
|---|---|---|
| 1 | Alice | Trentor |
| 2 | Gabriel | Wright |
| SingerId | FirstName | LastName |
|---|---|---|
| 1 | Benjamin | Martinez |
| 2 | Hannah | Harris |
Architecture mutualisée gérée par schéma
Une autre façon de concevoir une architecture mutualisée dans Spanner consiste à regrouper tous les clients dans une seule table d'une même base de données et à utiliser une valeur de clé primaire différente pour chaque client. Par exemple, vous pouvez inclure une colonne de clé CustomerId dans vos tables. Si vous faites de CustomerId la première colonne de clé, les données de chaque client ont une bonne localité. Spanner peut ensuite utiliser efficacement des divisions de base de données pour maximiser les performances en fonction de la taille des données et des modèles de charge. Dans l'exemple suivant, il existe une seule table Singers pour tous les clients:
| CustomerId | SingerId | FirstName | LastName |
|---|---|---|---|
| 1 | 1 | Marc | Richards |
| 1 | 2 | Catalina | Smith |
| 2 | 1 | Alice | Trentor |
| 2 | 2 | Gabriel | Wright |
| 3 | 1 | Benjamin | Martinez |
| 3 | 2 | Hannah | Harris |
Si vous devez disposer de bases de données distinctes pour chaque locataire, vous devez prendre en compte les contraintes suivantes :
- Le nombre de bases de données par instance, ainsi que le nombre de tables et d'index par base de données, sont limités. Selon le nombre de clients, il n'est peut-être pas possible d'avoir des bases de données ou des tables distinctes.
- L'ajout de nouvelles tables et d'index non entrelacés peut prendre beaucoup de temps. Vous ne pourrez peut-être pas obtenir les performances souhaitées si la conception de votre schéma dépend de l'ajout de nouvelles tables et de nouveaux index.
Si vous souhaitez créer des bases de données séparées, il sera peut-être plus efficace de répartir vos tables sur plusieurs bases de données de sorte que chaque base de données subisse peu de modifications de schéma par semaine.
Si vous créez des tables et des index distincts pour chaque client de votre application, ne placez pas toutes les tables et tous les index dans la même base de données. Répartissez-les plutôt sur de nombreuses bases de données afin de minimiser les problèmes de performances liés à la création d'un grand nombre d'index.
Pour en savoir plus sur d'autres modèles de gestion de données et la conception d'applications pour l'architecture mutualisée, consultez la page Implémenter la mutualisation dans Spanner.