クエリ オプティマイザーを管理する

Spanner クエリ オプティマイザーは、SQL クエリを実行する最も効率的な方法を決定します。ただし、クエリ オプティマイザー自体が進化したとき、またはデータベース統計情報が更新されたときに、オプティマイザーによって決定されるクエリプランがわずかに変更される可能性があります。Spanner では、クエリ オプティマイザーや統計情報が変更されたときにパフォーマンス低下の可能性を最小限に抑えるために、次のクエリ オプションを提供しています。

  • optimizer_version: クエリ オプティマイザーの変更は、オプティマイザーのバージョンとしてバンドルされてリリースされます。Spanner は、オプティマイザの最新バージョンがリリースされてから少なくとも 30 日後にデフォルトとして使用を開始します。クエリ オプティマイザーのバージョン オプションを使用すると、古いバージョンのオプティマイザーに対してクエリを実行できます。

  • オプティマイザーの統計情報パッケージ: Spanner は、オプティマイザーの統計情報を定期的に更新します。新しい統計情報は、パッケージとして利用できます。このクエリ オプションは、SQL クエリのコンパイル時に使用するクエリ オプティマイザーの統計情報パッケージを指定します。指定したパッケージは、ガベージ コレクションを無効にする必要があります。

GoogleSQL

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

PostgreSQL

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

このガイドでは、Spanner の異なるスコープで個々のオプションを設定する方法について説明します。

クエリ オプティマイザーのオプションを一覧表示する

Spanner には、利用可能なオプティマイザーのバージョンと選択可能な統計情報パッケージに関する情報が格納されています。

オプティマイザーのバージョン

クエリ オプティマイザーのバージョンは整数値であり、更新のたびに 1 単位で値が大きくなります。クエリ オプティマイザーの最新バージョンは 6 です。

次の SQL ステートメントを実行して、すべてのサポートされているオプティマイザ バージョンのリストを、それに対応するリリース日と、そのバージョンがデフォルトであるかどうかとともに返します。返される最大のバージョン番号が、サポートされている最新バージョンです。

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

デフォルトのバージョン

デフォルトでは、Spanner はリリースされてから 30 日以上経過した後にオプティマイザーの最新バージョンの使用を開始します。新しいリリースとそのリリースがデフォルトになってからの 30 日以上の間に、新しいバージョンに対してクエリをテストして、回帰を検出することをおすすめします。

デフォルト バージョンを確認するには、次の SQL ステートメントを実行します。

SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;

このクエリは、すべてのサポートされているオプティマイザー バージョンのリストを返します。IS_DEFAULT 列には、現在のデフォルトがどのバージョンかを指定します。

各バージョンの詳細については、クエリ オプティマイザーのバージョン履歴をご覧ください。

オプティマイザーの統計情報パッケージ

Spanner によって作成される新しいオプティマイザーの統計情報パッケージには、それぞれ特定のデータベース内で一意であることが保証されたパッケージ名が割り当てられます。

パッケージ名の形式は auto_{PACKAGE_TIMESTAMP}UTC です。GoogleSQL では、ANALYZE ステートメントによって統計情報パッケージ名の作成がトリガーされます。PostgreSQL では、ANALYZE ステートメントがこの作業を行います。統計情報パッケージ名の形式は analyze_{PACKAGE_TIMESTAMP}UTC です。ここで、{PACKAGE_TIMESTAMP} は統計情報の作成開始時のタイムスタンプ(UTC タイムゾーン)です。次の SQL ステートメントを実行して、使用可能なすべてのオプティマイザーの統計情報パッケージのリストを返します。

SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;

デフォルトでは、Spanner は最新のオプティマイザーの統計情報パッケージを使用します。ただし、このページで説明する方法のいずれかを使用してデータベースまたはクエリが古いパッケージに固定されている場合は除きます。

オプションのオーバーライド優先順位

GoogleSQL 言語データベースを使用している場合、Spanner でオプティマイザーのオプションを変更する方法はいくつかあります。たとえば、プロセスまたはクエリレベルで特定のクエリのオプションを設定することも、クライアント ライブラリのオプションを構成することもできます。オプションが複数の方法で設定されている場合、次の優先順位が適用されます。(このドキュメントのこのセクションに移動するには、リンクを選択してください)。

Spanner のデフォルト ← データベース オプションクライアント アプリ環境変数クライアント クエリステートメント ヒント

たとえば、クエリ オプティマイザーのバージョンを設定するときに優先順位を解釈する方法を次に示します。

