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 skema contoh 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 yang 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 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 pun yang diharapkan adalah nilai literal. Nama parameter yang sama dapat digunakan lebih dari sekali dalam satu pernyataan SQL.

Singkatnya, parameter kueri mendukung eksekusi kueri dengan cara berikut:

• Paket yang telah dioptimalkan: Kueri yang menggunakan parameter dapat dijalankan lebih cepat pada setiap pemanggilan karena parameterisasi tersebut memudahkan Spanner untuk menyimpan rencana eksekusi ke cache.
• 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.

Memahami cara Spanner menjalankan kueri

Catatan: Spanner Studio (sebelumnya berlabel Kueri di Konsol Google Cloud) mendukung operasi SQL, DML, dan DDL dalam satu editor. Untuk informasi selengkapnya, lihat Mengelola data Anda menggunakan Konsol Google Cloud.

Dengan Spanner, Anda dapat membuat kueri database menggunakan pernyataan SQL deklaratif yang menentukan data yang ingin diambil. Jika ingin memahami cara Spanner mendapatkan hasilnya, periksa rencana eksekusi untuk kueri tersebut. 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.

Guna 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 kiri.

4. Ketik kueri di kolom teks, lalu klik Jalankan kueri.

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 Anda, 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 paket 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. Menggunakan indeks sekunder dalam kueri SQL memungkinkan Anda menentukan cara 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 seperti itu 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 sebagian kecil Singers dengan nama belakang tersebut.

Anda dapat meningkatkan performa kueri ini dengan menentukan indeks sekunder di 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 yang lengkap.

Dalam skenario ini, Spanner secara 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 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, misalkan Anda juga ingin mengambil nama depan penyanyi selain ID-nya. 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 penuh saat menjalankan paket kueri. Sebagai gantinya, sistem ini memilih subset baris yang memenuhi predikat dari indeks SingersByLastName, lalu melakukan pencarian dari Singers tabel dasar 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);

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

• Kueri SQL yang menggunakan indeks dan kolom tertentu yang disimpan dalam klausa STORING atau INCLUDE tidak memerlukan gabungan 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 suatu kueri dapat diidentifikasi dengan cepat menggunakan indeks sekunder.

Skenario lain di mana indeks sekunder dapat menawarkan manfaat performa adalah saat kueri tertentu menampilkan hasil yang diurutkan. Misalnya, Anda ingin mengambil semua judul album dan tanggal rilisnya dalam urutan menaik pada tanggal rilis dan dalam urutan menurun dari 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 indeks dan kueri ini memenuhi kedua kriteria berikut:

• Untuk menghapus langkah pengurutan, pastikan daftar kolom dalam klausa ORDER BY adalah awalan daftar kunci indeks.
• Untuk menghindari penggabungan 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, penambahan indeks sekunder dapat menambah latensi ke operasi commit Anda, karena setiap indeks sekunder biasanya memerlukan keterlibatan node tambahan dalam 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. Tetapkan tolok ukur beban kerja Anda untuk memastikan performanya seperti yang diharapkan.

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, daripada 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 CPU dan latensi yang lebih rendah.

