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

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

optimizer_version: クエリオプティマイザーの変更は、オプティマイザーのバージョンとしてバンドルされてリリースされます。Spanner は、オプティマイザの最新バージョンがリリースされてから少なくとも 30 日後にデフォルトとして使用を開始します。クエリオプティマイザーのバージョンオプションを使用すると、古いバージョンのオプティマイザーに対してクエリを実行できます。
オプティマイザーの統計情報パッケージ: Spanner は、オプティマイザーの統計情報を定期的に更新します。新しい統計情報は、パッケージとして利用できます。このクエリオプションは、SQL クエリのコンパイル時に使用するクエリオプティマイザーの統計情報パッケージを指定します。指定したパッケージは、ガベージコレクションを無効にする必要があります。

GoogleSQL

 ALTER STATISTICS  SET OPTIONS (allow_gc=false)

PostgreSQL

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

このガイドでは、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 に設定することでオプションがクリアされ、デフォルト値が使用されます。

データベースの現在のオプションの値を確認するには、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

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

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

データベースレベルで設定されている、または
ALLOW_GC=false とマークされている

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

クエリで使用されるオプティマイザーのバージョンは、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 に表示されるクエリオプティマイザーのバージョン別にグループ化されたクエリの数。

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

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

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

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

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

GoogleSQL

PostgreSQL

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

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

デフォルトのバージョン

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

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

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

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

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

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

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

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

Linux / macOS

Windows

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

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

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

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

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

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

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

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

Google Cloud コンソール

gcloud CLI

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

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

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

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

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

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

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

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

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

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

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

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