Chiavi esterne

Questo documento descrive le chiavi esterne in Spanner e come utilizzarle per applicare l'integrità referenziale nel database. I seguenti argomenti ti aiutano a scoprire di più sulle chiavi esterne e su come utilizzarle:

• Panoramica delle chiavi esterne in Spanner
• Tipi di chiavi esterne
• Confronto dei chiave esterna esterne
• Scegli il tipo di chiave esterna da utilizzare
• Utilizza chiavi esterne applicate
• Utilizza chiavi esterne informative
• Indici di supporto
• Modifiche dello schema a lunga esecuzione

Panoramica delle chiavi esterne in Spanner

Le chiavi esterne definiscono le relazioni tra le tabelle. Puoi utilizzare le chiavi esterne per assicurarti che l'integrità dei dati di queste relazioni in Spanner venga mantenuta.

Immagina di essere lo sviluppatore principale di un'attività di e-commerce. Stai progettando un database per elaborare gli ordini dei clienti. Il database deve archiviare informazioni su ogni ordine, cliente e prodotto. La Figura 1 mostra la struttura di base del database per l'applicazione.

Figura 1. Diagramma di un database di elaborazione degli ordini

Definisci una tabella Customers per archiviare i dati dei clienti, una tabella Orders per monitorare tutti gli ordini e una tabella Products per archiviare le informazioni su ogni prodotto.

La Figura 1 mostra anche i link tra le tabelle che corrispondono alle seguenti relazioni reali:

• Un cliente effettua un ordine.

• Viene effettuato un ordine per un prodotto.

Decidi che il tuo database applichi le seguenti regole per garantire che gli ordini nel tuo sistema siano validi.

• Non puoi creare un ordine per un cliente che non esiste.

• Un cliente non può effettuare un ordine per un prodotto che non hai.

Quando applichi queste regole o vincoli, mantieni l'integrità referenziale dei tuoi dati. Quando un database mantiene l'integrità referenziale, tutti i tentativi di aggiungere dati non validi, che comporterebbero link o riferimenti non validi tra i dati, non vanno a buon fine. L'integrità referenziale previene gli errori degli utenti. Per impostazione predefinita, Spanner utilizza le chiavi esterne per applicare l'integrità referenziale.

Definisci l'integrità referenziale con le chiavi esterne

Di seguito viene esaminato nuovamente l'esempio di elaborazione degli ordini, con ulteriori dettagli aggiunti al design, come mostrato nella Figura 2.

Figura 2. Diagramma di uno schema di database con chiavi esterne

Il progetto ora mostra i nomi e i tipi di colonna in ogni tabella. La tabella Orders definisce anche due relazioni con chiave esterna. FK_CustomerOrder prevede che tutte le righe in Orders abbiano un CustomerId valido. La chiave esterna FK_ProductOrder prevede che tutti i valori ProductId nella tabella Orders siano validi. La tabella seguente mappa questi vincoli alle regole del mondo reale che vuoi applicare.

Nome chiave esterna	Vincolo	Descrizione reale
FK_CustomerOrder	Si aspetta che tutte le righe in Orders abbiano un valore CustomerId valido	Un cliente valido effettua un ordine
FK_ProductOrder	Si aspetta che tutte le righe in Orders abbiano un valore ProductId valido	È stato effettuato un ordine per un prodotto valido

Spanner applica i vincoli specificati utilizzando le chiavi esterne applicate. Ciò significa che Spanner non esegue alcuna transazione che tenta di inserire o aggiornare una riga nella tabella Orders che ha un CustomerId o ProductId non trovato nelle tabelle Customers e Products. Inoltre, non riesce a eseguire le transazioni che tentano di aggiornare o eliminare righe nelle tabelle Customers e Products che invaliderebbero gli ID nella tabella Orders. Per maggiori dettagli su come Spanner convalida i vincoli, consulta la sezione Convalida dei vincoli delle transazioni.

A differenza delle chiavi esterne applicate, Spanner non convalida i vincoli sulle chiavi esterne informative. Ciò significa che se utilizzi una chiave esterna informativa in questo scenario, una transazione che tenta di inserire o aggiornare una riga nella tabella Orders che ha un CustomerId o ProductId non trovato nelle tabelle Customers e Products non viene convalidata e la transazione non ha esito negativo. Inoltre, a differenza delle chiavi esterne applicate, le chiavi esterne informative sono supportate solo da GoogleSQL e non da PostgreSQL.

Caratteristiche delle chiavi esterne

Di seguito è riportato un elenco delle caratteristiche delle chiavi esterne in Spanner.

