Sobre esquemas

Esta página discute esquemas e introduz tabelas intercaladas, que podem melhorar o desempenho da consulta ao consultar tabelas em um relacionamento 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 exclusivamente cada linha. As chaves primárias são sempre indexadas para pesquisa rápida de linhas, e é possível definir índices secundários em uma ou mais colunas. Se você quiser atualizar ou excluir linhas existentes em uma tabela, será necessário que a tabela tenha uma chave primária. Uma tabela sem colunas de chave primária pode ter apenas uma linha. Somente os bancos de dados do dialeto do GoogleSQL podem ter tabelas sem uma chave primária.

Os dados no 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 de cada tabela. Os tipos de dados incluem escalares e complexos, descritos em Tipos de dados no GoogleSQL e Tipos de dados PostgreSQL.

Relacionamentos de tabelas pai e filho

Há duas maneiras de definir relacionamentos pai-filho no Spanner: intercalação de tabelas e chaves estrangeiras.

A intercalação de tabelas do Spanner é uma boa opção para muitos relacionamentos pai-filho. Com a intercalação, o Spanner colocaliza fisicamente as linhas filhas com linhas pai no armazenamento. A colocalização pode melhorar significativamente o desempenho. Por exemplo, se você tiver as tabelas Customers e Invoices, e o aplicativo busca com frequência todas as faturas de um cliente, defina Invoices como uma tabela filho intercalada de Customers. Ao fazer isso, você está declarando uma relação de localidade de dados entre duas tabelas independentes. Você está dizendo ao Spanner para armazenar uma ou mais linhas de Invoices com uma linha Customers.

Para associar uma tabela filho a uma tabela mãe, use a DDL que declara a tabela filho como intercalada no pai e inclua a chave primária da tabela mãe como a primeira parte da chave primária composta da tabela filha. Para mais informações sobre intercalação, consulte Criar tabelas intercaladas posteriormente 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.

Como escolher 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 uma CustomerId fornecida 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. Normalmente, ele seria um valor inteiro exclusivo sem importância comercial (uma chave primária alternativa).

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 Spanner divide os dados entre os servidores por intervalos de chave, o que significa que suas 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:

Como adicionar índices secundários com base em chaves primárias

Em determinadas circunstâncias, o uso do banco de dados pode se beneficiar da adição de índices secundários com base em chaves primárias. Isso é especialmente verdadeiro quando você executa com frequência consultas que exigem varreduras de ordem inversa da chave primária de uma tabela.

Chaves primárias em tabelas intercaladas

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 adicionais da tabela filho.

O Spanner armazena linhas em ordem de classificação por valores de chave primária, com linhas filho inseridas entre linhas pai. Veja uma ilustração de linhas intercaladas em Criar tabelas intercaladas posteriormente neste tópico.

Em resumo, o Spanner pode colocalizar fisicamente linhas de tabelas relacionadas. Os exemplos de esquema mostram como é esse layout físico.

Divisões de banco de dados

É possível definir hierarquias de relacionamentos pai-filho intercalados com até sete camadas de profundidade, o que significa que é possível colocalizar linhas de sete tabelas independentes. Se o tamanho dos dados nas tabelas for pequeno, um único servidor do Spanner provavelmente processará o 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 Spanner é um banco de dados distribuído, o que significa que, conforme seu banco de dados cresce, o Spanner divide seus dados em partes chamadas "divisões". Divisões individuais podem ser movidas independentemente entre si e atribuídas a servidores diferentes, que podem estar em locais físicos distintos. 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 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 Spanner realiza a divisão baseada em carga para reduzir os pontos de acesso de leitura, suponha que seu banco de dados contenha uma tabela com 10 linhas lidas com mais frequência do que todas as outras linhas na tabela. O Spanner pode adicionar limites de divisão entre cada uma dessas 10 linhas para que elas sejam processadas por um servidor diferente, em vez de permitir que todas as leituras dessas linhas consumam os recursos de um único servidor.

Como regra geral, se você seguir as práticas recomendadas para o design do esquema, o Spanner poderá atenuar os pontos de acesso de maneira que a capacidade de leitura melhore a cada poucos minutos até saturar os recursos na instância ou se deparar com casos em que nenhum novo limite de divisão possa ser adicionado (porque você tem uma divisão que abrange 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 correspondentes de dados.

Criar uma tabela pai

Imagine que você esteja criando um aplicativo de música e precise de uma tabela simples que armazene linhas de dados do cantor:

Tabela "Singers" com cinco linhas e quatro colunas. SingerID é a primeira coluna.

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.

Você pode definir a tabela com um esquema do Spanner como este:

GoogleSQL

CREATE TABLE Singers (
SingerId   INT64 NOT NULL,
FirstName  STRING(1024),
LastName   STRING(1024),
SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

PostgreSQL

CREATE TABLE singers (
singer_id   BIGINT PRIMARY KEY,
first_name  VARCHAR(1024),
last_name   VARCHAR(1024),
singer_info BYTEA
);

Observe o seguinte sobre o esquema do exemplo:

  • Singers é uma tabela na raiz da hierarquia do banco de dados (porque não é definida como filho intercalado de outra tabela).
  • Para os bancos de dados do dialeto do GoogleSQL, as colunas de chave primária geralmente são anotadas com NOT NULL, embora você possa omitir essa anotação se quiser permitir valores NULL em colunas de 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 ou BYTES no GoogleSQL 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 tipos varchar e character varying do PostgreSQL. Para mais informações, consulte Tipos de dados escalares para bancos de dados dialetos do GoogleSQL e Tipos de dados do PostgreSQL para bancos de dados do dialeto do PostgreSQL.

O que parece o layout físico das linhas na tabela Singers? O diagrama a seguir mostra as linhas da tabela Singers armazenadas pela chave primária ("Singers(1)", depois "Singers(2)" e assim por diante, em que o número entre parênteses é o valor da chave primária.

Exemplos de linhas de uma tabela armazenada na ordem da chave primária. Há uma linha pontilhada que indica um limite de divisão entre as chaves 3 e 4.

O diagrama anterior ilustra um exemplo de limite de divisão entre as linhas codificadas por Singers(3) e Singers(4), com os dados das divisões resultantes atribuídos a servidores diferentes. À medida que essa tabela cresce, é possível que as linhas de dados Singers sejam armazenadas em locais diferentes.

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.

Tabela de álbuns com cinco linhas e três colunas. As colunas de chave primária estão à esquerda.

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)

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
SingerId     INT64 NOT NULL,
AlbumId      INT64 NOT NULL,
AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId);

PostgreSQL

CREATE TABLE singers (
singer_id   BIGINT PRIMARY KEY,
first_name  VARCHAR(1024),
last_name   VARCHAR(1024),
singer_info BYTEA
);

CREATE TABLE albums (
singer_id     BIGINT,
album_id      BIGINT,
album_title   VARCHAR,
PRIMARY KEY (singer_id, album_id)
);

O layout físico das linhas de Singers e Albums é semelhante ao diagrama a seguir, com linhas da tabela Albums armazenadas por chave primária contígua, em seguida, linhas de Singers armazenadas por chave primária contígua:

Layout físico das linhas. As chaves primárias são mostradas na coluna mais à esquerda.
    Por exemplo, Álbuns(2,1), Álbuns(2, 2) e assim por diante.

Uma observação importante sobre o esquema é que o Spanner não pressupõe relações de localidade de dados entre as tabelas Singers e Albums, porque elas são tabelas de nível superior. À medida que o banco de dados cresce, o 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 o aplicativo precisa frequentemente recuperar informações sobre todos os álbuns de um determinado cantor, crie Albums como uma tabela filho intercalada de Singers, que colocaliza as linhas das duas tabelas na dimensão da chave primária. O próximo exemplo explica isso com mais detalhes.

Criar tabelas intercaladas

Uma tabela intercalada é uma tabela que é declarada filho filho intercalada de outra porque é necessário que as linhas da tabela filho 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 as linhas da tabela Albums quando acessa uma linha Singers. Por exemplo, quando você acessar a linha Singers(1), também precisará acessar as linhas Albums(1, 1) e Albums(1, 2). Nesse caso, Singers e Albums precisam ter uma forte relação de localidade de dados. É possível declarar essa relação de localidade de dados criando Albums como uma tabela filho intercalada de Singers.

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

A linha em negrito no esquema a seguir mostra como criar Albums como uma tabela intercalada de Singers.

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 ) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

PostgreSQL

CREATE TABLE singers (
 singer_id   BIGINT PRIMARY KEY,
 first_name  VARCHAR(1024),
 last_name   VARCHAR(1024),
 singer_info BYTEA
 );

CREATE TABLE albums (
 singer_id     BIGINT,
 album_id      BIGINT,
 album_title   VARCHAR,
 PRIMARY KEY (singer_id, album_id)
 )
 INTERLEAVE IN PARENT singers ON DELETE CASCADE;

