Índices da Pesquisa

Nesta página, descrevemos como adicionar e usar índices da Pesquisa. A pesquisa de texto completo é executada em entradas no índice da Pesquisa.

Como usar o índice da Pesquisa

É possível criar um índice da Pesquisa em todas as colunas que você quer disponibilizar para pesquisas de texto completo. Para criar um índice da Pesquisa, use a instrução DDL CREATE SEARCH INDEX. Para atualizar um índice, use a instrução DDL ALTER SEARCH INDEX. O Spanner cria e mantém automaticamente o índice da Pesquisa, incluindo a adição e atualização de dados no índice da Pesquisa assim que eles são alterados no banco de dados.

Partições do índice da Pesquisa

Um índice da Pesquisa pode ser particionado ou não particionado, dependendo do tipo de consulta que você quer acelerar.

  • Um exemplo de quando um índice particionado é a melhor opção é quando o aplicativo consulta uma caixa de correio de e-mail. Cada consulta é restrita a uma caixa de correio específica.

  • Um exemplo de quando uma consulta não particionada é a melhor opção é quando há uma consulta em todas as categorias dos produtos em um catálogo de produtos.

Casos de uso do índice da Pesquisa

Além da pesquisa de texto completo, os índices da Pesquisa do Spanner são compatíveis com o seguinte:

  • Pesquisas JSON, que são uma maneira eficiente de indexar e consultar documentos JSON e JSONB.
  • Pesquisas de substring, que são um tipo de consulta que procura uma string mais curta (a substring) em um corpo de texto maior.
  • Combinar condições em qualquer subconjunto de dados indexados, incluindo correspondência exata e numérica, em uma única verificação de índice.

Para mais informações sobre casos de uso, consulte Pesquisa versus índices secundários.

Exemplo de índice da Pesquisa

Para mostrar os recursos dos índices da Pesquisa, suponha que haja uma tabela que armazena informações sobre álbuns de música:

GoogleSQL 

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

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  albumtitle character varying,
PRIMARY KEY(albumid));

O Spanner tem várias funções de tokenização que criam tokens. Para modificar a tabela anterior e permitir que os usuários façam uma pesquisa de texto completo para encontrar títulos de álbuns, use a função TOKENIZE_FULLTEXT para criar tokens com base nos títulos dos álbuns. Em seguida, crie uma coluna que use o tipo de dados TOKENLIST para armazenar a saída de tokenização de TOKENIZE_FULLTEXT. Neste exemplo, criamos a coluna AlbumTitle_Tokens.

GoogleSQL 

ALTER TABLE Albums
  ADD COLUMN AlbumTitle_Tokens TOKENLIST
  AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN;

PostgreSQL 

ALTER TABLE albums
  ADD COLUMN albumtitle_tokens spanner.tokenlist
    GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN;

O exemplo a seguir usa a DDL CREATE SEARCH INDEX para criar um índice da Pesquisa (AlbumsIndex) nos tokens AlbumTitle (AlbumTitle_Tokens):

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
  ON Albums(AlbumTitle_Tokens);

PostgreSQL

Este exemplo usa CREATE SEARCH INDEX.

CREATE SEARCH INDEX albumsindex ON albums(albumtitle_tokens);

Depois de adicionar o índice da Pesquisa, use consultas SQL para encontrar álbuns que correspondam aos critérios de pesquisa. Exemplo:

GoogleSQL 

SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

PostgreSQL 

SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

Criar e consultar um índice da Pesquisa para um esquema nomeado

É possível criar um índice da Pesquisa em um esquema nomeado, conforme mostrado nos exemplos a seguir.

GoogleSQL 

CREATE SCHEMA AlbumSchema;
CREATE TABLE AlbumSchema.Albums (
  AlbumId STRING(MAX) NOT NULL,
  AlbumTitle STRING(MAX),
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) STORED HIDDEN,
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumSchema.AlbumsIndex ON AlbumSchema.Albums(AlbumTitle_Tokens);

PostgreSQL

No PostgreSQL, não é possível usar nomes totalmente qualificados para criar índices em esquemas nomeados.