• La tabella che definisce la chiave esterna è la tabella di riferimento e le colonne della chiave esterna sono le colonne di riferimento.

• La chiave esterna fa riferimento alle colonne di riferimento della tabella di riferimento.

• Come nell'esempio, puoi assegnare un nome a ogni vincolo di chiave esterna. Se non specifichi un nome, Spanner ne genera uno per te. Puoi eseguire query sul nome generato da INFORMATION_SCHEMA di Spanner. I nomi dei vincoli hanno come ambito lo schema, insieme ai nomi delle tabelle e degli indici, e devono essere univoci all'interno dello schema.

• Il numero di colonne di riferimento e di quelle a cui viene fatto riferimento deve essere lo stesso. L'ordine è importante. Ad esempio, la prima colonna di riferimento fa riferimento alla prima colonna di riferimento e la seconda colonna di riferimento fa riferimento alla seconda colonna di riferimento.

• Una colonna di riferimento e la relativa controparte di riferimento devono essere dello stesso tipo. Devi essere in grado di indicizzare le colonne.

• Non puoi creare chiavi esterne su colonne con l'opzione allow_commit_timestamp=true.

• Le colonne di array non sono supportate.

• Le colonne JSON non sono supportate.

• Una chiave esterna può fare riferimento alle colonne della stessa tabella (una chiave esterna autoreferenziale). Un esempio è una tabella Employee con una colonna ManagerId che fa riferimento alla colonna EmployeeId della tabella.

• Le chiavi esterne possono anche formare relazioni circolari tra tabelle in cui due tabelle fanno riferimento l'una all'altra, direttamente o indirettamente. La tabella di riferimento deve esistere prima di creare una chiave esterna. Ciò significa che almeno una delle chiavi esterne deve essere aggiunta utilizzando l'istruzione ALTER TABLE.

• Le chiavi di riferimento devono essere univoche. Spanner utilizza la PRIMARY KEY della tabella di riferimento se le colonne di riferimento per una chiave esterna corrispondono alle colonne della chiave primaria della tabella di riferimento. Se Spanner non può utilizzare la chiave primaria della tabella di riferimento, crea un UNIQUE NULL_FILTERED INDEX sulle colonne di riferimento.

• Le chiavi esterne non utilizzano gli indici secondari che hai creato. Invece, creano i propri indici di supporto. Gli indici di supporto sono utilizzabili nelle valutazioni delle query, anche nelle istruzioni force_index esplicite. Puoi eseguire query sui nomi degli indici di supporto da INFORMATION_SCHEMA di Spanner. Per saperne di più, consulta Indici di supporto.

Tipi di chiavi esterne

Esistono due tipi di chiavi esterne: applicate e informative. Le chiavi esterne applicate sono quelle predefinite e applicano l'integrità referenziale. Le chiavi esterne informative non applicano l'integrità referenziale e sono utilizzate al meglio per dichiarare il modello dei dati logico previsto per l'ottimizzazione delle query. Per maggiori dettagli, consulta le seguenti sezioni sulle chiavi esterne applicate e informative e la tabella di confronto dei chiave esterna esterne.

Chiavi esterne applicate

Le chiavi esterne applicate, il tipo di chiave esterna predefinito in Spanner, applicano l'integrità referenziale. Poiché le chiavi esterne applicate applicano l'integrità referenziale, i tentativi di eseguire le seguenti operazioni non andranno a buon fine:

• L'aggiunta di una riga a una tabella di riferimento con un valore di chiave esterna che non esiste nella tabella di riferimento non va a buon fine.

• L'eliminazione di una riga da una tabella di riferimento a cui fanno riferimento le righe della tabella di riferimento non va a buon fine.

Tutte le chiavi esterne PostgreSQL vengono applicate. Le chiavi esterne GoogleSQL vengono applicate per impostazione predefinita. Poiché le chiavi esterne vengono applicate per impostazione predefinita, l'utilizzo della parola chiave ENFORCED per specificare che una chiave esterna GoogleSQL è applicata è facoltativo.

Chiavi esterne informative

Le chiavi esterne informative vengono utilizzate per dichiarare il modello dei dati logico previsto per l'ottimizzazione delle query. Sebbene le chiavi della tabella di riferimento debbano essere univoche per le chiavi esterne informative, l'integrità referenziale non viene applicata. Se vuoi convalidare in modo selettivo l'integrità referenziale quando utilizzi chiavi esterne informative, devi gestire la logica di convalida sul lato client. Per saperne di più, consulta Utilizza chiavi esterne informative.

