SQL-työhaastatteluun valmistautuminen · Oppitunti

Hitaiden kyselyiden tunnistaminen ja korjaaminen

Diagnostiikan tarkistuslista haastattelutehtävään ”tämä kysely on hidas, korjatkaa se”.

Oppitunti 4/413 vaihetta

Hitaiden kyselyiden tunnistaminen ja korjaaminen on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 4/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.

Kysymys: Tämä kysely on hidas, korjatkaa se

Tämä on haastattelun päätehtävä: haastattelija antaa teille hitaan kyselyn ja EXPLAIN ANALYZE -suunnitelman ja pyytää diagnosoimaan ongelman. Hän testaa menetelmää, ei ulkoa opeteltuja temppuja.

Hyvä vastaus etenee järjestelmällisesti ja ääneen lausuttavan tarkistuslistan mukaan: mitatkaa, lukekaa suunnitelma, etsikää suurin kustannus, muodostakaa hypoteesi, ehdottakaa korjausta ja varmistakaa tulos. Tämä oppitunti rakentaa tarkistuslistan vaihe vaiheelta.

Toimikaa järjestelmällisesti ja kertokaa päättelystänne, sillä juuri se osoittaa senioritason osaamisen.

Vaihe 1: Mittaa EXPLAIN ANALYZElla

Älkää koskaan arvatko pelkän SQL:n perusteella. Hakekaa todellinen suunnitelma komennolla EXPLAIN (ANALYZE, BUFFERS).

ANALYZE antaa todelliset ajat ja rivimäärät, ja BUFFERS näyttää, käytetäänkö välimuistia vai luetaanko tietoa levyltä. Yhdessä ne kertovat, rajoittaako kyselyä suorittimen laskentateho, levyn siirtonopeus vai yksinkertaisesti tehtävän työn suuri määrä.

Suorittakaa komento muutaman kerran, sillä ensimmäiseen suoritukseen voi liittyä tyhjän välimuistin aiheuttama viive, joka vääristää ajoitusta.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01';

Vaihe 2: Etsi hallitseva solmu

Älkää lukeko suunnitelmaa ylhäältä alaspäin ja etsikö ongelmaa sattumanvaraisesti. Etsikää solmu, jossa kuluu tosiasiassa eniten aikaa.

Laskekaa kunkin solmun oma aika: sen kokonais-actual time vähennettynä lapsisolmujen ajalla ja kerrottuna loops-arvolla. Suurimman osuuden muodostava solmu on kohteenne; kaikki muu on sivuseikkaa.

Haastattelussa voitte sanoa: 80 prosenttia suoritusajasta kuluu tässä Seq Scan -operaatiossa, joten keskityn siihen. Minkä tahansa muun asian optimointi olisi hukkaan heitettyä työtä.

Vaihe 3: Vertaa arvioituja ja todellisia arvoja

Verratkaa hallitsevassa solmussa arvioitujen rivien määrää todelliseen rivimäärään. Suuri ero tarkoittaa, että suunnittelija toimii lähes sokkona ja valitsi todennäköisesti huonon suunnitelman (väärän liitosalgoritmin tai väärän tiedonsaantimenetelmän).

Esimerkissä rivimäärä arvioidaan 1000 kertaa todellista pienemmäksi. Ennen kuin suunnittelette mitään uudelleen, päivittäkää tilastot, sillä tämä yksittäinen komento korjaa suunnitelman usein ilmaiseksi.

ANALYZE laskee sarakkeiden tilastot uudelleen; VACUUM ANALYZE myös poistaa kuolleet monikot ja päivittää näkyvyyskartan.

-- estimate rows=100, actual rows=120000  -> stale stats
ANALYZE orders;
-- or, for bloated tables:
VACUUM ANALYZE orders;

Yleinen syy: indeksoidun sarakkeen ympärillä oleva funktio

Yleisin korjattavissa oleva virhe on se, että WHERE-ehdossa sarakkeen ympärillä käytetään funktiota tai tyyppimuunnosta. Tällöin indeksiä ei voi käyttää ja tietokantamoottori suorittaa Seq Scan -läpikäynnin.

