Índices secundários

Em um banco de dados do Cloud Spanner, o Cloud Spanner cria automaticamente um índice para cada chave primária da tabela. Por exemplo, você não precisa fazer nada para indexar a chave primária de Singers, porque ele é indexado automaticamente para você.

Também é possível criar índices secundários para outras colunas. Adicionar um índice secundário a uma coluna torna mais eficiente para procurar dados nessa coluna. Por exemplo, se você precisar procurar rapidamente um conjunto de valores SingerId para um determinado intervalo de valores LastName, crie um índice secundário em LastName. Portanto, o Cloud Spanner não precisa verificar a tabela inteira.

Se a pesquisa no exemplo acima for feita em uma transação de leitura e gravação, a pesquisa mais eficiente também evita reter bloqueios em toda a tabela, o que permite inserções e atualizações simultâneas na tabela para linhas fora do intervalo de pesquisa LastName.

Além dos benefícios que eles agregam às pesquisas, os índices secundários também podem ajudar o Cloud Spanner a executar verificações com mais eficiência, permitindo verificações de índice em vez de verificações de tabela completas.

O Cloud Spanner armazena os seguintes dados em cada índice secundário:

  • Todas as colunas de chave da tabela base
  • Todas as colunas incluídas no índice
  • Todas as colunas especificadas na cláusula STORING de definição do índice

Com o tempo, o Cloud Spanner analisa suas tabelas para garantir que os índices secundários sejam usados para as consultas apropriadas.

Como adicionar um índice secundário

O momento mais eficiente para adicionar um índice secundário é quando você cria a tabela. Para criar uma tabela e seus índices ao mesmo tempo, envie as instruções DDL para a nova tabela e os novos índices em uma única solicitação para o Cloud Spanner.

No Cloud Spanner, também é possível adicionar um novo índice secundário a uma tabela existente enquanto o banco de dados continua a veicular o tráfego. Como qualquer outra alteração de esquema no Cloud Spanner, adicionar um índice a um banco de dados existente não exige que o banco de dados fique off-line e não bloqueia colunas ou tabelas inteiras.

Sempre que um novo índice é adicionado a uma tabela existente, o Cloud Spanner automaticamente preenche o índice para refletir uma visualização atualizada dos dados que estão sendo indexados. O Cloud Spanner gerencia esse processo de preenchimento e usa recursos adicionais durante o preenchimento de índice.

O tempo de criação do índice pode variar de minutos a muitas horas. Como a criação do índice é uma atualização de esquema, ele está sujeito às mesmas restrições de desempenho que qualquer outra atualização de esquema. O tempo necessário para criar um índice secundário depende de vários fatores:

  • do tamanho do conjunto de dados;
  • A capacidade de computação da instância
  • da carga na instância.

Para ver o progresso de um processo de preenchimento de índice, consulte a seção de progresso.

Esteja ciente de que usar a coluna confirmar carimbo de data/hora como a primeira parte do índice secundário pode criar pontos de acesso e reduzir o desempenho de gravação.

Use a instrução CREATE INDEX para definir um índice secundário no seu esquema. Veja alguns exemplos:

Para indexar todos os Singers no banco de dados pelo nome e sobrenome:

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)

Para criar um índice de todos Songs no banco de dados pelo valor de SongName:

CREATE INDEX SongsBySongName ON Songs(SongName)

Para indexar somente as músicas de um determinado cantor, use a cláusula INTERLEAVE IN para intercalar o índice na tabela Singers:

CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName),
    INTERLEAVE IN Singers

Para indexar apenas as canções de um álbum específico:

CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName),
    INTERLEAVE IN Albums

Para indexar por ordem decrescente de SongName:

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC),
    INTERLEAVE IN Albums

A anotação DESC acima se aplica somente a SongName. Para indexar por ordem decrescente de outras chaves de índice, anote-as com DESC também: SingerId DESC, AlbumId DESC.

Observe também que PRIMARY_KEY é uma palavra reservada e não pode ser usada como o nome de um índice. É o nome dado ao pseudoíndice, que é criado quando uma tabela com a especificação PRIMARY KEY é criada

