Gestionar el optimizador de consultas

En esta página se describe cómo gestionar el optimizador de consultas en Spanner para bases de datos con dialecto de GoogleSQL y con dialecto de PostgreSQL.

El optimizador de consultas de Spanner determina la forma más eficiente de ejecutar una consulta de SQL. Sin embargo, el plan de consulta determinado por el optimizador puede cambiar ligeramente cuando el propio optimizador de consultas evoluciona o cuando se actualizan las estadísticas de la base de datos. Para minimizar la posibilidad de que el rendimiento se vea afectado cuando cambien el optimizador de consultas o las estadísticas, Spanner ofrece las siguientes opciones de consulta.

  • optimizer_version los cambios en el optimizador de consultas se agrupan y se publican como versiones del optimizador. Spanner empieza a usar la versión más reciente del optimizador como predeterminada al menos 30 días después de que se publique esa versión. Puede usar la opción de versión del optimizador de consultas para ejecutar consultas con una versión anterior del optimizador.

  • optimizer_statistics_package Spanner actualiza las estadísticas del optimizador con regularidad. Las nuevas estadísticas se ofrecen como paquete. Esta opción de consulta especifica un paquete de estadísticas que el optimizador de consultas debe usar al compilar una consulta de SQL. El paquete especificado debe tener la recogida de elementos no utilizados inhabilitada:

GoogleSQL

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

PostgreSQL

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

En esta guía se muestra cómo definir estas opciones individuales en diferentes ámbitos de Spanner.

Lista de opciones del optimizador de consultas

Spanner almacena información sobre las versiones del optimizador y los paquetes de estadísticas disponibles que puedes seleccionar.

Versiones del optimizador

La versión del optimizador de consultas es un valor entero que se incrementa en 1 con cada actualización. La versión más reciente del optimizador de consultas es la 8.

Ejecuta la siguiente instrucción SQL para obtener una lista de todas las versiones del optimizador admitidas, junto con sus fechas de lanzamiento correspondientes y si esa versión es la predeterminada. El número de versión más alto que se devuelve es la última versión compatible del optimizador.

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Versión predeterminada

De forma predeterminada, Spanner empieza a usar la versión más reciente del optimizador al menos 30 días después de que se publique esa versión. Durante el periodo de más de 30 días entre el lanzamiento de una nueva versión y el momento en que se convierte en la predeterminada, te recomendamos que pruebes las consultas con la nueva versión para detectar cualquier regresión.

Para encontrar la versión predeterminada, ejecuta la siguiente instrucción SQL:

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

La consulta devuelve una lista de todas las versiones del optimizador admitidas. La columna IS_DEFAULT indica qué versión es la predeterminada.

Para obtener más información sobre cada versión, consulta el historial de versiones del optimizador de consultas.

Paquetes de estadísticas de Optimizer

A cada nuevo paquete de estadísticas del optimizador que crea Spanner se le asigna un nombre de paquete que es único en la base de datos correspondiente.

El formato del nombre del paquete es auto_{PACKAGE_TIMESTAMP}UTC. En GoogleSQL, la instrucción ANALYZE activa la creación del nombre del paquete de estadísticas. En PostgreSQL, la instrucción ANALYZE realiza esta tarea. El formato del nombre del paquete de estadísticas es analyze_{PACKAGE_TIMESTAMP}UTC, donde {PACKAGE_TIMESTAMP} es la marca de tiempo, en la zona horaria UTC, de cuándo se inició la construcción de estadísticas. Ejecuta la siguiente instrucción SQL para devolver una lista de todos los paquetes de estadísticas del optimizador disponibles.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

De forma predeterminada, Spanner usa el paquete de estadísticas del optimizador más reciente, a menos que la base de datos o la consulta estén fijadas a un paquete anterior mediante uno de los métodos descritos en esta página.

Precedencia de la anulación de opciones

Si usas una base de datos con dialecto GoogleSQL, Spanner ofrece varias formas de cambiar las opciones del optimizador. Por ejemplo, puedes definir las opciones de una consulta específica o configurar la opción en la biblioteca de cliente a nivel de proceso o de consulta. Cuando una opción se define de varias formas, se aplica el siguiente orden de precedencia. (Selecciona un enlace para ir a esa sección del documento).

Spanner predeterminado ← opción de base de datos ← aplicación cliente ← variable de entorno ← consulta de cliente ← sugerencia de instrucción

Por ejemplo, a continuación se explica cómo interpretar el orden de precedencia al definir la versión del optimizador de consultas:

Cuando creas una base de datos, se usa la versión predeterminada del optimizador de Spanner. Si se define la versión del optimizador con uno de los métodos indicados anteriormente, tendrá prioridad sobre cualquier otro elemento situado a su izquierda. Por ejemplo, si define el optimizador de una aplicación mediante una variable de entorno, esta tendrá prioridad sobre cualquier valor que defina para la base de datos mediante la opción de base de datos. Definir la versión del optimizador mediante una sugerencia de instrucción tiene la prioridad más alta para la consulta en cuestión, por lo que prevalece sobre el valor definido con cualquier otro método.

En las siguientes secciones se proporcionan más detalles sobre cada método.

Definir opciones del optimizador a nivel de base de datos

Para definir la versión predeterminada del optimizador en una base de datos, usa el siguiente comando DDL ALTER DATABASE. Para definir esta opción, no es necesario que todas las consultas ejecuten esa versión. En su lugar, establece un límite superior en la versión de QO utilizada para las consultas. Su objetivo es mitigar las regresiones que se producen después de que se lance una nueva versión del optimizador.

GoogleSQL 

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  8);

PostgreSQL 

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Puedes definir el paquete de estadísticas de forma similar, como se muestra en el siguiente ejemplo.

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

También puedes definir más de una opción a la vez, como se muestra en el siguiente comando DDL.

GoogleSQL 

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

Puedes ejecutar ALTER DATABASE en la CLI de gcloud con el comando gcloud CLI databases ddl update de la siguiente manera.

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'

Si asignas el valor NULL (en GoogleSQL) o DEFAULT (en PostgreSQL) a una opción de base de datos, se borrará y se usará el valor predeterminado.

Para ver el valor actual de estas opciones en una base de datos, consulta la vista INFORMATION_SCHEMA.DATABASE_OPTIONS de GoogleSQL o la tabla information_schema database_options de PostgreSQL, como se indica a continuación.

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

Definir opciones de optimización con bibliotecas de cliente

Cuando interactúas de forma programática con Spanner a través de bibliotecas de cliente, hay varias formas de cambiar las opciones de consulta de tu aplicación cliente.

Debes usar las versiones más recientes de las bibliotecas de cliente para definir las opciones del optimizador.

Definir las opciones del optimizador de un cliente de base de datos

Una aplicación puede definir opciones de optimizador de forma global en la biblioteca de cliente configurando la propiedad de opciones de consulta, tal como se muestra en los siguientes fragmentos de código. Los ajustes del optimizador se almacenan en la instancia del cliente y se aplican a todas las consultas que se ejecutan durante la vida útil del cliente. Aunque las opciones se aplican a nivel de base de datos en el backend, cuando se definen a nivel de cliente, se aplican a todas las bases de datos conectadas a ese 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

Definir opciones del optimizador con variables de entorno

Para probar diferentes ajustes del optimizador sin tener que volver a compilar la aplicación, puedes definir las variables de entorno SPANNER_OPTIMIZER_VERSION y SPANNER_OPTIMIZER_STATISTICS_PACKAGE, y ejecutar la aplicación, como se muestra en el siguiente fragmento de código.

Linux o 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"

Los valores de las opciones del optimizador de consultas especificadas se leen y se almacenan en la instancia del cliente en el momento de la inicialización del cliente y se aplican a todas las consultas que se ejecuten durante el tiempo de vida del cliente.

Definir opciones del optimizador para una consulta de cliente

Puede especificar un valor para la versión del optimizador o la versión del paquete de estadísticas a nivel de consulta en su aplicación cliente. Para ello, especifique una propiedad de opciones de consulta al crear la 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

Definir opciones del optimizador para una consulta mediante una sugerencia de instrucción

Una sugerencia de instrucción es una sugerencia sobre una instrucción de consulta que cambia la ejecución de la consulta con respecto al comportamiento predeterminado. Al definir la sugerencia OPTIMIZER_VERSION en una instrucción, se obliga a que esa consulta se ejecute con la versión del optimizador de consultas especificada.

La sugerencia OPTIMIZER_VERSION tiene la precedencia de la versión del optimizador más alta. Si se especifica la sugerencia de la instrucción, se usa independientemente de los demás ajustes de la versión del optimizador.

GoogleSQL 

@{OPTIMIZER_VERSION=8} SELECT * FROM MyTable;

PostgreSQL 

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

También puedes usar el literal latest_version para definir la versión del optimizador de una consulta como la más reciente, tal como se muestra aquí.

GoogleSQL 

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL 

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

Al definir la sugerencia OPTIMIZER_STATISTICS_PACKAGE en una instrucción, se fuerza la ejecución de esa consulta con la versión del paquete de estadísticas del optimizador de consultas especificado. El paquete especificado debe tener inhabilitada la recolección de elementos no utilizados:

GoogleSQL 

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

PostgreSQL 

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

La sugerencia OPTIMIZER_STATISTICS_PACKAGE tiene la prioridad más alta en la configuración del paquete del optimizador. Si se especifica la sugerencia de la instrucción, se usará independientemente de los demás ajustes de la versión del paquete del optimizador.

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

También puedes usar el literal latest para usar el paquete de estadísticas más reciente.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

Ambas sugerencias se pueden definir en una sola instrucción, como se muestra en el siguiente ejemplo.

El literal default_version asigna a una consulta la versión predeterminada del optimizador, que puede ser diferente de la más reciente. Para obtener más información, consulta Versión predeterminada.

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;

Definir opciones del optimizador al usar el controlador JDBC de Spanner

Puede anular el valor predeterminado de la versión del optimizador y del paquete de estadísticas especificando opciones en la cadena de conexión JDBC, como se muestra en el siguiente ejemplo.

Estas opciones solo se admiten en las versiones más recientes del controlador JDBC de 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));
        }
      }
    }
  }
}

También puedes definir la versión del optimizador de consultas mediante la instrucción SET OPTIMIZER_VERSION, como se muestra en el siguiente ejemplo.

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 obtener más información sobre cómo usar el controlador de código abierto, consulta Usar el controlador JDBC de código abierto.

Cómo se gestionan las versiones no válidas del optimizador

Spanner admite un intervalo de versiones del optimizador. Este intervalo cambia con el tiempo cuando se actualiza el optimizador de consultas. Si la versión que especifica está fuera del intervalo, la consulta falla. Por ejemplo, si intentas ejecutar una consulta con la sugerencia de instrucción @{OPTIMIZER_VERSION=9}, pero el número de versión del optimizador más reciente es 8, Spanner responde con este mensaje de error:

Query optimizer version: 9 is not supported

Gestionar un ajuste no válido del paquete de estadísticas del optimizador

Puedes fijar tu base de datos o consulta a cualquier paquete de estadísticas disponible con uno de los métodos descritos anteriormente en esta página. Una consulta falla si se proporciona un nombre de paquete de estadísticas no válido. Un paquete de estadísticas especificado por una consulta debe ser:

Determinar la versión del optimizador de consultas que se ha usado para ejecutar una consulta

La versión del optimizador que se usa en una consulta se puede ver en la Google Cloud consola y en la CLI de Google Cloud.

Google Cloud consola

Para ver la versión del optimizador que se ha usado en una consulta, ejecútala en la página Spanner Studio de la consola de Google Cloud y, a continuación, selecciona la pestaña Explicación. Debería aparecer un mensaje similar al siguiente:

Versión del optimizador de consultas: 8

CLI de gcloud

Para ver la versión que se usa al ejecutar una consulta en la CLI de gcloud, asigna el valor PROFILE a la marca --query-mode, como se muestra en el siguiente fragmento de código.

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

Visualizar la versión del optimizador de consultas en Explorador de métricas

Cloud Monitoring recoge mediciones para ayudarte a entender el rendimiento de tus aplicaciones y servicios del sistema. Una de las métricas recogidas de Spanner es el recuento de consultas, que mide el número de consultas de una instancia, muestreadas a lo largo del tiempo. Aunque esta métrica es muy útil para ver las consultas agrupadas por código de error, también podemos usarla para ver qué versión del optimizador se ha usado para ejecutar cada consulta.

Puedes usar el explorador de métricas de la consola deGoogle Cloud para visualizar el número de consultas de tu instancia de base de datos. En la figura 1 se muestra el recuento de consultas de tres bases de datos. Puede ver qué versión del optimizador se está usando en cada base de datos.

La tabla que hay debajo del gráfico de esta figura muestra que my-db-1 ha intentado ejecutar una consulta con una versión no válida del optimizador, lo que ha devuelto el estado Uso incorrecto y ha dado como resultado un recuento de consultas de 0. Las otras bases de datos ejecutaron consultas con las versiones 1 y 2 del optimizador, respectivamente.

Número de consultas en el explorador de métricas agrupadas por versión del optimizador de consultas

Imagen 1. Número de consultas que se muestra en el explorador de métricas con consultas agrupadas por versión del optimizador.

Para configurar un gráfico similar en tu instancia, sigue estos pasos:

  1. Ve al Explorador de métricas en la Google Cloud consola.
  2. En el campo Tipo de recurso, selecciona Cloud Spanner Instance.
  3. En el campo Métrica, selecciona Count of queries.
  4. En el campo Agrupar por, selecciona database, optimizer_version y status.

En este ejemplo no se muestra el caso en el que se usa una versión diferente del optimizador para distintas consultas en la misma base de datos. En ese caso, el gráfico mostraría un segmento de barra por cada combinación de versión de base de datos y optimizador.

Para saber cómo usar Cloud Monitoring para monitorizar tus instancias de Spanner, consulta Monitorizar con Cloud Monitoring.