PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Aggregaattien ja ikkunafunktioiden optimointi

Opettele tekniikoita monimutkaisten aggregointien ja ikkunafunktioiden tehokkaaseen käsittelyyn.

Oppitunti 1/411 vaihetta

Aggregaattien ja ikkunafunktioiden optimointi on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 1/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Aggregaattien ja ikkunafunktioiden johdanto

Tervetuloa optimoimaan edistyneitä kyselyitä! Tänään perehdytte siihen, miten aggregaatti- ja ikkunafunktiot saadaan toimimaan nopeammin.

Näiden tehokkaiden SQL-ominaisuuksien avulla voitte tehdä laskutoimituksia riviryhmille tai toisiinsa liittyville riveille. Ilman huolellista optimointia niistä voi kuitenkin tulla suorituskyvyn pullonkauloja.

Aggregaattifunktioiden kertaus

Aggregaattifunktiot tiivistävät riviryhmän tiedot ja palauttavat kustakin ryhmästä yhden arvon. Yleisiä aggregaattifunktioita ovat COUNT(), SUM(), AVG(), MIN() ja MAX().

Niitä käytetään usein yhdessä GROUP BY-lausekkeen kanssa ryhmien määrittämiseen. Katsotaan yksinkertaista esimerkkiä:

SELECT
  category,
  COUNT(product_id) AS total_products
FROM
  products
GROUP BY
  category;

Ikkunafunktioiden yleiskatsaus

Ikkunafunktiot tekevät myös laskutoimituksia taulun rivijoukolle. Toisin kuin aggregaattifunktiot, ne eivät kuitenkaan yhdistä rivejä yhdeksi riviksi. Sen sijaan ne palauttavat tuloksen jokaiselle alkuperäisen kyselyn riville.

Ne käyttävät OVER()-lauseketta laskennassa käytettävän rivien ”ikkunan” määrittämiseen. Ikkuna voidaan osittaa ja järjestää.

Aggregaattien optimointi: aikainen suodatus

Nopeiden aggregaattien kannalta on tärkeää käsitellä vähemmän dataa. Suodattakaa data aina mahdollisimman aikaisin WHERE-lausekkeen avulla. Tämä vähentää niiden rivien määrää, jotka PostgreSQL:n on skannattava ja ryhmiteltävä.

Tarkastellaan esimerkkiä, jossa laskemme aggregaatin vain tuoteryhmälle ”Electronics”:

SELECT
  category,
  AVG(price) AS avg_price
FROM
  products
WHERE
  category = 'Electronics'
GROUP BY
  category;

Aggregaattien optimointi: GROUP BY -indeksit

Indeksit voivat nopeuttaa GROUP BY-lausekkeita merkittävästi. Jos GROUP BY-lausekkeessa käytetyille sarakkeille on luotu indeksi, PostgreSQL voi usein käyttää sitä koko aineiston lajittelun välttämiseen.

Tämä pätee erityisesti B-tree-indekseihin, jotka tallentavat tiedot lajiteltuina.

CREATE INDEX idx_products_category
ON products (category);

Ikkunafunktiot: PARTITION BY

OVER()-lausekkeen sisällä oleva PARTITION BY jakaa aineiston itsenäisiin ryhmiin, ja ikkunafunktio käsittelee kutakin ositusta erikseen. Ajatelkaa sitä GROUP BY-lausekkeena, joka ei kuitenkaan yhdistä rivejä.

Suorituskyvyn kannalta osittaminen edellyttää usein tietojen lajittelua ositusavainten mukaan, mikä voi kuluttaa paljon resursseja suurilla aineistoilla.

SELECT
  product_name,
  category,
  price,
  AVG(price) OVER (PARTITION BY category) AS avg_category_price
FROM
  products;

Ikkunafunktiot: ikkunan ORDER BY

OVER()-lausekkeen sisällä oleva ORDER BY-lauseke määrittää rivien loogisen järjestyksen kussakin osituksessa. Tämä on olennaista järjestysfunktioille, kuten ROW_NUMBER(), sekä rivien järjestyksestä riippuville funktioille, kuten LAG() ja LEAD().

