Forberedelse til SQL-interview · Lektion

Dækkende indekser og Index-Only-scanninger

Inkludering af kolonner, så en forespørgsel aldrig behøver at tilgå tabellens heap.

Lektion 3 af 413 trin

Dækkende indekser og Index-Only-scanninger er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til SQL-interview, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Genopfriskning af heap-hentning

Tidligere lærte du, at et normalt B-træ kun gemmer de indekserede kolonner samt en peger til rækken. Når indekset har fundet match, skal databasemotoren derfor stadig slå op i tabellen for at læse de øvrige kolonner. Det opslag er en heap-hentning, og det er den omkostning, et dækkende indeks er designet til at fjerne.

Interviewere spørger om dækkende indekser for at se, om du forstår hvorfor et indeks kan besvare en forespørgsel fuldstændigt uden at tilgå tabellen.

Hvad »dækkende« betyder

Et indeks dækker en forespørgsel, når alle kolonner, som forespørgslen har brug for i SELECT, WHERE, ORDER BY og GROUP BY, findes i selve indekset.

Når det er tilfældet, læser databasemotoren kun indekset og besøger aldrig tabellen. PostgreSQL kalder det en Index-Only Scan; SQL Server og andre kalder det et dækkende indeks. Fordelen er færre sidelæsninger og hurtigere forespørgsler.

Gennemgået eksempel: En dækket forespørgsel

Antag, at en forespørgsel kun har brug for customer_id og order_date. Et sammensat indeks med netop disse kolonner indeholder alt, hvad forespørgslen beder om, så den kan besvares udelukkende ud fra indekset.

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, order_date);

-- Covered: both selected columns are in the index
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 42;

Én ekstra kolonne fjerner dækningen

Tilføj en kolonne, som indekset ikke indeholder, så forsvinder dækningen, og databasemotoren må hente den fra heapen.

Her findes total ikke i indekset. Selvom customer_id styrer opslaget, udløser hver matchende række derfor en heap-hentning for at læse total.

-- NOT covered: total is not in the index, forces heap fetches
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

INCLUDE-sætningen

Du kunne tilføje total som en fjerde nøglekolonne, men hvis du aldrig filtrerer eller sorterer efter den, spilder det plads i træets sorteringsrækkefølge. Det renere værktøj er INCLUDE (understøttet af PostgreSQL og SQL Server): Det gemmer ekstra kolonner kun i indeksets blade som nyttedata, ikke som en del af sorteringsnøglen.

Nu er forespørgslen dækket, uden at den søgbare del af indekset bliver unødigt stor.

CREATE INDEX idx_orders_cust_date_inc
  ON orders (customer_id, order_date)
  INCLUDE (total);

-- Now covered: total is carried in the leaf
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

Nøglekolonner kontra inkluderede kolonner

En præcis skelnen, der imponerer interviewere:

Nøglekolonner definerer sorteringsrækkefølgen og kan bruges til opslag og områdescanning. De følger reglen om venstre præfiks.
Inkluderede kolonner gemmes kun i bladene som ekstra data. De kan ikke søges i, men de lader indekset dække flere forespørgsler.

Tommelfingerregel: Kolonner, du filtrerer eller sorterer efter, skal være nøglekolonner; kolonner, du kun returnerer, skal være med i INCLUDE.

MySQL/InnoDB: det klyngede twist

Vis, at du er bekendt med forskelle på databasesystemer. InnoDB-tabeller i MySQL er klynget efter primærnøglen: Sekundære indekser indeholder implicit primærnøglens kolonner. Derfor dækker et sekundært indeks automatisk enhver forespørgsel, der kun vælger de indekserede kolonner samt primærnøglekolonnerne. Der kræves ingen INCLUDE-sætning (MySQL har ikke INCLUDE).

Princippet bag dækkende indekser er universelt; syntaksen og de ekstra kolonner, der følger med uden særskilt omkostning, varierer fra databasemotor til databasemotor.

Kontrol af en Index-Only Scan

Påvis dækningen med EXPLAIN. I PostgreSQL viser planens node Index Only Scan i stedet for Index Scan. Se efter Heap Fetches: 0 i EXPLAIN (ANALYZE); det er det afgørende tegn på, at der ikke fandt nogen tabeladgang sted.

Hvis du forventede en Index-Only Scan, men ser Index Scan med heap-hentninger, mangler der en valgt kolonne i indekset.

EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0

Forbeholdet om Postgres' visibility map

En subtil Postgres-detalje, der er et ekstra point værd: En Index-Only Scan kan stadig tilgå heapen, hvis en side ikke er markeret som synlig for alle i visibility map. Efter mange opdateringer skal du køre VACUUM, så visibility map er opdateret. Ellers stiger Heap Fetches, og fordelen ved »index-only« bliver mindre.

-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;

Hvornår du IKKE skal bygge et bredt dækkende indeks

Dækkende indekser er ikke gratis. Hvis du fylder mange kolonner i INCLUDE, bliver indekset stort, bruger cacheplads og gør skrivninger langsommere (alle relevante skrivninger opdaterer indekset). De vigtigste afvejninger er:

Fremragende til hyppige læseforespørgsler, der er smalle og meget belastede.
Dårlige som losseplads for alle kolonner »for en sikkerheds skyld«.

Dæk den forespørgsel, der betyder noget, ikke hele rækken.

Sådan formulerer du det til interviewet

En klar opsummering:

»Et dækkende indeks indeholder alle kolonner, som en forespørgsel bruger, så databasemotoren kan besvare den udelukkende ud fra indekset med en Index-Only Scan og springe heap-hentningen over. Jeg placerer søgte kolonner i nøglen og kolonner, der kun returneres, i INCLUDE, kontrollerer med EXPLAIN ANALYZE, at Heap Fetches er nul, og holder indekset smalt for at beskytte skrivehastigheden.«

Hurtigt tjek

Overvej, hvad der skal til for at dække en forespørgsel, og hvor hver kolonne hører hjemme.

Besvar den i den interaktive lektion

Opsummering: dækkende indekser

Vigtigste pointer:

Et indeks dækker en forespørgsel, når det indeholder alle kolonner, forespørgslen har brug for, så der kan udføres en Index-Only Scan uden heap-hentning.
Nøglekolonner styrer opslag og følger reglen om venstre præfiks; INCLUDE-kolonner er nyttedata, der kun ligger i bladene, og som giver dækning.
Sekundære InnoDB-indekser indeholder implicit primærnøglen.
Kontrollér med EXPLAIN (ANALYZE), og hold øje med Heap Fetches; i Postgres skal VACUUM holdes opdateret.
Hold dækkende indekser smalle for at beskytte skrivehastigheden.

Næste emne: den anden side af sagen – hvornår indekser faktisk gør tingene værre.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 30
Lektioner: 120

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Dækkende indekser og Index-Only-scanninger” gratis?

Ja — hele teksten til “Dækkende indekser og Index-Only-scanninger” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til SQL-interview-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Dækkende indekser og Index-Only-scanninger”?

Inkludering af kolonner, så en forespørgsel aldrig behøver at tilgå tabellens heap. Du øver dig i Forberedelse til SQL-interview med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til SQL-interview?

Der kræves ingen tidligere erfaring. Forberedelse til SQL-interview på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.

Hvor lang tid tager lektionen “Dækkende indekser og Index-Only-scanninger”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til SQL-interview-lektion?

Ja. Alle Forberedelse til SQL-interview-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

B-træ-indekser, og hvordan de hjælper
Kolonnerækkefølge i sammensatte indekser
Dækkende indekser og Index-Only-scanninger
Når indekser skader: skrivninger og selektivitet

← Tilbage til Forberedelse til SQL-interview