ProHoster > Blog > башкаруу > PostgreSQL Antipatterns: SQLде абалды баалоо

PostgreSQL Antipatterns: SQLде абалды баалоо

SQL C++ эмес, JavaScript да эмес. Демек, логикалык туюнтмаларга баа берүү ар түрдүү жана бул такыр эле эмес:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL сурамынын аткаруу планын оптималдаштырууда эквиваленттүү шарттарды өзүм билемдик менен "кайра түзө" алат, жеке жазуулар үчүн алардын эч бирин эсептебеңиз, колдонулган индекстин абалына кайрылыңыз ... Кыскасы, эң оңой жолу - сиз башкара албайт алар кандай тартипте боло турганы (жана алар такыр эсептелеби) барабар шарттар.

Ошондуктан, эгерде сиз дагы эле приоритетти башкарууну кааласаңыз, анда структуралык жактан керек бул шарттарды бирдей эмес кылып шарттуу менен сөздөр и операторлор.

PostgreSQL Antipatterns: SQLде абалды баалоо
Маалыматтар жана алар менен иштөө негизи болуп саналат биздин VLSI комплексинин, ошондуктан биз үчүн алар боюнча операциялар туура гана эмес, ошондой эле натыйжалуу аткарылышы абдан маанилүү. Келгиле, туюнтмаларды баалоодо каталар кетирилиши мүмкүн болгон конкреттүү мисалдарды карап көрөлү жана алардын эффективдүүлүгүн жогорулатуу зарыл.

№0: RTFM

Баштоо документациядан мисал:

Баалоо тартиби маанилүү болгондо, аны конструкция менен бекитсе болот CASE. Мисалы, сүйлөмдө нөлгө бөлүнүүнү болтурбоо үчүн ушундай жол WHERE ишенимсиз:

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

Коопсуз вариант:

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

Колдонулган курулуш CASE туюнтманы оптималдаштыруудан коргойт, ошондуктан аны зарыл болгондо гана колдонуу керек.

№1: триггер шарты

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

Баары жакшы окшойт, бирок... Эч ким инвестиция салган деп убада бербейт SELECT биринчи шарт туура эмес болсо, аткарылбайт. Аны менен оңдоңуз уя салынган IF:

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

Эми кылдаттык менен карап көрөлү - триггер функциясынын бүт денеси "оролгон" болуп чыкты IF. Жана бул эч нерсе бизге бул шартты колдонуу процедурасынан алып салууга тоскоолдук кылбайт дегенди билдирет WHEN-шарттар:

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

Бул ыкма шарт жалган болсо, сервер ресурстарын кепилдик менен сактоого мүмкүндүк берет.

№2: ЖЕ/ЖАНА чынжыр

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

Болбосо, экөө тең болушу мүмкүн EXISTS чындык болот, бирок экөө тең аткарылат.

Бирок, алардын бири "чындык" экенин так билсек, алда канча көп (же "жалган" - үчүн AND-чынжырлар) - экинчиси дагы бир жолу аткарылбашы үчүн кандайдыр бир жол менен "анын артыкчылыктуулугун жогорулатуу" мүмкүнбү?

Көрсө, бул мүмкүн - алгоритмдик ыкма макаланын темасына жакын PostgreSQL Antipatterns: Сейрек кездешүүчү жазуу JOINдин ортосуна жетет.

Келгиле, бул эки шарттын тең "CASE астына түртүп" алалы:

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

Бул учурда, биз аныктаган жок ELSE-маани, башкача айтканда, эки шарт тең жалган болсо CASE кайра келет NULL, деп чечмеленет FALSE в WHERE- шарттар.

Бул мисал башка жол менен айкалыштырылышы мүмкүн - даамы жана түсү үчүн:

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

№3: шарттарды кантип жазуу керек

Биз бул триггердин "кызык" себептерин талдоо үчүн эки күн өткөрдүк - келгиле, эмне үчүн экенин карап көрөлү.

Булак:

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

Маселе №1: Теңсиздик NULLди эсепке албайт

Баарын ойлойлу OLD- талаалар маанилүү NULL. Эмне болот?

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

Ал эми шарттарды иштеп чыгуу жагынан NULL эквиваленттүү FALSE, жогоруда айтылгандай.

чечим: операторду колдонуңуз IS DISTINCT FROM от ROW-оператор, бир эле учурда бүт жазууларды салыштыруу:

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

№2 маселе: бир эле функциянын ар кандай ишке ашырылышы

Кудайдын салыштыралы:

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

Эмне үчүн кошумча инвестициялар бар SELECT? Функция to_regclass? Эмнеге башкача...

Келгиле оңдойлу:

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

№3 маселе: bool артыкчылыктуулугу

Булакты форматтайлы:

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

Ой... Чынында, биринчи эки шарттын кайсынысы чын болсо, бүт шарт айланат экен. TRUE, теңсиздикти эске албай. А бул биз каалаган нерсе эмес.

Келгиле оңдойлу:

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

Маселе №4 (кичинекей): татаал ЖЕ бир талаа үчүн шарт

Чынында бизде №3-те так үч шарт болгон үчүн көйгөйлөр болгон. Бирок алардын ордуна механизмди колдонуп, бирөө менен ала аласыз coalesce ... IN:

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

Бизде да ошондой NULL "кармап алуу" жана татаал OR Каша менен ызы-чуунун кереги жок.

жалпы

Келгиле, биз алган нерсени оңдойлу:

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

Жана бул триггер функциясын гана колдонсо болот UPDATEболушуна байланыштуу триггер OLD/NEW жогорку деңгээлдеги абалда, анда бул абалды жалпысынан алып салууга болот WHEN-шарт №1 көрсөтүлгөндөй...

Source: www.habr.com

Комментарий кошуу