CTE, alikysely vai väliaikainen taulu
Materialisoinnin, uudelleenkäytön ja optimoijan toiminnan kompromissit
CTE, alikysely vai väliaikainen taulu on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 3/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.
Kolme tapaa jäsentää logiikkaa
Kun kysely tarvitsee välituloksen, käytettävissänne on kolme yleistä työkalua: alikysely, CTE ja väliaikainen taulu. Haastattelijat pyytävät vertailemaan niitä, koska valinta kertoo, ymmärrättekö materialisoinnin ja optimoijan toiminnan.
Tässä oppitunnissa rakennetaan päätöksentekokehys, jonka voitte kerrata paineen alla.
Alikysely
Alikysely on toisen kyselyn sisään upotettu kysely, joka sijaitsee usein FROM-, WHERE- tai SELECT-osassa. Se kuuluu samaan lauseeseen, ja optimoija käsittelee sitä yhtenä kokonaisuutena.
- Nimeä ei tarvita (johdetut taulut tarvitsevat kuitenkin aliaksen).
- Optimoija voi yhdistää sen ympäröivään kyselyyn.
- Rakenne muuttuu monimutkaiseksi ja vaikealukuiseksi, jos sisäkkäisyyttä on paljon.
SELECT *
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) t
WHERE t.total > 1000;CTE
CTE on WITH-lohkossa määritelty nimetty alikysely, jonka näkyvyys rajoittuu yhteen lauseeseen. Se on selkeämpi kuin syvästi sisäkkäinen alikysely, ja siihen voidaan viitata useita kertoja.
- Nimi dokumentoi tarkoituksen.
- Siihen voidaan viitata useammin kuin kerran samassa lauseessa.
- Sen näkyvyys rajoittuu silti yhteen lauseeseen, minkä jälkeen se katoaa.
WITH spend AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;Väliaikainen taulu
Väliaikainen taulu on todellinen fyysinen taulu, joka säilyy istunnon (tai transaktion) ajan. Täytätte sen yhdellä lauseella ja kysytte siitä myöhemmissä, erillisissä lauseissa.
- Se säilyy istunnon aikana useiden lauseiden yli.
- Siihen voidaan luoda indeksejä ja sille voidaan kerätä tilastoja.
- Se aiheuttaa levy-I/O:ta ja edellyttää erillistä siivousta.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;
SELECT * FROM spend WHERE total > 1000;Materialisointi: keskeinen ero
Haastattelijoita kiinnostava keskeinen käsite on materialisointi: kirjoitetaanko välitulos fyysisesti jonnekin.
- Alikyselyitä ja CTE:itä ei yleensä materialisoida, vaan optimoija sisällyttää ne usein suoraan kyselyyn.
- Väliaikainen taulu materialisoidaan aina tallennustilaan.
- Joissakin tietokannoissa CTE:n materialisointi voidaan pakottaa tai estää vihjeillä.
Optimoinnin esteet ja vanha Postgres-ansa
Historiallisesti PostgreSQL käsitteli jokaista CTE:tä optimoinnin esteenä, materialisoi sen ja esti ehtojen työntämisen alemmas kyselyssä. Postgres 12:sta lähtien yksinkertaiset, kerran viitatut rekursiivittomat CTE:t sisällytetään oletusarvoisesti suoraan kyselyyn. Vihjeillä MATERIALIZED ja NOT MATERIALIZED tämä voidaan ohittaa.
Tämän yksityiskohdan mainitseminen osoittaa vahvaa senioritason ymmärrystä.
WITH spend AS NOT MATERIALIZED (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;Uudelleenkäyttö yhdessä lauseessa
Jos samaan välitulokseen viitataan useita kertoja yhdessä lauseessa, CTE voi olla selkeämpi kuin alikyselyn toistaminen. Huomioikaa kuitenkin, että suoraan kyselyyn sisällytetty CTE saatetaan laskea uudelleen jokaisen viittauksen yhteydessä.
Jos uudelleenlaskenta on kallista, materialisoinnin pakottaminen tai väliaikaisen taulun käyttö estää saman työn tekemisen kahdesti.
Uudelleenkäyttö lauseiden välillä
CTE:t ja alikyselyt ovat voimassa vain yhden lauseen ajan. Jos tarvitsette samaa tulosta useissa erillisissä kyselyissä, väliaikainen taulu on oikea työkalu.
Tyypillinen tapaus on monivaiheinen ETL-prosessi tai raportti, jossa valmistelujoukko rakennetaan kerran ja sen perusteella suoritetaan useita analyyseja. Väliaikaisen taulun indeksointi voi silloin nopeuttaa jokaista seuraavaa kyselyä.
Indeksointi ja tilastot
Vain väliaikaiseen tauluun voidaan liittää indeksit ja tuoreet tilastot. Jos valtava välitulostaulu yhdistetään monta kertaa, tällä voi olla ratkaiseva merkitys.
- CTE/alikysely: optimoija tekee arviot perustana olevien taulujen perusteella.
- Väliaikainen taulu: voitte suorittaa sille
ANALYZE-komennon ja lisätä indeksejä myöhempiä liitoksia varten.
Siksi väliaikainen taulu voi olla suorituskyvyn kannalta parempi suurille, paljon uudelleenkäytettäville tuloksille lisävaiheista huolimatta.
Päätöksenteon viitekehys
Tiivis vastaus työhaastatteluun:
- Alikysely: kertaluonteinen ja vähän sisäkkäinen, kun luettavuus on riittävä.
- CTE: parantaa luettavuutta tai siihen viitataan muutaman kerran yhdessä lauseessa.
- Väliaikainen taulu: tulosta käytetään useissa lauseissa, se on erittäin suuri tai tarvitsette indeksejä ja tilastoja.
Käyttäkää oletuksena CTE:tä selkeyden vuoksi ja valitkaa väliaikainen taulu, kun materialisointi tai uudelleenkäyttö eri lauseissa aidosti auttaa.
Kompromissin sanoittaminen
Välttäkää ehdottomia väitteitä, kuten ”CTE:t ovat aina hitaampia”. Sanokaa sen sijaan: CTE:t ja alikyselyt sisällytetään yleensä suoraan kyselyyn, joten kyse on luettavuudesta; väliaikainen taulu materialisoidaan, ja se on järkevä, kun käytän suurta tulosta uudelleen useissa lauseissa tai tarvitsen indeksin.
Sen tunnustaminen, että toiminta riippuu tietokantamoottorista ja Postgresissa myös versiosta, osoittaa todellista ymmärrystä.
Pikatarkistus
Valitkaa tilanne, jossa väliaikainen taulu on selvästi parempi valinta.
Kertaus: CTE vs. alikysely vs. väliaikainen taulu
Valinta perustuu materialisointiin ja näkyvyysalueeseen.
- Alikyselyt ja CTE:t: sisällytetään yleensä suoraan kyselyyn, niiden näkyvyys rajoittuu yhteen lauseeseen ja ne valitaan luettavuuden vuoksi.
- CTE:t lisäävät nimeämisen ja uudelleenkäytön yhden lauseen sisällä.
- Väliaikaiset taulut: materialisoidaan aina, säilyvät useiden lauseiden yli ja niihin voidaan luoda indeksejä.
- Postgres 12+ sisällyttää yksinkertaiset CTE:t suoraan kyselyyn; hallitkaa tätä
MATERIALIZED-vihjeillä.
Seuraavaksi: sekavan sisäkkäisen kyselyn refaktorointi selkeiksi CTE:iksi.
Opi SQL tekoälytuutorin avulla — ilmaiseksi
Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.
- Kurssit
- 30
- Oppitunnit
- 120
Usein kysytyt kysymykset
Onko oppitunti ”CTE, alikysely vai väliaikainen taulu” ilmainen?
Kyllä – oppitunnin ”CTE, alikysely vai väliaikainen taulu” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL-työhaastatteluun valmistautuminen-kurssin, päivitä CoddyKit PROhon. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”CTE, alikysely vai väliaikainen taulu”?
Materialisoinnin, uudelleenkäytön ja optimoijan toiminnan kompromissit Harjoittelet SQL-työhaastatteluun valmistautuminen-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni SQL-työhaastatteluun valmistautuminen-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin SQL-työhaastatteluun valmistautuminen-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 3/4.
Kuinka kauan ”CTE, alikysely vai väliaikainen taulu”-oppitunnin suorittaminen kestää?
Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.
Voinko kirjoittaa ja suorittaa koodia tällä SQL-työhaastatteluun valmistautuminen-oppitunnilla?
Kyllä. Jokainen SQL-työhaastatteluun valmistautuminen-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.
Kaikki tämän kurssin oppitunnit
- Ensimmäisen CTE:n kirjoittaminen
- Useiden CTE:iden ketjuttaminen
- CTE, alikysely vai väliaikainen taulu
- Sisäkkäisten kyselyjen uudelleenjärjestäminen CTE:iksi