Esimerkki pakottaa täyden läpikäynnin, koska DATE() suoritetaan jokaiselle riville. Muotoilkaa ehto uudelleen paljaan sarakkeen alue-ehdoksi (sargable-muotoon), jolloin created_at-sarakeen indeksi otetaan käyttöön.

Sama koskee ehtoa WHERE lower(email)=...: tallentakaa normalisoitu data, tehkää haku paljaasta sarakkeesta tai luokaa lausekeindeksi.

-- Not sargable: index unusable
WHERE DATE(created_at) = '2026-01-01'

-- Sargable: range over the bare column
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-01-02'

Yleinen syy: puuttuva indeksi

Jos hallitseva solmu on Seq Scan erittäin valikoivalla suodattimella tai Nested Loop, jossa loops on valtava indeksoimattoman sisemmän avaimen vuoksi, ratkaisu on yleensä indeksi.

Lisätkää indeksi suodatettuun tai yhdistettävään sarakkeeseen. Esimerkissä sellainen luodaan customer_id-sarakkeeseen, jotta liitos voi vaihtaa Seq Scan -läpikäynneistä indeksihakuihin ja suunnittelija voi valita huomattavasti halvemman suunnitelman.

Varmistakaa vaikutus suorittamalla EXPLAIN ANALYZE uudelleen, älkää olettako indeksin auttaneen.

CREATE INDEX idx_orders_customer
  ON orders (customer_id);

Yleinen syy: SELECT * ja leveät rivit

SELECT * hakee levyltä ja siirtää verkon yli jokaisen sarakkeen. Lisäksi se estää pelkät indeksiluvut sisältävät haut, koska indeksi kattaa harvoin kaikki sarakkeet.

Valitkaa vain tarvitsemanne sarakkeet. Tämä pienentää rivien leveyttä, vähentää levyltä lukemista ja voi mahdollistaa kaikki tarvittavat tiedot sisältävän indeksin varassa tehtävän haun.

Jos haastattelija on lisännyt kyselyyn SELECT * -lauseen, hän haluaa teidän huomaavan sen. Sarakeluettelon karsiminen on usein nopea ja käytännössä tehokas parannus leveillä tauluilla.

-- Before
SELECT * FROM orders WHERE customer_id = 42;

-- After: only needed columns (may enable index-only scan)
SELECT order_id, amount FROM orders WHERE customer_id = 42;

Yleinen syy: tietojen kirjoittaminen levylle

Jos Sort- tai Hash-solmu ilmoittaa levyn käytöstä (Sort Method: external merge Disk: 25000kB tai Batches: > 1), operaatio ylitti work_mem-rajan ja kirjoitti tietoja levylle.

Vaihtoehtoja ovat work_mem-arvon kasvattaminen istuntoa varten, lajitteluun tai hajautukseen päätyvien rivien määrän vähentäminen (suodattakaa aiemmin) tai lajitellun järjestyksen tuottavan indeksin lisääminen, jolloin lajittelua ei tarvita lainkaan.

Tämä on täsmällinen senioritason diagnoosi, jota haastattelijat arvostavat.

Sort  (actual rows=2000000 loops=1)
  Sort Key: o.amount
  Sort Method: external merge  Disk: 25000kB

Yleinen syy: liian monien rivien hakeminen

Kiinnittäkää huomiota merkintään Rows Removed by Filter: 9500000. Kysely luki kymmenen miljoonaa riviä ja hylkäsi niistä lähes kaikki, mikä on tyypillistä hukkatyötä.

Korjausvaihtoehtoja ovat indeksin lisääminen, jotta suodatus tehdään jo tiedonsaannin aikana (eikä vasta sen jälkeen), ehdon muuttaminen valikoivammaksi tai suodatuksen siirtäminen kyselyssä aiemmaksi, jotta puussa eteenpäin kulkee vähemmän rivejä.

