データ操作言語のベスト プラクティス

このページでは、GoogleSQL 言語データベースと PostgreSQL 言語データベースでデータ操作言語（DML）とパーティション化 DML を使用する際のベスト プラクティスについて説明します。

WHERE 句を使用してロックのスコープを狭める

DML ステートメントを読み取り / 書き込みトランザクション内で実行します。Spanner がデータを読み取る際に、読み取る行範囲の一部に対する共有読み取りロックを取得します。具体的には、アクセスする列に対してのみロックを取得します。ロックには、WHERE 句のフィルタ条件と一致しないデータを含めることが可能です。

Spanner が DML ステートメントを使用してデータを変更する際に、変更する特定のデータに対して排他ロックを取得します。また、データの読み取り時と同様に共有ロックを取得します。リクエストに大きな行範囲やテーブル全体が含まれている場合、共有ロックによって他のトランザクションが並行して完了しない可能性があります。

データをできるだけ効率的に変更するには、Spanner で必要な行のみを読み取る WHERE 句を使用します。主キーまたはセカンダリ インデックスのキーに対するフィルタを使用して、この目標を達成できます。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 にインデックスがない場合は、テーブル全体をスキャンしてターゲットであるミュージシャンを見つける必要があります。更新の効率が上がるようセカンダリ インデックスを追加しない場合は、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 は、クライアント サイドでミューテーションをバッファに保存し、commit オペレーションでサーバーサイドにミューテーションを送信します。このため、commit リクエストのミューテーションは、同じトランザクション内の SQL ステートメントまたは DML ステートメントで参照できません。

同じトランザクション内で DML ステートメントとミューテーションの両方を使用しない同じトランザクションで両方を使用する場合は、クライアント ライブラリ コードで実行順序を考慮する必要があります。同じリクエストでトランザクションに DML ステートメントとミューテーションの両方が含まれている場合、Spanner はミューテーションの前に DML ステートメントを実行します。

ミューテーションの使用のみがサポートされるオペレーションの場合は、同じトランザクションで DML ステートメントとミューテーションを組み合わせる必要があります（insert_or_update など）。

両方を使用すると、バッファへの書き込みはトランザクションの最後でのみ行われます。

commit タイムスタンプを書き込むために PENDING_COMMIT_TIMESTAMP 関数を使用する

GoogleSQL

DML ステートメントで commit タイムスタンプを作成するには、PENDING_COMMIT_TIMESTAMP 関数を使用します。トランザクションが commit されると、Spanner が commit タイムスタンプを選択します。

PostgreSQL

DML ステートメントで commit タイムスタンプを作成するには、SPANNER.PENDING_COMMIT_TIMESTAMP() 関数を使用します。トランザクションが commit されると、Spanner が commit タイムスタンプを選択します。

パーティション化 DML と日付 / タイムスタンプ関数

パーティション化 DML は 1 つ以上のトランザクションを使用します。これらのトランザクションは異なる時間に実行され、commit される可能性があります。日付関数またはタイムスタンプ関数を使用すると、変更した行に異なる値が格納される可能性があります。

バッチ DML を使用してレイテンシを改善する

レイテンシを短縮するには、バッチ DML を使用して、単一のクライアント サーバー ラウンド トリップ内で複数の DML ステートメントを Spanner に送信します。

バッチ DML では、バッチ内のステートメントのグループに最適化を適用して、データ更新をより迅速かつ効率的に行うことができます。

  • 1 つのリクエストで書き込みを実行する

    Spanner は、データ依存関係に違反しない限り、パラメータ値が異なる類似の INSERT、UPDATE、または DELETE バッチ処理ステートメントの連続するグループを自動的に最適化します。

    たとえば、多数の新しい行のセットを Albums というテーブルに挿入するシナリオを考えてみます。Spanner が必要なすべての INSERT ステートメントを単一の効率的なサーバーサイドのアクションに最適化できるようにするには、まず SQL クエリ パラメータを使用する適切な DML ステートメントを作成します。

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

    次に、このステートメントを繰り返し連続して呼び出す DML バッチを送信します。繰り返しは、ステートメントの 3 つのクエリ パラメータにバインドする値でのみ異なります。 Spanner は、これらの構造的に同じ DML ステートメントを、実行前に単一のサーバーサイドのオペレーションに最適化します。

  • 書き込みを並行して実行する

    Spanner は、データ依存関係に違反しなければ、DML ステートメントの連続するグループを自動的に並行して最適化します。この最適化により、DML ステートメントの種類（INSERT、UPDATE、DELETE）の組み合わせと、パラメータ化された DML ステートメントとパラメータ化されていない 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 でクライアントサイドのバッチ処理を有効にする

Spanner でサポートされている JDBC ドライバを使用する Java アプリケーションでは、クライアントサイドの 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 以外のステートメントが実行されたとき、または現在のトランザクションがコミットされたときに、バッファリングされた DML ステートメントがバッチとして送信されます。このプロパティは読み取り / 書き込みトランザクションにのみ適用されます。自動 commit モードの DML ステートメントは直接実行されます。

デフォルトでは、バッファリングされた DML ステートメントの更新数は 1 に設定されています。これは、auto_batch_dml_update_count 接続変数を別の値に設定することで変更できます。詳細については、JDBC でサポートされている接続プロパティをご覧ください。

last_statement オプションを使用して DML レイテンシを短縮する

読み取り / 書き込みトランザクションの最後のステートメントが DML ステートメントの場合、last_statement クエリ オプションを使用してレイテンシを短縮できます。このオプションは、executeSql クエリ API と executeStreamingSql クエリ API で使用できます。

このオプションを使用すると、一意制約の検証などの一部の検証ステップが、トランザクションが commit されるまで延期されます。last_statement を使用すると、同じトランザクション内の後続のオペレーション（読み取り、クエリ、DML など）は拒否されます。このオプションはミューテーションと互換性がありません。同じトランザクションにミューテーションを含めると、Spanner はエラーを返します。

last_statement オプションは、次のクライアント ライブラリでサポートされています。

  • Go バージョン 1.77.0 以降
  • Java バージョン 2.27.0 以降
  • Python バージョン 3.53.0 以降
  • バージョン 0.45.0 以降の PGAdapter

次のドライバで自動 commit モードを使用する場合、この機能はサポートされ、デフォルトで有効になっています。

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)