ProHoster > Blog > administratë > Antimodelet e PostgreSQL: Vlerësimi i gjendjes në SQL

Antimodelet e PostgreSQL: Vlerësimi i gjendjes në SQL

SQL nuk është C++, as nuk është JavaScript. Prandaj, vlerësimi i shprehjeve logjike është i ndryshëm, dhe kjo nuk është aspak e njëjta gjë:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ndërsa optimizoni planin e ekzekutimit të një pyetjeje PostgreSQL mund të "rirregullojë" në mënyrë arbitrare kushtet ekuivalente, mos llogarisni asnjërën prej tyre për regjistrime individuale, referojuni gjendjes së indeksit të aplikuar ... Shkurtimisht, mënyra më e lehtë është të supozoni se ju nuk mund të menaxhojë renditja në të cilën do të jenë (dhe nëse do të llogariten fare) të barabartë kushtet.

Prandaj, nëse ende dëshironi të menaxhoni prioritetin, duhet ta menaxhoni në mënyrë strukturore i bëjnë këto kushte të pabarabarta me kusht shprehjeve и operatorët.

Antimodelet e PostgreSQL: Vlerësimi i gjendjes në SQL
Të dhënat dhe puna me to është baza të kompleksit tonë VLSI, ndaj është shumë e rëndësishme për ne që operacionet mbi to të kryhen jo vetëm në mënyrë korrekte, por edhe me efikasitet. Le të shohim shembuj konkretë ku mund të bëhen gabime në vlerësimin e shprehjes dhe ku ia vlen të përmirësohet efikasiteti i tyre.

#0: RTFM

Duke filluar shembull nga dokumentacioni:

Kur rendi i vlerësimit është i rëndësishëm, ai mund të rregullohet me konstrukt CASE. Për shembull, në këtë mënyrë për të shmangur ndarjen me zero në një fjali WHERE e pabesueshme:

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

Opsioni i sigurt:

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

Ndërtimi i përdorur CASE mbron shprehjen nga optimizimi, kështu që duhet të përdoret vetëm kur është e nevojshme.

# 1: gjendja e nxitjes

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

Gjithçka duket se duket mirë, por... Askush nuk e premton se investimi SELECT nuk do të ekzekutohet nëse kushti i parë është i rremë. Rregullojeni me folezuar IF:

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

Tani le të shohim me kujdes - i gjithë trupi i funksionit të këmbëzës doli të jetë "mbështjellë" brenda IF. Dhe kjo do të thotë se asgjë nuk na pengon ta heqim këtë gjendje nga procedura e përdorimit WHEN-kushtet:

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

Kjo qasje ju lejon të kurseni burimet e serverit me një garanci nëse kushti është i rremë.

#2: OSE/DHE zinxhir

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

Përndryshe, mund të merret që të dyja EXISTS do të jetë e vërtetë, por të dy do të ekzekutohen.

Por nëse e dimë me siguri se njëra prej tyre është "e vërtetë" shumë më shpesh (ose "e rreme" - për AND-zinxhirët) - a është e mundur që disi të "rrisni përparësinë e tij" në mënyrë që i dyti të mos ekzekutohet edhe një herë?

Rezulton se është e mundur - qasja algoritmike është afër temës së artikullit Antimodelët PostgreSQL: Hyrja e rrallë arrin në mes të një JOIN.

Le të "fusim nën CASE" të dyja këto kushte:

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

Në këtë rast, ne nuk kemi përcaktuar ELSE-vlera, domethënë nëse të dy kushtet janë false CASE do te kthehen NULL, e cila interpretohet si FALSE в WHERE- kushtet.

Ky shembull mund të kombinohet në një mënyrë tjetër - për shije dhe ngjyrë:

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

#3: si të [mos] shkruani kushtet

Ne kaluam dy ditë për të analizuar arsyet e ndezjes "të çuditshme" të këtij shkaktari - le të shohim pse.

Burimi:

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

Problemi #1: Pabarazia nuk llogaritet për NULL

Le të supozojmë se gjithçka OLD-Fushat kishin rëndësi NULL. Çfarë do të ndodhë?

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

Dhe nga pikëpamja e përpunimit të kushteve NULL ekuivalente FALSE, siç u përmend më lart.

vendim: përdorni operatorin IS DISTINCT FROM nga ROW-operator, duke krahasuar të gjitha të dhënat menjëherë:

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

Problemi numër 2: zbatim i ndryshëm i të njëjtit funksionalitet

Le të krahasojmë:

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

Pse ka investime shtesë SELECT? Një funksion to_regclass? Pse eshte ndryshe...

Le të rregullojmë:

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

Problemi #3: përparësia bool

Le të formatojmë burimin:

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

Oops ... Në fakt, rezultoi se në rastin e vërtetësisë së ndonjë prej dy kushteve të para, e gjithë kushti kthehet në TRUE, duke mos marrë parasysh pabarazitë. Dhe kjo nuk është aspak ajo që ne donim.

Le të rregullojmë:

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

Problemi #4 (i vogël): kusht kompleks OSE për një fushë

Në fakt ne kishim probleme në numrin 3 pikërisht sepse ishin tre kushte. Por në vend të tyre, ju mund të kaloni me një, duke përdorur mekanizmin coalesce ... IN:

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

Kështu jemi edhe ne NULL "kapur", dhe komplekse OR Ju nuk duhet të shqetësoheni me kllapa.

Në total

Le të rregullojmë atë që kemi:

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

Dhe duke pasur parasysh se ky funksion aktivizues mund të përdoret vetëm në UPDATEshkas për shkak të pranisë OLD/NEW në gjendjen e nivelit të lartë, atëherë kjo gjendje në përgjithësi mund të hiqet WHEN-gjendja siç tregohet në #1...

Burimi: www.habr.com

Shto një koment