Panoramica degli schemi

Questa pagina illustra gli schemi e introduce le tabelle con interleaving, che possono migliorare le prestazioni delle query quando si eseguono query su tabelle in una relazione padre-figlio.

I database Spanner contengono una o più tabelle. Le tabelle sono strutturate come righe e colonne. Una o più colonne sono definite come chiave primaria della tabella, che identifica in modo univoco ogni riga. Le chiavi primarie sono sempre indicizzate per una ricerca rapida delle righe e puoi definire indici secondari su una o più colonne. Se vuoi aggiornare o eliminare righe esistenti in una tabella, la tabella deve avere una chiave primaria. Una tabella senza colonne di chiave primaria può avere una sola riga. Solo i database di dialetti GoogleSQL possono avere tabelle senza una chiave primaria.

I dati in Spanner sono con tipi elevati. Devi definire uno schema per ogni database, che deve specificare il tipo di dati di ogni colonna di ogni tabella. I tipi di dati includono quelli scalari e complessi, descritti in Tipi di dati in GoogleSQL e PostgreSQL.

Relazioni tabella padre-figlio

Esistono due modi per definire le relazioni padre-figlio in Spanner: interleaving delle tabelle e chiavi esterne.

L'interfoliazione delle tabelle di Spanner è una buona scelta per molte relazioni padre-figlio. Con l'interleaving, Spanner co-posiziona fisicamente le righe figlio con righe padre nello spazio di archiviazione. La colocation può migliorare significativamente le prestazioni. Ad esempio, se hai una tabella Customers e una tabella Invoices e l'applicazione recupera spesso tutte le fatture di un cliente, puoi definire Invoices come tabella secondaria con interfoliazione Customers. In questo modo dichiari una relazione di località dei dati tra due tabelle indipendenti. Stai dicendo a Spanner di archiviare una o più righe di Invoices con una riga Customers.

Per associare una tabella figlio a una tabella padre, utilizza il DDL che dichiara la tabella figlio come interlacciata nella tabella padre e includi la chiave primaria della tabella padre come prima parte della chiave primaria composita della tabella figlio. Per ulteriori informazioni sull'interfoliazione, vedi Creare tabelle con interleaving.

Le chiavi esterne sono una soluzione padre-figlio più generale e rispondono a ulteriori casi d'uso. Non si limitano alle colonne di chiave primaria e le tabelle possono avere più relazioni di chiave esterna, sia come padre in alcune relazioni che come figlio in altre. Tuttavia, una relazione di chiave esterna non implica la colocation delle tabelle nel livello di archiviazione.

Google consiglia di scegliere di rappresentare le relazioni padre-figlio come tabelle con interleaving o come chiavi esterne, ma non entrambe. Per ulteriori informazioni sulle chiavi esterne e sul loro confronto con le tabelle con interleaving, consulta Panoramica delle chiavi esterne.

Scelta di una chiave primaria

Spesso l'applicazione ha già un campo che può essere usato come chiave primaria. Ad esempio, per una tabella Customers, potrebbe esserci un elemento CustomerId fornito dall'applicazione che funge da chiave primaria. In altri casi, potrebbe essere necessario generare una chiave primaria quando inserisci la riga. In genere si tratta di un valore intero univoco privo di significato aziendale (una chiave primaria surrogata).

In ogni caso, devi fare attenzione a non creare hotspot con la scelta della tua chiave primaria. Ad esempio, se inserisci i record con un numero intero monotonico crescente come chiave, dovrai sempre inserirli alla fine dello spazio delle chiavi. Questo è indesiderato perché Spanner divide i dati tra i server in base a intervalli di chiavi, il che significa che i tuoi inserimenti saranno indirizzati a un singolo server, creando un hotspot. Esistono tecniche in grado di distribuire il carico su più server ed evitare gli hotspot:

