Praktik terbaik SQL

Seperti yang dijelaskan dalam Rencana eksekusi kueri, compiler SQL mengubah pernyataan SQL menjadi rencana eksekusi kueri, yang digunakan untuk mendapatkan hasil kueri. Halaman ini menjelaskan praktik terbaik untuk menyusun pernyataan SQL guna membantu Spanner menemukan rencana eksekusi yang efisien. Lihat Panduan [Penyesuaian Kueri][query_tuning] untuk mendapatkan bantuan dalam meningkatkan performa kueri.

Contoh pernyataan SQL yang ditampilkan di halaman ini menggunakan contoh skema berikut:

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Untuk referensi SQL lengkap, lihat Sintaksis pernyataan, Fungsi dan operator, serta Struktur dan sintaksis leksikal.

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Untuk mengetahui informasi selengkapnya, lihat Bahasa PostgreSQL di Spanner.

Menggunakan parameter kueri

Spanner mendukung parameter kueri untuk meningkatkan performa dan membantu mencegah injeksi SQL saat kueri dibuat menggunakan input pengguna. Anda dapat menggunakan parameter kueri sebagai pengganti ekspresi arbitrer, tetapi bukan sebagai pengganti ID, nama kolom, nama tabel, atau bagian lain dari kueri.

Parameter dapat muncul di mana saja yang diharapkan nilai literal. Nama parameter yang sama dapat digunakan lebih dari sekali dalam satu pernyataan SQL.

Singkatnya, parameter kueri mendukung eksekusi kueri dengan cara berikut:

• Rencana yang telah dioptimalkan sebelumnya: Kueri yang menggunakan parameter dapat dieksekusi lebih cepat pada setiap pemanggilan karena parameterisasi memudahkan Spanner untuk meng-cache rencana eksekusi.
• Komposisi kueri yang disederhanakan: Anda tidak perlu meng-escape nilai string saat memberikannya dalam parameter kueri. Parameter kueri juga mengurangi risiko error sintaksis.
• Keamanan: Parameter kueri membuat kueri Anda lebih aman dengan melindungi Anda dari berbagai serangan injeksi SQL. Perlindungan ini sangat penting untuk kueri yang Anda buat dari input pengguna.

Untuk meningkatkan performa, Spanner mengonversi nilai literal yang di-hardcode dalam SQL menjadi parameter jika rencana eksekusi kueri yang optimal tidak bergantung pada nilai literal tertentu. Hal ini memungkinkan Spanner mengurangi latensi dan biaya CPU dengan meng-cache dan menggunakan kembali rencana kueri. Namun, hal ini tidak menawarkan perlindungan injeksi SQL yang sama dan manfaat komposisi kueri yang lebih sederhana. Menggunakan parameter kueri dalam kode aplikasi adalah praktik terbaik.

Catatan: Kueri yang rencana kueri optimalnya mungkin bergantung pada nilai literal tidak otomatis dikonversi ke SQL berparameter. Untuk memaksa konversi literal menjadi parameter, gunakan petunjuk auto_parameter.

Memahami cara Spanner menjalankan kueri

Catatan: Spanner Studio (sebelumnya diberi label Query di konsol Google Cloud ) mendukung operasi SQL, DML, dan DDL dalam satu editor. Untuk mengetahui informasi selengkapnya, lihat Mengelola data menggunakan konsol Google Cloud .

Spanner memungkinkan Anda mengkueri database menggunakan pernyataan SQL deklaratif yang menentukan data yang ingin Anda ambil. Jika Anda ingin memahami cara Spanner mendapatkan hasil, periksa rencana eksekusi untuk kueri. Rencana eksekusi kueri menampilkan biaya komputasi yang terkait dengan setiap langkah kueri. Dengan menggunakan biaya tersebut, Anda dapat men-debug masalah performa kueri dan mengoptimalkan kueri Anda. Untuk mempelajari lebih lanjut, lihat Paket eksekusi kueri.

Anda dapat mengambil rencana eksekusi kueri melalui konsol Google Cloud atau library klien.

Untuk mendapatkan rencana eksekusi kueri untuk kueri tertentu menggunakan konsolGoogle Cloud , ikuti langkah-langkah berikut:

1. Buka halaman instance Spanner.

   Buka instance Spanner

2. Pilih nama instance Spanner dan database yang ingin Anda buat kuerinya.