Utilizza la parola chiave NOT ENFORCED per specificare che una chiave esterna GoogleSQL è informativa. PostgreSQL non supporta le chiavi esterne informative.

Confronto dei tipi di chiave esterna

Entrambe le modalità, Applicata e Informativa, presentano dei vantaggi. Le sezioni seguenti confrontano i due tipi di chiavi esterne e includono alcune best practice.

Differenze di alto livello tra le chiave esterna

A livello generale, di seguito sono riportate alcune delle differenze tra le chiavi esterne applicate e informative:

• Applicazione forzata. Le chiavi esterne applicate convalidano e garantiscono l'integrità referenziale nelle scritture. Le chiavi esterne informative non convalidano né garantiscono l'integrità referenziale.

• Spazio di archiviazione. Le chiavi esterne applicate potrebbero richiedere spazio di archiviazione aggiuntivo per l'indice di supporto nella tabella vincolata.

• Throughput di scrittura. Le chiavi esterne applicate potrebbero comportare un overhead maggiore nel percorso di scrittura rispetto alle chiavi esterne informative.

• Ottimizzazione delle query. Entrambi i tipi di chiavi esterne possono essere utilizzati per l'ottimizzazione delle query. Quando l'ottimizzatore è autorizzato a utilizzare chiavi esterne informative, i risultati delle query potrebbero non riflettere i dati effettivi se questi non corrispondono alle relazioni delle chiave esterna informative (ad esempio, se alcune chiavi vincolate non hanno chiavi di riferimento corrispondenti nella tabella di riferimento).

Tabella delle differenze delle chiavi esterne

La seguente tabella elenca le differenze dettagliate tra le chiavi esterne applicate e quelle informative:

	Chiavi esterne applicate	Chiavi esterne informative
Parole chiave	ENFORCED	NOT ENFORCED
Supportato da GoogleSQL	Sì. Le chiavi esterne in GoogleSQL vengono applicate per impostazione predefinita.	Sì.
Supportato da PostgreSQL	Sì. Le chiavi esterne in PostgreSQL possono essere solo applicate.	No.
Archiviazione	Le chiavi esterne applicate richiedono spazio di archiviazione per un massimo di due indici di supporto.	Le chiavi esterne informative richiedono spazio di archiviazione per un massimo di un indice di supporto.
Crea indici di supporto sulle colonne della tabella di riferimento, se necessario	Sì.	Sì.
Crea indici di supporto sulle colonne della tabella di riferimento quando necessario	Sì.	No.
Supporto delle azioni delle chiavi esterne	Sì.	No.
Convalida e applica l'integrità referenziale	Sì.	No. L'assenza di convalida migliora le prestazioni di scrittura, ma può influire sui risultati delle query quando vengono utilizzate chiavi esterne informative per l'ottimizzazione delle query. Puoi utilizzare la convalida lato client o una chiave esterna applicata per garantire l'integrità referenziale.

Scegli il tipo di chiave esterna da utilizzare

Puoi utilizzare le seguenti linee guida per decidere quale tipo di chiave esterna utilizzare:

Ti consigliamo di iniziare con le chiavi esterne applicate. Le chiavi esterne applicate mantengono sempre coerenti i dati e il modello logico. Le chiavi esterne applicate sono l'opzione consigliata, a meno che non siano adatte al tuo caso d'uso.

Ti consigliamo di prendere in considerazione le chiavi esterne informative se si verificano tutte le seguenti condizioni:

• Vuoi utilizzare il modello dei dati logico descritto dalla chiave esterna informativa nell'ottimizzazione delle query.

• Mantenere una rigorosa integrità referenziale non è pratico o influisce in modo significativo sulle prestazioni. Di seguito sono riportati alcuni esempi di casi in cui potresti voler prendere in considerazione l'utilizzo di una chiave esterna informativa:

  • L'origine dati upstream segue un modello di coerenza finale. In questo caso, gli aggiornamenti apportati nel sistema di origine potrebbero non essere applicati immediatamente in Spanner. Poiché gli aggiornamenti potrebbero non essere immediati, potrebbero verificarsi brevi incoerenze nelle relazioni con le chiave esterna.

  • I tuoi dati contengono righe di riferimento con un numero elevato di relazioni di riferimento. Gli aggiornamenti di queste righe possono utilizzare molte risorse perché Spanner deve convalidare o, in alcuni casi, eliminare tutte le righe correlate al mantenimento dell'integrità referenziale. In questo scenario, gli aggiornamenti potrebbero influire sulle prestazioni di Spanner e rallentare le transazioni simultanee.

• La tua applicazione può gestire potenziali incoerenze dei dati e il loro impatto sui risultati delle query.

Utilizza chiavi esterne informative

I seguenti argomenti riguardano solo le chiavi esterne informative. Per gli argomenti che si applicano sia alle chiavi esterne informative che a quelle applicate, consulta quanto segue:

• Modifiche dello schema a lunga esecuzione
• Indici di supporto

Crea una nuova tabella con una chiave esterna informativa

Puoi creare e rimuovere le chiavi esterne informative dal tuo database Spanner utilizzando le istruzioni DDL. Puoi aggiungere chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Allo stesso modo, puoi aggiungere o rimuovere chiavi esterne da una tabella esistente con l'istruzione ALTER TABLE.

L'esempio seguente crea una nuova tabella con una chiave esterna informativa utilizzando GoogleSQL. Le chiavi esterne informative non sono supportate da PostgreSQL.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductId INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
   REFERENCES Customers (CustomerId) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL

Not Supported

Per altri esempi di come creare e gestire le chiavi esterne, consulta Crea e gestisci relazioni con le chiave esterna esterne. Per saperne di più sulle istruzioni DDL, consulta il riferimento DDL.

Nota: puoi anche aggiungere e rimuovere chiavi esterne utilizzando la console Google Cloud . Seleziona Modifica come testo dall'interfaccia di creazione della tabella e inserisci il DDL per gestire le chiavi esterne per la tabella.

Utilizza chiavi esterne informative per l'ottimizzazione delle query

Sia le chiavi esterne applicate che le chiavi esterne informative possono essere utilizzate da Query Optimizer per migliorare le prestazioni delle query. L'utilizzo di chiavi esterne informative consente di sfruttare i piani di query ottimizzati senza l'overhead dell'applicazione rigorosa dell'integrità referenziale.

Se abiliti l'ottimizzatore delle query per utilizzare le informazioni delle chiavi esterne informative, è importante comprendere che la correttezza dell'ottimizzazione dipende dalla presenza di dati coerenti con il modello logico descritto dalle chiavi esterne informative. Se esistono incoerenze, i risultati delle query potrebbero non riflettere i dati effettivi. Un esempio di incoerenza è quando un valore in una colonna vincolata non ha un valore corrispondente in una colonna di riferimento.

Per impostazione predefinita, l'ottimizzatore delle query utilizza le chiavi esterne NOT ENFORCED. Per modificare questa impostazione, imposta l'opzione del database use_unenforced_foreign_key_for_query_optimization su false. Di seguito è riportato un esempio di GoogleSQL che lo dimostra (le chiavi esterne informative non sono disponibili in PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

L'hint dell'istruzione di query booleana @{use_unenforced_foreign_key} esegue l'override dell'opzione del database per singola query che controlla se l'ottimizzatore utilizza le chiavi esterne NOT ENFORCED. La disattivazione di questo suggerimento o dell'opzione del database può essere utile per risolvere i problemi relativi ai risultati delle query imprevisti. Di seguito viene mostrato come utilizzare @{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Utilizza chiavi esterne applicate

I seguenti argomenti riguardano solo le chiavi esterne applicate. Per gli argomenti che si applicano sia alle chiavi esterne informative che a quelle applicate, consulta quanto segue:

• Modifiche dello schema a lunga esecuzione
• Indici di supporto

Crea una nuova tabella con una chiave esterna applicata

Puoi creare e rimuovere chiavi esterne applicate dal tuo database Spanner utilizzando il DDL. Puoi aggiungere chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere o rimuovere una chiave esterna da una tabella esistente con l'istruzione ALTER TABLE.

Puoi creare e rimuovere le chiavi esterne dal database Spanner utilizzando il DDL. Puoi aggiungere chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere o rimuovere una chiave esterna da una tabella esistente con l'istruzione ALTER TABLE.

Di seguito è riportato un esempio di creazione di una nuova tabella con una chiave esterna applicata.

GoogleSQL

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
CustomerId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId),
PRIMARY KEY (OrderId)
);

Per altri esempi di come creare e gestire le chiavi esterne, consulta Crea e gestisci relazioni con le chiave esterna esterne.

Nota: puoi anche aggiungere e rimuovere chiavi esterne utilizzando la console Google Cloud . Seleziona Modifica come testo dall'interfaccia di creazione della tabella e inserisci il DDL per gestire le chiavi esterne per la tabella.

Azioni per le chiavi esterne

Le azioni delle chiavi esterne possono essere definite solo su chiavi esterne applicate.

Le azioni della chiave esterna controllano cosa succede alla colonna vincolata quando la colonna a cui fa riferimento viene eliminata o aggiornata. Spanner supporta l'utilizzo delle azioni ON DELETE CASCADE e ON DELETE SET NULL.

Con l'azione ON DELETE CASCADE, quando elimini una riga a cui fa riferimento una chiave esterna, vengono eliminate anche tutte le righe che fanno riferimento a quella chiave nella stessa transazione.

Con l'azione ON DELETE SET NULL, quando elimini una riga a cui fa riferimento una chiave esterna, le colonne di riferimento in tutte le righe che fanno riferimento a quella chiave vengono impostate su NULL nella stessa transazione.

Puoi aggiungere una chiave esterna con un'azione quando crei il database utilizzando DDL. Utilizza l'istruzione CREATE TABLE per aggiungere chiavi esterne con un'azione a una nuova tabella. Analogamente, puoi utilizzare l'istruzione ALTER TABLE per aggiungere un'azione di chiave esterna a una tabella esistente o per rimuovere un'azione di chiave esterna. Di seguito è riportato un esempio di come creare una nuova tabella con un'azione di chiave esterna.

GoogleSQL

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

Di seguito è riportato un elenco delle caratteristiche delle azioni delle chiave esterna in Spanner.

• Le azioni della chiave esterna sono ON DELETE CASCADE, ON DELETE SET NULL o ON DELETE NO ACTION.

• Non puoi utilizzare ON DELETE SET NULL su colonne di riferimento che fanno parte della chiave primaria della tabella di riferimento, contrassegnate con NOT NULL o colonne generate.

• Puoi eseguire una query su INFORMATION_SCHEMA per trovare i vincoli di chiave esterna che hanno un'azione.

• L'aggiunta di un'azione di chiave esterna a un vincolo di chiave esterna esistente non è supportata. Devi aggiungere un nuovo vincolo di chiave esterna con un'azione.

Convalida dei vincoli

La convalida dei vincoli si applica solo alle chiavi esterne applicate.

Spanner convalida i vincoli di chiave esterna applicati quando viene eseguito il commit di una transazione o quando gli effetti delle scritture vengono resi visibili alle operazioni successive nella transazione.

Un valore inserito nella colonna di riferimento viene confrontato con i valori della tabella di riferimento e delle colonne di riferimento. Le righe con valori di riferimento NULL non vengono controllate, il che significa che puoi aggiungerle alla tabella di riferimento.

Spanner convalida tutti i vincoli referenziali di chiave esterna applicati quando tenta di aggiornare i dati utilizzando istruzioni DML o un'API. Se un vincolo non è valido, viene eseguito il rollback di tutte le modifiche in attesa.

La convalida avviene immediatamente dopo ogni istruzione DML. Ad esempio, devi inserire la riga di riferimento prima di inserire le righe di riferimento. Quando utilizzi un'API di mutazione, le mutazioni vengono memorizzate nel buffer fino al commit della transazione. La convalida della chiave esterna applicata viene posticipata fino al commit della transazione. In questo caso, è consentito inserire prima le righe di riferimento.

Ogni transazione viene valutata per verificare la presenza di modifiche che influiscono sui vincoli di chiave esterna applicati. Queste valutazioni potrebbero richiedere ulteriori richieste al server. Gli indici di supporto richiedono anche un tempo di elaborazione aggiuntivo per valutare le modifiche alle transazioni e per mantenere gli indici. Per ogni indice è necessario anche spazio di archiviazione aggiuntivo.

Azione di eliminazione a cascata a lunga esecuzione

Quando elimini una riga da una tabella di riferimento, Spanner deve eliminare tutte le righe nelle tabelle di riferimento che fanno riferimento alla riga eliminata. Ciò può portare a un effetto a cascata, in cui una singola operazione di eliminazione comporta migliaia di altre operazioni di eliminazione. L'aggiunta di un vincolo di chiave esterna con l'azione di eliminazione a cascata a una tabella o la creazione di una tabella con vincoli di chiave esterna con l'azione di eliminazione a cascata può rallentare le operazioni di eliminazione.

Azione di eliminazione a lunga esecuzione con impostazione di un valore null

Quando elimini una riga da una tabella di riferimento, Spanner aggiorna tutte le righe nelle tabelle di riferimento che hanno un'azione ON DELETE SET NULL impostando le colonne di riferimento su NULL. Ciò può portare a un effetto a cascata, in cui una singola operazione di eliminazione comporta migliaia di operazioni di aggiornamento. L'aggiunta di un vincolo di chiave esterna ON DELETE SET NULL a una tabella o la creazione di una tabella con un vincolo di questo tipo può rallentare le operazioni di eliminazione.

Limite di mutazioni superato per l'eliminazione a cascata della chiave esterna

L'eliminazione di un numero elevato di record utilizzando un'eliminazione a cascata di chiave esterna può influire sulle prestazioni. Questo perché ogni record eliminato richiama l'eliminazione di tutti i record correlati. Se devi eliminare un numero elevato di record utilizzando un'eliminazione a cascata della chiave esterna, elimina esplicitamente le righe dalle tabelle secondarie prima di eliminare la riga dalle tabelle principali. In questo modo, la transazione non avrà esito negativo a causa del limite di mutazioni.

Limite di mutazioni superato per l'impostazione di un valore null per l'eliminazione di una chiave esterna

L'eliminazione di righe che attivano un numero elevato di aggiornamenti utilizzando un'azione di chiave esterna ON DELETE SET NULL può influire sulle prestazioni. Questo accade perché ogni riga eliminata nella tabella di riferimento attiva l'aggiornamento di tutte le righe che fanno riferimento a questa riga nella tabella di riferimento. Se devi eliminare una riga che attiva molti aggiornamenti utilizzando ON DELETE SET NULL, aggiorna esplicitamente le righe di riferimento impostandole su NULL o eliminale prima di eliminare la riga di riferimento. Questa pratica impedisce che la transazione non vada a buon fine a causa del limite di mutazioni.

Utilizzo delle risorse per le azioni di eliminazione delle chiave esterna

ON DELETE CASCADE e ON DELETE SET NULL hanno costi di risorse e mutazioni diversi a seconda dello schema:

• Mutazioni della tabella di base: ON DELETE SET NULL aggiorna le singole colonne, quindi ogni riga di riferimento aggiornata viene conteggiata come più mutazioni (per la chiave di riga e ogni colonna impostata su NULL). ON DELETE CASCADE viene conteggiata come una mutazione per riga eliminata o una mutazione per intervallo di chiavi eliminato quando le colonne di riferimento sono un prefisso della chiave primaria della tabella di riferimento.
• Mutazioni dell'indice: entrambe le azioni utilizzano una mutazione di eliminazione per riga sull'indice di supporto della chiave esterna. Per altri indici secondari nella tabella di riferimento, ON DELETE SET NULL modifica solo gli indici che includono le colonne di riferimento, mentre ON DELETE CASCADE elimina le voci da ogni indice secondario nella tabella.
• Letture: ON DELETE SET NULL legge sempre l'indice di supporto della chiave esterna per trovare le righe corrispondenti. ON DELETE CASCADE può saltare questa lettura ed eliminare direttamente un intervallo di chiavi quando le colonne di riferimento sono un prefisso della chiave primaria della tabella di riferimento.
• A cascata: ON DELETE SET NULL non viene mai applicato alle tabelle figlio con interleaving perché le colonne della chiave primaria non possono essere impostate su NULL. Attiva ulteriori azioni solo se un'altra chiave esterna fa riferimento alla colonna aggiornata. ON DELETE CASCADE elimina l'intera riga di riferimento, quindi si applica anche in modo congiunto a tutte le tabelle secondarie con interleaving con ON DELETE CASCADE e alle chiavi esterne downstream.

Confronto tra chiavi esterne applicate e interfoliazione delle tabelle

L'interfoliazione delle tabelle di Spanner è una buona scelta per molte relazioni padre-figlio in cui la chiave primaria della tabella figlio include le colonne di chiave primaria della tabella padre. La collocazione di righe figlio con le rispettive righe padre può offrire un miglioramento significativo delle prestazioni.

Le chiavi esterne sono una soluzione padre-figlio più generica 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 sia come figlio in altre. Tuttavia, una relazione di chiave esterna non implica la collocazione delle tabelle nel livello di archiviazione.

Considera un esempio che utilizza una tabella Orders definita come segue:

Figura 3. Diagramma dello schema del database con chiavi esterne applicate

Il design della Figura 3 presenta alcune limitazioni. Ad esempio, ogni ordine può contenere un solo articolo.