Para mais detalhes e práticas recomendadas para escolher índices não intercalados e intercalados, consulte Opções de índice e Usar um índice intercalado em uma coluna cujo valor aumenta ou diminui monotonicamente.

Como visualizar o progresso do preenchimento do índice

Etapas para visualizar o progresso do preenchimento do índice

  1. Um processo de preenchimento de índice faz parte de uma operação de longa duração schema-update, já que a adição de um índice secundário requer uma atualização de esquema. É possível ver o progresso do preenchimento do índice usando o ID da operação. Se você não tiver o código da operação, encontre-o usando a lista de operações do gcloud spanner:

    gcloud spanner operations list --instance=INSTANCE --database=DATABASE

    Observações sobre o uso:

    • Para limitar a lista de operações retornadas por esse comando, especifique a sinalização --filter. Por exemplo, use o filtro a seguir para retornar operações de atualização de esquema.

      --filter="@TYPE:UpdateDatabaseDdlMetadata"

      Para informações sobre a sintaxe do filtro, consulte gcloud topic filtros. Para informações sobre como filtrar operações de banco de dados, consulte o campo filter em ListDatabaseOperationsRequest.

    Veja um exemplo da saída:

    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

  2. Para acompanhar o progresso de um ou vários processos de preenchimento de índice secundário, use gcloud spanner operations describe:

    gcloud spanner operations describe _auto_op_123456 \
    --instance=INSTANCE \
    --database=DATABASE

    Aqui está um exemplo de saída de uma operação de longa duração com atualização de esquema que contém dois processos de preenchimento de índice:

    done: true
metadata:
  '@type': type.googleapis.com/google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata
  commitTimestamps:
  - '2021-01-22T21:58:42.912540Z'
  database: projects/my-project/instances/test-instance/databases/example-db
  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'
  statements:
  - CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)
  - CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName), INTERLEAVE IN Albums
name: projects/my-project/instances/test-instance/databases/example-db/operations/_auto_op_123456
response:
  '@type': type.googleapis.com/google.protobuf.Empty

    O progresso de cada instrução de preenchimento de índice pode ser encontrado no campo progress. Para cada instrução na matriz de instruções, há um campo correspondente na matriz de progresso. E os campos startTime e endTime são preenchidos quando disponíveis. A ordem das matrizes de progresso corresponde à ordem das matrizes.

Cenários ao visualizar o progresso do preenchimento do índice

Há diferentes cenários que um usuário pode enfrentar ao tentar verificar o progresso de um preenchimento de índice. As instruções de criação de índice que exigem um preenchimento de índice fazem parte de operações de atualização de esquema, e pode haver várias instruções que fazem parte de uma operação de atualização de esquema.

O primeiro cenário é o mais simples, que é quando a instrução de criação do índice é a primeira na operação de atualização do esquema. O campo startTime da instrução de criação do índice será preenchido com o horário de início da operação de atualização do esquema, já que é a primeira instrução. Ele será o primeiro a ser processado e executado devido. a ordem de execução. O campo progressPercent da instrução de criação do índice será preenchido quando o progresso do preenchimento do índice for superior a 0%. O campo endTime é preenchido quando a instrução é confirmada.

O segundo cenário é quando a instrução de criação do índice não é a primeira instrução na operação de atualização do esquema. Nenhum campo relacionado à instrução de criação do índice será preenchido até que as instruções anteriores tenham sido confirmadas devido à ordem de execução. Depois que as instruções anteriores forem confirmadas, o startTime do campo de progresso da instrução de criação do índice será o primeiro atributo preenchido. O campo progressPercent da instrução de criação do índice será preenchido quando o progresso do preenchimento do índice for superior a 0%. O campo endTime é preenchido quando a instrução é confirmada.

Como cancelar a criação do índice

É possível usar o SDK do Cloud para cancelar a criação de índices. Para recuperar uma lista de operações de atualização de esquema para um banco de dados do Cloud Spanner, use o comando gcloud spanner operations list e inclua o parâmetro --filter opção:

