本頁面由 Cloud Translation API 翻譯而成。

管理查詢最佳化工具

本頁面說明如何管理 Spanner 中的查詢最佳化工具，以便支援 GoogleSQL 方言資料庫和 PostgreSQL 方言資料庫。

Spanner 查詢最佳化工具會判斷執行 SQL 查詢最有效率的方式。不過，當查詢最佳化工具本身進化，或資料庫統計資料更新時，由最佳化工具決定的查詢計畫可能會略有變動。為盡量減少查詢最佳化工具或統計資料變更時的效能回溯情形，Spanner 提供下列查詢選項。

optimizer_version：查詢最佳化工具的變更會組合在一起，並以最佳化工具版本發布。在最新版本發布後至少 30 天，Spanner 才會開始將其設為預設值。您可以使用查詢最佳化器版本選項，針對舊版最佳化器執行查詢。
optimizer_statistics_package：Spanner 會定期更新最佳化工具統計資料。新的統計資料會以套件的形式提供。這個查詢選項會指定統計資料套件，供查詢最佳化工具在編譯 SQL 查詢時使用。指定的套件必須停用垃圾收集功能：

GoogleSQL

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

PostgreSQL

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

本指南將說明如何在 Spanner 的不同範圍中設定這些個別選項。

列出查詢最佳化工具選項

Spanner 會儲存可用的最佳化器版本和可選取的統計資料套件相關資訊。

最佳化工具版本

查詢最佳化器版本是整數值，每次更新時會遞增 1。查詢最佳化器的最新版本為 8。

執行下列 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 =  8);

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

PostgreSQL

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

將資料庫選項設為 NULL (在 GoogleSQL 中) 或 DEFAULT (在 PostgreSQL 中) 會清除該選項，以便使用預設值。

如要查看資料庫的這些選項目前的值，請查詢 GoogleSQL 的 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"
	sppb "cloud.google.com/go/spanner/apiv1/spannerpb"
	"google.golang.org/api/iterator"
)

func createClientWithQueryOptions(w io.Writer, database string) error {
	ctx := context.Background()
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	client, err := spanner.NewClientWithConfig(
		ctx, database, spanner.ClientConfig{QueryOptions: queryOptions},
	)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void clientWithQueryOptions(DatabaseId db) {
  SpannerOptions options =
      SpannerOptions.newBuilder()
          .setDefaultQueryOptions(
              db, QueryOptions
                  .newBuilder()
                  .setOptimizerVersion("1")
                  // The list of available statistics packages can be found by querying the
                  // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                  .setOptimizerStatisticsPackage("latest")
                  .build())
          .build();
  Spanner spanner = options.getService();
  DatabaseClient dbClient = spanner.getDatabaseClient(db);
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(
  databaseId,
  {},
  {
    optimizerVersion: '1',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  },
);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`,
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Create a client with query options.
 * Example:
 * ```
 * create_client_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_client_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient([
        'queryOptions' => [
            'optimizerVersion' => '1',
            // Pin the statistics package used for this client instance to the
            // latest version. The list of available statistics packages can be
            // found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
            // table.
            'optimizerStatisticsPackage' => 'latest'
        ]
    ]);
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues'
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client(
    query_options={
        "optimizer_version": "1",
        "optimizer_statistics_package": "latest",
    }
)
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

query_options = {
  optimizer_version: "1",
  # The list of available statistics packages can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id, query_options: query_options

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"

client.execute(sql_query).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

使用環境變數設定最佳化工具選項

如要輕鬆嘗試不同的最佳化器設定，而無須重新編譯應用程式，您可以設定 SPANNER_OPTIMIZER_VERSION 和 SPANNER_OPTIMIZER_STATISTICS_PACKAGE 環境變數，然後執行應用程式，如以下程式碼片段所示。

Linux / macOS

export SPANNER_OPTIMIZER_VERSION="8"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"

Windows

set SPANNER_OPTIMIZER_VERSION="8"
  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"
	sppb "cloud.google.com/go/spanner/apiv1/spannerpb"
	"google.golang.org/api/iterator"
)

func queryWithQueryOptions(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: `SELECT VenueId, VenueName, LastUpdateTime FROM Venues`}
	queryOptions := spanner.QueryOptions{
		Options: &sppb.ExecuteSqlRequest_QueryOptions{
			OptimizerVersion: "1",
			// The list of available statistics packages can be found by
			// querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
			OptimizerStatisticsPackage: "latest",
		},
	}
	iter := client.Single().QueryWithOptions(ctx, stmt, queryOptions)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueName string
		var lastUpdateTime time.Time
		if err := row.Columns(&venueID, &venueName, &lastUpdateTime); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s %s\n", venueID, venueName, lastUpdateTime)
	}
}

Java

