Gérer l'optimiseur de requêtes

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

• optimizer_version: les modifications apportées à l'optimiseur de requêtes sont groupées et publiées en tant que versions d'optimiseur. Spanner commence à utiliser la dernière version de l'optimiseur comme version par défaut au moins 30 jours après la publication de cette version. 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 régulièrement à jour les statistiques de l'optimiseur. Les nouvelles statistiques sont mises à disposition 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  SET OPTIONS (allow_gc=false)

PostgreSQL

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

Ce guide explique comment définir ces options individuelles avec différents champs d'application dans Spanner.

Lister les options de l'optimiseur de requêtes

Spanner stocke des informations sur les versions d'optimiseur et les packages de statistiques disponibles 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 la version 6.

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 la publication de cette version. Au cours des 30 jours de plus entre la publication d'une nouvelle version et son adoption par défaut, nous vous encourageons à tester les requêtes sur la nouvelle version pour 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 d'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 se voit attribuer un nom de package 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, dans le fuseau horaire UTC, du moment où la construction des statistiques a commencé. Exécutez l'instruction SQL suivante pour renvoyer la liste de tous les packages de statistiques d'optimiseur disponibles.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Par défaut, Spanner utilise le dernier package de statistiques d'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é de remplacement de l'option

Si vous utilisez une base de données de dialecte GoogleSQL, Spanner offre plusieurs façons de modifier les options d'optimisation. Par exemple, vous pouvez définir la ou les options d'une requête spécifique, ou les configurer 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 à la section correspondante de ce document.)

Spanner par défaut ← option de base de données ← application cliente ← variable d'environnement ← requête client ← indice de l'instruction

Par exemple, voici comment interpréter l'ordre de priorité lorsque vous définissez 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 ci-dessus est prioritaire par rapport aux éléments situés à gauche. Par exemple, la définition de l'optimiseur pour une application à l'aide d'une variable d'environnement est prioritaire par rapport à toute valeur définie pour la base de données à l'aide de l'option de base de données. Définir la version de l'optimiseur via une indication d'instruction a la priorité la plus élevée pour la requête donnée. Elle est prioritaire sur la valeur définie à l'aide de toute autre méthode.

Étudions maintenant chaque méthode plus en détail.

Définir les options d'optimisation 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 =  6);

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 LDD suivante.

GoogleSQL

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

Vous pouvez exécuter ALTER DATABASE dans la gcloud CLI à 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 = 6 )'

PostgreSQL

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

Si vous définissez une option de base de données sur NULL, cette option est effacée et la valeur par défaut est utilisée.

Pour afficher la valeur actuelle de ces options pour une base de données, interrogez la vue INFORMATION_SCHEMA.DATABASE_OPTIONS pour GoogleSQL 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'optimisation à l'aide de 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'optimisation.

Définir les options d'optimisation pour un client de base de données

Une application peut définir des options d'optimisation de manière globale 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 d'optimisation sont stockés dans l'instance cliente et sont appliqués à toutes les requêtes exécutées pendant 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 à 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"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

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 les options de l'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="6"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="6"
  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 du client au moment de l'initialisation du client. Elles s'appliquent à toutes les requêtes exécutées pendant la durée de vie du client.

Définir les options d'optimisation pour une requête client

Vous pouvez spécifier une valeur pour la version de l'optimiseur ou la version 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"
	"google.golang.org/api/iterator"
	sppb "google.golang.org/genproto/googleapis/spanner/v1"
)

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 d'optimisation pour une requête à l'aide d'un indice 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=6} SELECT * FROM MyTable;

PostgreSQL

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

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

GoogleSQL

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL

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

Définir la suggestion OPTIMIZER_STATISTICS_PACKAGE sur une instruction force cette requête à s'exécuter à l'aide de la version spécifiée du package de statistiques de l'optimiseur de requêtes. 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)

La suggestion OPTIMIZER_STATISTICS_PACKAGE a la priorité la plus élevée des paramètres de package d'optimiseur. Si la suggestion d'instruction est spécifiée, elle sera utilisée quels que soient tous 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 package de statistiques le plus récent.

@{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 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 d'optimisation 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 page Utiliser le pilote JDBC Open Source.

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

Spanner accepte une plage de versions d'optimiseur. Cette plage change au fil du temps lorsque l'optimiseur de requêtes est mis à jour. Si la version que vous spécifiez est hors plage, la requête échoue. Par exemple, si vous tentez d'exécuter une requête avec l'indice d'instruction @{OPTIMIZER_VERSION=7}, mais que le numéro de version le plus récent de l'optimiseur n'est que 6, Spanner répond par le message d'erreur suivant:

Query optimizer version: 7 is not supported

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

Vous pouvez épingler votre base de données ou votre requête à n'importe quel package de statistiques disponible en utilisant l'une des méthodes décrites précédemment 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 requête 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: 6

gcloud CLI

Pour afficher la version utilisée lors de l'exécution d'une requête dans la gcloud CLI, définissez l'option --query-mode sur PROFILE, comme indiqué dans l'extrait de code 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. Le nombre de requêtes est l'une des métriques collectées pour Spanner. Il mesure le nombre de requêtes dans 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 de 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 voir quelle version de l'optimiseur est utilisée dans chaque base de données.

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

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 :

1. Accédez à l'Explorateur de métriques dans la console Google Cloud.
2. Dans le champ Resource type (Type de ressource), sélectionnez Cloud Spanner Instance.
3. Dans le champ Metric (Métrique), sélectionnez Count of queries.
4. 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 savoir comment surveiller vos instances Spanner à l'aide de Cloud Monitoring, consultez la page Surveiller avec Cloud Monitoring.