Indeks SQLExplained

31 of 31 rows

Konsep↕	Penjelasan↕	Tip↕
Jenis indeks
⤢B-tree	Pohon seimbang untuk pemindaian kesetaraan, rentang, dan terurut. Bawaan untuk sebagian besar database.	Gunakan untuk =, <, >, BETWEEN, ORDER BY. Lewati untuk pencarian full-text.
⤢Clustered	Indeks yang tingkat daunnya adalah tabel itu sendiri. Baris disimpan dalam urutan kunci. Satu per tabel.	InnoDB mengelompokkan berdasarkan primary key. Tabel SQL Server tanpanya adalah heap.
⤢Nonclustered	Struktur terpisah yang menyimpan kunci plus penunjuk kembali ke tabel dasar. Banyak per tabel.	Pada heap ia menyimpan row ID. Pada tabel clustered ia menyimpan clustering key.
⤢Hash	Tabel hash hanya untuk pencarian kesetaraan. Kecocokan persis waktu konstan.	Gunakan hanya untuk operasi =; rentang dan pengurutan butuh B-tree. Hash memory-optimized SQL Server butuh bucket count mendekati jumlah baris.
⤢GIN	Generalized Inverted Index. Memetakan nilai ke baris. Dibuat untuk array dan full-text.	Gunakan untuk jsonb, array, tsvector, pencarian trigram. Lebih lambat dibangun daripada B-tree.
⤢GiST	Generalized Search Tree. Dapat diperluas untuk tipe non-skalar dan kueri tumpang-tindih.	Gunakan untuk rentang geometri, kNN, batasan eksklusi. pg_trgm mempercepat LIKE.
⤢SP-GiST	GiST berpartisi ruang. Memecah data ke wilayah non-tumpang-tindih lewat pohon radix atau quad.	Gunakan untuk titik, prefiks IP, dan pencocokan prefiks. Bukan untuk kueri tumpang-tindih; pakai GiST.
⤢BRIN	Block Range Index. Menyimpan min/maks per rentang blok. Jejak kecil.	Gunakan pada tabel besar yang terurut secara fisik. Murah dibangun, selektivitas lemah.
⤢Columnstore	Indeks yang disimpan sebagai segmen kolom alih-alih baris. Kompresi tinggi, eksekusi batch.	Gunakan untuk pemindaian analitik atas banyak baris dan sedikit kolom. Pencarian satu baris lambat.
⤢Full-text	Indeks kata terbalik khusus di MySQL dan SQL Server. Dibangun per kolom teks.	Gunakan MATCH AGAINST atau CONTAINS. Full-text Postgres memakai GIN pada tsvector sebagai gantinya.
⤢Bitmap	Indeks Oracle yang menyimpan satu bitmap per nilai berbeda. Setiap bit menandai satu baris.	Gunakan pada kolom kardinalitas rendah di warehouse yang padat baca. Tulis bersamaan terserialisasi per bitmap.
⤢Descending	Kolom indeks yang dideklarasikan DESC. Kunci untuk kolom itu disimpan terbalik.	Diperlukan untuk urut campuran seperti (a ASC, b DESC). Pengurutan DESC biasa juga bisa membaca indeks normal secara mundur.
⤢Invisible	Indeks MySQL 8 yang diabaikan optimizer. Tetap dipelihara pada setiap tulis.	Sembunyikan indeks sebelum dijatuhkan untuk menguji dampaknya. Jadikan terlihat lagi tanpa rebuild.
Desain
⤢Composite	Indeks multi-kolom. Kolom terdepan harus cocok dengan urutan filter kueri.	Urutkan kesetaraan dulu, lalu rentang. Dua indeks tunggal tidak bisa menggantikan satu komposit.
⤢Partial	Indeks pada predikat WHERE. SQL Server menyebutnya filtered index. Hanya mencakup baris yang cocok.	Indeks hanya baris active='t'. Memangkas ukuran dan biaya tulis menjadi setengah.
⤢Covering (INCLUDE)	B-tree dengan kolom non-kunci tambahan. Index-only scan menghindari lookup ke heap.	Tambahkan kolom yang sering diambil lewat INCLUDE. Menjaga indeks tetap dapat diurutkan.
⤢Unique	Indeks yang mewajibkan tidak ada nilai duplikat. Menolak insert yang bertabrakan.	Gunakan untuk natural key dan batasan satu-ke-satu. UNIQUE NULL mengizinkan banyak NULL.
⤢Expression	Indeks yang dibangun atas fungsi kolom, bukan kolomnya sendiri.	Indeks lower(email) atau date_trunc('day', created_at). Kueri harus mengulang ekspresi yang persis.
⤢Opclass	Kelas operator. Menentukan bagaimana indeks membandingkan dan menyimpan tipe satu kolom.	Set varchar_pattern_ops untuk LIKE 'abc%' tanpa case folding. Bawaan cocok untuk kesetaraan dan rentang.
⤢Index-only scan	Rencana yang menjawab kueri hanya dari halaman indeks. Heap tidak pernah dibaca.	Butuh setiap kolom yang diambil berada di indeks atau INCLUDE list. Vacuum menjaga visibility map tetap mutakhir.
⤢Foreign keys	Kolom foreign key tidak mendapat indeks otomatis pada tabel yang mereferensikan.	Indeks setiap kolom FK yang kamu join atau hapus lewatnya. Delete berjenjang memindai tabel anak tanpanya.
Pemeliharaan
⤢EXPLAIN	Inspektur rencana. Menunjukkan sequential scan, pilihan indeks, estimasi biaya.	Gunakan EXPLAIN (ANALYZE, BUFFERS). Seq Scan pada tabel 10M baris berarti indeks hilang.
⤢ANALYZE	Mengambil sampel kolom tabel untuk membangun statistik planner. Histogram mengarahkan pemilihan indeks.	Jalankan setelah bulk load. Statistik basi menyebabkan seq scan pada kolom berindeks.
⤢Over-indexing	Setiap indeks menambah overhead tulis. Insert, update, delete membayar per indeks.	Jatuhkan indeks yang tak terpakai. Lacak penggunaan di pg_stat_user_indexes.
⤢pg_stat_user_indexes	View Postgres atas penggunaan per indeks. Menghitung scan dan tuple yang dibaca sejak reset statistik.	Saring idx_scan = 0 untuk menemukan indeks tak terpakai. Reset penghitung dengan pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Membangun indeks tanpa memblokir baca atau tulis. Butuh dua pemindaian tabel.	Tidak bisa berjalan dalam transaksi. Build yang gagal meninggalkan indeks INVALID untuk dijatuhkan.
⤢DROP INDEX	Menghapus indeks dan membebaskan penyimpanannya. Mengambil lock eksklusif secara bawaan.	Gunakan DROP INDEX CONCURRENTLY pada tabel sibuk. Periksa statistik penggunaan sebelum menghapus.
⤢REINDEX	Membangun ulang indeks yang bloat. Mendapatkan kembali ruang dari dead tuple.	Gunakan REINDEX CONCURRENTLY di produksi. Tanpanya lock memblokir tulis.
⤢Bloat	Dead tuple dan celah halaman yang menumpuk di tabel MVCC dan indeksnya.	Ukur dengan pgstattuple. Vacuum sering; REINDEX saat ruang mati mendominasi.
⤢Fillfactor	Persentase halaman yang dipadatkan saat tulis. Ruang bebas menyerap update mendatang.	Turunkan ke 70-90 pada tabel hot-update. Halaman penuh memaksa page split.
⤢CLUSTER	Menulis ulang tabel terurut secara fisik menurut indeks. Operasi sekali jalan.	Berpasangan dengan BRIN untuk blok terurut. Urutan tidak dipertahankan pada tulisan berikutnya.