Faça a gestão do otimizador de consultas

Esta página descreve como gerir o otimizador de consultas no Spanner para bases de dados com dialeto GoogleSQL e bases de dados com dialeto PostgreSQL.

O otimizador de consultas do Spanner determina a forma mais eficiente de executar uma consulta SQL. No entanto, o plano de consulta determinado pelo otimizador pode mudar ligeiramente quando o próprio otimizador de consultas evolui ou quando as estatísticas da base de dados são atualizadas. Para minimizar qualquer potencial regressão de desempenho quando o otimizador de consultas ou as estatísticas mudam, o Spanner oferece as seguintes opções de consulta.

  • optimizer_version: as alterações ao otimizador de consultas são agrupadas e lançadas como versões do otimizador. O Spanner começa a usar a versão mais recente do otimizador como predefinição, pelo menos, 30 dias após o lançamento dessa versão. Pode usar a opção de versão do otimizador de consultas para executar consultas numa versão mais antiga do otimizador.

  • optimizer_statistics_package: o Spanner atualiza as estatísticas do otimizador regularmente. As novas estatísticas são disponibilizadas como um pacote. Esta opção de consulta especifica um pacote de estatísticas para o otimizador de consultas usar ao compilar uma consulta SQL. O pacote especificado tem de ter a recolha de lixo desativada:

GoogleSQL

 ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)

PostgreSQL

ALTER STATISTICS spanner."<package_name>" SET OPTIONS (allow_gc = true)

Este guia mostra como definir estas opções individuais em diferentes âmbitos no Spanner.

Liste as opções do otimizador de consultas

O Spanner armazena informações sobre as versões do otimizador disponíveis e os pacotes de estatísticas que pode selecionar.

Versões do otimizador

A versão do otimizador de consultas é um valor inteiro, incrementado em 1 com cada atualização. A versão mais recente do otimizador de consultas é a 8.

Execute a seguinte declaração SQL para devolver uma lista de todas as versões do otimizador suportadas, juntamente com as respetivas datas de lançamento e se essa versão é a predefinição. O número de versão mais elevado devolvido é a versão mais recente suportada do otimizador.

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Versão predefinida

Por predefinição, o Spanner começa a usar a versão mais recente do otimizador, pelo menos, 30 dias após o lançamento dessa versão. Durante o período de mais de 30 dias entre um novo lançamento e esse lançamento tornar-se o predefinido, recomendamos que teste as consultas com a nova versão para detetar qualquer regressão.

Para encontrar a versão predefinida, execute a seguinte declaração SQL:

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

A consulta devolve uma lista de todas as versões do otimizador suportadas. A coluna IS_DEFAULT especifica qual é a versão predefinida atual.

Para ver detalhes sobre cada versão, consulte o artigo Histórico de versões do otimizador de consultas.

Pacotes de estatísticas do Otimizador

Cada novo pacote de estatísticas do otimizador que o Spanner cria é-lhe atribuído um nome de pacote que é garantidamente único na base de dados especificada.

O formato do nome do pacote é auto_{PACKAGE_TIMESTAMP}UTC. No GoogleSQL, a declaração ANALYZE aciona a criação do nome do pacote de estatísticas. No PostgreSQL, a declaração ANALYZE realiza esta tarefa. O formato do nome do pacote de estatísticas é analyze_{PACKAGE_TIMESTAMP}UTC, onde {PACKAGE_TIMESTAMP} é a data/hora, no fuso horário UTC, em que a construção das estatísticas começou. Execute a seguinte declaração SQL para devolver uma lista de todos os pacotes de estatísticas do otimizador disponíveis.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Por predefinição, o Spanner usa o pacote de estatísticas do otimizador mais recente, a menos que a base de dados ou a consulta esteja fixada a um pacote mais antigo através de um dos métodos descritos nesta página.

Precedência da substituição de opções

Se estiver a usar uma base de dados com dialeto GoogleSQL, o Spanner oferece várias formas de alterar as opções do otimizador. Por exemplo, pode definir as opções para uma consulta específica ou configurar a opção na biblioteca cliente ao nível do processo ou da consulta. Quando uma opção é definida de várias formas, aplica-se a seguinte ordem de precedência. (Selecione um link para aceder a essa secção neste documento).

