Mengelola pengoptimal kueri

Halaman ini menjelaskan cara mengelola pengoptimal kueri di Spanner untuk database dialek GoogleSQL dan database dialek PostgreSQL.

Pengoptimal kueri Spanner menentukan cara paling efisien untuk menjalankan kueri SQL. Namun, rencana kueri yang ditentukan oleh pengoptimal dapat sedikit berubah saat pengoptimal kueri itu sendiri berkembang, atau saat statistik database diperbarui. Untuk meminimalkan potensi regresi performa saat pengoptimal kueri atau statistik berubah, Spanner menyediakan opsi kueri berikut.

• optimizer_version: Perubahan pada pengoptimal kueri dikelompokkan dan dirilis sebagai versi pengoptimal. Spanner mulai menggunakan pengoptimal versi terbaru sebagai default setidaknya 30 hari setelah versi tersebut dirilis. Anda dapat menggunakan opsi versi pengoptimal kueri untuk menjalankan kueri terhadap pengoptimal versi lama.

• optimizer_statistics_package: Spanner memperbarui statistik pengoptimal secara rutin. Statistik baru tersedia sebagai paket. Opsi kueri ini menentukan paket statistik yang akan digunakan pengoptimal kueri saat mengompilasi kueri SQL. Paket yang ditentukan harus menonaktifkan garbage collection:

  GoogleSQL

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

  PostgreSQL

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

Panduan ini menunjukkan cara menetapkan setiap opsi ini pada cakupan yang berbeda di Spanner.

Mencantumkan opsi pengoptimal kueri

Spanner menyimpan informasi tentang versi pengoptimal dan paket statistik yang tersedia yang dapat Anda pilih.

Versi pengoptimal

Versi pengoptimal kueri adalah nilai bilangan bulat, yang bertambah 1 dengan setiap pembaruan. Versi pengoptimal kueri terbaru adalah 9.

Jalankan pernyataan SQL berikut untuk menampilkan daftar semua versi pengoptimal yang didukung, beserta tanggal rilis yang sesuai dan apakah versi tersebut adalah default. Nomor versi terbesar yang ditampilkan adalah versi pengoptimal terbaru yang didukung.

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Versi default

Secara default, Spanner mulai menggunakan pengoptimal versi terbaru setidaknya 30 hari setelah versi tersebut dirilis. Selama periode 30+ hari antara rilis baru dan rilis tersebut menjadi default, sebaiknya uji kueri terhadap versi baru untuk mendeteksi regresi.

Untuk menemukan versi default, jalankan pernyataan SQL berikut:

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

Kueri menampilkan daftar semua versi pengoptimal yang didukung. Kolom IS_DEFAULT menentukan versi mana yang menjadi default saat ini.

Untuk mengetahui detail tentang setiap versi, lihat Histori versi pengoptimal kueri.

Paket statistik pengoptimal

Setiap paket statistik pengoptimal baru yang dibuat Spanner diberi nama paket yang dijamin unik dalam database tertentu.

Format nama paketnya adalah auto_{PACKAGE_TIMESTAMP}UTC. Di GoogleSQL, pernyataan ANALYZE memicu pembuatan nama paket statistik. Di PostgreSQL, pernyataan ANALYZE melakukan tugas ini. Format nama paket statistik adalah analyze_{PACKAGE_TIMESTAMP}UTC, dengan {PACKAGE_TIMESTAMP} adalah stempel waktu, dalam zona waktu UTC, saat pembuatan statistik dimulai. Jalankan pernyataan SQL berikut untuk menampilkan daftar semua paket statistik pengoptimal yang tersedia.

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

Secara default, Spanner menggunakan paket statistik pengoptimal terbaru kecuali jika database atau kueri disematkan ke paket lama menggunakan salah satu metode yang dijelaskan di halaman ini.

Prioritas penggantian opsi

