ProHoster > Blog > Administrimi > SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

Herë pas here shfaqet detyra e gjetjes së të dhënave të lidhura sipas një grupi çelësash, deri sa të arrijmë numrin total të nevojshëm të regjistrimeve.

Shembulli mĂ« "jetĂ«sor" — tĂ« shfaqim 20 problemet mĂ« tĂ« vjetra, tĂ« regjistruara nĂ« listĂ«n e punonjĂ«sve (p.sh., brenda njĂ« nĂ«shtrimi). PĂ«r dashboard-et e ndryshme menaxheriale me pĂ«rmbledhje tĂ« shkurtĂ«r mbi seksionet e punĂ«s, njĂ« temĂ« e tillĂ« kĂ«rkohet mjaft shpesh.

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

Në këtë artikull do të shqyrtojmë implementimin në PostgreSQL të një "versioni naive" të zgjidhjes së tillë, një algoritëm "më të zgjuar" dhe një që është krejtësisht i komplikuar "ciklit" në SQL me kushte daljeje nga të dhënat e gjetura, i cili mund të jetë i dobishëm si për zhvillim të përgjithshëm, ashtu edhe për aplikim në raste të ngjashme.

Të marrim një grup testues të dhënash nga artikulli i mëparshëm. Që regjistrimet e shfaqura të mos "kallzojnë" nga një herë në tjetrën kur vlerat e renditjes bien ndesh, do ta zgjerojmë indeksin tematik duke shtuar çelësin primar. Kështu, menjëherë do t'i japë atij unik për mbajtjen e rendit, dhe na garanton pjesën e qartë të renditjes:

Krijo indeks mbi task(owner_id, task_date, id);
-- dhe të vjetër - do ta heqim
Rrëzo indeksin task_owner_id_task_date_idx;

Ashtu si dëgjohet, ashtu shkruhet

Fillimisht, do të hartojmë variantin më të thjeshtë të kërkesës, duke kaluar ID e ekzekutorëve si një array si parametër hyrës:

Zgjidh
  *
Nga
  task
Ku
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
Rendit sipas
  task_date, id
Kufizo 20;

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"
[shiko në explain.tensor.ru]

ËshtĂ« pak e trishtueshme — ne kĂ«rkuam vetĂ«m 20 regjistra, por Index Scan na ktheu 960 rreshta, tĂ« cilĂ«t pastaj duhet t'i ndajmĂ«... Le ta provojmĂ« tĂ« lexojmĂ« mĂ« pak.

unnest + ARRAY

Propozimi i parĂ« qĂ« do na ndihmojĂ« — nĂ«se na nevojiten vetĂ«m 20 regjistra tĂ« renditur , mjafton tĂ« lexojmĂ« maksimalisht 20 tĂ« renditur nĂ« tĂ« njĂ«jtin rend sipas çdo çelĂ«si. FatmirĂ«sisht, indeksi pĂ«rkatĂ«s (owner_id, task_date, id) e kemi.

Do të përdorim të njëjtin mekanizëm të nxjerrjes dhe "zgjidhjes në kolona" e regjistrit të plotë të tabelës, ashtu si në artikullin e kaluar.Dhe gjithashtu do të aplikojmë përmbledhjen në array përmes funksionit ARRAY():

