Mencegah N+1 Query pada Inventori Game dengan Index yang Tepat

Rilis Godot 4.7 bisa menjadi pemicu bertambahnya fitur game, tetapi bottleneck performa sering justru muncul di backend: inventori, leaderboard, dan log aksi makin ramai diakses, sementara query database tetap memakai pola lama. Salah satu masalah yang paling sering muncul adalah N+1 query, yaitu ketika aplikasi mengambil satu daftar data lalu menembakkan query tambahan untuk setiap item di dalam daftar tersebut.

Pada backend inventori game, N+1 query biasanya tidak terasa saat data masih kecil. Masalah mulai terlihat ketika satu pemain punya ratusan item, guild memiliki banyak anggota, atau endpoint admin menampilkan inventori lintas pemain. Di tahap ini, perbaikan tidak cukup hanya dengan "menambah index". Anda perlu memastikan pola query-nya benar, relasi dimuat efisien, index sesuai urutan filter dan sorting, serta pagination tidak memaksa database memindai terlalu banyak baris.

Studi Kasus: Gejala N+1 Query pada Inventori Game

Anggap sebuah backend melayani endpoint berikut:

GET /players/{player_id}/inventory

Tabel yang dipakai:

players: data pemain
inventory_items: item yang dimiliki pemain
item_definitions: metadata item seperti nama, rarity, stack limit
item_upgrades: level upgrade atau enchant yang menempel pada item tertentu

Pola implementasi yang bermasalah sering terlihat seperti ini:

# pseudo-code backend service
items = db.query("SELECT id, item_definition_id, quantity FROM inventory_items WHERE player_id = ? ORDER BY created_at DESC LIMIT 50", [player_id])

result = []
for item in items:
    definition = db.query_one("SELECT id, name, rarity FROM item_definitions WHERE id = ?", [item.item_definition_id])
    upgrades = db.query("SELECT level, slot FROM item_upgrades WHERE inventory_item_id = ? ORDER BY slot ASC", [item.id])
    result.append({
        "id": item.id,
        "quantity": item.quantity,
        "definition": definition,
        "upgrades": upgrades
    })

Jika hasil awal memuat 50 item, backend bisa menghasilkan:

1 query untuk daftar inventory_items
50 query ke item_definitions
50 query ke item_upgrades

Totalnya menjadi 101 query untuk satu request. Ini inti masalah N+1 query. Dalam lingkungan pengembangan lokal, mungkin masih terlihat cepat. Namun saat traffic naik dan data bertambah, latency akan melonjak karena:

jumlah round-trip ke database bertambah drastis,
connection pool lebih cepat penuh,
CPU database tersita untuk banyak query kecil,
cache aplikasi sulit menolong jika pola akses sangat tersebar.

Tanda-tanda yang sering terlihat

Endpoint inventori lambat hanya untuk akun lama dengan banyak item.
Rata-rata query tidak terlalu lambat, tetapi total query per request sangat tinggi.
Latency naik tajam saat ukuran page dari 20 ke 100.
Database menampilkan banyak query serupa dengan parameter berbeda dalam waktu berdekatan.
CPU database meningkat walau throughput API tidak naik terlalu tinggi.

Cara Menemukan N+1 Query di Produksi

Jangan mulai dari asumsi. Mulailah dari pengamatan. Dua alat paling berguna adalah slow query log dan EXPLAIN.

1. Periksa slow query log

Slow query log membantu menemukan query yang memang lambat. Namun untuk kasus N+1, pelajarannya bukan hanya query mana yang paling mahal, tetapi juga apakah ada banyak query kecil yang berulang.

Contoh pola yang perlu dicurigai:

SELECT id, item_definition_id, quantity
FROM inventory_items
WHERE player_id = ?
ORDER BY created_at DESC
LIMIT 50;

SELECT id, name, rarity
FROM item_definitions
WHERE id = ?;

SELECT level, slot
FROM item_upgrades
WHERE inventory_item_id = ?
ORDER BY slot ASC;

Jika query kedua dan ketiga muncul puluhan kali dalam satu request, itu petunjuk kuat adanya N+1.

Catatan: query individual untuk item_definitions mungkin sangat cepat karena berdasarkan primary key. Tetap saja, ratusan query cepat bisa menjadi lambat secara total.

2. Tambahkan logging jumlah query per request

Slow query log saja sering belum cukup. Praktik yang lebih berguna adalah mencatat:

request path,
durasi total request,
jumlah query SQL,
waktu total yang dihabiskan di database.

Jika endpoint inventori secara konsisten menghasilkan puluhan hingga ratusan query per request, Anda punya masalah desain akses data, bukan sekadar query lambat tunggal.

