ProHoster > blog > Amministrazzjoni > PostgreSQL Antipatterns: Evalwazzjoni tal-Kundizzjoni fl-SQL

PostgreSQL Antipatterns: Evalwazzjoni tal-Kundizzjoni fl-SQL

SQL mhuwiex C++, u lanqas JavaScript. Għalhekk, l-evalwazzjoni tal-espressjonijiet loġiċi hija differenti, u din mhix l-istess ħaġa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Filwaqt li jiġi ottimizzat il-pjan ta 'eżekuzzjoni ta' mistoqsija PostgreSQL jista arbitrarjament "jirranġa" l-kundizzjonijiet ekwivalenti, tikkalkula l-ebda wieħed minnhom għal rekords individwali, irreferi għall-kundizzjoni tal-indiċi applikat ... Fil-qosor, l-eħfef mod huwa li tassumi li inti ma tistax tmexxi l-ordni li fiha se jkunu (u jekk humiex se jiġu kkalkulati xejn) ugwali kundizzjonijiet.

Għalhekk, jekk xorta trid timmaniġġja l-prijorità, għandek bżonn strutturalment jagħmlu dawn il-kundizzjonijiet mhux ugwali bil-kondizzjonali espressjonijiet и operaturi.

PostgreSQL Antipatterns: Evalwazzjoni tal-Kundizzjoni fl-SQL
Id-dejta u l-ħidma magħhom hija l-bażi tal-kumpless VLSI tagħna, għalhekk huwa importanti ħafna għalina li l-operazzjonijiet fuqhom jitwettqu mhux biss b'mod korrett, iżda wkoll b'mod effiċjenti. Ejja nħarsu lejn eżempji konkreti fejn jistgħu jsiru żbalji fl-evalwazzjoni tal-espressjoni, u fejn ta’ min titjieb l-effiċjenza tagħhom.

#0: RTFM

Bidu eżempju mid-dokumentazzjoni:

Meta l-ordni tal-evalwazzjoni hija importanti, tista 'tiġi ffissata bil-kostruzzjoni CASE. Pereżempju, b'dan il-mod tevita d-diviżjoni b'żero f'sentenza WHERE mhux affidabbli:

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

Għażla sikura:

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

Il-kostruzzjoni użata CASE tipproteġi l-espressjoni mill-ottimizzazzjoni, għalhekk għandha tintuża biss meta jkun meħtieġ.

#1: kundizzjoni tal-grillu

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

Kollox jidher li jidher tajjeb, imma... Ħadd ma jwiegħed li l-investit SELECT mhux se jiġi esegwit jekk l-ewwel kundizzjoni tkun falza. Waħħalha ma ibejt IF:

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

Issa ejja nħarsu bir-reqqa - il-ġisem kollu tal-funzjoni tal-grillu rriżulta li kien "imgeżwer" fih IF. U dan ifisser li xejn ma jżommna milli tneħħi din il-kundizzjoni mill-proċedura bl-użu WHEN-kondizzjonijiet:

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

Dan l-approċċ jippermettilek li tiffranka r-riżorsi tas-server b'garanzija jekk il-kundizzjoni tkun falza.

#2: JEW/U katina

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

Inkella, jista 'jinkiseb li t-tnejn EXISTS se jkun veru, imma it-tnejn se jiġu esegwiti.

Imma jekk nafu żgur li wieħed minnhom huwa "veru" ħafna aktar spiss (jew "falz" - għal AND-ktajjen) - huwa possibbli li b'xi mod "iżżid il-prijorità tagħha" sabiex it-tieni waħda ma terġax tiġi esegwita?

Jirriżulta li huwa possibbli - l-approċċ algoritmikament huwa qrib is-suġġett tal-artikolu PostgreSQL Antipatterns: Dħul rari jilħaq in-nofs ta 'JOIN.

Ejja biss "shove under CASE" dawn iż-żewġ kundizzjonijiet:

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

F'dan il-każ, aħna ma ddefinijniex ELSE-value, jiġifieri, jekk iż-żewġ kundizzjonijiet huma foloz CASE se jirritorna NULL, li hija interpretata bħala FALSE в WHERE- kondizzjonijiet.

Dan l-eżempju jista 'jingħaqad b'mod ieħor - għat-togħma u l-kulur:

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

#3: kif [ma] tikteb kundizzjonijiet

Għaddejna jumejn biex janalizzaw ir-raġunijiet għall-iskattar "stramba" ta 'dan il-grillu - ejja naraw għaliex.

Sors:

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 ...

Problema #1: L-inugwaljanza ma tagħtix kont għal NULL

Ejja nassumu li kollox OLD-oqsma importanti NULL. X'se jiġri?

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

U mil-lat ta 'ħidma fil-kundizzjonijiet NULL ekwivalenti FALSE, kif imsemmi hawn fuq.

deċiżjoni: uża operatur IS DISTINCT FROM minn ROW-operatur, li jqabbel rekords sħaħ f'daqqa:

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

Problema numru 2: implimentazzjoni differenti tal-istess funzjonalità

Qabbel:

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

Għaliex hemm investimenti żejda SELECT? Funzjoni to_regclass? Għaliex hija differenti...

Ejja nirranġaw:

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

Problema #3: preċedenza bool

Ejja nifformattja s-sors:

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

Oops ... Fil-fatt, irriżulta li fil-każ tal-verità ta 'kwalunkwe waħda mill-ewwel żewġ kundizzjonijiet, il-kundizzjoni kollha tinbidel fi TRUE, injorati l-inugwaljanzi. U dan mhu xejn dak li ridna.

Ejja nirranġaw:

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

Problema #4 (żgħira): kundizzjoni kumplessa JEW għal qasam wieħed

Fil-fatt, kellna problemi fin-Nru 3 proprju għax kien hemm tliet kundizzjonijiet. Iżda minflokhom, tista 'tmur ma' wieħed, billi tuża l-mekkaniżmu coalesce ... IN:

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

Hekk aħna NULL "qabda", u kumplessa OR M'għandekx għalfejn tħawwad bil-parentesi.

B'kollox

Ejja nirranġaw dak li ksibna:

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

U peress li din il-funzjoni grillu tista 'tintuża biss fi UPDATEtrigger minħabba l-preżenza OLD/NEW fil-kundizzjoni ta 'livell ta' fuq, allura din il-kundizzjoni ġeneralment tista 'titneħħa fi WHEN-kondizzjoni kif muri f'#1...

Sors: www.habr.com

Żid kumment