Coding Interview Prep · Pelajaran

Memfilter Nilai yang Dihitung

Mengapa fungsi pada kolom menghambat penggunaan indeks dan bagaimana pewawancara menguji hal ini.

Memfilter Nilai yang Dihitung adalah pelajaran Coding Interview Prep gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar Coding Interview Prep, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Mengapa Pertanyaan Ini Membedakan Tingkat Kemampuan

Pertanyaannya terdengar sederhana: kueri ini benar tetapi lambat, mengapa? Sering kali jawabannya adalah klausa WHERE membungkus kolom berindeks dengan suatu fungsi. Hal itu membuat predikat tidak dapat dicari dengan indeks: pengoptimal tidak lagi dapat menggunakan indeks dan harus memindai setiap baris.

Pelajaran ini menjelaskan kemampuan pencarian dengan indeks, menunjukkan penulisan ulang yang diharapkan pewawancara, serta membahas di mana filter hasil perhitungan seharusnya ditempatkan.

Dapat Dicari dengan Indeks dalam Satu Definisi

Dapat dicari dengan indeks (Argumen Pencarian ABLE) berarti suatu predikat dapat menggunakan indeks untuk langsung mencari baris yang cocok. Pedoman praktisnya: kolom berindeks harus muncul apa adanya di salah satu sisi perbandingan, bukan tersembunyi di dalam fungsi atau ekspresi.

Dapat dicari dengan indeks: col = 5, col > 100, col LIKE 'abc%'
Tidak dapat dicari dengan indeks: FUNC(col) = 5, col + 1 > 100

Pola Antifungsi pada Kolom

Di sini tujuannya adalah mendapatkan pesanan yang dibuat pada 2024. Membungkus kolom dengan YEAR() memaksa mesin menghitung tahun untuk setiap baris sebelum dapat melakukan perbandingan, sehingga indeks pada order_date tidak berguna.

Kueri ini mengembalikan jawaban yang benar, tetapi memindai seluruh tabel. Pada tabel besar, inilah perbedaan antara milidetik dan menit.

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

Tulis Ulang sebagai Rentang

Perbaikannya adalah membiarkan order_date apa adanya dan menyatakan kondisi sebagai rentang setengah terbuka. Sekarang indeks pada order_date dapat langsung mencari awal 2024 dan berhenti di 2025.

Hasilnya sama, tetapi menggunakan pemindaian rentang indeks, bukan pemindaian penuh. Penulisan ulang rentang ini adalah perbaikan kemampuan pencarian dengan indeks yang paling sering diuji dalam wawancara.

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

Aritmetika pada Kolom

Masalah yang sama juga tersembunyi dalam aritmetika. WHERE salary + bonus > 100000 atau WHERE price * 0.9 < 50 sama-sama melakukan perhitungan pada kolom dan menghalangi penggunaan indeks.

Pindahkan perhitungannya ke sisi konstanta jika memungkinkan: tulis ulang price * 0.9 < 50 menjadi price < 50 / 0.9. Nilai literal dihitung sekali, sementara price tetap apa adanya dan dapat menggunakan indeks.

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

Varian Pencarian Tanpa Membedakan Huruf Besar-Kecil

WHERE LOWER(email) = 'a@b.com' tidak dapat dicari dengan indeks biasa pada email, karena email setiap baris harus diubah menjadi huruf kecil terlebih dahulu.

Ada dua perbaikan untuk penggunaan di lingkungan produksi: simpan salinan yang telah dinormalisasi dan diubah menjadi huruf kecil, lalu buat indeks pada salinan tersebut; atau buat indeks fungsional pada LOWER(email) agar ekspresinya sendiri diindeks. Menyebutkan pilihan indeks fungsional menunjukkan pengalaman di dunia nyata.

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Saat Perhitungan Memang Diperlukan

Terkadang filter memang bergantung pada nilai hasil perhitungan yang tidak dapat ditulis ulang sebagai rentang, misalnya saat memfilter rasio. Anda tetap tidak dapat merujuk nama pengganti SELECT di WHERE, karena WHERE dievaluasi sebelum daftar SELECT.

Jadi, Anda dapat mengulangi ekspresinya di WHERE atau membungkus kueri dalam subkueri / CTE, lalu memfilter kolom hasil perhitungan tersebut di kueri luar.

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