gcloud spanner operations list \
    --instance=INSTANCE \
    --database=DATABASE \
    --filter="@TYPE:UpdateDatabaseDdlMetadata"

Encontre o OPERATION_ID para a operação que você quer cancelar, use o comando gcloud spanner operations cancel para cancelá-lo:

gcloud spanner operations cancel OPERATION_ID \
    --instance=INSTANCE \
    --database=DATABASE

Como visualizar índices existentes

Para visualizar informações sobre índices existentes em um banco de dados, é possível usar o Console do Google Cloud ou a ferramenta de linha de comando gcloud:

Console

  1. Acesse a página Instâncias do Cloud Spanner no Console do Cloud.

    Acessar a página "Instâncias"

  2. Clique no nome da instância que quer exibir.

  3. No painel esquerdo, clique no banco de dados que você quer visualizar e clique na tabela que você quer visualizar.

  4. Clique na guia "Índices". O Console do Cloud mostra uma lista de índices.

  5. Opcional: para detalhes sobre um índice, como as colunas incluídas, clique no nome do índice.

gcloud

Use o comando gcloud spanner databases ddl describe:

    gcloud spanner databases ddl describe DATABASE \
        --instance=INSTANCE

O gcloud imprime a Linguagem de definição de dados (DDL, na sigla em inglês) para criar as tabelas e os índices do banco de dados. As instruções CREATE INDEX descrevem os índices existentes. Por exemplo:

    --- |-
  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)

Consulta com um índice específico

As seções a seguir explicam como especificar um índice em uma instrução SQL e com a interface de leitura do Cloud Spanner. Os exemplos nessas seções presumem que você adicionou uma coluna MarketingBudget à tabela Albums e criou um índice chamado AlbumsByAlbumTitle:

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);

Como especificar um índice em uma instrução SQL

Quando você usa SQL para consultar uma tabela do Cloud Spanner, o Cloud Spanner usa automaticamente os índices que provavelmente tornarão a consulta mais eficiente. Como resultado, você não precisa especificar um índice para consultas SQL. No entanto, para consultas essenciais à sua carga de trabalho, o Google aconselha a utilização de diretivas FORCE_INDEX nas instruções SQL para um desempenho mais consistente.

Em alguns casos, o Cloud Spanner pode escolher um índice que faz com que a latência da consulta aumente. Se você seguiu as etapas de solução de problemas para regressões de desempenho e confirmou que convém tentar um índice diferente para a consulta, especifique o índice como parte de sua consulta.

Para especificar um índice em uma instrução SQL, use FORCE_INDEX para fornecer uma diretiva de índice. As diretivas de índice usam a seguinte sintaxe:

FROM MyTable@{FORCE_INDEX=MyTableIndex}

Você também pode usar uma diretiva de índice para instruir o Cloud Spanner a verificar a tabela base em vez de usar um índice:

FROM MyTable@{FORCE_INDEX=_BASE_TABLE}

O exemplo a seguir mostra uma consulta SQL que especifica um índice:

SELECT AlbumId, AlbumTitle, MarketingBudget
    FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
    WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo";

Uma diretiva de índice pode forçar o processador de consultas do Cloud Spanner a ler colunas adicionais que são exigidas pela consulta, mas não armazenadas no índice. O processador de consultas recupera essas colunas unindo o índice e a tabela base. Para evitar essa junção extra, use uma cláusula STORING para armazenar as colunas adicionais no índice.

Por exemplo, no exemplo mostrado acima, a coluna MarketingBudget não é armazenada no índice, mas a consulta SQL seleciona essa coluna. Como resultado, o Cloud Spanner deve pesquisar a coluna MarketingBudget na tabela base e, em seguida, associá-la aos dados do índice para retornar os resultados da consulta.

O Cloud Spanner gerará um erro se a diretiva de indexação tiver um dos seguintes problemas:

Os exemplos a seguir mostram como gravar e executar consultas que buscam os valores de AlbumId, AlbumTitle e MarketingBudget usando o índice AlbumsByAlbumTitle:

