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.

Pernyataan SQL contoh yang ditampilkan di halaman ini menggunakan skema contoh berikut:

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;

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;

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 nilai literal diharapkan. 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: Kueri yang menggunakan parameter dapat dieksekusi lebih cepat pada setiap pemanggilan karena parameterisasi memudahkan Spanner untuk menyimpan rencana eksekusi dalam cache.
• Komposisi kueri yang disederhanakan: Anda tidak perlu melakukan escape pada nilai string saat memberikannya di parameter kueri. Parameter kueri juga mengurangi risiko kesalahan 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

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 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 konsolGoogle 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 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 visualisasi 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 Rencana kueri yang diambil sampelnya.

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 dengan menggunakan Spanner Studio. 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 yang 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 penuh. Pemindaian tabel lengkap adalah cara yang mahal untuk mendapatkan hasil saat 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 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 direktif indeks dalam klausa FROM:

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

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:

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

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 ada dalam indeks, Anda tetap harus menentukan direktif indeks seperti sebelumnya:

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

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, Anda dapat menyimpan salinan kolom FirstName dalam indeks itu sendiri:

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

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

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

• Kueri SQL yang menggunakan indeks dan memilih kolom 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 dari 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 tanggal rilis dan urutan menurun 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:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS