Indeks — Komposit, Parsial, Ekspresi, dan UNIQUE

Dataset untuk artikel ini — tabel perf_sales

Sebuah indeks bekerja seperti indeks di bagian belakang buku: ia membuat kamu bisa menemukan baris dengan nilai kolom tertentu secara cepat.

Artikel ini menelusuri pembuatan indeks dengan CREATE INDEX, membangun indeks komposit, parsial, ekspresi, dan UNIQUE, serta memakai EXPLAIN QUERY PLAN untuk mengonfirmasi bagaimana strategi pencarian berubah.

Sebelum menyelam, periksa definisi kolom, sampel data, dan jumlah baris dari perf_sales. (Jalankan kueri dengan benar untuk memunculkan penjelasan.)

① Pakai PRAGMA table_info(perf_sales); untuk melihat nama kolom, tipe, dan primary key.

② Pakai SELECT COUNT(*) FROM perf_sales; untuk mengonfirmasi jumlah total baris.

③ Intip 5 baris pertama dengan SELECT * FROM perf_sales LIMIT 5;. Karena perf_sales memiliki 50.000 baris, selalu sertakan LIMIT.

CREATE INDEX dan EXPLAIN QUERY PLAN — ubah full table scan menjadi index seek

Buat indeks dengan CREATE INDEX nama_indeks ON tabel(kolom);.

Ketika kamu memakai kolom yang diindeks dalam kondisi kesetaraan atau rentang WHERE, optimizer bisa memilih pencarian lewat indeks alih-alih full table scan dan langsung melompat ke baris yang cocok.

Periksa strategi pencarian dengan EXPLAIN QUERY PLAN.

Beri prefix kueri kamu dengan EXPLAIN QUERY PLAN dan kamu akan melihat rencana bagaimana baris akan ditemukan alih-alih hasil sebenarnya.

Tanpa indeks kamu akan melihat SCAN perf_sales (juga disebut FULL SCAN — periksa setiap baris di tabel), dan setelah indeks bekerja kamu akan melihat sesuatu seperti SEARCH perf_sales USING INDEX ... (juga disebut INDEX SCAN — persempit pencarian dengan indeks).

Kata-kata persis dari plan bervariasi tergantung lingkungan, jadi artikel ini memperlakukan eksekusi yang berhasil sebagai jawaban yang benar dan membaca makna plan dalam prosa dan penjelasan.

-- Plan tanpa indeks (contoh read-only terpisah)
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales (periksa setiap baris)

-- Buat indeks, lalu lihat plan yang sama
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- Catatan: jalankan DROP -> CREATE -> EXPLAIN end-to-end dalam eksekusi yang sama

Bayangkan kebutuhannya: "Kami sering mencari penjualan untuk emp_id = 7, jadi kami ingin pindah dari full table scan ke index seek." Latihan ini terbagi menjadi tiga langkah lewat tiga konsol berurutan: ① ukur baseline tanpa indeks, ② bangun indeks pada emp_id, lalu ③ ukur ulang setelah indeks ada. Konsol kedua hanya membuat indeks (biaya sekali pakai), jadi tinggalkan ia dari pembandingan kecepatan pencarian murni — bandingkan run time konsol pertama (t1) dengan konsol ketiga (t3).

① Di konsol pertama, silakan jalankan SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; dan EXPLAIN QUERY PLAN yang cocok. Pastikan kamu melihat SCAN perf_sales dan catat total run time t1 yang ditampilkan di bagian bawah konsol.

Sekarang langkah kedua dari Latihan 1: bangun indeks pada emp_id supaya konsol berikutnya bisa memakainya.

② Di konsol kedua, silakan jalankan DROP INDEX IF EXISTS ix; lalu CREATE INDEX ix ON perf_sales(emp_id); untuk membangun indeks. Catatan: run time konsol ini adalah biaya membangun indeks (sekali pakai), bukan biaya pencarian, jadi ia tidak masuk ke dalam pembandingan murni antara t1 dan t3.

Langkah terakhir dari Latihan 1: ukur ulang kueri yang sama dengan langkah ① setelah indeks ix aktif.

