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 membuat pernyataan SQL guna membantu Spanner menemukan rencana eksekusi yang efisien.

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, dan 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 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 tidak sebagai pengganti ID, nama kolom, nama tabel, atau bagian lain dari kueri.

Parameter dapat muncul di mana saja yang mengharapkan 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 mempermudah Spanner menyimpan rencana eksekusi dalam cache.
• Komposisi kueri yang disederhanakan: Anda tidak perlu meng-escape nilai string saat menyediakan nilai tersebut 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.

Memahami cara Spanner menjalankan kueri

Catatan: Spanner Studio (sebelumnya diberi label Kueri 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 membuat kueri database menggunakan pernyataan SQL deklaratif yang menentukan data yang ingin Anda ambil. Jika Anda ingin memahami cara Spanner mendapatkan hasilnya, periksa rencana eksekusi untuk kueri. Rencana eksekusi kueri menampilkan biaya komputasi yang terkait dengan setiap langkah kueri. Dengan biaya tersebut, Anda dapat men-debug masalah performa kueri dan mengoptimalkan kueri. Untuk mempelajari lebih lanjut, lihat Rencana eksekusi kueri.

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

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

1. Buka halaman instance Spanner.

   Buka instance Spanner

2. Pilih nama instance Spanner dan database yang ingin Anda kueri.

3. Klik Spanner Studio di panel navigasi sebelah 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 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 paket 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. Cara yang lebih umum untuk mengambil data dari indeks adalah menggunakan Spanner Studio. Dengan menggunakan indeks sekunder dalam kueri SQL, Anda dapat menentukan cara yang Anda inginkan agar Spanner mendapatkan hasilnya. 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 perlu waktu lama untuk menampilkan hasilnya. Waktunya akan bergantung pada jumlah baris dalam tabel Singers dan jumlah 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 penuh. Pemindaian tabel lengkap adalah cara yang mahal untuk mendapatkan hasil jika tabel hanya berisi persentase 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 (asalkan tiga hari telah berlalu sejak pembuatan database; lihat Catatan tentang database baru). Namun, sebaiknya bilangkan secara eksplisit kepada Spanner untuk menggunakan indeks tersebut dengan menentukan perintah 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';

Sekarang, anggaplah 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 masih mendapatkan manfaat performa dari penggunaan indeks karena Spanner tidak perlu melakukan pemindaian tabel penuh saat menjalankan rencana kueri. Sebagai gantinya, fungsi ini memilih subset baris yang memenuhi predikat dari indeks SingersByLastName, lalu melakukan pencarian dari tabel dasar Singers untuk mengambil nama pertama hanya untuk subset baris tersebut.

Jika Anda ingin Spanner tidak perlu mengambil baris apa pun dari tabel dasar, Anda dapat menyimpan salinan kolom FirstName di 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 akan 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 cara 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 tanggal rilis menaik dan urutan judul album menurun. 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 guna 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 dalam setiap commit. Untuk sebagian besar beban kerja, 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 beban kerja Anda. Lakukan benchmark pada beban kerja Anda untuk memastikan performanya sesuai harapan.

Untuk referensi lengkap tentang indeks sekunder, lihat Indeks sekunder.

Mengoptimalkan pemindaian

Kueri Spanner tertentu mungkin mendapatkan manfaat dari penggunaan metode pemrosesan berorientasi batch saat memindai data, bukan metode pemrosesan berorientasi baris yang lebih umum. Memproses pemindaian dalam batch adalah cara yang lebih efisien untuk memproses data dalam jumlah besar sekaligus, dan memungkinkan kueri mencapai penggunaan dan latensi CPU yang lebih rendah.

Operasi pemindaian Spanner selalu memulai eksekusi dalam mode berorientasi baris. Selama waktu ini, Spanner mengumpulkan beberapa metrik runtime. Kemudian, Spanner menerapkan serangkaian heuristik berdasarkan hasil metrik ini untuk menentukan mode pemindaian yang optimal. Jika sesuai, Spanner akan beralih ke mode pemrosesan berorientasi batch untuk membantu meningkatkan throughput dan performa pemindaian.

Kasus penggunaan umum

Kueri dengan karakteristik berikut umumnya mendapatkan manfaat dari penggunaan pemrosesan berorientasi batch:

• Pemindaian besar pada data yang jarang diperbarui.
• Pemindaian dengan predikat pada kolom lebar tetap.
• Pemindaian dengan jumlah pencarian yang besar. (Penelusuran menggunakan indeks untuk mengambil data.)

Kasus penggunaan tanpa peningkatan performa

Tidak semua kueri mendapatkan manfaat dari pemrosesan berorientasi batch. Jenis kueri berikut berperforma lebih baik dengan pemrosesan pemindaian berorientasi baris:

• Kueri pencarian titik: kueri yang hanya mengambil satu baris.
• Kueri pemindaian kecil: pemindaian tabel yang hanya memindai beberapa baris kecuali jika memiliki jumlah pencarian yang besar.
• Kueri yang menggunakan LIMIT.
• Kueri yang membaca data churn tinggi: kueri yang lebih dari ~10% datanya sering diperbarui.
• Kueri dengan baris yang berisi nilai besar: baris nilai besar adalah baris yang berisi nilai lebih besar dari 32.000 byte (pra-kompresi) dalam satu kolom.

Cara memeriksa metode pemindaian yang digunakan oleh kueri

Untuk memeriksa apakah kueri Anda menggunakan pemrosesan berorientasi batch, pemrosesan berorientasi baris, atau beralih secara otomatis antara dua metode pemindaian:

1. Buka halaman Instance Spanner di konsol Google Cloud.

   Buka halaman Instances

2. Klik nama instance dengan kueri yang ingin Anda selidiki.

3. Di bagian tabel Databases, klik database dengan kueri yang ingin Anda periksa.

4. Di menu Navigation, klik Spanner Studio.

5. Buka tab baru dengan mengklik Tab editor SQL baru atau Tab baru.

6. Saat editor kueri muncul, tulis kueri Anda.

7. Klik Run.

   Spanner menjalankan kueri dan menampilkan hasilnya.

8. Klik tab Explanation di bawah editor kueri.

   Spanner menampilkan visualisasi rencana eksekusi rencana kueri. Setiap kartu pada grafik mewakili iterator.

9. Klik kartu iterator Pemindaian tabel untuk membuka panel informasi.

   Panel informasi menampilkan informasi kontekstual tentang pemindaian yang dipilih. Metode pemindaian ditampilkan di kartu ini. Otomatis menunjukkan bahwa Spanner menentukan metode pemindaian. Nilai lain yang mungkin mencakup Vectorized untuk pemrosesan berorientasi batch dan Scalar untuk pemrosesan berorientasi baris.

   

Cara menerapkan metode pemindaian yang digunakan oleh kueri

Untuk mengoptimalkan performa kueri, Spanner memilih metode pemindaian yang optimal untuk kueri Anda. Sebaiknya gunakan metode pemindaian default ini. Namun, mungkin ada skenario saat Anda ingin menerapkan jenis metode pemindaian tertentu.

Cara menerapkan pemindaian berorientasi batch

Anda dapat menerapkan pemindaian berorientasi batch di tingkat tabel dan tingkat pernyataan.

Untuk menerapkan metode pemindaian berorientasi batch di tingkat tabel, gunakan petunjuk tabel dalam kueri Anda:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=BATCH} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=batch */ JOIN t2 on ...)
  WHERE ...

