Når indekser gjør skade: skriving og selektivitet
Skriveforsterkning og hvorfor en indeks på en kolonne med lav selektivitet er ubrukelig.
Når indekser gjør skade: skriving og selektivitet 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.
Spørsmålet bak spørsmålet
Etter tre leksjoner om hvorfor indekser hjelper, snur intervjuere på spørsmålet: «Hvorfor ikke bare indeksere hver kolonne?» En sterk kandidat forklarer at indekser har reelle kostnader, både for skriving og for hurtigbuffer og lagring, og at spørringsplanleggeren aldri vil bruke enkelte indekser.
Denne leksjonen dekker de to viktigste grunnene til at en indeks kan være skadelig: write amplification og lav selektivitet.
Hver indeks bremser skriving
En indeks må holdes synkronisert med tabellen. Hver INSERT, hver DELETE og hver UPDATE av en indeksert kolonne må også oppdatere indeksstrukturen. Dette er write amplification: Én endring av en rad blir til én tabellskriving pluss én skriving per berørt indeks.
En tabell med åtte indekser krever omtrent ni ganger så mye skrivearbeid som en uten indekser. På tabeller med mye skriving eller høy gjennomstrømming er dette en betydelig kostnad.
Eksempel: Skrivekostnaden
Se for deg en hendelsestabell som tar imot tusenvis av rader per sekund. Hver ekstra indeks gjør at hver innsetting krever mer arbeid, med oppsplitting av indekssider, oppdatering av blader og konkurranse om hurtigbufferen.
For en tabell der rader bare legges til, og som hovedsakelig brukes til skriving, er det riktige svaret ofte få eller ingen indekser utover primærnøkkelen. Tunge leseoperasjoner kan i stedet utføres på en replika eller i et datavarehus.
-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts ON events (created_at);
INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now()); -- now updates table + 3 indexesHva selektivitet betyr
Selektivitet beskriver hvor godt en kolonne skiller mellom rader, altså andelen rader en typisk verdi treffer. Høy selektivitet betyr få rader per verdi, som for en e-postadresse eller en UUID. Lav selektivitet betyr mange rader per verdi, som for en boolsk verdi eller en status med tre alternativer.
Indekser lønner seg på kolonner med høy selektivitet, der et oppslag eliminerer nesten alle radene. På kolonner med lav selektivitet gjør de ofte ikke det.
Hvorfor en indeks med lav selektivitet er ubrukelig
Anta at is_active er true for 90 % av brukerne. Et indeksoppslag ville returnert 90 % av tabellen, og for så mange rader måtte motoren gjort én heap-henting per rad. Det ville vært tregere enn å skanne tabellen sekvensielt i én omgang.
Derfor ignorerer planleggeren indeksen og utfører en sekvensiell skanning. Indeksen medfører da bare skrivekostnader og lagringsbruk, uten å gi noen lesegevinst.
-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;Den omtrentlige terskelen
En nyttig tommelfingerregel å nevne: Når et predikat treffer mer enn omtrent 5 til 20 % av en tabell, er en sekvensiell skanning vanligvis raskere enn en indeksskanning, fordi tilfeldige heap-hentinger koster mer enn å lese sider sekvensielt.
Det nøyaktige vendepunktet avhenger av radstørrelse, hurtigbufring og lagringshastighet. Derfor bruker planleggeren statistikk, ikke et fast tall, når den tar avgjørelsen.
Delindekser til unnsetning
Hvis du bare spør etter de sjeldne verdiene i en skjevfordelt kolonne, kan en delindeks (Postgres) indeksere bare disse radene – liten, selektiv og billig å vedlikeholde.
Hvis 1 % av ordrene har status pending, og det er disse du stadig spør etter, bør du bare indeksere dem. Indeksen holder seg liten, og planleggeren bruker den gjerne.
-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'pending';Utdatert statistikk villeder planleggeren
Optimalisereren avgjør om den skal bruke indeks eller skanning, basert på kolonnestatistikk. Hvis statistikken er utdatert, for eksempel etter en masseinnlasting eller en stor oppdatering, kan den feilvurdere selektiviteten og velge feil plan.
Når en intervjuer sier «Indeksen finnes, men brukes ikke», bør et godt svar inkludere at statistikken oppdateres med ANALYZE før man legger skylden på selve indeksen.
ANALYZE orders; -- refresh planner statisticsAndre måter indekser kan skade på
Ta også med de mindre kjente kostnadene:
- Lagring og hurtigbuffer: Indekser opptar diskplass og konkurrerer om minne, slik at nyttige datasider fortrenges.
- Overflødige eller overlappende indekser: De vedlikeholdes, men blir aldri valgt.
- Oppsvulming: Ved mange oppdateringer blir B-trær fragmenterte og trenger
REINDEX. - Forvirring for optimalisereren: For mange lignende indekser gjør planleggingen tregere og mindre forutsigbar.
Slik finner du ubrukte indekser
For å begrunne en opprydding i praksis bør du nevne at Postgres sporer indeksbruk. Indekser med idx_scan = 0 er kandidater for sletting; de koster skrivearbeid og plass uten noen gang å brukes til lesing.
SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;Slik kan du formulere det i intervjuet
En fullstendig og balansert oppsummering:
«Indekser medfører write amplification – hver innsetting, oppdatering og sletting vedlikeholder dem – i tillegg til press på lagring og hurtigbuffer. De lønner seg bare for predikater med høy selektivitet; på en kolonne der de fleste radene samsvarer, foretrekker planleggeren med rette en sekvensiell skanning, så indeksen er ren kostnad. For skjevfordelte kolonner bruker jeg en delindeks, holder statistikken oppdatert med ANALYZE og sletter ubrukte indekser.»
Kort sjekk
Vurder hvilken indeks som minst sannsynlig er verdt kostnaden.
Oppsummering: Når indekser skader
Viktige poeng:
- Hver indeks medfører write amplification samt kostnader for lagring og hurtigbuffer.
- Indekser hjelper på kolonner med høy selektivitet; på kolonner med lav selektivitet foretrekker planleggeren en sekvensiell skanning.
- Når mer enn omtrent 5 til 20 % av radene samsvarer, vinner en skanning vanligvis.
- Bruk en delindeks for skjevfordelte kolonner du bare spør etter ved de sjeldne verdiene.
- Hold statistikken oppdatert med
ANALYZE, og slett ubrukte indekser (idx_scan = 0).
Dermed er kurset om indekseringsstrategier fullført: Opprett indekser der de gir verdi, og bevis det med planen.
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
Ofte stilte spørsmål
Er leksjonen «Når indekser gjør skade: skriving og selektivitet» gratis?
Ja – hele teksten i «Når indekser gjør skade: skriving og selektivitet» 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 «Når indekser gjør skade: skriving og selektivitet»?
Skriveforsterkning og hvorfor en indeks på en kolonne med lav selektivitet er ubrukelig. 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 «Når indekser gjør skade: skriving og selektivitet»?
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
- B-tre-indekser og hvordan de hjelper
- Kolonnerekkefølge i sammensatte indekser
- Dekketøyindekser og Index-Only-skanninger
- Når indekser gjør skade: skriving og selektivitet