Pola Umum Masalah SQL

Ada beberapa permasalahan yang umum terjadi pada saat menulis query di SQL. Masalah tersebut dapat membuat masalah yang lebih serius pada aplikasi. Beberapa masalah ini sering gw temui saat develop aplikasi. Seringkali masalah-masalah ini terjadi karena kita ga mengikuti best practice dari penggunaan database. Ada beberapa pola yang umum jadi penyebab terjadinya masalah tersebut.

N+1

N+1 adalah melakukan query berulang kali sebanyak N kali untuk tiap baris data. Ini merupakan kesalahan umum yang terjadi saat develop aplikasi. Misalnya kita nyari 100 user dari database. Tiap data tersebut kita cari lagi masing-masing data email dan data alamatnya lewat looping.

@RequiredArgsConstructor
public static class UserListUseCase{
	private final UserGateway gateway;

	public void execute(){
		List<User> userList = gateway.findUsers(100);
		for(User user : userList){
			System.out.println("user = " + user);

			List<Email> userEmails = gateway.findEmailsByUserId(user.userId());
			System.out.println("userEmails = " + userEmails);

			List<Address> userAddresses = gateway.findAddressesByUserId(user.userId());
			System.out.println("userAddresses = " + userAddresses);
		}
	}

}

Ini dapat menyebabkan system jadi berat karena membuat aplikasi bolak-balik melakukan eksekusi query ke database di tiap user. Jadi kita query buat nyari user dulu 1x, lalu tiap 100 data kita looping untuk nyari masing-masing data email dan alamat sebanyak 100x. Kalau misalkan data usernya 1000 berarti kita perlu query 1000x lagi😱. Solusinya, kita perlu batch query tersebut lalu mapping berdasarkan user id.

public void execute(){
	List<User> userList = gateway.findUsers(100);
	Set<String> userIds = userList.stream().map(User::userId).toSet();

	List<Email> emails = gateway.findEmailsByUserIds(userIds);
	Map<String, List<Email>> emailsByUserId = emails.stream().collect(Collectors.groupingBy(Email::userId));

	List<Address> addresses = gateway.findAddressesByUserIds(userIds);
	Map<String, List<Address>> addressesByUserId = addresses.stream().collect(Collectors.groupingBy(Address::userId));

	for(User user : userList){
		System.out.println("user = " + user);

		List<Email> userEmails = emailsByUserId.get(user.userId());
		System.out.println("userEmails = " + userEmails);

		List<Address> userAddresses = addressesByUserId.get(user.userId());
		System.out.println("userAddresses = " + userAddresses);
	}
}

Sekarang ga perlu query lagi sebanyak N kali sesuai jumlah data sehingga performa jadi lebih cepat dan efisien.

Missing Index

Saat data masih sedikit mungkin belum ada masalah. Ketika data udah mulai ratusan ribu maka akan terasa dampaknya. Di sini kita perlu analisa kolom apa yang sering dijadiin filter. Misalkan pada tabel user sering dilakukan pencarian menggunakan kolom nama user. Berarti kita perlu tambahin index pada kolom tersebut.

CREATE INDEX users_full_name_index ON users(full_name)
;

Perlu dicek juga pakai Explain untuk menganalisa cost apakah query sudah optimal atau belum.

EXPLAIN (ANALYZE, VERBOSE, FORMAT JSON)
SELECT
    u.id,
    u.full_name
FROM users u
WHERE
    u.full_name = 'Admin'
;

Function di Index

Misalkan kita udah apply index ke sebuah kolom. Kemudian saat query kita menggunakan function pada kolom tersebut. Index jadi ga berlaku di sini. Contohnya kita ingin melakukan pencarian menggunakan function lower() agar data yang difilter case insensitive.

SELECT
    u.id,
    u.full_name
FROM users u
WHERE
    lower(u.full_name) = 'admin'
;

Untuk mengatasinya, maka kita perlu index menggunakan function tersebut.

CREATE INDEX users_full_name_index ON users(lower(full_name))
;

Dengan begini saat kita mengeksekusi query menggunakan function yang sama akan terindex. Oh ya, pada kasus ini hanya berlaku pada PostgreSql. Untuk MySql by default kolomnya udah case insensitive, jadi ga perlu pakai function lagi saat query.

OR in WHERE Clause

Menggunakan OR pada where clause juga dapat membuat eksekusi query jadi lambat pada data yang banyak. Database akan melakukan pengecekan semua data untuk memastikan data yang difilter memenuhi kondisi OR pada where clause. Penggunaan index jadi ngaruh di sini.

SELECT
    p.id,
    p.name,
    p.brand_id
FROM products p
WHERE
    price > 1000000 OR
    brand_id = 2
;

Meskipun price dan brand id diindex, ini akan tetap full scan. Solusinya, kita perlu pecah jadi 2 query dan gunakan Union.

SELECT
    p.id,
    p.name,
    p.brand_id
FROM (SELECT
          p.id,
          p.name,
          p.brand_id
      FROM products p
      WHERE
          price > 1000000
      UNION
      SELECT
          p.id,
          p.name,
          p.brand_id
      FROM products p
      WHERE
          brand_id = 2) p
;

Dengan begini query akan dipecah jadi 2. Masing-masing query akan menggunakan index price dan brand id sebelum datanya digabung. Karena udah menggunakan index, maka ini akan lebih cepat dibanding sebelumnya. Oh ya, ini hanya dilakukan jika memang terjadi masalah pada query sebelumnya. Jika datanya masih kecil atau menggunakan OR masih baik-baik saja maka belum perlu diganti jadi pakai Union.

Large Offset

Biasanya ini terjadi pada saat pagination. Saat query menggunakan offset & limit, sebenarnya database tetap mengambil data sebanyak limit + offset sebelum difilter lagi sesuai offset & limit. Misalnya kita query dengan limit = 10 dan offset = 60, maka database akan mengambil 70 data, lalu difilter untuk mengambil 10 data terakhir. Masalahnya, jika offsetnya gede seperti 1juta pada tabel dengan data yang banyak, maka database akan mengambil 1juta data terlebih dulu sebelum difilter 10 data terakhir sehingga performa jadi berat.

SELECT
    o.order_no,
    o.note,
    o.ordering_date
FROM orders o
ORDER BY o.ordering_date
LIMIT 10 OFFSET 1000000
;

Solusinya, ini harus dihindari. Jika ini terjadi pada fitur yang butuh pagination, maka perlu alternatif pagination yang lebih baik. Untuk solusinya gw pernah tulis di tulisan tentang Numeric Pagination.

Select All

Kalau ini masalahnya terkait memori. Melakukan selection dengan symbol * memang sangat simple digunakan. Namun ini ga efisien jika ada kolom yang ternyata ga digunakan. Secara teori artinya semua kolom akan di-load dan disimpan di memori.

SELECT *
FROM products p
WHERE
    price > 1000000
;

Pada query di atas artinya semua kolom akan diambil dari database dan akan disimpan di memori aplikasi. Misalnya pada kasus tertentu hanya product id yang ingin digunakan. Penggunaan memori untuk kolom-kolom lainnya jadi mubazir. Untuk itu kita harus bikin projection tiap mengambil data dari database agar hanya kolom-kolom yang benar-benar dibutuhkan aja yang diambil.

SELECT p.id
FROM products p
WHERE
    price > 1000000
;

NOT IN in WHERE Clause

Bagian ini masalahnya bukan di performa, tapi hasil query yang ga sesuai harapan. Value null ga bisa digunakan pada filter ini. Umumnya terjadi saat menggunakan subquery. Contohnya pada tabel berikut:

Misalnya kita ingin mencari brand yang harga produknya ga ada yang lebih dari Rp1juta menggunakan Anti-Join dengan Not In Clause.

SELECT
    b.id,
    b.name
FROM brands b
WHERE
    b.id NOT IN (SELECT p.brand_id FROM products p WHERE p.price > 1000000)
;

Ekspektasinya adalah kita akan mendapatkan brand indofood karena produk-produknya ga ada yang lebih dari Rp1juta. Tapi hasilnya adalah kosong karena ada produk dengan brand_id bernilai null. Solusinya adalah gunakan Anti-Join dengan function Not Exists().

SELECT
    b.id,
    b.name
FROM brands b
WHERE
    NOT EXISTS (SELECT 1 FROM products p WHERE p.brand_id = b.id AND p.price > 1000000)
;

Sekarang brand indofood sudah muncul dan sudah sesuai ekspektasi. Masalah terkait ini juga udah pernah gw bahas di tulisan tentang Subquery.

Verdict

Itulah permasalahan umum terkait SQL. Kita harus waspada dan menghindari bad practice yang dapat membuat aplikasi jadi bermasalah. Analisa kembali query cost dan terapkan solusi yang sesuai.