Praktik terbaik Bahasa Manipulasi Data

Halaman ini menjelaskan praktik terbaik untuk menggunakan bahasa manipulasi data (DML) dan DML berpartisi untuk database dialek GoogleSQL dan database dialek PostgreSQL.

Menggunakan klausa WHERE untuk mengurangi cakupan kunci

Anda menjalankan pernyataan DML di dalam transaksi baca-tulis. Saat Spanner membaca data, Spanner akan memperoleh kunci baca bersama pada bagian terbatas dari rentang baris yang Anda baca. Secara khusus, Spanner hanya memperoleh kunci ini pada kolom yang Anda akses. Kunci dapat menyertakan data yang tidak memenuhi kondisi filter klausa WHERE.

Saat Spanner mengubah data menggunakan pernyataan DML, Spanner akan memperoleh kunci eksklusif pada data tertentu yang Anda ubah. Selain itu, Spanner memperoleh kunci bersama dengan cara yang sama seperti saat Anda membaca data. Jika permintaan Anda menyertakan rentang baris yang besar, atau seluruh tabel, kunci bersama dapat mencegah transaksi lain membuat progres secara paralel.

Untuk mengubah data seefisien mungkin, gunakan klausa WHERE yang memungkinkan Spanner hanya membaca baris yang diperlukan. Anda dapat mencapai tujuan ini dengan filter pada kunci utama, atau pada kunci indeks sekunder. Klausa WHERE membatasi cakupan kunci bersama dan memungkinkan Spanner memproses pembaruan dengan lebih efisien.

Misalnya, salah satu musisi di tabel Singers mengubah nama depannya, dan Anda perlu memperbarui nama di database. Anda dapat menjalankan pernyataan DML berikut, tetapi pernyataan ini memaksa Spanner untuk memindai seluruh tabel dan memperoleh kunci bersama yang mencakup seluruh tabel. Akibatnya, Spanner harus membaca lebih banyak data daripada yang diperlukan, dan transaksi serentak tidak dapat mengubah data secara paralel: 

-- 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";

Untuk membuat pembaruan lebih efisien, sertakan kolom SingerId dalam klausa WHERE. Kolom SingerId adalah satu-satunya kolom kunci utama untuk tabel Singers: 

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

Jika tidak ada indeks di FirstName atau LastName, Anda harus memindai seluruh tabel untuk menemukan penyanyi target. Jika Anda tidak ingin menambahkan indeks sekunder untuk membuat pembaruan lebih efisien, sertakan kolom SingerId dalam klausa WHERE.

Kolom SingerId adalah satu-satunya kolom kunci utama untuk tabel Singers. Untuk menemukannya, jalankan SELECT dalam transaksi baca-saja terpisah sebelum transaksi pembaruan: 


  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;

Menghindari penggunaan pernyataan DML dan mutasi dalam transaksi yang sama

Spanner mem-buffer penyisipan, pembaruan, dan penghapusan yang dilakukan menggunakan pernyataan DML di sisi server, dan hasilnya terlihat oleh pernyataan SQL dan DML berikutnya dalam transaksi yang sama. Perilaku ini berbeda dengan Mutation API, tempat Spanner mem-buffer mutasi di sisi klien dan mengirim mutasi sisi server sebagai bagian dari operasi commit. Akibatnya, mutasi dalam permintaan commit tidak terlihat oleh pernyataan SQL atau DML dalam transaksi yang sama.

Hindari penggunaan pernyataan DML dan mutasi dalam transaksi yang sama. Jika Anda menggunakan keduanya dalam transaksi yang sama, Anda harus memperhitungkan urutan eksekusi dalam kode library klien. Jika transaksi berisi pernyataan DML dan mutasi dalam permintaan yang sama, Spanner akan menjalankan pernyataan DML sebelum mutasi.

Untuk operasi yang hanya didukung menggunakan mutasi, Anda mungkin ingin menggabungkan pernyataan DML dan mutasi dalam transaksi yang sama—misalnya, insert_or_update.

Jika Anda menggunakan keduanya, buffer hanya akan menulis di akhir transaksi.

Menggunakan fungsi PENDING_COMMIT_TIMESTAMP untuk menulis stempel waktu commit

GoogleSQL

Anda menggunakan fungsi PENDING_COMMIT_TIMESTAMP untuk menulis stempel waktu commit dalam pernyataan DML. Spanner memilih stempel waktu commit saat transaksi di-commit.

PostgreSQL

Anda menggunakan fungsi SPANNER.PENDING_COMMIT_TIMESTAMP() untuk menulis stempel waktu commit dalam pernyataan DML. Spanner memilih stempel waktu commit saat transaksi di-commit.

DML berpartisi dan fungsi tanggal dan stempel waktu

DML berpartisi menggunakan satu atau beberapa transaksi yang mungkin berjalan dan di-commit pada waktu yang berbeda. Jika Anda menggunakan fungsi tanggal atau stempel waktu, baris yang diubah mungkin berisi nilai yang berbeda.

Meningkatkan latensi dengan DML batch