Jika Anda menggunakan database dialek GoogleSQL, Spanner menawarkan beberapa cara untuk mengubah opsi pengoptimal. Misalnya, Anda dapat menetapkan opsi untuk kueri tertentu atau mengonfigurasi opsi di library klien pada tingkat proses atau kueri. Jika opsi ditetapkan dengan beberapa cara, urutan prioritas berikut akan berlaku. (Pilih link untuk membuka bagian tersebut dalam dokumen ini).

Default Spanner ← opsi database ← aplikasi klien ← variabel lingkungan ← kueri klien ← petunjuk pernyataan

Misalnya, berikut cara menafsirkan urutan prioritas saat menetapkan versi pengoptimal kueri:

Saat Anda membuat database, database tersebut akan menggunakan versi pengoptimal default Spanner . Menetapkan versi pengoptimal menggunakan salah satu metode yang tercantum sebelumnya lebih diutamakan daripada apa pun di sebelah kirinya. Misalnya, menetapkan pengoptimal untuk aplikasi menggunakan variabel lingkungan lebih diutamakan daripada nilai apa pun yang Anda tetapkan untuk database menggunakan opsi database. Menetapkan versi pengoptimal melalui petunjuk pernyataan memiliki prioritas tertinggi untuk kueri tertentu, yang lebih diutamakan daripada nilai yang ditetapkan menggunakan metode lain.

Bagian berikut memberikan detail selengkapnya tentang setiap metode.

Menetapkan opsi pengoptimal

Anda dapat menetapkan opsi pengoptimal default melalui metode berikut:

• Di tingkat database
• Untuk kueri menggunakan petunjuk pernyataan
• Dengan library klien
  • Untuk klien database
  • Dengan variabel lingkungan
  • Untuk kueri klien
• Menggunakan driver JDBC Spanner

Dalam beberapa kasus, Spanner mungkin menggunakan versi pengoptimal kueri yang lebih lama untuk bentuk kueri tertentu, meskipun Anda telah menyematkan versi yang lebih baru. Ini adalah perilaku internal yang diharapkan untuk memastikan stabilitas dan performa kueri. Anda dapat mengidentifikasi versi pengoptimal yang digunakan untuk kueri tertentu dengan memeriksa rencana eksekusi kueri.

Menetapkan opsi pengoptimal di tingkat database

Untuk menetapkan versi pengoptimal default di database, gunakan perintah DDL ALTER DATABASE berikut. Menetapkan opsi ini tidak mengharuskan semua kueri menjalankan versi tersebut. Sebagai gantinya, opsi ini menetapkan batas atas pada versi QO yang digunakan untuk kueri. Tujuannya adalah untuk mengurangi regresi yang terjadi setelah versi pengoptimal baru dirilis.

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  9);

PostgreSQL

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Anda dapat menetapkan paket statistik dengan cara yang sama, seperti yang ditunjukkan dalam contoh berikut.

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

Anda juga dapat menetapkan lebih dari satu opsi secara bersamaan, seperti yang ditunjukkan dalam perintah DDL berikut.

GoogleSQL

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

Anda dapat menjalankan ALTER DATABASE di gcloud CLI dengan perintah gcloud CLI databases ddl update sebagai berikut.

GoogleSQL

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

PostgreSQL

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

Menetapkan opsi database ke NULL (di GoogleSQL) atau DEFAULT (di PostgreSQL) akan menghapusnya sehingga nilai default digunakan.

Untuk melihat nilai opsi saat ini untuk database, kueri tampilan INFORMATION_SCHEMA.DATABASE_OPTIONS untuk GoogleSQL, atau tabel information_schema database_options untuk PostgreSQL, sebagai berikut.

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

Menetapkan opsi pengoptimal untuk kueri menggunakan petunjuk pernyataan

Petunjuk pernyataan adalah petunjuk pada pernyataan kueri yang mengubah eksekusi kueri dari perilaku default. Menetapkan petunjuk OPTIMIZER_VERSION pada pernyataan akan memaksa kueri tersebut berjalan menggunakan versi pengoptimal kueri yang ditentukan.

