ניהול הכלי לאופטימיזציה של שאילתות

בדף הזה מוסבר איך לנהל את האופטימיזציה של השאילתות במסדי נתונים של Spanner בפורמט GoogleSQL ובפורמט PostgreSQL.

אופטימיזציית השאילתות של Spanner קובעת את הדרך היעילה ביותר להריץ שאילתת SQL. עם זאת, יכול להיות שתוכנית השאילתה שנקבעה על ידי האופטימיזציה תשתנה מעט כשהאופטימיזציה של השאילתות עצמה תתפתח, או כשהסטטיסטיקות של מסד הנתונים יעודכנו. כדי לצמצם את הסיכון לירידה בביצועים כשמבצעים שינויים באופטימיזציה של השאילתות או בסטטיסטיקה, Spanner מספק את האפשרויות הבאות לשאילתות.

‫optimizer_version: שינויים באופטימיזציית השאילתות נארזים ומופצים כגרסאות של אופטימיזציה. מערכת Spanner מתחילה להשתמש בגרסה האחרונה של האופטימיזציה כברירת מחדל לפחות 30 יום אחרי שהגרסה הזו יוצאת. אפשר להשתמש באפשרות של גרסת אופטימיזציית השאילתות כדי להריץ שאילתות נגד גרסה ישנה יותר של האופטימיזציה.
‫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 = false)
```

במדריך הזה מוסבר איך להגדיר את האפשרויות האלה בהיקפים שונים ב-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. הגדרת גרסת האופטימיזציה באמצעות אחת מהשיטות שצוינו קודם מקבלת קדימות על פני כל מה שמופיע משמאל לה. לדוגמה, אם מגדירים את האופטימיזציה של אפליקציה באמצעות משתנה סביבה, ההגדרה הזו מקבלת עדיפות על פני כל ערך שמגדירים למסד הנתונים באמצעות האפשרות database. הגדרת גרסת האופטימיזציה באמצעות רמז להצהרה מקבלת את העדיפות הגבוהה ביותר בשאילתה הנתונה, והיא קודמת לערך שהוגדר באמצעות כל שיטה אחרת.

בקטעים הבאים מוסבר בהרחבה על כל שיטה.

הגדרת אפשרויות של כלי האופטימיזציה

אפשר להגדיר את אפשרות האופטימיזציה שמוגדרת כברירת מחדל באמצעות השיטות הבאות:

ברמת מסד הנתונים
לשאילתה שמשתמשת ברמז של הצהרה
באמצעות ספריות לקוח
שימוש ב-Spanner JDBC driver

במקרים מסוימים, יכול להיות ש-Spanner ישתמש בגרסה ישנה יותר של אופטימיזציית שאילתות עבור צורה ספציפית של שאילתה, גם אם הצמדתם גרסה חדשה יותר. זו התנהגות פנימית צפויה שמטרתה להבטיח את היציבות והביצועים של השאילתות. כדי לזהות את גרסת האופטימיזציה ששימשה לשאילתה מסוימת, צריך לבדוק את תוכנית הביצוע של השאילתה.

הגדרת אפשרויות של הכלי לאופטימיזציה ברמת מסד הנתונים

כדי להגדיר את גרסת האופטימיזציה שמוגדרת כברירת מחדל במסד נתונים, משתמשים בפקודת ה-DDL‏ ALTER DATABASE הבאה. הגדרת האפשרות הזו לא מחייבת שכל השאילתות יפעילו את הגרסה הזו. במקום זאת, היא מגדירה רף עליון לגרסת QO שמשמשת לשאילתות. השימוש המיועד בו הוא לצמצום רגרסיות שמתרחשות אחרי שגרסה חדשה של כלי האופטימיזציה יוצאת.

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

אפשר להריץ את ALTER DATABASE ב-CLI של gcloud באמצעות הפקודה gcloud CLI databases ddl update, באופן הבא.

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) מוחקת אותה כך שנעשה שימוש בערך ברירת המחדל.

כדי לראות את הערך הנוכחי של האפשרויות האלה במסד נתונים, מריצים שאילתה בתצוגה INFORMATION_SCHEMA.DATABASE_OPTIONS ב-GoogleSQL או בטבלה information_schema database_options ב-PostgreSQL, כמו שמוצג בהמשך.

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

הגדרת אפשרויות של כלי האופטימיזציה לשאילתה באמצעות רמז להצהרה

רמז להצהרה הוא רמז בהצהרת שאילתה שמשנה את ההרצה של השאילתה מההתנהגות שמוגדרת כברירת מחדל. הגדרת הרמז 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 בהצהרה מאלצת את השאילתה הזו לפעול באמצעות גרסת חבילת הנתונים הסטטיסטיים של אופטימיזציית השאילתות שצוינה. יש להשבית את ה-garbage collection בחבילה שצוינה:

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 באופן פרוגרמטי באמצעות ספריות לקוח, יש כמה דרכים לשנות את אפשרויות השאילתה באפליקציית הלקוח.

כדי להגדיר אפשרויות של הכלי לאופטימיזציה, צריך להשתמש בגרסאות העדכניות של ספריות הלקוח.

ללקוח מסד נתונים

אפליקציה יכולה להגדיר אפשרויות אופטימיזציה באופן גלובלי בספריית הלקוח על ידי הגדרת המאפיין query options, כמו שמוצג בקטעי הקוד הבאים. הגדרות האופטימיזציה מאוחסנות במופע הלקוח ומוחלות על כל השאילתות שמופעלות במהלך משך החיים של הלקוח. למרות שהאפשרויות חלות ברמת מסד הנתונים בקצה העורפי, כשהן מוגדרות ברמת הלקוח, הן חלות על כל מסדי הנתונים שמחוברים לאותו לקוח.

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

הגדרת אפשרויות אופטימיזציה כשמשתמשים במנהל ההתקנים של Spanner JDBC

אפשר לשנות את ערך ברירת המחדל של גרסת האופטימיזציה וחבילת הנתונים הסטטיסטיים על ידי ציון אפשרויות במחרוזת החיבור של JDBC, כמו שמוצג בדוגמה הבאה.

האפשרויות האלה נתמכות רק בגרסאות העדכניות של Spanner JDBC driver.

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

כדי לראות את גרסת האופטימיזציה ששימשה לשאילתה, מריצים את השאילתה בדף Spanner Studio במסוף Google Cloud , ואז בוחרים בכרטיסייה הסבר. אמורה להופיע הודעה דומה לזו:

גרסה של כלי אופטימיזציית השאילתות: 8

‫CLI של gcloud

כדי לראות את הגרסה שבה נעשה שימוש כשמריצים שאילתה ב-CLI של gcloud, מגדירים את הדגל --query-mode לערך PROFILE כמו בדוגמה הבאה.

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

המחשה ויזואלית של הגרסה של אופטימיזציית השאילתות ב-Metrics Explorer

‫Cloud Monitoring אוסף מדידות כדי לעזור לכם להבין את הביצועים של האפליקציות ושירותי המערכת שלכם. אחד המדדים שנאספים ב-Spanner הוא count of queries (מספר השאילתות), שמודד את מספר השאילתות במופע, שנדגמו לאורך זמן. המדד הזה מאוד שימושי לצפייה בשאילתות שמקובצות לפי קוד שגיאה, אבל אפשר להשתמש בו גם כדי לראות באיזו גרסה של הכלי לאופטימיזציה נעשה שימוש כדי להריץ כל שאילתה.

אתם יכולים להשתמש בMetrics Explorer במסוףGoogle Cloud כדי להציג את מספר השאילתות של מופע מסד הנתונים. באיור 1 מוצג מספר השאילתות עבור שלושה מסדי נתונים. אפשר לראות איזו גרסת אופטימיזציה נמצאת בשימוש בכל מסד נתונים.

בטבלה שמתחת לתרשים באיור הזה אפשר לראות שב-my-db-1 נעשה ניסיון להריץ שאילתה עם גרסה לא תקינה של אופטימיזציה, והסטטוס שהוחזר הוא שימוש לא תקין. כתוצאה מכך, מספר השאילתות הוא 0. במסדי הנתונים האחרים הופעלו שאילתות באמצעות גרסאות 1 ו-2 של הכלי לאופטימיזציה, בהתאמה.

מספר השאילתות ב-Metrics Explorer מקובץ לפי גרסת הכלי לאופטימיזציה של שאילתות

איור 1. ספירת השאילתות שמוצגת ב-Metrics Explorer עם שאילתות שמקובצות לפי גרסת האופטימיזציה.

כדי להגדיר תרשים דומה למופע שלכם:

עוברים אל Metrics Explorer במסוף Google Cloud .
בשדה Resource type, בוחרים באפשרות Cloud Spanner Instance.
בשדה Metric (מדד), בוחרים באפשרות Count of queries.
בשדה Group By, בוחרים באפשרויות database,‏ optimizer_version ו-status.

בדוגמה הזו לא מוצג המקרה שבו נעשה שימוש בגרסה אחרת של כלי האופטימיזציה לשאילתות שונות באותו מסד נתונים. במקרה כזה, בתרשים יוצג פלח עמודה לכל שילוב של מסד נתונים וגרסת אופטימיזציה.

במאמר מעקב באמצעות Cloud Monitoring מוסבר איך להשתמש ב-Cloud Monitoring כדי לעקוב אחרי מופעי Spanner.

ניהול הכלי לאופטימיזציה של שאילתות קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.

GoogleSQL

PostgreSQL

רשימה של אפשרויות לאופטימיזציה של שאילתות

גרסאות של הכלי לשיפור הביצועים

גרסת ברירת המחדל

חבילות נתונים סטטיסטיים של אופטימיזציה

סדר העדיפות של שינוי ברירת המחדל של האפשרות

הגדרת אפשרויות של כלי האופטימיזציה

הגדרת אפשרויות של הכלי לאופטימיזציה ברמת מסד הנתונים

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

הגדרת אפשרויות של כלי האופטימיזציה לשאילתה באמצעות רמז להצהרה

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

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

הגדרת אפשרויות אופטימיזציה כשמשתמשים במנהל ההתקנים של Spanner JDBC

איך המערכת מטפלת בגרסאות לא תקינות של כלי האופטימיזציה

טיפול בהגדרה לא תקינה של חבילת נתונים סטטיסטיים של אופטימיזציה

איך קובעים את הגרסה של הכלי לאופטימיזציה של שאילתות ששימשה להרצת שאילתה

מסוף Google Cloud

‫CLI של gcloud

המחשה ויזואלית של הגרסה של אופטימיזציית השאילתות ב-Metrics Explorer

ניהול הכלי לאופטימיזציה של שאילתות