שיטות מומלצות לשימוש בשפת טיפול בנתונים (DML)

בדף הזה מתוארות שיטות מומלצות לשימוש בשפת טיפול בנתונים (DML) וב-DML עם חלוקה למחיצות במסדי נתונים של ניב GoogleSQL ובמסדי נתונים של ניב PostgreSQL.

שימוש בסעיף WHERE כדי לצמצם את היקף הנעילות

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

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

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

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

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

כדי שהעדכון יהיה יעיל יותר, צריך לכלול את העמודה SingerId במשפט WHERE. העמודה SingerId היא עמודת המפתח הראשי היחידה בטבלה Singers: 

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

אם אין אינדקס ב-FirstName או ב-LastName, צריך לסרוק את כל הטבלה כדי למצוא את הזמרים המבוקשים. אם לא רוצים להוסיף אינדקס משני כדי שהעדכון יהיה יעיל יותר, צריך לכלול את העמודה SingerId בסעיף WHERE.

העמודה SingerId היא העמודה היחידה של המפתח הראשי בטבלה Singers. כדי למצוא אותו, מריצים את הפקודה SELECT בעסקה נפרדת לקריאה בלבד לפני עסקה העדכון: 


  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

מומלץ להימנע משימוש בהצהרות DML ושינויים באותה טרנזקציה

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

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

בפעולות שנתמכות רק באמצעות מוטציות, יכול להיות שתרצו לשלב בין הצהרות DML ומוטציות באותה טרנזקציה – לדוגמה, insert_or_update.

אם משתמשים בשניהם, הכתיבה למאגר מתבצעת רק בסוף העסקה.

משתמשים בפונקציה PENDING_COMMIT_TIMESTAMP כדי לכתוב חותמות זמן של ביצועים

GoogleSQL

משתמשים בפונקציה PENDING_COMMIT_TIMESTAMP כדי לכתוב את חותמת הזמן של ביצוע השינוי בפקודת DML. מערכת Spanner בוחרת את חותמת הזמן של השמירה כשמתבצעת שמירה של הטרנזקציה.

PostgreSQL

משתמשים בפונקציה SPANNER.PENDING_COMMIT_TIMESTAMP() כדי לכתוב את חותמת הזמן של ביצוע השינוי בפקודת DML. מערכת Spanner בוחרת את חותמת הזמן של השמירה כשמתבצעת שמירה של הטרנזקציה.

פונקציות של DML מחולקות למחיצות ופונקציות של תאריך וחותמת זמן

ב-DML עם חלוקה למחיצות נעשה שימוש בעסקה אחת או יותר, שיכול להיות שהן יפעלו ויבוצעו בזמנים שונים. אם משתמשים בפונקציות date או timestamp, יכול להיות שהשורות ששונו יכילו ערכים שונים.

שיפור זמן האחזור באמצעות DML של קבוצות

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

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

  • ביצוע פעולות כתיבה באמצעות בקשה אחת

    מערכת Spanner מבצעת אופטימיזציה אוטומטית לקבוצות רצופות של הצהרות דומות INSERT, UPDATE או DELETE באצ' עם ערכי פרמטרים שונים, אם הן לא מפרות תלות בנתונים.

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

    INSERT INTO Albums (SingerId, AlbumId, AlbumTitle) VALUES (@Singer, @Album, @Title);

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

  • ביצוע כתיבות במקביל

    ‫Spanner מבצע אופטימיזציה אוטומטית של קבוצות רצופות של הצהרות DML על ידי ביצוע מקביל, אם זה לא פוגע בתלות בנתונים. האופטימיזציה הזו משפרת את הביצועים של מגוון רחב יותר של פקודות DML באצווה, כי היא יכולה לחול על שילוב של סוגי פקודות DML‏ (INSERT,‏ UPDATE ו-DELETE) וגם על פקודות DML עם פרמטרים או בלי פרמטרים.

    לדוגמה, בסכימה לדוגמה שלנו יש את הטבלאות Singers, Albums ו-Accounts. ‫Albums משולב ב-Singers ושומר מידע על אלבומים ב-Singers. הקבוצה הרציפה הבאה של הצהרות כותבת שורות חדשות לכמה טבלאות ואין לה תלות מורכבת בנתונים.

    INSERT INTO Singers (SingerId, Name) VALUES(1, "John Doe");
INSERT INTO Singers (SingerId, Name) VALUES(2, "Marcel Richards");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10001, "Album 1");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10002, "Album 2");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (2, 10001, "Album 1");
UPDATE Accounts SET Balance = 100 WHERE AccountId = @AccountId;

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

הפעלת אצווה בצד הלקוח ב-JDBC

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

כברירת מחדל, auto_batch_dml מוגדר כ-false. כדי להפעיל את האפשרות הזו, צריך להגדיר אותה לערך true במחרוזת החיבור של JDBC.

לדוגמה:

String url = "jdbc:cloudspanner:/projects/my-project/instances/my-instance/databases/my-database;auto_batch_dml=true";
try (Connection connection = DriverManager.getConnection(url)) {
    // Include your DML statements for batching here
}

כשהמאפיין הזה מופעל, Spanner שולח הצהרות DML שנשמרו במאגר כקבוצה כשמופעלת הצהרה שאינה DML או כשמתבצעת פעולת commit של העסקה הנוכחית. המאפיין הזה חל רק על טרנזקציות של קריאה וכתיבה; הוראות DML במצב אישור אוטומטי מופעלות ישירות.

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

שימוש באפשרות last_statement כדי להפחית את זמן האחזור של DML

אם המשפט האחרון בעסקת קריאה-כתיבה הוא פקודת DML, אפשר להשתמש באפשרות השאילתה last_statement כדי להקטין את זמן האחזור. האפשרות הזו זמינה בממשקי ה-API של שאילתות executeSql ו-executeStreamingSql.

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

האפשרות last_statement נתמכת בספריות הלקוח הבאות:

  • גרסה 1.77.0 ואילך
  • ‫Java בגרסה 2.27.0 ואילך
  • ‫Python בגרסה 3.53.0 ואילך
  • ‫PGAdapter בגרסה 0.45.0 ואילך

היא נתמכת ומופעלת כברירת מחדל כשמשתמשים במצב אישור אוטומטי במנהלי ההתקנים הבאים:

Go

GoogleSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func updateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

PostgreSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func pgUpdateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

Java

GoogleSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of(
                    "UPDATE Singers SET Singers.LastName = 'Doe' WHERE SingerId = 54213\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

PostgreSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any.

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of("UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

Python

GoogleSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# [START spanner_dml_last_statement]
# 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)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)

PostgreSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# 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)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)