Petunjuk OPTIMIZER_VERSION memiliki prioritas versi pengoptimal tertinggi. Jika petunjuk pernyataan ditentukan, petunjuk tersebut akan digunakan terlepas dari semua setelan versi pengoptimal lainnya.

GoogleSQL

@{OPTIMIZER_VERSION=9} SELECT * FROM MyTable;

PostgreSQL

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

Anda juga dapat menggunakan literal latest_version untuk menetapkan versi pengoptimal untuk kueri ke versi terbaru seperti yang ditunjukkan di sini.

GoogleSQL

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL

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

Menetapkan petunjuk OPTIMIZER_STATISTICS_PACKAGE pada pernyataan akan memaksa kueri tersebut berjalan menggunakan versi paket statistik pengoptimal kueri yang ditentukan. Paket yang ditentukan harus menonaktifkan pembersihan sampah memori:

GoogleSQL

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

PostgreSQL

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

Petunjuk OPTIMIZER_STATISTICS_PACKAGE memiliki prioritas setelan paket pengoptimal tertinggi. Jika petunjuk pernyataan ditentukan, petunjuk tersebut akan digunakan terlepas dari semua setelan versi paket pengoptimal lainnya.

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

Anda juga dapat menggunakan literal latest untuk menggunakan paket statistik terbaru.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

Kedua petunjuk dapat ditetapkan dalam satu pernyataan seperti yang ditunjukkan dalam contoh berikut.

Literal default_version menetapkan versi pengoptimal untuk kueri ke versi default, yang mungkin berbeda dengan versi terbaru. Lihat Versi default untuk mengetahui detailnya.

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;

Menetapkan opsi pengoptimal dengan library klien

Saat Anda berinteraksi secara terprogram dengan Spanner melalui library klien, ada beberapa cara untuk mengubah opsi kueri untuk aplikasi klien Anda.

Anda harus menggunakan library klien versi terbaru untuk menetapkan opsi pengoptimal.

Untuk klien database

Aplikasi dapat menetapkan opsi pengoptimal secara global di library klien dengan mengonfigurasi properti opsi kueri seperti yang ditunjukkan dalam cuplikan kode berikut. Setelan pengoptimal disimpan dalam instance klien dan diterapkan ke semua kueri yang dijalankan selama masa aktif klien. Meskipun opsi berlaku di tingkat database di backend, saat opsi ditetapkan di tingkat klien, opsi tersebut berlaku untuk semua database yang terhubung ke klien tersebut.

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

Dengan variabel lingkungan

Untuk memudahkan mencoba setelan pengoptimal yang berbeda tanpa harus mengompilasi ulang aplikasi, Anda dapat menetapkan variabel lingkungan SPANNER_OPTIMIZER_VERSION dan SPANNER_OPTIMIZER_STATISTICS_PACKAGE, lalu menjalankan aplikasi Anda, seperti yang ditunjukkan dalam cuplikan berikut.

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="9"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="9"
  set SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Nilai opsi pengoptimal kueri yang ditentukan dibaca dan disimpan dalam instance klien pada waktu inisialisasi klien dan berlaku untuk semua kueri yang dijalankan selama masa aktif klien.

Untuk kueri klien

Anda dapat menentukan nilai untuk versi pengoptimal atau versi paket statistik di tingkat kueri dalam aplikasi klien dengan menentukan properti opsi kueri saat membuat kueri.

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

Menetapkan opsi pengoptimal saat menggunakan driver JDBC Spanner

Anda dapat mengganti nilai default versi pengoptimal dan paket statistik dengan menentukan opsi dalam string koneksi JDBC seperti yang ditunjukkan dalam contoh berikut.

Opsi ini hanya didukung di driver JDBC Spanner versi terbaru .

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

Anda juga dapat menetapkan versi pengoptimal kueri menggunakan pernyataan SET OPTIMIZER_VERSION seperti yang ditunjukkan dalam contoh berikut.

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

Untuk mengetahui detail selengkapnya tentang penggunaan driver open source, lihat Menggunakan driver JDBC open source.

