Cette page a été traduite par l'API Cloud Translation.

Gérer l'optimiseur de requêtes

Cette page explique comment gérer l'optimiseur de requêtes dans Spanner pour les bases de données en dialecte GoogleSQL et en dialecte PostgreSQL.

L'optimiseur de requêtes Spanner détermine le moyen le plus efficace d'exécuter une requête SQL. Toutefois, le plan de requête déterminé par l'optimiseur peut varier légèrement lorsque l'optimiseur lui-même évolue ou lorsque les statistiques de la base de données sont mises à jour. Pour minimiser le risque de régression des performances lorsque l'optimiseur de requêtes ou les statistiques changent, Spanner fournit les options de requête suivantes.

optimizer_version: les modifications apportées à l'optimiseur de requêtes sont regroupées et publiées en tant que versions de l'optimiseur. Spanner commence à utiliser la dernière version de l'optimiseur par défaut au moins 30 jours après sa publication. Vous pouvez utiliser l'option de version de l'optimiseur de requêtes pour exécuter des requêtes sur une ancienne version de l'optimiseur.
optimizer_statistics_package: Spanner met à jour régulièrement les statistiques de l'optimiseur. De nouvelles statistiques sont disponibles sous forme de package. Cette option de requête spécifie un package de statistiques que l'optimiseur de requêtes doit utiliser lors de la compilation d'une requête SQL. La récupération de mémoire doit être désactivée pour le package spécifié:

GoogleSQL

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

PostgreSQL

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

Ce guide explique comment définir ces options individuelles à différents niveaux d'étendue dans Spanner.

Répertorier les options de l'optimiseur de requêtes

Spanner stocke des informations sur les versions d'optimiseur disponibles et les packages de statistiques que vous pouvez sélectionner.

Versions de l'optimiseur

La version de l'optimiseur de requêtes est une valeur entière, incrémentée de 1 à chaque mise à jour. La dernière version de l'optimiseur de requêtes est 8.

Exécutez l'instruction SQL suivante pour renvoyer la liste de toutes les versions de l'optimiseur prises en charge, ainsi que les dates de disponibilité correspondantes et l'indication de si cette version est la version par défaut. Le numéro de version le plus élevé renvoyé est la dernière version prise en charge de l'optimiseur.

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Version par défaut

Par défaut, Spanner commence à utiliser la dernière version de l'optimiseur au moins 30 jours après sa publication. Au cours de la période de 30 jours minimum entre la publication d'une nouvelle version et son adoption comme version par défaut, nous vous encourageons à tester les requêtes sur la nouvelle version afin de détecter toute régression.

Pour trouver la version par défaut, exécutez l'instruction SQL suivante :

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

La requête renvoie la liste de toutes les versions de l'optimiseur compatibles. La colonne IS_DEFAULT indique la version par défaut actuelle.

Pour en savoir plus sur chaque version, consultez l'article Historique des versions de l'optimiseur de requêtes.

Packages de statistiques de l'optimiseur

Chaque nouveau package de statistiques d'optimiseur créé par Spanner est associé à un nom de package qui est garanti d'être unique dans la base de données donnée.

Le format du nom du package est auto_{PACKAGE_TIMESTAMP}UTC. Dans GoogleSQL, l'instruction ANALYZE déclenche la création du nom du package de statistiques. Dans PostgreSQL, l'instruction ANALYZE effectue cette tâche. Le format du nom du package de statistiques est analyze_{PACKAGE_TIMESTAMP}UTC, où {PACKAGE_TIMESTAMP} correspond à l'horodatage, au fuseau horaire UTC, du début de la création des statistiques. Exécutez l'instruction SQL suivante pour renvoyer la liste de tous les packages de statistiques de l'optimiseur disponibles.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Par défaut, Spanner utilise le dernier package de statistiques de l'optimiseur, sauf si la base de données ou la requête est épinglée à un package plus ancien à l'aide de l'une des méthodes décrites sur cette page.

Priorité du remplacement d'option

Si vous utilisez une base de données en dialecte GoogleSQL, Spanner propose plusieurs méthodes pour modifier les options de l'optimiseur. Par exemple, vous pouvez définir l'option ou les options d'une requête spécifique ou configurer l'option dans la bibliothèque cliente au niveau du processus ou de la requête. Lorsqu'une option est définie de plusieurs manières, l'ordre de priorité suivant s'applique. (Sélectionnez un lien pour accéder à cette section dans ce document.)

Valeur par défaut Spanner ← option de base de données ← application cliente ← variable d'environnement ← requête client ← optimisation de déclaration

Par exemple, voici comment interpréter l'ordre de priorité lors de la définition de la version de l'optimiseur de requêtes:

Lorsque vous créez une base de données, celle-ci utilise la version de l'optimiseur par défaut de Spanner. La définition de la version de l'optimiseur à l'aide de l'une des méthodes listées précédemment prévaut sur tout élément situé à gauche de celle-ci. Par exemple, la définition de l'optimiseur pour une application à l'aide d'une variable d'environnement prévaut sur toute valeur définie pour la base de données à l'aide de l'option de base de données. La définition de la version de l'optimiseur via une optimisation d'instruction a la priorité la plus élevée pour la requête donnée, l'mportant sur la valeur définie à l'aide de toute autre méthode.

Les sections suivantes fournissent plus d'informations sur chaque méthode.

Définir des options d'optimiseur au niveau de la base de données

Vous pouvez définir la version de l'optimiseur par défaut sur une base de données à l'aide de la commande LDD ALTER DATABASE suivante.

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  8);

PostgreSQL

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Vous pouvez définir le package de statistiques de la même manière, comme illustré dans l'exemple suivant.

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

Vous pouvez également définir plusieurs options en même temps, comme indiqué dans la commande DDL suivante.

GoogleSQL

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

Vous pouvez exécuter ALTER DATABASE dans la CLI gcloud à l'aide de la commande gcloud CLI databases ddl update comme suit.

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'

Définir une option de base de données sur NULL (dans GoogleSQL) ou DEFAULT (dans PostgreSQL) la vide afin que la valeur par défaut soit utilisée.

Pour afficher la valeur actuelle de ces options pour une base de données, interrogez la vue INFORMATION_SCHEMA.DATABASE_OPTIONS pour Google SQL ou la table information_schema database_options pour PostgreSQL, comme suit.

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

Définir des options d'optimiseur avec des bibliothèques clientes

Lorsque vous interagissez de manière automatisée avec Spanner via des bibliothèques clientes, il existe plusieurs façons de modifier les options de requête pour votre application cliente.

Vous devez utiliser les dernières versions des bibliothèques clientes pour définir les options d'optimiseur.

Définir les options de l'optimiseur pour un client de base de données

Une application peut définir des options d'optimiseur globalement sur la bibliothèque cliente en configurant la propriété des options de requête, comme indiqué dans les extraits de code suivants. Les paramètres de l'optimiseur sont stockés dans l'instance cliente et sont appliqués à toutes les requêtes exécutées tout au long de la durée de vie du client. Même si les options s'appliquent au niveau de la base de données dans le backend, elles s'appliquent à toutes les bases de données connectées via ce client lorsqu'elles sont définies au niveau du client.

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

Définir des options d'optimiseur avec des variables d'environnement

Pour vous aider à tester les différents paramètres de l'optimiseur sans avoir à recompiler votre application, vous pouvez définir les variables d'environnement SPANNER_OPTIMIZER_VERSION et SPANNER_OPTIMIZER_STATISTICS_PACKAGE et exécuter votre application, comme indiqué dans l'extrait suivant.

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"

Les valeurs des options de l'optimiseur de requêtes spécifiées sont lues et stockées dans l'instance cliente au moment de l'initialisation du client. Elles s'appliquent à toutes les requêtes exécutées tout au long de la durée de vie du client.

Définir les options de l'optimiseur pour une requête cliente

Vous pouvez spécifier une valeur pour la version de l'optimiseur ou du package de statistiques au niveau de la requête dans votre application cliente, en spécifiant une propriété d'options de requête lors de la création de votre requête.

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

Définir les options de l'optimiseur pour une requête à l'aide d'une optimisation d'instruction

Une optimisation d'instruction est une optimisation sur une instruction de requête qui modifie le comportement par défaut de l'exécution de la requête. La définition de l'optimisation OPTIMIZER_VERSION sur une instruction force l'exécution de cette requête à l'aide de la version de l'optimiseur de requêtes spécifiée.

L'optimisation OPTIMIZER_VERSION a la priorité de version d'optimiseur la plus élevée. Si l'optimisation de l'instruction est spécifiée, elle sera utilisée quels que soient les autres paramètres de version de l'optimiseur.

GoogleSQL

@{OPTIMIZER_VERSION=8} SELECT * FROM MyTable;

PostgreSQL

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

Vous pouvez également utiliser le littéral latest_version pour définir la version de l'optimiseur pour une requête sur la dernière version, comme indiqué ici.

GoogleSQL

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL

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

La définition de l'optimisation OPTIMIZER_STATISTICS_PACKAGE sur une instruction force l'exécution de cette requête à l'aide de la version du package de statistiques de l'optimiseur de requêtes spécifiée. La récupération de mémoire doit être désactivée pour le package spécifié:

GoogleSQL

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

PostgreSQL

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

L'optimisation OPTIMIZER_STATISTICS_PACKAGE a la priorité de configuration du package d'optimiseur la plus élevée. Si l'optimisation de l'instruction est spécifiée, elle sera utilisée quels que soient les autres paramètres de version du package d'optimiseur.

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

Vous pouvez également utiliser le littéral latest pour utiliser le dernier package de statistiques.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

