Index dan rencana eksekusi

Index adalah salinan terurut dari beberapa kolom, ditambah penunjuk kembali ke barisnya. Kalimat itu menjelaskan hampir semua yang bisa dan tidak bisa dilakukan index: dia bisa menemukan nilai dengan cepat, dia bisa mengembalikan baris yang sudah terurut, dan dia berbiaya waktu pada setiap penulisan karena salinannya harus dijaga.

Kedua mesin memakai B-tree secara bawaan.

Aturan prefiks kiri

Index gabungan diurutkan berdasar kolom pertama, lalu kolom kedua di antara yang pertamanya sama, dan seterusnya. Seperti buku telepon yang diurutkan berdasar nama keluarga lalu nama depan.

CREATE INDEX ix_order_customer_placed ON "order" (customer_id, placed_at);

Karena itulah urutan kolom di index gabungan adalah keputusan, bukan formalitas. Patokannya: kolom kesamaan dulu, lalu kolom yang kamu pindai sebagai rentang atau kamu pakai untuk mengurutkan.

Akibat yang sering terlewat: (a, b) membuat index terpisah pada (a) jadi berlebihan, tapi tidak pada (b). Dua index (a, b) dan (a) biasanya satu index terlalu banyak.

InnoDB itu clustered, PostgreSQL tidak

Ini perbedaan struktural terbesar antara keduanya, dan dia mengubah keputusan desain.

Jadi di MySQL: jaga primary key tetap kecil, dan utamakan key yang berurutan. Primary key UUID CHAR(36) disalin ke setiap secondary index dan menyebarkan penyisipan ke seluruh file; BIGINT AUTO_INCREMENT menambahkan di ujung. Di PostgreSQL pilihan yang sama jauh lebih murah.

Ganti rugi PostgreSQL adalah index-only scan: kalau setiap kolom yang dibutuhkan query ada di dalam index-nya, dia tidak menyentuh tabelnya sama sekali.

-- Tercakup oleh ix_order_customer_placed: tidak perlu akses heap
SELECT customer_id, placed_at FROM "order" WHERE customer_id = 7;

-- PostgreSQL juga bisa membawa kolom tambahan tanpa menjadikannya bagian key
CREATE INDEX ix_order_cover ON "order" (customer_id) INCLUDE (status, placed_at);

MySQL mendapat efek yang sama dengan mencantumkan kolomnya di dalam index; dia tidak punya INCLUDE.

Apa yang menghentikan index dipakai

Sebuah predikat itu sargable ketika mesinnya bisa mengubahnya jadi rentang atas nilai yang di-index. Bungkus kolomnya dengan fungsi dan dia tidak bisa.

-- Tidak sargable: index-nya menyimpan placed_at, bukan YEAR(placed_at)
WHERE YEAR(placed_at) = 2026
WHERE DATE(placed_at) = '2026-01-01'
WHERE lower(email) = 'a@b.com'

-- Sargable: arti yang sama, ramah index
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'
WHERE email = 'a@b.com'         -- dengan collation tak peka huruf, atau:

Kalau fungsinya memang dibutuhkan, index-kan ekspresinya:

-- PostgreSQL: expression index
CREATE INDEX ix_customer_lower_email ON customer (lower(email));

-- MySQL: functional index (8.0.13+), atau generated column plus index
CREATE INDEX ix_customer_lower_email ON customer ((lower(email)));

Hal lain yang mengalahkan index:

• LIKE '%abc' — wildcard di depan tidak punya prefiks untuk dituju. LIKE 'abc%' aman.
• OR melintasi kolom berbeda — kadang ditangani bitmap atau index merge, sering lebih baik ditulis ulang sebagai UNION ALL.
• Ketidakcocokan tipe. Membandingkan kolom varchar dengan angka membuat MySQL mengonversi kolomnya, bukan literalnya, dan index-nya tak terpakai. Yang ini tidak terlihat dari teks query-nya.
• Selektivitas rendah. Index pada boolean yang cocok dengan 60% tabel lebih banyak kerja daripada memindai, dan planner benar mengabaikannya.

Partial index, dan penggantinya di MySQL

