ProHoster > Blog > Идораи > Antipatterns PostgreSQL: Арзёбии вазъият дар SQL

Antipatterns PostgreSQL: Арзёбии вазъият дар SQL

SQL C++ нест ва JavaScript ҳам нест. Аз ин рӯ, арзёбии ибораҳои мантиқӣ гуногун аст ва ин тамоман як чиз нест:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ҳангоми оптимизатсияи нақшаи иҷрои дархости PostgreSQL шартхои баробарро худсарона «аз нав ташкил» карда метавонад, ягонтои онҳоро барои сабтҳои инфиродӣ ҳисоб накунед, ба ҳолати индекси татбиқшаванда муроҷиат кунед ... Хулоса, роҳи осонтарин ин аст, ки шумо фикр кунед идора карда наметавонад тартиби ки онҳо хоҳанд буд (ва оё онҳо умуман ҳисоб карда мешаванд) баробар шароит.

Аз ин рӯ, агар шумо ба ҳар ҳол хоҳед, ки афзалиятро идора кунед, ба шумо лозим аст, ки сохторӣ ин шароитро нобаробар созанд бо шартй ифодаҳо и операторҳо.

Antipatterns PostgreSQL: Арзёбии вазъият дар 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-занҷирҳо) - оё мумкин аст, ки бо ягон роҳ "афзалияти онро зиёд кард", то дуюмаш бори дигар иҷро нашавад?

Маълум мешавад, ки ин имконпазир аст - муносибати алгоритмӣ ба мавзӯи мақола наздик аст Antipatterns PostgreSQL: Вуруди нодир ба миёнаи 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 нишон дода шудааст...

Манбаъ: will.com

Илова Эзоҳ