Antipatterns za PostgreSQL: Tathmini ya Masharti katika SQL

SQL sio C++, na sio JavaScript. Kwa hivyo, hesabu ya misemo ya kimantiki hufanyika tofauti, na hii sio kitu sawa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Katika mchakato wa kuboresha mpango wa utekelezaji wa swala la PostgreSQL inaweza "kupanga upya" hali sawa kiholela, usihesabu baadhi yao kwa rekodi za kibinafsi, yanahusiana na masharti ya index iliyotumiwa ... Kwa kifupi, njia rahisi ni kudhani kuwa wewe haiwezi kudhibiti watahesabiwa kwa utaratibu gani (na kama watahesabiwa kabisa) sawa masharti.

Kwa hivyo, ikiwa bado unataka kusimamia kipaumbele, unahitaji kuitengeneza kufanya masharti haya kutokuwa sawa kwa kutumia masharti maneno и waendeshaji.

Data na kufanya kazi nao ni msingi tata yetu ya VLSI, kwa hiyo ni muhimu sana kwetu kwamba shughuli juu yao hazifanyiki kwa usahihi tu, bali pia kwa ufanisi. Hebu tuangalie mifano maalum ambapo makosa katika kuhesabu maneno yanaweza kufanywa, na ambapo inafaa kuboresha ufanisi wao.

#0: RTFM

Kuanzia mfano kutoka kwa nyaraka:

Wakati mpangilio wa tathmini ni muhimu, inaweza kunaswa kwa kutumia muundo CASE. Kwa mfano, hii ni njia ya kuzuia mgawanyiko kwa sifuri katika sentensi WHERE asiyeaminika:

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

Chaguo salama:

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

Ubunifu uliotumika kwa njia hii CASE hulinda usemi dhidi ya uboreshaji, kwa hivyo inapaswa kutumika tu inapohitajika.

#1: hali ya kichochezi

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

Kila kitu kinaonekana kuwa kizuri, lakini ... Hakuna mtu anayeahidi kwamba uwekezaji SELECT haitatekelezwa ikiwa sharti la kwanza ni la uwongo. Hebu turekebishe na kiota IF:

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

Sasa hebu tuangalie kwa uangalifu - mwili mzima wa kazi ya trigger "imefungwa" ndani IF. Hii ina maana kwamba hakuna kitu kinatuzuia kuondoa hali hii kutoka kwa utaratibu wa kutumia WHEN-masharti:

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

Mbinu hii imehakikishwa kuokoa rasilimali za seva wakati hali si kweli.

#2: AU/NA mnyororo

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

Vinginevyo, unaweza kuishia na zote mbili EXISTS itakuwa "kweli", lakini zote mbili zitatimizwa.

Lakini ikiwa tunajua kwa hakika kuwa mmoja wao ni "kweli" mara nyingi zaidi (au "uongo" - kwa AND-minyororo) - inawezekana kwa namna fulani "kuongeza kipaumbele chake" ili ya pili isitekelezwe tena?

Inageuka kuwa inawezekana - mbinu ya algorithmic iko karibu na mada ya makala PostgreSQL Antipatterns: rekodi adimu itafikia katikati ya JIUNGE.

Hebu "tusukume" masharti haya yote mawili chini ya KESI:

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

Katika kesi hii hatukufafanua ELSE-thamani, yaani, ikiwa masharti yote mawili ni ya uwongo CASE itarudi NULL, ambayo inafasiriwa kama FALSE в WHERE-masharti.

Mfano huu unaweza kuunganishwa kwa njia zingine - kulingana na ladha na rangi:

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

#3: jinsi [si] kuandika masharti

Tulitumia siku mbili kuchambua sababu za operesheni "ya kushangaza" ya kichochezi hiki - wacha tuone ni kwanini.

Chanzo:

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

Tatizo #1: ukosefu wa usawa hauheshimu NULL

Hebu fikiria kwamba kila kitu OLD-mashamba yalikuwa na maana NULL. Nini kitatokea?

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

Na kutoka kwa mtazamo wa kufanya kazi nje ya masharti NULL sawa FALSE, kama ilivyoelezwa hapo juu.

uamuzi: tumia opereta IS DISTINCT FROM kutoka ROW- mwendeshaji, kulinganisha rekodi nzima mara moja:

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

Tatizo #2: utekelezaji tofauti wa utendakazi sawa

Hebu tulinganishe:

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

Kwa nini kuna uwekezaji wa ziada hapa? SELECT? Kitendaji to_regclass? Kwa nini ni tofauti? ..

Hebu turekebishe:

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

Tatizo #3: kipaumbele cha uendeshaji wa bool

Wacha tupange chanzo:

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

Lo ... Kwa kweli, ikawa kwamba ikiwa yoyote ya masharti mawili ya kwanza ni ya kweli, hali nzima inageuka TRUE, bila kuzingatia usawa. Na hii sio kabisa tuliyotaka.

Hebu turekebishe:

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

Tatizo #4 (ndogo): changamano AU hali ya sehemu moja

Kweli, tulikuwa na matatizo katika Nambari 3 kwa usahihi kwa sababu kulikuwa na masharti matatu. Lakini badala yao unaweza kupata na moja, kwa kutumia utaratibu coalesce ... IN:

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

Kwa hiyo sisi NULL "tutashika", na ngumu OR Hakuna haja ya kuweka uzio na mabano.

Katika jumla ya

Wacha turekodi kile tulichonacho:

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

Na ukizingatia kuwa kitendaji hiki cha kichochezi kinaweza kutumika tu ndani UPDATE-anzisha kwa sababu ya kupatikana OLD/NEW katika hali ya juu, basi hali hii inaweza kuwekwa ndani WHEN-hali, kama inavyoonyeshwa katika #1...

Chanzo: mapenzi.com