쿼리 최적화 도구 관리

Spanner 쿼리 최적화 도구는 SQL 쿼리를 실행하는 가장 효율적인 방법을 결정합니다. 그러나 쿼리 최적화 도구 자체가 진화하거나 데이터베이스 통계가 업데이트되면 최적화 도구에서 결정한 쿼리 계획이 약간 변경될 수 있습니다. 쿼리 최적화 도구나 통계가 변경될 때 성능 회귀 가능성이 최소화되도록 Spanner는 다음 쿼리 옵션을 제공합니다.

  • optimizer_version: 쿼리 최적화 도구 변경사항이 번들화 되어 최적화 도구 버전으로 출시됩니다. Spanner는 출시일로부터 최소 30일 후에 최신 버전의 최적화 도구를 기본적으로 사용하기 시작합니다. 사용자는 쿼리 최적화 도구 버전 옵션을 사용하여 이전 버전의 최적화 도구에 대해 쿼리를 실행할 수 있습니다.

  • optimizer_statistics_package: 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로 설정하면 옵션이 삭제되므로 기본값이 사용됩니다.

데이터베이스에 대한 이러한 옵션의 현재 값을 보려면 다음과 같이 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"
	"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/Mac OS

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;

다음 예시와 같이 두 힌트 모두 단일 문에서 설정할 수 있습니다.

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 Console 및 Google Cloud CLI에서 확인할 수 있습니다.

Google Cloud 콘솔

쿼리에 사용된 최적화 도구 버전을 보려면 Google Cloud 콘솔의 Spanner 스튜디오 페이지에서 쿼리를 실행한 후 설명 탭을 선택합니다. 다음과 유사한 메시지가 표시됩니다.

쿼리 옵티마이저 버전: 6

gcloud CLI

gcloud CLI에서 쿼리를 실행할 때 사용되는 버전을 확인하려면 아래 스니펫과 같이 --query-mode 플래그를 PROFILE로 설정합니다.

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

측정항목 탐색기에서 쿼리 최적화 도구 버전 시각화

Cloud Monitoring은 애플리케이션과 시스템 서비스의 성능을 파악하는 데 도움이 되는 측정 값을 수집합니다. Spanner에 수집되는 측정항목 중 하나는 일정 기간에 따라 샘플링된 인스턴스 하나에서 쿼리 수를 측정하는 쿼리 수입니다. 이 측정항목은 오류 코드로 그룹화된 쿼리를 볼 때 매우 유용하지만, 각 쿼리 실행에 사용된 최적화 도구 버전을 확인하는 데에도 사용할 수 있습니다.

Google Cloud 콘솔의 측정항목 탐색기를 사용하여 데이터베이스 인스턴스의 쿼리 수를 시각화할 수 있습니다. 그림 1은 3개 데이터베이스의 쿼리 수를 보여줍니다. 각 데이터베이스에서 사용 중인 최적화 도구 버전을 확인할 수 있습니다.

이 그림의 차트 아래에 있는 표는 my-db-1이 잘못된 최적화 도구 버전으로 쿼리를 실행하려 시도하여 잘못된 사용 상태가 반환되고 쿼리 수가 0이 되었음을 보여줍니다. 다른 데이터베이스는 각각 최적화 도구 버전 1 및 2를 사용하여 쿼리를 실행했습니다.

쿼리 최적화 버전으로 그룹화된 측정항목 탐색기 내의 쿼리 수

그림 1. 최적화 그룹 버전별로 그룹화된 쿼리로 측정항목 탐색기에 표시되는 쿼리 수

인스턴스에 대해 비슷한 차트를 설정하려면 다음 안내를 따르세요.

  1. Google Cloud 콘솔에서 측정항목 탐색기로 이동합니다.
  2. 리소스 유형 필드에서 Cloud Spanner Instance를 선택합니다.
  3. 측정항목 필드에서 Count of queries를 선택합니다.
  4. 그룹화 기준 필드에서 database, optimizer_version, status를 선택합니다.

이 예에서는 동일한 데이터베이스의 여러 쿼리에 다른 최적화 도구 버전을 사용하는 경우에 대해서는 설명하지 않습니다. 이 경우 차트에는 데이터베이스 및 최적화 도구 버전의 각 조합에 대한 막대 세그먼트가 표시됩니다.

Cloud Monitoring을 사용하여 Spanner 인스턴스를 모니터링하는 방법은 Cloud Monitoring으로 모니터링을 참조하세요.