Opção de base de dados do Spanner predefinida ← ← app cliente ← variável de ambiente ← consulta do cliente ← sugestão de declaração

Por exemplo, veja como interpretar a ordem de precedência ao definir a versão do otimizador de consultas:

Quando cria uma base de dados, esta usa a versão do otimizador predefinida do Spanner. A definição da versão do otimizador através de um dos métodos indicados anteriormente tem precedência sobre tudo o que estiver à sua esquerda. Por exemplo, definir o otimizador para uma app através de uma variável de ambiente tem precedência sobre qualquer valor que defina para a base de dados através da opção de base de dados. A definição da versão do otimizador através de uma sugestão de declaração tem a precedência mais elevada para a consulta especificada, tendo precedência sobre o valor definido através de qualquer outro método.

As secções seguintes fornecem mais detalhes sobre cada método.

Defina opções do otimizador ao nível da base de dados

Para definir a versão do otimizador predefinida numa base de dados, use o seguinte comando DDL ALTER DATABASE. A definição desta opção não exige que todas as consultas executem essa versão. Em vez disso, define um limite superior para a versão do QO usada para consultas. A sua utilização destina-se a mitigar as regressões que ocorrem após o lançamento de uma nova versão do otimizador.

GoogleSQL 

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  8);

PostgreSQL 

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Pode definir o pacote de estatísticas de forma semelhante, conforme mostrado no exemplo seguinte.

GoogleSQL 

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_statistics_package = "auto_20191128_14_47_22UTC");

PostgreSQL 

ALTER DATABASE MyDatabase
SET spanner.optimizer_statistics_package = "auto_20191128_14_47_22UTC";

Também pode definir mais de uma opção em simultâneo, conforme mostrado no comando LDD seguinte.

GoogleSQL 

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 8,
            optimizer_statistics_package = "auto_20191128_14_47_22UTC");

Pode executar ALTER DATABASE na CLI gcloud com o comando gcloud CLI databases ddl update da seguinte forma.

GoogleSQL 

gcloud spanner databases ddl update MyDatabase --instance=test-instance \
    --ddl='ALTER DATABASE MyDatabase SET OPTIONS ( optimizer_version = 8 )'

PostgreSQL 

gcloud spanner databases ddl update MyDatabase --instance=test-instance \
  --ddl='ALTER DATABASE MyDatabase SET spanner.optimizer_version = 8'

Definir uma opção de base de dados como NULL (em GoogleSQL) ou DEFAULT (em PostgreSQL) limpa-a para que seja usado o valor predefinido.

Para ver o valor atual destas opções para uma base de dados, consulte a vista INFORMATION_SCHEMA.DATABASE_OPTIONS para o GoogleSQL ou a tabela information_schema database_options para o PostgreSQL, da seguinte forma.

GoogleSQL 

SELECT
  s.OPTION_NAME,
  s.OPTION_VALUE
FROM
  INFORMATION_SCHEMA.DATABASE_OPTIONS s
WHERE
  s.SCHEMA_NAME=""
  AND s.OPTION_NAME IN ('optimizer_version', 'optimizer_statistics_package')

PostgreSQL 

  SELECT
    s.option_name,
    s.option_value
  FROM
    information_schema.database_options s
  WHERE
    s.schema_name='public'
    AND s.option_name IN ('optimizer_version',
      'optimizer_statistics_package')

Defina opções de otimização com bibliotecas de cliente

Quando interage programaticamente com o Spanner através de bibliotecas de cliente, existem várias formas de alterar as opções de consulta para a sua aplicação cliente.

Tem de usar as versões mais recentes das bibliotecas de cliente para definir opções do otimizador.

Defina opções do otimizador para um cliente de base de dados

Uma aplicação pode definir opções do otimizador globalmente na biblioteca cliente configurando a propriedade de opções de consulta, conforme mostrado nos seguintes fragmentos de código. As definições do otimizador são armazenadas na instância do cliente e são aplicadas a todas as consultas executadas ao longo da duração do cliente. Embora as opções se apliquem ao nível da base de dados no back-end, quando as opções são definidas ao nível do cliente, aplicam-se a todas as bases de dados ligadas a esse cliente.

C++

namespace spanner = ::google::cloud::spanner;
spanner::Client client(
    spanner::MakeConnection(db),
    google::cloud::Options{}
        .set<spanner::QueryOptimizerVersionOption>("1")
        .set<spanner::QueryOptimizerStatisticsPackageOption>(
            "auto_20191128_14_47_22UTC"));

