En treg SELECT som pleide å fullføre på 20 millisekunder begynner plutselig å henge i 12 sekunder. Databasen vokste fra 50 tusen rader til 5 millioner, og hver spørring ble et lotteri. Høres det kjent ut?

Indekser er noe alle har hørt om, men få konfigurerer bevisst. Du la til et par, ting virket raskere, og du gikk videre. Så, seks måneder senere, er INSERT tregere enn SELECT uten indeks, fordi hver innsetting bygger om fem unødvendige B-trær.

La oss bryte ned hvordan indekser faktisk fungerer, hvilke typer som finnes, og viktigst av alt, hvilke regler du bør følge når du bygger dem, slik at du ikke skaper kaos i produksjon.

💡 Rask oversikt:

• Forstå mekanikken bak B-tre-, hash- og sammensatte indekser, fordi det er umulig å velge riktig type uten denne kunnskapen
• Mestre 7 nøkkelpraksiser: fra å indeksere fremmednøkler til å droppe ubrukte indekser
• Lær å lese EXPLAIN og skille en nyttig indeks fra en ubrukelig

Hva er en databaseindeks

En databaseindeks er en separat struktur som lagrer sorterte verdier av én eller flere tabellkolonner sammen med pekere til radene. Når du kjører SELECT ... WHERE category_id = 42, skanner serveren uten indeks tabellen rad for rad (full tabellskanning). Med en indeks finner den de nødvendige postene i O(log n), som å slå opp et navn i en telefonkatalog.

En indeks opprettes med CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Men prisen for raskere lesing er tregere skriving. Hver INSERT, UPDATE og DELETE må oppdatere ikke bare tabellen, men alle tilknyttede indekser. Tre indekser på en tabell med en million rader, og bulkinnsettinger blir en størrelsesorden tregere. Balansen mellom lesehastighet og skrivehastighet er det sentrale spørsmålet i indeksdesign.

En detaljert gjennomgang av temaet av Hussein Nasser: 497 tusen abonnenter, en ingeniørmessig tilnærming uten fyllstoff. Med PostgreSQL som eksempel viser han den interne mekanikken til indekser, hvorfor én CREATE INDEX gir en 100x raskere spørring mens en annen ikke gjør noe.

Indekstyper: når du skal bruke hvilken

Valget av indekstype avgjør hvor effektivt databasen behandler spørringen din. Ulike DBMS-er implementerer dem forskjellig, men prinsippene er universelle.

B-tre (balansert tre)

Standardtypen i de fleste relasjons-DBMS-er. PostgreSQL, MySQL, Oracle og SQL Server bruker alle B-tre som standardindeks. Den lagrer nøkler i sortert rekkefølge, støtter sammenligningsoperasjoner, intervaller (BETWEEN), prefikssøk (LIKE 'prefix%') og sortering. Det er det beste valget i de aller fleste tilfeller.

Hash-indeks

Fungerer bare for eksakte likhetssammenligninger =. Lynrask på punktspørringer, men ubrukelig for intervaller og sortering. I PostgreSQL ble hash-indekser produksjonsklare fra og med versjon 10; i MySQL er de ikke tilgjengelige i InnoDB (bare i MEMORY).

Gruppert indeks

Definerer den fysiske rekkefølgen av rader på disken. I MySQL InnoDB er primærnøkkelen alltid gruppert: rader lagres i PRIMARY KEY-rekkefølge. I SQL Server finnes det én gruppert indeks per tabell. Å velge riktig gruppert nøkkel (monotont økende: BIGSERIAL, AUTO_INCREMENT eller UNIQUEIDENTIFIER med NEWSEQUENTIALID()) gir gevinster på intervallspørringer og forhindrer sidefragmentering.

Sammensatt indeks

En indeks på to eller flere kolonner. Kritisk for spørringer som filtrerer og sorterer etter flere kolonner:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Kolonnerekkefølgen har betydning: sett kolonnen med høyest selektivitet som brukes i WHERE først. Prinsippet om prefiks lengst til venstre: en indeks (A, B, C) fungerer for WHERE A og WHERE A AND B, men ikke for WHERE B AND C.

