ProHoster > Блог > Gudanarwa > PostgreSQL Antipatterns: Kimanta Yanayi a cikin SQL

PostgreSQL Antipatterns: Kimanta Yanayi a cikin SQL

SQL ba C++ ba, kuma ba JavaScript ba. Don haka, lissafin maganganun ma’ana yana faruwa daban-daban, kuma wannan ba abu ɗaya ba ne:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

A cikin aiwatar da inganta tsarin aiwatar da tambayar PostgreSQL zai iya daidaita yanayin “sake tsarawa” daidai gwargwado, Kada ku lissafta wasu daga cikinsu don bayanan mutum ɗaya, danganta su da yanayin da aka yi amfani da su ... A takaice, hanya mafi sauƙi ita ce ɗauka cewa ku ba zai iya sarrafawa ba a wane tsari za su yi (kuma ko za a lissafta su gaba daya) daidai yanayi.

Don haka, idan har yanzu kuna son sarrafa fifiko, kuna buƙatar tsara shi sanya waɗannan sharuɗɗan ba su daidaita ta amfani da sharadi maganganu и aiki.

PostgreSQL Antipatterns: Kimanta Yanayi a cikin SQL
Bayanai da aiki tare da su shine tushen hadaddun VLSI namu, don haka yana da mahimmanci a gare mu cewa ana gudanar da ayyukan a kansu ba kawai daidai ba, amma har da inganci. Bari mu dubi takamaiman misalai inda za a iya yin kurakurai a cikin lissafin maganganu, da kuma inda ya dace da inganta aikin su.

#0: RTFM

farawa misali daga takardun:

Lokacin da tsarin kimantawa yana da mahimmanci, ana iya kama shi ta amfani da ginin CASE. Misali, wannan hanya ce ta guje wa rarrabuwa ta sifili a cikin jumla WHERE wanda ba a dogara ba:

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

Zabin aminci:

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

Tsarin da aka yi amfani da shi ta wannan hanya CASE yana kare magana daga ingantawa, don haka ya kamata a yi amfani da shi kawai idan ya cancanta.

#1: yanayin jawo

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

Duk abin da alama yana da kyau, amma ... Babu wanda yayi alkawarin cewa zuba jari SELECT ba za a kashe shi idan yanayin farko karya ne. Mu gyara shi da gida IF:

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

Yanzu bari mu duba a hankali - duk jikin aikin jawo yana "nannade" a ciki IF. Wannan yana nufin cewa babu wani abu da zai hana mu cire wannan yanayin daga hanyar yin amfani da shi WHEN-sharadi:

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

Ana ba da tabbacin wannan hanyar don adana albarkatun uwar garken lokacin da yanayin karya ne.

#2: KO/DA sarkar

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

In ba haka ba, za ku iya ƙare tare da duka biyu EXISTS zai zama "gaskiya", amma duka biyu za su cika.

Amma idan mun san tabbas daya daga cikinsu shine "gaskiya" sau da yawa (ko "ƙarya" - don AND- sarƙoƙi) - shin zai yiwu a ko ta yaya "ƙara fifikonsa" don kada a sake kashe na biyu?

Ya bayyana cewa yana yiwuwa - tsarin algorithmic yana kusa da batun labarin PostgreSQL Antipatterns: rikodin da ba kasafai ba zai kai tsakiyar JOIN.

Bari kawai mu “kore” duka waɗannan sharuɗɗan ƙarƙashin CASE:

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

A wannan yanayin ba mu ayyana ba ELSE-daraja, wato, idan duka sharuddan karya ne CASE zai dawo NULL, wanda aka fassara a matsayin FALSE в WHERE-sharadi.

Ana iya haɗa wannan misalin ta wasu hanyoyi - dangane da dandano da launi:

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

#3: yadda [ba] rubuta sharuɗɗan

Mun shafe kwanaki biyu muna nazarin dalilan da suka haifar da "bakon" aiki na wannan faɗakarwa - bari mu ga dalilin.

Source:

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

Matsala #1: rashin daidaito baya mutunta NULL

Bari mu yi tunanin cewa komai OLD- filayen suna da ma'ana NULL. Me zai faru?

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

Kuma daga mahangar yin aiki da yanayin NULL daidai FALSE, kamar yadda aka ambata a sama.

yanke shawara: amfani da mai aiki IS DISTINCT FROM daga ROW-Mai aiki, kwatanta dukkan bayanan lokaci guda:

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

Matsala #2: aiwatarwa daban-daban na ayyuka iri ɗaya

Kwatanta:

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

Me yasa ake samun ƙarin saka hannun jari a nan? SELECT? Aiki to_regclass? Me yasa ya bambanta?..

Mu gyara:

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

Matsala #3: fifikon ayyukan bool

Bari mu tsara tushen:

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

Kash... A zahiri, ya zama cewa idan ɗayan sharuɗɗan biyu na farko gaskiya ne, gabaɗayan yanayin ya koma TRUE, ba tare da la'akari da rashin daidaito ba. Kuma wannan ba shine abin da muke so ba.

Mu gyara:

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

Matsala #4 (karamin): hadaddun KO yanayin filin daya

A gaskiya, muna da matsaloli a No. 3 daidai saboda akwai sharuɗɗa uku. Amma maimakon su za ku iya samun ta tare da ɗaya, ta amfani da injin coalesce ... IN:

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

Don haka mu NULL "zamu kama", kuma da wahala OR Babu buƙatar shinge da shinge.

Jimlar

Bari mu yi rikodin abin da muka samu:

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

Kuma idan kun yi la'akari da cewa wannan aikin jawo za a iya amfani dashi kawai a ciki UPDATE-haɓaka saboda samuwa OLD/NEW a cikin yanayin babban matakin, to ana iya sanya wannan yanayin gabaɗaya a ciki WHEN- yanayin, kamar yadda aka nuna a # 1 ...

source: www.habr.com

Add a comment