ME T T SI (
  Zgjidh
    unnest(ARRAY(
      Zgjidh
        t
      Nga
        task t
      Ku
        owner_id = unnest
      Rendit sipas
        task_date, id
      Kufizo 20 -- e kufizojmë këtu...
    )) r
  Nga
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
Zgjidh
  (r).* 
Nga
  T
Rendit sipas
  (r).task_date, (r).id
Kufizo 20; -- ... dhe këtu - po ashtu

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"
[shiko në explain.tensor.ru]

O, tani më mirë! 40% më shpejt, dhe 4.5 herë më pak të dhëna që duhe të lexohej.

Materializimi i regjistrimeve të tabelave përmes CTEDua të theksoj se në disa raste përpjekja për të punuar menjëherë me fushat e regjistrimit pas kërkimit të saj në nënkërkesë, pa "mbështjellje" në CTE, mund të çojë në "shumzimin" e InitPlan proporcionalisht me numrin e këtyre fushave:

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Rezultati  (kostot=4.77..4.78 rreshta=1 gjerësi=16) (koha e vërtetë=0.063..0.063 rreshta=1 cikle=1)
  Buffer: hit të përbashkëta=16
  Plani i iniciatorit 1 (kthen $0)
    ->  Kufizimi  (kostot=0.42..1.19 rreshta=1 gjerësi=48) (koha e vërtetë=0.031..0.032 rreshta=1 cikle=1)
          Buffer: hit të përbashkëta=4
          ->  Skandimi i indeksit duke përdorur task_owner_id_task_date_id_idx mbi detyrën t  (kostot=0.42..387.57 rreshta=500 gjerësi=48) (koha e vërtetë=0.030..0.030 rreshta=1 cikle=1)
                Kushti i indeksit: (owner_id = 1)
                Buffer: hit të përbashkëta=4
  Plani i iniciatorit 2 (kthen $1)
    ->  Kufizimi  (kostot=0.42..1.19 rreshta=1 gjerësi=48) (koha e vërtetë=0.008..0.009 rreshta=1 cikle=1)
          Buffer: hit të përbashkëta=4
          ->  Skandimi i indeksit duke përdorur task_owner_id_task_date_id_idx mbi detyrën t_1  (kostot=0.42..387.57 rreshta=500 gjerësi=48) (koha e vërtetë=0.008..0.008 rreshta=1 cikle=1)
                Kushti i indeksit: (owner_id = 1)
                Buffer: hit të përbashkëta=4
  Plani i iniciatorit 3 (kthen $2)
    ->  Kufizimi  (kostot=0.42..1.19 rreshta=1 gjerësi=48) (koha e vërtetë=0.008..0.008 rreshta=1 cikle=1)
          Buffer: hit të përbashkëta=4
          ->  Skandimi i indeksit duke përdorur task_owner_id_task_date_id_idx mbi detyrën t_2  (kostot=0.42..387.57 rreshta=500 gjerësi=48) (koha e vërtetë=0.008..0.008 rreshta=1 cikle=1)
                Kushti i indeksit: (owner_id = 1)
                Buffer: hit të përbashkëta=4"
  Plani i iniciatorit 4 (kthen $3)
    ->  Kufizimi  (kostot=0.42..1.19 rreshta=1 gjerësi=48) (koha e vërtetë=0.009..0.009 rreshta=1 cikle=1)
          Buffer: hit të përbashkëta=4
          ->  Skandimi i indeksit duke përdorur task_owner_id_task_date_id_idx mbi detyrën t_3  (kostot=0.42..387.57 rreshta=500 gjerësi=48) (koha e vërtetë=0.009..0.009 rreshta=1 cikle=1)
                Kushti i indeksit: (owner_id = 1)
                Buffer: hit të përbashkëta=4

NjĂ« e njĂ«jta shĂ«nim «u gjet» 4 herë  Deri nĂ« PostgreSQL 11, ky sjellje Ă«shtĂ« e zakonshme, dhe zgjidhja Ă«shtĂ« «mbĂ«shtjellja» nĂ« CTE, qĂ« Ă«shtĂ« njĂ« kufi i patjetĂ«rsueshĂ«m pĂ«r optimizuesin nĂ« kĂ«to versione.

Akkumulatori rekursiv

NĂ« variantin e mĂ«parshĂ«m, ne lexuam gjithsej 200 rreshta pĂ«r nevojĂ«n pĂ«r 20. Tani nuk janĂ« 960, por akoma mĂ« pak — a Ă«shtĂ« e mundur?

Le të provojmë të përdorim njohuritë tona se na duhen vetëm 20 shënime. Kështu që do të iterojmë leximin e të dhënave vetëm deri sa të arrijmë numrin e dëshiruar.

Hapi 1: lista fillestare

Kjo Ă«shtĂ« evidente se lista jonĂ« «e synuar» prej 20 shĂ«nimesh duhet tĂ« fillojĂ« me «shĂ«nimet e para» nga njĂ«ra prej çelĂ«save tanĂ« owner_id. Prandaj sĂ« pari do tĂ« gjejmĂ« ato «mĂ« tĂ« parat» pĂ«r secilin nga çelĂ«sat dhe do t'i vendosim nĂ« listĂ«, duke e renditur atĂ« nĂ« rendin qĂ« dĂ«shirojmĂ« — (task_date, id).

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

Hapi 2: gjejmë shënimet «në vijim»

Tani, nĂ«se e marrim shĂ«nimin e parĂ« nga lista jonĂ« dhe fillojmĂ« tĂ« «nĂ«ci» mĂ« tej pĂ«rmes indeksit me ruajtjen e çelĂ«sit owner_id, atĂ«herĂ« tĂ« gjitha shĂ«nimet e gjetura — janĂ« pikĂ«risht ato qĂ« vijojnĂ« nĂ« seçim rezultues. Sigurisht, vetĂ«m derisa tĂ« mos kalojmĂ« çelĂ«sin aplikativ tĂ« shĂ«nimit tĂ« dytĂ« nĂ« listĂ«.

Nëse ka ndodhur që kemi "prishur" regjistrimin e dytë, atëherë regjistrimi i fundit të lexuar duhet të shtohet në listë në vend të atij të parë (me të njëjtin owner_id), pas së cilës lista do të riorganizohet sërish.

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

Pra, gjithmonë kemi në listë jo më shumë se një regjistrim për secilin nga çelsat (nëse regjistrimet mbarojnë dhe ne nuk "prishim", atëherë regjistrimi i parë thjesht do të zhduket dhe nuk do të shtohet asgjë), për më tepër ato janë gjithmonë të renditura në rendin në rritje të çelësit aplikativ (task_date, id).

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"

Hapi 3: filtrojmë dhe "zhvillojmë" regjistrimet

NĂ« disa rreshta tĂ« zgjedhjes tonĂ« rekursive disa regjistrime rv duket se janĂ« tĂ« dyfishta — fillimisht gjejmĂ« ato qĂ« "prekĂ«n kufirin e regjistrimit tĂ« dytĂ« tĂ« listĂ«s", dhe mĂ« pas i vendosim si tĂ« parĂ«n nĂ« listĂ«. Pra, shfaqja e parĂ« duhet tĂ« filtrohet.

Kërkesa e tmerrshme përfundimtare

ME RECURSIV T SI (
  -- #1 : shtojmë në listën "të parat" regjistrime për çdo nga çelësat e grumbullimit
  ME wrap SI ( -- "materializojmë" regjistrimet, në mënyrë që qasja në fushat të mos shkaktojë shumim InitPlan/SubPlan
    ME T SI (
      ZGJEDH
        (
          ZGJEDH
            r
          NGA
            detyra r
          KU
            owner_id = unnest
          RENDIT DHE
            task_date, id
          LIMIT 1
        ) r
      NGA
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    ZGJEDH
      array_agg(r RENDIT DHE (r).task_date, (r).id) list -- rendisim lista në rendin e duhur
    NGA
      T
  )
  ZGJEDH
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  NGA
    wrap
UNION ALL
  -- #2 : lexojmë regjistrimet e çelësit të parë, derisa të kalojmë mbi regjistrimin e çelësit të dytë
  ZGJEDH
    RAST
      -- nëse nuk gjendet asgjë për çelësin e regjistrimit të parë
      KUR X._r ËSHTË JO NDARË NGA NULL ATËHERË
        T.list[2:] -- e heqim nga lista
      -- nëse NE NUK kaluam çelësin aplikativ të regjistrimit të dytë
      KUR X.not_cross ATËHERË
        T.list -- thjesht vazhdojmë listën e njëjtë pa modifikime
      -- nëse në listë nuk ka regjistrim të dytë
      KUR T.list[2] ËSHTË NULL ATËHERË
        -- thjesht kthejmë listë të zbrazët
        '{}'
      -- riparojmë fjalorin, duke hequr regjistrimin e parë dhe duke shtuar të fundit të gjendur
      NDËRQoftĂ« (
        ZGJEDH
          coalesce(T.list[2] || array_agg(r RENDIT DHE (r).task_date, (r).id), '{}')
        NGA
          unnest(T.list[3:] || X._r) r
      )
    FUND
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  NGA
    T
  , LATERAL(
      ME wrap SI ( -- "materializojmë" regjistrimin
        ZGJEDH
          RAST
            -- nëse gjithsesi "kaluam" mbi regjistrimin e dytë
            KUR NUK T.not_cross
              -- atëherë regjistrimi i nevojshëm është i pari nga lista
              ATËHERË T.list[1]
            NDËRQoftĂ« ( -- nĂ«se nuk kaluan, atĂ«herĂ« çelĂ«si mbetet si nĂ« regjistrimin e mĂ«parshĂ«m - e bazojmĂ« nga ajo
              ZGJEDH
                _r
              NGA
                detyra _r
              KU
                owner_id = (rv).owner_id DHE
                (task_date, id) > ((rv).task_date, (rv).id)
              RENDIT DHE
                task_date, id
              LIMIT 1
            )
          FUND _r
      )
      ZGJEDH
        _r
      , RAST
          -- nëse regjistrimi i dytë nuk është më në listë, por ndonjë gjë kemi gjetur
          KUR list[2] ËSHTË NULL DHE _r ËSHTË NDARË NGA NULL ATËHERË
            TË VERTETË
          NDËRQoftĂ« -- nuk gjeta asgjĂ« ose "kaluam"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        FUND not_cross
      NGA
        wrap
    ) X
  KU
    T.size < 20 DHE -- kufizojmë këtu sasinë
    T.list ËSHTË JO NDARË NGA '{}' -- ose pĂ«r sa kohĂ« qĂ« lista nuk ka mbaruar
)
-- #3 : "zhvillojmë" regjistrimet - rendi është i garantuar sipas ndërtimit
ZGJEDH
  (rv).* 
 NGA
  T
KU
  not_cross; -- marrim vetëm regjistrimet "jo të kaluara"

SQL HowTo: shkruajmë një cikël while direkt në pyetje, ose "Një tre-këndesh elementar"
[shiko në explain.tensor.ru]

Kështu, ne këmben 50% të leximeve të të dhënave për 20% kohë ekzekutimi. Kështu që, nëse keni arsye për të besuar se leximi mund të jetë i gjatë (p.sh., të dhënat shpesh nuk janë në cache dhe duhet të shkohet në disk për to), në këtë mënyrë mund të varfëroheni në lexim më pak.

NĂ« çdo rast, koha e ekzekutimit rezultoi mĂ« e mirĂ« se nĂ« versionin e parĂ« ‘naiv’. Por cili nga kĂ«to 3 versione tĂ« pĂ«rdorni — Ă«shtĂ« vendimi juaj.

Burimi: habr.com

Bleni hostim tĂ« besueshĂ«m pĂ«r faqe me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Bleni hostim tĂ« besueshĂ«m pĂ«r faqe me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster