PricingLoginStart free
SQL-työhaastatteluun valmistautuminen · Oppitunti

EXPLAIN-suunnitelman lukeminen

Kyselysuunnitelman skannaustyyppien, liitosmenetelmien ja kustannusarvioiden tulkitseminen.

Oppitunti 1/413 vaihetta

EXPLAIN-suunnitelman lukeminen on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 1/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.

Miksi haastattelijat kysyvät EXPLAIN-komennosta

Kun etenette senior-tason haastatteluun, haastattelijat lakkaavat kysymästä kirjoittakaa kysely ja alkavat kysyä miksi tämä kysely on hidas. Tähän vastaa komento EXPLAIN.

EXPLAIN näyttää tietokannan suoritussuunnitelman: vaiheittaisen strategian, jonka suunnittelija valitsi SQL:n suorittamiseen. Se paljastaa, mitä tauluja skannataan, missä järjestyksessä ne yhdistetään ja kuinka kallis kukin vaihe suunnilleen on.

Suunnitelman lukeminen osoittaa, että ymmärrätte moottoria ettekä vain syntaksia. Juuri tätä eroa haastattelijat käyttävät keskitason ja senior-tason osaajien välillä.

EXPLAIN ja EXPLAIN ANALYZE

Muotoja on kaksi, ja haastattelijat arvostavat niiden eron ymmärtämistä.

  • EXPLAIN näyttää suunnittelijan arvioiman suunnitelman suorittamatta kyselyä. Se on nopea ja turvallinen.
  • EXPLAIN ANALYZE suorittaa kyselyn oikeasti ja ilmoittaa arvioiden rinnalla todelliset rivimäärät ja suoritusajat.

Arvokkain vertailu tehdään arvioitujen ja todellisten rivimäärien välillä. Suuri ero tarkoittaa, että suunnittelijan tilastotiedot ovat huonoja ja se tekee todennäköisesti väärän valinnan.

Huomio: EXPLAIN ANALYZE suorittaa kyselyn oikeasti, joten se toteuttaa kaikki INSERT- tai UPDATE-toiminnot, ellei kyselyä suoriteta transaktiossa, joka peruutetaan.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Puun lukeminen

Suunnitelma on puu, ei luettelo. Eniten sisennetyt solmut ovat ensin suoritettavia lehtisolmuja; tulokset kulkevat ylöspäin juurisolmuun, joka tuottaa lopullisen tuloksen.

Lukekaa suunnitelmaa sisältä ulospäin: etsikää syvimmällä oleva solmu, sillä suoritus alkaa sieltä. Kukin vanhempi käsittelee rivit, jotka sen lapset tuottavat.

Selostakaa se haastattelussa näin: ensin skannaamme tämän taulun, nämä rivit syötetään tähän liitokseen, liitos syöttää rivit lajitteluun ja lajittelu syöttää ne rajoitukseen. Tällainen alhaalta ylöspäin etenevä selostus on juuri se, mitä haastattelija haluaa kuulla.

Suunnitelmasolmun rakenne

Jokainen Postgres-suunnitelman solmu sisältää samat keskeiset luvut:

  • cost=0.00..35.50 käynnistyskustannus .. kokonaiskustannus mielivaltaisina suunnittelijan yksikköinä
  • rows=1000 tuotettavien rivien arvioitu määrä
  • width=64 rivin arvioitu keskimääräinen koko tavuina

Ensimmäinen kustannus on käynnistyskustannus (ennen ensimmäisen rivin ilmestymistä tehtävä työ, kuten hajautustaulun rakentaminen). Toinen on kaikkien rivien palauttamiseen tarvittava kokonaiskustannus. Suurempi kokonaiskustannus tarkoittaa, että suunnittelija arvioi operaation suhteellisesti kalliimmaksi.

Seq Scan on orders  (cost=0.00..35.50 rows=1000 width=64)

Käytännön esimerkki

Tarkastellaan yksinkertaista suodatettua kyselyä. Alla oleva suunnitelma kertoo tarinan yhdellä rivillä.

Kyseessä on Seq Scan (koko taulukon luku) taululle orders, ja siihen sovelletaan suodatinta status = 'shipped'. Suunnittelija arvioi täsmääviä rivejä olevan 1000.

Jos orders-taulussa on 10 miljoonaa riviä ja vain 1000 täsmää, haastattelija odottaa teidän sanovan: sekventiaalinen skannaus on tässä tuhlaileva; indeksillä sarakkeeseen status (tai valikoivampaan sarakkeeseen) voisimme välttää koko taulukon lukemisen.

EXPLAIN SELECT * FROM orders WHERE status = 'shipped';

Seq Scan on orders  (cost=0.00..18334.00 rows=1000 width=64)
  Filter: (status = 'shipped'::text)

Arvioidut ja toteutuneet rivimäärät

Komennolla EXPLAIN ANALYZE saatte sulkeissa myös toteutuneet luvut.

Esimerkissä suunnittelija arvioi rivejä olevan 1000, mutta todellisuudessa niitä saatiin 480000. Tämä on 480-kertainen aliarvio. Suunnittelija valitsi strategian olettaen rivejä olevan vähän, joten valinta on todennäköisesti todellisten tietojen kannalta väärä.

Haastatteluissa tämä ero on tärkein diagnoosinne: tilastot ovat vanhentuneet; suorittakaa taululle ANALYZE, minkä jälkeen suunnittelija valitsee todennäköisesti paremman suunnitelman.

Seq Scan on orders
  (cost=0.00..18334.00 rows=1000 width=64)
  (actual time=0.02..210.4 rows=480000 loops=1)

Mitä loops=N tarkoittaa

loops-arvolla on suurempi merkitys kuin ehdokkaat usein odottavat. Se kertoo, kuinka monta kertaa solmu suoritettiin.

Tämä näkyy sisäkkäisen silmukkaliitoksen sisemmällä puolella: sisempi solmu suoritetaan jokaista ulomman puolen riviä kohden. Jos loops=480000, kyseinen sisempi vaihe suoritettiin 480 tuhatta kertaa.

Tärkeää: näytetty rivikohtainen aika ja rivimäärä ovat kierroskohtaisia. Todellisen kokonaismäärän saatte kertomalla luvun arvolla loops. Solmu, joka näyttää kierrosta kohden halvalta arvolla 0.004ms, vie 480000 kierroksella lähes 2 sekuntia.

Index Scan using idx_cust on orders
  (actual time=0.003..0.004 rows=1 loops=480000)

Kustannus on suhteellinen, ei millisekunteja

Yleinen ansa: ehdokkaat lukevat cost=18334 ja sanovat se kestää 18 sekuntia. Väärin.

Kustannus ilmoitetaan mielivaltaisina suunnittelijan yksikköinä, jotka on kalibroitu siten, että yksi sivun sekventiaalinen luku vastaa arvoa 1.0. Sillä on merkitystä vain suunnitelmien vertailussa, ei seinäkellon näyttämänä aikana.

Todelliseen ajoitukseen tarvitaan EXPLAIN ANALYZE ja sen actual time-arvot, jotka ilmoitetaan millisekunteina. Sanokaa tämä haastattelussa selkeästi; se osoittaa, että ymmärrätte mittarin todella.

Liitossuunnitelman lukeminen

Kyseessä on kahden taulukon suunnitelma. Lukekaa se alhaalta ylöspäin.

Kaksi ensimmäistä skannausta keräävät rivejä tauluista orders ja customers. Ne syöttävät rivit Hash Join-liitokselle: toinen puoli hajautetaan ja toinen puoli etsii tietoja hajautuksesta. Liitoksen tulos syötetään lopullisen tuloksen muodostamiseen.

Huomatkaa, että sisennys osoittaa rakenteen: molemmat skannaukset ovat Hash Join -liitoksen alla. Haastattelija haluaa teidän tunnistavan liitosmenetelmän (tässä hash) ja sen, mikä taulu hajautetaan (yleensä pienempi).

Hash Join  (cost=30.0..520.0 rows=900 width=72)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0..400 rows=10000)
  ->  Hash  (cost=18..18 rows=500)
        ->  Seq Scan on customers c  (cost=0..18 rows=500)

Esille nostettavat varoitusmerkit

Opetelkaa tunnistamaan nämä varoitusmerkit mistä tahansa suunnitelmasta:

  • Seq Scan suurella taululla ja valikoiva suodatin; indeksi voisi auttaa.
  • Arvioidut rivimäärät poikkeavat paljon toteutuneista; tilastot ovat vanhentuneet.
  • Nested Loop ja suuri loops-arvo suuren taulun yhteydessä; sisemmän liitossarakkeen indeksi puuttuu usein.
  • Sort tai Hash vuotaa levylle (näkyy Disk-käyttönä); work_mem on liian pieni.
  • Rows Removed by Filter on erittäin suuri; suurin osa taulusta luettiin ja hylättiin.

Tulostusmuodot ja BUFFERS

Suunnitelmia on saatavana useissa muodoissa. Oletusmuoto TEXT on se, jonka luette haastatteluissa ääneen. Voitte kuitenkin pyytää myös rakenteisen tulosteen.

EXPLAIN (FORMAT JSON) tai FORMAT YAML tuottaa koneellisesti luettavia suunnitelmia, joita työkalut ja koontinäytöt jäsentävät. Niitä tarvitsee harvoin käsitellä itse, mutta niiden olemassaolon tunteminen on hyvä kokeneen kehittäjän yksityiskohta.

Lisätkää asetukset sulkeisiin: EXPLAIN (ANALYZE, BUFFERS). BUFFERS-asetus ilmoittaa välimuistiosumat ja levyltä tehdyt luvut, mikä on erittäin hyödyllistä I/O-rajoitteisten kyselyiden vianmäärityksessä.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Pikatarkistus

Haastattelija näyttää teille EXPLAIN ANALYZE -solmun, jossa kustannusosiossa on rows=1000 mutta toteutuneissa arvoissa actual ... rows=480000. Mikä on todennäköisin diagnoosi?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Osaatte nyt lukea suunnitelmaa kuin kokenut kehittäjä:

  • EXPLAIN tekee arvioita, EXPLAIN ANALYZE suorittaa ja mittaa.
  • Lukekaa puu alhaalta ylöspäin; lehdet suoritetaan ensin ja juuri tuottaa tulosteen.
  • Jokainen solmu näyttää kustannuksen (suhteelliset yksiköt), rivit ja leveyden; actual time on todellinen millisekunteina ilmoitettu arvo.
  • loops kertoo, kuinka monta kertaa kierroskohtaiset luvut toistuvat; kiinnittäkää huomiota sisäkkäisiin silmukoihin.
  • Arvioitujen ja toteutuneiden rivimäärien välinen ero on tärkein diagnostinen signaalinne.

Selostakaa suunnitelma ääneen ja nostakaa varoitusmerkit esiin – juuri tällaista toimintaa haastattelussa arvostetaan.

Aloita maksutta

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
Aloita opiskelu maksuttaNäytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”EXPLAIN-suunnitelman lukeminen” ilmainen?

Kyllä – oppitunnin ”EXPLAIN-suunnitelman lukeminen” 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 ”EXPLAIN-suunnitelman lukeminen”?

Kyselysuunnitelman skannaustyyppien, liitosmenetelmien ja kustannusarvioiden tulkitseminen. 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 1/4.

Kuinka kauan ”EXPLAIN-suunnitelman lukeminen”-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

  1. EXPLAIN-suunnitelman lukeminen
  2. Seq Scan, Index Scan ja Index-Only
  3. Liitosalgoritmit: Nested Loop, Hash, Merge
  4. Hitaiden kyselyiden tunnistaminen ja korjaaminen
← Takaisin: SQL-työhaastatteluun valmistautuminen