Agregat Berada di HAVING, Bukan WHERE

Perhitungan yang merupakan agregat sama sekali tidak dapat ditempatkan di WHERE, karena WHERE memfilter setiap baris sebelum pengelompokan berlangsung. WHERE SUM(amount) > 1000 adalah sebuah kesalahan.

Filter agregat berada di HAVING, yang dijalankan setelah GROUP BY. Mengetahui klausa mana yang melihat perhitungan itu sendiri merupakan pertanyaan tentang urutan eksekusi yang sering diajukan.

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Cara Pewawancara Mengujinya

Mereka menunjukkan kueri lambat dengan fungsi pada kolom dan meminta Anda membuatnya cepat tanpa mengubah hasilnya. Langkah Anda:

Identifikasi fungsi pada kolom sebagai sesuatu yang tidak dapat dicari dengan indeks
Tulis ulang agar kolom tetap apa adanya (gunakan rentang atau perhitungan di sisi konstanta)
Jika tidak ada penulisan ulang yang memungkinkan, usulkan indeks fungsional atau kolom hasil perhitungan yang disimpan

Menyebutkan EXPLAIN untuk memastikan rencana berubah dari pemindaian berurutan menjadi pemindaian indeks akan menyempurnakan jawaban.

Kesadaran akan Kompromi

Bersikaplah seimbang: indeks dan indeks fungsional mempercepat pembacaan, tetapi memperlambat penulisan dan menggunakan ruang penyimpanan. Pada tabel yang sangat kecil, pemindaian penuh tidak masalah dan menambahkan indeks hanya membuang usaha.

Jawaban tingkat lanjut bersifat kondisional: jika kolom ini besar dan sering difilter dengan cara ini, buat predikat dapat dicari dengan indeks atau tambahkan indeks fungsional; jika tidak, biarkan saja. Dalam wawancara, konteks lebih penting daripada dogma.

Indeks Fungsional Membuat Perhitungan Dapat Memanfaatkan Indeks

Terkadang Anda memang harus memfilter berdasarkan nilai yang telah diubah—misalnya, untuk pencocokan yang tidak membedakan huruf besar dan kecil. Daripada menyerah pada indeks, buatlah indeks ekspresi (fungsional) berdasarkan ekspresi persis yang digunakan untuk memfilter.

Dengan begitu, pengoptimal tetap dapat menggunakan indeks meskipun sebuah fungsi membungkus kolom.
Ekspresi indeks harus sama persis dengan ekspresi predikat.

-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

Pemeriksaan Singkat

Identifikasi predikat yang dapat diindeks oleh pengoptimal.

Ringkasan

Hal-hal penting:

Predikat dapat dicari dengan indeks ketika kolom berindeks muncul apa adanya, bukan di dalam fungsi atau aritmetika
Tulis ulang YEAR(col) = 2024 sebagai rentang setengah terbuka; pindahkan perhitungan ke sisi konstanta
Untuk ekspresi yang tidak dapat dihindari, gunakan indeks fungsional atau kolom hasil perhitungan yang disimpan
Anda tidak dapat menggunakan nama pengganti SELECT di WHERE; agregat berada di HAVING

Pertanyaan klasiknya adalah kueri lambat; perbaikan klasiknya adalah membiarkan kolom apa adanya.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Memfilter Nilai yang Dihitung” gratis?

Ya — teks lengkap “Memfilter Nilai yang Dihitung” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus Coding Interview Prep, upgrade ke CoddyKit PRO. Kursus Coding Interview Prep mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Memfilter Nilai yang Dihitung”?

Mengapa fungsi pada kolom menghambat penggunaan indeks dan bagaimana pewawancara menguji hal ini. Kamu berlatih Coding Interview Prep dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai Coding Interview Prep?

Tidak diperlukan pengalaman sebelumnya. Coding Interview Prep di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “Memfilter Nilai yang Dihitung” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran Coding Interview Prep ini?

Ya. Setiap pelajaran Coding Interview Prep menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Prioritas AND/OR dan Penggunaan Tanda Kurung
BETWEEN, IN, dan Batas Inklusif
LIKE, Karakter Pengganti, dan Escape
Memfilter Nilai yang Dihitung

← Kembali ke Coding Interview Prep