Kueri yang memerlukan waktu milidetik pada tahun lalu dapat memerlukan waktu beberapa menit saat ini, hanya karena tabelnya bertambah. Pengoptimalan kueri SQL bukan tentang trik cerdas dan lebih banyak tentang proses yang berulang: cari tahu apa yang dilakukan database, hapus pekerjaan yang terbuang, dan periksa apakah itu membantu.

Kami menguji setiap teknik di bawah ini pada satu juta baris yang dihasilkan di SQLite 3.50. Pengaturan waktu adalah yang terbaik dari beberapa proses yang dijalankan dari satu laptop, menggunakan database dalam memori. Mereka tidak akan cocok dengan sistem Anda, dan PostgreSQL, MySQL, dan SQL Server masing-masing memiliki pengoptimal dan format paketnya sendiri. Fokus pada ukuran setiap perbaikan dan prinsip di baliknya.

Sekilas tentang hasil

Pengaturan tes

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Proses optimasi sederhana

1. Temukan kueri yang penting. Gunakan log atau pemantauan kueri yang lambat. Kueri 200 ms yang dijalankan seribu kali per menit mungkin lebih penting daripada laporan bulanan 10 detik.
2. Baca rencananya. Lihat apa yang sebenarnya dilakukan database.
3. Ubah satu hal. Tambahkan indeks atau tulis ulang bagian kueri.
4. Ukur lagi, pada data realistis. Rencana untuk 100 baris pengujian memberi tahu Anda sedikit tentang satu juta.
5. Perhatikan seiring berjalannya waktu. Rencana dapat berubah seiring bertambahnya data dan perubahan statistik.

Baca rencana eksekusi

Rencana eksekusi adalah strategi langkah demi langkah database untuk menjalankan kueri, termasuk indeks mana yang digunakan dan urutan penggabungan tabel. Awali kueri untuk melihatnya:

Perhatikan bahwa EXPLAIN ANALYZE di PostgreSQL benar-benar menjalankan kueri, jadi berhati-hatilah saat menggunakannya dengan UPDATE atau DELETE. Inilah yang harus dicari terlebih dahulu:

Pemindaian penuh tidak otomatis buruk. Jika kueri memerlukan sebagian besar tabel kecil, pemindaian bisa menjadi pilihan tercepat. Di EXPLAIN ANALYZE PostgreSQL, bandingkan juga baris perkiraan dengan baris sebenarnya. Perbedaan besar sering kali berarti statistik tabel sudah ketinggalan zaman.

Tambahkan indeks yang tepat

Kueri ini menemukan pesanan satu pelanggan:

SELECT id, total FROM orders WHERE customer_id = 123;

Sebelum menambahkan indeks, rencananya adalah pemindaian penuh:

SCAN orders
-- 20 rows in 73 ms

Kemudian kami menambahkan indeks pada kolom filter:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

20 baris yang sama muncul ribuan kali lebih cepat, karena database tidak lagi membaca satu juta baris untuk menemukannya. Tidak ada teknik lain dalam artikel ini yang memberi kami kemenangan lebih besar. Kandidat pertama yang baik adalah kolom yang digunakan di WHERE, JOIN dan ORDER BY, terutama ketika kolom tersebut mempersempit hasilnya menjadi beberapa baris. Panduan kami untuk indeks SQL menjelaskan cara kerjanya.

Jangan bungkus kolom yang diindeks dalam fungsi

Berikut dua cara untuk menemukan pesanan bulan Maret, dengan indeks pada created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Keduanya menghasilkan baris yang sama, namun baris pertama membutuhkan waktu sekitar tiga kali lebih lama. Rencana tersebut menunjukkan alasannya: dengan fungsi pada kolom, database tidak dapat melompat ke posisi dalam indeks yang diurutkan, sehingga harus SCAN menelusuri setiap entri. Versi rentang dapat SEARCH langsung ke tempat yang tepat. Kondisi yang dapat menggunakan indeks dengan cara ini disebut sargable.

Masalah yang sama muncul dengan pola umum berikut:

Kami memeriksa yang terakhir juga. Wildcard terdepan dihasilkan SCAN customers, karena indeks yang diurutkan dari huruf pertama tidak dapat membantu kecocokan di akhir.

Indeks komposit

Saat kueri memfilter pada dua kolom, indeks satu kolom hanya melakukan sebagian pekerjaan:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

Urutan kolom sangat penting. Indeks pada (status, created) berfungsi seperti buku telepon yang diurutkan berdasarkan nama belakang dan kemudian nama depan. Anda dapat mencari nama keluarga saja, tetapi tidak hanya nama depan saja. Permintaan kami pada status saja dapat menggunakan indeks ini. Memfilter pada created saja umumnya tidak dapat menggunakannya secara efisien. Sebagai aturan praktis, letakkan kolom yang diuji dengan kesetaraan terlebih dahulu dan kolom rentang setelahnya.

Rencana tersebut juga menyatakan mencakup indeks, artinya indeks menampung semua kueri yang diperlukan, sehingga tabel itu sendiri tidak perlu dibaca. Memilih hanya kolom yang Anda perlukan membuat hal ini lebih mungkin terjadi, yang merupakan alasan lain untuk dihindari SELECT *.

Penomoran halaman lebih cepat

Menggunakan OFFSET untuk halaman dalam adalah biaya tersembunyi, karena database masih harus melewati semua baris yang dilewati:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

Penomoran halaman keyset tetap cepat tidak peduli seberapa dalam Anda melakukannya. Ini juga menghindari perpindahan baris antar halaman saat data baru ditambahkan. Kerugiannya adalah pengguna tidak dapat langsung membuka halaman 5.000, yang jarang merupakan kebutuhan nyata. Pada tabel dengan baris yang lebar, atau dengan lebih banyak baris yang harus dilewati, jaraknya bisa jauh lebih besar dibandingkan dengan pengujian kami.

Tulis pertanyaan yang lebih ramping

• Pilih hanya kolom yang Anda perlukan. SELECT * mengirimkan lebih banyak data, dan ini dapat mencegah indeks menutupi kueri.
• Filter lebih awal. Gunakan WHERE untuk menghapus baris sebelum mengelompokkan, dan HAVING hanya untuk kondisi agregat. Lihat SQL GROUP BY dan HAVING.
• Gunakan LIMIT bila Anda hanya memerlukan beberapa baris.
• Pilih UNION ALL bila Anda tidak perlu menghapus duplikat. Pengujian UNION vs UNION ALL kami menunjukkan apa yang dapat dihemat.
• Hindari DISTINCT yang tidak perlu. Seringkali menyembunyikan gabungan yang menghasilkan duplikat.
• Gunakan EXISTS untuk pemeriksaan keberadaan. Ini bisa berhenti pada kecocokan pertama, seperti yang dijelaskan dalam panduan kami untuk subkueri SQL.
• Perhatikan N+1 kueri. Satu kueri per baris dalam satu loop, biasanya disebabkan oleh kode aplikasi, sering kali merupakan masalah sebenarnya. Satu kueri gabungan atau kumpulan dapat menggantikan ratusan perjalanan bolak-balik.

Bergabung

Indeks kolom tempat Anda bergabung. Kami menjalankan laporan total pembelanjaan untuk pelanggan di satu kota, dan rencana tersebut sesuai dengan harapan Anda:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Ini memindai pelanggan, lalu menggunakan indeks untuk menemukan pesanan masing-masing. Tanpa indeks pada orders.customer_id, database harus melakukan lebih banyak pekerjaan untuk setiap pelanggan. Indeks pada kunci asing adalah salah satu tambahan paling umum dan berharga yang dapat Anda buat. Untuk menyegarkan kembali pemahaman Anda tentang dasar-dasarnya, lihat penjelasan gabungan SQL.

Ketika indeks merugikan Anda

Indeks tidak gratis, jadi jangan tambahkan satu indeks untuk setiap kolom:

• Penulisan lebih lambat. Setiap penyisipan, pembaruan, dan penghapusan juga harus memperbarui setiap indeks.
• Lebih banyak penyimpanan dan memori. Indeks dapat bertambah besar di tabel besar.
• Sedikit manfaat pada kolom yang tidak selektif. Indeks pada kolom benar atau salah mungkin tidak membantu jika sebagian besar baris cocok.
• Redundansi. Indeks pada (a) sering kali tidak diperlukan jika Anda sudah memilikinya di (a, b).

Periksa statistik penggunaan secara berkala, dan hapus indeks yang tidak ada gunanya. Dan selalu perbarui statistik dengan ANALYZE atau database Anda yang setara, karena pengoptimal mengandalkan statistik tersebut untuk memilih paket yang baik. Terkadang bagian yang lambat adalah pertikaian, bukan pertanyaan. Penguncian transaksi yang berjalan lama dapat membuat kueri cepat terasa lambat.

Kesalahan umum

• Mengoptimalkan tanpa mengukur. Banyak tebakan yang meyakinkan ternyata salah.
• Menguji dengan data kecil. Rencana dan waktu berubah secara dramatis seiring bertambahnya tabel.
• Mempercayai tip untuk database lain. Saran untuk satu mesin mungkin tidak berlaku untuk mesin lainnya.
• Menambahkan indeks secara membabi buta. Masing-masing memperlambat penulisan.
• Mengabaikan efek pengoperasian pertama. Cache membuat pengoperasian kedua lebih cepat, jadi jalankan beberapa kali.

Penutup

Sebagian besar kueri lambat memiliki penyebab biasa: indeks hilang, fungsi pada kolom, atau terlalu banyak data yang diambil. Ambil kueri nyata Anda yang paling lambat minggu ini, jalankan EXPLAIN di atasnya, dan cari pemindaian penuh pada tabel besar. Perbaiki satu hal, ukur lagi, dan catat apa yang Anda ubah.

Pertanyaan yang sering diajukan

Teruslah belajar