• Esegui l'hashing della chiave e memorizzala in una colonna. Utilizza la colonna hash (o la colonna hash insieme alle colonne della chiave univoca) come chiave primaria.
• Inverti l'ordine delle colonne nella chiave primaria.
• Utilizza un UUID (Universally Unique Identifier). Si consiglia la versione 4 UUID perché utilizza valori casuali nei bit di ordine più alto. Non utilizzare un algoritmo UUID (ad esempio UUID versione 1) che memorizza il timestamp nei bit di ordine superiore.
• Esegui l'inversione dei bit.

Aggiunta di indici secondari in base alle chiavi primarie

In determinate circostanze, l'utilizzo del database può trarre vantaggio dall'aggiunta di indici secondari in base a chiavi primarie. Ciò è particolarmente vero se esegui spesso query che richiedono analisi in ordine inverso della chiave primaria di una tabella.

Chiavi primarie nelle tabelle con interleaving

Per l'interleaving, ogni tabella deve avere una chiave primaria. Se dichiari una tabella come secondaria con interleaving di un'altra, la tabella deve avere una chiave primaria composita che includa tutti i componenti della chiave primaria dell'elemento padre, nello stesso ordine e, in genere, una o più colonne di tabella figlio aggiuntive.

Spanner archivia le righe in ordine ordinato in base ai valori della chiave primaria, con righe figlio inserite tra le righe padre. Guarda un'illustrazione delle righe con interleaving in Creare tabelle con interleaving.

In sintesi, Spanner può collocare fisicamente righe di tabelle correlate. Gli esempi di schema mostrano l'aspetto di questo layout fisico.

Suddivisione del database

È possibile definire le gerarchie di relazioni padre-figlio con interfoliazione fino a sette livelli, il che significa che è possibile collocare righe di sette tabelle indipendenti. Se le dimensioni dei dati nelle tabelle sono ridotte, probabilmente un singolo server Spanner è in grado di gestire il database. Ma cosa succede quando le tabelle correlate crescono e iniziano a raggiungere i limiti delle risorse di un singolo server? Spanner è un database distribuito, il che significa che man mano che il database cresce, Spanner suddivide i dati in blocchi chiamati "split". I singoli segmenti possono spostarsi in modo indipendente l'uno dall'altro ed essere assegnati a server diversi, che possono trovarsi in località fisiche diverse. Una suddivisione contiene un intervallo di righe contigue. Le chiavi iniziale e finale di questo intervallo sono chiamate &quotconfini della suddivisionei". Spanner aggiunge e rimuove automaticamente i confini della suddivisione in base alle dimensioni e al carico, il che cambia il numero di split nel database.

Suddivisione basata sul carico

Per un esempio di come Spanner esegue la suddivisione basata sul carico per mitigare gli hotspot di lettura, supponiamo che il tuo database contenga una tabella con 10 righe che vengono lette più spesso di tutte le altre righe della tabella. Spanner può aggiungere confini della suddivisionee tra ciascuna di queste 10 righe in modo che ciascuna venga gestita da un server diverso, anziché consentire a tutte le letture di queste righe di consumare le risorse di un singolo server.

Come regola generale, se segui le best practice per la progettazione dello schema, Spanner può mitigare gli hotspot in modo che la velocità effettiva di lettura migliorare a intervalli di pochi minuti fino a quando non saturano le risorse nell'istanza o non si verificano casi in cui non è possibile aggiungere nuovi confini della suddivisione (perché hai una suddivisione che copre solo una singola riga senza elementi figlio interleali).

Esempi di schema

Gli esempi di schema nelle sezioni seguenti mostrano come creare tabelle padre e figlio con e senza interfoliazione e illustrano i layout fisici dei dati corrispondenti.

Crea una tabella padre

Supponiamo che tu stia creando un'applicazione di musica e di aver bisogno di una tabella che archivi righe di dati relativi al cantante:

Tieni presente che la tabella contiene una colonna di chiave primaria, SingerId, che appare a sinistra della riga in grassetto, e che le tabelle sono organizzate per righe e colonne.

Puoi definire la tabella con uno schema Spanner come il seguente:

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
);

