🥇PostgreSQL Antipatterns: Kev Ntsuas Kev Ntsuas hauv SQL

SQL tsis yog C ++, tsis yog JavaScript. Yog li ntawd, qhov kev ntsuam xyuas ntawm cov lus piav qhia yog txawv, thiab qhov no tsis yog tib yam nkaus:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Thaum optimizing txoj kev npaj ua tiav ntawm PostgreSQL cov lus nug tuaj yeem arbitrarily "rearrange" cov xwm txheej sib npaug, tsis txhob suav ib qho ntawm lawv rau ib tus neeg cov ntaub ntawv, xa mus rau qhov xwm txheej ntawm qhov ntsuas ntsuas ... Hauv luv, qhov yooj yim tshaj yog xav tias koj tswj tsis tau qhov kev txiav txim uas lawv yuav yog (thiab seb lawv puas yuav raug xam txhua) sib npaug tej yam kev mob.

Yog li ntawd, yog tias koj tseem xav tswj hwm qhov tseem ceeb, koj yuav tsum tau ua kom zoo ua rau cov xwm txheej no tsis sib xws nrog conditional cov lus qhia и cov neeg ua haujlwm.

Cov ntaub ntawv thiab ua haujlwm nrog lawv yog lub hauv paus ntawm peb VLSI complex, yog li nws tseem ceeb heev rau peb tias kev ua haujlwm ntawm lawv tau ua tsis tau tsuas yog kom raug, tab sis kuj ua tau zoo. Cia peb saib cov piv txwv uas ua yuam kev hauv kev ntsuam xyuas kev qhia tuaj yeem ua tau, thiab qhov twg nws tsim nyog txhim kho lawv cov txiaj ntsig.

#0: RTFM

Pib piv txwv los ntawm cov ntaub ntawv:

Thaum qhov kev txiav txim ntawm kev ntsuam xyuas tseem ceeb, nws tuaj yeem kho nrog kev tsim kho CASE. Piv txwv li, txoj kev no kom tsis txhob faib los ntawm xoom hauv kab lus WHERE tsis ntseeg siab:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Kev xaiv nyab xeeb:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Kev tsim kho siv CASE tiv thaiv cov lus qhia los ntawm optimization, yog li nws yuav tsum tsuas yog siv thaum tsim nyog.

# 1: qhov ua rau mob

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

Txhua yam zoo li zoo, tab sis ... Tsis muaj leej twg cog lus tias qhov kev nqis peev SELECT yuav tsis raug tua yog tias thawj qhov xwm txheej tsis tseeb. Kho nws nrog ua zes IF:

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

Tam sim no cia saib ua tib zoo - tag nrho lub cev ntawm qhov ua haujlwm ua haujlwm tau muab "pob" rau hauv IF. Thiab qhov no txhais tau hais tias tsis muaj dab tsi tiv thaiv peb ntawm kev tshem tawm tus mob no los ntawm txoj kev siv WHEN- yam mob:

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

Txoj hauv kev no tso cai rau koj kom txuag tau cov peev txheej server nrog kev lees paub yog tias qhov xwm txheej tsis raug.

# 2: LOS YOG / THIAB saw

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

Txwv tsis pub, nws tuaj yeem tau txais tias ob qho tib si EXISTS yuav muaj tseeb, tab sis ob leeg yuav raug tua.

Tab sis yog tias peb paub tseeb tias ib qho ntawm lawv yog "tseeb" ntau zaus (lossis "false" - rau AND-chains) - nws puas tuaj yeem ua "nce nws qhov tseem ceeb" kom qhov thib ob tsis raug tua ib zaug ntxiv?

Nws hloov tawm hais tias nws yog ua tau - lub algorithmically mus kom ze yog ze rau lub ntsiab lus ntawm tsab xov xwm PostgreSQL Antipatterns: Tsis tshua muaj nkag mus txog qhov nruab nrab ntawm JOIN.

Cia li "shove under CASE" ob qho tib si ntawm cov xwm txheej no:

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

Hauv qhov no, peb tsis tau txhais ELSE-value, uas yog, yog tias ob qho xwm txheej tsis tseeb CASE yuav rov qab los NULL, uas yog txhais raws li FALSE в WHERE- tej yam kev mob.

Qhov piv txwv no tuaj yeem ua ke rau lwm txoj hauv kev - kom saj thiab xim:

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

#3: yuav ua li cas [tsis] sau tej yam kev mob

Peb tau siv sijhawm ob hnub los tshuaj xyuas cov laj thawj rau qhov "kev txawv txav" ntawm qhov ua rau qhov no - cia saib yog vim li cas.

Qhov chaw:

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

Teeb meem #1: Kev tsis sib xws tsis suav rau NULL

Cia peb xav tias txhua yam OLD- teb tseem ceeb NULL. Yuav ua li cas?

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

Thiab los ntawm qhov pom ntawm kev ua haujlwm tawm cov xwm txheej NULL sib npaug FALSE, raws li tau hais los saum toj no.

kev txiav txim siab: siv tus neeg teb xov tooj IS DISTINCT FROM los ntawm ROW- tus neeg teb xov tooj, sib piv tag nrho cov ntaub ntawv ib zaug:

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

Qhov teeb meem 2: sib txawv ntawm kev siv tib yam kev ua haujlwm

Sib piv:

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

Vim li cas thiaj muaj kev nqis peev ntxiv SELECT? Ib txoj haujlwm to_regclass? Vim li cas thiaj txawv...

Cia peb kho:

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

Teeb meem #3: bool precedent

Wb format qhov chaw:

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

Oops ... Qhov tseeb, nws muab tawm tias nyob rau hauv rooj plaub ntawm qhov tseeb ntawm ib qho ntawm thawj ob qho xwm txheej, tag nrho cov xwm txheej hloov mus rau hauv TRUE, tsis quav ntsej txog kev tsis sib xws. Thiab qhov no tsis yog txhua yam peb xav tau.

Cia peb kho:

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

Teeb meem #4 (me): complex OR condition for one field

Qhov tseeb, peb muaj teeb meem nyob rau hauv No.3 precisely vim hais tias muaj peb tej yam kev mob. Tab sis es tsis txhob ntawm lawv, koj yuav tau txais los ntawm ib tug, siv lub mechanism coalesce ... IN:

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

Yog li peb NULL "catch", thiab complex OR Koj tsis tas yuav fuss nrog cov kab lus.

Tag nrho

Cia peb kho qhov peb tau txais:

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

Thiab muab hais tias qhov kev ua haujlwm no tsuas yog siv tau rau hauv UPDATEtshwm sim vim muaj OLD/NEW nyob rau hauv lub Upper-theem mob, ces tus mob no feem ntau yuav muab tshem tawm nyob rau hauv WHEN-cov xwm txheej raws li qhia hauv #1...

Tau qhov twg los: www.hab.com

PostgreSQL Antipatterns: Kev Ntsuas Kev Ntsuas hauv SQL