データベースを作成する場合、Spanner のデフォルトのオプティマイザーのバージョンが使用されます。上記のいずれかの方法を使用してオプティマイザのバージョンを設定することで、オプティマイザーの左側にあるものよりも優先されます。たとえば、環境変数を使用してアプリのオプティマイザーを設定すると、データベース オプションを使用してデータベースに設定した値よりも優先されます。 ステートメント ヒントを使用してオプティマイザーのバージョンを設定すると、他の方法で設定された値よりも優先されます。

次に、各メソッドについて詳しく見ていきましょう。

データベース レベルでオプティマイザー オプションを設定する

次の ALTER DATABASE DDL コマンドを使用して、データベースにデフォルトのオプティマイザーのバージョンを設定できます。

GoogleSQL

ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version =  6);

PostgreSQL

ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;

統計情報パッケージも同様に、次の例に示すように設定できます。

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

次の DDL コマンドのように、複数のオプションを同時に設定することもできます。

GoogleSQL

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

次のように、gcloud CLI databases ddl update コマンドを使用して gcloud CLI で ALTER DATABASE を実行できます。

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'

データベース オプションを NULL(GoogleSQL)または DEFAULT(PostgreSQL)に設定することでオプションがクリアされ、デフォルト値が使用されます。

データベースの現在のオプションの値を確認するには、INFORMATION_SCHEMA.DATABASE_OPTIONS ビューにクエリを送信するか、PostgreSQL の information_schema database_options テーブルに対して次のクエリを実行します。

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

クライアント ライブラリでオプティマイザー オプションを設定する

クライアント ライブラリを介して Spanner をプログラムで操作する場合、クライアント アプリケーションのクエリ オプションを変更する方法がいくつかあります。

オプティマイザーのオプションを設定するには、クライアント ライブラリの最新バージョンを使用する必要があります。

データベース クライアントのオプティマイザー オプションを設定する

アプリケーションは、次のコード スニペットに示すように、クエリ オプション プロパティを構成することで、クライアント ライブラリにオプティマイザー オプションをグローバルに設定できます。 オプティマイザの設定はクライアント インスタンスに保存され、クライアントの存続期間中に実行されるすべてのクエリに適用されます。オプションはバックエンドのデータベース レベルで適用されますが、クライアント レベルで設定された場合は、そのクライアントに接続されるすべてのデータベースに適用されます。

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

環境変数を使用してオプティマイザーのオプションを設定する

アプリを再コンパイルせずにさまざまなオプティマイザ バージョンを試すには、次のスニペットのように SPANNER_OPTIMIZER_VERSION 環境変数と SPANNER_OPTIMIZER_STATISTICS_PACKAGE 環境変数を設定してアプリを実行します。

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="6"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="6"
  set SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

指定されたクエリ オプティマイザーのオプション値は、クライアントの初期化時にクライアント インスタンスに読み取られて保存され、クライアントの存続期間中に実行されるすべてのクエリに適用されます。

クライアント クエリのオプティマイザーのオプションを設定する

クエリを作成するときにクエリ オプション プロパティを指定することで、クライアント アプリケーションのクエリレベルでオプティマイザーのバージョンまたは統計情報パッケージのバージョンの値を指定できます。

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

ステートメント ヒントを使用してクエリのオプティマイザー オプションを設定する

ステートメント ヒントは、クエリの実行をデフォルトの動作から変更するヒントです。ステートメントに OPTIMIZER_VERSION ヒントを設定すると、指定したクエリ オプティマイザーのバージョンを使用してそのクエリが実行されます。

OPTIMIZER_VERSION ヒントは、オプティマイザのバージョン優先度が最も高くなります。ステートメント ヒントが指定されている場合は、他のすべてのオプティマイザーのバージョン設定に関係なく使用されます。

GoogleSQL

@{OPTIMIZER_VERSION=6} SELECT * FROM MyTable;

PostgreSQL

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

latest_version リテラルを使用して、クエリのオプティマイザのバージョンを最新バージョンに設定することもできます。

GoogleSQL

@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;

PostgreSQL

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

ステートメントに OPTIMIZER_STATISTICS_PACKAGE ヒントを設定すると、指定したクエリ オプティマイザーの統計情報パッケージのバージョンを使用して、そのクエリが実行されます。指定したパッケージは、ガベージ コレクションを無効にする必要があります

GoogleSQL

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

PostgreSQL

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

OPTIMIZER_STATISTICS_PACKAGE ヒントは、オプティマイザー パッケージの設定優先度が最も高くなります。ステートメント ヒントが指定されている場合は、他のすべてのオプティマイザー パッケージのバージョン設定に関係なく使用されます。