Tieni presente quanto segue in merito allo schema di esempio:

• Singers è una tabella all'inizio della gerarchia del database (perché non è definita come elemento figlio con interfoliazione di un'altra tabella).
• Per i database di dialetti GoogleSQL, le colonne di chiave primaria sono in genere annotate con NOT NULL (anche se puoi omettere questa annotazione se vuoi consentire i valori NULL nelle colonne chiave. Per maggiori informazioni, consulta Colonne chiave.
• Le colonne che non sono incluse nella chiave primaria sono chiamate colonne non chiave e possono avere un'annotazione NOT NULL facoltativa.
• Le colonne che utilizzano il tipo STRING o BYTES in GoogleSQL devono essere definite con una lunghezza, che rappresenta il numero massimo di caratteri Unicode che possono essere archiviati nel campo. La specifica della lunghezza è facoltativa per i tipi PostgreSQL varchar e character varying. Per maggiori informazioni, consulta Tipi di dati Scalari per i database di dialetti GoogleSQL e Tipi di dati PostgreSQL per i database di dialetti PostgreSQL.

Qual è il layout fisico delle righe nella tabella Singers? Il seguente diagramma mostra le righe della tabella Singers archiviate dalla chiave primaria ("Singers(1)", poi "Singers(2)" e così via, dove il numero tra parentesi è il valore della chiave primaria.

Il diagramma precedente illustra un esempio di confine di suddivisione tra le righe associate a Singers(3) e Singers(4), con i dati delle suddivisioni risultanti assegnati a server diversi. Man mano che la tabella cresce, è possibile che le righe di dati Singers vengano archiviate in località diverse.

Creare tabelle padre e figlio

Supponiamo di voler aggiungere all'applicazione musicale alcuni dati di base sugli album di ogni cantante.

Tieni presente che la chiave primaria di Albums è composta da due colonne, SingerId e AlbumId, per associare ogni album al relativo cantante. Lo schema di esempio seguente definisce entrambe le tabelle Albums e Singers alla radice della gerarchia del database, il che le rende tabelle di pari livello.

-- 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)
);

Il layout fisico delle righe Singers e Albums è simile al seguente diagramma, con le righe della tabella Albums archiviate da una chiave primaria contigua e poi le righe Singers archiviate da una chiave primaria contigua:

Una nota importante sullo schema è che Spanner non presuppone alcuna relazione di località dei dati tra le tabelle Singers e Albums, poiché si tratta di tabelle di primo livello. Man mano che il database cresce, Spanner può aggiungere confini divisi tra qualsiasi riga. Ciò significa che le righe della tabella Albums potrebbero essere suddivise in una suddivisione diversa dalle righe della tabella Singers e i due segmenti potrebbero muoversi in modo indipendente l'uno dall'altro.

A seconda delle esigenze dell'applicazione, potrebbe essere opportuno consentire la posizione dei dati Albums su suddivisioni diverse dai dati Singers. Tuttavia, in questo caso potresti incorrere in una riduzione delle prestazioni dovuta alla necessità di coordinare le letture e gli aggiornamenti tra risorse distinte. Se la tua applicazione deve spesso recuperare informazioni su tutti gli album di un determinato cantante, devi creare Albums come tabella secondaria con interleaving di Singers, che colloca le righe delle due tabelle lungo la dimensione della chiave primaria. L'esempio seguente spiega questo aspetto in modo più dettagliato.

Creare tabelle con interleaving

Una tabella con interleaving è una tabella che dichiari come elemento figlio con interfoliazione di un'altra tabella perché vuoi che le righe della tabella figlio siano archiviate fisicamente con la riga padre associata. Come accennato in precedenza, la chiave primaria della tabella padre deve essere la prima parte della chiave primaria composita della tabella figlio.