static void queryWithQueryOptions(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement
                  .newBuilder("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")
                  .withQueryOptions(QueryOptions
                      .newBuilder()
                      .setOptimizerVersion("1")
                      // The list of available statistics packages can be found by querying the
                      // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
                      .setOptimizerStatisticsPackage("latest")
                      .build())
                  .build())) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums
        ORDER BY AlbumTitle`,
  queryOptions: {
    optimizerVersion: 'latest',
    // The list of available statistics packages can be found by querying the
    // "INFORMATION_SCHEMA.SPANNER_STATISTICS" table.
    optimizerStatisticsPackage: 'latest',
  },
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();
    const marketingBudget = json.MarketingBudget
      ? json.MarketingBudget
      : null; // This value is nullable
    console.log(
      `AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`,
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;

/**
 * Queries sample data using SQL with query options.
 * Example:
 * ```
 * query_data_with_query_options($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_query_options(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT VenueId, VenueName, LastUpdateTime FROM Venues',
        [
            'queryOptions' => [
                'optimizerVersion' => '1',
                // Pin the statistics package to the latest version just for
                // this query.
                'optimizerStatisticsPackage' => 'latest'
            ]
        ]
    );

    foreach ($results as $row) {
        printf('VenueId: %s, VenueName: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['VenueId'], $row['VenueName'], $row['LastUpdateTime']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT VenueId, VenueName, LastUpdateTime FROM Venues",
        query_options={
            "optimizer_version": "1",
            "optimizer_statistics_package": "latest",
        },
    )

    for row in results:
        print("VenueId: {}, VenueName: {}, LastUpdateTime: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options = {
  optimizer_version: "1",
  # The list of available statistics packagebs can be
  # found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
  # table.
  optimizer_statistics_package: "latest"
}

client.execute(sql_query, query_options: query_options).rows.each do |row|
  puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end

使用陳述式提示為查詢設定最佳化器選項

陳述式提示是指查詢陳述式中的提示，可將查詢的執行方式從預設行為變更。在陳述式上設定 OPTIMIZER_VERSION 提示，可強制使用指定的查詢最佳化器版本執行查詢。

OPTIMIZER_VERSION 提示的最佳化器版本優先順序最高。如果指定陳述式提示，系統會無視所有其他最佳化工具版本設定，直接使用該陳述式提示。

GoogleSQL

@{OPTIMIZER_VERSION=8} SELECT * FROM MyTable;

PostgreSQL

/*@OPTIMIZER_VERSION=8*/ 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;

這兩種提示可在單一陳述式中設定，如以下範例所示。

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=9} 執行查詢，但最新的最佳化工具版本號碼僅為 8，Spanner 會回應以下錯誤訊息：

Query optimizer version: 9 is not supported

處理無效的最佳化工具統計資料套件設定

您可以使用本頁先前所述的其中一種方法，將資料庫或查詢釘選至任何可用的統計資料套件。如果提供的統計資料套件名稱無效，查詢就會失敗。查詢指定的統計資料套件必須為下列其中一種：

在資料庫層級設定；或
標示為 ALLOW_GC=false

判斷執行查詢時使用的查詢最佳化器版本

您可以透過 Google Cloud 控制台和 Google Cloud CLI 查看用於查詢的最佳化器版本。

Google Cloud 控制台

如要查看用於查詢的最佳化器版本，請在 Google Cloud 控制台的 Spanner Studio 頁面中執行查詢，然後選取「Explanation」分頁。畫面上應會顯示類似以下的訊息：

查詢最佳化器版本：8

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 收集的指標之一是「查詢數量」，可用於評估執行個體中查詢的數量，並隨時間進行取樣。雖然這個指標非常適合用來查看依錯誤代碼分組的查詢，但我們也可以用來查看執行每個查詢時使用的最佳化器版本。

您可以使用Google Cloud 控制台中的 Metrics Explorer，以視覺化方式呈現資料庫執行個體的查詢數量。圖 1 顯示三個資料庫的查詢次數。您可以查看每個資料庫使用的最佳化器版本。

這張圖表下方的表格顯示 my-db-1 嘗試使用無效的最佳化器版本執行查詢，因此會傳回「Bad usage」狀態，並導致查詢計數為 0。其他資料庫則分別使用 1 和 2 版最佳化工具執行查詢。

Metrics Explorer 中依查詢最佳化器版本分組的查詢數量

圖 1. Metrics Explorer 中顯示的查詢次數，其中查詢會按最佳化器版本分組。

如要為執行個體設定類似的圖表，請按照下列步驟操作：

前往 Google Cloud 控制台的「Metrics Explorer」。
在「Resource type」欄位中，選取「Cloud Spanner Instance」。
在「指標」欄位中，選取 Count of queries。
在「Group By」(分組依據) 欄位中，選取 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 中以圖表呈現查詢最佳化器版本

管理查詢最佳化工具