C#


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

public class CreateConnectionWithQueryOptionsAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public int SingerId { get; set; }
        public string AlbumTitle { get; set; }
    }

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

        using var connection = new SpannerConnection(connectionString)
        {
            // Set query options on the connection.
            QueryOptions = QueryOptions.Empty
                .WithOptimizerVersion("1")
                // The list of available statistics packages for the database can
                // be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
                // table.
                .WithOptimizerStatisticsPackage("latest")
        };

        var albums = new List<Album>();
        var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"time"

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

func createClientWithQueryOptions(w io.Writer, database string) error {
	ctx := context.Background()
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	client, err := spanner.NewClientWithConfig(
		ctx, database, spanner.ClientConfig{QueryOptions: queryOptions},
	)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void clientWithQueryOptions(DatabaseId db) {
  SpannerOptions options =
      SpannerOptions.newBuilder()
          .setDefaultQueryOptions(
              db, QueryOptions
                  .newBuilder()
                  .setOptimizerVersion("1")
                  // The list of available statistics packages can be found by querying the
                  // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                  .setOptimizerStatisticsPackage("latest")
                  .build())
          .build();
  Spanner spanner = options.getService();
  DatabaseClient dbClient = spanner.getDatabaseClient(db);
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

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,
  {},
  {
    optimizerVersion: '1',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  },
);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
};

// 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;
use Google\Cloud\Spanner\Database;

/**
 * Create a client with query options.
 * Example:
 * ```
 * create_client_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_client_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient([
        'queryOptions' => [
            'optimizerVersion' => '1',
            // Pin the statistics package used for this client instance to the
            // latest version. The list of available statistics packages can be
            // found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
            // table.
            'optimizerStatisticsPackage' => 'latest'
        ]
    ]);
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues'
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client(
    query_options={
        "optimizer_version": "1",
        "optimizer_statistics_package": "latest",
    }
)
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".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"

query_options = {
  optimizer_version: "1",
  # The list of available statistics packages can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

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

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"

client.execute(sql_query).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

Defina opções do otimizador com variáveis de ambiente

Para facilitar a experimentação de diferentes definições do otimizador sem ter de recompilar a app, pode definir as variáveis de ambiente SPANNER_OPTIMIZER_VERSION e SPANNER_OPTIMIZER_STATISTICS_PACKAGE e executar a app, conforme mostrado no fragmento seguinte.

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="8"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="8"
  set SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Os valores das opções do otimizador de consultas especificados são lidos e armazenados na instância do cliente no momento da inicialização do cliente e aplicam-se a todas as consultas executadas durante o ciclo de vida do cliente.

Defina opções do otimizador para uma consulta de cliente

Pode especificar um valor para a versão do otimizador ou a versão do pacote de estatísticas ao nível da consulta na sua aplicação cliente, especificando uma propriedade de opções de consulta quando cria a consulta.

C++

void QueryWithQueryOptions(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto sql = spanner::SqlStatement("SELECT SingerId, FirstName FROM Singers");
  auto opts =
      google::cloud::Options{}
          .set<spanner::QueryOptimizerVersionOption>("1")
          .set<spanner::QueryOptimizerStatisticsPackageOption>("latest");
  auto rows = client.ExecuteQuery(std::move(sql), std::move(opts));

  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "FirstName: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_query_with_query_options]\n";
}

C#


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

public class RunCommandWithQueryOptionsAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task<List<Album>> RunCommandWithQueryOptionsAsync(string projectId, string instanceId, string databaseId)
    {
        var connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");

        cmd.QueryOptions = QueryOptions.Empty
            .WithOptimizerVersion("1")
            // The list of available statistics packages for the database can
            // be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
            // table.
            .WithOptimizerStatisticsPackage("latest");
        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album()
            {
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumTitle = reader.GetFieldValue<string>("AlbumTitle")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"time"

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

func queryWithQueryOptions(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 VenueId, VenueName, LastUpdateTime FROM Venues`}
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	iter := client.Single().QueryWithOptions(ctx, stmt, queryOptions)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void queryWithQueryOptions(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement
                  .newBuilder("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")
                  .withQueryOptions(QueryOptions
                      .newBuilder()
                      .setOptimizerVersion("1")
                      // The list of available statistics packages can be found by querying the
                      // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                      .setOptimizerStatisticsPackage("latest")
                      .build())
                  .build())) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

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 query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
  queryOptions: {
    optimizerVersion: 'latest',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  },
};

// 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;
use Google\Cloud\Spanner\Database;

/**
 * Queries sample data using SQL with query options.
 * Example:
 * ```
 * query_data_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues',
        [
            'queryOptions' => [
                'optimizerVersion' => '1',
                // Pin the statistics package to the latest version just for
                // this query.
                'optimizerStatisticsPackage' => 'latest'
            ]
        ]
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues",
        query_options={
            "optimizer_version": "1",
            "optimizer_statistics_package": "latest",
        },
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".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

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options = {
  optimizer_version: "1",
  # The list of available statistics packagebs can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

client.execute(sql_query, query_options: query_options).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

Defina opções do otimizador para uma consulta através de uma sugestão de declaração

Uma sugestão de declaração é uma sugestão numa declaração de consulta que altera a execução da consulta a partir do comportamento predefinido. A definição da sugestão OPTIMIZER_VERSION numa declaração força a execução dessa consulta com a versão especificada do otimizador de consultas.

A sugestão OPTIMIZER_VERSION tem a precedência da versão do otimizador mais elevada. Se o indicador da declaração for especificado, é usado independentemente de todas as outras definições da versão do otimizador.

GoogleSQL 

@{OPTIMIZER_VERSION=8} SELECT * FROM MyTable;

PostgreSQL 

/*@OPTIMIZER_VERSION=8*/ SELECT * FROM MyTable;