Durante la progettazione della tua applicazione musicale, supponi di renderti conto che l'app deve accedere frequentemente alle righe della tabella Albums quando accede a una riga Singers. Ad esempio, quando accedi alla riga Singers(1), devi accedere anche alle righe Albums(1, 1) e Albums(1, 2). In questo caso, Singers e Albums devono avere una relazione forte tra le località dei dati. Puoi dichiarare questa relazione di località dei dati creando Albums come tabella secondaria con interleaving di Singers.

-- Schema hierarchy:
-- + Singers
--   + Albums (interleaved table, child table of Singers)

La riga in grassetto nello schema seguente mostra come creare Albums come tabella con interleaving di 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;

Note su questo schema:

• SingerId, che è la prima parte della chiave primaria della tabella figlio Albums, è anche la chiave primaria della relativa tabella padre Singers.
• L'annotazione ON DELETE CASCADE indica che quando viene eliminata una riga della tabella padre, vengono eliminate automaticamente anche le relative righe secondarie. Se una tabella secondaria non ha questa annotazione o l'annotazione è ON DELETE NO ACTION, devi eliminare le righe figlio prima di poter eliminare la riga padre.
• Le righe con interleaving vengono ordinate prima in base alle righe della tabella padre, poi in base alle righe contigue della tabella figlio che condividono la chiave primaria dell'elemento padre. Ad esempio, "Cantanti(1)", poi "Album(1, 1)" e infine "Album(1, 2)".
• La relazione di località dei dati di ogni cantante e i dati dei relativi album viene conservata se il database viene suddiviso, a condizione che la dimensione di una riga Singers e di tutte le sue righe Albums rimangano al di sotto del limite di dimensione suddiviso e che non sia presente alcun hotspot in nessuna di queste righe (Albums).
• La riga principale deve esistere prima di poter inserire righe figlio. La riga padre può già esistere nel database o può essere inserita prima dell'inserimento delle righe figlio nella stessa transazione.

Creare una gerarchia di tabelle con interleaving

La relazione padre-figlio tra Singers e Albums può essere estesa a più tabelle discendenti. Ad esempio, puoi creare una tabella con interleaving denominata Songs come elemento secondario di Albums per archiviare l'elenco delle tracce di ogni album:

Songs deve avere una chiave primaria che includa tutte le chiavi primarie delle tabelle più in alto nella gerarchia, ovvero SingerId e 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;

Il seguente diagramma rappresenta una vista fisica delle righe con interleaving.

In questo esempio, man mano che il numero di cantanti aumenta, Spanner aggiunge confini della suddivisione tra i cantanti per preservare la località dei dati tra un cantante e i dati dell'album e del brano. Tuttavia, se le dimensioni di una riga cantante e delle relative righe secondarie superano il limite di dimensioni di suddivisione o se viene rilevato un hotspot nelle righe figlio, Spanner tenta di aggiungere confini della suddivisione per isolare la riga dell'hotspot insieme a tutte le righe figlio sotto di essa.

In sintesi, una tabella padre, insieme a tutte le tabelle figlio e discendenti, forma una gerarchia di tabelle nello schema. Sebbene ogni tabella nella gerarchia sia indipendente da un punto di vista logico, l'interlesione fisica in questo modo può migliorare le prestazioni, eseguendo efficacemente il pre-unione delle tabelle e consentendo di accedere insieme alle righe correlate riducendo al minimo gli accessi allo spazio di archiviazione.

Join con tabelle con interleaving

Se possibile, unisci i dati in tabelle con interleaving in base alla chiave primaria. Poiché ogni riga con interleaving solitamente viene archiviata fisicamente nella stessa suddivisione della riga padre, Spanner può eseguire localmente i join mediante chiave primaria, riducendo al minimo l'accesso allo spazio di archiviazione e il traffico di rete. Nell'esempio seguente, Singers e Albums sono uniti nella chiave primaria 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;

Colonne chiave

Questa sezione include alcune note sulle colonne chiave.

Modifica delle chiavi della tabella

Le chiavi di una tabella non possono essere modificate. Non puoi aggiungere una colonna chiave a una tabella esistente o rimuovere una colonna chiave da una tabella esistente.

