SQL-indexeringExplained

31 of 31 rows

Begrepp↕	Förklaring↕	Tips↕
Indextyper
⤢B-tree	Balanserat träd för likhet, intervall och sorterade skanningar. Standard i de flesta databaser.	Använd för =, <, >, BETWEEN, ORDER BY. Inte för fulltextsökning.
⤢Clustered	Index vars bladnivå är själva tabellen. Raderna lagras i nyckelordning. Ett per tabell.	InnoDB klustrar på primärnyckeln. SQL Server-tabeller utan en sådan är heaps.
⤢Nonclustered	Separat struktur med nycklar plus en pekare tillbaka till bastabellen. Många per tabell.	På ett heap lagrar den ett rad-ID. På en klustrad tabell lagrar den klustringsnyckeln.
⤢Hash	Hashtabell endast för likhetssökningar. Exakt träff i konstant tid.	Använd endast för =-operationer; intervall och sortering kräver B-tree. Hashindex för minnesoptimerade SQL Server-tabeller behöver ett bucket-antal nära radantalet.
⤢GIN	Generalized Inverted Index. Avbildar värden till rader. Byggt för arrayer och fulltext.	Använd för jsonb, arrayer, tsvector och trigrammsökning. Långsammare att bygga än B-tree.
⤢GiST	Generalized Search Tree. Utbyggbar för icke-skalära typer och överlappningsfrågor.	Använd för geometriska intervall, kNN och exkluderingsvillkor. pg_trgm snabbar upp LIKE.
⤢SP-GiST	Rumsmångfaldat GiST. Delar data i icke-överlappande regioner via radix- eller kvadratträd.	Använd för punkter, IP-prefix och prefixmatchning. Inte för överlappningsfrågor; använd GiST.
⤢BRIN	Block Range Index. Lagrar min/max per blockintervall. Ytterst litet fotavtryck.	Använd på stora, fysiskt sorterade tabeller. Billigt att bygga, svag selektivitet.
⤢Columnstore	Index lagrat som kolumnsegment i stället för rader. Hög komprimering, batchkörning.	Använd för analytiska skanningar över många rader och få kolumner. Uppslag av enskilda rader är långsamma.
⤢Full-text	Dedikerat inverterat ordindex i MySQL och SQL Server. Byggs per textkolumn.	Använd MATCH AGAINST eller CONTAINS. Postgres fulltext använder i stället GIN på tsvector.
⤢Bitmap	Oracle-index som lagrar en bitmap per distinkt värde. Varje bit markerar en rad.	Använd på kolumner med låg kardinalitet i lästunga datalager. Samtidiga skrivningar serialiseras per bitmap.
⤢Descending	Indexkolumn deklarerad DESC. Nycklar för kolumnen lagras i omvänd ordning.	Nödvändigt för blandade sorteringar som (a ASC, b DESC). Rena DESC-sorteringar kan också läsa ett normal index baklänges.
⤢Invisible	MySQL 8-index som optimeraren ignorerar. Underhålls ändå vid varje skrivning.	Dölj ett index innan du tar bort det för att testa effekten. Gör det synligt igen utan ombyggnad.
Design
⤢Composite	Flerkolumnindex. De ledande kolumnerna måste matcha frågans filterordning.	Ordna med likhet först, sedan intervall. Två enskilda index kan inte ersätta ett sammansatt.
⤢Partial	Index på ett WHERE-villkor. SQL Server kallar det ett filtrerat index. Täcker bara matchande rader.	Indexera endast rader med active='t'. Halverar storlek och skrivkostnad.
⤢Covering (INCLUDE)	B-tree med extra icke-nyckelkolumner tillagda. Skanning enbart från indexet undviker heap-uppslag.	Lägg till ofta hämtade kolumner via INCLUDE. Håller indexet sorterbart.
⤢Unique	Index som förbjuder dubblettvärden. Avvisar infogningar som krockar.	Använd för naturliga nycklar och ett-till-ett-villkor. UNIQUE med NULL tillåter många NULL.
⤢Expression	Index byggt på en funktion av kolumner i stället för kolumnerna själva.	Indexera lower(email) eller date_trunc('day', created_at). Frågan måste upprepa exakt samma uttryck.
⤢Opclass	Operatorklass. Definierar hur ett index jämför och lagrar en kolumns typ.	Sätt varchar_pattern_ops för LIKE 'abc%' utan skiftlägesvikning. Standardvärdena passar likhet och intervall.
⤢Index-only scan	Plan som besvarar en fråga enbart från indexsidor. Heappen läses aldrig.	Kräver varje hämtad kolumn i indexet eller i INCLUDE-listan. Vacuum håller visibility map aktuell.
⤢Foreign keys	Främmande-nyckel-kolumner får inget automatiskt index i den refererande tabellen.	Indexera varje FK-kolumn du sammanfogar eller tar bort genom. Kaskadborttagningar skannar utan ett sådant hela barn-tabellen.
Underhåll
⤢EXPLAIN	Planinspektör. Visar sekventiella skanningar, indexval och kostnadsuppskattningar.	Använd EXPLAIN (ANALYZE, BUFFERS). En Seq Scan på en tabell med 10 miljoner rader betyder saknat index.
⤢ANALYZE	Samplar tabellkolumner för att bygga plannerstatistik. Histogram styr indexvalet.	Kör efter massinläsning. Gammal statistik ger seq scans på indexerade kolumner.
⤢Over-indexing	Varje index ökar skrivkostnaden. Inserts, uppdateringar och borttagningar betalar per index.	Ta bort oanvända index. Följ användningen i pg_stat_user_indexes.
⤢pg_stat_user_indexes	Postgres-vy över användning per index. Räknar skanningar och lästa tupler sedan statistiknollställningen.	Filtrera på idx_scan = 0 för att hitta oanvända index. Nollställ räknare med pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Bygger ett index utan att blockera läsning eller skrivning. Kräver två tabellskanningar.	Kan inte köras i en transaktion. En misslyckad byggnad lämnar ett INVALID-index att ta bort.
⤢DROP INDEX	Tar bort ett index och frigör dess lagring. Tar ett exklusivt lås som standard.	Använd DROP INDEX CONCURRENTLY på upptagna tabeller. Kontrollera användningsstatistik innan borttagning.
⤢REINDEX	Bygger om ett uppblåst index. Återvinner utrymme från döda tupler.	Använd REINDEX CONCURRENTLY i produktion. Lås blockerar skrivningar utan det.
⤢Bloat	Döda tupler och sidhål som ansamlas i MVCC-tabeller och deras index.	Mät med pgstattuple. Kör vacuum ofta; REINDEX när dött utrymme dominerar.
⤢Fillfactor	Procent av en sida som packas vid skrivning. Ledigt utrymme tar upp framtida uppdateringar.	Sänk till 70-90 på tabeller med många uppdateringar. Fulla sidor tvingar fram sidklyvningar.
⤢CLUSTER	Skriver om tabellen fysiskt ordnad efter ett index. Engångsoperation.	Paras med BRIN för sorterade block. Ordningen behålls inte vid framtida skrivningar.