資料操縱語言最佳做法

本頁面說明使用資料操縱語言 (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 函式寫入修訂時間戳記

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

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

使用批次 DML 改善延遲時間

如要減少延遲時間，請使用 批次 DML，在單一用戶端-伺服器來回通訊中傳送多個 DML 陳述式至 Spanner。

批次 DML 可將最佳化方式套用至批次中的陳述式群組，以便更快速且有效率地更新資料。

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

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

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

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

  接著，請傳送 DML 批次給 Spanner，以便重複且連續叫用此陳述式，而重複的次數只會影響您繫結至陳述式三個查詢參數的值。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 語意。