Forberedelse til kodeintervjuer · leksjon

Filtrere på beregnede verdier

Hvorfor funksjoner på kolonner hindrer indeksbruk, og hvordan intervjuere undersøker dette.

Leksjon 4 av 413 trinn

Filtrere på beregnede verdier er en gratis leksjon i Forberedelse til kodeintervjuer på CoddyKit. Dette er leksjon 4 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til kodeintervjuer, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hvorfor dette spørsmålet skiller nivåene

Spørsmålet høres uskyldig ut: denne spørringen er korrekt, men treg – hvorfor? Ofte er svaret at WHERE-klausulen pakker inn en indeksert kolonne i en funksjon. Da blir predikatet ikke-sargbart: Optimereren kan ikke lenger bruke indeksen og må skanne hver rad.

Denne leksjonen forklarer sargbarhet, viser omskrivingene intervjuere forventer, og går gjennom hvor et beregnet filter faktisk hører hjemme.

Sargbarhet i én definisjon

Sargbart (Search ARGument ABLE) betyr at et predikat kan bruke en indeks til å søke direkte etter samsvarende rader. Tommelfingerregelen er at den indekserte kolonnen må stå ubearbeidet på én side av sammenligningen, ikke være skjult inne i en funksjon eller et uttrykk.

Sargbart: col = 5, col > 100, col LIKE 'abc%'
Ikke-sargbart: FUNC(col) = 5, col + 1 > 100

Anti-mønsteret med funksjon på kolonnen

Målet her er å finne ordrer som ble lagt inn i 2024. Når kolonnen pakkes inn i YEAR(), tvinges motoren til å beregne året for hver eneste rad før den kan sammenligne, og indeksen på order_date blir dermed ubrukelig.

Spørringen returnerer riktig svar, men skanner hele tabellen. I en stor tabell kan forskjellen være millisekunder mot minutter.

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

Skriv om til et intervall

Løsningen er å la order_date stå ubearbeidet og uttrykke betingelsen som et halvåpent intervall. Nå kan indeksen på order_date søke direkte til starten av 2024 og stoppe ved 2025.

Samme resultat, men en indeksskanning av et intervall i stedet for en full skanning. Denne omskrivingen til et intervall er den mest testede sargbarhetsløsningen i intervjuer.

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

Aritmetikk på kolonnen

Det samme problemet skjuler seg i aritmetikk. WHERE salary + bonus > 100000 eller WHERE price * 0.9 < 50 utfører begge beregningen på kolonnen og blokkerer indeksen.

Flytt regnestykket til konstantleddet der det er mulig: skriv om price * 0.9 < 50 til price < 50 / 0.9. Konstanten beregnes én gang, og price forblir ubearbeidet og kan bruke indeksen.

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

Varianten med søk uten hensyn til store og små bokstaver

WHERE LOWER(email) = 'a@b.com' er ikke-sargbart mot en vanlig indeks på email, fordi e-postadressen i hver rad først gjøres om til små bokstaver.

Det finnes to løsninger i produksjon: Lagre en normalisert kopi med små bokstaver og indekser den, eller opprett en funksjonell indeks på LOWER(email), slik at selve uttrykket indekseres. Å nevne alternativet med funksjonell indeks viser erfaring fra virkelige systemer.

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

Når en beregning faktisk er nødvendig

Noen ganger avhenger filteret faktisk av en beregnet verdi som ikke kan skrives om til et intervall, for eksempel ved filtrering på et forholdstall. Det er likevel ikke mulig å referere til et SELECT-alias i WHERE, fordi WHERE evalueres før SELECT-listen.

Da må uttrykket enten gjentas i WHERE, eller spørringen må pakkes inn i en underspørring / CTE, slik at den beregnede kolonnen kan filtreres i den ytre spørringen.

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

Aggregeringer hører hjemme i HAVING, ikke WHERE

En beregning som er en aggregering, kan ikke være i WHERE i det hele tatt, fordi WHERE filtrerer individuelle rader før grupperingen skjer. WHERE SUM(amount) > 1000 gir en feil.

Filtre på aggregeringer hører hjemme i HAVING, som kjøres etter GROUP BY. Å vite hvilken klausul som ser beregningen, er i seg selv et vanlig spørsmål om utførelsesrekkefølge.

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

Slik undersøker intervjuere dette

Intervjuere viser en treg spørring med en funksjon på en kolonne og ber om å gjøre den raskere uten å endre resultatet. Fremgangsmåten er:

Identifiser funksjonen på kolonnen som ikke-sargbar
Skriv om uttrykket slik at kolonnen står ubearbeidet (intervall eller beregning på konstantleddet)
Hvis ingen omskriving er mulig, foreslå en funksjonell indeks eller en lagret beregnet kolonne

Å nevne EXPLAIN for å bekrefte at planen ble endret fra seq scan til index scan, fullfører svaret på en overbevisende måte.

Bevissthet om avveininger

Vær balansert: Indekser og funksjonelle indekser gjør lesing raskere, men gjør skriving tregere og bruker lagringsplass. I en liten tabell er en full skanning helt grei, og det er bortkastet arbeid å legge til en indeks.

Det modne svaret er betinget: Hvis denne kolonnen er stor og det ofte filtreres på denne måten, bør predikatet gjøres sargbart eller en funksjonell indeks legges til; ellers bør det stå som det er. I intervjuer er kontekst viktigere enn dogmer.

Funksjonelle indekser gjør beregninger sargable

Noen ganger må du faktisk filtrere på en transformert verdi – for eksempel ved et skilletegnsuavhengig søk. I stedet for å gi opp indekser kan du opprette en uttrykksindeks (funksjonell indeks) på nøyaktig det uttrykket du filtrerer på.

Da kan optimalisatoren bruke indeksen selv om en funksjon omslutter kolonnen.
Indeksen må stemme nøyaktig overens med predikatuttrykket.

-- 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';

Kjapp kontroll

Identifiser hvilket predikat optimereren kan bruke en indeks på.

Besvar den i den interaktive leksjonen

Oppsummering

Dette er de viktigste punktene:

Et predikat er sargbart når den indekserte kolonnen står ubearbeidet, ikke inne i en funksjon eller aritmetikk
Skriv om YEAR(col) = 2024 til et halvåpent intervall, og flytt regnestykker til konstantleddet
Bruk en funksjonell indeks eller en lagret beregnet kolonne for uttrykk som ikke kan unngås
Et SELECT-alias kan ikke brukes i WHERE; aggregeringer hører hjemme i HAVING

Det klassiske spørsmålet handler om en treg spørring; den klassiske løsningen er å la kolonnen stå ubearbeidet.

Gratis å komme i gang

Lær deg Forberedelse til kodeintervjuer med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 90
Leksjoner: 360

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Filtrere på beregnede verdier» gratis?

Ja – hele teksten i «Filtrere på beregnede verdier» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til kodeintervjuer-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hva lærer jeg i «Filtrere på beregnede verdier»?

Hvorfor funksjoner på kolonner hindrer indeksbruk, og hvordan intervjuere undersøker dette. Du øver på Forberedelse til kodeintervjuer med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til kodeintervjuer?

Ingen tidligere erfaring er nødvendig. Forberedelse til kodeintervjuer på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 4 av 4.

Hvor lang tid tar leksjonen «Filtrere på beregnede verdier»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til kodeintervjuer-leksjonen?

Ja. Alle Forberedelse til kodeintervjuer-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

← Tilbake til Forberedelse til kodeintervjuer