Desain Indeks — Urutan Kolom Komposit dan Kondisi yang Memakai Indeks

Dataset untuk artikel ini — perf_sales (50.000 baris)

Mulai dari sini, artikel-artikel tuning memakai tabel dengan banyak baris untuk mengamati bagaimana indeks sebenarnya berperilaku.

Artikel ini membahas urutan kolom dari indeks komposit (indeks yang dibangun di beberapa kolom dalam urutan tertentu), aturan leftmost prefix, dan cara menulis kondisi yang bisa memakai indeks (juga disebut SARGable).

Sebelum latihan, lihat sekilas definisi kolom dan ukuran serta sampel data untuk perf_sales. (Jalankan kueri dengan benar dan penjelasan akan dimunculkan.)

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

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

③ Intip 5 baris pertama dengan SELECT emp_id, region, product, amount, sale_date, status FROM perf_sales LIMIT 5; (karena tabel punya 50.000 baris, jangan pernah menarik setiap baris dengan SELECT * — pakai agregasi atau LIMIT untuk mengintip).

Urutan kolom indeks komposit — aturan leftmost prefix

Indeks komposit (seperti CREATE INDEX ix ON table(col_A, col_B), mencantumkan beberapa kolom dalam urutan tetap) menyimpan entri-entrinya tepat dalam urutan itu.

Indeks diurutkan seperti kamus: berdasarkan col_A ascending, dan dalam col_A yang sama berdasarkan col_B ascending.

Karena ini, apakah indeks membantu tergantung pada apakah kondisi kamu mencakup kolom secara terus-menerus dari yang utama.

Ini disebut aturan leftmost prefix (indeks komposit hanya membantu kondisi yang ditentukan secara terus-menerus dari kolom utama).

Indeks pada (emp_id, sale_date) bekerja untuk kondisi pada emp_id, atau pada emp_id dan sale_date bersama, tapi tidak untuk kondisi pada sale_date saja — emp_id utama hilang, jadi indeks tidak dipakai.

-- Bangun indeks komposit pada region dan product, lalu bandingkan
-- plan untuk kondisi yang menyertakan region utama
-- dengan yang memakai product saja
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, product);

-- Memakai kolom utama region -> ix_demo bekerja
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';

-- Melewati region utama dan memfilter berdasarkan product saja -> ix_demo tidak bekerja
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product = 'P050';

Bangun indeks komposit pada emp_id dan sale_date di atas perf_sales dan konfirmasi aturan leftmost prefix dengan membaca execution plan. Buat ulang indeks dan tampilkan plan di dalam satu eksekusi konsol supaya langkah-langkahnya mandiri. (Jalankan kueri dengan benar dan penjelasan akan dimunculkan.)

① Mulai dengan DROP INDEX IF EXISTS supaya indeks bisa dibangun ulang bahkan kalau yang bernama sama tertinggal.

② Buat indeks komposit pada perf_sales yang mencantumkan emp_id dan sale_date dengan urutan itu.

③ Pakai EXPLAIN QUERY PLAN untuk menampilkan plan untuk kueri agregat yang difilter berdasarkan emp_id saja.

④ Lalu tampilkan plan untuk kueri agregat yang difilter berdasarkan sale_date saja dan bandingkan bagaimana plan berubah dari ③.

Cantumkan kolom dalam urutan equality-lalu-range

Ada panduan untuk urutan kolom di indeks komposit.

Taruh kondisi equality (= atau IN yang mematok kolom ke satu nilai) di depan, dan kondisi range (>, >=, <, BETWEEN, dll. yang mencakup rentang) di belakang.

Mematok kolom utama dengan equality menjaga entri yang cocok dalam satu blok kontigu di dalam indeks, jadi kamu bisa mengikis blok itu sekaligus.

Kondisi seperti WHERE emp_id = 7 AND sale_date >= '2024-01-01' adalah kecocokan alami untuk urutan (emp_id, sale_date).

-- Indeks region (equality) dan amount (range) bersama,
-- dengan kolom equality pertama dan kolom range kedua
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE region = 'West' AND amount >= 500000;

Bayangkan kebutuhannya: "Kami ingin merangkum penjualan untuk rep tertentu mulai dari tanggal tertentu." Kondisinya adalah equality pada emp_id dan range pada sale_date. Bangun indeks komposit pada dua kolom ini dalam urutan equality-lalu-range dan konfirmasi dengan execution plan bahwa ia berubah menjadi index seek.

① Jalankan DROP INDEX IF EXISTS untuk membuang indeks sebelumnya.

② Buat indeks komposit dengan kolom equality pertama dan kolom range kedua.

③ Pakai EXPLAIN QUERY PLAN pada kueri agregat yang menggabungkan equality pada emp_id dengan range pada sale_date lewat AND, dan konfirmasi bahwa ia menjadi index seek.

Kondisi yang bisa memakai indeks — bandingkan kolom apa adanya, tidak dibungkus

Bahkan dengan indeks di tempat, cara kamu menulis kondisi memutuskan apakah ia dipakai.

Kondisi yang dibentuk supaya indeks bisa mempersempit disebut kondisi yang bisa memakai indeks (dalam bahasa Inggris juga dikenal sebagai SARGable).

Aturan dasarnya: bandingkan kolom yang diindeks apa adanya, tanpa membungkusnya dalam komputasi apa pun.

Sesuatu seperti WHERE emp_id = 7, di mana kolom dibandingkan langsung dengan equality atau range, memungkinkan indeks bekerja.

Di sisi lain, WHERE emp_id + 0 = 7 (kolom ada di dalam ekspresi) atau WHERE substr(sale_date,1,4) = '2024' (kolom dibungkus dalam fungsi) memutus korespondensi dengan pengurutan indeks dan kembali ke full row scan.

Jaga kolom mentah di sisi kiri perbandingan, dan kalau ada penyesuaian yang diperlukan, terapkan ke nilai di sisi kanan.

-- Arti sama, bentuk berbeda -> plan berbeda
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Kolom dibandingkan apa adanya -> indeks bekerja, SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Kolom di dalam ekspresi -> indeks tidak bekerja, kembali ke SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount + 0 >= 800000;

Amati bagaimana dua kondisi dengan arti sama — satu membandingkan kolom apa adanya dan satu membungkusnya dalam ekspresi — menghasilkan execution plan yang berbeda. Bangun indeks pada emp_id dan bandingkan plan untuk dua bentuk.

① Jalankan DROP INDEX IF EXISTS untuk membuang indeks sebelumnya.

② Buat indeks satu-kolom pada emp_id.

③ Tampilkan plan untuk kueri agregat yang membandingkan emp_id apa adanya dengan equality.

④ Lalu tampilkan plan untuk kueri agregat dengan arti sama tapi dengan emp_id di dalam ekspresi, dan bandingkan bagaimana plan berubah dari ③.