🥇 Antipatterns PostgreSQL: Gwerthuso Cyflwr yn SQL

Nid C++ yw SQL, ac nid JavaScript mohono ychwaith. Felly, mae'r gwerthusiad o ymadroddion rhesymegol yn wahanol, ac nid yw hyn yr un peth o gwbl:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Wrth optimeiddio cynllun gweithredu ymholiad PostgreSQL yn gallu "aildrefnu" yr amodau cyfatebol yn fympwyol, peidiwch â chyfrifo unrhyw un ohonynt ar gyfer cofnodion unigol, cyfeiriwch at gyflwr y mynegai cymhwysol ... Yn fyr, y ffordd hawsaf yw tybio eich bod methu ymdopi ym mha drefn y byddant (ac a fyddant yn cael eu cyfrifo o gwbl) cyfartal amodau.

Felly, os ydych yn dal eisiau rheoli blaenoriaeth, mae angen ichi wneud hynny yn strwythurol gwneud yr amodau hyn yn anghyfartal ag amodol ymadroddion и gweithredwyr.

Data a gweithio gyda nhw yw'r sail o'n cyfadeilad VLSI, felly mae'n bwysig iawn i ni fod gweithrediadau arnynt yn cael eu perfformio nid yn unig yn gywir, ond hefyd yn effeithlon. Edrychwn ar enghreifftiau pendant lle gellir gwneud gwallau wrth werthuso mynegiant, a lle mae'n werth gwella eu heffeithlonrwydd.

#0: RTFM

Yn dechrau enghraifft o ddogfennaeth:

Pan fo'r drefn werthuso yn bwysig, gellir ei osod gyda'r adeiladwaith CASE. Er enghraifft, fel hyn i osgoi rhannu o sero mewn brawddeg WHERE annibynadwy:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Opsiwn diogel:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Yr adeiladwaith a ddefnyddiwyd CASE yn amddiffyn y mynegiant rhag optimeiddio, felly dim ond pan fo angen y dylid ei ddefnyddio.

#1: cyflwr sbardun

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

Popeth yn ymddangos i edrych yn dda, ond ... Nid oes unrhyw un yn addo bod y buddsoddi SELECT ni chaiff ei weithredu os yw'r amod cyntaf yn anwir. Atgyweiria 'i ag nythu IF:

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

Nawr, gadewch i ni edrych yn ofalus - roedd corff cyfan y swyddogaeth sbarduno wedi'i "lapio" i mewn IF. Ac mae hyn yn golygu nad oes dim yn ein hatal rhag tynnu'r amod hwn o'r weithdrefn gan ddefnyddio WHEN-amodau:

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

Mae'r dull hwn yn caniatáu ichi arbed adnoddau gweinydd gyda gwarant os yw'r cyflwr yn ffug.

#2: NEU/AND cadwyn

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

Fel arall, gellir cael bod y ddau EXISTS bydd yn wir, ond bydd y ddau yn cael eu dienyddio.

Ond os ydym yn gwybod yn sicr bod un ohonynt yn "wir" yn llawer amlach (neu "anwir" - ar gyfer AND-chains) - a yw'n bosibl rhywsut "cynyddu ei flaenoriaeth" fel nad yw'r ail yn cael ei weithredu unwaith eto?

Mae'n ymddangos ei bod yn bosibl - mae'r dull algorithmig yn agos at bwnc yr erthygl Gwrthbatrymau PostgreSQL: Mynediad prin yn cyrraedd canol YMUNIAD.

Gadewch i ni "gwthio o dan CASE" y ddau amod hyn:

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

Yn yr achos hwn, ni wnaethom ddiffinio ELSE-value, hynny yw, os yw'r ddau amod yn ffug CASE bydd yn dychwelyd NULL, a ddehonglir fel FALSE в WHERE- amodau.

Gellir cyfuno'r enghraifft hon mewn ffordd arall - i flasu a lliwio:

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

#3: sut i [peidio] ysgrifennu amodau

Fe wnaethon ni dreulio dau ddiwrnod yn dadansoddi’r rhesymau dros y sbardun “rhyfedd” o’r sbardun hwn – gadewch i ni weld pam.

Ffynhonnell:

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

Problem #1: Nid yw anghydraddoldeb yn cyfrif am NULL

Gadewch i ni dybio bod popeth OLD-maes yn bwysig NULL. Beth fydd yn digwydd?

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

Ac o safbwynt gweithio allan yr amodau NULL cyfatebol FALSE, fel y crybwyllwyd uchod.

penderfyniad: defnyddio gweithredwr IS DISTINCT FROM o ROW-gweithredwr, yn cymharu cofnodion cyfan ar unwaith:

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

Rhif problem 2: gweithredu'r un swyddogaeth yn wahanol

Cymharwch:

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

Pam fod yna fuddsoddiadau ychwanegol SELECT? Mae swyddogaeth to_regclass? Pam ei fod yn wahanol...

Gadewch i ni drwsio:

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

Problem #3: blaenoriaeth uchel

Gadewch i ni fformatio'r ffynhonnell:

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

Wps ... Mewn gwirionedd, yn achos gwirionedd unrhyw un o'r ddau amod cyntaf, mae'r cyflwr cyfan yn troi'n TRUE, diystyru anghydraddoldebau. Ac nid dyma'r hyn yr oeddem ei eisiau o gwbl.

Gadewch i ni drwsio:

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

Problem #4 (bach): cyflwr NEU gymhleth ar gyfer un maes

Mewn gwirionedd, cawsom broblemau yn Rhif 3 yn union oherwydd bod tri chyflwr. Ond yn hytrach na nhw, gallwch chi ddod ymlaen ag un, gan ddefnyddio'r mecanwaith coalesce ... IN:

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

Felly ydym ni NULL "dal", a chymhleth OR Does dim rhaid i chi ffwdanu gyda cromfachau.

Yn gyfan gwbl

Gadewch i ni drwsio'r hyn a gawsom:

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

Ac o ystyried mai dim ond mewn UPDATEsbardun oherwydd presenoldeb OLD/NEW yn y cyflwr lefel uwch, yna yn gyffredinol gellir cymryd yr amod hwn allan i mewn WHEN-cyflwr fel y dangosir yn #1...

Ffynhonnell: hab.com

Antipatterns PostgreSQL: Gwerthuso Cyflwr yn SQL