ProHoster > Π‘Π»ΠΎΠ³ > Pagdumala > Mga Antipattern sa PostgreSQL: "Kinahanglan usa ra ang nahabilin!"

Mga Antipattern sa PostgreSQL: "Kinahanglan usa ra ang nahabilin!"

Sa SQL, imong gihulagway ang "unsa" gusto nimong makab-ot, dili "unsaon" kini kinahanglan ipatuman. Busa, ang problema sa pagpalambo sa mga pangutana sa SQL sa estilo sa "ingon nga kini nadungog mao ang paagi nga kini gisulat" nagpuli sa iyang dapit sa kadungganan, uban sa mga bahin sa pagkalkula sa mga kondisyon sa SQL.

Karon, gamit ang labi ka yano nga mga pananglitan, tan-awon naton kung unsa ang mahimo niini sa konteksto sa paggamit GROUP/DISTINCT ΠΈ LIMIT uban kanila.

Karon, kung nagsulat ka sa hangyo "Ikonektar una kini nga mga timailhan, ug dayon ilabay ang tanan nga mga duplicate, kinahanglan nga usa na lang ang nahabilin kopya para sa matag yawe" - ingon niini kung giunsa kini molihok, bisan kung ang koneksyon dili kinahanglan.

Ug usahay ikaw swerte ug kini "nagtrabaho lang", usahay kini adunay dili maayo nga epekto sa pasundayag, ug usahay kini naghatag mga epekto nga wala damha gikan sa punto sa panglantaw sa developer.

Mga Antipattern sa PostgreSQL: "Kinahanglan usa ra ang nahabilin!"
Aw, tingali dili kaayo talagsaon, apan ...

β€œSweet couple”: AMBAY + DISTINCT

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

Klaro kon unsay ilang gusto pilia ang mga rekord X diin adunay mga rekord sa Y nga may kalabutan sa natuman nga kondisyon. Nagsulat og hangyo pinaagi sa JOIN β€” nakabaton ug pipila ka pk values ​​​​sa makadaghang higayon (eksaktong pila ka angay nga mga entry ang nagpakita sa Y). Unsaon pagtangtang? Sigurado DISTINCT!

Labi na nga "makapalipay" kung alang sa matag X-record adunay daghang gatos nga may kalabutan nga Y-record, ug dayon ang mga duplicate mabayanihon nga gikuha ...

Mga Antipattern sa PostgreSQL: "Kinahanglan usa ra ang nahabilin!"

Unsaon pag-ayo? Sa pagsugod, hinumdomi nga ang problema mahimong mabag-o sa "pilia ang mga rekord X diin sa Y adunay labing menos USA nga nalangkit sa natuman nga kondisyon" - pagkahuman, wala kami magkinahanglan bisan unsa gikan sa Y-record mismo.

Nested EXISTS

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

Ang ubang mga bersyon sa PostgreSQL nakasabut nga sa EXISTS igo na nga makit-an ang una nga entry nga moabut, ang mga tigulang wala. Busa mas gusto nako nga kanunay ipahibalo LIMIT 1 sa sulod EXISTS.

LATERAL AMBAY

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;

Ang parehas nga kapilian nagtugot, kung gikinahanglan, nga ibalik dayon ang pipila ka mga datos gikan sa nakit-an nga kauban nga Y-record. Ang susamang kapilian gihisgotan sa artikulo "Mga Antipattern sa PostgreSQL: usa ka talagsaon nga rekord ang moabot sa tunga-tunga sa usa ka JOIN".

β€œNganong mobayad man og dugang”: DISTINCT [ON] + LIMIT 1

Ang usa ka dugang nga kaayohan sa ingon nga mga pagbag-o sa pangutana mao ang katakus nga dali nga limitahan ang pagpangita alang sa mga rekord kung kinahanglan ra ang usa o pipila niini, sama sa mosunod nga kaso:

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

Karon among gibasa ang hangyo ug gisulayan nga masabtan kung unsa ang gisugyot nga buhaton sa DBMS:

  • pagkonektar sa mga timailhan
  • talagsaon sa X.pk
  • gikan sa nahabilin nga mga entri, pagpili og usa

Busa unsa ang imong nakuha? "Usa ra ka entry" gikan sa mga talagsaon - ug kung atong kuhaon kining usa sa mga dili talagsaon, mausab ba ang resulta?

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ΄ΡΡƒΠ½ΡƒΡ‚ΡŒ подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Ug parehas nga hilisgutan sa GROUP BY + LIMIT 1.

β€œMangutana lang ko”: implicit GROUP + LIMIT

Ang susamang mga butang mahitabo sa lain-laing mga non-emptiness checks mga timailhan o CTE samtang nagpadayon ang hangyo:

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

Aggregate functions (count/min/max/sum/...) malampuson nga gipatuman sa tibuok set, bisan walay klaro nga mga instruksyon GROUP BY. Lamang uban sa LIMIT dili kaayo sila mahigalaon.

Makahunahuna ang developer "kung adunay mga rekord didto, nan kinahanglan nako dili labaw pa sa LIMIT". Apan ayaw kana buhata! Tungod kay alang sa base kini mao ang:

  • isipa ang ilang gusto sumala sa tanang rekord
  • hatag ug daghang linya sa ilang gipangayo

Depende sa gipunting nga mga kondisyon, angay nga himuon ang usa sa mga mosunud nga kapuli:

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

"Pila ang ibitay sa gramo": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ang usa ka walay pulos nga developer mahimong sinsero nga nagtuo nga ang hangyo mohunong sa pagpatuman. sa diha nga nakit-an namon ang $1 sa una nga lainlaing mga kantidad nga makita.

Usahay sa umaabot mahimo kini ug molihok salamat sa usa ka bag-ong node Index Laktawan Scan, ang pagpatuman niini sa pagkakaron ginabuhat, apan dili pa.

Sa pagkakaron una ang tanan nga mga rekord makuha, talagsaon, ug gikan lamang kanila ang kantidad nga gipangayo ibalik. Labi na nga makapasubo kung gusto namon ang usa ka butang nga sama $ 1 = 4, ug adunay gatusan ka libo nga mga rekord sa lamesa...

Aron dili maguol sa walay kapuslanan, gamiton nato ang recursive nga pangutana "DISTINCT para sa mga kabus" gikan sa PostgreSQL Wiki:

Mga Antipattern sa PostgreSQL: "Kinahanglan usa ra ang nahabilin!"

Source: www.habr.com

Idugang sa usa ka comment