Database Query Optimization

Aplikasi web yang lambat hampir selalu menyalahkan satu tersangka yang sama: database. Tidak karena database-nya jelek, tetapi karena query yang kita tulis boros secara diam-diam — memuat kolom yang tidak dipakai, memindai tabel penuh, atau menghitung ulang angka yang sama di setiap permintaan. Chapter ini merangkum strategi optimasi query yang berlaku untuk query builder maupun Eloquent, dengan setiap klaim didukung pengukuran nyata pada tabel posts berisi 10.000 baris (kolom isi ± 5,7 KB per baris). Mekanisme dasar seperti toSql() dan query log sudah kita bahas di Query Optimization ; di sini kita naik ke taktik yang lebih luas, karena N+1 saja tidak menghabiskan daftar penyakit query.

Disiplin Pengukuran: Dengarkan Dulu, Baru Perbaiki #

Sebelum mengubah satu baris query, pasang alat ukurnya. DB::listen() adalah bentuk paling ringan — callback yang dipanggil untuk setiap query, lengkap dengan properti time dalam milidetik. Kombinasikan dengan ambang batas dan kita punya slow-query logger sederhana:

use App\Models\Post;
use Illuminate\Support\Facades\DB;

DB::listen(function ($query) {
    if ($query->time >= 5.0) {
        echo sprintf("[SLOW %6.2f ms] %s\n", $query->time, $query->sql);
    }
});

DB::table('posts')->get(); // boros: SELECT * 10.000 baris besar
Post::where('judul', 'Post Latihan 05000')->value('id'); // gesit

Output yang Dihasilkan:

[SLOW  50.85 ms] select * from "posts"

Logger ini diam saat query yang cepat lewat, lalu berteriak hanya pada query yang melewati ambang 5 ms. Di aplikasi nyata, callback semacam ini biasa dipasang di service provider untuk menulis ke log alih-alih echo. Aturannya satu: tidak ada optimasi tanpa bilangan sebelum-sesudah.

Seleksi Kolom: Jangan Angkut yang Tak Dipakai #

SELECT * memuat semua kolom — termasuk isi sepanjang 5,7 KB yang tidak dibutuhkan halaman daftar. Mari kita bandingkan pada 10.000 baris yang sama:

use Illuminate\Support\Facades\DB;

// pembantu: jalankan beberapa kali, ambil waktu tercepat
function ukur(callable $fn, int $ulang = 5): float
{
    $terbaik = INF;
    for ($i = 0; $i < $ulang; $i++) {
        $mulai = hrtime(true);
        $fn();
        $terbaik = min($terbaik, (hrtime(true) - $mulai) / 1e6);
    }
    return $terbaik;
}

echo sprintf("SELECT * (semua kolom): %5.1f ms\n",
    ukur(fn () => DB::table('posts')->get()->count()));

echo sprintf("SELECT id, judul, views: %5.1f ms\n",
    ukur(fn () => DB::table('posts')->select('id', 'judul', 'views')->get()->count()));

Output yang Dihasilkan:

SELECT * (semua kolom):  45.9 ms
SELECT id, judul, views:  19.0 ms

Waktu turun lebih dari separuh, dan memori jauh lebih dramatis: memuat 10.000 baris lengkap menghabiskan 88,0 MB memori proses, sementara tiga kolom saja tidak sampai 0,1 MB. Kolom teks besar seperti isi adalah biang keroknya — jangan diangkat dari database kalau halamannya hanya butuh judul.

Hindari Fungsi pada Kolom Terindeks di WHERE #

Kolom yang sudah diindeks kehilangan manfaat indeksnya begitu dibungkus fungsi. Kita buktikan dengan EXPLAIN QUERY PLAN pada tabel yang sama:

use Illuminate\Support\Facades\DB;

DB::statement('CREATE INDEX posts_judul_index ON posts (judul)');

$r = DB::select("explain query plan select id from posts where judul = 'Post Latihan 05000'");
echo $r[0]->detail, "\n";

$r = DB::select("explain query plan select id from posts where lower(judul) = 'post latihan 05000'");
echo $r[0]->detail, "\n";

Output yang Dihasilkan:

SEARCH posts USING COVERING INDEX posts_judul_index (judul=?)
SCAN posts USING COVERING INDEX posts_judul_index

Query pertama melakukan SEARCH — lompat langsung ke baris yang cocok lewat index. Query kedua dipaksa SCAN membaca seluruh index, karena lower(judul) harus dihitung dulu untuk setiap entri sebelum bisa dibandingkan — hasil fungsi tidak ada di pohon index. Di 10.000 baris, waktunya 0,037 ms berubah menjadi 0,567 ms — 15 kali lebih lambat, dan jurangnya melebar linear dengan ukuran tabel. Solusinya: bandingkan kolom secara langsung, atau simpan versi yang sudah dinormalisasi di kolom tersendiri.

Offset vs Cursor pada Halaman Dalam #

offset() membaca lalu membuang semua baris sebelum posisi awal. Di halaman awal ini murah; di halaman ke-1.000 ia menjadi bencana. Bandingkan dua cara mengambil 10 baris “setelah” baris ke-9.990:

use Illuminate\Support\Facades\DB;

// cara offset: lewati 9.990 baris
DB::table('posts')->orderBy('id')->offset(9990)->limit(10)->get();

// cara cursor: mulai setelah id tertentu
DB::table('posts')->where('id', '>', 9990)->orderBy('id')->limit(10)->get();

Output yang Dihasilkan:

OFFSET 9990 (halaman 1000):     5.27 ms
WHERE id > 9990:               0.06 ms

88 kali lebih cepat, karena WHERE id > ? memakai index untuk melompat langsung. Method cursorPaginate() memakai prinsip ini secara otomatis — mekanisme lengkapnya ada di chapter Pagination . Komprominya, cursor tidak tahu total halaman; untuk daftar yang butuh nomor halaman, offset tetap sah di halaman-halaman awal.

Memproses Data Besar: chunkById() dan lazy() #

Untuk pekerjaan yang memang mengolah seluruh tabel, get() adalah bunuh diri memori. Pengukuran nyata pada 10.000 post dengan Eloquent:

chunkById() memproses tabel per potongan dengan WHERE id > ? — memori tetap kecil berapa pun ukuran tabel. lazy() memberi antarmuka koleksi yang nyaman di atas mekanisme serupa. Waktu total hampir sama; yang berubah drastis adalah memori — dan di tabel jutaan baris, memorilah yang menentukan hidup mati proses.

Menghitung dengan Aman: Count Caching #

Post::count() pada primary key cepat (0,06 ms). Namun Post::where('published', true)->count() memindai 10.000 baris tanpa index dan tercatat 5,39 ms. Angka ini dibayar ulang di setiap kunjungan — widget sidebar yang menampilkannya menghabiskan lebih dari setengah detik waktu database per 100 kunjungan. Dua jalan keluarnya: simpan hasil hitungan di cache dengan Cache::remember, atau pertahankan kolom penentu (posts_count di tabel categories) yang di-update lewat increment()/decrement() saat data berubah. Strategi cache lengkapnya kita bahas di chapter Application Caching .

Rangkaian di atas — mendengarkan query, menyeleksi kolom, menghormati index, mengganti offset — adalah taktik yang berlaku di semua database. Namun taktik paling ampuh belum kita sentuh: menambahkan index yang tepat pada kolom WHERE dan ORDER BY, yang akan mendapat panggung penuh di chapter berikutnya.