ProHoster > blog > Gweinyddiaeth > Antipatterns PostgreSQL: "Rhaid bod dim ond un!"

Antipatterns PostgreSQL: "Rhaid bod dim ond un!"

Yn SQL, rydych chi'n disgrifio "beth" rydych chi am ei gael, nid "sut" y dylid ei wneud. Felly, mae'r broblem o ddatblygu ymholiadau SQL yn arddull "fel y'i clywir yw sut y caiff ei ysgrifennu" yn cymryd ei le anrhydedd, ynghyd â hynodion gwerthuso cyflwr yn SQL.

Heddiw, gan ddefnyddio enghreifftiau hynod o syml, gadewch i ni weld beth y gall hyn arwain ato yng nghyd-destun defnyddio GROUP/DISTINCT и LIMIT gyda nhw.

Dyna os gwnaethoch chi ysgrifennu yn y cais “Cysylltwch y tabledi hyn yn gyntaf, ac yna taflu'r holl gopïau dyblyg allan, dim ond un ddylai fod enghraifft ar gyfer pob allwedd" - dyma'n union sut y bydd yn gweithio, hyd yn oed os nad oedd angen y cysylltiad o gwbl.

Ac weithiau rydych chi'n ffodus ac mae'n “gweithio”, weithiau mae'n cael effaith annymunol ar berfformiad, ac weithiau mae'n rhoi effeithiau sy'n hollol annisgwyl o safbwynt y datblygwr.

Antipatterns PostgreSQL: "Rhaid bod dim ond un!"
Wel, efallai ddim mor ysblennydd, ond…

"Cwpl melys": YMUNWCH + WAHANOL

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

Sut y byddai'n glir beth oeddent ei eisiau dewiswch gofnodion o'r fath X, y mae yn Y ar eu cyfer yn gysylltiedig â'r amod a gyflawnwyd. Wedi cyflwyno cais trwy JOIN - wedi derbyn rhai gwerthoedd pk sawl gwaith (yn union faint o gofnodion addas a drodd allan i fod yn Y). Sut i gael gwared? Yn sicr DISTINCT!

Mae'n arbennig o "ddymunol" pan fo cannoedd o gofnodion Y cysylltiedig ar gyfer pob record X, ac yna mae copïau dyblyg yn cael eu tynnu'n arwrol ...

Antipatterns PostgreSQL: "Rhaid bod dim ond un!"

Sut i drwsio? I ddechrau, sylweddoli y gellir addasu'r dasg i "dewiswch y cofnodion hynny X lle mae O leiaf UN yn Y yn gysylltiedig â'r amod sy'n cael ei gyflawni" - wedi'r cyfan, nid oes angen unrhyw beth o'r Y-record ei hun.

Nythu YN BODOLI

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

Mae rhai fersiynau o PostgreSQL yn deall ei bod yn ddigon yn EXISTS i ddod o hyd i'r cofnod cyntaf a ddaw i'r amlwg, nad yw rhai hŷn yn gwneud hynny. Felly, mae'n well gennyf nodi bob amser LIMIT 1 y tu mewn EXISTS.

YMUNIAD LLAWR

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;

Mae'r un opsiwn yn caniatáu, os oes angen, i ddychwelyd rhywfaint o ddata ar unwaith o'r cofnod Y cysylltiedig a ddarganfuwyd ar yr un pryd. Mae opsiwn tebyg yn cael ei drafod yn yr erthygl "PostgreSQL Antipatterns: record brin yn cyrraedd canol YMuno".

"Pam talu mwy": NODWCH [AR] + TERFYN 1

Mantais ychwanegol trawsnewid ymholiad o'r fath yw'r gallu i gyfyngu'n hawdd ar gyfrif cofnodion os mai dim ond un / ychydig ohonynt sydd eu hangen, fel yn yr achos canlynol:

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

Nawr rydyn ni'n darllen y cais ac yn ceisio deall beth mae'r DBMS i fod i'w wneud:

  • rydym yn cysylltu'r platiau
  • unigryw gan X.pk
  • dewiswch un o'r cofnodion sy'n weddill

Felly beth gawsoch chi? "Rhyw un cofnod" o’r rhai unigryw – ac os cymerwch hwn un o’r rhai nad ydynt yn unigryw, a fydd y canlyniad yn newid rhywsut? .. “Ac os nad oes gwahaniaeth, pam talu mwy?”

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

Ac yn union yr un thema â GROUP BY + LIMIT 1.

"Mae'n rhaid i mi ofyn": GROUP + TERFYN ymhlyg

Mae pethau tebyg yn digwydd mewn gwahanol gwiriadau segurdod labeli neu CTEs wrth i'r cais fynd yn ei flaen:

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

ffwythiannau cyfanredol (count/min/max/sum/...) yn cael eu gweithredu'n llwyddiannus ar y set gyfan, hyd yn oed heb nodi'n benodol GROUP BY. Dim ond yma gyda LIMIT nid ydynt yn gyfeillgar iawn.

Gall y datblygwr feddwl “Nawr, os oes cofnodion yno, yna does dim angen mwy na LIMIT arna i”. Ond does dim rhaid i chi! Oherwydd ar gyfer y sylfaen mae'n:

  • cyfrif beth maen nhw eisiau ar bob cofnod
  • rhowch gymaint o linellau ag a ofynnant

Yn dibynnu ar yr amodau targed, mae'n briodol gwneud un o'r dirprwyon canlynol:

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

"Faint i'w hongian mewn gramau": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Gall datblygwr naïf gredu’n ddiffuant y bydd gweithredu cais yn dod i ben, cyn gynted ag y byddwn yn dod o hyd i'r $1 gwerthoedd gwahanol cyntaf sy'n dod ar eu traws.

Rywbryd yn y dyfodol, gall hyn weithio a bydd yn gweithio diolch i nod newydd Sgan Sgipio Mynegai, y mae ei weithrediad yn cael ei weithio allan ar hyn o bryd, ond nid eto.

Hyd yn hyn yn gyntaf bydd yr holl gofnodion yn cael eu hadalw, yn unigryw, a dim ond cymaint ohonynt ag y gofynnir amdanynt fydd yn cael eu dychwelyd. Mae'n arbennig o drist os oedden ni eisiau rhywbeth tebyg $ 1 = 4, ac mae cannoedd o filoedd o gofnodion yn y tabl ...

Er mwyn peidio â bod yn drist yn ofer, byddwn yn defnyddio ymholiad ailadroddus "NODWEDDOL i'r Tlodion" o Wiki PostgreSQL:

Antipatterns PostgreSQL: "Rhaid bod dim ond un!"

Ffynhonnell: hab.com

Ychwanegu sylw