En långsam SELECT som tidigare gick på 20 millisekunder hänger sig plötsligt i 12 sekunder. Databasen växte från 50 000 rader till 5 miljoner, och varje fråga blev ett lotteri. Känns det igen?

Index är något alla har hört talas om men få konfigurerar medvetet. Du lade till ett par, det verkade gå snabbare, och så gick du vidare. Ett halvår senare är INSERT långsammare än SELECT utan index, eftersom varje insert bygger om fem onödiga B-träd.

Låt oss bryta ner hur index faktiskt fungerar, vilka typer som finns och framför allt vilka regler du ska följa när du bygger dem, så att du inte ställer till det i produktion.

💡 Snabb översikt:

• Förstå mekaniken bakom B-träd-, hash- och sammansatta index, för utan den kunskapen är det omöjligt att välja rätt typ
• Bemästra 7 viktiga principer: från att indexera främmande nycklar till att ta bort oanvända index
• Lär dig läsa EXPLAIN och skilja ett användbart index från ett värdelöst

Vad är ett databasindex

Ett databasindex är en separat struktur som lagrar sorterade värden från en eller flera tabellkolumner tillsammans med pekare till raderna. När du kör SELECT ... WHERE category_id = 42 scannar servern utan index tabellen rad för rad (full tabellscanning). Med ett index hittar den rätt poster på O(log n), ungefär som att slå upp ett namn i en telefonkatalog.

Ett index skapas 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 priset för snabbare läsningar är långsammare skrivningar. Varje INSERT, UPDATE och DELETE måste uppdatera inte bara tabellen utan alla associerade index. Tre index på en tabell med en miljon rader, och bulkinsertar blir en tiopotens långsammare. Balansen mellan läshastighet och skrivhastighet är den centrala frågan i indexdesign.

En detaljerad genomgång av ämnet av Hussein Nasser: 497 000 prenumeranter, ett ingenjörsmässigt angreppssätt utan utfyllnad. Med PostgreSQL som exempel visar han indexens interna mekanik, varför ett CREATE INDEX snabbar upp en fråga 100 gånger medan ett annat inte gör någon skillnad alls.

Indextyper: när du ska använda vilken

Valet av indextyp avgör hur effektivt databasen bearbetar din fråga. Olika DBMS implementerar dem olika, men principerna är universella.

B-träd (balanced tree)

Standardtypen i de flesta relationsdatabaser. PostgreSQL, MySQL, Oracle och SQL Server använder alla B-träd som standardindex. Det lagrar nycklar i sorterad ordning, stöder jämförelseoperationer, intervall (BETWEEN), prefixsökning (LIKE 'prefix%') och sortering. Det är det bästa valet i de allra flesta fall.

Hash-index

Fungerar bara för exakta likhetsjämförelser =. Blixtsnabbt på punktfrågor men oanvändbart för intervall och sortering. I PostgreSQL blev hash-index produktionsredo från och med version 10; i MySQL är de inte tillgängliga i InnoDB (endast i MEMORY).

Klustrat index

Definierar den fysiska ordningen på raderna på disk. I MySQL InnoDB är primärnyckeln alltid klustrad: rader lagras i PRIMARY KEY-ordning. I SQL Server finns ett klustrat index per tabell. Att välja rätt klustrad nyckel (monotont ökande: BIGSERIAL, AUTO_INCREMENT eller UNIQUEIDENTIFIER med NEWSEQUENTIALID()) ger vinster på intervallfrågor och förhindrar sidfragmentering.

Sammansatt index

Ett index på två eller flera kolumner. Avgörande för frågor som filtrerar och sorterar på flera kolumner:

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

Kolumnordningen spelar roll: sätt kolumnen med högst selektivitet som används i WHERE först. Principen om vänsterprefix: ett index (A, B, C) fungerar för WHERE A och WHERE A AND B, men inte för WHERE B AND C.

Täckande index

Innehåller alla kolumner frågan behöver, både för filtrering och för utdata. Databasen hämtar allt från indexet utan att röra tabellen. I PostgreSQL görs detta med INCLUDE-kolumner; i MySQL InnoDB sker implicit täckning via det klustrade indexet.

7 Viktiga indexeringsprinciper

1. Indexera kolumner som används i WHERE

Första regeln: varje kolumn som regelbundet används i WHERE, JOIN ... ON och HAVING bör indexeras. Det är dessa operationer som drar störst nytta av index.

Innan du skapar ett index, kontrollera kolumnens selektivitet. Om kolumnen status bara har tre värden ('ny', 'under behandling', 'klar') och det finns 2 miljoner rader, är ett index på status nästan värdelöst: planeraren kommer att välja en full tabellscanning som det billigare alternativet. Ett index är meningsfullt när antalet unika värden är tillräckligt stort i förhållande till tabellstorleken.

2. Indexera kolumner som används för sortering

ORDER BY utan index innebär filesort (MySQL) eller explicit sort (PostgreSQL): servern samlar in alla rader och sorterar dem i minnet (eller på disk om work_mem/sort_buffer_size är för liten). Ett index på samma kolumner som ORDER BY kommer gratis, eftersom data redan är sorterad i B-trädet.

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. Indexera kolumner som används i GROUP BY och aggregeringar

Gruppering utan index kräver en full scanning och att en hashtabell byggs upp. Ett index på GROUP BY-kolumnerna gör om operationen till en strömmande aggregering: raderna är redan grupperade i nyckelordning.

4. Indexera alla främmande nycklar

En oindexerad främmande nyckel är en tickande bomb. DELETE FROM users WHERE id = 5 med en FOREIGN KEY (user_id) REFERENCES users(id) på orders-tabellen men utan index på user_id innebär en full tabellscanning av orders för varje borttagning. Alla populära DBMS kräver ett index på den främmande nyckeln eller skapar ett implicit (MySQL InnoDB gör det automatiskt, PostgreSQL gör det inte).

5. Indexera unika kolumner och primärnycklar

Primärnyckeln indexeras automatiskt (ofta som ett klustrat index). Ett explicit UNIQUE INDEX skyddar mot dubbletter och snabbar också upp uppslagningar. Varje kolumn med ett affärsmässigt unikhetskrav (till exempel email, slug eller external_id) bör ha ett unikt index, både för integriteten och för prestandan.

6. Använd det klustrade indexet medvetet

För stora tabeller (tiotals miljoner rader) är rätt klustrad nyckel avgörande. Ett bra val är ett monotont ökande värde: AUTO_INCREMENT, BIGSERIAL eller UUID v7. Ett slumpmässigt UUID som klustrad nyckel orsakar sidfragmentering: varje insert hamnar på en slumpmässig plats i B-trädet och splittrar fulla sidor till två halvfyllda.

7. Ta bort oanvända index

Ett index som inga frågor använder är en ren förlust. Det saktar ner skrivningar, tar upp diskutrymme och buffer pool-minne och vilseleder frågeplaneraren. I PostgreSQL ger systemtabellen pg_stat_user_indexes en lista över oanvända index:

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

I MySQL finns liknande information i sys.schema_unused_indexes (från och med version 5.7). Schemalägg en månatlig granskning och ta bort index som inte har använts en enda gång under rapportperioden.

Så kontrollerar du att dina index fungerar

När du har skapat ett index, verifiera att det faktiskt används. Kommandot EXPLAIN (eller EXPLAIN ANALYZE) visar frågans exekveringsplan och faktisk indexanvändning:

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

I utdatan, leta efter Index Scan eller Index Only Scan (PostgreSQL) / Using index (MySQL). Om du ser Seq Scan (PostgreSQL) eller Using where; Using filesort (MySQL) används inte indexet. Möjliga orsaker: låg selektivitet, fel indextyp, felaktig kolumnordning i ett sammansatt index eller inaktuell statistik (ANALYZE table_name;).

Övervaka mätvärden regelbundet: pg_stat_user_indexes.idx_scan i PostgreSQL, sys.schema_index_statistics i MySQL. Ett index med noll scanningar under en månad är en kandidat för borttagning.

⁉️🤔 Vanliga frågor

Hur många index bör en tabell ha?

Idealt 2 till 6 index per aktivt använd tabell. Färre än två innebär nästan säkert att vissa frågor är suboptimala. Fler än sex, och du bör noggrant kontrollera om alla verkligen behövs: varje extra index saktar ner skrivningar. För uppslagstabeller (sällan skrivningar, ofta läsningar) är fler index motiverade. För högtrafikerade operationella tabeller (mycket INSERT/UPDATE) bör du hålla antalet till ett minimum.

Hur är ett sammansatt index bättre än flera enkolumnsindex?

Ett sammansatt index (A, B) är EN struktur. Servern traverserar det en gång. Tre separata index (A), (B) och (C) för WHERE A=1 AND B=2 tvingar servern att antingen välja ett index (och filtrera resten), eller utföra en bitmap index scan (sammanfogning av bitmappar). Ett sammansatt index är nästan alltid mer effektivt, förutsatt att kolumnordningen matchar dina frågor.

När skadar ett index mer än det hjälper?

Tre typiska scenarier. För det första: tabellen är liten (upp till några tusen rader), och en full tabellscanning är snabbare än att läsa indexet plus hämta raderna. För det andra: ett index på en kolumn med låg selektivitet (is_deleted, status med tre värden). För det tredje: bulkinsertar under ETL/import, där index byggs om vid varje batch. Ta bort dem före inläsning och återskapa dem efteråt.

Bör du indexera kolumner som används i JOIN?

Absolut. Varje JOIN utan index på join-kolumnen i den yttre tabellen är en nested loop med en full scanning. För LEFT JOIN orders ON users.id = orders.user_id gör ett index på orders.user_id om den nested loop-en till en indexuppslagning. Indexera alltid de kolumner du joinar på.

B-träd eller hash: vilket ska man välja för exakta uppslagningar?

För = är hash snabbare: en hash-uppslagning tar konstant tid, medan B-träd traverserar trädet i ett logaritmiskt antal steg. Men hash stöder inte intervall, sortering eller UNIQUE. I praktiken täcker B-träd de allra flesta scenarier; hash är ett nischverktyg för punktuppslagningar på nyckel i högt belastade system (sessioner, cache:ar). I PostgreSQL har hash-index varit produktionsredo sedan version 10 och tar mindre plats än B-träd.

Ska du indexera "för säkerhets skull"?

Nej. Varje index är en avvägning. Det snabbar upp läsningar på bekostnad av långsammare skrivningar och extra diskutrymme. Indexera inte "för säkerhets skull"; indexera för specifika frågor som faktiskt körs i din applikation. Profilera långsamma frågor (pg_stat_statements, slow_query_log), lägg till index kirurgiskt och kontrollera EXPLAIN före och efter.

Huvudpoängen är enkel: index är ett verktyg, inte ett mål i sig. Ett väldesignat sammansatt index kan ersätta tre enkolumnsindex och spara gigabyte diskutrymme. Och ett oanvänt index på en skrivtung tabell kan sakta ner hela applikationen.

Om du vill gräva djupare, börja med den officiella designguiden för SQL Server-index och PostgreSQL-dokumentationen om indextyper. Och om du stöter på en fråga som index inte kan fixa, kan problemet ligga i själva datamodellen.