PostgreSQL bisa meng-index sebagian baris, yang sekaligus lebih kecil dan lebih selektif:

-- Hanya order yang belum dikirim — yang ditanyakan antrean pengiriman
CREATE INDEX ix_order_unshipped ON "order" (placed_at) WHERE shipped_at IS NULL;

-- Dan aturan keunikan dari materi 2 yang tidak bisa diungkapkan UNIQUE biasa
CREATE UNIQUE INDEX ux_one_open_coupon ON "order" (customer_id) WHERE coupon_code IS NULL;

MySQL tidak punya partial index. Jalan pintasnya adalah generated column yang bernilai NULL untuk baris yang ingin kamu kecualikan — karena UNIQUE mengabaikan NULL — ditambah index padanya.

Membaca EXPLAIN

-- PostgreSQL: hanya perkiraan
EXPLAIN SELECT ...;
-- PostgreSQL: benar-benar dijalankan, lalu melaporkan waktu dan I/O sesungguhnya
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- MySQL: perkiraan
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
-- MySQL 8.0.18+: benar-benar dijalankan
EXPLAIN ANALYZE SELECT ...;

EXPLAIN ANALYZE mengeksekusi pernyataannya. Pada SELECT itu aman; pada UPDATE atau DELETE dia benar-benar mengerjakannya. Di PostgreSQL, bungkus dalam transaksi lalu rollback.

Yang perlu dicari, berurutan:

1. Perkiraan baris versus baris sesungguhnya. Di PostgreSQL, rows=1000 di sebelah actual rows=250000 berarti statistiknya salah, dan setiap keputusan di atas node itu dibuat berdasar angka yang buruk. Jalankan ANALYZE namatabel (PostgreSQL) atau ANALYZE TABLE namatabel (MySQL).
2. Metode aksesnya. Seq Scan / ALL pada tabel besar dengan WHERE yang selektif adalah temuan yang paling lazim. Index Scan, Index Only Scan, ref, range, eq_ref adalah pemakaian index; const dan eq_ref kasus terbaiknya.
3. Pengurutan yang tidak kamu inginkan. Sort di PostgreSQL atau Using filesort di MySQL berarti urutannya harus diproduksi. Index yang cocok dengan ORDER BY menghapusnya — dan bersama LIMIT, itu sering jadi seluruh perbedaannya.
4. Ke mana waktunya sebenarnya pergi. Di PostgreSQL, actual time itu kumulatif dan per-putaran: node yang menunjukkan 0,5ms dengan loops=20000 berbiaya sepuluh detik. Baca jumlah putarannya sebelum menyimpulkan sebuah node itu murah.

Peta kosakata:

Biaya index

Setiap index dijaga pada setiap INSERT, setiap UPDATE kolom yang di-index, dan setiap DELETE. Sepuluh index di tabel yang sibuk membuat penulisan beberapa kali lebih mahal, dan mereka memakan cache yang diinginkan tabelnya sendiri.

Kedua mesin memaparkan pemakaiannya, jadi ini bisa diukur alih-alih jadi soal selera:

-- PostgreSQL: index yang tidak pernah dibaca apa pun
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

-- MySQL: pertanyaan yang sama
SELECT object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0;

Index dengan nol pemindaian sejak restart terakhir adalah biaya tulis murni. Periksa lama server hidup sebelum menghapusnya — index untuk laporan bulanan memang wajar menunjukkan nol di hari kedua.

Yang perlu dibawa pulang

• Index gabungan bekerja dari kiri ke kanan. Kolom kesamaan dulu, rentang atau pengurutan di belakang.
• InnoDB menyimpan tabel dalam urutan PK dan menyalin PK ke setiap secondary index: jaga dia kecil dan berurutan.
• Fungsi yang membungkus kolom mematikan index-nya. Index-kan ekspresinya.
• LIKE 'abc%' memakai index; LIKE '%abc' tidak bisa.
• PostgreSQL punya partial dan expression index; MySQL menggantinya dengan generated column.
• Di EXPLAIN, bandingkan perkiraan dengan baris sesungguhnya lebih dulu, dan baca jumlah putaran sebelum memercayai sebuah node itu murah.