Praktik terbaik Bahasa Manipulasi Data

Halaman ini menjelaskan praktik terbaik untuk menggunakan bahasa pengolahan data (DML) dan DML yang dipartisi 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 membaca data, Spanner memperoleh kunci baca bersama pada bagian terbatas dari rentang baris yang Anda baca. Secara khusus, Cloud Spanner memperoleh kunci ini hanya pada kolom yang Anda akses. Kunci dapat menyertakan data yang tidak memenuhi kondisi filter klausa WHERE.

Saat mengubah data menggunakan pernyataan DML, Spanner memperoleh kunci eksklusif pada data tertentu yang Anda ubah. Selain itu, metode ini 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 berjalan secara paralel.

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

Misalnya, salah satu musisi dalam tabel Singers mengubah nama depannya, dan Anda perlu memperbarui nama tersebut 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";

Agar update 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 update 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 hanya baca yang terpisah sebelum transaksi update:

  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 menyangga penyisipan, pembaruan, dan penghapusan yang dilakukan menggunakan pernyataan DML di sisi server, dan hasilnya dapat dilihat oleh pernyataan SQL dan DML berikutnya dalam transaksi yang sama. Perilaku ini berbeda dengan Mutation API, di mana Spanner menyangga mutasi di sisi klien dan mengirimkan mutasi di sisi server sebagai bagian dari operasi commit. Akibatnya, mutasi dalam permintaan commit tidak terlihat oleh pernyataan SQL atau pernyataan 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 Anda. Jika transaksi berisi pernyataan DML dan mutasi dalam permintaan yang sama, Spanner akan mengeksekusi pernyataan DML sebelum mutasi.

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.

Catatan: Setelah Anda memanggil fungsi PENDING_COMMIT_TIMESTAMP, tabel dan indeks turunan apa pun tidak dapat dibaca oleh pernyataan SQL mendatang dalam transaksi. Oleh karena itu, aliran data perubahan tidak dapat mengekstrak nilai sebelumnya untuk kolom yang memiliki stempel waktu commit yang tertunda, jika kolom tersebut diubah lagi nanti dalam transaksi yang sama. Anda harus menulis stempel waktu commit sebagai pernyataan terakhir dalam transaksi untuk mencegah kemungkinan mencoba membaca tabel. Jika Anda mencoba membaca tabel, Spanner akan menghasilkan error.

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.

Catatan: Setelah Anda memanggil fungsi SPANNER.PENDING_COMMIT_TIMESTAMP(), tabel dan indeks turunan apa pun tidak dapat dibaca oleh pernyataan SQL berikutnya dalam transaksi. Anda harus menulis stempel waktu commit sebagai pernyataan terakhir dalam transaksi untuk mencegah kemungkinan mencoba membaca tabel. Jika Anda mencoba membaca tabel, Spanner akan menampilkan error.

DML yang dipartisi serta fungsi tanggal dan stempel waktu

DML yang dipartisi menggunakan satu atau beberapa transaksi yang mungkin berjalan dan di-commit pada waktu yang berbeda. Jika Anda menggunakan fungsi date atau timestamp, 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 otomatis mengoptimalkan grup yang berdekatan dari pernyataan batch INSERT, UPDATE, atau DELETE serupa yang memiliki nilai parameter berbeda, jika tidak melanggar dependensi data.

  Misalnya, pertimbangkan skenario saat Anda ingin menyisipkan sekumpulan besar baris baru 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 berurutan, dengan pengulangan yang hanya berbeda dalam nilai yang Anda ikat ke tiga parameter kueri pernyataan. Spanner mengoptimalkan pernyataan DML yang identik secara struktural ini menjadi satu operasi sisi server sebelum mengeksekusinya.

• Menjalankan penulisan secara paralel

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

  Misalnya, skema contoh 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 pengelompokan sisi klien di JDBC

Untuk aplikasi Java yang menggunakan driver JDBC yang didukung Spanner, Anda dapat mengurangi latensi dengan mengaktifkan pengelompokan DML sisi klien. Driver JDBC memiliki properti koneksi yang disebut auto_batch_dml yang, jika diaktifkan, akan menyangga pernyataan DML pada klien dan mengirimkannya ke Spanner sebagai satu batch. Hal ini dapat mengurangi jumlah perjalanan bolak-balik ke server dan meningkatkan performa secara keseluruhan.

Secara default, auto_batch_dml disetel 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 mengaktifkan properti koneksi ini, Spanner mengirimkan 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 update 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 API kueri executeSql dan executeStreamingSql.

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 di versi 1.77.0 atau yang lebih baru
• Java di versi 2.27.0 atau yang lebih baru
• Python versi 3.53.0 atau yang lebih baru
• PGAdapter dalam versi 0.45.0 atau yang lebih baru

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

• Driver JDBC dalam versi 6.87.0 atau yang lebih baru
• Driver database/sql Go di versi 1.11.2 atau yang lebih baru

• Driver dbapi Python dalam versi 3.53.0 atau yang lebih baru

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)