③ Di konsol ketiga, silakan jalankan SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; dan EXPLAIN QUERY PLAN yang sama dengan konsol pertama. Pastikan kamu sekarang melihat SEARCH perf_sales USING INDEX ix (bukan SCAN) dan bandingkan total run time t3 dengan t1 — t3 seharusnya lebih pendek karena indeks langsung melompat ke baris yang cocok.

Indeks komposit — indeks beberapa kolom bersama-sama

Menulis beberapa kolom di dalam tanda kurung — CREATE INDEX nama ON tabel(col1, col2); — menghasilkan indeks komposit (juga disebut indeks multi-kolom).

Ini mempercepat kueri yang memakai kondisi dari kolom pertama dan seterusnya, seperti WHERE col1 = ... AND col2 >= ....

Urutan kolom penting: indeks komposit tidak akan membantu kueri yang tidak memakai kolom pertama dalam klausa WHERE-nya.

Kalau pola akses kamu tetap — misalnya "kesetaraan pada emp_id dan rentang pada sale_date" — membangun indeks komposit dengan urutan itu membuat satu indeks bisa mencakup kedua kondisi.

-- Indeks komposit (contoh read-only terpisah)
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- Persempit berdasarkan region (kesetaraan), lalu persempit sale_date berdasarkan rentang

Bayangkan kebutuhannya: "Kami sering memilih sales rep dengan emp_id dan periode dengan sale_date, jadi kami ingin satu indeks yang mencakup kedua kondisi."

① Mulai dengan DROP INDEX IF EXISTS ix;.

② Buat indeks komposit bernama ix di perf_sales yang mencantumkan emp_id dan sale_date dengan urutan itu.

③ Lalu jalankan EXPLAIN QUERY PLAN pada kueri yang menyaring berdasarkan emp_id = 7 dan sale_date >= '2024-01-01' dan memilih sale_id dan amount, dan konfirmasi bahwa indeks komposit dipakai.

Indeks parsial dan indeks ekspresi

Indeks parsial dibangun dengan CREATE INDEX nama ON tabel(kolom) WHERE kondisi; dan mengindeks hanya sebagian baris dalam tabel.

Kalau kamu sering mencari baris dengan nilai tertentu seperti status = 'pending', mengindeks hanya baris itu membuat indeks tetap kecil dan fokus pada kueri yang sebenarnya kamu jalankan.

Di perf_sales, hanya 5.000 baris (10% dari total) yang memiliki status = 'pending', jadi indeks parsial dengan WHERE status = 'pending' cocok secara alami.

Indeks ekspresi dibangun dengan CREATE INDEX nama ON tabel(ekspresi); dan mengindeks hasil ekspresi yang dihitung alih-alih kolom mentah.

Pakai ini untuk kueri yang menerapkan ekspresi yang sama di sisi pencarian, seperti WHERE amount / 100 = ....

Indeks biasa pada sebuah kolom umumnya tidak membantu setelah kamu menerapkan fungsi atau aritmetika pada kolom itu, tapi kalau kamu mengindeks ekspresinya sendiri, pencarian yang memakai ekspresi yang cocok bisa memakainya.

-- Indeks parsial (contoh read-only terpisah)
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- Indeks ekspresi
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

Bayangkan kebutuhannya: "Kami sering mencari penjualan pending (status = 'pending') berdasarkan rentang tanggal, jadi kami ingin indeks kecil yang hanya mencakup baris itu."

① Mulai dengan DROP INDEX IF EXISTS ix;.

② Buat indeks parsial bernama ix pada perf_sales(sale_date) yang menargetkan hanya baris di mana status = 'pending'.

③ Jalankan EXPLAIN QUERY PLAN pada kueri yang menyaring berdasarkan status = 'pending' AND sale_date >= '2024-01-01' dan konfirmasi indeks parsial dipakai (SEARCH ... USING INDEX ix).

④ Lalu jalankan EXPLAIN QUERY PLAN pada kueri yang memakai kolom yang sama tetapi dengan status = 'paid' (misalnya, status = 'paid' AND sale_date >= '2024-01-01') dan konfirmasi bahwa indeks parsial tidak dipakai (SCAN perf_sales) karena kondisinya tidak cocok dengan predikat indeks.