Cara penanganan versi pengoptimal yang tidak valid

Spanner mendukung berbagai versi pengoptimal. Rentang ini berubah dari waktu ke waktu saat pengoptimal kueri diperbarui. Jika versi yang Anda tentukan berada di luar rentang, kueri akan gagal. Misalnya, jika Anda mencoba menjalankan kueri dengan petunjuk pernyataan @{OPTIMIZER_VERSION=10}, tetapi nomor versi pengoptimal terbaru hanya 9, Spanner akan merespons dengan pesan error ini:

Query optimizer version: 10 is not supported

Menangani setelan paket statistik pengoptimal yang tidak valid

Anda dapat menyematkan database atau kueri ke paket statistik yang tersedia menggunakan salah satu metode yang dijelaskan sebelumnya di halaman ini. Kueri akan gagal jika nama paket statistik yang tidak valid diberikan. Paket statistik yang ditentukan oleh kueri harus:

• ditetapkan di tingkat database; atau
• ditandai sebagai ALLOW_GC=false

Menentukan versi pengoptimal kueri yang digunakan untuk menjalankan kueri

Versi pengoptimal yang digunakan untuk kueri dapat dilihat melalui Google Cloud konsol dan di Google Cloud CLI.

Google Cloud Konsol

Untuk melihat versi pengoptimal yang digunakan untuk kueri, jalankan kueri Anda di halaman Spanner Studio Google Cloud konsol, lalu pilih tab Penjelasan. Anda akan melihat pesan yang mirip dengan yang berikut ini:

Versi pengoptimal kueri: 9

gcloud CLI

Untuk melihat versi yang digunakan saat menjalankan kueri di gcloud CLI, tetapkan flag --query-mode ke PROFILE seperti yang ditunjukkan dalam cuplikan berikut.

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

Memvisualisasikan versi pengoptimal kueri di Metrics Explorer

Cloud Monitoring mengumpulkan pengukuran untuk membantu Anda memahami performa aplikasi dan layanan sistem. Salah satu metrik yang dikumpulkan untuk Spanner adalah jumlah kueri, yang mengukur jumlah kueri dalam instance, yang diambil sampelnya dari waktu ke waktu. Meskipun metrik ini sangat berguna untuk melihat kueri yang dikelompokkan menurut kode error, kita juga dapat menggunakannya untuk melihat versi pengoptimal yang digunakan untuk menjalankan setiap kueri.

Anda dapat menggunakan Metrics Explorer di Google Cloud konsol untuk memvisualisasikan Jumlah kueri untuk instance database Anda. Gambar 1 menunjukkan jumlah kueri untuk tiga database. Anda dapat melihat versi pengoptimal yang digunakan di setiap database.

Tabel di bawah diagram pada gambar ini menunjukkan bahwa my-db-1 mencoba menjalankan kueri dengan versi pengoptimal yang tidak valid, yang menampilkan status Penggunaan buruk dan menghasilkan jumlah kueri 0. Database lainnya menjalankan kueri menggunakan pengoptimal versi 1 dan 2.

Gambar 1. Jumlah kueri yang ditampilkan di Metrics Explorer dengan kueri yang dikelompokkan menurut versi pengoptimal.

Untuk menyiapkan diagram serupa untuk instance Anda:

1. Buka Metrics Explorer di Google Cloud konsol.
2. Di kolom Jenis resource, pilih Cloud Spanner Instance.
3. Di kolom Metrik, pilih Count of queries.
4. Di kolom Group By, pilih database, optimizer_version, dan status.

Contoh ini tidak menunjukkan kasus saat versi pengoptimal yang berbeda digunakan untuk kueri yang berbeda di database yang sama. Dalam hal ini, diagram akan menampilkan segmen batang untuk setiap kombinasi database dan versi pengoptimal.

Untuk mempelajari cara menggunakan Cloud Monitoring untuk memantau instance Spanner, lihat Pemantauan dengan Cloud Monitoring