Les deux indications peuvent être définies dans une seule instruction, comme illustré dans l'exemple suivant.

Le littéral default_version définit la version de l'optimiseur pour une requête sur la version par défaut, qui peut être différente de la dernière version. Pour en savoir plus, consultez la section Version par défaut.

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;

Définir les options de l'optimiseur lors de l'utilisation du pilote JDBC Spanner

Vous pouvez remplacer la valeur par défaut de la version de l'optimiseur et du package de statistiques en spécifiant des options dans la chaîne de connexion JDBC, comme illustré dans l'exemple suivant.

Ces options ne sont compatibles qu'avec les dernières versions du pilote JDBC 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));
        }
      }
    }
  }
}

Vous pouvez également définir la version de l'optimiseur de requêtes à l'aide de l'instruction SET OPTIMIZER_VERSION, comme indiqué dans l'exemple suivant.

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

Pour en savoir plus sur l'utilisation du pilote Open Source, consultez la section Utiliser le pilote JDBC Open Source.

Comment les versions de l'optimiseur non valides sont gérées

Spanner accepte une plage de versions de l'optimiseur. Cette plage évolue au fil du temps lorsque l'optimiseur de requêtes est mis à jour. Si la version que vous spécifiez est en dehors de la plage, la requête échoue. Par exemple, si vous tentez d'exécuter une requête avec l'indice d'instruction @{OPTIMIZER_VERSION=9}, mais que la version la plus récente de l'optimiseur n'est que 8, Spanner renvoie le message d'erreur suivant:

Query optimizer version: 9 is not supported

Gérer un paramètre de package de statistiques de l'optimiseur non valide

Vous pouvez épingler votre base de données ou votre requête à n'importe quel package de statistiques disponible à l'aide de l'une des méthodes décrites plus haut sur cette page. Une requête échoue si un nom de package de statistiques non valide est fourni. Un package de statistiques spécifié par une requête doit être:

défini au niveau de la base de données ; ou
marqué comme ALLOW_GC=false

Déterminer la version de l'optimiseur de requêtes utilisée pour exécuter une requête

La version de l'optimiseur utilisée pour une requête est visible dans la console Google Cloud et dans la Google Cloud CLI.

Console Google Cloud

Pour afficher la version de l'optimiseur utilisée pour une requête, exécutez-la sur la page Spanner Studio de la console Google Cloud, puis sélectionnez l'onglet Explication. Un message semblable au suivant doit s'afficher :

Version de l'optimiseur de requêtes: 8

CLI gcloud

Pour afficher la version utilisée lors de l'exécution d'une requête dans gcloud CLI, définissez l'option --query-mode sur PROFILE comme indiqué dans l'extrait suivant.

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

Visualiser la version de l'optimiseur de requêtes dans l'explorateur de métriques

Cloud Monitoring collecte des mesures pour vous aider à comprendre les performances de vos applications et de vos services système. L'une des métriques collectées pour Spanner est le nombre de requêtes, qui mesure le nombre de requêtes d'une instance, échantillonnées au fil du temps. Bien que cette métrique soit très utile pour afficher les requêtes regroupées par code d'erreur, nous pouvons également l'exploiter pour déterminer la version de l'optimiseur utilisée pour exécuter chaque requête.

Vous pouvez utiliser l'explorateur de métriques dans la console Google Cloud pour visualiser le nombre de requêtes de votre instance de base de données. La figure 1 indique le nombre de requêtes pour trois bases de données. Vous pouvez identifier la version de l'optimiseur utilisée dans chaque base de données.

Le tableau situé au-dessous du graphique de cette figure montre que my-db-1 a tenté d'exécuter une requête avec une version de l'optimiseur non valide, renvoyant l'état Bad usage (Mauvaise utilisation) et un nombre de requêtes défini sur 0. Les autres bases de données ont exécuté des requêtes à l'aide des versions 1 et 2 de l'optimiseur.

Nombre de requêtes affichées dans l'explorateur de métriques regroupées par version de l'optimiseur de requêtes

Figure 1 : Nombre de requêtes affichées dans l'explorateur de métriques avec les requêtes regroupées par version de l'optimiseur.

Pour configurer un graphique similaire pour votre instance, procédez comme suit :

Accédez à Metrics Explorer (Explorateur de métriques) dans la console Google Cloud.
Dans le champ Resource type (Type de ressource), sélectionnez Cloud Spanner Instance.
Dans le champ Metric (Métrique), sélectionnez Count of queries.
Sélectionnez database, optimizer_version et status dans le champ Group By (Grouper par).

Cet exemple n'aborde pas le cas où une autre version de l'optimiseur est utilisée pour différentes requêtes dans la même base de données. Dans ce cas, le graphique affiche un segment à barres pour chaque combinaison de la base de données et de la version de l'optimiseur.

Pour apprendre à utiliser Cloud Monitoring pour surveiller vos instances Spanner, consultez la page Surveiller avec Cloud Monitoring.