Kuten aggregaateissa, myös tämä järjestämisvaihe voi olla kallis etenkin silloin, kun sopivaa lajittelujärjestystä tukevaa indeksiä ei ole.

SELECT
  product_name,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM
  products;

Ikkunakehykset: ROWS ja RANGE

PARTITION BY- ja ORDER BY-lausekkeiden lisäksi voitte määrittää tietyn ikkunakehyksen käyttämällä ROWS- tai RANGE-lauseketta. Se määrittää, mitä nykyisen osituksen rivien osajoukkoa funktion tulee käsitellä.

ROWS: Perustuu nykyiseen riviin nähden kiinteään määrään rivejä.
RANGE: Perustuu nykyisen rivin arvoon nähden määritettyyn arvoalueeseen.

Pienemmän ja täsmällisemmän ikkunakehyksen, kuten ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, käyttäminen parantaa usein suorituskykyä suurempiin rajoittamattomiin kehyksiin verrattuna.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS three_day_moving_avg
FROM
  sales;

Yleisiä optimointivinkkejä

Seuraavassa on yleisiä vinkkejä sekä aggregaatti- että ikkunafunktioille:

Käyttäkää sopivia indeksejä: Erityisesti GROUP BY-, PARTITION BY- ja ORDER BY -lausekkeissa käytetyille sarakkeille.
Minimoikaa datan määrä: Suodattakaa aikaisin WHERE-lausekkeilla.
Välttäkää monimutkaisia lausekkeita: Aggregaattien tai ikkunafunktioiden sisällä tehtävät laskutoimitukset voivat olla hitaita. Laskekaa arvot etukäteen, jos mahdollista.
Ymmärtäkää datan jakauma: Vino jakauma voi johtaa työn epätasaiseen jakautumiseen ja hitaisiin osituksiin.

Pikatesti: aggregaattien optimointi

Käytössänne on suuri orders-taulu, ja haluatte selvittää kunkin asiakkaan niiden tilausten kokonaissumman, jotka on tehty kuukauden ”2023-01” aikana. Mikä lähestymistapa on yleensä suorituskykyisempi?

Vastaa interaktiivisessa oppitunnissa

Kertaus ja seuraavat vaiheet

Hienoa työtä! Olette oppineet, miten sekä aggregaatti- että ikkunafunktioiden optimointiin kannattaa suhtautua.

Suodattakaa aggregaattien data aikaisin.
Käyttäkää indeksejä GROUP BY-, PARTITION BY- ja ORDER BY -lausekkeissa käytetyille sarakkeille.
Huomioikaa lajittelun kustannukset sekä aggregaateissa että ikkunafunktioissa.
Määrittäkää täsmälliset ikkunakehykset ROWS- tai RANGE-lausekkeilla aina kun mahdollista.

Kun pidätte nämä tekniikat mielessä, voitte kirjoittaa nopeampia ja tehokkaampia PostgreSQL-kyselyitä!

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: 22
Oppitunnit: 88

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Aggregaattien ja ikkunafunktioiden optimointi” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Aggregaattien ja ikkunafunktioiden optimointi”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Aggregaattien ja ikkunafunktioiden optimointi”?

Opettele tekniikoita monimutkaisten aggregointien ja ikkunafunktioiden tehokkaaseen käsittelyyn. Harjoittelet PostgreSQL:n suorituskyky ja kyselyjen optimointi-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni PostgreSQL:n suorituskyky ja kyselyjen optimointi-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 1/4.

Kuinka kauan ”Aggregaattien ja ikkunafunktioiden optimointi”-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ä PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunnilla?

Kyllä. Jokainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-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

Aggregaattien ja ikkunafunktioiden optimointi
Rekursiiviset CTE:t ja graafikyselyt
Materialisoitujen näkymien käyttö suorituskyvyn parantamiseen
Kyselyiden optimointi FILTERillä ja ehdollisella aggregoinnilla

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi