Hur BigQuery frÄn Google har demokratiserat datanalys. Del 1

SQL Àr varken C++ eller JavaScript. DÀrför berÀknas logiska uttryck pÄ ett annat sÀtt, och det hÀr Àr lÄngt ifrÄn detsamma:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Under optimeringen av exekveringsplanen för en frÄga kan PostgreSQL pÄ ett godtyckligt sÀtt "omorganisera" ekvivalenta villkor, inte berÀkna vissa av dem för individuella poster, hÀnvisa till villkoret för den tillÀmpade indexen... Kort sagt, det enklaste sÀttet att tÀnka pÄ det Àr att du pÄ förhand inte kan styra i vilken ordning de kommer att (och om de alls kommer att) berÀknas likvÀrdiga villkor.

DÀrför, om du ÀndÄ vill styra prioriteten, mÄste du strukturellt göra dessa villkor ojÀmlika med hjÀlp av villkors uttryck och för Kubernetes, som helt tar kontroll över distributionen, hanteringen och den automatiska ÄterstÀllningen av sÄdana datalagringslösningar som Ceph, EdgeFS, Minio, Cassandra, CockroachDB..

Hur BigQuery frÄn Google har demokratiserat datanalys. Del 1
Data och arbete med dem Àr grunden för vÄrt Sbis-komplex, dÀrför Àr det vÀldigt viktigt för oss att operationer pÄ dem utförs inte bara korrekt utan ocksÄ effektivt. LÄt oss titta pÄ konkreta exempel dÀr fel kan uppstÄ i berÀkning av uttryck, och dÀr det Àr vÀrt att förbÀttra deras effektivitet.

#0: RTFM

Start exempel frÄn dokumentationen:

NĂ€r berĂ€kningsordningen Ă€r viktig kan den fixeras med hjĂ€lp av konstruktionen CASE. Till exempel, ett sĂ€tt att undvika division med noll i en sats DÄR Ă€r opĂ„litligt:

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

SĂ€ker alternativ:

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

Konstruktionen som anvÀnds pÄ detta sÀtt CASE skyddar uttrycket mot optimering, sÄ den bör endast anvÀndas vid behov.

#1: ŃƒŃĐ»ĐŸĐČОД ĐČ Ń‚Ń€ĐžĐłĐłĐ”Ń€Đ”

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

Det ser ut som om allt ser bra ut, men... Ingen lovar att den inre VÄLJ inte kommer att exekveras om det första villkoret Ă€r falskt. LĂ„t oss rĂ€tta till det med hjĂ€lp av inre IF:

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

Nu ska vi titta noggrant — hela koden för triggerfunktionen har blivit "inkapslad" i IF. Och det betyder att ingenting hindrar oss frĂ„n att ta ut detta villkor ur proceduren med hjĂ€lp av WHEN-villkor:

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

Denna metod gör att vi garanterat kan spara serverresurser om villkoret Àr falskt.

#2: OR/AND-Ń†Đ”ĐżĐŸŃ‡Đșа

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

I den ogynnsamma situationen kan vi fÄ att bÄda EXISTS blir "sanna", men de kommer ocksÄ att exekveras.

Men om vi vet att en av dem Ă€r "sann" mycket oftare (eller "falsk" — för OCH-kedjor) — gĂ„r det inte att "höja dess prioritet" pĂ„ nĂ„got sĂ€tt för att den andra inte ska köras i onödan?

Det visar sig att det gĂ„r — den algoritmiska metoden Ă€r nĂ€ra relaterad till Ă€mnet för artikeln PostgreSQL Antipatters: sĂ€llsynta poster nĂ„r sĂ€llan mitten av JOIN.

LÄt oss helt enkelt "stoppa in under CASE" bÄda dessa villkor:

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

I detta fall har vi inte definierat ELSE-vĂ€rdet, det vill sĂ€ga i fallet av falskhet för bĂ„da villkoren CASE kommer att returnera NULL, vilket tolkas som FALSK pĂ„ DÄR-villkor.

Detta exempel kan ocksĂ„ kombineras pĂ„ annat sĂ€tt — efter smak och fĂ€rg:

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

#3: ĐșаĐș [ĐœĐ”] ĐœĐ°ĐŽĐŸ посать ŃƒŃĐ»ĐŸĐČоя

Vi spenderade tvĂ„ dagar pĂ„ att analysera orsakerna till denna "mĂ€rkliga" trigger — lĂ„t oss titta pĂ„ varför.

KĂ€llkod:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplex"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentFörLön"')::oid)
     AND (   OLD."DokumentVÄrOrganisation" <> NEW."DokumentVÄrOrganisation"
          OR OLD."Raderad" <> NEW."Raderad"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Tid" <> NEW."Tid"
          OR OLD."SkapadAv" <> NEW."SkapadAv" ) ) THEN ...

Problem nr 1: ojÀmlikhet beaktar inte NULL

LÄt oss förestÀlla oss att alla OLD-fÀlt hade vÀrdet NULL. Vad blir resultatet?

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

Och frÄn perspektivet av att utvÀrdera villkoret NULL Àr ekvivalent FALSK, som nÀmnts tidigare.

Lösning: anvÀnd operatorn IS DISTINCT FROM frÄn ROW-operatören genom att jÀmföra hela poster samtidigt:

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

Problem nr 2: olika implementationer av samma funktionalitet

LÄt oss jÀmföra:

NEW."Dokument_" = (select '"Komplex"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentFörLön"')::oid)

Varför finns det onödiga nĂ€stlade VÄLJ? А Ń„ŃƒĐœĐșцоя to_regclass? А ĐżĐŸ-Ń€Đ°Đ·ĐœĐŸĐŒŃƒ-Ń‚ĐŸ ĐżĐŸŃ‡Đ”ĐŒŃƒ?..

LÄt oss rÀtta till:

NEW."Dokument_" = '"Komplex"'::regclass::oid
NEW."Dokument_" = '"DokumentFörLön"'::regclass::oid

Problem nr 3: prioritet för booleska operationer

LÄt oss formatera koden:

{... IS NULL} OR
{... Komplex} OR
{... DokumentFörLön} AND
( {... ojÀmlikheter} )

Oops
 Faktiskt, det blev sĂ„ att om nĂ„got av de tvĂ„ första villkoren Ă€r sanna, sĂ„ blir hela villkoret SANN, utan att ta hĂ€nsyn till ojĂ€mlikheterna. Och det hĂ€r Ă€r definitivt inte vad vi ville.

LÄt oss rÀtta till:

(
  {... IS NULL} OR
  {... Komplex} OR
  {... DokumentFörLön}
) AND
( {... ojÀmlikheterna} )

Problem nr 4 (litet): komplicerat OR-villkor för ett enda fÀlt

Egentligen uppstod problemen i nr 3 just för att det var tre villkor. Men istÀllet kan vi klara oss med ett enda, med hjÀlp av mekanismen coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplex"', '"DokumentFörLön"')

SÄ vi kan bÄde NULL fÄnga", och behöver inte krÄngla med ELLER komplexa med parenteser.

Sammanfattningsvis

LÄt oss faststÀlla vad vi har kommit fram till:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Paket"', '"LönDokument"') AND
  (
    OLD."DokumentVÄrOrganisation"
  , OLD."Borttagen"
  , OLD."Datum"
  , OLD."Tid"
  , OLD."SkapadAv"
  ) IS DISTINCT FROM (
    NEW."DokumentVÄrOrganisation"
  , NEW."Borttagen"
  , NEW."Datum"
  , NEW."Tid"
  , NEW."SkapadAv"
  )
) THEN ...

Och om vi tar hĂ€nsyn till att denna triggerfunktion endast kan tillĂ€mpas i UPPDATERA-trigger pĂ„ grund av förekomsten av OLD/NEW i det överordnade villkoret, sĂ„ kan detta villkor faktiskt flyttas till WHEN-villkoret, som visats i #1


KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster