ProHoster > Blog > Stjórnsýsla > PostgreSQL Antipatterns: ástandsmat í SQL

PostgreSQL Antipatterns: ástandsmat í SQL

SQL er ekki C++, né JavaScript. Þess vegna er mat á rökrænum tjáningum öðruvísi og þetta er alls ekki það sama:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Þó að hagræða framkvæmdaráætlun PostgreSQL fyrirspurnar getur geðþótta "endurraðað" jafngildum skilyrðum, ekki reikna neina þeirra fyrir einstakar skrár, vísa til ástands notaðrar vísitölu ... Í stuttu máli er auðveldasta leiðin að gera ráð fyrir að þú ræður ekki við í hvaða röð þær verða (og hvort þær verða yfirleitt reiknaðar) jöfn skilyrði.

Þess vegna, ef þú vilt samt stjórna forgangi, þarftu að skipuleggja gera þessi skilyrði ójöfn með skilyrtum tjáning и rekstraraðilar.

PostgreSQL Antipatterns: ástandsmat í SQL
Gögn og vinna með þau er grunnurinn af VLSI flókinu okkar, svo það er mjög mikilvægt fyrir okkur að aðgerðir á þeim séu framkvæmdar ekki aðeins rétt heldur einnig á skilvirkan hátt. Skoðum áþreifanleg dæmi þar sem hægt er að gera villur í tjáningarmati og hvar það er þess virði að bæta skilvirkni þeirra.

#0: RTFM

Byrjar dæmi úr skjölum:

Þegar matsröðin er mikilvæg er hægt að laga hana með smíðinni CASE. Til dæmis, svona til að forðast deilingu með núll í setningu WHERE óáreiðanlegt:

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

Öruggur valkostur:

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

Byggingin sem notuð var CASE verndar tjáninguna fyrir hagræðingu, þannig að það ætti aðeins að nota þegar nauðsyn krefur.

#1: kveikja ástand

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

Allt virðist líta vel út, en... Enginn lofar að fjárfest SELECT verður ekki keyrt ef fyrsta skilyrðið er rangt. Lagaðu það með hreiður IF:

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

Nú skulum við skoða vandlega - allur líkaminn af kveikjuaðgerðinni reyndist vera "vafinn inn". IF. Og þetta þýðir að ekkert kemur í veg fyrir að við fjarlægjum þetta ástand úr málsmeðferðinni WHEN-skilyrði:

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

Þessi aðferð gerir þér kleift að vista netþjónaauðlindir með ábyrgð ef ástandið er rangt.

#2: EÐA/OG keðja

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

Annars er hægt að fá að bæði EXISTS mun vera satt, en báðir verða teknir af lífi.

En ef við vitum með vissu að ein þeirra er "sönn" miklu oftar (eða "ósönn" - fyrir AND-keðjur) - er hægt að "auka forgang sinn" einhvern veginn þannig að sá seinni sé ekki keyrður aftur?

Það kemur í ljós að það er mögulegt - reiknirit nálgunin er nálægt efni greinarinnar PostgreSQL Antipatterns: Sjaldgæf færsla nær miðju JOIN.

Við skulum bara „skoða undir CASE“ bæði þessi skilyrði:

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

Í þessu tilviki skilgreindum við ekki ELSE-gildi, það er að segja ef bæði skilyrðin eru röng CASE kem aftur NULL, sem er túlkað sem FALSE в WHERE- skilyrði.

Þetta dæmi er hægt að sameina á annan hátt - eftir smekk og lit:

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

#3: hvernig á að [ekki] skrifa skilyrði

Við eyddum tveimur dögum í að greina ástæðurnar fyrir „furðulegri“ kveikju þessarar kveikju - við skulum sjá hvers vegna.

Heimild:

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

Vandamál #1: Ójöfnuður gerir ekki grein fyrir NULL

Gerum ráð fyrir að allt OLD-reitir skiptu máli NULL. Hvað mun gerast?

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

Og frá því sjónarmiði að vinna út skilyrðin NULL jafngildi FALSE, eins og fyrr segir.

ákvörðun: nota stjórnanda IS DISTINCT FROM frá ROW-operator, ber saman heilar færslur í einu:

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

Vandamál númer 2: mismunandi útfærsla á sömu virkni

Bera saman:

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

Hvers vegna eru aukafjárfestingar SELECT? Aðgerð to_regclass? Af hverju er það öðruvísi...

Við skulum laga:

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

Vandamál #3: bool forgangur

Við skulum forsníða upprunann:

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

Úbbs ... Reyndar kom í ljós að ef um er að ræða sannleika einhvers af fyrstu tveimur skilyrðunum breytist allt ástandið í TRUE, án tillits til ójöfnuðar. Og þetta er alls ekki það sem við vildum.

Við skulum laga:

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

Vandamál #4 (lítið): flókið EÐA ástand fyrir einn reit

Reyndar áttum við í vandræðum í nr. 3 einmitt vegna þess að það voru þrjú skilyrði. En í stað þeirra geturðu komist af með einn með því að nota vélbúnaðinn coalesce ... IN:

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

Það erum við líka NULL "grípa", og flókið OR Þú þarft ekki að tuða með sviga.

Alls

Við skulum laga það sem við fengum:

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

Og í ljósi þess að aðeins er hægt að nota þessa kveikjuaðgerð í UPDATEkveikja vegna nærverunnar OLD/NEW í efri stigi ástandi, þá er almennt hægt að taka þetta ástand út í WHEN-ástand eins og sýnt er í #1...

Heimild: www.habr.com

Bæta við athugasemd