Operasi pemindaian Spanner selalu memulai eksekusi dalam mode berorientasi baris. Selama waktu ini, Spanner mengumpulkan beberapa metrik runtime. Kemudian, Spanner menerapkan sekumpulan heuristik berdasarkan hasil metrik tersebut 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.
• Memindai dengan predikat pada kolom dengan lebar tetap.
• Memindai dengan jumlah pencarian yang besar. (Pencarian menggunakan indeks untuk mengambil kumpulan 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 hanya memindai beberapa baris kecuali jika baris tersebut memiliki jumlah pencarian yang besar.
• Kueri yang menggunakan LIMIT.
• Kueri yang membaca data churn tinggi: kueri dengan lebih dari ~10% pembacaan data 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 antara dua metode pemindaian secara otomatis:

1. Buka halaman Instance Spanner di Konsol Google Cloud.

   Buka halaman Instances

2. Klik nama instance dengan kueri yang ingin diselidiki.

3. Di tabel Database, klik database dengan kueri yang ingin diselidiki.

4. Di menu Navigasi, 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 Penjelasan di bawah editor kueri.

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

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

   Panel informasi menampilkan informasi kontekstual tentang pemindaian yang dipilih. Metode pemindaian ditampilkan pada kartu ini. Otomatis menunjukkan Spanner menentukan metode pemindaian. Nilai yang memungkinkan lainnya 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 mungkin ingin menerapkan jenis metode pemindaian tertentu.

Cara menerapkan pemindaian berorientasi batch

Anda bisa menerapkan pemindaian berorientasi batch pada tingkat tabel dan tingkat pernyataan.

Untuk menerapkan metode pemindaian berorientasi batch pada 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 pada 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 disetel oleh Spanner, Anda dapat menonaktifkannya dan menggunakan metode pemindaian berorientasi baris untuk tujuan pemecahan masalah, seperti mendiagnosis latensi.

Untuk menonaktifkan metode pemindaian otomatis dan menerapkan pemrosesan baris di level 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 level 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 membaca beberapa baris dari Spanner berdasarkan daftar kunci yang diketahui.

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

• Jika daftar kunci renggang 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 dibahas dalam praktik terbaik sebelumnya.

• Jika daftar kunci berdekatan dan berada dalam rentang, tentukan batas yang lebih rendah dan tinggi dari 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 masing-masing 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 masing-masing 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 menentukan batas yang lebih rendah dan lebih tinggi seperti di kueri sebelumnya karena kueri yang dihasilkan akan memindai setiap nilai antara 1 dan 1000.

Optimize gabungan

Operasi gabungan bisa jadi 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 dalam database relasional lainnya untuk mengoptimalkan kueri gabungan, berikut beberapa praktik terbaik untuk JOIN yang lebih efisien saat menggunakan Spanner SQL:

• Jika memungkinkan, gabungkan data dalam tabel yang disisipi dengan {i>primary key<i}. Contoh:

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

  Baris dalam tabel sisipan Albums dijamin akan secara fisik disimpan dalam pemisahan yang sama dengan baris induk di Singers, seperti yang dibahas dalam Skema dan Model Data. Oleh karena itu, penggabungan dapat diselesaikan secara lokal tanpa mengirim banyak data melalui jaringan.

• Gunakan perintah join jika Anda ingin memaksa urutan penggabungan. 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 penggabungan yang ditentukan dalam kueri (yaitu, Singers JOIN Albums, bukan Albums JOIN Singers). Hasil yang ditampilkan sama, apa pun urutan yang dipilih Spanner. Namun, Anda mungkin ingin menggunakan perintah gabungan ini jika Anda melihat di rencana kueri bahwa Spanner telah mengubah urutan penggabungan dan menyebabkan konsekuensi yang tidak diinginkan, seperti hasil perantara yang lebih besar, atau kehilangan peluang untuk mencari baris.

• Gunakan perintah gabungan untuk memilih penerapan gabungan. Saat Anda menggunakan SQL untuk membuat kueri beberapa tabel, Spanner akan otomatis menggunakan metode gabungan yang mungkin akan membuat kueri lebih efisien. Namun, Google menyarankan Anda untuk melakukan pengujian dengan berbagai algoritma gabungan. Memilih algoritma gabungan 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 memiliki klausa WHERE yang sangat selektif di satu sisi JOIN, tempatkan tabel yang menghasilkan jumlah baris terkecil sebagai tabel pertama dalam klausa FROM gabungan. Struktur ini membantu karena saat ini di HASH JOIN, Spanner selalu memilih tabel sisi kiri sebagai build dan tabel sisi kanan sebagai pemeriksaan. Demikian pula, untuk APPLY JOIN, Spanner memilih tabel sisi kiri sebagai outer dan tabel sisi kanan sebagai dalam. Lihat informasi selengkapnya tentang jenis penggabungan ini: Hash join dan Apply join.

• Untuk kueri yang sangat penting bagi beban kerja Anda, tentukan metode gabungan dan urutan penggabungan yang paling berperforma tinggi 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 bacaan atau kueri SQL, dan dapat mencakup serangkaian mutasi, sebelum panggilan ke commit. Untuk menjaga konsistensi data, Spanner mendapatkan kunci saat membaca dan menulis baris di tabel dan indeks Anda. Untuk mengetahui informasi selengkapnya tentang penguncian, lihat Masa Berlaku Bacaan dan Penulisan.

Karena cara kerja penguncian di Spanner, melakukan kueri baca atau 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 baris dalam jumlah besar, mungkin diperlukan waktu lebih lama daripada transaksi yang membaca rentang baris yang jauh lebih kecil (misalnya SELECT LastName FROM Singers WHERE SingerId = 7), yang selanjutnya akan memperburuk masalah dan mengurangi throughput sistem.

Jadi, cobalah untuk menghindari pembacaan besar (misalnya, pemindaian tabel penuh atau operasi gabungan 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 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 nilai sejak Anda melakukan transaksi hanya baca di langkah 1.
   • Jika baris telah berubah, roll back transaksi Anda dan mulai lagi pada langkah 1.
   • Jika tidak ada masalah, lakukan mutasi.

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

Menggunakan ORDER BY untuk memastikan urutan hasil SQL Anda

Jika Anda mengharapkan pengurutan tertentu untuk hasil kueri SELECT, sertakan klausa ORDER BY secara eksplisit. Misalnya, jika Anda ingin menampilkan daftar 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 dalam urutan kunci utama. Selain itu, urutan hasil dapat berubah kapan saja dan tidak dijamin konsisten dari pemanggilan hingga 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 khawatirkan dampak performa dari penyertaan klausa 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 yang diparameterisasi hingga waktu eksekusi, Spanner harus membaca semua baris dan mengevaluasinya berdasarkan ekspresi LIKE untuk memfilter baris yang tidak cocok.

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

Tidak direkomendasikan:

GoogleSQL

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

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

PostgreSQL

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

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

Direkomendasikan:

GoogleSQL

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

Catatan: Contoh ini mengasumsikan bahwa @prefix terikat dengan 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Berjalan lebih cepat jika indeks ditentukan pada Albums.AlbumTitle.

PostgreSQL

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

Catatan: Contoh ini mengasumsikan bahwa $1 terikat dengan 'Love'. Kueri ini lebih efisien daripada kueri sebelumnya. Berjalan lebih cepat jika indeks ditentukan pada 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. Dengan commit stempel waktu, pengoptimalan Spanner dapat mengurangi I/O kueri yang klausa WHERE-nya membatasi hasil ke baris yang ditulis lebih baru daripada waktu tertentu.

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