Archiviazione dei valori NULL in una chiave primaria

In GoogleSQL, se vuoi archiviare NULL in una colonna di chiave primaria, ometti la clausola NOT NULL per quella colonna nello schema. I database di dialetti PostgreSQL non supportano i valori NULL in una colonna di chiave primaria.

Ecco un esempio di omissione della clausola NOT NULL nella colonna di chiave primaria SingerId. Tieni presente che, poiché SingerId è la chiave primaria, può essere presente una sola riga che archivia NULL in quella colonna.

CREATE TABLE Singers (
  SingerId   INT64,
  FirstName  STRING(1024),
  LastName   STRING(1024),
) PRIMARY KEY (SingerId);

La proprietà null della colonna di chiave primaria deve corrispondere tra le dichiarazioni della tabella padre e quelle secondarie. In questo esempio, NOT NULL per la colonna Albums.SingerId non è consentito perché Singers.SingerId lo omette.

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;

Tipi non consentiti

Le seguenti colonne non possono essere di tipo ARRAY:

• Le colonne chiave di una tabella.
• Le colonne chiave di un indice.

Progettazione per l'architettura multi-tenancy

Potresti voler implementare la multitenancy se archivi dati che appartengono a clienti diversi. Ad esempio, un servizio musicale potrebbe voler archiviare i contenuti di ogni singola casa discografica.

Multitenancy classica

Il modo classico di progettare un'architettura multi-tenancy è creare un database separato per ogni cliente. In questo esempio, ogni database ha la propria tabella Singers:

Database 1: record Ackworth

SingerId	FirstName	LastName
1	Marco	Riccardo
2	Catalina	Smith

Database 2: record Cama

SingerId	FirstName	LastName
1	Alice	Trento
2	Gabriele	Wright

Database 3: record Eagan

SingerId	FirstName	LastName
1	Federico	Martinez
2	Anna	Harris

Multitenancy gestita da schema

Un altro modo per progettare l'architettura multi-tenancy in Spanner è avere tutti i clienti in una singola tabella in un unico database e utilizzare un valore chiave primaria diverso per ogni cliente. Ad esempio, puoi includere nelle tabelle una colonna chiave CustomerId. Se imposti CustomerId come prima colonna chiave, i dati per ogni cliente hanno una località corretta. Spanner può quindi utilizzare in modo efficace le suddivisioni del database per massimizzare le prestazioni in base alla dimensione dei dati e ai pattern di caricamento. Nell'esempio seguente, è presente una singola tabella Singers per tutti i clienti:

Database multi-tenancy di Spanner

CustomerId	SingerId	FirstName	LastName
1	1	Marco	Riccardo
1	2	Catalina	Smith
2	1	Alice	Trento
2	2	Gabriele	Wright
3	1	Federico	Martinez
3	2	Anna	Harris

Se devi avere database separati per ogni tenant, devi tenere presente i seguenti vincoli:

• Esistono limiti al numero di database per istanza e al numero di tabelle e indici per database. A seconda del numero di clienti, potrebbe non essere possibile avere database o tabelle separati.
• L'aggiunta di nuove tabelle e indici senza interleaving può richiedere molto tempo. Potresti non essere in grado di ottenere le prestazioni desiderate se la progettazione dello schema dipende dall'aggiunta di nuove tabelle e nuovi indici.

Se vuoi creare database separati, potresti avere più successo se distribuisci le tabelle tra i database in modo che ogni database abbia un basso numero di modifiche allo schema ogni settimana.

Se crei tabelle e indici separati per ciascun cliente della tua applicazione, non inserire tutte le tabelle e gli indici nello stesso database. Suddividili invece tra più database per mitigare i problemi di prestazioni causati dalla creazione di un elevato numero di indici.

Per saperne di più su altri pattern di gestione dei dati e sulla progettazione di applicazioni per l'architettura multi-tenancy, consulta Implementazione della multitenancy in Spanner