3. Gunakan EXPLAIN pada query inti

Setelah pola N+1 ditemukan, fokus ke query yang seharusnya menjadi query utama setelah refactor. Jalankan EXPLAIN untuk memeriksa apakah database memakai index yang sesuai, apakah terjadi full scan, dan apakah sorting memerlukan kerja tambahan.

Contoh query utama inventori:

SELECT id, player_id, item_definition_id, quantity, created_at
FROM inventory_items
WHERE player_id = ? AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Yang ingin Anda pastikan dari EXPLAIN secara umum:

filter player_id dipakai oleh index,
kondisi deleted_at IS NULL tidak membuat optimizer terpaksa membaca terlalu banyak baris,
urutan ORDER BY created_at DESC, id DESC selaras dengan index jika memungkinkan,
jumlah baris yang dipindai mendekati jumlah hasil, bukan jauh lebih besar.

Perbaikan Tahap 1: Ganti N+1 dengan Eager Loading atau JOIN

Perbaikan pertama hampir selalu berupa mengurangi jumlah query. Tujuannya bukan membuat satu query raksasa tanpa pertimbangan, tetapi mengubah puluhan query kecil menjadi beberapa query yang terkontrol.

Pendekatan A: Eager loading dalam 2-3 query

Pola ini cocok jika Anda ingin menjaga struktur aplikasi tetap mudah dibaca dan menghindari hasil JOIN yang terlalu terduplikasi.

# pseudo-code backend service
items = db.query(""
    SELECT id, item_definition_id, quantity, created_at
    FROM inventory_items
    WHERE player_id = ? AND deleted_at IS NULL
    ORDER BY created_at DESC, id DESC
    LIMIT 50
"", [player_id])

item_definition_ids = unique([item.item_definition_id for item in items])
inventory_item_ids = [item.id for item in items]

definitions = db.query(""
    SELECT id, name, rarity
    FROM item_definitions
    WHERE id IN (...)
"", [item_definition_ids])

upgrades = db.query(""
    SELECT inventory_item_id, level, slot
    FROM item_upgrades
    WHERE inventory_item_id IN (...)
    ORDER BY inventory_item_id ASC, slot ASC
"", [inventory_item_ids])

Hasilnya turun dari 101 query menjadi sekitar 3 query. Ini biasanya sudah memberi dampak besar.

Pendekatan B: JOIN untuk kebutuhan list yang ringkas

Jika endpoint hanya perlu metadata item tanpa struktur relasi yang rumit, JOIN sering lebih sederhana dan lebih cepat.

SELECT
    ii.id,
    ii.quantity,
    ii.created_at,
    d.id AS definition_id,
    d.name,
    d.rarity
FROM inventory_items ii
JOIN item_definitions d ON d.id = ii.item_definition_id
WHERE ii.player_id = ?
  AND ii.deleted_at IS NULL
ORDER BY ii.created_at DESC, ii.id DESC
LIMIT 50;

Dengan JOIN, Anda menghindari query per item ke tabel definisi. Untuk relasi satu-ke-banyak seperti item_upgrades, hati-hati: JOIN langsung bisa menggandakan baris inventori jika satu item punya beberapa upgrade.

Kapan memilih eager loading, kapan JOIN?

Pilih eager loading jika relasi satu-ke-banyak cukup besar, hasil perlu dibentuk ke objek terpisah, atau Anda ingin menghindari duplikasi baris.
Pilih JOIN jika relasi satu-ke-satu atau banyak-ke-satu, dan data yang dibutuhkan memang cocok ditampilkan sebagai satu list datar.

Kesalahan umum setelah menghapus N+1

Mengganti 100 query kecil dengan 1 query JOIN yang menghasilkan ledakan jumlah baris.
Mengambil terlalu banyak kolom padahal yang dipakai hanya sedikit.
Memakai SELECT * pada endpoint panas.
Menganggap semua eager loading otomatis efisien tanpa memeriksa EXPLAIN.

Perbaikan Tahap 2: Pasang Composite Index yang Sesuai Query

Setelah jumlah query berkurang, bottleneck berikutnya biasanya ada pada query utama list inventori. Di sinilah index yang tepat berperan. Bukan sekadar menambahkan index di semua kolom, melainkan menyelaraskan index dengan pola WHERE, ORDER BY, dan kadang JOIN.

Contoh query yang sering dipakai

SELECT id, item_definition_id, quantity, created_at
FROM inventory_items
WHERE player_id = ?
  AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Untuk query seperti ini, index tunggal pada player_id saja sering belum cukup. Database mungkin masih harus menyortir banyak baris milik pemain tersebut setelah filtering. Dalam banyak kasus, Anda perlu composite index yang mengikuti pola akses:

(player_id, deleted_at, created_at, id)

Mengapa urutan kolom penting?

player_id diletakkan di depan karena menjadi filter utama.
deleted_at berguna jika soft delete sering dipakai dan hampir semua query hanya ingin data aktif.
created_at dan id membantu pengurutan stabil untuk list terbaru.

Urutan ideal tetap bergantung pada engine database, selektivitas data, dan bentuk query aktual. Karena itu, EXPLAIN wajib dipakai setelah index dibuat.

Index untuk tabel relasi pendukung

Perbaikan N+1 sering melibatkan query IN (...) ke tabel anak. Contohnya:

SELECT inventory_item_id, level, slot
FROM item_upgrades
WHERE inventory_item_id IN (...)
ORDER BY inventory_item_id ASC, slot ASC;

Index yang masuk akal untuk pola ini adalah:

(inventory_item_id, slot)

Dengan begitu, database bisa lebih efisien saat mengambil upgrade per item sekaligus mempertahankan urutan slot.

Index untuk leaderboard atau log aksi

Pola serupa juga muncul di backend game lain:

Leaderboard: filter berdasarkan season atau mode, urut berdasarkan score dan tie-breaker.
Log aksi: filter berdasarkan player_id atau entity_id, urut berdasarkan waktu terbaru.

Contoh pola index yang sering relevan:

(season_id, score DESC, player_id) untuk leaderboard per season
(player_id, created_at DESC, id DESC) untuk riwayat aksi pemain

Prinsipnya sama: jangan menebak. Cocokkan index dengan query yang benar-benar dominan.

Trade-off: index tambahan memperlambat write

Setiap index baru mempercepat sebagian read, tetapi ada biaya:

INSERT lebih mahal karena index juga harus diperbarui.
UPDATE pada kolom terindeks dapat memicu kerja tambahan.
Storage bertambah.
Vacuum/maintenance atau proses serupa dapat menjadi lebih berat tergantung engine.

Untuk backend inventori game, trade-off ini nyata karena tabel seperti inventory_items dan action_logs biasanya sering ditulis. Karena itu, hindari kebiasaan menambah banyak index "untuk berjaga-jaga". Tambahkan hanya untuk query yang terbukti penting dan sering dipakai.

Perbaikan Tahap 3: Redesign Query Saat Data Tumbuh

Ada titik di mana eager loading dan index saja belum cukup. Penyebabnya bukan lagi N+1 murni, melainkan desain query yang tidak skala terhadap pertumbuhan data.

Kasus 1: OFFSET makin mahal

Query seperti ini tampak normal:

SELECT id, item_definition_id, quantity, created_at
FROM inventory_items
WHERE player_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Masalahnya, database sering tetap harus melewati banyak baris sebelum sampai ke halaman yang diminta. Saat inventori, log, atau leaderboard membesar, pagination berbasis offset menjadi makin mahal.

Di titik ini, pertimbangkan keyset pagination atau cursor-based pagination.

SELECT id, item_definition_id, quantity, created_at
FROM inventory_items
WHERE player_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dengan pendekatan ini, database tidak perlu menghitung dan melewati ribuan baris lebih dulu. Query juga lebih selaras dengan composite index seperti (player_id, created_at, id).

Kapan offset masih layak dipakai?

Jumlah data per pemain kecil dan stabil.
Halaman dalam biasanya jarang diakses.
Anda butuh kemampuan lompat langsung ke halaman tertentu untuk dashboard admin kecil.

Kapan keyset lebih tepat?

Data terus bertambah dan endpoint sering mengambil "halaman berikutnya".
Urutan data bersifat kronologis atau berdasarkan skor.
Latency halaman dalam mulai jauh lebih buruk daripada halaman pertama.

Kasus 2: Query summary dan detail dicampur

Kesalahan lain adalah satu endpoint inventori mencoba memuat:

daftar item,
metadata item lengkap,
upgrade,
harga pasar,
riwayat perubahan item.

Jika semua digabung dalam satu operasi sinkron, query menjadi berat dan sulit dioptimalkan. Solusinya bisa berupa:

memisahkan endpoint list dan endpoint detail,
mengambil field minimum untuk layar utama,
menyimpan hasil agregasi tertentu di tabel terpisah bila memang dibutuhkan sangat sering.

Ini bukan berarti selalu harus melakukan denormalisasi. Tetapi untuk backend game dengan traffic tinggi, mendesain ulang bentuk baca sering lebih efektif daripada terus menambah index.

Contoh Sebelum dan Sesudah

Sebelum

1 query mengambil 50 inventory item.
50 query mengambil item definition per item.
50 query mengambil upgrade per item.
Index hanya ada di primary key dan player_id.
Pagination memakai offset untuk halaman dalam.

