In un database Spanner crea automaticamente un indice
per la chiave primaria di ogni tabella. Ad esempio, non devi fare nulla per indicizzare la chiave primaria di Singers
, perché viene indicizzata automaticamente.
Puoi anche creare indici secondari per altre colonne. L'aggiunta di un indice secondario a una colonna rende più efficiente la ricerca dei dati nella colonna. Ad esempio, se devi cercare rapidamente un album per titolo, devi creare un indice secondario su AlbumTitle
, in modo che Spanner non debba eseguire la scansione dell'intera tabella.
Se la ricerca nell'esempio precedente viene eseguita all'interno di una transazione di lettura e scrittura,
la ricerca più efficiente evita anche di bloccare l'intera tabella,
il che consente inserimenti e aggiornamenti simultanei della tabella per le righe al di fuori dell'intervallo di ricerca
AlbumTitle
.
Oltre ai vantaggi che apportano alle ricerche, gli indici secondari possono anche aiutare Spanner a eseguire le scansioni in modo più efficiente, abilitando le scansioni degli indici anziché le analisi complete delle tabelle.
Spanner archivia i seguenti dati in ogni indice secondario:
- Tutte le colonne principali della tabella di base
- Tutte le colonne incluse nell'indice
- Tutte le colonne specificate nella clausola facoltativa
STORING
(database di dialetto GoogleSQL) o nella clausolaINCLUDE
(database di dialetto PostgreSQL) della definizione dell'indice.
Nel tempo, Spanner analizza le tabelle per garantire che gli indici secondari vengano utilizzati per le query appropriate.
Aggiungi un indice secondario
Il momento più efficiente per aggiungere un indice secondario è quando crei la tabella. Per creare contemporaneamente una tabella e i suoi indici, invia le istruzioni DDL per la nuova tabella e i nuovi indici in una singola richiesta a Spanner.
In Spanner, puoi anche aggiungere un nuovo indice secondario a una tabella esistente mentre il database continua a gestire il traffico. Come per qualsiasi altra modifica allo schema in Spanner, l'aggiunta di un indice a un database esistente non richiede la connessione del database e non blocca intere colonne o tabelle.
Ogni volta che viene aggiunto un nuovo indice a una tabella esistente, Spanner esegue automaticamente il backfill (o compila) dell'indice per riflettere una vista aggiornata dei dati indicizzati. Spanner gestisce questo processo di backfill per te, che viene eseguito in background utilizzando le risorse dei nodi a bassa priorità. Nella maggior parte dei casi non è possibile accelerare il processo (ad es. aggiungendo più nodi) e il backfill non influisce in modo significativo sulle prestazioni del database.
La creazione degli indici può richiedere da diversi minuti a molte ore. Poiché la creazione di un indice è un aggiornamento dello schema, è vincolata dagli stessi vincoli delle prestazioni di qualsiasi altro aggiornamento dello schema. Il tempo necessario per creare un indice secondario dipende da diversi fattori:
- La dimensione del set di dati
- La capacità di calcolo dell'istanza
- Il carico sull'istanza
Per visualizzare l'avanzamento di un processo di backfill dell'indice, consulta la sezione di avanzamento.
Tieni presente che l'utilizzo della colonna timestamp di commit come prima parte dell'indice secondario può creare hotspot e ridurre le prestazioni di scrittura.
Utilizza l'istruzione CREATE INDEX
per definire un indice secondario
nello schema. Ecco alcuni esempi:
Per indicizzare tutti i Singers
nel database in base al nome e al cognome:
GoogleSQL
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
PostgreSQL
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
Per creare un indice di tutti i valori Songs
nel database in base al valore SongName
:
GoogleSQL
CREATE INDEX SongsBySongName ON Songs(SongName);
PostgreSQL
CREATE INDEX SongsBySongName ON Songs(SongName);
Per indicizzare solo i brani di un determinato cantante, utilizza la clausola INTERLEAVE IN
per interfoliare l'indice nella tabella Singers
:
GoogleSQL
CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName),
INTERLEAVE IN Singers;
PostgreSQL
CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName)
INTERLEAVE IN Singers;
Per indicizzare solo i brani di un determinato album:
GoogleSQL
CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName),
INTERLEAVE IN Albums;
PostgreSQL
CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName)
INTERLEAVE IN Albums;
Per indicizzare in ordine decrescente di SongName
:
GoogleSQL
CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC),
INTERLEAVE IN Albums;
PostgreSQL
CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC)
INTERLEAVE IN Albums;
Tieni presente che l'annotazione DESC
precedente si applica solo a SongName
. Per indicizzare
in ordine decrescente le altre chiavi di indice, annotale anche con DESC
:
SingerId DESC, AlbumId DESC
.
Tieni inoltre presente che PRIMARY_KEY
è una parola riservata e non può essere utilizzata come nome
di un indice. Si tratta del nome assegnato allo pseudo-index creato quando viene creata una tabella con la specifica PRIMARY KEY
Per ulteriori dettagli e best practice per la scelta di indici senza interleaving e indici con interleaving, consulta Opzioni di indice e Utilizzare un indice con interleaving su una colonna il cui valore aumenta o diminuisce monotonicamente.
Controlla l'avanzamento del backfill dell'indice
Console
Nel menu di navigazione di Spanner, fai clic sulla scheda Operazioni. La pagina Operazioni mostra un elenco delle operazioni attualmente in esecuzione.
Trova l'operazione di backfill nell'elenco. Se è ancora in esecuzione, l'indicatore di avanzamento nella colonna Ora di fine mostra la percentuale di completamento dell'operazione, come mostrato nell'immagine seguente:
gcloud
Utilizza gcloud spanner operations describe
per controllare l'avanzamento di un'operazione.
Recupera l'ID operazione:
gcloud spanner operations list --instance=INSTANCE-NAME \ --database=DATABASE-NAME --type=DATABASE_UPDATE_DDL
Sostituisci quanto segue:
- INSTANCE-NAME con il nome istanza Spanner.
- DATABASE-NAME con il nome del database.
Note sull'utilizzo:
Per limitare l'elenco, specifica il flag
--filter
. Ad esempio:--filter="metadata.name:example-db"
elenca solo le operazioni su un database specifico.- In
--filter="error:*"
vengono elencate solo le operazioni di backup non riuscite.
Per informazioni sulla sintassi dei filtri, consulta gcloud topic usando i filtri. Per informazioni su come filtrare le operazioni di backup, consulta il campo
filter
in ListBackupOperationsRequest.Il flag
--type
non è sensibile alle maiuscole.
L'output è simile al seguente:
OPERATION_ID STATEMENTS DONE @TYPE _auto_op_123456 CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName) False UpdateDatabaseDdlMetadata CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName), INTERLEAVE IN Albums _auto_op_234567 True CreateDatabaseMetadata
Esegui
gcloud spanner operations describe
:gcloud spanner operations describe \ --instance=INSTANCE-NAME \ --database=DATABASE-NAME \ projects/PROJECT-NAME/instances/INSTANCE-NAME/databases/DATABASE-NAME/operations/OPERATION_ID
Sostituisci quanto segue:
- INSTANCE-NAME: il nome dell'istanza Spanner.
- DATABASE-NAME: il nome del database Spanner.
- PROJECT-NAME: il nome del progetto.
- OPERATION-ID: l'ID dell'operazione che vuoi controllare.
La sezione
progress
nell'output mostra la percentuale di completamento dell'operazione. L'output è simile al seguente:done: true ... progress: - endTime: '2021-01-22T21:58:42.912540Z' progressPercent: 100 startTime: '2021-01-22T21:58:11.053996Z' - progressPercent: 67 startTime: '2021-01-22T21:58:11.053996Z' ...
REST v1
Recupera l'ID operazione:
gcloud spanner operations list --instance=INSTANCE-NAME
--database=DATABASE-NAME --type=DATABASE_UPDATE_DDL
Sostituisci quanto segue:
- INSTANCE-NAME con il nome istanza Spanner.
- DATABASE-NAME con il nome del database.
Prima di utilizzare i dati della richiesta, effettua le seguenti sostituzioni:
- PROJECT-ID: l'ID progetto.
- INSTANCE-ID: l'ID istanza.
- DATABASE-ID: l'ID del database.
- OPERATION-ID: l'ID operazione.
Metodo HTTP e URL:
GET https://spanner.googleapis.com/v1/projects/PROJECT-ID/instances/INSTANCE-ID/databases/DATABASE-ID/operations/OPERATION-ID
Per inviare la richiesta, espandi una delle seguenti opzioni:
Dovresti ricevere una risposta JSON simile alla seguente:
{ ... "progress": [ { "progressPercent": 100, "startTime": "2023-05-27T00:52:27.366688Z", "endTime": "2023-05-27T00:52:30.184845Z" }, { "progressPercent": 100, "startTime": "2023-05-27T00:52:30.184845Z", "endTime": "2023-05-27T00:52:40.750959Z" } ], ... "done": true, "response": { "@type": "type.googleapis.com/google.protobuf.Empty" } }
Per gcloud
e REST, puoi trovare lo stato di avanzamento di ogni istruzione di backfill dell'indice nella sezione progress
. Per ogni istruzione, c'è un campo
corrispondente nell'array di istruzioni. L'ordine dell'array di avanzamento
corrisponde all'ordine della matrice delle istruzioni. Una volta disponibili, i campi startTime
, progressPercent
e endTime
vengono compilati di conseguenza.
Tieni presente che l'output non mostra un tempo stimato per il completamento dell'avanzamento
del backfill.
Se l'operazione richiede troppo tempo, puoi annullarla. Per maggiori informazioni, consulta Annullare la creazione dell'indice.
Scenari di visualizzazione dell'avanzamento del backfill dell'indice
Esistono diversi scenari che puoi incontrare quando cerchi di controllare l'avanzamento di un backfill dell'indice. Le istruzioni per la creazione degli indici che richiedono un backfill dell'indice fanno parte delle operazioni di aggiornamento dello schema e possono esistere diverse istruzioni che fanno parte di un'operazione di aggiornamento dello schema.
Il primo scenario è quello più semplice, ovvero quando l'istruzione di creazione dell'indice è la prima istruzione nell'operazione di aggiornamento dello schema. Poiché l'istruzione di creazione dell'indice è la prima istruzione, è la prima elaborata ed eseguita a causa dell'ordine di esecuzione.
Il campo startTime
dell'istruzione di creazione dell'indice verrà
compilato immediatamente con l'ora di inizio dell'operazione di aggiornamento dello schema. Successivamente, il campo progressPercent
dell'istruzione di creazione dell'indice viene compilato quando l'avanzamento del backfill dell'indice è superiore allo 0%. Infine, il campo endTime
viene compilato dopo il commit dell'istruzione.
Il secondo scenario si verifica quando l'istruzione di creazione dell'indice non è la prima nell'operazione di aggiornamento dello schema. Nessun campo relativo all'istruzione di creazione dell'indice verrà completato fino a quando le istruzioni precedenti non sono state confermate in base all'ordine di esecuzione.
Come nello scenario precedente, una volta eseguito il commit delle istruzioni precedenti, il campo startTime
dell'istruzione di creazione dell'indice viene compilato per primo, seguito dal campo progressPercent
. Infine, il campo endTime
viene compilato al termine
del commit dell'istruzione.
Annulla creazione indice
Puoi utilizzare Google Cloud CLI per annullare la creazione dell'indice. Per recuperare un elenco di operazioni di aggiornamento dello schema per un database Spanner, utilizza il comando gcloud spanner operations list
e includi l'opzione --filter
:
gcloud spanner operations list \
--instance=INSTANCE \
--database=DATABASE \
--filter="@TYPE:UpdateDatabaseDdlMetadata"
Trova il OPERATION_ID
relativo all'operazione da annullare, quindi usa il comando gcloud spanner operations cancel
per annullarla:
gcloud spanner operations cancel OPERATION_ID \
--instance=INSTANCE \
--database=DATABASE
Visualizza indici esistenti
Per visualizzare le informazioni sugli indici esistenti in un database, puoi utilizzare la console Google Cloud o Google Cloud CLI:
Console
Vai alla pagina Istanze di Spanner nella console Google Cloud.
Fai clic sul nome dell'istanza che vuoi visualizzare.
Nel riquadro a sinistra, fai clic sul database da visualizzare e poi sulla tabella da visualizzare.
Fai clic sulla scheda Indici. La console Google Cloud mostra un elenco di indici.
(Facoltativo) Per visualizzare i dettagli di un indice, ad esempio le colonne che include, fai clic sul suo nome.
gcloud
Usa il comando gcloud spanner databases ddl describe
:
gcloud spanner databases ddl describe DATABASE \
--instance=INSTANCE
L'interfaccia alla gcloud CLI stampa le istruzioni DDL (Data Definition Language) per creare le tabelle e gli indici del database. Le istruzioni CREATE
INDEX
descrivono gli indici esistenti. Ad
esempio:
--- |-
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX),
) PRIMARY KEY(SingerId)
---
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)
Query con un indice specifico
Le sezioni seguenti spiegano come specificare un indice in un'istruzione SQL e
con l'interfaccia di lettura per Spanner. Gli esempi in queste sezioni
presuppongono che tu abbia aggiunto una colonna MarketingBudget
alla tabella Albums
e
creato un indice denominato AlbumsByAlbumTitle
:
GoogleSQL
CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
MarketingBudget INT64,
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);
PostgreSQL
CREATE TABLE Albums (
SingerId BIGINT NOT NULL,
AlbumId BIGINT NOT NULL,
AlbumTitle VARCHAR,
MarketingBudget BIGINT,
PRIMARY KEY (SingerId, AlbumId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);
Specifica un indice in un'istruzione SQL
Quando esegui query SQL per eseguire query su una tabella Spanner, Spanner utilizza automaticamente tutti gli indici che potrebbero rendere la query più efficiente. Di conseguenza,
non devi specificare un indice per le query SQL. Tuttavia,
per le query fondamentali per il tuo carico di lavoro, Google consiglia di utilizzare
le istruzioni FORCE_INDEX
nelle istruzioni SQL per prestazioni più coerenti.
In alcuni casi, Spanner potrebbe scegliere un indice che causa l'aumento della latenza delle query. Se hai seguito i passaggi per la risoluzione dei problemi relativi alla regressione delle prestazioni e hai confermato che ha senso provare un indice diverso per la query, puoi specificare l'indice come parte della query.
Per specificare un indice in un'istruzione SQL, utilizza il suggerimento FORCE_INDEX
per fornire un'istruzione di indice. Le istruzioni di indice utilizzano la seguente sintassi:
GoogleSQL
FROM MyTable@{FORCE_INDEX=MyTableIndex}
PostgreSQL
FROM MyTable /*@ FORCE_INDEX = MyTableIndex */
Puoi anche utilizzare un'istruzione di indice per indicare a Spanner di eseguire la scansione della tabella di base invece di utilizzare un indice:
GoogleSQL
FROM MyTable@{FORCE_INDEX=_BASE_TABLE}
PostgreSQL
FROM MyTable /*@ FORCE_INDEX = _BASE_TABLE */
L'esempio seguente mostra una query SQL che specifica un indice:
GoogleSQL
SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo";
PostgreSQL
SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums /*@ FORCE_INDEX = AlbumsByAlbumTitle */
WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo';
Un'istruzione di indice potrebbe forzare il processore di query di Spanner a leggere altre colonne richieste dalla query, ma non archiviate nell'indice.
Il processore di query recupera queste colonne unendo l'indice e la tabella di base. Per evitare questo join aggiuntivo, utilizza una
clausola STORING
(database di dialetti GoogleSQL) o una clausola INCLUDE
(database di dialetti PostgreSQL) per
archiviare le colonne aggiuntive nell'indice.
Nell'esempio precedente, la colonna MarketingBudget
non è archiviata nell'indice, ma la query SQL seleziona questa colonna. Di conseguenza,
Spanner deve cercare la colonna MarketingBudget
nella tabella di base,
quindi unirla ai dati dell'indice per restituire i risultati della query.
Spanner genera un errore se l'istruzione di indice presenta uno dei seguenti problemi:
- L'indice non esiste.
- L'indice si trova su un'altra tabella di base.
- Nella query manca un'espressione di filtro
NULL
obbligatoria per un indiceNULL_FILTERED
.
I seguenti esempi mostrano come scrivere ed eseguire query che recuperano i valori di AlbumId
, AlbumTitle
e MarketingBudget
utilizzando l'indice AlbumsByAlbumTitle
:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Specifica un indice nell'interfaccia di lettura
Quando usi l'interfaccia di lettura per Spanner e vuoi che Spanner utilizzi un indice, devi specificare l'indice. L'interfaccia di lettura non seleziona l'indice automaticamente.
Inoltre, l'indice deve contenere tutti i dati visualizzati nei risultati della query, escluse le colonne che fanno parte della chiave primaria. Questa limitazione esiste perché l'interfaccia di lettura non supporta i join tra l'indice e la tabella di base. Se devi includere altre colonne nei risultati della query, hai alcune opzioni:
- Utilizza una clausola
STORING
oINCLUDE
per archiviare le colonne aggiuntive nell'indice. - Esegui la query senza includere le colonne aggiuntive, poi utilizza le chiavi primarie per inviare un'altra query che legga le colonne aggiuntive.
Spanner restituisce i valori dell'indice in ordine crescente per chiave indice. Per recuperare i valori in ordine decrescente:
Annota la chiave indice con
DESC
. Ad esempio:CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle DESC);
L'annotazione
DESC
si applica a una singola chiave di indice. Se l'indice include più di una chiave e vuoi che i risultati della query vengano visualizzati in ordine decrescente in base a tutte le chiavi, includi un'annotazioneDESC
per ogni chiave.Se la lettura specifica un intervallo di chiavi, assicurati che anche l'intervallo di chiavi sia in ordine decrescente. In altre parole, il valore della chiave iniziale deve essere maggiore del valore della chiave finale.
L'esempio seguente mostra come recuperare i valori di AlbumId
e
AlbumTitle
utilizzando l'indice AlbumsByAlbumTitle
:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Crea un indice per le scansioni solo dell'indice
Facoltativamente, puoi utilizzare la clausola STORING
(per i database di dialetti GoogleSQL) o la clausola INCLUDE
(per i database di dialetti PostgreSQL) per archiviare una copia di una colonna nell'indice. Questo tipo di indice offre vantaggi per le query e le chiamate di lettura che utilizzano l'indice, al costo dell'utilizzo di spazio di archiviazione aggiuntivo:
- Le query SQL che utilizzano l'indice e selezionano le colonne archiviate nella clausola
STORING
oINCLUDE
non richiedono un join aggiuntivo alla tabella di base. - Le chiamate
read()
che utilizzano l'indice possono leggere le colonne archiviate dalla clausolaSTORING
/INCLUDE
.
Ad esempio, supponi di aver creato una versione alternativa di AlbumsByAlbumTitle
che archivia una copia della colonna MarketingBudget
nell'indice (nota la
clausola STORING
o INCLUDE
in grassetto):
GoogleSQL
CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);
PostgreSQL
CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) INCLUDE (MarketingBudget);
Con l'indice AlbumsByAlbumTitle
precedente, Spanner deve unire l'indice alla tabella di base, quindi recuperare la colonna dalla tabella di base. Con il nuovo indice AlbumsByAlbumTitle2
, Spanner legge la colonna direttamente dall'indice, il che è più efficiente.
Se utilizzi l'interfaccia di lettura anziché SQL, il nuovo indice AlbumsByAlbumTitle2
ti consente anche di leggere direttamente la colonna MarketingBudget
:
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Modificare un indice
Puoi usare l'istruzione ALTER INDEX
per aggiungere altre colonne
a un indice esistente o per eliminare colonne. Questo
può aggiornare l'elenco di colonne definito dalla clausola STORING
(database di dialetti GoogleSQL) o dalla clausola INCLUDE
(database di dialetti PostgreSQL) quando crei
l'indice. Non puoi utilizzare questa istruzione per aggiungere o eliminare colonne dalla chiave di indice. Ad esempio, anziché creare un nuovo indice AlbumsByAlbumTitle2
, puoi utilizzare ALTER INDEX
per aggiungere una colonna in AlbumsByAlbumTitle
, come mostrato nell'esempio seguente:
GoogleSQL
ALTER INDEX AlbumsByAlbumTitle ADD STORED COLUMN MarketingBudget
PostgreSQL
ALTER INDEX AlbumsByAlbumTitle ADD INCLUDE COLUMN MarketingBudget
Quando aggiungi una nuova colonna a un indice esistente, Spanner utilizza un processo di backfill in background. Mentre il backfill è in corso, la colonna dell'indice non è leggibile, per cui potresti non ottenere il miglioramento previsto delle prestazioni. Puoi utilizzare il comando gcloud spanner operations
per elencare l'operazione a lunga esecuzione e visualizzarne lo stato.
Per ulteriori informazioni, consulta la sezione Descrivere l'operazione.
Puoi anche utilizzare l'opzione Annulla operazione per annullare un'operazione in esecuzione.
Al termine del backfill, Spanner aggiunge la colonna all'indice. Con l'aumento delle dimensioni dell'indice, le query che lo utilizzano potrebbero rallentare.
L'esempio seguente mostra come eliminare una colonna da un indice:
GoogleSQL
ALTER INDEX AlbumsByAlbumTitle DROP STORED COLUMN MarketingBudget
PostgreSQL
ALTER INDEX AlbumsByAlbumTitle DROP INCLUDE COLUMN MarketingBudget
Indice dei valori NULL
Per impostazione predefinita, Spanner indicizza i valori NULL
. Ad esempio, richiama la definizione dell'indice SingersByFirstLastName
nella tabella Singers
:
CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);
Tutte le righe di Singers
vengono indicizzate anche se FirstName
o LastName
o
entrambe sono NULL
.
Quando i valori NULL
vengono indicizzati, puoi eseguire query SQL efficienti e letture
sui dati che includono valori NULL
. Ad esempio, usa questa istruzione di query SQL
per trovare tutti i valori Singers
con un NULL
FirstName
:
GoogleSQL
SELECT s.SingerId, s.FirstName, s.LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName IS NULL;
PostgreSQL
SELECT s.SingerId, s.FirstName, s.LastName
FROM Singers /* @ FORCE_INDEX = SingersByFirstLastName */ AS s
WHERE s.FirstName IS NULL;
Ordinamento per i valori NULL
Spanner ordina NULL
come il valore più piccolo per qualsiasi tipo specifico. Per una colonna in ordine crescente (ASC
), i valori NULL
vengono ordinati per primi. Per una colonna in ordine decrescente (DESC
), i valori NULL
vengono ordinati per ultimi.
Disabilita l'indicizzazione dei valori NULL
GoogleSQL
Per disabilitare l'indicizzazione dei valori null, aggiungi la parola chiave NULL_FILTERED
alla definizione dell'indice. Gli indici NULL_FILTERED
sono particolarmente utili per l'indicizzazione di colonne sparse, dove la maggior parte delle righe contiene un valore NULL
. In questi casi, l'indice NULL_FILTERED
può essere notevolmente più piccolo e più efficiente da mantenere rispetto a un normale indice che include valori di NULL
.
Ecco una definizione alternativa di SingersByFirstLastName
che
non indicizza i valori NULL
:
CREATE NULL_FILTERED INDEX SingersByFirstLastNameNoNulls
ON Singers(FirstName, LastName);
La parola chiave NULL_FILTERED
si applica a tutte le colonne chiave dell'indice. Non puoi specificare il filtro NULL
in base alla singola colonna.
PostgreSQL
Per filtrare le righe con valori nulli in una o più colonne indicizzate, utilizza il predicato WHERE COLUMN IS NOT NULL
.
Gli indici con filtri null sono particolarmente utili per l'indicizzazione delle colonne sparse, dove la maggior parte delle righe contiene un valore NULL
. In questi casi, l'indice con filtro nullo può essere notevolmente più piccolo e più efficiente da mantenere rispetto a un normale indice che include valori NULL
.
Ecco una definizione alternativa di SingersByFirstLastName
che
non indicizza i valori NULL
:
CREATE INDEX SingersByFirstLastNameNoNulls
ON Singers(FirstName, LastName)
WHERE FirstName IS NOT NULL
AND LastName IS NOT NULL;
L'esclusione dei valori NULL
impedisce a Spanner di utilizzarlo per alcune query. Ad esempio, Spanner non utilizza l'indice per questa query,
perché l'indice omette tutte le righe Singers
per le quali LastName
è NULL
. Di conseguenza, l'utilizzo dell'indice impedisce alla query di restituire le righe corrette:
GoogleSQL
FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = "John";
PostgreSQL
FROM Singers /*@ FORCE_INDEX = SingersByFirstLastNameNoNulls */
WHERE FirstName = 'John';
Per abilitare Spanner all'utilizzo dell'indice, devi riscrivere la query in modo che escluda le righe escluse anche dall'indice:
GoogleSQL
SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = 'John' AND LastName IS NOT NULL;
PostgreSQL
SELECT FirstName, LastName
FROM Singers /*@ FORCE_INDEX = SingersByFirstLastNameNoNulls */
WHERE FirstName = 'John' AND LastName IS NOT NULL;
Campi protocollo indice
Utilizza le colonne generate per indicizzare i campi nei buffer di protocollo archiviati nelle colonne PROTO
, purché i campi indicizzati utilizzino i tipi di dati primitivi o ENUM
.
Se definisci un indice in un campo di messaggio di protocollo, non puoi modificare o rimuovere questo campo dallo schema del protocollo. Per maggiori informazioni, consulta Aggiornamenti agli schemi che contengono un indice sui campi protocollo.
Di seguito è riportato un esempio della tabella Singers
con una colonna messaggio proto
SingerInfo
. Per definire un indice nel campo nationality
di PROTO
,
devi creare una colonna generata archiviata:
GoogleSQL
CREATE PROTO BUNDLE (googlesql.example.SingerInfo, googlesql.example.SingerInfo.Residence);
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
...
SingerInfo googlesql.example.SingerInfo,
SingerNationality STRING(MAX) AS (SingerInfo.nationality) STORED
) PRIMARY KEY (SingerId);
Ha la seguente definizione del tipo di proto googlesql.example.SingerInfo
:
GoogleSQL
package googlesql.example;
message SingerInfo {
optional string nationality = 1;
repeated Residence residence = 2;
message Residence {
required int64 start_year = 1;
optional int64 end_year = 2;
optional string city = 3;
optional string country = 4;
}
}
Poi definisci un indice nel campo nationality
del proto:
GoogleSQL
CREATE INDEX SingersByNationality ON Singers(SingerNationality);
La seguente query SQL legge i dati utilizzando l'indice precedente:
GoogleSQL
SELECT s.SingerId, s.FirstName
FROM Singers AS s
WHERE s.SingerNationality = "English";
Note
- Utilizza un'istruzione di indice per accedere agli indici nei campi delle colonne del buffer di protocollo.
- Non puoi creare un indice su campi ripetuti del buffer di protocollo.
Aggiornamenti agli schemi che contengono un indice sui campi proto
Se definisci un indice in un campo di messaggio di protocollo, non puoi modificare o rimuovere questo campo dallo schema del protocollo. Il motivo è che, dopo aver definito l'indice, il controllo del tipo viene eseguito ogni volta che lo schema viene aggiornato. Spanner acquisisce le informazioni sul tipo per tutti i campi del percorso utilizzati nella definizione dell'indice.
Indici univoci
Gli indici possono essere dichiarati UNIQUE
. Gli indici UNIQUE
aggiungono ai dati indicizzati un vincolo che vieta voci duplicate per una determinata chiave di indice.
Questo vincolo viene applicato da Spanner al momento del commit della transazione.
In particolare, non verrà eseguito il commit di qualsiasi transazione che causerebbe l'esistenza di più voci di indice per la stessa chiave.
Se una tabella contiene dati non UNIQUE
al suo interno, il tentativo di creare un indice UNIQUE
al suo interno avrà esito negativo.
Una nota sugli indici UNIQUE NULL_FILTERED
Un indice UNIQUE NULL_FILTERED
non applica l'univocità della chiave di indice se almeno una delle parti chiave dell'indice è NULL.
Ad esempio, supponiamo di aver creato la tabella e l'indice seguenti:
GoogleSQL
CREATE TABLE ExampleTable (
Key1 INT64 NOT NULL,
Key2 INT64,
Key3 INT64,
Col1 INT64,
) PRIMARY KEY (Key1, Key2, Key3);
CREATE UNIQUE NULL_FILTERED INDEX ExampleIndex ON ExampleTable (Key1, Key2, Col1);
PostgreSQL
CREATE TABLE ExampleTable (
Key1 BIGINT NOT NULL,
Key2 BIGINT,
Key3 BIGINT,
Col1 BIGINT,
PRIMARY KEY (Key1, Key2, Key3)
);
CREATE UNIQUE INDEX ExampleIndex ON ExampleTable (Key1, Key2, Col1)
WHERE Key1 IS NOT NULL
AND Key2 IS NOT NULL
AND Col1 IS NOT NULL;
Le due righe seguenti in ExampleTable
hanno gli stessi valori per le chiavi indice secondarie Key1
, Key2
e Col1
:
1, NULL, 1, 1
1, NULL, 2, 1
Poiché Key2
è NULL
e l'indice è con filtri null, le righe non saranno
presenti nell'indice ExampleIndex
. Poiché non vengono inseriti nell'indice, non vengono rifiutati per violazione dell'univocità su (Key1, Key2,
Col1)
.
Se vuoi che l'indice applichi l'univocità dei valori della tupla (Key1
,
Key2
, Col1
), devi annotare Key2
con NOT NULL
nella definizione della tabella
o creare l'indice senza filtrare i valori null.
Rilascia un indice
Utilizza l'istruzione DROP INDEX
per eliminare un indice secondario
dallo schema.
Per rilasciare l'indice denominato SingersByFirstLastName
:
DROP INDEX SingersByFirstLastName;
Indice per una scansione più rapida
Quando Spanner deve eseguire una scansione della tabella (anziché una ricerca indicizzata) per recuperare i valori da una o più colonne, puoi ricevere risultati più rapidi se esiste un indice per quelle colonne e nell'ordine specificato dalla query. Se esegui spesso query che richiedono analisi, valuta la possibilità di creare indici secondari per garantirne l'esecuzione in modo più efficiente.
In particolare, se hai bisogno che Spanner esegua spesso la scansione della chiave primaria o di un altro indice di una tabella in ordine inverso, puoi aumentarne l'efficienza mediante un indice secondario che rende esplicito l'ordine scelto.
Ad esempio, la seguente query restituisce sempre un risultato rapido, anche se
Spanner deve analizzare Songs
per trovare il valore più basso di
SongId
:
SELECT SongId FROM Songs LIMIT 1;
SongId
è la chiave primaria della tabella, archiviata (come in tutte le chiavi primarie)
in ordine crescente. Spanner può analizzare l'indice
di quella chiave e trovare rapidamente il primo risultato.
Tuttavia, senza l'aiuto di un indice secondario, la seguente query non
restituisce così rapidamente, soprattutto se Songs
contiene molti dati:
SELECT SongId FROM Songs ORDER BY SongId DESC LIMIT 1;
Anche se SongId
è la chiave primaria della tabella, Spanner non ha modo di recuperare il valore più alto della colonna senza ricorrere a una scansione completa della tabella.
L'aggiunta del seguente indice consentirebbe di restituire più rapidamente questa query:
CREATE INDEX SongIdDesc On Songs(SongId DESC);
Con questo indice attivo, Spanner lo userebbe per restituire un risultato per la seconda query molto più rapidamente.
Passaggi successivi
- Scopri le best practice SQL per Spanner.
- Scopri di più sui piani di esecuzione delle query per Spanner.
- Scopri come risolvere i problemi di regressione delle prestazioni nelle query SQL.