ProHoster > Blag > Riarachán > Antipatterns PostgreSQL: Coinníollacha a Mheasú i SQL

Antipatterns PostgreSQL: Coinníollacha a Mheasú i SQL

Ní C++ é SQL, agus ní JavaScript. Mar sin, tarlaíonn ríomh na n-abairtí loighciúla go difriúil, agus ní hé seo an rud céanna ar chor ar bith:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Tá plean forghníomhaithe fiosrúcháin PostgreSQL á bharrfheabhsú is féidir leo coinníollacha coibhéiseacha a “atheagrú” go treallach, ná ríomh cuid acu le haghaidh taifead aonair, ceangail iad le coinníollacha an innéacs a cuireadh i bhfeidhm... I mbeagán focal, is é an bealach is éasca chun glacadh leis go bhfuil tú Ní féidir a rialú cén t-ord a dhéanfaidh siad (agus cé acu a ríomhfar ar chor ar bith iad) comhionann coinníollacha.

Dá bhrí sin, má tá tú fós ag iarraidh tosaíocht a bhainistiú, ní mór duit é a struchtúrú na coinníollacha seo a dhéanamh míchothrom ag baint úsáide as coinníollach nathanna cainte и oibreoirí.

Antipatterns PostgreSQL: Coinníollacha a Mheasú i SQL
Tá sonraí agus oibriú leo mar bhunús ár gcoimpléasc VLSI, dá bhrí sin tá sé an-tábhachtach dúinn go ndéanfaí oibríochtaí orthu ní hamháin i gceart, ach freisin go héifeachtach. Breathnaímid ar shamplaí sonracha inar féidir earráidí a dhéanamh agus na slonn á ríomh, agus inar fiú a n-éifeachtúlacht a fheabhsú.

#0: RTFM

Ag tosú sampla ó dhoiciméadú:

Nuair a bhíonn an t-ord meastóireachta tábhachtach, is féidir é a ghabháil leis an tógáil CASE. Mar shampla, is bealach é seo chun deighilt faoi nialas a sheachaint in abairt WHERE neamhiontaofa:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Rogha sábháilte:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

An dearadh a úsáidtear ar an mbealach seo CASE chosnaíonn an abairt ó bharrfheabhsú, mar sin níor chóir é a úsáid ach amháin nuair is gá.

#1: riocht truicear

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Dealraíonn sé gach rud chun breathnú go maith, ach ... Geallann aon duine go bhfuil an infheistíocht SELECT ní dhéanfar é a fhorghníomhú má tá an chéad choinníoll bréagach. A ligean ar a shocrú le neadaithe IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Breathnaímid anois go cúramach - tá corp iomlán na feidhme truicear "fillte" isteach IF. Ciallaíonn sé seo nach gcuireann aon rud cosc ​​​​ar ár gcumas an coinníoll seo a bhaint as an nós imeachta a úsáideann WHEN-coinníollacha:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Tá an cur chuige seo ráthaithe chun acmhainní freastalaí a shábháil nuair a bhíonn an riocht bréagach.

#2: NÓ/AND slabhra

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Seachas sin, is féidir leat deireadh suas leis an dá EXISTS beidh “fíor”, ach comhlíonfar an dá rud.

Ach má tá a fhios againn go cinnte go bhfuil ceann acu "fíor" i bhfad níos minice (nó "bréagach" - do AND-chains) - an féidir “a thosaíocht a mhéadú” ar bhealach éigin ionas nach gcuirfear an dara ceann i gcrích arís?

Tharlaíonn sé go raibh sé indéanta - tá an cur chuige algartamaíoch gar do ábhar an ailt Antipatterns PostgreSQL: sroichfidh taifead annamh lár JOIN.

Déanaimis an dá choinníoll seo a “shove” faoi CÁS:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Sa chás seo ní raibh muid a shainiú ELSE-value, is é sin, má tá an dá choinníoll bréagach CASE fillfidh NULL, a léirmhínítear mar FALSE в WHERE-coinníollacha.

Is féidir an sampla seo a chomhcheangal ar bhealaí eile - ag brath ar bhlas agus dath:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: conas [ní] coinníollacha a scríobh

Chaith muid dhá lá ag déanamh anailíse ar na fáthanna le hoibriú “aisteach” an truicear seo - feicimis cén fáth.

Foinse:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

Fadhb #1: níl meas ag éagothroime ar NULL

A ligean ar a shamhlú go bhfuil gach rud OLD-fields bhí brí NULL. Cad a tharlóidh?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Agus ó thaobh na coinníollacha a oibriú amach NULL comhionann FALSE, mar a luadh thuas.

cinneadh: úsáid oibreoir IS DISTINCT FROM ó ROW-oibreoir, taifid iomlána a chur i gcomparáid láithreach:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Fadhb #2: feidhmiúcháin éagsúla den fheidhmiúlacht chéanna

Déanaimis comparáid:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Cén fáth a bhfuil infheistíocht bhreise anseo? SELECT? Feidhm to_regclass? Cén fáth go bhfuil sé difriúil saor in aisce,. .

Déanaimis é a dheisiú:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Fadhb #3: tosaíocht d'oibríochtaí bola

Déanaimis an fhoinse a fhormáidiú:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Úps... Go deimhin, d'éirigh sé amach má tá aon cheann den chéad dá choinníoll fíor, go dtiocfaidh an coinníoll iomlán isteach TRUE, gan éagothroime a chur san áireamh. Agus ní hé seo ar chor ar bith a theastaigh uainn.

Déanaimis é a dheisiú:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Fadhb #4 (beag): casta NÓ riocht do réimse amháin

I ndáiríre, bhí fadhbanna againn in Uimh. 3 go beacht toisc go raibh trí choinníoll ann. Ach in ionad iad is féidir leat a fháil le ceann amháin, ag baint úsáide as an meicníocht coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Mar sin againn NULL “gabhróimid”, agus deacair OR Níl gá le fál le lúibíní.

Ar an iomlán

Déanaimis taifead ar a bhfuil faighte againn:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Agus má mheasann tú nach féidir an fheidhm truicear seo a úsáid ach amháin i UPDATE-trigger mar gheall ar infhaighteacht OLD/NEW sa riocht ardleibhéil, ansin is féidir an coinníoll seo a chur i gcoitinne WHEN-coinníoll, mar a thaispeántar i #1...

Foinse: will.com

Add a comment