SQL Academy · Oppitunti

Osittaiset indeksit ja lausekeindeksit

Indeksoi WHERE-ehtojen avulla vain tarvitsemasi rivit ja indeksoi lasketut lausekkeet (lower(email), date_trunc('day', ts)).

Oppitunti 3/413 vaihetta

Osittaiset indeksit ja lausekeindeksit on ilmainen SQL Academy-oppitunti CoddyKitissä. Tämä on oppitunti 3/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL Academy-kurssilla on yhteensä 4 oppituntia.

Osittainen indeksi: indeksoikaa osajoukko

Osittainen indeksi kattaa vain rivit, jotka täyttävät indeksin luonnin yhteydessä määritetyn WHERE-ehdon. Se on pienempi ja nopeampi sekä sopii erinomaisesti yleisimpään kyselytapaukseen:

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

Milloin osittaiset indeksit ovat hyödyllisiä

Käyttäkää niitä, kun:

Suodatatte aina samalla predikaatilla (esimerkiksi loogisesti poistetut rivit)
Yksi arvo hallitsee saraketta (esimerkiksi 95 %:lla riveistä status='closed')
Haluatte pienen ja aktiivisesti välimuistissa käytettävän indeksin

Esimerkki: aktiiviset istunnot

Useimmat istunnot ovat vanhentuneita, ja kyselyitä tehdään vain aktiivisista istunnoista:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

Parempi ratkaisu: käyttäkää vakaata predikaattia

Indeksin predikaatin on oltava muuttumaton. Aikariippuvaiset lausekkeet (NOW()) eivät kelpaa. Käyttäkää niiden sijaan saraketta, kuten is_active:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

Lausekeindeksi

Indeksoikaa sarakkeesta laskettu funktio:

CREATE INDEX users_email_lower_idx ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

Yleisiä lausekeindeksejä

LOWER(email) kirjainkoosta riippumattomaan hakuun
(price * tax_rate) laskettua lajittelua varten
date_trunc('day', created_at) päivittäisiä raportteja varten
(data ->> 'user_id')::BIGINT JSONB-poimintoja varten

Lausekkeen on oltava IMMUTABLE

Lauseke on merkittävä IMMUTABLE-lausekkeeksi — sen tulos riippuu vain syötteestä. RANDOM(), NOW() ja CURRENT_USER eivät kelpaa.

Osittaisen indeksin ja lausekeindeksin yhdistelmä

Voitte yhdistää molemmat:

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

Yksilölliset osittaiset indeksit

Klassinen "yksi aktiivinen rivi" -malli:

CREATE UNIQUE INDEX users_one_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Same email can exist many times in deleted users, but only once active.

Indeksin predikaatin on vastattava kyselyä

Suunnittelija käyttää osittaista indeksiä vain, kun kyselyn WHERE-ehto "implikoi" indeksin predikaatin:

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

Älkää käyttäkö liikaa

Useat toisistaan erillisiä osajoukkoja kattavat osittaiset indeksit voivat hidastaa kirjoituksia, koska jokaisen INSERT-operaation on päivitettävä kaikki asiaankuuluvat indeksit. Käyttäkää niitä säästeliäästi usein suoritettaviin kyselyihin.

Kertaus

Osittaiset indeksit ja lausekeindeksit tarjoavat enemmän tehoa tavua kohden.

Osittainen indeksi: indeksoikaa vain kyselyissä käyttämänne osajoukko
Lausekeindeksi: indeksoikaa lasketut arvot
Molemmat edellyttävät IMMUTABLE-predikaatteja
Kyselyn on käytettävä samaa predikaattia, jotta indeksi sopii siihen

Pikatarkistus

Haluatte tehdä kirjainkoosta riippumattomia hakuja sähköpostiosoitteen perusteella. Mikä indeksi on tehokkain?

Vastaa interaktiivisessa oppitunnissa

Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 46
Oppitunnit: 183

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Osittaiset indeksit ja lausekeindeksit” ilmainen?

Kyllä – oppitunnin ”Osittaiset indeksit ja lausekeindeksit” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL Academy-kurssin, päivitä CoddyKit PROhon. SQL Academy-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Osittaiset indeksit ja lausekeindeksit”?

Indeksoi WHERE-ehtojen avulla vain tarvitsemasi rivit ja indeksoi lasketut lausekkeet (lower(email), date_trunc('day', ts)). Harjoittelet SQL Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni SQL Academy-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin SQL Academy-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 3/4.

Kuinka kauan ”Osittaiset indeksit ja lausekeindeksit”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä SQL Academy-oppitunnilla?

Kyllä. Jokainen SQL Academy-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

B-tree-, Hash-, GiST- ja GIN-indeksit
Yhdistelmäindeksit ja sarakkeiden järjestys
Osittaiset indeksit ja lausekeindeksit
Indeksien ylläpito ja paisuminen

← Takaisin: SQL Academy