Pelajaran modul (1/4)
Indeks dan B-Trees
Bagaimana database dapat menemukan satu catatan di antara jutaan baris dalam sepersekian detik?
Secara default, ketika kita menanyakan SELECT * FROM customers WHERE city = 'Milano', Postgres harus melakukan Pemindaian Berurutan: Postgres secara fisik membaca seluruh tabel dari disk baris demi baris, membandingkan setiap kota dengan "Milano". Ketika sebuah tabel menyimpan jutaan catatan, proses ini melumpuhkan kinerja.
Jawaban struktural utama terhadap hambatan ini adalah Indeks.
Indeks (B-Pohon)
Indeks bekerja persis seperti indeks analitis di bagian belakang sebuah buku besar. Daripada membaca seluruh buku untuk menemukan di mana ia berbicara tentang "Milano", kita mencari halaman belakang di mana kata "Milano" mengarahkan kita ke [p. 45, 114, 230].
Basis data, di balik layar, membangun struktur data yang terpisah dan sangat kompak (seringkali disebut "B-Tree") yang diurutkan berdasarkan kolom atau kolom yang dimaksud, berisi petunjuk langsung ke penyimpanan fisik dari baris yang cocok.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Operasi tunggal ini memicu mesin Postgres, yang akan mengekstrak dan mengindeks semua nilai city di latar belakang. Setelah selesai, setiap SELECT ... WHERE city = '...' berikutnya akan terbang!
Kunci utama dan keunikannya
Tahukah Anda bahwa aturan kunci utama (PRIMARY KEY) dan UNIQUE secara diam-diam menggunakan indeks di balik terpal? Karena DB harus memastikan bahwa email atau ID tidak pernah terduplikasi saat menyisipkan atau mengubah baris, PostgreSQL secara otomatis membuat indeks B-Tree implisit di atasnya: dengan cara ini pemeriksaan keunikan memerlukan waktu mikrodetik.
Itu sebabnya bergabung di bidang ID memiliki performa yang luar biasa.
Menjatuhkan indeks
Jika Anda menyadari bahwa kueri berat telah berubah dan indeks tidak pernah dikonsultasikan, sebaiknya hancurkan indeks tersebut dan dapatkan kembali ruang penting:
DROP INDEX idx_customers_city;Giliranmu
Dalam e-commerce, kami melihat melalui dasbor bahwa kami sering memfilter pelanggan berdasarkan kebangsaan (kolom negara di pelanggan). \nBuat indeks dasar baru pada kolom negara dan beri nama 'idx_customers_country'.
Tunjukkan petunjuk
Gunakan CREATE INDEX nama_indeks PADA nama_tabel(nama_kolom);
Solusi tersedia setelah 3 upaya
Menjelajahi indeks unik sekunder
Indeks B-Tree juga dapat digunakan untuk menyimulasikan batasan UNIK pada waktu pembuatan dan mempercepat pencarian, sekaligus membuat maksud logis menjadi eksplisit dan diterapkan.\n\nBuat indeks baru tempat Anda mendeklarasikan kata kunci UNIK. Tentukan sehingga memblokir pendaftaran duplikat yang tidak diinginkan dari pengguna lain, memaksakan keunikan pada kolom 'email' dari 'pelanggan', dan beri nama 'idx_unique_email_customer'.
Tunjukkan petunjuk
Tulis CREATE UNIQUE INDEX nama_indeks PADA tabel(kolom);
Solusi tersedia setelah 3 upaya