Mengelola pengoptimal kueri

Pengoptimal kueri Spanner menentukan cara paling efisien untuk mengeksekusi 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 dipaketkan dan dirilis sebagai versi pengoptimal. Spanner mulai menggunakan versi terbaru pengoptimal 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 pembersihan sampah:

GoogleSQL

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

PostgreSQL

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

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 update. Versi terbaru pengoptimal kueri adalah 7.

Jalankan pernyataan SQL berikut untuk menampilkan daftar semua versi pengoptimal yang didukung, beserta tanggal rilis yang sesuai dan apakah versi tersebut adalah versi 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 lebih dari 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 ini menampilkan daftar semua versi pengoptimal yang didukung. Kolom IS_DEFAULT menentukan versi mana yang merupakan 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 akan 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 konstruksi 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 langsung 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 di atas akan lebih diutamakan daripada apa pun di sebelah kirinya. Misalnya, menetapkan pengoptimal untuk aplikasi menggunakan variabel lingkungan lebih diprioritaskan 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.

Sekarang, mari kita lihat setiap metode secara lebih mendetail.

Menetapkan opsi pengoptimal di tingkat database

Anda dapat menetapkan versi pengoptimal default di database menggunakan perintah DDL ALTER DATABASE berikut.

GoogleSQL 

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  7);

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

PostgreSQL 

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

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

Untuk melihat nilai saat ini dari opsi ini untuk database, buat 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 dengan library klien

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

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

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 di 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

Menetapkan opsi pengoptimal dengan variabel lingkungan

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

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="7"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="7"
  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 sepanjang masa aktif klien.

Menetapkan opsi pengoptimal untuk kueri klien

Anda dapat menentukan nilai untuk versi pengoptimal atau versi paket statistik pada tingkat kueri di 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 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 dinyatakan, petunjuk pernyataan akan digunakan terlepas dari semua setelan versi pengoptimal lainnya.

GoogleSQL 

@{OPTIMIZER_VERSION=7} SELECT * FROM MyTable;

PostgreSQL 

/*@OPTIMIZER_VERSION=7*/ 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 ditentukan, petunjuk pernyataan 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 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 versi terbaru driver 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));
        }
      }
    }
  }
}

Anda juga dapat menetapkan versi pengoptimal kueri menggunakan pernyataan SET OPTIMIZER_VERSION seperti yang ditunjukkan pada 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 rentang 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=8}, tetapi nomor versi pengoptimal terbaru hanya 7, Spanner akan merespons dengan pesan error ini:

Query optimizer version: 8 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 gagal jika nama paket statistik yang diberikan tidak valid. Paket statistik yang ditentukan oleh kueri harus:

Menentukan versi pengoptimal kueri yang digunakan untuk menjalankan kueri

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

Konsol Google Cloud

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

Versi pengoptimal kueri: 7

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 Anda. 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 konsol Google Cloud 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 dalam 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.

Jumlah kueri di Metrics Explorer yang dikelompokkan menurut versi pengoptimal kueri

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 konsol Google Cloud.
  2. Di kolom Resource type, pilih Cloud Spanner Instance.
  3. Di kolom Metric, pilih Count of queries.
  4. Di kolom Group By, pilih database, optimizer_version, dan status.

Tidak ditampilkan dalam contoh ini adalah kasus saat versi pengoptimal yang berbeda digunakan untuk kueri yang berbeda dalam database yang sama. Dalam hal ini, diagram akan menampilkan segmen batang untuk setiap kombinasi database dan versi pengoptimal.

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