@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;

latest リテラルを使用して最新の統計情報パッケージを使用することもできます。

@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;

次の例に示すように、両方のヒントを 1 つのステートメントで設定できます。

default_version リテラルでは、クエリのオプティマイザのバージョンがデフォルトのバージョンに設定されます。これは、最新バージョンと異なる場合があります。詳細については、デフォルトのバージョンをご覧ください。

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;

Spanner JDBC ドライバを使用する場合にオプティマイザー オプションを設定する

次の例に示すように、JDBC 接続文字列でオプションを指定すると、オプティマイザーのバージョンと統計情報パッケージのデフォルト値をオーバーライドできます。

これらのオプションは、Spanner JDBC ドライバの最新バージョンでのみサポートされています。

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

次の例に示すように、SET OPTIMIZER_VERSION ステートメントを使用してクエリ オプティマイザーのバージョンを設定することもできます。

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

オープンソース ドライバの使用の詳細については、オープンソース JDBC ドライバの使用をご覧ください。

無効なオプティマイザー バージョンの処理方法

Spanner では、オプティマイザーのバージョンの範囲がサポートされます。 この範囲は、クエリ オプティマイザーが更新されると変更されます。指定したバージョンが範囲外の場合、クエリは失敗します。たとえば、ステートメント ヒント @{OPTIMIZER_VERSION=7} を使用してクエリを実行しようとしたものの、最新のオプティマイザーのバージョン番号が 6 の場合、Spanner はこのエラー メッセージで応答します。

Query optimizer version: 7 is not supported

無効なオプティマイザーの統計情報パッケージの設定を処理する

データベースまたはクエリを使用可能な統計パッケージに固定するには、このページの上記のいずれかの方法を使用します。無効な統計情報パッケージ名を指定すると、クエリは失敗します。クエリで指定された統計情報パッケージは、次のいずれかである必要があります。

クエリの実行に使用されるクエリ オプティマイザーのバージョンを確認する

クエリで使用されるオプティマイザーのバージョンは、Google Cloud コンソールと Google Cloud CLI に表示されます。

Google Cloud コンソール

クエリに使用されるオプティマイザーのバージョンを表示するには、Google Cloud コンソールの [Spanner Studio] ページでクエリを実行し、[説明] タブを選択します。次のようなメッセージが表示されます。

クエリ オプティマイザーのバージョン: 6

gcloud CLI

gcloud CLI でクエリを実行するときに使用されるバージョンを確認するには、次のスニペットに示すように --query-mode フラグを PROFILE に設定します。

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

Metrics Explorer でクエリ オプティマイザーのバージョンを可視化する

Cloud Monitoring では、アプリケーションやシステム サービスのパフォーマンスの理解に役立つ測定値が収集されます。Spanner 用に収集される指標の 1 つは、クエリ数です。これは、一定期間にサンプリングされたインスタンス内のクエリ数を測定します。この指標は、エラーコードでグループ化されたクエリを表示するために非常に有用で、この指標を使用して各クエリの実行に使われているオプティマイザー バージョンを確認することもできます。

Google Cloud コンソールの Metrics Explorer を使用して、データベース インスタンスのクエリ数を可視化できます。図 1 は、3 つのデータベースのクエリ数を示しています。各データベースで使用されているオプティマイザーのバージョンを確認できます。

この図のグラフの下にある表は、my-db-1 での無効なオプティマイザー バージョンを使用したクエリの実行によって、Bad usage というステータスが返され、その結果、クエリ数が 0 になったことを示しています。他のデータベースでは、それぞれオプティマイザーのバージョン 1 とバージョン 2 を使用してクエリを実行しました。

クエリ オプティマイザーのバージョン別にグループ化された Metrics Explorer でのクエリ数

図 1:Metrics Explorer に表示されるクエリ オプティマイザーのバージョン別にグループ化されたクエリの数

インスタンスに同様のチャートを設定するには:

  1. Google Cloud コンソールで Metrics Explorer に移動します。
  2. [リソースタイプ] フィールドで Cloud Spanner Instance を選択します。
  3. [指標] フィールドで [Count of queries] を選択します。
  4. [グループ条件] フィールドで databaseoptimizer_versionstatus を選択します。

この例では、同じデータベース内の複数のクエリで異なるオプティマイザ バージョンを使用している場合は示していません。その場合は、データベースとオプティマイザのバージョンの組み合わせごとに棒グラフが表示されます。

Cloud Monitoring を使用して Spanner インスタンスをモニタリングする方法については、Cloud Monitoring を使用したモニタリングをご覧ください。