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

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

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

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

GoogleSQL

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

PostgreSQL

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

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

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

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

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

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

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

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

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

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

המשך

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)