Também pode usar o literal latest_version para definir a versão do otimizador de uma consulta para a versão mais recente, conforme mostrado aqui.

GoogleSQL 

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL 

/*@OPTIMIZER_VERSION=latest_version*/ SELECT * FROM MyTable;

A definição da sugestão OPTIMIZER_STATISTICS_PACKAGE numa declaração força a execução dessa consulta com a versão do pacote de estatísticas do otimizador de consultas especificada. O pacote especificado tem de ter a recolha de lixo desativada:

GoogleSQL 

ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)

PostgreSQL 

ALTER STATISTICS spanner."package_name" SET OPTIONS (allow_gc=false)

A sugestão OPTIMIZER_STATISTICS_PACKAGE tem precedência na definição do pacote de otimizadores. Se a sugestão de declaração for especificada, é usada independentemente de todas as outras definições da versão do pacote do otimizador.

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

Também pode usar o literal latest para usar o pacote de estatísticas mais recente.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

Ambas as sugestões podem ser definidas numa única declaração, como mostrado no exemplo seguinte.

O literal default_version define a versão do otimizador para uma consulta como a versão predefinida, que pode ser diferente da versão mais recente. Consulte o artigo Versão predefinida para ver detalhes.

GoogleSQL 

@{OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

PostgreSQL 

/*@OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC*/ SELECT * FROM KeyValue;

Defina opções do otimizador quando usar o controlador JDBC do Spanner

Pode substituir o valor predefinido da versão do otimizador e do pacote de estatísticas especificando opções na string de ligação JDBC, conforme mostrado no exemplo seguinte.

Estas opções só são suportadas nas versões mais recentes do controlador JDBC do Spanner.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class ConnectionWithQueryOptionsExample {

  static void connectionWithQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    connectionWithQueryOptions(projectId, instanceId, databaseId);
  }

  static void connectionWithQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String optimizerVersion = "1";
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s?optimizerVersion=%s",
            projectId, instanceId, databaseId, optimizerVersion);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Execute a query using the optimizer version '1'.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

Também pode definir a versão do otimizador de consultas através da declaração SET OPTIMIZER_VERSION , conforme mostrado no exemplo seguinte.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class SetQueryOptionsExample {

  static void setQueryOptions() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    setQueryOptions(projectId, instanceId, databaseId);
  }

  static void setQueryOptions(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // Instruct the JDBC connection to use version '1' of the query optimizer.
      // NOTE: Use `SET SPANNER.OPTIMIZER_VERSION='1`` when connected to a PostgreSQL database.
      statement.execute("SET OPTIMIZER_VERSION='1'");
      // Execute a query using the latest optimizer version.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf("%d %s %s%n", rs.getLong(1), rs.getString(2), rs.getString(3));
        }
      }
      // NOTE: Use `SHOW SPANNER.OPTIMIZER_VERSION` when connected to a PostgreSQL database.
      try (ResultSet rs = statement.executeQuery("SHOW VARIABLE OPTIMIZER_VERSION")) {
        while (rs.next()) {
          System.out.printf("Optimizer version: %s%n", rs.getString(1));
        }
      }
    }
  }
}

Para mais detalhes sobre a utilização do controlador de código aberto, consulte o artigo Usar o controlador JDBC de código aberto.

Como são processadas as versões inválidas do otimizador

O Spanner suporta um intervalo de versões do otimizador. Este intervalo muda ao longo do tempo quando o otimizador de consultas é atualizado. Se a versão especificada estiver fora do intervalo, a consulta falha. Por exemplo, se tentar executar uma consulta com a sugestão de declaração @{OPTIMIZER_VERSION=9}, mas o número da versão do otimizador mais recente for apenas 8, o Spanner responde com esta mensagem de erro:

Query optimizer version: 9 is not supported

Resolva uma definição de pacote de estatísticas do otimizador inválida

Pode fixar a sua base de dados ou consulta a qualquer pacote de estatísticas disponível através de um dos métodos descritos anteriormente nesta página. Uma consulta falha se for fornecido um nome de pacote de estatísticas inválido. Um pacote de estatísticas especificado por uma consulta tem de ser:

Determine a versão do otimizador de consultas usada para executar uma consulta

A versão do otimizador usada para uma consulta é visível através da Google Cloud consola e na CLI do Google Cloud.

Google Cloud consola

Para ver a versão do otimizador usada para uma consulta, execute a consulta na página Spanner Studio da Google Cloud consola e, em seguida, selecione o separador Explicação. Deve ver uma mensagem semelhante à seguinte:

Versão do otimizador de consultas: 8

CLI gcloud

Para ver a versão usada quando executa uma consulta na CLI gcloud, defina a flag --query-mode para PROFILE, conforme mostrado no fragmento seguinte.

gcloud spanner databases execute-sql MyDatabase --instance=test-instance \
    --query-mode=PROFILE --sql='SELECT * FROM MyTable'

Visualize a versão do otimizador de consultas no Explorador de métricas

O Cloud Monitoring recolhe medições para ajudar a compreender o desempenho das suas aplicações e serviços do sistema. Uma das métricas recolhidas para o Spanner é a contagem de consultas, que mede o número de consultas numa instância, amostradas ao longo do tempo. Embora esta métrica seja muito útil para ver as consultas agrupadas por código de erro, também podemos usá-la para ver que versão do otimizador foi usada para executar cada consulta.

Pode usar o Explorador de métricas na Google Cloud consola para visualizar a quantidade de consultas para a instância da base de dados. A Figura 1 mostra a contagem de consultas para três bases de dados. Pode ver que versão do otimizador está a ser usada em cada base de dados.

A tabela abaixo do gráfico nesta figura mostra que my-db-1 tentou executar uma consulta com uma versão do otimizador inválida, devolvendo o estado Bad usage e resultando numa contagem de consultas de 0. As outras bases de dados executaram consultas com as versões 1 e 2 do otimizador, respetivamente.

Contagem de consultas no Explorador de métricas agrupadas por versão do otimizador de consultas

Figura 1. Contagem de consultas apresentadas no explorador de métricas com consultas agrupadas por versão do otimizador.

Para configurar um gráfico semelhante para a sua instância:

  1. Navegue para o Explorador de métricas na Google Cloud consola.
  2. No campo Tipo de recurso, selecione Cloud Spanner Instance.
  3. No campo Métrica, selecione Count of queries.
  4. No campo Agrupar por, selecione database, optimizer_version e status.

Neste exemplo, não é apresentado o caso em que está a ser usada uma versão do otimizador diferente para consultas diferentes na mesma base de dados. Nesse caso, o gráfico apresentaria um segmento de barra para cada combinação de base de dados e versão do otimizador.

Para saber como usar o Cloud Monitoring para monitorizar as suas instâncias do Spanner, consulte o artigo Monitorizar com o Cloud Monitoring