Untuk menerapkan metode pemindaian berorientasi batch di tingkat pernyataan, gunakan petunjuk pernyataan dalam kueri Anda:

GoogleSQL

  @{SCAN_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

Cara menonaktifkan pemindaian otomatis dan menerapkan pemindaian berorientasi baris

Meskipun kami tidak merekomendasikan untuk menonaktifkan metode pemindaian otomatis yang ditetapkan oleh Spanner, Anda dapat memutuskan untuk menonaktifkannya dan menggunakan metode pemindaian berorientasi baris untuk tujuan pemecahan masalah, seperti mendiagnosis latensi.

Untuk menonaktifkan metode pemindaian otomatis dan menerapkan pemrosesan baris di tingkat tabel, gunakan petunjuk tabel dalam kueri Anda:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=ROW} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=row */ JOIN t2 on ...)
  WHERE ...

Untuk menonaktifkan metode pemindaian otomatis dan menerapkan pemrosesan baris di tingkat pernyataan, gunakan petunjuk pernyataan dalam kueri Anda:

GoogleSQL

  @{SCAN_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=row */
  SELECT ...
  FROM ...
  WHERE ...

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.

  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 telah dibahas dalam praktik terbaik sebelumnya.

• Jika daftar kunci berdekatan dan dalam rentang, tentukan batas bawah dan batas 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 dengan nilai 1 dan 5.

  PostgreSQL

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

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

  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 yang lebih rendah dan lebih tinggi seperti dalam kueri sebelumnya karena kueri yang dihasilkan akan memindai setiap nilai antara 1 dan 1000.

Mengoptimalkan join

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

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

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

  Baris dalam tabel interleaved Albums dijamin disimpan secara fisik dalam bagian yang sama dengan baris induk di Singers, seperti yang dibahas dalam Skema dan Model Data. Oleh karena itu, join dapat selesai secara lokal tanpa mengirim banyak data ke seluruh jaringan.

• Gunakan perintah join jika Anda ingin memaksa 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 penerapan join. Saat Anda menggunakan SQL untuk mengkueri beberapa tabel, Spanner akan otomatis menggunakan metode join yang cenderung membuat kueri lebih efisien. Namun, Google menyarankan Anda untuk menguji dengan algoritma join yang berbeda. 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 memiliki klausa WHERE yang sangat selektif di satu sisi JOIN, tempatkan tabel yang menghasilkan jumlah baris terkecil sebagai tabel pertama dalam klausa FROM join. Struktur ini membantu karena saat ini 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 luar dan tabel sisi kanan sebagai dalam. Lihat selengkapnya tentang jenis join ini: Hash join dan Apply join.

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

Menghindari pembacaan dalam jumlah besar di dalam transaksi baca-tulis

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

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

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

Jadi, coba hindari operasi baca dalam jumlah besar (misalnya, pemindaian tabel lengkap atau operasi join massal) dalam transaksi Anda, kecuali jika Anda bersedia menerima throughput menulis 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 gabungan yang lebih tinggi karena tidak menggunakan kunci.
2. Opsional: Lakukan pemrosesan yang diperlukan pada data yang baru saja Anda baca.
3. Mulai transaksi baca-tulis.
4. Pastikan baris penting belum mengubah nilai sejak Anda melakukan transaksi hanya baca di langkah 1.
   • Jika baris telah berubah, rollback transaksi Anda dan mulai lagi dari langkah 1.
   • Jika semuanya sudah sesuai, commit mutasi Anda.

Salah satu cara untuk memastikan bahwa Anda menghindari operasi baca 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 urutan tertentu untuk hasil kueri SELECT, sertakan klausa ORDER BY secara eksplisit. Misalnya, jika Anda ingin menampilkan 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 kapan saja dan tidak dijamin konsisten dari pemanggilan ke pemanggilan. Jika kueri memiliki klausa ORDER BY, dan Spanner menggunakan indeks yang memberikan urutan yang diperlukan, Spanner tidak akan mengurutkan data secara eksplisit. Oleh karena itu, jangan khawatir dengan dampak performa dari penyertaan klausul ini. Anda dapat memeriksa apakah operasi pengurutan eksplisit disertakan dalam eksekusi dengan melihat paket kueri.

Gunakan 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 dengan 'Love%'.

PostgreSQL

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

Catatan: Contoh ini mengasumsikan $2 terikat dengan 'Love%'.

Direkomendasikan:

GoogleSQL

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

Catatan: Contoh ini mengasumsikan @prefix terikat dengan 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Proses 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 $1 terikat dengan 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Proses ini berjalan lebih cepat jika indeks ditentukan di Albums.AlbumTitle.

Menggunakan stempel waktu commit

Jika aplikasi Anda perlu membuat kueri 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 dari waktu tertentu.

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