資料操縱語言最佳做法

本頁面說明使用資料操縱語言 (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";

如要提高更新效率，請在 WHERE 子句中加入 SingerId 欄。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 沒有索引，您就必須掃描整個資料表，才能找到目標歌手。如果不想新增次要索引來提高更新效率，請在 WHERE 子句中加入 SingerId 資料欄。

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 陳述式，再執行異動。

如果兩者都使用，緩衝區只會在交易結束時寫入。

使用 PENDING_COMMIT_TIMESTAMP 函式寫入修訂版本時間戳記

GoogleSQL

您可以使用 PENDING_COMMIT_TIMESTAMP 函式，在 DML 陳述式中寫入修訂版本時間戳記。Spanner 會在交易提交時選取提交時間戳記。

注意：呼叫 PENDING_COMMIT_TIMESTAMP 函式後，交易中任何後續的 SQL 陳述式都無法讀取資料表和任何衍生索引。因此，如果資料欄在同一筆交易中再次遭到修改，變更串流就無法擷取該資料欄的先前值。您必須將修訂時間戳記寫入為交易中的最後一個陳述式，以免嘗試讀取資料表。如果嘗試讀取資料表，Spanner 會產生錯誤。

PostgreSQL

您可以使用 SPANNER.PENDING_COMMIT_TIMESTAMP() 函式，在 DML 陳述式中寫入修訂版本時間戳記。Spanner 會在交易提交時選取提交時間戳記。

注意：呼叫 SPANNER.PENDING_COMMIT_TIMESTAMP() 函式後，資料表和任何衍生索引都無法由交易中的後續 SQL 陳述式讀取。您必須將修訂版本時間戳記寫入為交易中的最後一個陳述式，以免嘗試讀取資料表。如果嘗試讀取資料表，Spanner 會傳回錯誤。

分區 DML 和日期與時間戳記函式

分區 DML 使用的一或多個交易可能會在不同時間執行和修訂。如果使用 date 或 timestamp 函式，修改後的資料列可能包含不同的值。

使用批次 DML 縮短延遲時間

如要縮短延遲時間，請使用批次 DML，在單一用戶端與伺服器來回中，將多個 DML 陳述式傳送至 Spanner。

批次 DML 可對批次中的陳述式群組套用最佳化，加快資料更新速度並提高效率。

• 透過單一要求執行寫入作業

  如果類似的 INSERT、UPDATE 或 DELETE 批次陳述式具有不同的參數值，且不違反資料依賴，Spanner 會自動最佳化這些陳述式的連續群組。

  舉例來說，假設您想在名為 Albums 的資料表中插入大量新資料列。如要讓 Spanner 將所有必要的 INSERT 陳述式最佳化，整合成單一高效的伺服器端動作，請先編寫適當的 DML 陳述式，並使用 SQL 查詢參數：

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

  接著，將 DML 批次傳送至 Spanner，重複且連續叫用這個陳述式，重複作業的差異僅在於繫結至陳述式三個查詢參數的值。Spanner 會將這些結構相同的 DML 陳述式最佳化，整合成單一伺服器端作業，然後再執行。

• 平行執行寫入作業

  Spanner 會自動最佳化連續的 DML 陳述式群組，在不違反資料依賴性的情況下平行執行。這項最佳化功能可套用至混合的 DML 陳述式類型 (INSERT、UPDATE 和 DELETE)，以及參數化或非參數化的 DML 陳述式，因此可為更多批次 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 應用程式使用 Spanner 支援的 JDBC 驅動程式，您可以啟用用戶端 DML 批次處理，降低延遲時間。JDBC 驅動程式有名為 auto_batch_dml 的連線屬性，啟用後會緩衝處理用戶端上的 DML 陳述式，並以單一批次傳送至 Spanner。這可以減少伺服器的來回次數，並提升整體效能。

根據預設，auto_batch_dml 會設為 false。如要啟用，請在 JDBC 連線字串中將其設為 true。

例如：

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 查詢選項來縮短延遲時間。executeSql 和 executeStreamingSql 查詢 API 提供這個選項。

使用這個選項會延遲部分驗證步驟 (例如不重複限制驗證)，直到交易提交為止。使用 last_statement 時，系統會拒絕同一交易中的後續作業，例如讀取、查詢和 DML。這個選項與變動不相容。如果您在同一筆交易中加入異動，Spanner 會傳回錯誤。

下列用戶端程式庫支援 last_statement 選項：

• Go 1.77.0 以上版本
• Java 2.27.0 以上版本
• Python 3.53.0 以上版本
• PGAdapter 0.45.0 以上版本

在下列驅動程式中使用自動修訂模式時，系統預設會支援並啟用這項功能：

• 6.87.0 以上版本的 JDBC 驅動程式
• 1.11.2 以上版本的 Go 資料庫/SQL 驅動程式

• 3.53.0 以上版本的 Python dbapi 驅動程式

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)