CTE kontra underforespørgsel kontra midlertidig tabel
Afvejninger ved materialisering, genbrug og optimizerens adfærd
CTE kontra underforespørgsel kontra midlertidig tabel er en gratis Forberedelse til kodeinterviews-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 kodeinterviews, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.
Tre måder at opdele logik på
Når en forespørgsel har brug for et mellemresultat, har du tre almindelige værktøjer: en underforespørgsel, en CTE og en midlertidig tabel. Interviewere beder dig sammenligne dem, fordi valget viser, om du forstår materialisering og optimeringsprogrammets adfærd.
Denne lektion opbygger en beslutningsramme, som du kan gengive under pres.
Underforespørgslen
En underforespørgsel er en forespørgsel, der er indlejret direkte i en anden, ofte i FROM, WHERE eller SELECT. Den er en del af den samme sætning, og optimeringsprogrammet ser den som én samlet enhed.
- Der kræves intet navn (afledte tabeller skal dog have et alias).
- Optimeringsprogrammet kan frit flette den ind i den ydre forespørgsel.
- Den bliver omstændelig og svær at læse, når den indlejres i mange niveauer.
SELECT *
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) t
WHERE t.total > 1000;CTE'en
En CTE er en navngiven underforespørgsel i en WITH-blok, hvis omfang er begrænset til én sætning. Den er mere læselig end en dybt indlejret underforespørgsel og kan refereres til flere gange.
- Den har et navn, så formålet er dokumenteret.
- Den kan refereres til mere end én gang i den samme sætning.
- Omfanget er stadig begrænset til én sætning, hvorefter den forsvinder.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;Den midlertidige tabel
En midlertidig tabel er en rigtig, fysisk tabel, der lever i sessionen (eller transaktionen). Du udfylder den med én sætning og forespørger i den med senere, separate sætninger.
- Den består på tværs af flere sætninger i sessionen.
- Den kan indekseres, og der kan indsamles statistik for den.
- Den medfører disk-I/O og kræver eksplicit oprydning.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;
SELECT * FROM spend WHERE total > 1000;Materialisering: Den afgørende forskel
Det centrale begreb, som interviewere undersøger, er materialisering: om mellemresultatet fysisk skrives et sted.
- Underforespørgsler og CTE'er materialiseres normalt ikke; optimeringsprogrammet indlejrer dem ofte direkte.
- En midlertidig tabel materialiseres altid i lageret.
- Nogle databaser lader dig tvinge eller forhindre materialisering af CTE'er med optimeringsanvisninger.
Optimeringsbarrierer og den gamle Postgres-fælde
Historisk behandlede PostgreSQL enhver CTE som en optimeringsbarriere, materialiserede den og forhindrede nedskubning af prædikater. Siden Postgres 12 bliver enkle, ikke-rekursive CTE'er, der refereres til én gang, som standard indlejret, og MATERIALIZED- og NOT MATERIALIZED-anvisninger kan tilsidesætte dette.
At nævne denne nuance er et stærkt signal om seniorniveau.
WITH spend AS NOT MATERIALIZED (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;Genbrug i én sætning
Hvis du refererer til det samme mellemresultat flere gange i én sætning, kan en CTE være mere overskuelig end at gentage en underforespørgsel. Men pas på: En indlejret CTE kan blive beregnet igen ved hver reference.
Når genberegningen er dyr, undgår tvungen materialisering (eller brug af en midlertidig tabel) at udføre arbejdet to gange.
Genbrug på tværs af sætninger
CTE'er og underforespørgsler lever kun i én sætning. Hvis du har brug for det samme resultat i flere separate forespørgsler, er den midlertidige tabel det rigtige værktøj.
Et typisk eksempel er en ETL-proces i flere trin eller en rapport, hvor du opbygger et mellemresultat én gang og derefter kører flere analyser på det. Hvis du indekserer den midlertidige tabel, kan det fremskynde alle efterfølgende forespørgsler.
Indeksering og statistik
Kun en midlertidig tabel kan have indeks og friske statistikker. For et enormt mellemresultat, der sammenføjes mange gange, kan det være afgørende.
- CTE/underforespørgsel: Optimeringsprogrammet baserer sine estimater på de underliggende tabeller.
- Midlertidig tabel: Du kan køre
ANALYZEpå den og tilføje indeks, der er tilpasset dine senere sammenføjninger.
For store, intensivt genbrugte resultater kan en midlertidig tabel derfor give bedre ydeevne trods de ekstra trin.
Beslutningsrammen
Et præcist svar til interviewet:
- Underforespørgsel: enkeltstående, lavt indlejret, og læseligheden er fin.
- CTE: forbedrer læseligheden, eller du refererer til den nogle få gange i én sætning.
- Midlertidig tabel: genbruges på tværs af sætninger, er meget stor, eller du har brug for indeks og statistik.
Vælg som udgangspunkt en CTE for klarhedens skyld, og brug en midlertidig tabel, når materialisering eller genbrug på tværs af sætninger reelt hjælper.
Sådan formulerer du afvejningen
Undgå absolutte udsagn som CTE'er er altid langsommere. Sig i stedet: CTE'er og underforespørgsler indlejres normalt, så de handler primært om læselighed; en midlertidig tabel materialiseres og kan betale sig, når jeg genbruger et stort resultat på tværs af sætninger eller har brug for et indeks.
At anerkende, at adfærden afhænger af database- og versionen (og af Postgres-versionen), viser reel dybde.
Hurtig kontrol
Vælg det scenarie, hvor en midlertidig tabel klart er det bedste valg.
Opsummering: CTE kontra underforespørgsel kontra midlertidig tabel
Valget afhænger af materialisering og omfang.
- Underforespørgsler og CTE'er: normalt indlejret, begrænset til én sætning og valgt for læselighedens skyld.
- CTE'er tilføjer navngivning og genbrug i samme sætning.
- Midlertidige tabeller: altid materialiserede, består på tværs af sætninger og kan indekseres.
- Postgres 12+ indlejrer enkle CTE'er; brug MATERIALIZED-anvisninger til at styre det.
Næste trin: Omstrukturering af en sammenfiltret, indlejret forespørgsel til rene CTE'er.
Lær Forberedelse til kodeinterviews 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
- 90
- Lektioner
- 360
Ofte stillede spørgsmål
Er lektionen “CTE kontra underforespørgsel kontra midlertidig tabel” gratis?
Ja — hele teksten til “CTE kontra underforespørgsel kontra midlertidig tabel” 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 kodeinterviews-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.
Hvad lærer jeg i “CTE kontra underforespørgsel kontra midlertidig tabel”?
Afvejninger ved materialisering, genbrug og optimizerens adfærd Du øver dig i Forberedelse til kodeinterviews 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 kodeinterviews?
Der kræves ingen tidligere erfaring. Forberedelse til kodeinterviews 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 “CTE kontra underforespørgsel kontra midlertidig tabel”?
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 kodeinterviews-lektion?
Ja. Alle Forberedelse til kodeinterviews-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
- Skriv din første CTE
- Sammenkædning af flere CTE'er
- CTE kontra underforespørgsel kontra midlertidig tabel
- Omlægning af indlejrede forespørgsler til CTE'er