Nesta página, você verá esquemas e as tabelas intercaladas, que podem melhorar o desempenho das consultas em tabelas em uma relação pai-filho.
Os bancos de dados do Cloud Spanner contêm uma ou mais tabelas. As tabelas são estruturadas como linhas e colunas. Uma ou mais colunas são definidas como a chave primária da tabela, que identifica cada linha de forma exclusiva. As chaves primárias são sempre indexadas para pesquisa de linha rápida, e é possível definir índices secundários em uma ou mais colunas.
Os dados no Cloud Spanner são fortemente tipados. É necessário definir um esquema para cada banco de dados, e esse esquema precisa especificar o tipo de dados de cada coluna em cada tabela. Os tipos de dados incluem tipos escalares e complexos, descritos em Tipos de dados no SQL padrão do Google para bancos de dados SQL do Google Standard e Tipos de dados do PostgreSQL para bancos de dados do PostgreSQL.
Relacionamentos de tabelas mães e filhas
Há duas maneiras de definir relações pai-filho no Cloud Spanner: intercalação de tabelas e chaves estrangeiras.
A intercalação de tabelas do Cloud Spanner é uma boa opção para muitos relacionamentos pai-filho. Com a intercalação, o Cloud Spanner colocaliza fisicamente as linhas filhas com as linhas mães no armazenamento. A colocalização pode
melhorar significativamente o desempenho. Por exemplo, se você tiver uma tabela Customers
e uma Invoices
, e seu aplicativo buscar frequentemente todas as
faturas de um cliente, defina Invoices
como uma tabela filha
intercalada de Customers
. Fazendo isso, você declara uma relação de localidade
de dados entre duas tabelas independentes. Você está instruindo o Cloud Spanner a armazenar uma ou mais linhas de Invoices
com uma linha Customers
.
Para associar uma tabela filha a uma mãe, use a DDL que declara a tabela filha como intercalada no pai e incluindo a chave primária da tabela mãe como a primeira parte da chave primária composta da tabela filha. Para saber mais sobre intercalação, consulte Criar tabelas intercaladas mais adiante neste tópico.
As chaves externas são uma solução pai-filho mais geral e abordam casos de uso adicionais. Elas não estão limitadas a colunas de chave primária, e as tabelas podem ter várias relações de chave externa, como pai em alguns relacionamentos e filho em outros. No entanto, uma relação de chave externa não sugere a co-localização das tabelas na camada de armazenamento.
O Google recomenda que você escolha representar relações pai-filho como tabelas intercaladas ou chaves externas, mas não ambas. Para mais informações sobre chaves externas e a comparação delas com tabelas intercaladas, consulte Visão geral de chaves externas.
Chaves primárias em tabelas intercaladas
Como informar ao Cloud Spanner quais linhas Invoices
precisam ser armazenadas com quais linhas Customers
? Para isso, use a chave primária dessas tabelas. Para
intercalar, cada tabela
precisa ter uma chave primária. Se você declarar uma tabela como filha
intercalada de outra, a tabela precisará ter uma chave primária composta que inclua todos os componentes
da chave primária do pai, na mesma ordem e, normalmente, uma ou mais
colunas de tabela filha adicionais.
O Cloud Spanner armazena linhas ordenadas por valores de chave primária. As linhas filhas são inseridas entre as linhas mães. Veja uma ilustração de linhas intercaladas em Criar tabelas intercaladas mais adiante neste tópico.
Resumidamente, o Cloud Spanner pode colocalizar fisicamente linhas de tabelas relacionadas. Os exemplos de esquema mostram a aparência desse layout físico.
Escolher chave principal
A chave primária identifica exclusivamente cada uma das linhas em uma tabela. Para atualizar ou excluir linhas existentes em uma tabela, é preciso que a tabela tenha uma chave primária composta de uma ou mais colunas. Uma tabela sem colunas de chave primária pode ter apenas uma linha. Apenas os bancos de dados do dialeto SQL padrão do Google podem ter tabelas sem uma chave primária.
Muitas vezes, o aplicativo já tem um campo que é uma escolha natural para uso como chave primária. Por exemplo, para uma tabela Customers
, pode haver um CustomerId
fornecido pelo aplicativo que serve bem como a chave primária. Em outros casos, talvez seja necessário gerar uma chave primária ao
inserir a linha. Ele normalmente seria um valor inteiro exclusivo sem importância comercial (uma chave primária substituta).
Em todos os casos, tenha cuidado para não criar pontos de acesso com a escolha da chave primária. Por exemplo, se você inserir registros com um número inteiro monotônico crescente como a chave, a inserção sempre ocorrerá no final do espaço da chave. Isso não é desejável, porque o Cloud Spanner divide os dados entre servidores por intervalos de chaves, o que significa que as inserções serão direcionadas para um único servidor, criando um ponto de acesso. Há técnicas que podem espalhar a carga em vários servidores e evitar pontos de acesso:
- Gere a chave e armazene-a em uma coluna. Use a coluna hash (ou a coluna hash junto com as colunas de chave única) como a chave primária.
- Troque a ordem das colunas na chave primária.
- Use um identificador único universal (UUID, na sigla em inglês). Recomenda-se a versão 4 do UUID (em inglês), já que ela usa valores aleatórios nos bits mais altos. Não use um algoritmo UUID (como a versão 1 do UUID) que armazene o carimbo de data/hora nos bits de ordem superior.
- Faça reversão em bits dos valores sequenciais.
Adicionar índices secundários com base nas chaves primárias
Em determinadas circunstâncias, o uso do banco de dados pode ser beneficiado com a adição de índices secundários com base nas chaves principais. Isso acontece principalmente se você executa consultas que exigem verificações inversas de chave primária da tabela.
Divisões de banco de dados
É possível definir hierarquias de relações pai-filho intercalados com até sete camadas, o que significa que você pode colocalizar linhas de sete tabelas independentes. Se o tamanho dos dados nas tabelas for pequeno, é possível que um único servidor do Cloud Spanner processe seu banco de dados. Mas o que acontece quando as tabelas relacionadas crescem e começam a atingir os limites de recursos de um servidor individual? O Cloud Spanner é um banco de dados distribuído. Isso significa que, à medida que o banco de dados cresce, o Cloud Spanner divide seus dados em blocos chamados "divisões." As divisões individuais podem se mover de maneira independente e atribuídas a servidores diferentes, que podem estar em locais físicos diferentes. Uma divisão contém um intervalo de linhas contíguas. As chaves de início e término desse intervalo são chamadas de "limites de divisão". O Cloud Spanner adiciona e remove automaticamente limites de divisão com base no tamanho e na carga, o que altera o número de divisões no banco de dados.
Divisão baseada em carga
Como um exemplo de como o Cloud Spanner executa a divisão baseada em carga para reduzir os pontos de acesso de leitura, imagine que seu banco de dados contém uma tabela com 10 linhas que são lidas com mais frequência do que todas as outras linhas na tabela. O Cloud Spanner pode adicionar limites de divisão entre cada uma dessas 10 linhas para que cada um seja processado por um servidor diferente, em vez de permitir que todas as leituras dessas linhas consumam os recursos de um único servidor. de dados.
Como regra geral, se você seguir as práticas recomendadas para o design do esquema, o Cloud Spanner poderá mitigar pontos de acesso de modo que a capacidade de leitura melhore em intervalos de alguns minutos até saturar os recursos na instância ou em casos em que nenhum novo limite de divisão possa ser adicionado (porque você tem uma divisão que cobre apenas uma linha sem filhos intercalados).
Exemplos de esquema
Os exemplos de esquema abaixo mostram como criar tabelas mãe e filha com e sem intercalação e ilustrar os layouts físicos de dados correspondentes.
Criar uma tabela mãe
Imagine que você esteja criando um aplicativo de música e precise de uma tabela simples que armazene linhas de dados do cantor:
Observe que a tabela contém uma coluna de chave primária, SingerId
, que aparece
à esquerda da linha em negrito, e que as tabelas são organizadas por linhas
e colunas.
É possível definir a tabela com um esquema do Cloud Spanner como este:
SQL padrão do Google
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 );
Observe o seguinte sobre o esquema do exemplo:
Singers
é uma tabela na raiz da hierarquia do banco de dados porque não é definida como uma filha intercalada de outra tabela.- Para bancos de dados SQL dialetos padrão do Google, as colunas de chave primária geralmente são anotadas com
NOT NULL
(embora seja possível omitir essa anotação se quiser permitir valoresNULL
nas colunas da chave. Para mais informações, consulte Colunas-chave. - As colunas que não estão incluídas na chave primária são chamadas de colunas não chave e podem ter uma anotação
NOT NULL
opcional. - As colunas que usam o tipo
STRING
ouBYTES
no Google Standard SQL precisam ser definidas com um comprimento, o que representa o número máximo de caracteres Unicode que podem ser armazenados no campo. A especificação de tamanho é opcional para os tiposvarchar
echaracter varying
do PostgreSQL. Para saber mais, consulte Tipos de dados escalares para bancos de dados dialetos SQL padrão do Google e Tipos de dados PostgreSQL para bancos de dados dialetos do PostgreSQL.
O que parece o layout físico das linhas na tabela Singers
? O diagrama a seguir mostra linhas da tabela Singers
armazenadas pela chave primária ("Singers(1)" e depois "Singers(2)" e assim por diante, em que "Singers(1)" representa a linha na tabela Singers codificadas por 1).
O diagrama anterior ilustra um exemplo de limite de divisão entre as linhas de chave por Singers(3)
e Singers(4)
, com os dados das divisões resultantes atribuídos a diferentes servidores. À medida que essa tabela cresce, é possível que linhas de dados Singers
sejam armazenadas em diferentes locais.
Criar tabelas mãe e filha
Suponha que você queira adicionar alguns dados básicos sobre os álbuns de cada cantor ao aplicativo de música.
A chave primária de Albums
é composta por duas colunas: SingerId
e AlbumId
, para associar cada álbum à sua cantora. O esquema de exemplo a seguir
define as tabelas Albums
e Singers
na raiz da hierarquia do banco de dados, o que as torna tabelas irmãs.
-- Schema hierarchy: -- + Singers (sibling table of Albums) -- + Albums (sibling table of Singers)
SQL padrão do Google
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) );
O layout físico das linhas de Singers
e Albums
é semelhante ao diagrama a seguir,
com as linhas da tabela Albums
armazenadas por chave primária contígua, em seguida,
linhas de Singers
armazenadas por chave primária contígua:
Uma observação importante sobre o esquema é que o Cloud Spanner não supõe relações de localidade de dados entre as tabelas Singers
e Albums
, porque são tabelas de nível superior. À medida que o banco de dados cresce, o Cloud Spanner pode adicionar limites de divisão entre qualquer uma das linhas. Isso significa que as linhas da tabela Albums
podem ter uma divisão diferente das linhas da tabela Singers
, e as duas divisões poderiam se mover independentemente umas das outras.
Dependendo das necessidades do seu aplicativo, convém permitir que os dados Albums
sejam localizados em divisões diferentes dos dados Singers
. No entanto, isso pode gerar
uma penalidade de desempenho devido à necessidade de coordenar leituras e atualizações em
recursos distintos. Se seu aplicativo precisa recuperar frequentemente informações sobre todos os álbuns de um determinado cantor, crie Albums
como uma tabela filha intercalada de Singers
, que coloca as linhas das duas tabelas ao longo da dimensão da chave primária. O próximo exemplo explica isso em mais detalhes.
Criar tabelas intercaladas
Uma tabela intercalada é uma tabela que é declarada como filha intercalada de outra porque é necessário que as linhas da tabela filha sejam armazenadas fisicamente com a linha pai associada. Como mencionado anteriormente, a chave primária da tabela mãe precisa ser a primeira parte da chave primária composta da tabela filha.
Ao projetar seu aplicativo de música, suponha que você perceba que o app precisa acessar com frequência linhas filhas da tabela Albums
ao acessar uma linha Singers
. Por exemplo, ao acessar a linha Singers(1)
, você também precisa
acessar as linhas Albums(1, 1)
e Albums(1, 2)
. Nesse caso, Singers
e Albums
precisam ter uma relação sólida de localidade de dados.
É possível declarar essa relação de localidade de dados criando Albums
como uma
tabela filha intercalada de Singers
.
A linha em negrito no esquema abaixo mostra como criar Albums
como uma tabela intercalada de Singers
.
-- Schema hierarchy: -- + Singers -- + Albums (interleaved table, child table of Singers)
SQL padrão do Google
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;
Observações sobre este esquema:
SingerId
, que é a primeira parte da chave primária da tabela filhaAlbums
, também é a chave primária da tabela paiSingers
. Isso não será necessário seSingers
eAlbums
estiverem no mesmo nível de hierarquia, mas nesse esquema porqueAlbums
é declarado como uma tabela filha intercalada deSingers
.- A anotação
ON DELETE CASCADE
significa que, quando uma linha da tabela pai é excluída, as linhas filhas também são excluídas automaticamente. Se uma tabela filha não tiver essa anotação ou se a anotação forON DELETE NO ACTION
, exclua as linhas filhas antes de excluir a linha pai. - As linhas intercaladas são ordenadas primeiro por linhas da tabela mãe e, em seguida, por linhas contíguas da tabela filha que compartilham a chave primária da tabela mãe. Por exemplo, "Singers(1)" e "Albums(1, 1)", depois, "Albums(1, 2)" e assim por diante.
- A relação de localidade de dados de cada cantor e os dados dos álbuns deles será
preservada se o banco de dados se dividir, desde que o tamanho de uma linha
Singers
e todas as linhasAlbums
fiquem abaixo do limite de tamanho da divisão e que não haja pontos de acesso em nenhuma dessas linhasAlbums
. - A linha mãe já precisa existir para que seja possível inserir linhas filhas. A linha pai pode já existir no banco de dados ou pode ser inserida antes da inserção das linhas filho na mesma transação.
Criar uma hierarquia de tabelas intercaladas
A relação mãe e filha entre Singers
e Albums
pode ser estendido para mais tabelas descendentes. Por exemplo, é possível criar uma tabela intercalada chamada Songs
como filha de Albums
para armazenar a lista de faixas de cada álbum:
Songs
precisa ter uma chave primária que inclua todas as chaves primárias das
tabelas acima dela na hierarquia, ou seja, SingerId
e AlbumId
.
-- Schema hierarchy: -- + Singers -- + Albums (interleaved table, child table of Singers) -- + Songs (interleaved table, child table of Albums)
SQL padrão do Google
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;
O diagrama a seguir representa uma visualização física de linhas intercaladas.
Neste exemplo, conforme o número de cantores cresce, o Cloud Spanner adiciona limites de divisão entre os cantores para preservar a localidade dos dados entre um cantor e seus dados de álbum e música. No entanto, se o tamanho de uma linha do cantor e as linhas filhas exceder o limite de tamanho da divisão, ou se um ponto de acesso for detectado nas linhas filhas, o Cloud Spanner tentará adicionar limites de divisão para isolar essas linhas junto com todas as linhas filhas abaixo.
Em resumo, uma tabela mãe e todas as tabelas filhas e descendentes formam uma hierarquia de tabelas no esquema. Embora cada tabela na hierarquia seja logicamente independente, a intercalação física delas pode melhorar o desempenho, pré-mesclando as tabelas de maneira eficiente e permitindo que você acesse as linhas relacionadas, minimizando os acessos ao armazenamento.
Mesclagens com tabelas intercaladas
Se possível, vincule dados em tabelas intercaladas por chave principal. Como cada linha intercalada geralmente é armazenada fisicamente na mesma divisão que a linha mãe, o Cloud Spanner pode realizar junções por chave primária localmente, minimizando o acesso ao armazenamento e o tráfego de rede. No exemplo a seguir, Singers
e Albums
são unidos na chave primária SingerId
.
SQL padrão do Google
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;
Colunas de chave
As chaves de uma tabela não podem mudar. Não é possível adicionar ou remover uma coluna de chave a uma tabela existente.
Armazenar NULLs
No SQL padrão do Google, se você quiser armazenar NULL em uma coluna de chave primária, omita a cláusula NOT NULL
dessa coluna no esquema. Os bancos de dados do dialeto do PostgreSQL não são compatíveis com NULLs em uma coluna de chave primária.
Veja um exemplo de como omitir a cláusula NOT NULL
na coluna da chave principal SingerId
. Como SingerId
é a chave primária, pode haver no máximo uma linha na tabela Singers
que armazena NULL
nessa coluna.
CREATE TABLE Singers ( SingerId INT64, FirstName STRING(1024), LastName STRING(1024), ) PRIMARY KEY (SingerId);
A propriedade anulável da coluna da chave primária precisa coincidir entre as instruções da tabela mãe e as da tabela filha. Neste exemplo, Albums.SingerId INT64 NOT
NULL
não é permitido. A declaração de chave precisa omitir a cláusula NOT NULL
porque Singers.SingerId
a omite.
CREATE TABLE Singers ( SingerId INT64, FirstName STRING(1024), LastName STRING(1024), ) PRIMARY KEY (SingerId); CREATE TABLE Albums ( SingerId INT64 NOT NULL, -- NOT ALLOWED! AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX), ) PRIMARY KEY (SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
Tipos não permitidos
Os itens a seguir não podem ser do tipo ARRAY
:
- colunas de chave de uma tabela
- colunas de chave de um índice
Projetar para multilocação
Caso estejam sendo armazenados dados pertencentes a diferentes clientes, talvez seja interessante fornecer multilocação. Por exemplo, um serviço de música pode querer armazenar o conteúdo de cada gravadora individualmente.
Multilocação clássica
A maneira clássica de desenvolver um design para multilocação é criar um banco de dados separado para cada cliente. Neste exemplo, cada banco de dados tem sua própria tabela Singers
:
SingerId | FirstName | LastName |
---|---|---|
1 | Marc | Richards |
2 | Catalina | Smith |
SingerId | FirstName | LastName |
---|---|---|
1 | Alice | Trentor |
2 | Gabriel | Wright |
SingerId | FirstName | LastName |
---|---|---|
1 | Benjamin | Martinez |
2 | Hannah | Harris |
Multilocação gerenciada por esquema
Outra maneira de projetar para multilocação no Cloud Spanner é usar todos os clientes em uma única tabela em um único banco de dados e usar uma chave-valor principal diferente para cada cliente. Por exemplo, é possível incluir uma coluna de chave CustomerId
nas tabelas. Se você tornar CustomerId
a primeira coluna de chave, os dados de cada cliente têm uma boa localização. O Cloud Spanner pode usar divisões de banco de dados para maximizar o desempenho com base no tamanho dos dados e nos padrões de carga. No exemplo a seguir, há uma única tabela Singers
para todos os clientes:
CustomerId | SingerId | FirstName | LastName |
---|---|---|---|
1 | 1 | Marc | Richards |
1 | 2 | Catalina | Smith |
2 | 1 | Alice | Trentor |
2 | 2 | Gabriel | Wright |
3 | 1 | Benjamin | Martinez |
3 | 2 | Hannah | Harris |
Se forem necessários bancos de dados separados para cada locatário, é preciso estar ciente das seguintes restrições:
- Há limites para o número de bancos de dados por instância e o número de tabelas e índices por banco de dados. Dependendo do número de clientes, talvez não seja possível ter bancos de dados ou tabelas separados.
- Adicionar novas tabelas e índices não intercalados pode levar muito tempo. Pode não ser possível alcançar o desempenho desejado se o design do esquema depender da adição de novas tabelas e índices.
Se for preciso criar bancos de dados separados, pode ser mais interessante distribuir suas tabelas entre os bancos de dados de forma que cada banco de dados tenha um número baixo de alterações de esquema por semana.
Se você criar tabelas e índices separados para cada cliente de seu aplicativo, não coloque todas as tabelas e índices no mesmo banco de dados. Em vez disso, divida-os entre vários bancos de dados para reduzir os problemas de desempenho com a criação de um grande número de índices.
Para saber mais sobre outros padrões de gerenciamento de dados e design de aplicativos para multilocação, consulte Como implementar a multilocação no Cloud Spanner