Immagina che i tuoi clienti vogliano poter ordinare più di un prodotto per ordine. Puoi migliorare il design introducendo una tabella OrderItems che contiene una voce per ogni prodotto ordinato dal cliente. Puoi introdurre un'altra chiave esterna applicata per rappresentare questa nuova relazione one-to-many tra Orders e OrderItems. Tuttavia, sai anche che spesso vuoi eseguire query su ordini e sui rispettivi articoli. Poiché la co-localizzazione di questi dati migliora le prestazioni, ti consigliamo di creare la relazione padre-figlio utilizzando la funzionalità di interfoliazione delle tabelle di Spanner.

Ecco come definire la tabella OrderItems, con l'interfoliazione di Orders.

GoogleSQL

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

La Figura 4 è una rappresentazione visiva dello schema del database aggiornato in seguito all'introduzione di questa nuova tabella, OrderItems, con interleaving con Orders. Qui puoi anche vedere la relazione one-to-many tra queste due tabelle.

Immagine 4. Aggiunta di una tabella OrderItems con interleaving

In questa configurazione, puoi avere più voci OrderItems in ogni ordine e le voci OrderItems per ogni ordine sono intercalate e, di conseguenza, collocate insieme agli ordini. L'interfoliazione fisica di Orders e OrderItems in questo modo può migliorare le prestazioni, pre-unendo in modo efficace le tabelle e consentendoti di accedere alle righe correlate insieme, riducendo al minimo gli accessi al disco. Ad esempio, Spanner può eseguire join per chiave primaria localmente, riducendo al minimo l'accesso al disco e il traffico di rete.

Se il numero di mutazioni in una transazione supera 80.000, la transazione non va a buon fine. Queste eliminazioni a cascata di grandi dimensioni funzionano bene per le tabelle con una relazione "interleaved in parent", ma non per le tabelle con una relazione di chiave esterna. Se hai una relazione di chiave esterna e devi eliminare un numero elevato di righe, devi prima eliminare esplicitamente le righe dalle tabelle secondarie.

Se hai una tabella utente con una relazione di chiave esterna con un'altra tabella e l'eliminazione di una riga dalla tabella di riferimento attiva l'eliminazione di milioni di righe, devi progettare lo schema con un'azione di eliminazione a cascata con "interfoliazione nel padre".

Nota: Google consiglia di scegliere di rappresentare le relazioni padre-figlio come tabelle con interleaving o come chiavi esterne, ma non entrambe. L'utilizzo di entrambi è ridondante e potrebbe consumare risorse di archiviazione e di calcolo aggiuntive per verificare i vincoli e mantenere gli indici di supporto.

Tabella di confronto

La seguente tabella riassume il confronto tra le chiavi esterne applicate e l'interfoliazione delle tabelle. Puoi utilizzare queste informazioni per decidere cosa è meglio per il tuo design.

Tipo di relazione principale-secondario	Interfoliazione delle tabelle	Chiavi esterne applicate
Può utilizzare le chiavi primarie	Sì	Sì
Può utilizzare colonne non di chiave primaria	No	Sì
Numero di genitori supportati	0 .. 1	0 .. N
Archivia i dati principali e secondari insieme	Sì	No
Supporta l'eliminazione a cascata	Sì	Sì
Modalità di corrispondenza null	La verifica ha esito positivo se tutti i valori di riferimento non sono distinti dai valori a cui viene fatto riferimento. I valori null non sono distinti dai valori null; i valori null sono distinti dai valori non null.	Passa se i valori di riferimento sono nulli. Passa se tutti i valori di riferimento non sono nulli e la tabella di riferimento ha una riga con valori uguali ai valori di riferimento. Non passa se non è stata trovata alcuna riga corrispondente.
Tempi di applicazione	Per operazione quando utilizzi l'API di mutazione. Per istruzione quando utilizzi DML.	Per transazione quando utilizzi l'API Mutation. Per istruzione quando utilizzi DML.
Può essere rimosso	No. L'interfoliazione delle tabelle non può essere rimossa dopo la creazione, a meno che non elimini l'intera tabella secondaria.	Sì

Indici di supporto

Le chiavi esterne non utilizzano indici creati dall'utente. ma creano i propri indici di supporto. Le chiavi esterne applicate e informative creano indici di supporto in modo diverso in Spanner:

• Per le chiavi esterne applicate, Spanner può creare fino a due indici di supporto secondari per ogni chiave esterna, uno per le colonne di riferimento e un secondo per le colonne a cui viene fatto riferimento.

• Per le chiavi esterne informative, Spanner può creare fino a un indice di supporto, se necessario, per le colonne di riferimento. Le chiavi esterne informative non creano un indice di supporto per le colonne di riferimento.

Sia per le chiavi esterne applicate che per quelle informative, una chiave esterna di solito fa riferimento alle chiavi primarie della tabella di riferimento, quindi in genere non è necessario un indice per la tabella di riferimento. Per questo motivo, le chiavi esterne informative in genere non hanno indici di supporto. Se necessario, l'indice di supporto creato per la tabella di riferimento è un indice UNIQUE NULL_FILTERED. La creazione della chiave esterna non va a buon fine se i dati esistenti violano il vincolo di unicità dell'indice.

Le chiavi esterne informative non hanno un indice di supporto per la tabella di riferimento. Per le chiavi esterne applicate, l'indice di supporto per la tabella di riferimento è NULL_FILTERED.

Se due o più chiavi esterne richiedono lo stesso indice di supporto, Spanner crea un singolo indice per ciascuna. Gli indici di supporto vengono eliminati quando vengono eliminate le chiavi esterne che li utilizzano. Non puoi modificare o eliminare gli indici di supporto.

Spanner utilizza lo schema di informazioni di ogni database per archiviare i metadati relativi agli indici di supporto. Le righe all'interno di INFORMATION_SCHEMA.INDEXES che hanno un valore SPANNER_IS_MANAGED di true descrivono gli indici di supporto.

Al di fuori delle query SQL che richiamano direttamente lo schema delle informazioni, la consoleGoogle Cloud non visualizza alcuna informazione sugli indici di supporto di un database.

Modifiche allo schema a lunga esecuzione

L'aggiunta di una chiave esterna applicata a una tabella esistente o la creazione di una nuova tabella con una chiave esterna può comportare operazioni a lunga esecuzione. Nel caso di una nuova tabella, questa non è scrivibile fino al completamento dell'operazione a lunga esecuzione.

La tabella seguente mostra cosa succede in Spanner quando una chiave esterna applicata e una informativa si trovano in una tabella nuova o esistente:

Tipo di tabella	Chiave esterna applicata	Chiave esterna informativa
Nuovo	Spanner esegue il backfill degli indici di riferimento in base alle esigenze di ogni chiave esterna.	Spanner esegue il backfill degli indici di riferimento in base alle esigenze di ogni chiave esterna.
Esistente	Spanner esegue il backfill degli indici di riferimento e di quelli a cui viene fatto riferimento, se necessario. Spanner convalida anche i dati esistenti nella tabella per assicurarsi che rispettino il vincolo di integrità referenziale della chiave esterna. La modifica dello schema non va a buon fine se i dati non sono validi.	Spanner esegue il backfill dell'indice di riferimento in base alle esigenze e non convalida i dati esistenti nella tabella.

Le seguenti funzionalità non sono supportate:

• Aggiunta di un'azione di chiave esterna a un vincolo di chiave esterna applicato esistente.
• Modifica dell'applicazione di una chiave esterna esistente.

In entrambi i casi, ti consigliamo di procedere come segue:

1. Aggiungi un nuovo vincolo con l'azione o l'applicazione richiesta.
2. Elimina il vecchio vincolo.

L'aggiunta di un nuovo vincolo e l'eliminazione del vecchio vincolo impediscono un problema di operazione di modifica del vincolo a lunga esecuzione. Ad esempio, supponiamo che tu voglia aggiungere un'azione DELETE CASCADE a una chiave esterna esistente. Dopo aver creato la nuova chiave esterna con l'azione ON DELETE CASCADE, l'effetto di entrambi i vincoli è un'azione DELETE CASCADE. A questo punto, puoi eliminare il vecchio vincolo in sicurezza.

L'eliminazione di un vincolo può comportare l'eliminazione degli indici di supporto della chiave esterna se non vengono utilizzati da altri vincoli di chiave esterna. Per questo motivo, se elimini prima il vecchio vincolo, l'aggiunta dello stesso vincolo di chiave esterna con un'azione successiva potrebbe comportare operazioni a lunga esecuzione, come il backfill degli indici, la convalida dei vincoli di indice univoci o la convalida dei vincoli referenziali della chiave esterna.

Nota: Se non si elimina il vincolo di chiave esterna precedente, è necessaria la convalida di vincoli di chiave esterna aggiuntivi, il che causa un sovraccarico non necessario. Inoltre, se una violazione del vincolo UNIQUE impedisce la creazione dell'indice di riferimento, l'aggiunta del nuovo vincolo potrebbe non riuscire.

Puoi eseguire query su INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE per verificare lo stato di creazione della chiave esterna.

Passaggi successivi

• Scopri di più sulla creazione e la gestione di relazioni con le chiave esterna esterne.

• Scopri di più sullo schema delle informazioni.