Dampaknya:

jumlah query per request sangat tinggi,
latency memburuk untuk akun lama,
database sibuk melayani query berulang,
halaman dalam makin lambat seiring pertumbuhan data.

Sesudah

Query list inventori dipadatkan menjadi 1 query utama.
Relasi dimuat via eager loading terkontrol atau JOIN sesuai kebutuhan.
Composite index disesuaikan dengan WHERE + ORDER BY.
Query upgrades memakai index (inventory_item_id, slot).
Pagination halaman beruntun diganti ke keyset.

Hasil yang biasanya terlihat:

jumlah query per request turun drastis,
waktu total database per request lebih stabil,
pertumbuhan data tidak langsung menghancurkan latency,
connection pool lebih sehat di bawah beban.

Poin pentingnya: performa membaik karena pola akses datanya diubah, bukan hanya karena menambahkan index secara acak.

Debugging Tips yang Praktis

1. Profil per endpoint, bukan per query saja

N+1 sering lolos jika Anda hanya melihat query tunggal. Pantau:

jumlah query per request,
waktu total SQL per request,
endpoint paling sering dipanggil,
parameter yang memicu hasil besar, misalnya player dengan inventori terpadat.

2. Gunakan data yang realistis

Jangan menguji hanya dengan 10 item. Siapkan data yang mendekati kondisi produksi:

pemain dengan ratusan atau ribuan item,
item dengan banyak upgrade atau atribut,
log aksi yang terus tumbuh.

Banyak query terlihat sehat di dataset kecil, lalu berubah total di volume nyata.

3. Cek cardinality dan selektivitas index

Index pada kolom yang nilainya sangat tidak variatif belum tentu membantu. Misalnya, menaruh kolom boolean atau status yang hampir selalu sama di posisi depan index bisa membuat index kurang selektif. Pastikan susunan composite index benar-benar membantu pola filter utama.

4. Waspadai ORM yang tampak nyaman

ORM mempermudah pengembangan, tetapi relasi malas (lazy loading) sering memicu N+1 tanpa disadari. Biasakan memeriksa SQL yang benar-benar dihasilkan, bukan hanya kode modelnya.

5. Uji kembali setelah menambah index

Index yang tepat bisa mempercepat satu query tetapi memperburuk write-heavy workload. Setelah perubahan, pantau juga:

durasi insert/update,
ukuran index,
lock contention atau gejala antrian write.

Checklist Audit SQL untuk Tim Backend Game

Gunakan daftar ini saat mengaudit endpoint inventori, leaderboard, atau log aksi:

Hitung jumlah query per request. Apakah satu endpoint menghasilkan puluhan query serupa?
Cari pola query berulang. Apakah ada query per item, per pemain, atau per relasi anak?
Periksa slow query log. Apakah endpoint lambat karena satu query mahal, atau banyak query kecil?
Jalankan EXPLAIN. Apakah filter dan sorting memakai index yang benar?
Bandingkan WHERE dengan ORDER BY. Apakah composite index selaras dengan pola akses utama?
Audit relasi ORM. Apakah lazy loading memicu N+1?
Kurangi kolom yang diambil. Apakah endpoint panas masih memakai SELECT *?
Evaluasi JOIN. Apakah JOIN mengurangi query, atau malah menggandakan baris secara berlebihan?
Periksa query IN untuk relasi anak. Apakah tabel anak punya index pada foreign key dan urutan sort-nya?
Uji dengan data besar. Apakah performa tetap sehat untuk akun dengan inventori sangat besar?
Tinjau pagination. Apakah offset sudah terlalu mahal dan perlu diganti keyset?
Hitung biaya write. Apakah index tambahan memperburuk insert/update secara signifikan?
Pisahkan list dan detail. Apakah endpoint mencoba memuat terlalu banyak informasi sekaligus?
Pantau setelah deploy. Apakah query count, latency, dan beban database benar-benar turun?

Penutup

Mencegah N+1 query pada inventori game dengan index yang tepat bukan pekerjaan satu langkah. Urutannya penting: temukan gejalanya dari slow query log dan profiling request, kurangi jumlah query dengan eager loading atau JOIN, cocokkan composite index dengan pola filter dan sorting, lalu redesign query jika pertumbuhan data membuat offset dan endpoint serba-muat tidak lagi layak.

Dalam backend game yang terus berkembang, masalah performa jarang selesai hanya dengan satu trik. Tetapi jika tim Anda disiplin mengaudit query count, EXPLAIN, index, dan bentuk pagination, endpoint inventori akan tetap sehat lebih lama saat fitur dan volume data bertambah.

Quick Search

shopping_cart Keranjang