Mengelola pengoptimal kueri

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

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 teratur. Statistik baru tersedia sebagai satu paket. Opsi kueri ini menentukan paket statistik yang akan digunakan oleh pengoptimal kueri saat mengompilasi kueri SQL. Paket yang ditentukan harus menonaktifkan pembersihan sampah memori:

GoogleSQL

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

PostgreSQL

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

Panduan ini menunjukkan cara menetapkan masing-masing opsi ini pada berbagai cakupan di Spanner.

Mencantumkan opsi pengoptimal kueri

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

Versi pengoptimal

Versi pengoptimal kueri adalah nilai bilangan bulat, yang bertambah 1 pada setiap update. Versi terbaru pengoptimal kueri adalah 6.

Jalankan pernyataan SQL berikut untuk menampilkan daftar semua versi pengoptimal yang didukung, beserta tanggal rilisnya 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 versi terbaru pengoptimal setidaknya 30 hari setelah versi tersebut dirilis. Selama periode lebih dari 30 hari antara rilis baru dan rilis tersebut yang menjadi default, sebaiknya uji kueri terhadap versi baru untuk mendeteksi regresi apa pun.

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 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 yang diberikan.

Format nama paket adalah auto_{PACKAGE_TIMESTAMP}UTC. Di GoogleSQL, pernyataan ANALYZE memicu pembuatan nama paket statistik. Di PostgreSQL, pernyataan ANALYZE menjalankan 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 melompat ke bagian tersebut dalam dokumen ini).

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

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

Saat Anda membuat database, database tersebut menggunakan versi pengoptimal default Spanner. Menetapkan versi pengoptimal menggunakan salah satu metode yang tercantum di atas akan diprioritaskan daripada apa pun yang ada di sebelah kirinya. Misalnya, penetapan pengoptimal untuk aplikasi yang 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, lebih diutamakan daripada kumpulan nilai menggunakan metode lain.

Sekarang, mari kita lihat setiap metode secara lebih mendetail.

Menetapkan opsi pengoptimal di tingkat database

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

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  6);

PostgreSQL

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

Anda dapat menetapkan paket statistik dengan cara yang sama, seperti yang ditunjukkan pada 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 = 6,
            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 = 6 )'

PostgreSQL

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

Menyetel opsi database ke NULL akan menghapusnya sehingga nilai default digunakan.

Guna 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 pada level database di backend, ketika opsi ditetapkan pada level 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"
	"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

Menetapkan opsi pengoptimal dengan variabel lingkungan

Agar lebih mudah untuk mencoba berbagai setelan pengoptimal yang berbeda tanpa harus mengompilasi ulang aplikasi, Anda dapat menetapkan variabel lingkungan SPANNER_OPTIMIZER_VERSION dan SPANNER_OPTIMIZER_STATISTICS_PACKAGE serta menjalankan aplikasi, seperti yang ditunjukkan dalam cuplikan berikut.

Linux / Mac

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"

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

Menetapkan opsi pengoptimal untuk kueri klien

Anda dapat menetapkan 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"
	"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

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 untuk dijalankan menggunakan versi pengoptimal kueri yang ditetapkan.

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=6} SELECT * FROM MyTable;

PostgreSQL

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

Anda juga dapat menggunakan literal latest_version untuk menetapkan versi pengoptimal untuk kueri ke versi terbaru seperti yang ditampilkan 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 untuk dijalankan 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 terbaru untuk menggunakan paket statistik terbaru.

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

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

Literal default_version menetapkan versi pengoptimal untuk sebuah kueri ke versi default, yang mungkin berbeda dari 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 dalam 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 detail selengkapnya tentang cara menggunakan driver open source, baca Menggunakan driver JDBC open source.

Cara penanganan versi pengoptimal yang tidak valid

Spanner mendukung rentang versi pengoptimal. Rentang ini berubah seiring waktu ketika 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=7}, tetapi nomor versi pengoptimal terbaru hanya 6, Spanner akan merespons dengan pesan error ini:

Query optimizer version: 7 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 diberikan tidak valid. Paket statistik yang ditentukan oleh kueri harus berupa:

ditetapkan di level 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 Console dan di Google Cloud CLI.

Konsol Google Cloud

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

Versi pengoptimal kueri: 6

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 sebuah instance, yang diambil sampelnya dari waktu ke waktu. Meskipun metrik ini sangat berguna untuk melihat kueri yang dikelompokkan berdasarkan kode error, kita juga dapat menggunakannya untuk melihat versi pengoptimal apa yang digunakan untuk menjalankan setiap kueri.

Anda dapat menggunakan Metrics Explorer di Konsol Google Cloud untuk memvisualisasikan Jumlah kueri untuk instance database. Gambar 1 menunjukkan jumlah kueri untuk tiga database. Anda dapat melihat versi pengoptimal mana 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 lain menjalankan kueri masing-masing menggunakan versi 1 dan 2 dari pengoptimal.

Jumlah kueri di Metrics Explorer yang dikelompokkan berdasarkan versi pengoptimal kueri

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

Untuk menyiapkan diagram serupa untuk instance Anda:

Buka Metrics Explorer di Konsol Google Cloud.
Di kolom Resource type, pilih Cloud Spanner Instance.
Di kolom Metric, pilih Count of queries.
Di kolom Group By, pilih database, optimizer_version, dan status.

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

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