שיטות מומלצות לשימוש בשפת טיפול בנתונים (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 כדי לכתוב חותמות זמן של ביצוע commit

GoogleSQL

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

PostgreSQL

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

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

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

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

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

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

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

    ‫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 או כשמתבצעת התחייבות של העסקה הנוכחית. המאפיין הזה חל רק על טרנזקציות של קריאה וכתיבה; הוראות 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 ואילך

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

המשך

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)