ProHoster > Blog > Rianachd > Antipatterns PostgreSQL: "Feumaidh dìreach aon a bhith ann!"

Antipatterns PostgreSQL: "Feumaidh dìreach aon a bhith ann!"

Ann an SQL, tha thu a 'toirt cunntas air "dè" a tha thu airson faighinn, chan e "ciamar" a bu chòir a dhèanamh. Mar sin, tha an duilgheadas ann a bhith a’ leasachadh cheistean SQL ann an stoidhle “mar a chluinnear mar a tha e sgrìobhte” a’ gabhail àite urraim, còmhla ri feartan sònraichte de mheasadh suidheachadh ann an SQL.

An-diugh, a’ cleachdadh eisimpleirean air leth sìmplidh, chì sinn dè dh’ fhaodadh seo leantainn ann an co-theacsa cleachdadh GROUP/DISTINCT и LIMIT leotha.

Sin ma sgrìobh thu san iarrtas “Ceangail na clàran sin an toiseach, agus an uairsin tilg a-mach na dùblaidhean gu lèir, cha bu chòir ach aon a bhith ann eisimpleir airson gach iuchair" - tha seo dìreach mar a dh'obraicheas e, eadhon ged nach robh feum air a 'cheangal idir.

Agus uaireannan tha thu fortanach agus tha e “dìreach ag obair”, uaireannan tha buaidh mì-thlachdmhor aige air coileanadh, agus uaireannan bheir e seachad buaidhean a tha gu tur ris nach robh dùil bho shealladh an leasaiche.

Antipatterns PostgreSQL: "Feumaidh dìreach aon a bhith ann!"
Uill, is dòcha nach eil e cho iongantach, ach…

"Càraid milis": Thig còmhla + DISTINCT

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

Ciamar a bhiodh e soilleir dè bha iad ag iarraidh tagh na clàran sin X, airson a bheil ann an Y co-cheangailte ris a’ chumha coileanta. Cuir a-steach iarrtas tro JOIN - fhuair cuid de luachan pk grunn thursan (dìreach cia mheud clàr iomchaidh a thàinig gu bhith ann an Y). Ciamar a thoirt air falbh? Gu cinnteach DISTINCT!

Tha e gu sònraichte “tlachdmhor” nuair a tha ceudan de chlàran Y co-cheangailte ri gach clàr X, agus an uairsin thèid dùblaidhean a thoirt air falbh gu gaisgeil ...

Antipatterns PostgreSQL: "Feumaidh dìreach aon a bhith ann!"

Ciamar a chàradh? An toiseach, tuigibh gum faodar an obair atharrachadh gu "tagh na clàran sin X airson a bheil CO-dhiù AON ann an Y co-cheangailte ris a’ chumha ga choileanadh" - às deidh a h-uile càil, chan fheum sinn dad bhon chlàr Y fhèin.

ANN AN neadachadh

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

Tha cuid de dhreachan de PostgreSQL a 'tuigsinn gu bheil e gu leòr ann an EXISTS a' chiad chlàr a tha a 'tighinn tarsainn a lorg, nach eil feadhainn nas sine. Mar sin, is fheàrr leam an-còmhnaidh innse LIMIT 1 taobh a-staigh EXISTS.

COINNEACH LAOIDH

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;

Leigidh an aon roghainn, ma tha sin riatanach, beagan dàta a thilleadh bhon chlàr Y co-cheangailte a chaidh a lorg aig an aon àm. Tha roghainn coltach ris air a dheasbad san artaigil "PostgreSQL Antipatterns: ruigidh clàr tearc gu meadhan JOIN".

"Carson a phàigheas tu barrachd": SÒNRAICHTE [ON] + LIMIT 1

Is e buannachd a bharrachd de chruth-atharrachaidhean ceiste mar sin an comas àireamhachd chlàran a chuingealachadh gu furasta mura h-eil feum air ach aon / beagan dhiubh, mar a tha sa chùis a leanas:

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

A-nis leugh sinn an t-iarrtas agus feuchaidh sinn ri tuigsinn dè a bu chòir don DBMS a dhèanamh:

  • bidh sinn a 'ceangal nam plèanaichean
  • gun samhail le X.pk
  • tagh aon de na clàran a tha air fhàgail

Mar sin dè fhuair thu? "Aon chlàr" bhon fheadhainn gun samhail - agus ma ghabhas tu am fear seo den fheadhainn neo-shònraichte, an atharraich an toradh dòigh air choireigin? .. “Agus mura h-eil diofar ann, carson a phàigheas tu barrachd?”

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

Agus dìreach an aon chuspair le GROUP BY + LIMIT 1.

"Chan fheum mi ach faighneachd": buidheann neo-chinnteach + LIMIT

Bidh rudan coltach ris a’ tachairt ann an diofar sgrùdaidhean neo-fhollaiseachd bileagan no CTE mar a thèid an t-iarrtas air adhart:

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

Gnìomhan iomlan (count/min/max/sum/...) air an cur gu bàs gu soirbheachail air an t-seata gu lèir, eadhon gun a bhith air a shònrachadh gu soilleir GROUP BY. A-mhàin an seo le LIMIT chan eil iad glè chàirdeil.

Faodaidh an leasaiche smaoineachadh “A-nis, ma tha clàran ann, chan fheum mi barrachd air LIMIT”. Ach chan fheum thu! Air sgàth airson a 'bhunait tha e:

  • cunnt na tha iad ag iarraidh air a h-uile clàr
  • thoir seachad na h-uimhir de loidhnichean ’s a dh’ iarras iad

A rèir nan suidheachaidhean targaid, tha e iomchaidh aon de na h-ionadan a leanas a dhèanamh:

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

"Dè an ìre a bu chòir a chrochadh ann an gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Faodaidh leasaiche naive a bhith den bheachd gu dùrachdach gun stad coileanadh iarrtas, cho luath ‘s a lorgas sinn a’ chiad $1 luachan eadar-dhealaichte a thig tarsainn.

Uaireigin san àm ri teachd, is dòcha gum bi seo agus obraichidh e le taing do nód ùr Clàr-innse Skip Scan, a tha ga bhuileachadh an-dràsta ag obair a-mach, ach chan eil fhathast.

Gu ruige seo an toiseach gheibhear a h-uile clàr air ais, gun samhail, agus cha tèid ach na h-uimhir dhiubh a thèid iarraidh air ais. Tha e gu sònraichte brònach nan robh sinn ag iarraidh rudeigin mar sin $ 1 = 4, agus tha ceudan de mhìltean de chlàran sa chlàr ...

Gus nach bi sinn brònach gu dìomhain, cleachdaidh sinn ceist ath-chuairteach "Sònraichte dha na Bochdan" bho PostgreSQL Wiki:

Antipatterns PostgreSQL: "Feumaidh dìreach aon a bhith ann!"

Source: www.habr.com

Cuir beachd ann