DML 권장사항

이 페이지에서는 GoogleSQL 언어 데이터베이스 및 PostgreSQL 언어 데이터베이스에 DML 및 Partitioned 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";

업데이트를 더 효율적으로 수행하려면 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 문에서 결과를 확인할 수 있습니다. 이 동작은 Spanner가 클라이언트 측에서 변형을 버퍼링하고 커밋 작업의 일부로 변형을 서버 측에 보내는 변형 API와 다릅니다. 그 결과 커밋 요청의 변형은 동일 트랜잭션 내의 SQL 또는 DML 문에 보이지 않습니다.

같은 트랜잭션에서 DML 문과 변형을 모두 사용하지 마세요. 동일한 트랜잭션에서 둘 다 사용하는 경우 클라이언트 라이브러리 코드에서 실행 순서를 고려해야 합니다. 트랜잭션이 동일한 요청에 DML 문과 변형을 모두 포함하는 경우, Spanner는 변형 전에 DML 문을 실행합니다.

변형을 사용해서만 지원되는 작업의 경우 같은 트랜잭션에서 DML 문과 변형을 결합하는 것이 좋습니다.(예: insert_or_update).

둘 다 사용하는 경우 버퍼는 트랜잭션의 맨 마지막에만 기록합니다.

PENDING_COMMIT_TIMESTAMP 함수를 사용하여 커밋 타임스탬프 작성

GoogleSQL

PENDING_COMMIT_TIMESTAMP 함수를 사용하여 DML 문에서 커밋 타임스탬프를 씁니다. Spanner는 트랜잭션이 커밋될 때 커밋 타임스탬프를 선택합니다.

PostgreSQL

SPANNER.PENDING_COMMIT_TIMESTAMP() 함수를 사용하여 DML 문에서 커밋 타임스탬프를 씁니다. Spanner는 트랜잭션이 커밋될 때 커밋 타임스탬프를 선택합니다.

Partitioned DML과 날짜 및 타임스탬프 함수

Partitioned DML은 실행 및 커밋 시간이 서로 다른 하나 이상의 트랜잭션을 사용합니다. 날짜 또는 타임스탬프 함수를 사용하면 수정된 행에 서로 다른 값이 포함될 수 있습니다.

일괄 DML을 사용하여 지연 시간 개선

지연 시간을 줄이려면 일괄 DML을 사용하여 단일 클라이언트-서버 왕복 중에 여러 DML 문을 Spanner로 전송합니다.

일괄 DML은 배치 내의 문 그룹에 최적화를 적용하여 더 빠르고 효율적인 데이터 업데이트를 지원할 수 있습니다.

  • 단일 요청으로 쓰기 실행

    Spanner는 파라미터 값이 서로 다른 유사한 INSERT, UPDATE 또는 DELETE 일괄 처리 문의 연속된 그룹(데이터 종속 항목을 위반하지 않는 경우)을 자동으로 최적화합니다.

    예를 들어 Albums라는 테이블에 새 행 집합을 삽입하려는 경우를 가정해 보겠습니다. Spanner에서 필요한 모든 INSERT 문을 하나의 효율적인 서버 측 작업으로 최적화하려면 먼저 SQL 쿼리 파라미터를 사용하는 적절한 DML 문을 작성합니다.

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

    그런 다음 이 문을 반복하여 연속으로 호출하는 Spanner DML 배치를 전송합니다. 반복은 문의 3개 쿼리 매개변수에 바인딩한 값에서만 다릅니다. 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 문 그룹을 최적화합니다. 쓰기는 배치의 문 순서대로 적용되며 실행 중에 문이 실패하면 일괄 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
}

이 연결 속성이 사용 설정되면 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 이상
  • 버전 0.45.0 이상의 PGAdapter

다음 드라이버에서 자동 커밋 모드를 사용하는 경우 지원되며 기본적으로 사용 설정됩니다.

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
}

자바

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)