Optimasi Query Google Cloud Spanner

Ketika kita menulis query, sering nya kita terlalu percaya terhadap DBMS(Database Management System) yang kita gunakan untuk menjalankan query kita dengan optimal, seperti otomatis menggunakan index yang ada dari klausa where atau on sehingga tidak melakukan full table scan atau mengetahui urutan eksekusi query yang paling optimal dari tabel-tabel yang kita join.

Hal ini memang benar untuk beberapa DBMS populer seperti PostgreSQL, MySQL, Oracle, dan sebagainya, yang mana DBMS tersebut bisa mengetahui index mana yang perlu digunakan. Dan seiring data bertambah, DBMS bisa mengoptimalkan query berdasarkan statistik dari pelaksanaan query tersebut, seperti PostgreSQL. Namun terkadang kita perlu memaksakan penggunaan index di query kita, kita sendiri yang harus mengatur urutan tabel tabel yang kita join, dsb.

Untungnya di setiap DBMS pasti ada fitur yang bisa memberikan execution plan dari query-query kita. Begitu pula dengan Google Cloud Spanner(selanjutnya akan disebut Spanner). Query Execution Plan ini adalah tool yang sangat kuat yang bisa kita gunakan untuk mencari bagian bagian dari query kita yang bisa kita optimalkan. Di artikel ini, kita akan membicarakan optimasi query untuk Spanner dengan memanfaatkan index dan urutan join, dengan memanfaatkan query explanation yang bisa kita akses di console Spanner.

TLDR;

• Gunakan JOIN_TYPE join hint, terutama untuk outer join query, bereksperimenlah dengan berbagai macam JOIN_TYPE
• Gunakan index dengan hati hati, perlu diperhatikan bahwa jika kita memaksakan penggunaan index, ada satu tabel yang perlu dipindai yaitu tabel index itu sendiri
• Gunakan index sesuai dengan kolom di klausa where dan on
• Urutan tabel yang kita query berpengaruh terhadap query plan
• Kolom yang kita query juga berpengaruh terhadap query plan
• Manfaatkan tools query execution plan dengan maksimal

Database Setup

Untuk artikel ini, kita akan membuat database forum dengan tabel users, threads, dan posts.

Kita bisa akses web console Spanner untuk membuat instance dan database yang diperlukan. Menu Spanner ada di bawah menu Storage di web console Google Cloud.

Untuk membuat instance, kita pilih Create Instance di halaman spanner console

Masukkan instance name, instance id yang diinginkan.

Untuk membuat database, pertama kita harus masuk ke halaman instance yang telah kita buat sebelumnya lalu pilih create database.

Masukkan forum sebagai nama database dan skema tabel(opsional).

Kita akan membuat tiga tabel, users, threads, dan posts. Berikut skema tabel nya:

Data definition language nya:

CREATE TABLE users (
 id INT64 NOT NULL,
 email STRING(MAX),
 username STRING(MAX),
) PRIMARY KEY (id)

Kita juga akan membuat index untuk tabel users dan posts:

CREATE INDEX users_username 
ON users (
 username
)

Query Execution Plan

Jika kita ke web console Spanner, dan kita expand database forum yang telah kita buat sebelumnya, kita bisa lihat tabel-tabel yang ada di bawah database kita dan satu pilihan lagi Query.

Jika kita pilih menu ini, kita akan dibawa ke halaman di mana kita bisa menjalankan query select dan melihat penjelasan bagaimana Spanner menjalankan query yang akan kita tulis. Di halaman ini, kita bisa melihat skema tabel-tabel kita, hasil query yang kita jalankan, dan penjelasan bagaimana Spanner menjalankan query kita.

Join

Apabila tabel tabel yang kita join adalah interleaved tables(https://cloud.google.com/spanner/docs/schema-and-data-model#creating-interleaved-tables) sebisa mungkin kita gunakan kolom primary key di klausa on.

Spanner memiliki attribut klausa join yang bisa kita gunakan untuk membantu Spanner menjalankan query kita(https://cloud.google.com/spanner/docs/query-syntax#join-hints). Kita bisa memaksa spanner untuk mengikuti urutan tabel di query kita dengan menggunakan FORCE_JOIN_ORDER=true, dan kita bisa memaksa Spanner menggunakan JOIN_TYPE tertentu ketika menjalankan query kita. Ini bisa mempercepat atau malah bisa memperlambat query kita. Selalu perhatikan execution plan query kita di web console Spanner.

Dalam melakukan join, bila kita menggunakan klausa where sebisa mungkin tabel di mana kolom yang kita gunakan di klausa where tadi berada di klausa from, terutama jika ukuran tabel ini kecil. Ini sangat berpengaruh terhadap HASH_JOIN dan LOOP_JOIN karena dua join type ini akan menggunakan hasil pindai tabel di klausa FROM untuk mencari pasangannya di klausa JOIN.

Index

Penjelasan sederhana mengenai index adalah data yang menjelaskan lokasi data di tabel. Seperti di semua DBMS, Spanner juga mempunyai dukungan untuk index. Namun terkadang Spanner tidak menggunakan index yang telah kita buat untuk tabel kita.

Spanner menyediakan klausa FORCE_INDEX yang bisa kita pasangkan setelah nama tabel, untuk memberitahu Spanner menggunakan index yang kita pilih dalam menjalankan query kita.

Contoh

Index

Untuk contoh penggunaan index, di database forum yang telah kita buat sebelumnya, kita akan mengambil data posts dari suatu user berdasarkan username. Sebelumnya kita sudah menyiapkan seribu data untuk setiap tabel.

SELECT users.email, posts.content 
FROM users
JOIN posts ON posts.author_id = users.id 
WHERE users.username = ‘clebbon2’

Apabila query ini kita jalankan di menu Query di web console Spanner dan kita lihat execution plannya, akan menghasilkan hasil seperti ini:

Hasilnya cukup mengerikan, Spanner akan memindai seluruh isi tabel dari tabel users dan posts, bayangkan jika kita memiliki ratusan ribu atau jutaan row. Kita sudah buat dua index sebelumnya, tabel posts dengan index posts_author_id dan tabel users dengan index users_username. Namun Spanner belum menggunakan kedua index ini untuk menjalankan querynya.

Untuk query ini, kita bisa optimalkan dengan memaksa Spanner untuk menggunakan index yang telah kita buat, karena di query kita, kita menggunakan users.username di klausa where dan kita menggunakan posts.author_id di klausa on. Dengan menggunakan index users.username, maka pencarian data di tabel users berdasarkan username bisa lebih optimal dilakukan dengan menggunakan index users_username, begitu pula ketika join dilakukan dengan tabel posts, karena kita menggunakan posts.author_id, maka dengan menggunakan index posts_author_id, pencarian dapat dilakukan dengan lebih efisien.

SELECT users.email, posts.content 
FROM users@{FORCE_INDEX=users_username}
JOIN posts@{FORCE_INDEX=posts_author_id} ON posts.author_id = users.id 
WHERE users.username = ‘clebbon2’

Menghasilkan query plan seperti ini:

Dapat kita lihat bahwa jumlah baris yang dipindai berkurang jauh. Spanner tidak melakukan pemindaian tabel secara keseluruhan, namun hanya melihat index yang telah kita buat untuk users.username dan posts.author_id, dan mencari baris yang sesuai dengan index tersebut. Namun, hal yang menarik adalah, waktu yang digunakan untuk menjalankan query ini meningkat. hal ini disebabkan karena spanner harus memindai tabel index nya terlebih dahulu sebelum tabel asli nya.

Tentu saja jika kita menjalankan query dengan menggunakan primary key kadang penggunaan index yang salah dapat menyebabkan query kita menjadi lebih lambat.

SELECT users.email, posts.content
FROM users
JOIN posts@{FORCE_INDEX=} ON posts.author_id = users.id 
WHERE users.id = 2

Bila kita paksakan index users_username dari tabel users, ini akan memberikan dampak negatif.

SELECT users.email, posts.content
FROM users@{FORCE_INDEX=users_username}
JOIN posts@{FORCE_INDEX=posts_author_id} ON posts.author_id = users.id 
WHERE users.id = 2

Spanner akan memindai seluruh baris di tabel users karena kita paksakan pencarian menggunakan index yang berbeda kolom. Sehingga Spanner akan memindai tabel index nya dan tabel asli nya, lalu menggabungkan hasilnya.

Tetap berhati-hati dalam menggunakan index, dan pastikan kita cek execution plan query kita.

Hal menarik lainnya adalah, bila kita hanya memilih kolom dari tabel users.

SELECT users.email
FROM users
JOIN posts ON posts.author_id = users.id 
WHERE users.id = 2

Bisa kita lihat karena Spanner tidak perlu mengambil kolom apapun dari tabel posts selain author_id Spanner langsung mengaplikasikan index yang dibutuhkan dalam memindai tabel posts.

Outer Join

Contoh berikutnya untuk penggunaan JOIN_HINT terutama untuk outer join seperti left join dan right join. Untuk outer join Spanner akan menggunakan JOIN_TYPE=HASH_JOIN sebagai tipe join default, namun Spanner memiliki tipe join lain antara lain LOOP_JOIN dan APPLY_JOIN.

Kita akan query semua user dengan semua thread yang pernah dibuat oleh user tersebut, tidak semua user pernah membuat thread.

SELECT users.id, users.email, threads.title
FROM users
LEFT JOIN@{JOIN_TYPE=HASH_JOIN} threads ON threads.author_id = users.id

Spanner akan memindai semua baris dari kedua tabel dan akan menggabungkan hasilnya.

Bila kita gunakan APPLY_JOIN,

SELECT users.id, users.email, threads.title
FROM users
LEFT JOIN@{JOIN_TYPE=APPLY_JOIN} threads ON threads.author_id = users.id

hasil nya lebih mengerikan, hal ini disebabkan karena cara Spanner memproses APPLY_JOIN. Spanner akan menggunakan masing masing baris dari tabel users dan menggunakan nya untuk melakukan sub query ke tabel threads berdasarkan kolom author_id. Karena tidak ada index di kolom author_id maka Spanner akan memindai semua baris di tabel threads untuk mencocokkan users.id dengan threads.author_id. Karena di tabel users ada seribu baris dan di tabel threads ada seribu baris, total baris yang di baca oleh Spanner adalah seribu baris di tabel users dan seribu dikali seribu baris di tabel threads. Cara kerja LOOP_JOIN mirip dengan APPLY_JOIN. Untuk kasus query ini, JOIN_TYPE yang paling tepat kita gunakan adalah HASH_JOIN.

Penutup

Dengan memperhatikan index, tipe join, urutan tabel, dan urutan kolom di query-query yang Tim kami gunakan. Kami bisa mengurangi beban Spanner yang kami gunakan dari sepuluh node Spanner dengan penggunaan CPU rata-rata 50% menjadi 3 node Spanner dengan penggunaan CPU rata-rata di bawah 10%. Kami mengandalkan query explanation yang disediakan di web console Spanner untuk menganalisa apakah query kami optimal, apakah index yang ada terpakai dengan baik, dan sebagainya.

Selalu periksa execution plan dari query kita di DBMS yang kita gunakan. Tidak selamanya index akan mempercepat pelaksanaan query kita, tidak selalu DBMS bisa dengan pintar mengoptimalkan pelaksanaan query kita.