Perusperiaate on tehdä mahdollisimman vähän työtä ja suodattaa mahdollisimman aikaisin ja edullisesti.

Seq Scan on events
  Filter: (event_type = 'purchase')
  Rows Removed by Filter: 9500000

Vianmäärityksen tarkistuslista

Lausukaa tämä haastattelussa, niin pysytte oikealla tiellä:

Mitatkaa komennolla EXPLAIN (ANALYZE, BUFFERS).
Paikantakaa eniten aikaa kuluttava solmu.
Verratkaa arvioituja ja todellisia rivimääriä ja korjatkaa ensin vanhentuneet tilastot.
Tarkistakaa sargable-muoto ja poistakaa funktiot suodatettavista sarakkeista.
Indeksoikaa valikoivat suodattimet ja liitosavaimet.
Karsikaa sarakkeita ja välttäkää SELECT * -kyselyitä.
Tarkkailkaa levylle kirjoittamista ja liian monien rivien hakemista.
Varmistakaa tulos suorittamalla suunnitelma uudelleen.

Kootaan kokonaisuus

Käykää koko esimerkki ääneen läpi. Suunnitelmassa näkyy Seq Scan 50 miljoonan rivin orders-taululle, suodatus customer_id = 42 ja Rows Removed by Filter -arvo lähellä 50 miljoonaa. Arvio vastaa suunnilleen toteutunutta arvoa.

Diagnoosi: suodatus on selektiivinen, indeksiä ei ole ja suurin kustannus aiheutuu skannauksesta. Korjaus: CREATE INDEX ON orders(customer_id). Kun kysely suoritetaan uudelleen, suunnitelma vaihtuu Index Scan -suunnitelmaksi ja aika putoaa sekunneista alle millisekuntiin.

Tämä mittaa–diagnosoi–korjaa–varmista-silmukka on vastauspohja kaikkiin hitaita kyselyitä koskeviin kysymyksiin.

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, amount FROM orders WHERE customer_id = 42;

Pikatarkistus

Kysely suodattaa ehdolla WHERE YEAR(order_date) = 2026, ja suunnitelmassa näkyy täysi Seq Scan, vaikka sarakkeelle order_date on jo olemassa B-tree-indeksi. Mikä on paras ensimmäinen korjaus?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Teillä on nyt toistettava menetelmä hitaita kyselyitä koskeviin kysymyksiin:

Mitatkaa aina suorituskykyä komennolla EXPLAIN (ANALYZE, BUFFERS) ja keskittykää hallitsevaan solmuun.
Korjatkaa ensin vanhentuneet tilastot, kun arviot ja toteutuneet arvot poikkeavat toisistaan.
Muotoilkaa predikaatit sargable-muotoon, lisätkää indeksit selektiivisiä suodattimia ja liitosavaimia varten ja karsikaa SELECT *.
Korjatkaa levylle vuotaminen ja liiallinen tietojen haku, ja varmistakaa sitten uusi suunnitelma.

Kertokaa tarkistuslista, ehdottakaa konkreettista muutosta ja suorittakaa suunnitelma uudelleen sen todistamiseksi – tämä on senior-tason vastaus.

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 maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Hitaiden kyselyiden tunnistaminen ja korjaaminen” ilmainen?

Kyllä – oppitunnin ”Hitaiden kyselyiden tunnistaminen ja korjaaminen” 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 ”Hitaiden kyselyiden tunnistaminen ja korjaaminen”?

Diagnostiikan tarkistuslista haastattelutehtävään ”tämä kysely on hidas, korjatkaa se”. 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 4/4.

Kuinka kauan ”Hitaiden kyselyiden tunnistaminen ja korjaaminen”-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

EXPLAIN-suunnitelman lukeminen
Seq Scan, Index Scan ja Index-Only
Liitosalgoritmit: Nested Loop, Hash, Merge
Hitaiden kyselyiden tunnistaminen ja korjaaminen

← Takaisin: SQL-työhaastatteluun valmistautuminen