Untuk mengurangi latensi, gunakan DML batch untuk mengirim beberapa pernyataan DML ke Spanner dalam satu perjalanan pulang pergi klien-server.

DML batch dapat menerapkan pengoptimalan ke grup pernyataan dalam batch untuk memungkinkan pembaruan data yang lebih cepat dan efisien.

  • Menjalankan penulisan dengan satu permintaan

    Spanner secara otomatis mengoptimalkan grup pernyataan batch INSERT, UPDATE, atau DELETE yang berdekatan dan serupa yang memiliki nilai parameter berbeda, jika tidak melanggar dependensi data.

    Misalnya, pertimbangkan skenario saat Anda ingin menyisipkan kumpulan baris baru yang besar ke dalam tabel bernama Albums. Agar Spanner dapat mengoptimalkan semua pernyataan INSERT yang diperlukan menjadi satu tindakan sisi server yang efisien, mulailah dengan menulis pernyataan DML yang sesuai yang menggunakan parameter kueri SQL:

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

    Kemudian, kirim batch DML ke Spanner yang memanggil pernyataan ini berulang kali dan berdekatan, dengan pengulangan yang hanya berbeda dalam nilai yang Anda ikat ke tiga parameter kueri pernyataan. Spanner mengoptimalkan pernyataan DML yang secara struktural identik ini menjadi satu operasi sisi server sebelum menjalankannya.

  • Menjalankan penulisan secara paralel

    Spanner secara otomatis mengoptimalkan grup pernyataan DML yang berdekatan dengan menjalankan secara paralel jika hal tersebut tidak melanggar dependensi data. Pengoptimalan ini memberikan manfaat performa ke kumpulan pernyataan DML batch yang lebih luas karena dapat diterapkan ke campuran jenis pernyataan DML (INSERT, UPDATE, dan DELETE) dan ke pernyataan DML yang diberi parameter atau tidak diberi parameter.

    Misalnya, skema sampel kami memiliki tabel Singers, Albums, dan Accounts. Albums disisipkan dalam Singers dan menyimpan informasi tentang album untuk Singers. Grup pernyataan yang berdekatan berikut menulis baris baru ke beberapa tabel dan tidak memiliki dependensi data yang kompleks.

    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 mengoptimalkan grup pernyataan DML ini dengan menjalankan pernyataan secara paralel. Penulisan diterapkan sesuai urutan pernyataan dalam batch dan mempertahankan semantik DML batch jika pernyataan gagal selama eksekusi.

Mengaktifkan batching sisi klien di JDBC

Untuk aplikasi Java yang menggunakan driver JDBC yang didukung Spanner , Anda dapat mengurangi latensi dengan mengaktifkan batching DML sisi klien. Driver JDBC memiliki properti koneksi bernama auto_batch_dml yang, jika diaktifkan, akan mem-buffer pernyataan DML di klien dan mengirimkannya ke Spanner sebagai satu batch. Hal ini dapat mengurangi jumlah perjalanan pulang pergi ke server dan meningkatkan performa secara keseluruhan.

Secara default, auto_batch_dml ditetapkan ke false. Anda dapat mengaktifkannya dengan menetapkannya ke true di string koneksi JDBC.

Contoh:

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
}

Dengan properti koneksi ini diaktifkan, Spanner akan mengirim pernyataan DML yang di-buffer sebagai batch saat pernyataan non-DML dijalankan atau saat transaksi saat ini di-commit. Properti ini hanya berlaku untuk transaksi baca-tulis; pernyataan DML dalam mode autocommit dijalankan secara langsung.

Secara default, jumlah pembaruan untuk pernyataan DML yang di-buffer ditetapkan ke 1. Anda dapat mengubahnya dengan menetapkan variabel koneksi auto_batch_dml_update_count ke nilai yang berbeda. Untuk mengetahui informasi selengkapnya, lihat Properti koneksi yang didukung JDBC.

Menggunakan opsi last_statement untuk mengurangi latensi DML

Jika pernyataan terakhir dalam transaksi baca-tulis adalah pernyataan DML, Anda dapat menggunakan opsi kueri last_statement untuk mengurangi latensi. Opsi ini tersedia di executeSql dan executeStreamingSql API kueri.

Menggunakan opsi ini akan menunda beberapa langkah validasi, seperti validasi batasan unik, hingga transaksi di-commit. Saat menggunakan last_statement, operasi berikutnya, seperti pembacaan, kueri, dan DML, dalam transaksi yang sama akan ditolak. Opsi ini tidak kompatibel dengan mutasi. Jika Anda menyertakan mutasi dalam transaksi yang sama, Spanner akan menampilkan error.

Opsi last_statement didukung di library klien berikut:

  • Go dalam versi 1.77.0 atau yang lebih baru
  • Java dalam versi 2.27.0 atau yang lebih baru
  • Python dalam versi 3.53.0 atau yang lebih baru
  • PGAdapter dalam versi 0.45.0 atau yang lebih baru

Opsi ini didukung dan diaktifkan secara default saat menggunakan mode autocommit di driver berikut:

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)