ProHoster > Блог > Isakoso > PostgreSQL Antipatterns: "Ẹyọkan gbọdọ wa ni osi!"

PostgreSQL Antipatterns: "Ẹyọkan gbọdọ wa ni osi!"

Ni SQL, o ṣe apejuwe “kini” ti o fẹ lati ṣaṣeyọri, kii ṣe “bii” o yẹ ki o ṣiṣẹ. Nitorinaa, iṣoro ti idagbasoke awọn ibeere SQL ni aṣa ti “bi o ti gbọ ni bi a ti kọ” gba ipo ọlá rẹ, pẹlu awọn ẹya ara ẹrọ ti awọn ipo iṣiro ni SQL.

Loni, lilo awọn apẹẹrẹ ti o rọrun pupọ, jẹ ki a wo kini eyi le ja si ni ipo ti lilo GROUP/DISTINCT и LIMIT pẹlu wọn.

Bayi, ti o ba kowe ninu ìbéèrè "akọkọ so awọn ami wọnyi pọ, lẹhinna jabọ gbogbo awọn ẹda-ẹda, o yẹ ki o jẹ ọkan nikan daakọ fun bọtini kọọkan" - Eyi ni deede bi yoo ṣe ṣiṣẹ, paapaa ti asopọ ko ba nilo rara.

Ati nigba miiran o ni orire ati pe o “ṣiṣẹ nikan”, nigbakan o ni ipa ti ko dara lori iṣẹ, ati nigba miiran o fun awọn ipa ti o jẹ airotẹlẹ patapata lati oju wiwo ti olupilẹṣẹ.

PostgreSQL Antipatterns: "Ẹyọkan gbọdọ wa ni osi!"
O dara, boya kii ṣe iyalẹnu pupọ, ṣugbọn…

"Tọkọtaya didùn": Darapọ mọ + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

O ni yio jẹ ko o ohun ti won fe yan awọn igbasilẹ X fun eyiti awọn igbasilẹ wa ni Y ti o ni ibatan si ipo ti o ṣẹ. Kọ kan ìbéèrè nipasẹ JOIN - ni diẹ ninu awọn iye pk ni igba pupọ (gangan melo ni awọn titẹ sii ti o yẹ ti o han ni Y). Bawo ni lati yọ kuro? Dajudaju DISTINCT!

O jẹ paapaa “idunnu” nigbati fun igbasilẹ X kọọkan ni awọn igbasilẹ Y-igbasilẹ ti o ni ibatan si ọgọọgọrun, ati lẹhinna awọn ẹda-iwe ni a yọkuro ni akọni…

PostgreSQL Antipatterns: "Ẹyọkan gbọdọ wa ni osi!"

Bawo ni lati ṣe atunṣe? Lati bẹrẹ pẹlu, mọ pe iṣoro naa le yipada si “Yan awọn igbasilẹ X fun eyiti o wa ni O kere ju ỌKAN ti o ni nkan ṣe pẹlu ipo ti o ti ṣẹ” - lẹhinna, a ko nilo ohunkohun lati Y-igbasilẹ funrararẹ.

Itẹle EXISTS

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Diẹ ninu awọn ẹya ti PostgreSQL loye pe ni EXISTS o to lati wa titẹsi akọkọ ti o wa, awọn agbalagba ko ṣe. Nitorinaa Mo fẹ lati tọka nigbagbogbo LIMIT 1 inu EXISTS.

DARA ARA LATERAL

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Aṣayan kanna ngbanilaaye, ti o ba jẹ dandan, lati da pada diẹ ninu data lẹsẹkẹsẹ lati igbasilẹ Y-igbasilẹ ti o ni nkan ṣe. Aṣayan iru kan ni a jiroro ninu nkan naa "PostgreSQL Antipatterns: igbasilẹ toje yoo de arin JOIN".

"Kí nìdí san diẹ sii": DISTINCT [ON] + LIMIT 1

Anfaani afikun ti iru awọn iyipada ibeere ni agbara lati ni irọrun idinwo wiwa awọn igbasilẹ ti ọkan tabi diẹ ninu wọn ba nilo, bi ninu ọran atẹle:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Bayi a ka ibeere naa ati gbiyanju lati loye kini DBMS ti daba lati ṣe:

  • sisopọ awọn ami
  • oto nipa X.pk
  • lati awọn titẹ sii ti o ku, yan ọkan

Nitorina kini o gba? "O kan titẹsi" lati awọn alailẹgbẹ - ati pe ti a ba mu ọkan ninu awọn ti kii ṣe alailẹgbẹ, ṣe abajade yoo yipada ni ọna kan?… “Ati pe ti ko ba si iyatọ, kilode ti o san diẹ sii?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Ati pato koko kanna pẹlu GROUP BY + LIMIT 1.

"Mo kan ni lati beere": GROUP + LIMIT ti ko tọ

Awọn nkan ti o jọra waye ni oriṣiriṣi ti kii-ofo sọwedowo awọn ami tabi awọn CTE bi ibeere ti nlọsiwaju:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Awọn iṣẹ apapọ (count/min/max/sum/...) ti ṣiṣẹ ni aṣeyọri lori gbogbo eto, paapaa laisi awọn ilana ti o fojuhan GROUP BY. Nikan pẹlu LIMIT wọn kii ṣe ọrẹ pupọ.

Olùgbéejáde le ronu "Ti awọn igbasilẹ ba wa nibẹ, lẹhinna Emi ko nilo diẹ sii ju LIMIT". Ṣugbọn maṣe ṣe iyẹn! Nitoripe fun ipilẹ o jẹ:

  • ka ohun ti wọn fẹ ni ibamu si gbogbo awọn igbasilẹ
  • fun bi ọpọlọpọ awọn ila bi nwọn ti beere

Da lori awọn ipo ibi-afẹde, o yẹ lati ṣe ọkan ninu awọn aropo wọnyi:

  • (count + LIMIT 1) = 0 on NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 on EXISTS(LIMIT 1)
  • count >= N on (SELECT count(*) FROM (... LIMIT N))

"Elo ni lati gbele ni awọn giramu": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Olùgbéejáde aláìlẹ́gbẹ́ kan lè gbàgbọ́ tọkàntọkàn pé ìbéèrè náà yóò dáwọ́ iṣẹ́ dúró. ni kete ti a ba rii $1 ti awọn iye oriṣiriṣi akọkọ ti o wa kọja.

Nigbakan ni ọjọ iwaju eyi le ati pe yoo ṣiṣẹ ọpẹ si ipade tuntun kan Atọka Rekọja wíwo, imuse ti eyi ti o ti wa ni Lọwọlọwọ ṣiṣẹ jade, sugbon ko sibẹsibẹ.

Fun bayi akọkọ gbogbo awọn igbasilẹ yoo gba pada, jẹ alailẹgbẹ, ati pe lati ọdọ wọn nikan ni iye ti o beere yoo pada. O jẹ ibanujẹ paapaa ti a ba fẹ nkan bi $ 1 = 4, ati pe awọn ọgọọgọrun awọn igbasilẹ wa ninu tabili…

Ni ibere ki o má ba ni ibanujẹ lasan, jẹ ki a lo ibeere atunṣe "DISTINCT jẹ fun talaka" lati PostgreSQL Wiki:

PostgreSQL Antipatterns: "Ẹyọkan gbọdọ wa ni osi!"

orisun: www.habr.com

Fi ọrọìwòye kun