CREATE SCHEMA AlbumSchema;
CREATE TABLE AlbumSchema.albums (
  albumid character varying NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) STORED HIDDEN,
  PRIMARY KEY(albumid)
);
CREATE SEARCH INDEX albumsindex ON AlbumSchema.albums(albumtitle_tokens);

Os exemplos a seguir mostram como consultar o índice da Pesquisa no esquema nomeado:

GoogleSQL 

SELECT AlbumId
FROM AlbumSchema.Albums
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

O exemplo a seguir usa uma dica de consulta para informar ao otimizador de consultas que use o índice chamado AlbumsIndex ao executar a consulta.

SELECT AlbumId
FROM AlbumSchema.Albums@{FORCE_INDEX="AlbumSchema.AlbumsIndex"}
WHERE SEARCH(AlbumTitle_Tokens, "fifth symphony")

PostgreSQL

No PostgreSQL, não é possível usar nomes totalmente qualificados para criar índices em esquemas nomeados.

SELECT albumid
FROM AlbumSchema.albums
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

O exemplo a seguir usa uma dica de consulta para informar ao otimizador de consultas que use o índice chamado albumsindex ao executar a consulta.

SELECT albumid
FROM AlbumSchema.albums /*@ FORCE_INDEX = albumsindex */
WHERE spanner.search(albumtitle_tokens, 'fifth symphony')

Consistência de dados

Quando um índice é criado, o Spanner usa processos automatizados para preencher os dados e garantir a consistência. Quando as gravações são confirmadas, os índices são atualizados na mesma transação. O Spanner realiza verificações de consistência de dados automaticamente.

Definições de esquema de índice da Pesquisa

Os índices da Pesquisa são definidos em uma ou mais colunas TOKENLIST de uma tabela. Os índices de pesquisa têm os seguintes componentes:

  • Tabela base: a tabela do Spanner que precisa de indexação.
  • Coluna TOKENLIST: uma coleção de colunas que definem os tokens que precisam de indexação. A ordem dessas colunas não é importante.

Por exemplo, na instrução a seguir, a tabela base é Albums. As colunas TOKENLIST são criadas em AlbumTitle (AlbumTitle_Tokens) e Rating (Rating_Tokens).

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  SingerId INT64 NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  AlbumTitle STRING(MAX),
  Rating FLOAT64,
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN,
  Rating_Tokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  singerid bigint NOT NULL,
  releasetimestamp bigint NOT NULL,
  albumtitle character varying,
  rating double precision,
  albumtitle_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN,
  rating_tokens spanner.tokenlist GENERATED ALWAYS AS (spanner.tokenize_fulltext(rating)) VIRTUAL HIDDEN,
PRIMARY KEY(AlbumId));

Use a seguinte instrução CREATE SEARCH INDEX para criar um índice da Pesquisa usando os tokens para AlbumTitle e Rating:

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens, Rating_Tokens)
PARTITION BY SingerId
ORDER BY ReleaseTimestamp DESC

PostgreSQL 

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens, rating_tokens)
PARTITION BY singerid
ORDER BY releasetimestamp DESC

Os índices da Pesquisa têm as seguintes opções:

  • Partições: um grupo opcional de colunas que dividem o índice da Pesquisa. Consultar um índice particionado costuma ser muito mais eficiente do que consultar um índice não particionado. Para mais informações, consulte Particionar índices da Pesquisa.
  • Coluna de ordem de classificação: uma coluna INT64 opcional que estabelece a ordem de recuperação do índice de pesquisa. Para mais informações, consulte Ordem de classificação do índice da Pesquisa.
  • Intercalação: assim como os índices secundários, é possível intercalar índices da Pesquisa. Os índices da Pesquisa intercalados usam menos recursos para gravar e mesclar com a tabela base. Para mais informações, consulte Índices de pesquisa intercalados.
  • Cláusula de opções: uma lista de pares de chave-valor que substitui as configurações padrão do índice da Pesquisa.

Layout interno dos índices da Pesquisa

Um elemento importante da representação interna dos índices da Pesquisa é um docid, que serve como uma representação eficiente de armazenamento da chave primária da tabela base, que pode ser arbitrariamente longa. Ele também cria a ordem do layout de dados internos de acordo com as colunas ORDER BY fornecidas pelo usuário da instrução CREATE SEARCH INDEX. Ele é representado como um ou dois números inteiros de 64 bits.

Os índices da Pesquisa são implementados internamente como um mapeamento de dois níveis:

  1. Tokens para docids
  2. Docids para chaves primárias da tabela base

Esse esquema resulta em uma economia significativa de armazenamento, já que o Spanner não precisa armazenar a chave primária completa da tabela base para cada par <token, document>.

Há dois tipos de índices físicos que implementam os dois níveis de mapeamento:

  1. Um índice secundário que mapeia chaves de partição e um docid para a chave primária da tabela base. No exemplo da seção anterior, isso mapeia {SingerId, ReleaseTimestamp, uid} para {AlbumId}. O índice secundário também armazena todas as colunas especificadas na cláusula STORING de CREATE SEARCH INDEX.
  2. Um índice de token que mapeia tokens para docids, semelhante aos índices invertidos na literatura de recuperação de informações. O Spanner mantém um índice de token separado para cada TOKENLIST do índice da Pesquisa. Logicamente, os índices de token mantêm listas de docids para cada token em cada partição (conhecidas na recuperação de informações como listas de postagens). As listas são ordenadas por tokens para recuperação rápida e, dentro das listas, o docid é usado para ordenação. Os índices de token individuais são um detalhe de implementação não exposto pelas APIs do Spanner.

O Spanner é compatível com as seguintes opções para docid.

Índice da Pesquisa ID do documento Comportamento
A cláusula ORDER BY é omitida para o índice da Pesquisa {uid} O Spanner adiciona um valor exclusivo oculto (UID) para identificar cada linha.
ORDER BY column {column, uid} O Spanner adiciona a coluna UID como um critério de desempate entre linhas com os mesmos valores de column em uma partição.

Observações sobre o uso:

  • A coluna de UID interno não é exposta pela API Spanner.
  • Em índices em que o UID não é adicionado, as transações que adicionam uma linha com uma (partição,ordem de classificação) já existente falham.

Por exemplo, considere os seguintes dados:

AlbumId SingerId ReleaseTimestamp SongTitle
a1 1 997 Dias bonitos
a2 1 743 Olhos bonitos

Supondo que a coluna de pré-classificação esteja em ordem crescente, o conteúdo do índice de token particionado por SingerId particiona o conteúdo do índice de token da seguinte maneira:

SingerId _token ReleaseTimestamp uid
1 bonito 743 uid1
1 bonito 997 uid2
1 dias 743 uid1
1 olhos 997 uid2

Fragmentação do índice da Pesquisa

Quando o Spanner divide uma tabela, ele distribui os dados do índice da Pesquisa para que todos os tokens em uma linha específica da tabela base estejam na mesma divisão. Em outras palavras, o índice da Pesquisa é fragmentado por documento. Essa estratégia de fragmentação tem implicações significativas no desempenho:

  1. O número de servidores com que cada transação se comunica permanece constante, independentemente do número de tokens ou de colunas TOKENLIST indexadas.
  2. As consultas de pesquisa que envolvem várias expressões condicionais são executadas de forma independente em cada divisão, evitando a sobrecarga de desempenho associada a uma mesclagem distribuída.

Os índices da Pesquisa têm dois modos de distribuição:

  • Fragmentação uniforme (padrão). Na fragmentação uniforme, os dados indexados de cada linha da tabela base são atribuídos aleatoriamente a uma divisão de índice de uma partição.
  • Fragmentação por ordem de classificação. Na fragmentação por ordem de classificação, os dados de cada linha da tabela base são atribuídos a uma divisão de índice de uma partição com base nas colunas ORDER BY (ou seja, colunas de pré-classificação). Por exemplo, no caso de uma ordem de classificação decrescente, todas as linhas com os maiores valores de ordem de classificação aparecem na primeira divisão de índice de uma partição, e o próximo maior grupo de valores de ordem de classificação na próxima divisão.

Esses modos de fragmentação têm uma compensação entre os riscos de ponto de acesso e o custo da consulta:

  • Os índices da Pesquisa fragmentados uniformes são recomendados quando os padrões de leitura ou gravação no índice da Pesquisa podem levar a hotspots. A fragmentação uniforme reduz os pontos de acesso distribuindo a carga de leitura e gravação de maneira uniforme entre as divisões, mas isso pode aumentar o uso de recursos durante as execuções de consulta como uma compensação. Em índices da Pesquisa fragmentados uniformes, as consultas precisam ler todas as divisões em uma partição, devido aos dados distribuídos aleatoriamente. Ao acessar índices fragmentados de maneira uniforme, o Spanner lê todas as divisões em paralelo para reduzir a latência geral da consulta.
  • Os índices da Pesquisa fragmentados por ordem de classificação são preferíveis quando é improvável que os padrões de leitura ou gravação causem pontos de acesso. Essa abordagem pode reduzir o custo de consultas em que o ORDER BY corresponde ao ORDER BY do índice e especifica um LIMIT relativamente baixo. Ao executar essas consultas, o Spanner lê a partir das primeiras divisões de uma partição de forma incremental, e a consulta pode ser concluída sem ler todas as divisões quando LIMIT pode ser atendido antecipadamente.

  • O modo de fragmentação de um índice da Pesquisa é configurado usando a cláusula OPTIONS.

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens, Rating_Tokens)
PARTITION BY SingerId
ORDER BY ReleaseTimestamp DESC
OPTIONS (sort_order_sharding = true);

PostgreSQL

O modo de fragmentação de um índice da Pesquisa é configurado usando a cláusula WITH.

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens, rating_tokens)
PARTITION BY singerid
ORDER BY releasetimestamp DESC
WITH (sort_order_sharding = true);

Quando sort_order_sharding=false é definido ou não é especificado, o índice da Pesquisa é criado usando fragmentação uniforme.

Índices da Pesquisa intercalados

Assim como os índices secundários, é possível intercalar índices da Pesquisa em uma tabela pai da tabela base. O principal motivo para usar índices de pesquisa intercalados é colocalizar dados da tabela base com dados de índice para partições pequenas. Essa colocation oportunista tem as seguintes vantagens:

  • As gravações não precisam fazer um commit em duas fases.
  • As mesclagens de retorno do índice da Pesquisa com a tabela base não são distribuídas.

Os índices da Pesquisa intercalados têm as seguintes restrições:

  1. Somente índices fragmentados por ordem de classificação podem ser intercalados.
  2. Os índices da Pesquisa só podem ser intercalados em tabelas de nível superior, não em tabelas filho.
  3. Assim como as tabelas intercaladas e os índices secundários, faça com que a chave da tabela pai seja um prefixo das colunas PARTITION BY no índice da Pesquisa intercalado.

Definir um índice da Pesquisa intercalado

O exemplo a seguir demonstra como definir um índice da Pesquisa intercalado:

GoogleSQL 

CREATE TABLE Singers (
  SingerId INT64 NOT NULL
) PRIMARY KEY(SingerId);

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

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens)
PARTITION BY SingerId,
INTERLEAVE IN Singers
OPTIONS (sort_order_sharding = true);

PostgreSQL 

CREATE TABLE singers(
  singerid bigint NOT NULL
PRIMARY KEY(singerid));

CREATE TABLE albums(
  singerid bigint NOT NULL,
  albumid character varying NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist
  GENERATED ALWAYS
AS (
  spanner.tokenize_fulltext(albumtitle)
) VIRTUAL HIDDEN,
  PRIMARY KEY(singerid, albumid)),
INTERLEAVE IN PARENT singers ON DELETE CASCADE;

CREATE
  SEARCH INDEX albumsindex
ON
  albums(albumtitle_tokens)
  PARTITION BY singerid INTERLEAVE IN singers WITH(sort_order_sharding = true);

Ordem de classificação do índice da Pesquisa

Os requisitos para a definição da ordem de classificação do índice da Pesquisa são diferentes dos índices secundários.

Por exemplo, considere a seguinte tabela:

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  AlbumName STRING(MAX),
  AlbumName_Token TOKENLIST AS (TOKEN(AlbumName)) HIDDEN
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  releasetimestamp bigint NOT NULL,
  albumname character varying,
  albumname_token spanner.tokenlist
      GENERATED ALWAYS AS(spanner.token(albumname)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

O aplicativo pode definir um índice secundário para pesquisar informações usando o AlbumName classificado por ReleaseTimestamp:

CREATE INDEX AlbumsSecondaryIndex ON Albums(AlbumName, ReleaseTimestamp DESC);

O índice da Pesquisa equivalente é o seguinte (usa tokenização de correspondência exata, já que os índices secundários não são compatíveis com pesquisas de texto completo):

CREATE SEARCH INDEX AlbumsSearchIndex
ON Albums(AlbumName_Token)
ORDER BY ReleaseTimestamp DESC;

A ordem de classificação do índice da Pesquisa precisa obedecer aos seguintes requisitos:

  1. Use apenas colunas INT64 para a ordem de classificação de um índice da Pesquisa. As colunas com tamanhos arbitrários usam muitos recursos no índice da Pesquisa porque o Spanner precisa armazenar um docid ao lado de cada token. Especificamente, a coluna de ordem de classificação não pode usar o tipo TIMESTAMP porque TIMESTAMP usa precisão de nanossegundos, que não cabe em um número inteiro de 64 bits.

  2. As colunas de ordem de classificação não podem ser NULL. Há duas maneiras de atender a esse requisito:

    1. Declare a coluna de ordem de classificação como NOT NULL.
    2. Configure o índice para excluir valores NULL.

Um carimbo de data/hora geralmente é usado para determinar a ordem de classificação. Uma prática comum é usar microssegundos desde a época Unix para esses carimbos de data/hora.

Os aplicativos geralmente recuperam os dados mais recentes primeiro usando um índice de pesquisa classificado em ordem decrescente.

Como a coluna de ordem de classificação faz parte da chave docid no índice secundário e nos índices de token, sempre que o valor dela muda na tabela base, o Spanner precisa reescrever a linha do índice secundário correspondente e todas as linhas do índice de token associadas, o que gera sobrecarga de gravação. Se o valor da ordem de classificação mudar com frequência, omita essa coluna de ORDER BY ou use um valor menos granular. Por exemplo, se uma coluna UpdateTime for atualizada a cada poucos segundos, use uma coluna gerada que agrupe o carimbo de data/hora por hora, como UpdateTimeHours INT64 AS (UNIX_SECONDS(TIMESTAMP_TRUNC(UpdateTime, HOUR))) STORED (ou updatetimehours bigint GENERATED ALWAYS AS (EXTRACT(EPOCH FROM updatetime - '1970-01-01Z'::timestamptz)::bigint / 3600) STORED no PostgreSQL).

Índices da Pesquisa filtrados por NULL

Os índices da Pesquisa podem usar a sintaxe WHERE column_name IS NOT NULL para excluir linhas da tabela base. A filtragem NULL pode ser aplicada a chaves de particionamento, colunas de ordem de classificação e colunas armazenadas. Não é permitido filtrar NULL em colunas de matriz armazenadas.

Exemplo

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums(AlbumTitle_Tokens)
STORING (Genre)
WHERE Genre IS NOT NULL

PostgreSQL 

CREATE SEARCH INDEX albumsindex
ON albums(albumtitle_tokens)
INCLUDE (genre)
WHERE genre IS NOT NULL

A consulta precisa especificar a condição de filtragem NULL (Genre IS NOT NULL para este exemplo) na cláusula WHERE. Caso contrário, o otimizador de consultas não poderá usar o índice da Pesquisa. Para mais informações, consulte Requisitos de consulta SQL.

O uso do filtro WHERE <column> IS NOT NULL na definição do índice garante que apenas valores não nulos sejam indexados. Isso permite que o otimizador de consultas use o índice de maneira eficaz para consultas relevantes, mesmo que a condição IS NOT NULL não esteja explicitamente na cláusula WHERE da consulta.

Use a filtragem NULL em uma coluna gerada para excluir linhas com base em qualquer critério arbitrário. Para mais informações, consulte Criar um índice parcial usando uma coluna gerada.

A seguir