3. Klik Spanner Studio di panel navigasi kiri.

4. Ketik kueri di kolom teks, lalu klik Run query.

5. Klik Penjelasan
   . Konsol Google Cloud menampilkan rencana eksekusi visual untuk kueri Anda.

   

Untuk mengetahui informasi selengkapnya tentang cara memahami rencana visual dan menggunakannya untuk men-debug kueri, lihat Menyesuaikan kueri menggunakan visualizer rencana kueri.

Anda juga dapat melihat contoh rencana kueri historis dan membandingkan performa kueri dari waktu ke waktu untuk kueri tertentu. Untuk mempelajari lebih lanjut, lihat Contoh rencana kueri.

Menggunakan indeks sekunder

Seperti database relasional lainnya, Spanner menawarkan indeks sekunder, yang dapat Anda gunakan untuk mengambil data menggunakan pernyataan SQL atau antarmuka baca Spanner. Dengan menggunakan indeks sekunder dalam kueri SQL, Anda dapat menentukan cara Spanner mendapatkan hasil. Menentukan indeks sekunder dapat mempercepat eksekusi kueri.

Misalnya, Anda ingin mengambil ID semua penyanyi dengan nama belakang tertentu. Salah satu cara untuk menulis kueri SQL tersebut adalah:

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Kueri ini akan menampilkan hasil yang Anda harapkan, tetapi mungkin memerlukan waktu lama untuk menampilkan hasilnya. Waktunya akan bergantung pada jumlah baris dalam tabel Singers dan jumlah baris yang memenuhi predikat WHERE s.LastName = 'Smith'. Jika tidak ada indeks sekunder yang berisi kolom LastName untuk dibaca, rencana kueri akan membaca seluruh tabel Singers untuk menemukan baris yang cocok dengan predikat. Membaca seluruh tabel disebut pemindaian tabel lengkap. Pemindaian tabel lengkap adalah cara yang mahal untuk mendapatkan hasil jika tabel hanya berisi sebagian kecil Singers dengan nama belakang tersebut.

Anda dapat meningkatkan performa kueri ini dengan menentukan indeks sekunder pada kolom nama belakang:

CREATE INDEX SingersByLastName ON Singers (LastName);

Karena indeks sekunder SingersByLastName berisi kolom tabel yang diindeks LastName dan kolom kunci utama SingerId, Spanner dapat mengambil semua data dari tabel indeks yang jauh lebih kecil, bukan memindai tabel Singers lengkap.

Dalam skenario ini, Spanner otomatis menggunakan indeks sekunder SingersByLastName saat menjalankan kueri (selama tiga hari telah berlalu sejak pembuatan database; lihat Catatan tentang database baru). Namun, sebaiknya beri tahu Spanner secara eksplisit untuk menggunakan indeks tersebut dengan menentukan arahan indeks dalam klausa FROM:

GoogleSQL

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Jika Anda menggunakan skema bernama, gunakan sintaksis berikut untuk klausa FROM:

GoogleSQL

FROM NAMED_SCHEMA_NAME.TABLE_NAME@{FORCE_INDEX="NAMED_SCHEMA_NAME.TABLE_INDEX_NAME"}

PostgreSQL

FROM NAMED_SCHEMA_NAME.TABLE_NAME /*@ FORCE_INDEX = TABLE_INDEX_NAME */

Sekarang, misalkan Anda juga ingin mengambil nama depan penyanyi selain ID. Meskipun kolom FirstName tidak terdapat dalam indeks, Anda tetap harus menentukan perintah indeks seperti sebelumnya:

GoogleSQL

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId, s.FirstName
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Anda tetap mendapatkan manfaat performa dari penggunaan indeks karena Spanner tidak perlu melakukan pemindaian tabel lengkap saat menjalankan rencana kueri. Sebagai gantinya, kueri ini memilih subset baris yang memenuhi predikat dari indeks SingersByLastName, lalu melakukan pencarian dari tabel dasar Singers untuk mengambil nama depan hanya untuk subset baris tersebut.

Jika Anda ingin Spanner tidak perlu mengambil baris apa pun dari tabel dasar sama sekali, Anda dapat menyimpan salinan kolom FirstName dalam indeks itu sendiri:

GoogleSQL

CREATE INDEX SingersByLastName ON Singers (LastName) STORING (FirstName);

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

Menggunakan klausa STORING (untuk dialek GoogleSQL) atau klausa INCLUDE (untuk dialek PostgreSQL) seperti ini memerlukan penyimpanan tambahan, tetapi memberikan keuntungan berikut:

• Kueri SQL yang menggunakan indeks dan memilih kolom yang disimpan dalam klausa STORING atau INCLUDE tidak memerlukan join tambahan ke tabel dasar.
• Panggilan baca yang menggunakan indeks dapat membaca kolom yang disimpan dalam klausa STORING atau INCLUDE.

Contoh sebelumnya menggambarkan bagaimana indeks sekunder dapat mempercepat kueri saat baris yang dipilih oleh klausa WHERE kueri dapat diidentifikasi dengan cepat menggunakan indeks sekunder.

Skenario lain saat indeks sekunder dapat menawarkan manfaat performa adalah untuk kueri tertentu yang menampilkan hasil yang diurutkan. Misalnya, Anda ingin mengambil semua judul album dan tanggal rilisnya dalam urutan menaik berdasarkan tanggal rilis dan urutan menurun berdasarkan judul album. Anda dapat menulis kueri SQL sebagai berikut:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Tanpa indeks sekunder, kueri ini memerlukan langkah pengurutan yang berpotensi mahal dalam rencana eksekusi. Anda dapat mempercepat eksekusi kueri dengan menentukan indeks sekunder ini:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

Kemudian, tulis ulang kueri untuk menggunakan indeks sekunder:

GoogleSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Definisi kueri dan indeks ini memenuhi kedua kriteria berikut:

• Untuk menghapus langkah pengurutan, pastikan daftar kolom dalam klausa ORDER BY adalah awalan dari daftar kunci indeks.
• Untuk menghindari penggabungan kembali dari tabel dasar untuk mengambil kolom yang hilang, pastikan indeks mencakup semua kolom dalam tabel yang digunakan kueri.

Meskipun indeks sekunder dapat mempercepat kueri umum, menambahkan indeks sekunder dapat menambah latensi pada operasi commit Anda, karena setiap indeks sekunder biasanya memerlukan keterlibatan node tambahan di setiap commit. Untuk sebagian besar workload, memiliki beberapa indeks sekunder tidak masalah. Namun, Anda harus mempertimbangkan apakah Anda lebih mementingkan latensi baca atau tulis, dan mempertimbangkan operasi mana yang paling penting untuk workload Anda. Lakukan tolok ukur pada workload Anda untuk memastikan bahwa workload tersebut berperforma sesuai harapan Anda.

Untuk referensi lengkap tentang indeks sekunder, lihat Indeks sekunder.

Mengoptimalkan pencarian kunci rentang

Penggunaan umum kueri SQL adalah untuk membaca beberapa baris dari Spanner berdasarkan daftar kunci yang diketahui.

Praktik terbaik berikut membantu Anda menulis kueri yang efisien saat mengambil data berdasarkan rentang kunci:

• Jika daftar kunci jarang dan tidak berdekatan, gunakan parameter kueri dan UNNEST untuk membuat kueri Anda.

  Misalnya, jika daftar kunci Anda adalah {1, 5, 1000}, tulis kueri seperti ini:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST (@KeyList)

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST ($1)

  Catatan:

  • Operator UNNEST array meratakan array input menjadi baris elemen.

  • Parameter kueri, yaitu @KeyList untuk GoogleSQL dan $1 untuk PostgreSQL, dapat mempercepat kueri Anda seperti yang dibahas dalam praktik terbaik sebelumnya.

  • Sebaiknya gunakan UNNEST dengan satu parameter, bukan OR dengan beberapa parameter.

• Jika daftar kunci berdekatan dan berada dalam rentang, tentukan batas bawah dan atas rentang kunci dalam klausa WHERE.

  Misalnya, jika daftar kunci Anda adalah {1,2,3,4,5}, buat kueri sebagai berikut:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN @min AND @max

  Catatan: Di sini, @min dan @max adalah parameter kueri yang terikat ke nilai 1 dan 5, masing-masing.

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN $1 AND $2

  Catatan: Di sini, $1 dan $2 adalah parameter kueri yang terikat ke nilai 1 dan 5, masing-masing.

  Kueri ini hanya lebih efisien jika kunci dalam rentang kunci berdekatan. Dengan kata lain, jika daftar kunci Anda adalah {1, 5, 1000}, jangan tentukan batas bawah dan atas seperti dalam kueri sebelumnya karena kueri yang dihasilkan akan memindai setiap nilai antara 1 dan 1000.

Mengoptimalkan penggabungan

Operasi join bisa mahal karena dapat meningkatkan jumlah baris yang perlu dipindai oleh kueri Anda secara signifikan, sehingga menghasilkan kueri yang lebih lambat. Selain teknik yang biasa Anda gunakan di database relasional lain untuk mengoptimalkan kueri join, berikut beberapa praktik terbaik untuk JOIN yang lebih efisien saat menggunakan Spanner SQL:

• Jika memungkinkan, gabungkan data dalam tabel yang disisipkan berdasarkan kunci utama. Contoh:

SELECT s.FirstName, a.ReleaseDate
FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

The rows in the interleaved table `Albums` are physically
stored in the same splits as the parent row in `Singers`, as discussed in
[Schema and Data Model](/spanner/docs/schema-and-data-model). Therefore, joins can be
completed locally without sending lots of data across the network.

• Gunakan perintah join jika Anda ingin menerapkan urutan join. Misalnya:

  GoogleSQL

  SELECT *
  FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  PostgreSQL

  SELECT *
  FROM Singers AS s JOIN/*@ FORCE_JOIN_ORDER=TRUE */ Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  Perintah join FORCE_JOIN_ORDER memberi tahu Spanner untuk menggunakan urutan join yang ditentukan dalam kueri (yaitu, Singers JOIN Albums, bukan Albums JOIN Singers). Hasil yang ditampilkan sama, terlepas dari urutan yang dipilih Spanner. Namun, Anda mungkin ingin menggunakan perintah join ini jika melihat dalam rencana kueri bahwa Spanner telah mengubah urutan join dan menyebabkan konsekuensi yang tidak diinginkan, seperti hasil perantara yang lebih besar, atau telah melewatkan peluang untuk mencari baris.

• Gunakan perintah join untuk memilih implementasi join. Saat Anda menggunakan SQL untuk mengkueri beberapa tabel, Spanner akan otomatis menggunakan metode gabungan yang cenderung membuat kueri lebih efisien. Namun, Google menyarankan Anda untuk menguji dengan berbagai algoritma join. Memilih algoritma join yang tepat dapat meningkatkan latensi, konsumsi memori, atau keduanya. Kueri ini menunjukkan sintaksis untuk menggunakan perintah JOIN dengan petunjuk JOIN_METHOD untuk memilih HASH JOIN:

  GoogleSQL

  SELECT *
  FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
  ON a.SingerId = a.SingerId

  PostgreSQL

  SELECT *
  FROM Singers s JOIN/*@ JOIN_METHOD=HASH_JOIN */ Albums AS a
  ON a.SingerId = a.SingerId

• Jika Anda menggunakan HASH JOIN atau APPLY JOIN dan jika Anda memiliki klausa WHERE yang sangat selektif di satu sisi JOIN, tempatkan tabel yang menghasilkan jumlah baris terkecil sebagai tabel pertama dalam klausa FROM dari join. Struktur ini membantu karena di HASH JOIN, Spanner selalu memilih tabel sisi kiri sebagai build dan tabel sisi kanan sebagai probe. Demikian pula, untuk APPLY JOIN, Spanner memilih tabel sisi kiri sebagai outer dan tabel sisi kanan sebagai inner. Lihat selengkapnya tentang jenis gabungan ini: [Gabungan hash][hash_join] dan [Gabungan penerapan][apply_join].

• Untuk kueri yang penting bagi workload Anda, tentukan metode join dan urutan join yang paling berperforma dalam pernyataan SQL Anda untuk performa yang lebih konsisten.

Mengoptimalkan kueri dengan pushdown predikat stempel waktu

Pushdown predikat stempel waktu adalah teknik pengoptimalan kueri yang digunakan di Spanner untuk meningkatkan efisiensi kueri yang menggunakan stempel waktu dan data dengan kebijakan penyimpanan bertingkat berbasis usia. Saat Anda mengaktifkan pengoptimalan ini, operasi pemfilteran pada kolom stempel waktu akan dilakukan sedini mungkin dalam rencana eksekusi kueri. Hal ini dapat mengurangi jumlah data yang diproses secara signifikan dan meningkatkan performa kueri secara keseluruhan.

Dengan pushdown predikat stempel waktu, mesin database menganalisis kueri dan mengidentifikasi filter stempel waktu. Kemudian, BigQuery "mendorong" filter ini ke lapisan penyimpanan, sehingga hanya data yang relevan berdasarkan kriteria stempel waktu yang dibaca dari SSD. Hal ini meminimalkan jumlah data yang diproses dan ditransfer, sehingga eksekusi kueri menjadi lebih cepat.

Untuk mengoptimalkan kueri agar hanya mengakses data yang disimpan di SSD, hal berikut harus berlaku:

• Kueri harus mengaktifkan pushdown predikat stempel waktu. Untuk mengetahui informasi selengkapnya, lihat Petunjuk pernyataan GoogleSQL dan Petunjuk pernyataan PostgreSQL

• Kueri harus menggunakan batasan berbasis usia yang sama dengan atau kurang dari usia yang ditentukan dalam kebijakan spill data (ditetapkan dengan opsi ssd_to_hdd_spill_timespan dalam pernyataan DDL CREATE LOCALITY GROUP atau ALTER LOCALITY GROUP). Untuk mengetahui informasi selengkapnya, lihat pernyataan LOCALITY GROUP GoogleSQL dan pernyataan LOCALITY GROUP PostgreSQL.

  • Kolom yang difilter dalam kueri harus berupa kolom stempel waktu yang berisi stempel waktu commit. Untuk mengetahui detail tentang cara membuat kolom stempel waktu commit, lihat Stempel waktu commit di GoogleSQL dan Stempel waktu commit di PostgreSQL. Kolom ini harus diperbarui bersama dengan kolom stempel waktu, dan berada dalam grup lokalitas yang sama, yang memiliki kebijakan penyimpanan bertingkat berbasis usia.

    Jika, untuk baris tertentu, beberapa kolom yang dikueri berada di SSD dan beberapa kolom berada di HDD (karena kolom diupdate pada waktu yang berbeda dan beralih ke HDD pada waktu yang berbeda), performa kueri mungkin akan lebih buruk saat Anda menggunakan petunjuk. Hal ini karena kueri harus mengisi data dari lapisan penyimpanan yang berbeda. Sebagai hasil dari penggunaan petunjuk, Spanner akan mengukur usia data pada tingkat sel individual (tingkat perincian baris dan kolom) berdasarkan stempel waktu commit setiap sel, sehingga memperlambat kueri. Untuk mencegah masalah ini, pastikan untuk memperbarui semua kolom yang sedang dikueri secara rutin menggunakan teknik pengoptimalan ini dalam transaksi yang sama, sehingga semua kolom memiliki stempel waktu commit yang sama dan mendapatkan manfaat dari pengoptimalan.

Untuk mengaktifkan pushdown predikat stempel waktu di tingkat pernyataan, gunakan petunjuk pernyataan dalam kueri Anda. Contoh:

GoogleSQL

@{allow_timestamp_predicate_pushdown=TRUE}
SELECT s.SingerInfo
FROM Singers s
WHERE s.ModificationTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

PostgreSQL

/*@allow_timestamp_predicate_pushdown=TRUE*/
SELECT s.SingerInfo
FROM Singers s
WHERE s.ModificationTime > CURRENT_TIMESTAMP - INTERVAL '12 hours';

Menghindari pembacaan dalam jumlah besar di dalam transaksi baca-tulis

Transaksi baca-tulis memungkinkan urutan nol atau lebih pembacaan atau kueri SQL, dan dapat menyertakan serangkaian mutasi, sebelum panggilan untuk melakukan commit. Untuk menjaga konsistensi data Anda, Spanner memperoleh kunci saat membaca dan menulis baris dalam tabel dan indeks Anda. Untuk mengetahui informasi selengkapnya tentang penguncian, lihat Masa Pakai Operasi Baca dan Tulis.

Karena cara kerja penguncian di Spanner, melakukan pembacaan atau kueri SQL yang membaca banyak baris (misalnya SELECT * FROM Singers) berarti tidak ada transaksi lain yang dapat menulis ke baris yang telah Anda baca hingga transaksi Anda di-commit atau dibatalkan.

Selain itu, karena transaksi Anda memproses sejumlah besar baris, transaksi tersebut kemungkinan akan memerlukan waktu lebih lama daripada transaksi yang membaca rentang baris yang jauh lebih kecil (misalnya SELECT LastName FROM Singers WHERE SingerId = 7), yang selanjutnya memperburuk masalah dan mengurangi throughput sistem.

Jadi, cobalah untuk menghindari pembacaan dalam jumlah besar (misalnya, pemindaian tabel lengkap atau operasi penggabungan besar-besaran) dalam transaksi Anda, kecuali jika Anda bersedia menerima throughput tulis yang lebih rendah.

Dalam beberapa kasus, pola berikut dapat memberikan hasil yang lebih baik:

1. Lakukan pembacaan dalam jumlah besar di dalam transaksi hanya baca. Transaksi hanya baca memungkinkan throughput agregat yang lebih tinggi karena tidak menggunakan kunci.
2. Opsional: Lakukan pemrosesan yang diperlukan pada data yang baru saja Anda baca.
3. Memulai transaksi baca-tulis.
4. Pastikan baris penting tidak berubah nilainya sejak Anda melakukan transaksi hanya baca di langkah 1.
   • Jika baris telah berubah, roll back transaksi Anda dan mulai lagi di langkah 1.
   • Jika semuanya terlihat baik-baik saja, commit mutasi Anda.

Salah satu cara untuk memastikan bahwa Anda menghindari pembacaan dalam jumlah besar dalam transaksi baca-tulis adalah dengan melihat rencana eksekusi yang dihasilkan kueri Anda.

Menggunakan ORDER BY untuk memastikan pengurutan hasil SQL Anda

Jika Anda mengharapkan pengurutan tertentu untuk hasil kueri SELECT, sertakan klausa ORDER BY secara eksplisit. Misalnya, jika Anda ingin mencantumkan semua penyanyi dalam urutan kunci utama, gunakan kueri ini:

SELECT * FROM Singers
ORDER BY SingerId;

Spanner menjamin pengurutan hasil hanya jika klausa ORDER BY ada dalam kueri. Dengan kata lain, pertimbangkan kueri ini tanpa ORDER BY:

SELECT * FROM Singers;

Spanner tidak menjamin bahwa hasil kueri ini akan berada dalam urutan kunci utama. Selain itu, pengurutan hasil dapat berubah sewaktu-waktu dan tidak dijamin konsisten dari satu pemanggilan ke pemanggilan lainnya. Jika kueri memiliki klausa ORDER BY, dan Spanner menggunakan indeks yang menyediakan urutan yang diperlukan, Spanner tidak akan mengurutkan data secara eksplisit. Oleh karena itu, jangan khawatir tentang dampak performa dari penyertaan klausa ini. Anda dapat memeriksa apakah operasi pengurutan eksplisit disertakan dalam eksekusi dengan melihat rencana kueri.

Menggunakan STARTS_WITH, bukan LIKE

Karena Spanner tidak mengevaluasi pola LIKE berparameter hingga waktu eksekusi, Spanner harus membaca semua baris dan mengevaluasinya terhadap ekspresi LIKE untuk memfilter baris yang tidak cocok.

Jika pola LIKE memiliki bentuk foo% (misalnya, dimulai dengan string tetap dan diakhiri dengan satu persen karakter pengganti) dan kolom diindeks, gunakan STARTS_WITH, bukan LIKE. Opsi ini memungkinkan Spanner mengoptimalkan rencana eksekusi kueri secara lebih efektif.

Tidak direkomendasikan:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Catatan: Contoh ini mengasumsikan @like_clause terikat ke 'Love%'.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

Catatan: Contoh ini mengasumsikan $1 terikat ke 'Love%'.

Direkomendasikan:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

Catatan: Contoh ini mengasumsikan @prefix terikat ke 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Kueri ini berjalan lebih cepat jika indeks ditentukan di Albums.AlbumTitle.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

Catatan: Contoh ini mengasumsikan $2 terikat ke 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Kueri ini berjalan lebih cepat jika indeks ditentukan di Albums.AlbumTitle.

Menggunakan stempel waktu commit

Jika aplikasi Anda perlu mengkueri data yang ditulis setelah waktu tertentu, tambahkan kolom stempel waktu commit ke tabel yang relevan. Stempel waktu commit memungkinkan pengoptimalan Spanner yang dapat mengurangi I/O kueri yang klausa WHERE-nya membatasi hasil ke baris yang ditulis lebih baru daripada waktu tertentu.

Pelajari lebih lanjut pengoptimalan ini dengan database dialek GoogleSQL atau dengan database dialek PostgreSQL.