Bayangkan kebutuhannya: "Kami kadang mencari berdasarkan nilai bucket dari amount dibagi 100 (amount / 100), jadi kami ingin mengindeks hasil hitungan itu." Pencarian yang menerapkan aritmetika pada kolom tidak diuntungkan oleh indeks kolom biasa, jadi pakai indeks ekspresi sebagai gantinya.

① Mulai dengan DROP INDEX IF EXISTS ix;.

② Buat indeks bernama ix pada ekspresi amount / 100 di atas perf_sales.

③ Jalankan EXPLAIN QUERY PLAN pada kueri yang menyaring berdasarkan amount / 100 = 5000 dan konfirmasi indeks ekspresi dipakai (SEARCH ... USING INDEX ix).

④ Lalu jalankan EXPLAIN QUERY PLAN pada kueri yang menyaring berdasarkan amount = 500000 tanpa melewati ekspresi (contoh memakai kolom amount yang sama tetapi dengan bentuk berbeda dari indeks) dan konfirmasi indeks ekspresi tidak dipakai (SCAN perf_sales).

Indeks UNIQUE — indeks yang melarang duplikat

Indeks UNIQUE — dibangun dengan CREATE UNIQUE INDEX nama ON tabel(kolom); — mempercepat pencarian dan mencegah nilai duplikat dimasukkan ke kolom.

Kalau kamu mencoba membuat indeks UNIQUE di kolom yang sudah berisi duplikat, kamu akan mendapat error UNIQUE constraint failed dan indeksnya tidak akan dibuat sama sekali.

Sisi positifnya: kalau kamu membuatnya di kolom yang nilainya unik (primary key atau business key yang unik), kamu mendapat pencegahan duplikat plus pencarian kesetaraan yang cepat.

Di perf_sales, product (200 nilai berbeda) memiliki banyak pengulangan, jadi indeks UNIQUE tidak mungkin di sana. sale_id, di sisi lain, adalah primary key dan unik, jadi kamu bisa membangunnya.

Latihan berikutnya membuat indeks UNIQUE pada sale_id, mengonfirmasi bahwa kolom unik menerima indeks dan bahwa pencarian kesetaraan pada kolom itu memakainya.

-- Indeks UNIQUE (contoh read-only terpisah)
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- Mencoba menambah UNIQUE pada kolom dengan duplikat ditolak
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product memiliki 200 nilai berbeda yang berulang di 50.000 baris, jadi tidak bisa dijadikan UNIQUE

Bayangkan kebutuhannya: "sale_id unik per penjualan, jadi kami ingin mencegah duplikat dan mempercepat pencarian baris tunggal." Ini latihan terakhir di artikel.

① Mulai dengan DROP INDEX IF EXISTS ix;.

② Buat indeks UNIQUE bernama ix pada kolom sale_id milik perf_sales (ini berhasil karena sale_id unik).

③ Lalu jalankan EXPLAIN QUERY PLAN pada kueri yang menyaring berdasarkan sale_id = 12345 dan memilih sale_id dan amount, dan konfirmasi bahwa pencarian baris tunggal melewati indeks.

Tips — kapan menambahkan indeks

Aturan praktis: indeks cenderung mengalahkan SCAN ketika kamu bisa mempersempit hasil ke sekitar 15-20% atau kurang dari tabel. Di atas ambang itu, optimizer sering memilih SCAN secara otomatis.

- Tambahkan: pencarian kesetaraan atau rentang pada kolom tabel besar, primary key / foreign key / kolom UNIQUE, kunci JOIN, kolom yang dirujuk oleh ORDER BY / GROUP BY

- Lewati: tabel kecil dengan beberapa ribu baris atau kurang, kolom selektivitas rendah seperti gender, tabel berat-tulis, LIKE '%foo%' (wildcard di depan tidak bisa memakai indeks)

Alur kerja baseline: periksa plan SCAN / SEARCH dengan EXPLAIN QUERY PLAN dan tambahkan indeks hanya pada kueri yang lambat.