資料操縱語言最佳做法

本頁面說明使用資料操縱語言 (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 不同。使用 Mutation API 時，Spanner 會將變異放在用戶端上的緩衝區，再做為修訂作業的一部分傳送給伺服器端。因此，相同交易中的 SQL 或 DML 陳述式無法看到修訂要求中的變異。

請勿在相同交易中使用 DML 陳述式和變異。如果您在同一項交易中同時使用這兩者，則必須在用戶端程式庫的程式碼中說明執行順序。如果交易在同一項要求中同時包含 DML 陳述式和變異，Spanner 會先執行 DML 陳述式，然後再執行變異。

對於只能使用變異執行的作業，您可能會在相同交易中結合 DML 陳述式和變異，例如 insert_or_update。

如果同時使用這兩項工具，則應等到交易結束時，才將寫入內容放在緩衝區。

使用 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 使用的一或多個交易可能會在不同時間執行和修訂。如果您使用日期或時間戳記函式，修改後的資料列可能會包含不同的值。

使用批次 DML 縮短延遲時間

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

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

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

  如果連續的類似 INSERT、UPDATE 或 DELETE 批次陳述式具有不同的參數值，且未違反資料依附元件，Spanner 會自動進行最佳化。

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

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

  接著，傳送 DML 批次給 Spanner，重複且連續地叫用這個陳述式，重複次數只會因您繫結至陳述式三個查詢參數的值而有所不同。Spanner 會先將這些結構相同的 DML 陳述式最佳化為單一伺服器端作業，再執行該作業。

• 同時執行寫入作業

  如果並未違反資料依附元件，Spanner 會平行執行連續的 DML 陳述式群組，藉此自動提升效能。這項最佳化功能適用於各種批次 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
}

啟用這項連線屬性後，當執行非 DML 陳述式或提交目前交易時，Spanner 會以批次形式傳送緩衝的 DML 陳述式。這項屬性僅適用於讀取/寫入交易； 系統會直接執行自動提交模式中的 DML 陳述式。

根據預設，緩衝 DML 陳述式的更新計數會設為 1。如要變更這項設定，請將 auto_batch_dml_update_count 連線變數設為其他值。詳情請參閱「JDBC 支援的連線屬性」。