Dekkende indeks

Inneholder alle kolonnene spørringen trenger, både for filtrering og for utdata. Databasen henter alt fra indeksen uten å berøre tabellen. I PostgreSQL gjøres dette med INCLUDE-kolonner; i MySQL InnoDB skjer implisitt dekning gjennom den grupperte indeksen.

7 Nøkkelpraksiser for indeksering

1. Indekser kolonner brukt i WHERE

Den første regelen: hver kolonne som regelmessig brukes i WHERE, JOIN ... ON og HAVING bør indekseres. Dette er operasjonene som drar mest nytte av indekser.

Før du oppretter en indeks, sjekk kolonnens selektivitet. Hvis status-kolonnen bare har tre verdier ('ny', 'under behandling', 'ferdig') og det er 2 millioner rader, er en indeks på status nesten ubrukelig: planleggeren vil velge en full tabellskanning som det billigere alternativet. En indeks gir mening når antallet unike verdier er stort nok i forhold til tabellstørrelsen.

2. Indekser kolonner brukt til sortering

ORDER BY uten indeks betyr filesort (MySQL) eller eksplisitt sortering (PostgreSQL): serveren samler alle rader og sorterer dem i minnet (eller på disk hvis work_mem/sort_buffer_size er for liten). En indeks på de samme kolonnene som ORDER BY kommer gratis, siden dataene allerede er sortert i B-treet.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indekser kolonner brukt i GROUP BY og aggregeringer

Gruppering uten indeks krever en full skanning og bygging av en hashtabell. En indeks på GROUP BY-kolonnene gjør operasjonen om til en strømmende aggregering: radene er allerede gruppert i nøkkelrekkefølge.

4. Indekser alle fremmednøkler

En uindeksert fremmednøkkel er en tikkende bombe. DELETE FROM users WHERE id = 5 med en FOREIGN KEY (user_id) REFERENCES users(id) på orders-tabellen, men uten indeks på user_id, betyr en full tabellskanning av orders for hver sletting. Alle populære DBMS-er krever en indeks på fremmednøkkelen eller oppretter en implisitt (MySQL InnoDB gjør det automatisk, PostgreSQL gjør det ikke).

5. Indekser unike kolonner og primærnøkler

Primærnøkkelen indekseres automatisk (ofte som en gruppert indeks). En eksplisitt UNIQUE INDEX beskytter mot duplikater og øker også hastigheten på oppslag. Enhver kolonne med en forretningsmessig unikhetsbegrensning (for eksempel email, slug eller external_id) bør ha en unik indeks, både for integritet og for ytelse.

6. Bruk den grupperte indeksen bevisst

For store tabeller (titalls millioner rader) er riktig gruppert nøkkel kritisk. Et godt valg er en monotont økende verdi: AUTO_INCREMENT, BIGSERIAL eller UUID v7. En tilfeldig UUID som gruppert nøkkel forårsaker sidefragmentering: hver innsetting lander på et tilfeldig sted i B-treet og splitter fulle sider i to halvfulle.

7. Dropp ubrukte indekser

En indeks som ingen spørringer bruker, er et rent tap. Den bremser skriving, tar opp diskplass og buffer pool-minne, og villeder spørringsplanleggeren. I PostgreSQL gir systemtabellen pg_stat_user_indexes en liste over ubrukte indekser:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

I MySQL er lignende informasjon tilgjengelig fra sys.schema_unused_indexes (fra og med versjon 5.7). Planlegg en månedlig revisjon og dropp indekser som ikke har blitt brukt en eneste gang i løpet av rapporteringsperioden.

Hvordan bekrefte at indeksene dine fungerer

Når du har opprettet en indeks, bekreft at den faktisk blir brukt. Kommandoen EXPLAIN (eller EXPLAIN ANALYZE) viser spørringens utførelsesplan og faktisk indeksbruk:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

I utdataene, se etter Index Scan eller Index Only Scan (PostgreSQL) / Using index (MySQL). Hvis du ser Seq Scan (PostgreSQL) eller Using where; Using filesort (MySQL), blir ikke indeksen brukt. Mulige årsaker: lav selektivitet, feil indekstype, feil kolonnerekkefølge i en sammensatt indeks, eller utdatert statistikk (ANALYZE table_name;).

Overvåk målinger regelmessig: pg_stat_user_indexes.idx_scan i PostgreSQL, sys.schema_index_statistics i MySQL. En indeks med null skanninger over en måned er en kandidat for fjerning.

⁉️🤔 Ofte stilte spørsmål

Hvor mange indekser bør en tabell ha?

Ideelt sett 2 til 6 indekser per aktivt brukt tabell. Færre enn to betyr nesten helt sikkert at noen spørringer er suboptimale. Mer enn seks, og du bør nøye sjekke om alle faktisk er nødvendige: hver ekstra indeks bremser skriving. For oppslagstabeller (sjeldne skrivinger, hyppige lesinger) er flere indekser berettiget. For høy-trafikk operasjonelle tabeller (mye INSERT/UPDATE) bør du holde antallet på et minimum.

Hvordan er en sammensatt indeks bedre enn flere enkeltkolonneindekser?

En sammensatt indeks (A, B) er ÉN struktur. Serveren traverserer den én gang. Tre separate indekser (A), (B) og (C) for WHERE A=1 AND B=2 tvinger serveren til enten å velge én indeks (og filtrere resten), eller utføre en bitmap-indeksskanning (som slår sammen bitmaps). En sammensatt indeks er nesten alltid mer effektiv, forutsatt at kolonnerekkefølgen samsvarer med spørringene dine.

Når skader en indeks mer enn den hjelper?

Tre typiske scenarioer. For det første: tabellen er liten (opptil noen få tusen rader), og en full tabellskanning er raskere enn å lese indeksen pluss å hente radene. For det andre: en indeks på en kolonne med lav selektivitet (is_deleted, status med tre verdier). For det tredje: bulkinnsettinger under ETL/import, der indekser bygges om for hver batch. Dropp dem før lasting og gjenopprett dem etterpå.

Bør du indeksere kolonner som brukes i JOIN?

Absolutt. Hver JOIN uten en indeks på sammenføyningskolonnen i den ytre tabellen er en nøstet løkke med full skanning. For LEFT JOIN orders ON users.id = orders.user_id gjør en indeks på orders.user_id den nøstede løkken om til et indeksoppslag. Indekser alltid kolonnene du sammenføyer på.

B-tre eller hash: hva skal man velge for eksakte oppslag?

For = er hash raskere: et hash-oppslag tar konstant tid, mens B-tre traverserer treet i et logaritmisk antall steg. Men hash støtter ikke intervaller, sortering eller UNIQUE. I praksis dekker B-tre de aller fleste scenarioer; hash er et nisjeverktøy for punktoppslag etter nøkkel i systemer med høy last (sesjoner, cacher). I PostgreSQL har hash-indekser vært produksjonsklare siden versjon 10 og tar mindre plass enn B-tre.

Bør du indeksere "for sikkerhets skyld"?

Nei. Hver indeks er et kompromiss. Den øker lesehastigheten på bekostning av tregere skriving og ekstra diskplass. Ikke indekser "for sikkerhets skyld"; indekser for spesifikke spørringer som faktisk kjører i applikasjonen din. Profiler trege spørringer (pg_stat_statements, slow_query_log), legg til indekser kirurgisk, og sjekk EXPLAIN før og etter.

Hovedpoenget er enkelt: indekser er et verktøy, ikke et mål i seg selv. Én veltilpasset sammensatt indeks kan erstatte tre enkeltkolonneindekser og spare gigabyte med diskplass. Og én ubrukt indeks på en skrivetung tabell kan bremse hele applikasjonen.

Hvis du vil dykke dypere, start med den offisielle SQL Server-indeksdesignguiden og PostgreSQL-dokumentasjonen om indekstyper. Og hvis du støter på en spørring som indekser ikke kan fikse, kan problemet ligge i selve datamodellen.