ProHoster > Blogs > Administrācija > PostgreSQL antipatterns: nosacÄ«jumu aprēķināŔana SQL

PostgreSQL antipatterns: nosacÄ«jumu aprēķināŔana SQL

SQL nav C++ un nav JavaScript. Tādēļ loÄ£isko izteiksmju novērtÄ“Å”ana notiek citādi, un tas - pavisam nav viens un tas pats:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Optimizējot PostgreSQL izpildes plāna procesus var brÄ«vi "pārkārtot" ekvivalentus nosacÄ«jumus, neizvērtējot dažus no tiem atseviŔķām ierakstiem, pieŔķirot tos pie pielietotā indeksa nosacÄ«juma... ÄŖsāk sakot, visvieglāk ir uzskatÄ«t, ka jÅ«s iepriekÅ” nevarat kontrolēt kādā secÄ«bā tiks (un tiks) novērtēti vienlÄ«dzÄ«gi nosacÄ«jumi.

Tādēļ, ja prioritātes pārvaldÄ«Å”ana tomēr ir vēlama, jāstrukturē Å”os nosacÄ«jumus par nevienlÄ«dzÄ«giem izmantojot nosacÄ«jumus izteiksmes un operatorus.

PostgreSQL antipatterns: nosacÄ«jumu aprēķināŔana SQL
Dati un darbs ar tiem ir pamats mÅ«su kompleksajam SBIS, tādēļ mums ir ļoti svarÄ«gi, lai operācijas ar tiem tiktu veiktas ne tikai pareizi, bet arÄ« efektÄ«vi. ApskatÄ«sim konkrētus piemērus, kur var rasties kļūdas izteiksmju aprēķināŔanā, un kur var uzlabot to efektivitāti.

#0: RTFM

Sākuma piemērs no dokumentācijas:

Kad aprēķina secÄ«ba ir svarÄ«ga, to var nostiprināt, izmantojot konstrukciju CASE. Piemēram, Å”is veids, kā izvairÄ«ties no dalÄ«Å”anas ar nulli apgalvojumā WHERE nav droÅ”s:

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

DroÅ”a iespēja:

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

Tāda konstrukcija CASE aizsargā izteiksmi no optimizācijas, tādēļ to vajadzētu izmantot tikai tad, kad tas ir nepiecieÅ”ams.

#1: усŠ»Š¾Š²ŠøŠµ Š² трŠøŠ³Š³ŠµŃ€Šµ

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

Viss izskatās labi, betā€¦ Neviens nenodroÅ”ina, ka iemiesotais SELECT netiks izpildÄ«ts, ja pirmais nosacÄ«jums ir nepaties. Labosim to, izmantojot iekļautus IF:

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

Tagad apskatÄ«sim rÅ«pÄ«gi - visa trigeri funkcijas Ä·ermenis izrādÄ«jās "iesaiņots" IF. Un tas nozÄ«mē, ka nekas neliedz mums izņemt Å”o nosacÄ«jumu no procedÅ«ras, izmantojot WHEN-nosacÄ«jumu:

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

Šāda pieeja garantēti ļauj ietaupīt servera resursus, ja nosacījums ir nepaties.

#2: OR/AND-цŠµŠæŠ¾Ń‡ŠŗŠ°

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

Nepatīkamā gadījumā var būt tā, ka abi EXISTS būs "patiesi", bet abi tiks izpildīti.

Bet, ja mēs precÄ«zi zinām, ka viens no tiem ir "paties" daudz biežāk (vai "nepaties" - attiecÄ«bā uz AND-ces afēnu) ā€” vai nevarētu kaut kā Ā«paaugstināt tā prioritātiĀ», lai otrais neizpildÄ«tos lieki?

Izrādās, var ā€” algoritmiskais pieejas veids ir lÄ«dzÄ«gs raksta tēmai PostgreSQL Antipatterns: retā ieraksts nonāks lÄ«dz JOIN vidum.

VienkārÅ”i Ā«ieslÄ«dēsim zem CASEĀ» abos Å”ajos apstākļos:

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

Å ajā gadÄ«jumā mēs neesam definējuÅ”i ELSE-vērtÄ«bu, proti, ja abi apstākļi ir nepatiesi CASE atgriezÄ«s NULL, kas interpretējas kā FALSE par WHERE-apstākļos.

Å o piemēru var apvienot arÄ« citādi ā€” pēc gaumes un krāsas:

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

#3: ŠŗŠ°Šŗ [Š½Šµ] Š½Š°Š“Š¾ ŠæŠøсŠ°Ń‚ŃŒ усŠ»Š¾Š²Šøя

Mēs pavadÄ«jām divas dienas, analizējot iemeslus Ā«dÄ«vainajaiĀ» Ŕī trigera darbÄ«bai ā€” apskatÄ«sim, kāpēc.

Source code:

IF( NEW."Dokuments_" ir null vai NEW."Dokuments_" = (select '"Komplekts"'::regclass::oid) vai NEW."Dokuments_" = (select to_regclass('"DokumentsPārzinē"')::oid)
     AND (   OLD."DokumentsMūsuOrganizācija"  NEW."DokumentsMūsuOrganizācija"
          OR OLD."Dzēsts"  NEW."Dzēsts"
          OR OLD."Datums"  NEW."Datums"
          OR OLD."Laiks"  NEW."Laiks"
          OR OLD."PersonaIzveidoja"  NEW."PersonaIzveidoja" ) ) THEN ...

Problēma Nr. 1: nevienlīdzība neņem vērā NULL

Iedomājieties, ka visi OLD-lauki bija ar vērtību NULL. Kas sanāks?

SELECT NULL  1 OR NULL  2;
-- NULL

Un no nosacÄ«juma izpildes viedokļa NULL ir ekvivalentā FALSE, kā minēts iepriekÅ”.

Risinājums: izmantojiet operatoru IS DISTINCT FROM no ROW-operatora, salīdzinot uzreiz visu ierakstu:

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

Problēma Nr. 2: atŔķirÄ«gs vienāda funkcionalitāte Ä«stenojums

Salīdzināsim:

NEW."Dokuments_" = (select '"Komplekts"'::regclass::oid)
NEW."Dokuments_" = (select to_regclass('"DokumentsPārzinē"')::oid)

Kāpēc te lieki iedziļinājumi SELECT? Š фуŠ½ŠŗцŠøя to_regclass? Š ŠæŠ¾-рŠ°Š·Š½Š¾Š¼Ńƒ-тŠ¾ ŠæŠ¾Ń‡ŠµŠ¼Ńƒ?..

Labosim:

NEW."Dokuments_" = '"Komplekts"'::regclass::oid
NEW."Dokuments_" = '"DokumentsPārzinē"'::regclass::oid

Problēma Nr. 3: bool operatoru prioritāte

Formatēsim avota kodu:

{... IS NULL} OR
{... Komplekts} OR
{... DokumentsPārzinē} AND
( {... nevienlīdzības} )

Upsā€¦ Faktiski, izrādÄ«jās, ka, ja kāda no pirmajiem diviem nosacÄ«jumiem bÅ«s patiesa, visi nosacÄ«jumi kopumā pārvērÅ”as par TRUE, neraugoties uz nevienlÄ«dzÄ«bām. Bet tas vispār nav tas, ko vēlējāmies.

Labosim:

(
  {... IS NULL} OR
  {... Komplekts} OR
  {... DokumentsPārzinē}
) AND
( {... nevienlīdzības} )

Problēma Nr. 4 (mazs): sarežģīts OR nosacījums vienam laukam

PatiesÄ«bā, problēmas 3. numurā radās tieÅ”i tāpēc, ka bija trÄ«s nosacÄ«jumi. Bet to vietā var iztikt ar vienu, izmantojot mehānismu coalesce ... IN:

coalesce(NEW."Dokuments_"::text, '') IN ('', '"Komplekts"', '"DokumentsParAlgu"')

Tā mēs arÄ« NULL Ā«noÄ·ersimĀ», un sarežģītu OR ar iekavām nebÅ«s jānodarbojas.

Kopējā summa

Fiksēsim to, ko esam ieguvuÅ”i:

IF (
  coalesce(NEW."Dokuments_"::text, '') IN ('', '"Komplekts"', '"DokumentsParAlgu"') AND
  (
    OLD."DokumentsMūsuOrganizācija"
  , OLD."Dzēsts"
  , OLD."Datums"
  , OLD."Laiks"
  , OLD."PersonasIzveidojis"
  ) IS DISTINCT FROM (
    NEW."DokumentsMūsuOrganizācija"
  , NEW."Dzēsts"
  , NEW."Datums"
  , NEW."Laiks"
  , NEW."PersonasIzveidojis"
  )
) THEN ...

Un, ja ņem vērā, ka Å”o trigera funkciju var pielietot tikai UPDATE-trigerÄ«, ņemot vērā OLD/NEW augstā lÄ«meņa nosacÄ«jumā, tad Å”o nosacÄ«jumu var vispār iznest WHEN-nosacÄ«jumā, kā tika parādÄ«ts #1ā€¦

Avots: habr.com

Iegādājieties uzticamu tÄ«mekļa hostingu ar DDoS aizsardzÄ«bu, VPS VDS serveriem šŸ”„ Iegādājieties uzticamu tÄ«mekļa hostingu ar DDoS aizsardzÄ«bu, VPS VDS serveriem | ProHoster