Observações sobre este esquema:

  • SingerId, que é a primeira parte da chave primária da tabela filho Albums, também é a chave primária da tabela pai Singers.
  • A anotação ON DELETE CASCADE significa que, quando uma linha da tabela pai é excluída, as linhas filho também são excluídas automaticamente. Se uma tabela filho não tiver essa anotação ou se a anotação for ON DELETE NO ACTION, será necessário excluir as linhas filhas antes de excluir a linha pai.
  • As linhas intercaladas são ordenadas primeiro por linhas da tabela pai e, em seguida, por linhas contíguas da tabela filho que compartilham a chave primária da tabela pai. Por exemplo, "Singers(1)", depois "Albums(1, 1)", depois "Albums(1, 2)" e assim por diante.
  • A relação de localidade de dados de cada cantor e dados dos respectivos álbuns é preservada se este banco de dados é dividido, desde que o tamanho de uma linha Singers e todas as suas linhas Albums permaneçam abaixo do limite de tamanho de divisão e que não haja ponto de acesso em nenhuma dessas linhas Albums.
  • A linha pai já precisa existir para que seja possível inserir linhas filho. 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.

Layout físico das linhas: as linhas de "Albums" são intercaladas entre as linhas de "Singers"

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:

Tabela "Songs" com seis linhas e quatro colunas. As três colunas mais à esquerda compreendem a chave primária.

Songs precisa ter uma chave primária que inclua todas as chaves primárias das tabelas acima na hierarquia, ou seja, SingerId e AlbumId.

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

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
 INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 TrackId      INT64 NOT NULL,
 SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
 INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

PostgreSQL

CREATE TABLE singers (
 singer_id   BIGINT PRIMARY KEY,
 first_name  VARCHAR(1024),
 last_name   VARCHAR(1024),
 singer_info BYTEA
 );

CREATE TABLE albums (
 singer_id     BIGINT,
 album_id      BIGINT,
 album_title   VARCHAR,
 PRIMARY KEY (singer_id, album_id)
 )
 INTERLEAVE IN PARENT singers ON DELETE CASCADE;

CREATE TABLE songs (
 singer_id     BIGINT,
 album_id      BIGINT,
 track_id      BIGINT,
 song_name     VARCHAR,
 PRIMARY KEY (singer_id, album_id, track_id)
 )
 INTERLEAVE IN PARENT albums ON DELETE CASCADE;

O diagrama a seguir representa uma visualização física de linhas intercaladas.

Visualizações físicas de linhas: as músicas são intercaladas em "Albums", que são intercaladas entre "Singers"

Neste exemplo, à medida que o número de cantores aumenta, o Spanner adiciona limites de divisão entre os cantores para preservar a localidade dos dados entre um cantor e seus dados de álbum e de música. No entanto, se o tamanho de uma linha cantor e das linhas filhas exceder o limite de tamanho de divisão ou se um ponto de acesso for detectado nas linhas filhas, o Spanner tentará adicionar limites de divisão para isolar essa linha do ponto de acesso com todas as linhas filhas abaixo dela.

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 efetivamente as tabelas e permitindo acessar 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 da linha pai, o 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.

GoogleSQL

SELECT s.FirstName, a.AlbumTitle
FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

PostgreSQL

SELECT s.first_name, a.album_title
FROM singers AS s JOIN albums AS a ON s.singer_id = a.singer_id;

Colunas de chave

Esta seção inclui algumas observações sobre as colunas de chave.

Como alterar chaves de tabela

As chaves de uma tabela não podem mudar. Não é possível adicionar ou remover uma coluna de chave a uma tabela existente.

Como armazenar NULLs em uma chave primária

No GoogleSQL, se você quiser armazenar NULL em uma coluna de chave primária, omita a cláusula NOT NULL para essa 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. Observe que, como SingerId é a chave primária, pode haver apenas uma linha 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, NOT NULL para a coluna Albums.SingerId não é permitido 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,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Tipos não permitidos

As colunas 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

Implemente a multilocação se você estiver armazenando dados que pertençam a clientes diferentes. 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:

Banco de dados 1: Ackworth Records
SingerId FirstName LastName
1MarcRichards
2CatalinaSmith
Banco de dados 2: Cama Records
SingerId FirstName LastName
1AliceTrentor
2GabrielWright
Banco de dados 3: Eagan Records
SingerId FirstName LastName
1BenjaminMartinez
2HannahHarris

Multilocação gerenciada por esquema

Outra maneira de projetar para multilocação no Spanner é ter todos os clientes em uma única tabela de um único banco de dados e usar um valor de chave primária 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 Spanner pode usar efetivamente 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:

Banco de dados multilocação do Spanner
CustomerId SingerId FirstName LastName
11MarcRichards
12CatalinaSmith
21AliceTrentor
22GabrielWright
31BenjaminMartinez
32HannahHarris

Se forem necessários bancos de dados separados para cada locatário, é preciso estar ciente das seguintes restrições:

  • limites no número de bancos de dados por instância e no número de tabelas e índices por banco de dados. Dependendo do número de clientes, pode não ser 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