Panoramica degli schemi

Questa pagina illustra gli schemi e introduce tabelle con interleaving, che possono migliorare le prestazioni delle query durante l'esecuzione di query sulle 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 nelle righe ed è possibile definire indici secondari su una o più colonne. Se vuoi aggiornare o eliminare le righe esistenti in una tabella, questa deve avere una chiave primaria. Una tabella senza colonne di chiave primaria può avere una sola riga. Solo i database dialetto SQL possono avere tabelle senza una chiave primaria.

In Spanner i dati sono di tipo forte. Devi definire uno schema per ogni database e questo schema deve specificare il tipo di dati di ogni colonna di ogni tabella. I tipi di dati includono tipi scalari e complessi, descritti in Tipi di dati in GoogleSQL e Tipi di dati di PostgreSQL.

Relazioni tabella padre-figlio

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

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

Per associare una tabella figlio a una padre, devi utilizzare un DDL che dichiari la tabella figlio come con interleaving nella tabella padre e includere la chiave primaria della tabella padre come prima parte della chiave primaria composita della tabella figlio. Per ulteriori informazioni sull'interleaving, consulta Creare tabelle con interleaving più avanti in questo argomento.

Le chiavi esterne sono una soluzione padre-figlio più generale e sono adatte a ulteriori casi d'uso. Non sono limitate 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 collocazione 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 saperne di più sulle chiavi esterne e sul relativo confronto con le tabelle con interleaving, consulta la panoramica sulle chiavi esterne.

Scelta di una chiave primaria

Spesso l'applicazione dispone già di un campo che può essere utilizzato come chiave primaria. Ad esempio, per una tabella Customers, potrebbe essere presente una tabella CustomerId fornita dall'applicazione che gestisce la chiave primaria. In altri casi, potresti dover generare una chiave primaria quando inserisci la riga. In genere si tratta di un valore intero univoco senza significatività aziendale (una chiave primaria surogata).

In ogni caso, fai attenzione a non creare hotspot scegliendo la chiave primaria. Ad esempio, se inserisci record con un numero intero in crescita monotonica come chiave, inserirai sempre i record alla fine dello spazio della chiave. Questo è indesiderato perché Spanner suddivide i dati tra i server in base a intervalli di chiavi, il che significa che gli insert verranno indirizzati a un singolo server, creando un hotspot. Esistono tecniche che possono distribuire il carico su più server ed evitare hotspot:

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

Aggiunta di indici secondari basati sulle chiavi primarie

In determinate circostanze, l'utilizzo del database può trarre vantaggio dall'aggiunta di indici secondari basati sulle chiavi primarie. Ciò è particolarmente vero se esegui spesso query che richiedono scansioni 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 figlio con interleaving di un'altra, questa 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 chiave primaria, con le righe figlio inserite tra le righe padre. Vedi un'illustrazione delle righe con interleaving in Creare tabelle con interleaving più avanti in questo argomento.

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

Suddivisione dei database

Puoi definire gerarchie di relazioni padre-figlio con interleaving fino a sette livelli, il che significa che puoi 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 di risorse di un singolo server? Spanner è un database distribuito. Ciò significa che man mano che il database cresce, Spanner suddivide i dati in blocchi chiamati "split". Le singole suddivisioni possono spostarsi in modo indipendente l'una dall'altra e essere assegnate a server diversi, che possono trovarsi in località fisiche diverse. Una suddivisione contiene un intervallo di righe contigue. Le chiavi di inizio e fine di questo intervallo sono chiamate "confini della suddivisione". Spanner aggiunge e rimuove automaticamente i confini della suddivisione in base alle dimensioni e al carico, modificando il numero di sezioni nel database.

Suddivisione basata sul carico

Come esempio di come Spanner esegue la suddivisione basata sul carico per ridurre gli hotspot di lettura, supponiamo che il database contenga una tabella con 10 righe lette più spesso di tutte le altre righe della tabella. Spanner può aggiungere confini della suddivisione tra ciascuna di queste 10 righe in modo che siano gestite ciascuna 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 migliori a intervalli di pochi minuti fino a quando non saturano le risorse nell'istanza o quando non puoi aggiungere nuovi confini della suddivisione (perché hai una suddivisione che copre solo una riga senza elementi secondari con interleaving).

Esempi di schemi

Gli esempi di schema riportati di seguito mostrano come creare tabelle padre e figlio con e senza interleaving e illustrano i layout fisici corrispondenti dei dati.

Crea una tabella padre

Supponiamo che tu stia creando un'applicazione musicale e che ti serva una semplice tabella in cui sono memorizzate le righe di dati dei cantanti:

Tabella dei cantanti con 5 righe e 4 colonne. SingerID è la prima colonna.

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 questo:

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 sullo schema di esempio:

Singers è una tabella nella radice della gerarchia del database (perché non è definita come elemento figlio con interleaving di un'altra tabella).
Per i database dialetti di GoogleSQL, le colonne di chiave primaria sono in genere annotate con NOT NULL (anche se puoi omettere questa annotazione se vuoi consentire valori NULL nelle colonne chiave). Per ulteriori informazioni, consulta Colonne chiave.
Le colonne non 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 Scala per i database dialetti SQL e Tipi di dati PostgreSQL per i database dialetto PostgreSQL.

Che aspetto ha 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 mostra un esempio di limite 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 diverse località.

Creare tabelle padre e figlio

Supponi 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, rendendole 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 di Singers e Albums è simile al seguente schema, con le righe della tabella Albums archiviate da una chiave primaria contigua, poi le righe di Singers archiviate da una chiave primaria contigua:

Una nota importante sullo schema è che Spanner non presume 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 le righe. Ciò significa che le righe della tabella Albums potrebbero trovarsi in una suddivisione diversa rispetto alle righe della tabella Singers e le due suddivisioni potrebbero spostarsi in modo indipendente l'una dall'altra.

A seconda delle esigenze della tua applicazione, potrebbe essere possibile consentire l'individuazione dei dati Albums in suddivisioni diverse rispetto ai dati Singers. Tuttavia, ciò potrebbe comportare una riduzione delle prestazioni dovuta alla necessità di coordinare le letture e gli aggiornamenti tra le risorse distinte. Se la tua applicazione deve recuperare spesso informazioni su tutti gli album di un determinato cantante, devi creare Albums come tabella secondaria con interleaving di Singers, che posiziona le righe delle due tabelle lungo la dimensione della chiave primaria. Il prossimo esempio spiega questo aspetto in modo più dettagliato.

Creare tabelle con interleaving

Una tabella con interleaving è una tabella che dichiari di essere figlio con interleaving di un'altra tabella perché vuoi che le righe della tabella con interleaving vengano 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 dell'applicazione musicale, supponi di renderti conto che l'app deve accedere di frequente 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 forte relazione di località dei dati. Puoi dichiarare questa relazione di località dei dati creando Albums come tabella figlio 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 figlio. Se una tabella figlio 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 della tabella padre. Ad esempio, "Singers(1)", "Album(1, 1)", "Album(1, 2)" e così via.
La relazione tra le località dei dati di ogni cantante e i dati del relativo album viene preservata in caso di suddivisione del database, a condizione che le dimensioni di una riga Singers e di tutte le relative righe Albums rimangano al di sotto del limite delle dimensioni di suddivisione e che non esista un hotspot in nessuna di queste righe Albums.
La riga padre 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, potresti creare una tabella con interleaving denominata Songs come elemento secondario di Albums per memorizzare l'elenco di tracce di ogni album:

Tabella delle canzoni con 6 righe e 4 colonne. Le tre colonne più a sinistra
sono la chiave primaria.

Songs deve avere una chiave primaria che includa tutte le chiavi primarie delle tabelle di livello superiore 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 visualizzazione fisica di righe con interleaving.

Visualizzazioni fisiche
delle righe: negli album i brani vengono interfoliati tra loro

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 di un cantante e i relativi dati dell'album e del brano. Tuttavia, se le dimensioni di una riga cantante e delle relative righe secondarie superano il limite di dimensioni della 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 secondarie sottostanti.

Riassumendo, una tabella padre insieme a tutte le relative tabelle figlio e discendenti forma una gerarchia di tabelle nello schema. Sebbene ogni tabella nella gerarchia sia logicamente indipendente, l'interfoliazione fisica delle tabelle in questo modo può migliorare le prestazioni, pre unendo efficacemente le tabelle e consentendoti 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 archiviata fisicamente nella stessa suddivisione della riga padre, Spanner può eseguire join in base alla chiave primaria localmente, 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 delle tabelle

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.

Memorizzazione 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 dialetto 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, in quella colonna può esserci una sola riga in cui è possibile archiviare NULL.

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

La proprietà con valore null della colonna di chiave primaria deve corrispondere alle dichiarazioni della tabella padre e di quella figlio. In questo esempio, NOT NULL per la colonna Albums.SingerId non è consentito perché Singers.SingerId la 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.

Progetta per l'architettura multi-tenancy

Se stai archiviando dati che appartengono a clienti diversi, potresti voler implementare l'architettura multi-tenancy. Ad esempio, un servizio musicale potrebbe voler archiviare separatamente i contenuti di ogni singola casa discografica.

Multitenancy classica

Il modo classico di progettare per la multitenancy è 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	Richards
2	Catalina	Smith

Database 2: record Cama
SingerId	FirstName	LastName
1	Alice	Trentor
2	Gabriele	Wright

Database 3: record Eagan
SingerId	FirstName	LastName
1	Federico	Martinez
2	Anna	Harris

Multitenancy gestito da schema

Un altro modo di progettare per l'architettura multi-tenancy in Spanner è quello di avere tutti i clienti in una singola tabella in un singolo database e di utilizzare un valore di chiave primaria diverso per ogni cliente. Ad esempio, potresti includere una colonna chiave CustomerId nelle tue tabelle. Se imposti CustomerId come prima colonna chiave, i dati per ogni cliente hanno una buona località. Spanner può quindi utilizzare in modo efficace le suddivisioni dei database per massimizzare le prestazioni in base alle dimensioni dei dati e ai pattern di carico. Nel seguente esempio, è presente una singola tabella Singers per tutti i clienti:

Database multi-tenancy Spanner
CustomerId	SingerId	FirstName	LastName
1	1	Marco	Richards
1	2	Catalina	Smith
2	1	Alice	Trentor
2	2	Gabriele	Wright
3	1	Federico	Martinez
3	2	Anna	Harris

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

Esistono dei 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 ottenere risultati migliori se distribuisci le tabelle tra i database in modo che ogni database abbia un numero ridotto di modifiche allo schema a settimana.

Se crei tabelle e indici separati per ogni cliente dell'applicazione, non mettere tutte le tabelle e gli indici nello stesso database. Suddividili invece in molti database per ridurre i problemi di prestazioni con la creazione di un numero elevato di indici.

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