C++

void QueryUsingIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT AlbumId, AlbumTitle, MarketingBudget"
      " FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}"
      " WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",
      {{"start_title", spanner::Value("Aardvark")},
       {"end_title", spanner::Value("Goo")}});
  using RowType =
      std::tuple<std::int64_t, std::string, absl::optional<std::int64_t>>;
  auto rows = client.ExecuteQuery(std::move(select));
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_query_data_with_index]\n";
}

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            var marketingBudget = reader.IsDBNull(
                reader.GetOrdinal("MarketingBudget")) ?
                "" :
                reader.GetFieldValue<string>("MarketingBudget");
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + marketingBudget);
        }
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func queryUsingIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{
		SQL: `SELECT AlbumId, AlbumTitle, MarketingBudget
			FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
			WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title`,
		Params: map[string]interface{}{
			"start_title": "Aardvark",
			"end_title":   "Goo",
		},
	}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumTitle", &albumTitle); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
	return nil
}

Java

static void queryUsingIndex(DatabaseClient dbClient) {
  Statement statement =
      Statement
          // We use FORCE_INDEX hint to specify which index to use. For more details see
          // https://cloud.google.com/spanner/docs/query-syntax#from-clause
          .newBuilder(
              "SELECT AlbumId, AlbumTitle, MarketingBudget "
                  + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
                  + "WHERE AlbumTitle >= @StartTitle AND AlbumTitle < @EndTitle")
          // We use @BoundParameters to help speed up frequently executed queries.
          //  For more details see https://cloud.google.com/spanner/docs/sql-best-practices
          .bind("StartTitle")
          .to("Aardvark")
          .bind("EndTitle")
          .to("Goo")
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("AlbumId"),
          resultSet.getString("AlbumTitle"),
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const startTitle = 'Ardvark';
// const endTitle = 'Goo';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
        WHERE AlbumTitle >= @startTitle AND AlbumTitle <= @endTitle`,
  params: {
    startTitle: startTitle,
    endTitle: endTitle,
  },
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from the database using SQL and an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * query_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $startTitle The start of the title index.
 * @param string $endTitle   The end of the title index.
 */
function query_data_with_index(
    $instanceId,
    $databaseId,
    $startTitle = 'Aardvark',
    $endTitle = 'Goo'
) {
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $parameters = [
        'startTitle' => $startTitle,
        'endTitle' => $endTitle
    ];

    $results = $database->execute(
        'SELECT AlbumId, AlbumTitle, MarketingBudget ' .
        'FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} ' .
        'WHERE AlbumTitle >= @startTitle AND AlbumTitle < @endTitle',
        ['parameters' => $parameters]
    );

    foreach ($results as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def query_data_with_index(
    instance_id, database_id, start_title="Aardvark", end_title="Goo"
):
    """Queries sample data from the database using SQL and an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    This sample also uses the `MarketingBudget` column. You can add the column
    by running the `add_column` sample or by running this DDL statement against
    your database:

        ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    params = {"start_title": start_title, "end_title": end_title}
    param_types = {
        "start_title": spanner.param_types.STRING,
        "end_title": spanner.param_types.STRING,
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT AlbumId, AlbumTitle, MarketingBudget "
            "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
            "WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",
            params=params,
            param_types=param_types,
        )

        for row in results:
            print(u"AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# start_title = "An album title to start with such as 'Ardvark'"
# end_title   = "An album title to end with such as 'Goo'"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT AlbumId, AlbumTitle, MarketingBudget
             FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
             WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title"

params      = { start_title: start_title, end_title: end_title }
param_types = { start_title: :STRING,     end_title: :STRING }

client.execute(sql_query, params: params, types: param_types).rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Como especificar um índice na interface de leitura

Quando você usa a interface de leitura para o Cloud Spanner e quer que o Cloud Spanner use um índice, é necessário especificar o índice. A interface de leitura não seleciona o índice automaticamente.

Além disso, seu índice deve conter todos os dados que aparecem nos resultados da consulta, excluindo as colunas que fazem parte da chave primária. Essa restrição existe porque a interface de leitura não é compatível com junções entre o índice e a tabela base. Se você precisar incluir outras colunas nos resultados da consulta, terá algumas opções:

  • Use uma cláusula STORING para armazenar as colunas adicionais no índice.
  • Consulte sem incluir as colunas adicionais e use as chaves primárias para enviar outra consulta que lê as colunas adicionais.

O Cloud Spanner retorna valores do índice em ordem de classificação crescente por chave de índice. Para recuperar os valores em ordem decrescente, siga estas etapas:

  • Anote a chave de índice com DESC: Por exemplo:

    CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle DESC);

    A anotação DESC se aplica a uma única chave de índice. Se o índice incluir mais de uma chave e você desejar que os resultados apareçam em ordem descendente com base em todas as chaves, inclua uma anotação DESC para cada chave.

  • Se a leitura especificar um intervalo de chaves, certifique-se de que o intervalo de chaves também esteja em ordem decrescente. Em outras palavras, o valor da chave inicial precisa ser maior que o valor da chave final.

O exemplo a seguir mostra como recuperar os valores de AlbumId e AlbumTitle usando o índice AlbumsByAlbumTitle:

C++

void ReadDataWithIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle";
  auto rows = client.Read("Albums", google::cloud::spanner::KeySet::All(),
                          {"AlbumId", "AlbumTitle"}, read_options);
  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_read_data_with_index]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithIndexAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
        public long MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithIndexAsync(string projectId, string instanceId, string databaseId,
        string startTitle, string endTitle)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand(
            "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
            + "{FORCE_INDEX=AlbumsByAlbumTitle} "
            + $"WHERE AlbumTitle >= @startTitle "
            + $"AND AlbumTitle < @endTitle",
            new SpannerParameterCollection
            {
                { "startTitle", SpannerDbType.String, startTitle },
                { "endTitle", SpannerDbType.String, endTitle }
            });

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func readUsingIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s\n", albumID, albumTitle)
	}
}

Java

static void readUsingIndex(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle"))) {
    while (resultSet.next()) {
      System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle'],
  keySet: {
    all: true,
  },
  index: 'AlbumsByAlbumTitle',
};

// Reads the Albums table using an index
try {
  const [rows] = await albumsTable.read(query);

  rows.forEach(row => {
    const json = row.toJSON();
    console.log(`AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`);
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * read_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle'],
        ['index' => 'AlbumsByAlbumTitle']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle']);
    }
}

Python

def read_data_with_index(instance_id, database_id):
    """Reads sample data from the database using an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table="Albums",
            columns=("AlbumId", "AlbumTitle"),
            keyset=keyset,
            index="AlbumsByAlbumTitle",
        )

        for row in results:
            print("AlbumId: {}, AlbumTitle: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle],
                     index: "AlbumsByAlbumTitle"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]}"
end

Cláusula STORING

Opcionalmente, é possível usar a cláusula STORING para armazenar uma cópia de uma coluna no índice. Esse tipo de índice oferece vantagens para consultas e chamadas de leitura usando o índice, ao custo de usar armazenamento extra:

  • As consultas SQL que usam o índice e selecionam as colunas armazenadas na cláusula STORING não exigem uma vinculação extra à tabela base.
  • As chamadas de leitura que usam o índice podem ler colunas armazenadas na cláusula STORING.

Por exemplo, supondo que você criou uma versão alternativa de AlbumsByAlbumTitle que armazena uma cópia da coluna MarketingBudget no índice (observe a cláusula STORING em negrito):

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

Com o antigo índice AlbumsByAlbumTitle, o Cloud Spanner precisa unificar o índice com a tabela base e recuperar a coluna da tabela base. Com o novo índice AlbumsByAlbumTitle2, o Cloud Spanner lê a coluna diretamente do índice, que é mais eficiente.

Se você usar a interface de leitura em vez de SQL, o novo índice AlbumsByAlbumTitle2 também permitirá que você leia a coluna MarketingBudget diretamente:

C++

void ReadDataWithStoringIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle2";
  auto rows =
      client.Read("Albums", google::cloud::spanner::KeySet::All(),
                  {"AlbumId", "AlbumTitle", "MarketingBudget"}, read_options);
  using RowType =
      std::tuple<std::int64_t, std::string, absl::optional<std::int64_t>>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_read_data_with_storing_index]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithStoringIndexAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
        public long? MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithStoringIndexAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        var cmd = connection.CreateSelectCommand(
            "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
            + "{FORCE_INDEX=AlbumsByAlbumTitle2}");

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func readStoringIndex(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle2", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle", "MarketingBudget"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle, &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
}

Java

static void readStoringIndex(DatabaseClient dbClient) {
  // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget.
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle2",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong(0),
          resultSet.getString(1),
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
    }
  }
}

Node.js

// "Storing" indexes store copies of the columns they index
// This speeds up queries, but takes more space compared to normal indexes
// See the link below for more information:
// https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
  keySet: {
    all: true,
  },
  index: 'AlbumsByAlbumTitle2',
};

// Reads the Albums table using a storing index
try {
  const [rows] = await albumsTable.read(query);

  rows.forEach(row => {
    const json = row.toJSON();
    let rowString = `AlbumId: ${json.AlbumId}`;
    rowString += `, AlbumTitle: ${json.AlbumTitle}`;
    if (json.MarketingBudget) {
      rowString += `, MarketingBudget: ${json.MarketingBudget}`;
    }
    console.log(rowString);
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index with a storing
 * clause.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_storing_index` sample or by running this DDL statement
 * against your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
 *     STORING (MarketingBudget)
 *
 * Example:
 * ```
 * read_data_with_storing_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_storing_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
        ['index' => 'AlbumsByAlbumTitle2']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def read_data_with_storing_index(instance_id, database_id):
    """Reads sample data from the database using an index with a storing
    clause.

    The index must exist before running this sample. You can add the index
    by running the `add_soring_index` sample or by running this DDL statement
    against your database:

        CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
        STORING (MarketingBudget)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table="Albums",
            columns=("AlbumId", "AlbumTitle", "MarketingBudget"),
            keyset=keyset,
            index="AlbumsByAlbumTitle2",
        )

        for row in results:
            print(u"AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle, :MarketingBudget],
                     index: "AlbumsByAlbumTitle2"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Indexação de valores NULL

Por padrão, o Cloud Spanner indexa os valores NULL. Por exemplo, lembre-se da definição do índice SingersByFirstLastName na tabela Singers:

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

Todas as linhas de Singers são indexadas mesmo se FirstName ou LastName, ou ambas, forem NULL.

Um diagrama mostra linhas que são omitidas de um índice filtrado NULL.

Quando valores NULL são indexados, é possível realizar consultas SQL eficientes e leituras sobre dados que incluem valores NULL. Por exemplo, use esta instrução de consulta SQL para encontrar todos Singers com NULL FirstName:

SELECT s.SingerId, s.FirstName, s.LastName
    FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
    WHERE s.FirstName IS NULL;

Ordem de classificação para valores NULL

O Cloud Spanner classifica NULL como o menor valor para qualquer tipo. Para uma coluna em ordem crescente (ASC), os valores NULL são classificados primeiro. Para uma coluna em ordem decrescente (DESC), os NULL valores classificam por último.

Como desativar a indexação de valores NULL

Para desativar a indexação de nulos, adicione a palavra-chave NULL_FILTERED à definição do índice. Índices NULL_FILTERED são particularmente úteis para indexar colunas esparsas, onde a maioria das linhas contém um valor NULL. Nesses casos, o índice NULL_FILTERED pode ser consideravelmente menor e mais eficiente de manter do que um índice normal que inclua valores NULL.

Aqui está uma definição alternativa de SingersByFirstLastName que não indexa os valores NULL:

CREATE NULL_FILTERED INDEX SingersByFirstLastNameNoNulls
    ON Singers(FirstName, LastName);

A palavra-chave NULL_FILTERED se aplica a todas as colunas de chave de índice. Não é possível especificar a filtragem de NULL por coluna.

Criar um índice NULL_FILTERED impede que o Cloud Spanner o use para algumas consultas. Por exemplo, o Cloud Spanner não usa o índice para esta consulta, porque o índice omite Singers linhas para as quais LastName é NULL. Como resultado, usar o índice impediria que a consulta retornasse as linhas corretas:

FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
    WHERE FirstName = "John";

Para permitir que o Cloud Spanner use o índice, você deve regravá-la para que exclua as linhas que também foram excluídas do índice:

SELECT FirstName, LastName
    FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
    WHERE FirstName = "John" AND LastName IS NOT NULL;

Índices exclusivos

Os índices podem ser declarados como UNIQUE. Os índices UNIQUE adicionam uma restrição aos dados indexados que proíbem entradas duplicadas para uma determinada chave de índice. Essa restrição é imposta pelo Cloud Spanner no momento da confirmação da transação. Especificamente, qualquer transação que gere várias entradas de índice para a mesma chave falhará na confirmação.

Se uma tabela contiver dados não UNIQUE para começar, a tentativa de criar um índice UNIQUE nela falhará.

Uma observação sobre índices UNIQUE NULL_FILTERED

Um índice UNIQUE NULL_FILTERED não impõe a exclusividade da chave de índice quando pelo menos uma das partes de chave do índice é NULL.

Por exemplo, suponha que você tenha criado a tabela e o índice a seguir:

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);

As duas linhas a seguir em ExampleTable têm os mesmos valores para as chaves de índice secundário Key1, Key2 e Col1:

1, NULL, 1, 1
1, NULL, 2, 1

Como Key2 é NULL e o índice é NULL_FILTERED, as linhas não estarão presentes no índice ExampleIndex. Como eles não são inseridos no índice, o índice não os rejeita por violar a exclusividade em (Key1, Key2, Col1).

Se desejar que o índice imponha a exclusividade dos valores da tupla (Key1, Key2, Col1), anote Key2 com NOT NULL na definição da tabela ou crie o índice sem NULL_FILTERED.

Como descartar um índice

Use a instrução DROP INDEX para soltar um índice secundário do seu esquema.

Para descartar o índice chamado SingersByFirstLastName:

DROP INDEX SingersByFirstLastName;

Indexação para verificação mais rápida

Quando o Cloud Spanner precisa executar uma verificação de tabela (em vez de uma pesquisa indexada), para buscar valores de uma ou mais colunas, é possível receber resultados mais rápidos, se houver um índice para essas colunas, e na ordem especificada pela consulta. Se você realizar consultas com frequência que exigem verificações, crie índices secundários para ajudar a que elas ocorram com mais eficiência.

Se você precisar que o Cloud Spanner verifique com frequência a chave primária ou outro índice de uma tabela na ordem inversa, aumente a eficiência por meio de um índice secundário que torna a ordem desejada explícita.

Por exemplo, a consulta abaixo sempre retorna um resultado rápido, mesmo que o Cloud Spanner precise verificar Songs para encontrar o valor mais baixo de SongId:

SELECT SongId FROM Songs LIMIT 1;

SongId é a chave primária da tabela, armazenada (como todas as chaves primárias) em ordem crescente. O Cloud Spanner pode verificar o índice dessa chave e encontrar o primeiro resultado rapidamente.

No entanto, sem a ajuda de um índice secundário, a consulta a seguir não seria retornada tão rapidamente, especialmente se Songs tivesse muitos dados:

SELECT SongId FROM Songs ORDER BY SongId DESC LIMIT 1;

Mesmo que SongId seja a chave primária da tabela, o Cloud Spanner não tem como buscar o valor mais alto da coluna sem recorrer a uma verificação completa da tabela.

Adicionar o índice a seguir permite que essa consulta retorne mais rapidamente:

CREATE INDEX SongIdDesc On Songs(SongId DESC);

Com esse índice em vigor, o Cloud Spanner o usaria para retornar um resultado para a segunda consulta mais rapidamente.

A seguir